Корреляционный анализ как сделать в excel. Множественный коэффициент корреляции в Excel (Эксель)
где Е[…] – оператор математического ожидания , μ и σ – среднее случайной величины и ее стандартное отклонение .
Как следует из определения, для вычисления коэффициента корреляции
требуется знать распределение случайных величин Х и Y. Если распределения неизвестны, то для оценки
коэффициента корреляции
используется
выборочный коэффициент корреляцииr(
еще он обозначается как
R xy
или
r xy)
:
где S x – стандартное отклонение выборки случайной величины х, вычисляемое по формуле:
, знаменатель (произведение стандартных отклонений) просто нормирует числитель таким образом, что
корреляция
оказывается безразмерным числом от -1 до 1.
Корреляция
и
ковариация
предоставляют одну и туже информацию (если известны
стандартные отклонения
), но
корреляцией
удобнее пользоваться, т.к. она является безразмерной величиной.
и
ковариацию выборки
в MS EXCEL не представляет труда, так как для этого имеются специальные функции КОРРЕЛ() и КОВАР() . Гораздо сложнее разобраться, как интерпретировать полученные значения, большая часть статьи посвящена именно этому.
Как сделать формулу в Excel: пошаговая инструкция
- Для данных с нелинейной связью корреляцию
нужно использовать с осторожностью. Для некоторых задач бывает полезно преобразовать одну или обе переменных так, чтобы получить линейную взаимосвязь (для этого требуется сделать предположение о виде нелинейной связи, чтобы предложить нужный тип преобразования). - С помощью диаграммы рассеяния
у некоторых данных можно наблюдать неравную вариацию (разброс). Проблема неодинаковой вариации состоит в том, что места с высокой вариацией не только предоставляют наименее точную информацию, но и оказывают наибольшее влияние при расчете статистических показателей. Эту проблему также часто решают с помощью преобразования данных, например, с помощью логарифмирования. - У некоторых данных можно наблюдать разделение на группы (clustering), что может свидетельствовать о необходимости разделения совокупности на части.
- Выброс (резко отклоняющееся значение) может исказить вычисленное значение коэффициента корреляции. Выброс может быть причиной случайности, ошибки при сборе данных или могут действительно отражать некую особенность взаимосвязи. Так как выброс сильно отклоняется от среднего значения, то он вносит большой вклад при расчете показателя. Часто расчет статистических показателей производят с и без учета выбросов.
Рынок был для каждого управляющего по-своему неблагоприятным, но присутствие их всех в портфеле привело к большой просадке. Совпадение? Не совсем, ведь это были ПАММ-счета с похожими элементами в торговых стратегиях. Без опыта торговли на рынке Форекс может быть сложно понять, как это работает, но по корреляционной таблице степень взаимосвязи видна и так:
Расчет дисперсии в Excel
Генеральную и выборочную дисперсии легко рассчитать в Excel. Есть специальные функции: ДИСП.Г и ДИСП.В соответственно.
В чистом виде дисперсия не используется. Это вспомогательный показатель, который нужен в других расчетах. Например, в проверке статистических гипотез или расчете коэффициентов корреляции. Отсюда неплохо бы знать математические свойства дисперсии.
7 полезных формул для тех, кто считает деньги в эксель-таблице
Для расчета стандартного отклонения достаточно из дисперсии извлечь квадратный корень. Но в Excel есть и готовые функции: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В (по генеральной и выборочной совокупности соответственно).
Шаг 2. Необходимый минимум теории
Прежде чем перейти к вычислению VaR портфеля, обратимся к некоторым сведениям, которые дают понимание расчетных алгоритмов. В целом VaR портфеля из многих активов можно определить по следующей формуле:
VaR портфеля = Среднее изменение – К * Волатильность портфеля,
где
Среднее изменение – просто сумма средних изменений всех компонентов портфеля;
К – число стандартных отклонений, требуемых для заданного доверительного уровня, вычисляемое по формуле в Excel «=NORMSINV()», где аргумент – соответствующий доверительный уровень.
Волатильность портфеля калькулируется с использованием формулы, предложенной Г. Марковицем:
Волатильность портфеля = SQRT (Вектор волатильной компоненты *Корреляционная матрица * Транспонированный вектор волатильной компоненты)
Вектор волатильной компоненты можно вычислить, пользуясь формулой:
Компонента волатильности = Стоимость позиции * Волатильность позиции
Помимо уже выполненных расчетов для вычисления VaR портфеля потребуется построить корреляционную матрицу, основываясь на данных, обработанных в шаге 1.
Она представляет собой совокупность коэффициентов, отражающих корреляцию между каждым из инструментов, входящих в портфель.
Вычисления в Excel выполняются с помощью соответствующей функции «=correl()», причем построение матрицы лучше выполнить на странице, где будут размещены все прочие расчеты, в том числе и VaR. Это уже будет шаг 3.
Риск практикум. Оценка риска Value at Risk (VaR) с помощью исторического моделирования | Школа финансового анализа и инвестиционной оценки Жданова Василия и Жданова Ивана
Если из дисперсии извлечь квадратный корень, получится среднеквадратичное (стандартное) отклонение (сокращенно СКО). Встречается название среднее квадратичное отклонение и сигма (от названия греческой буквы). Общая формула стандартного отклонения в математике следующая:
