Green-sell.info

Новые технологии
41 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как построить корреляционную матрицу в excel

Инвестиционный анализ. Расчет корреляционной матрицы в Excel

В данной статье рассматривается практическая задача для любого инвестора – это расчет корреляционной матрицы. Инвесторы, рассчитывая свой портфель активов, акций, облигаций и других финансовых инструментов должны уметь рассчитывать корреляционную матрицу.

Задача любого инвестора – снизить риски вложения, для этого используется различные виды диверсификации (распределения) средств по различным направлениям. Диверсифицированный портфель содержит некоррелированные активы, другими словами, активы которые имеют различную динамику изменения стоимости (цены). Например, если цена одного актива растет, то другого изменяется независимо от первого. Для описания силы связи между активами инвесторы и аналитики используют коэффициент корреляция, который также называет коэффициентом корреляции Пирсона. Он изменяется в диапазоне от -1 до 1.

Если коэффициент корреляции между двумя активами равен 1, то их стоимость изменяется в тандеме (параллельно).
Если коэффициент корреляции равен 0, то цены активов изменяются независимо друг от друга.
Если коэффициент корреляции равен -1, то цены активов сильно зависимы друг от друга и изменяются в противоположных направлениях. Например, если один из активов растет, то другой с таким же темпом обесценивается.

Инвестору приходится анализировать множество различных активов при включении их в свой портфель и ему пригодится корреляционная матрица, которая позволяет сразу сравнить силу взаимосвязи между акциями, облигациями, золотом, недвижимость, фьючерсами, валютой и другими финансовыми инструментами. Формула расчета корреляционной матрицы представлена ниже, где i,j – индексы сравниваемых активов портфеля:

Наиболее распространено использование корреляционной матрицы на фондовом рынке при составлении инвестиционных портфелей Марковица-Тобина и их модификаций, и показывает как диверсифицированы активы портфеля.
Инвесторы советуют, что для минимизации рисков активы портфеля должны быть минимально коррелированны между собой.

Сложности использования коэффициента корреляции в инвестиционном анализе
Финансовые аналитики часто не соглашаются, что корреляция вообще может быть оценена и рассчитана. Выделяют основные недостатки:

  • Корреляционную матрицу и коэффициенты можно рассчитать только тогда когда между активами наблюдаются линейные зависимости, на практике такое встречается крайне редко.
  • При значении коэффициента корреляции равного 0 означает, что отношения и связи отсутствует на исследуемом промежутке времени, но говорит о полном отсутствии связи в будущем между активами нельзя.
  • Коэффициент корреляции неустойчив и изменяется со временем. Например, нефть имеет устойчивую долгосрочную корреляцию с золотом, но в краткосрочном периоде корреляция может сильно изменятся.

Расчет корреляционной матрицы в Excel
Заполняет колонки с изменением стоимости актива. Данные по котировкам можно получить на сайте finam.ru в разделе экспорт котировок. Данные котировки были получены с сайта finance.yahoo.com по иностранным акциям.

Далее необходимо выбрать в главном меню Excel раздел «Данные» и надстройку «Анализ данных» (если она не подключена, то следует ее подключить) – «Корреляция»

Далее необходимо в Excel выбрать входной интервал котировок (зеленая область + названия активов). Отметить галочку метки в первой строке. Выбрать выходной интервал как ячейку и нажать Oк.

Итоговый результат расчета корреляционной матрицы представлен на рисунке ниже. Можно выделить низкий коэффициент корреляции между активами TBILL и всеми остальными (стремится к 0), также между USX и GMC составляет 0.2. Это показывает высокую степень диверсификации данных активов в портфеле. Но активы ATT и GMC имеют достаточно высокую зависимость 0.5, это требует пересмотра данных активов в инвестиционном портфеле.

Читать еще:  Как повысить используемую видеопамять

Как построить корреляционную матрицу в excel

п.13 . Решение прикладных задач средствами EXCEL .

Коэффициент линейной корреляции Браве-Пирсона ( ) — параметр, характеризующий степень линейной взаимосвязи между двумя выборками. Он изменяется от (-1) (полная обратная линейная зависимость) до 1 (полная прямая пропорциональная зависимость). Коэффициент корреляции является безразмерной величиной и его значение не зависит от единиц измерения случайных величин X и У.

В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КОРРЕЛ. Параметрами функции являются КОРРЕЛ (массив 1, массив 2), где:

массив 1 — это диапазон ячеек первой случайной величины;

массив 2 — это второй интервал ячеек со значениями второй случайной величины.

При большом числе наблюдений, когда коэффициенты корреляции необходимо последовательно вычислять из нескольких рядов числовых данных, для удобства получаемые коэффициенты сводят в таблицы, называемые корреляционными матрицами.

Корреляционная матрица — это квадратная (или прямоугольная) таблица, в которой на пересечении соответствующих строки и столбца находится коэффициент корреляции между соответствующими параметрами.

В MS Excel для вычисления корреляционных матриц используется процедура Корреляция. Процедура позволяет получить корреляционную матрицу, содержащую коэффициенты корреляции между различными параметрами.

Для реализации процедуры необходимо:

•выполнить команду Сервис ►Анализ данных;

•в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку 0К;

•в появившемся диалоговом окне указать Входной интервал, то есть ввести ссыл­ку на ячейки, содержащие анализируемые данные. Для этого следует навести указатель мыши на левую верхнюю ячейку данных, нажать левую кнопку мыши и, не отпуская ее, протянуть указатель мыши к правой нижней ячейке, содержащей анализируемые данные, затем отпустить левую кнопку мыши. Входной интервал должен содержать не менее двух столбцов.

•в разделе Группировка переключатель установить в соответствии с введенными данными;

•указать выходной диапазон, то есть ввести ссылку на ячейки, в которые будут выведены результаты анализа. Для этого следует поставить флажок в левое поле Выходной интервал (навести указатель мыши и щелкнуть левой кнопкой), далее навести указатель мыши на правое поле ввода Выходной интервал и щелкнуть левой кнопкой мыши, затем указатель мыши навести на левую верхнюю ячейку выходного диапазона и щелкнуть левой кнопкой мыши. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные.

•нажать кнопку ОК.

Результаты анализа. В выходной диапазон будет выведена корреляционная матрица, в которой на пересечении каждых строки и столбца находится коэффициент корреляции между соответствующими параметрами. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждый столбец во входном диапазоне полностью коррелирует с самим собой.

Интерпретация результатов. Рассматривается отдельно каждый коэффициент корреляции между соответствующими параметрами. Его числовое значение оценивается по эмпирическим правилам, изложенным в соответствующей лекции.

Читать еще:  Alt enter в excel код символа

Ниже показаны две возможности вычисления коэффициента линейной корреляции Браве-Пирсона: на основе привлечения возможностей Мастера функций и на основе использования Пакета анализа.

Приведен пример исходных данных измерения двух показателей интеллекта (вербального (Х i ) и невербального (У i )) у 20 учащихся 8 класса. Рассчитать коэффициент корреляции.

Вербальный (Х i ): 13, 9, 8, 9, 7, 9, 8, 13, 11, 12, 8, 9, 10, 10, 12, 10, 8, 9, 10, 11.

Невербальный ( Yi ): 12, 11, 8, 12, 9, 11, 9, 13, 9, 10, 9, 8, 10, 12, 10, 10, 11, 10, 11, 13.

Для расчета коэффициента корреляции, прежде всего, необходимо ввести данные в рабочую таблицу. Откройте новую рабочую таблицу. Введите в ячейку А1 – Вербальный (Х i ) . Затем в ячейки А2-А21 — соответствующие значения. В ячейки B 1- B 21 введите название Невербальный ( Yi ) и значения. Затем вычисляется значение коэффициента корреляции между выборками. Для этого табличный курсор установите в свободную ячейку (А22). На панели инструментов нажмите кнопку Вставка функции ( fx ). В появившемся диалоговом окне Мастер функций выберите категорию Статистические и функцию КОРРЕЛ, после чего нажмите кнопку ОК. Появившееся диалоговое окно КОРРЕЛ за серое поле мышью отодвиньте вправо на 1-2 см от данных (при нажатой левой клавише). Указателем мыши введите диапазон данных Х1 в поле Массив 1 (А2-А21). В поле Массив 2 введите диапазон данных У1 (В2-В21). Нажмите кнопку ОК. В ячейке А22 появится значение коэффициента корреляции — 0,517392.

Результаты анализа. В результате будет получена таблица, показанная на рисунке.

Множественный коэффициент корреляции в Excel (Эксель)

Коэффициент корреляции используется в том случае, когда нужно определить значение зависимости между значениями. Позже эти данные задают в одной таблице которая определяется как матрица корреляции. С помощью программы Microsoft Excel можно сделать расчёт корреляции.

Коэффициент корреляции определяется некоторыми данными. Если уровень показателя составляет от 0 до 0.3, то в таком случае связи нет. Если показатель составляет от 0.3 до 0.5 — это слабая связь. Если показатель доходит до 0.7, то связь средняя. Высокой можно назвать когда показатель достигает отметки 0.7-0.9. Если показатель составляет 1 — это наиболее сильная связь.

Первым делом нужно подключить пакет анализа данных. Без его активации дальнейшие действия нельзя провести. Подключить его можно открыв раздел «Главная» и в меню выбрать «Параметры».

Далее откроется новое окно. В нём нужно выбрать «Надстройки» и в поле управления параметрами выбрать среди элементов списка «Надстройки Excel»
После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». После этого нажимаем «Перейти».

Далее откроется новое окно надстроек. Находим в списке «Пакет анализа» и ставим галочку. После этого подтверждаем действие. И пакет анализа данных будет подключён для документа Excel.

После этих действий можно начать работу. Создана таблица с данными и на её примере сделаем нахождение множественного коэффициента корреляции.
Для начала откроем раздел «Данные» и среди инструментария выбираем «Анализ данных».

Откроется специальное окно с инструментами для анализа. Выбираем «Корреляция» и подтверждаем действие.

Читать еще:  Как отменить группировку в excel

Перед пользователем появится новое окно с параметрами. Как входной интервал задается диапазон значений в таблице. Задать можно как в ручную так и выделив данные, которые будут отображены в специальном поле. Также можно разгруппировать элементы таблицы. Вывод сделаем на текущей странице, а значит в настройках параметра вывода выбираем «Выходной интервал». После этого подтверждаем действие.

Результатом будет отображение корреляционной матрицы с данными с различными значениями. Все взаимосвязи имеют высокий уровень.

Plot корреляционной матрицы в R, как в Примере Excel

Я пытался свести к минимуму использование Excel в пользу R, но все еще застрял, когда дело доходит до отображения простых ячеек данных, как это часто требуется в качестве последнего шага анализа. Следующий пример-это тот, который я хотел бы взломать, так как он поможет мне переключиться на R для этой критической части моего рабочего процесса.

Я хотел бы проиллюстрировать следующую корреляционную матрицу в R :

Клетки должны быть окрашены в зависимости от их ранга (например >95 процентиль полностью зеленый, Поделиться Источник nassimhddd 20 июля 2012 в 15:00

3 Ответа

Ваша корреляционная матрица имеет несколько значений больше 1, что невозможно. Но все равно.

Попробуй вот это

Myaseen208 имеет хорошее начало для ответа. Я подумал, что мне нужно заполнить еще несколько частей: получить градиент цвета в красном/зеленом цвете, который вы указали, перевернуть порядок оси y и очистить несколько других точек (серый фон и легенда).

Теперь попытайтесь получить синие полосы размера внизу.

Что делает размер баров сложнее, так это то, что они представляют собой совершенно другое представление различных данных, чем корреляционная матрица. Поэтому я сначала попытаюсь отделить только эту часть, а затем поработаю над их объединением.

Как и в случае с данными cor, сначала данные размера извлекаются из Матрицы, а затем преобразуются в data.frame, который имеет полезные значения, включая долю от общего числа.

Вызов geom_rect использует некоторые приемы, такие как использование числового представления категориальной (дискретной) переменной для тщательного позиционирования объектов. Каждое число «item» идет от 0.5 под ним до 0.5 над ним. Таким образом, левый край прямоугольника находится 0.5 ниже значения элемента, а правый край- frac справа от него. Использование Inf и -Inf для пределов y означает переход к крайнему значению plot. Это дает

Теперь, чтобы попробовать и сложить их вместе. Шкала x является общей, а шкалу y можно сделать общей (хотя и непересекающейся). Играть с уровнями и ордерами необходимо. Кроме того, я перевернул x и y в оригинале (что хорошо, так как он симметричен). Поскольку наборы данных извлекаются и форматируются немного по-другому, я переименовал их.

Эта окончательная версия не предполагает, что это корреляция 10×10 с дополнительной строкой. Это может быть любое число. cor_matrix должен иметь правильные имена (а «Size» должна быть последней строкой) и item_names должен содержать список элементов. Но это вовсе не обязательно be 10.

Вот подход с использованием базовой графики:

Ссылка на основную публикацию
Adblock
detector