Как сделать ковариацию в экселе

Добавил пользователь Владимир З.
Обновлено: 05.10.2024

Чтобы вычислить ковариацию в Excel, используйте функцию ковариации. Синтаксис функции: COVARIANCE.P (array1, array2), где array1 и array2 - два набора данных, для которых определяется ковариация.

Упорядочить в формате массива означает упорядочить в формате столбца или строки.

Чтобы ввести данные для array1, выделите первую ячейку столбца. В правом нижнем углу выделенной ячейки появится маленький черный квадрат. Поместите на него курсор. Когда курсор превратится в маленький черный крестик, потяните вниз, чтобы выбрать диапазон до последней ячейки. Сделайте то же самое для array2. Нажмите Ввод; цифра ковариации появляется в ячейке вместо формулы.

C_I=XX t .

Выборочная матрица ковариаций C_J между переменными так –

C_J=X t X .

Для вычисления парных ковариаций в Excel используют следующие стандартные функции: COVAR (КОВАР), CORREL (КОРРЕЛ).

COVAR(x, y)

Возвращает выборочную ковариацию между выборками x и y.

CORREL(x, y)

Возвращает выборочный коэффициент корреляции между выборками x и y.

Понятие статистической процедуры оценивания параметров эконометрической модели. Линейные статистические процедуры. Требования к наилучшей статистической процедуре: несмещённость и минимальные дисперсии оценок параметров.

Оценкой вn параметра называют всякую функцию результатов наблюдений над случайной величиной X (иначе — статистику), с помощью которой судят о значениях параметра a.

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

Оценка в n параметра a называется несмещенной, если ее математическое ожидание равно оцениваемому параметру, т. е. М(r) = a.

В противном случае оценка называется смещенной.

Если это равенство не выполняется, то оценка г , полученная по разным выборкам, будет в среднем либо завышать значение a (если М(r) > a , либо занижать его (если М(r)

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

Например, проанализировав данные по ВВП на душу населения и продолжительности жизни в странах мира, мы невооруженным глазом заметим тенденцию:

Корреляция между ВВП и длительностью жизни — 59%

А благодаря расчёту коэффициента корреляции мы можем узнать силу взаимосвязи в конкретном числовом выражении. Это очень удобно и полезно при анализе данных в самых разных областях науки, в том числе в экономике и инвестировании.

Сегодня я расскажу вам подробнее о том, что такое корреляция простыми словами, без сложных формул и терминов. Также я покажу вам, как правильно и легко рассчитать коэффициент корреляции в Excel и как правильно интерпретировать результаты, чтобы использовать их для составления инвестиционного портфеля.

Спасибо за внимание, продолжаем!

Что такое корреляция простыми словами

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

Значение коэффициента может меняться от -1 до +1:

Если значение близко к единице или минус единице — значит два явления так или иначе сильно взаимосвязаны. Впрочем, причины этого не всегда очевидны — явление А может влиять на явление B, может быть наоборот. Нередко бывает, что существует явление C, которое приводит в движение А и В одновременно. В общем, природа корреляции — это уже второй вопрос, которым должны заниматься исследователи.

Околонулевые значения, в свою очередь, говорят об отсутствии какой-либо зависимости между явлениями. Нет конкретного предела, где заканчивается случайность и начинается взаимосвязь, все зависит от предмета исследования и количества данных. Навскидку, обычно при значениях от -0.3 до 0.3 можно говорить о том, что зависимость отсутствует.

При высокой положительной корреляции вслед за графиком А растёт и график B, и чем выше значение, тем слаженнее оба движутся. Для наглядности, вот как выглядит корреляция +1:

Движения графиков полностью повторяют друг друга, причем это как в случае простого добавления, так и с множителем.

При сильной отрицательной корреляции рост графика А приводит к падению графика B и наоборот. Вот так выглядит корреляция -1:

Движения графиков похожи на зеркальные отражения.

Коэффициент корреляции — удобный инструмент для анализа во многих сферах науки и жизни. Его легко рассчитать в Excel и применить, поэтому самая большая сложность в работе с ним — грамотно подобрать данные для расчёта. Основное правило — чем больше данных, тем лучше. Многие взаимосвязи проявляют себя лишь на длинной дистанции.

Также нужно следить за тем, чтобы найденные корреляции не были ложными.

Ложные корреляции

Дело в том, что с помощью коэффициента корреляции можно проверить на взаимосвязь любые явления, которые можно выразить в числовом выражении. То есть, реально любые — например количество свадеб в Нью-Йорке и объем импорта нефти в США из Норвегии:

Корреляция составила 86%! Действительно ли свадьбы влияют на экспорт нефти? Разумеется, нет — подобная зависимость совершенно случайна. Именно так выглядит ловушка ложной корреляции — она может показать взаимосвязь там, где её на самом деле нет.

Корреляция и диверсификация

Другими словами, инвестировать в финансовые инструменты с высокой корреляцией не очень хорошо. Почему? Все просто — похожие активы плохо диверсифицируются. Вот пример портфеля двух активов с корреляцией +1:

Как видите, график портфеля во всех деталях повторяет графики каждого из активов — рост и падение обоих активов синхронны. Диверсификация в теории должна снижать инвестиционные риски за счёт того, что убытки одного актива перекрываются за счёт прибыли другого, но здесь этого не происходит совершенно. Все показатели просто усредняются:

Портфель даёт небольшой выигрыш в снижении рисков — но только по сравнению с более доходным Активом 1. А так, никаких преимуществ по сути нет, нам лучше просто вложить все деньги в Актив 1 и не париться.

А вот пример портфеля двух активов с корреляцией близкой к 0:

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

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

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

Несмотря на то, что каждый из активов обладает определенным риском, портфель получился фактически безрисковым. Какая-то магия, не правда ли? Очень жаль, но на практике такого не бывает, иначе инвестирование было бы слишком лёгким занятием.

Коэффициент корреляции и ПАММ-счета

С расчётом корреляции я как студент экономического ВУЗа познакомился еще на втором курсе. Тем не менее, долгое время недооценивал важность расчёта корреляции именно для подбора ПАММ-портфеля. 2018 год очень четко показал, что ПАММ-счета с похожими стратегиями в случае кризиса могут вести себя очень похоже.

Случилось так, что с середины года отказала не просто одна стратегия управляющего, а большинство торговых систем, завязанных на активные движения валютной пары EUR/USD:

Рынок был для каждого управляющего по-своему неблагоприятным, но присутствие их всех в портфеле привело к большой просадке. Совпадение? Не совсем, ведь это были ПАММ-счета с похожими элементами в торговых стратегиях. Без опыта торговли на рынке Форекс может быть сложно понять, как это работает, но по корреляционной таблице степень взаимосвязи видна и так:

Мы ранее рассматривали корреляцию вплоть до +1, но как видите на практике даже совпадение в районе 20-30% уже говорит о некоторой схожести ПАММ-счетов и, как следствие, результатов торговли.

Чтобы снизить шансы на повторение ситуации, как в 2018 году, я считаю в портфель стоит подбирать ПАММ-счета с низкой взаимной корреляцией. По сути, нам нужны уникальные стратегии с разными подходами и разными валютными парами для торговли. На практике, конечно, сложнее подобрать прибыльные счета с уникальными стратегиями, но если хорошо покопаться в рейтинге ПАММ-счетов, то все возможно. К тому же, низкая взаимная корреляция снижает требования для диверсификации, 5-6 счетов вполне хватит.

Данные о прибыльности ПАММов изначально хранятся в формате накопленной доходности, нам это не подходит. Корреляция стандартных графиков доходности двух прибыльных ПАММ-счетов всегда будет очень высокой, просто потому что они все движутся в правый верхний угол:

У всех счетов положительная корреляция от 0.5 и выше за редким исключением, так мы ничего не поймем. Реальное сходство стратегий ПАММ-счетов можно увидеть только по дневным доходностям. Рассчитать их не особо сложно, если знаете нужные формулы доходности. Если прибыль или убыток двух ПАММ-счетов совпадают по дням и по процентам, высока вероятность что их стратегии имеют общие элементы — и коэффициент корреляции нам это покажет:

Как видите, некоторые корреляции стали нулевыми, а некоторые остались на высоком уровне. Мы теперь видим, какие ПАММ-счета действительно похожи между собой, а какие не имеют ничего общего.

Напоследок давайте разберёмся, что делать и как посчитать корреляцию, если у вас появилась в этом необходимость.

Коэффициент корреляции в Excel и формула расчёта

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

Rxy — коэффициент корреляции;
COVxy — ковариация переменных X и Y;
σX, σY — стандартное отклонение переменных X и Y
X и Y с чертой — среднее значение Х и Y

Кстати, студентам на экзамене до сих пор компьютеров не выдают, хоть калькулятор можно и на том спасибо. Как вы понимаете, занятие все равно трудоёмкое :)

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

Чтобы далеко за примером не ходить, давайте рассчитаем корреляцию двух популярных ПАММ-счетов Lucky Pound и Hohla EUR. Они находятся на площадке компании Alpari, а значит мы можем скачать историю доходности прямо с сайта:

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

Теперь, как я уже писал выше, для ПАММ-счетов (и для многих других инвестиционных инструментов) надо рассчитать дневные доходности:

А дальше все просто — используется встроенная формула коэффицента корреляции в Excel =КОРРЕЛ():

Получили значение 0.12, а значит стратегии ПАММ-счетов практически не имеют ничего общего. Это хорошо для диверсификации, так что можно добавлять обоих в инвестиционный портфель.

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

Мне понравилось работать над этой темой и статья получилась неплохой. Есть еще одна интересная тема по основам инвестирования, которую я хочу подробно обсудить… Будет обидно, если пропустите, так что подписывайтесь на обновления блога по почте или через соцсети.

До встречи и успешных вам инвестиций!

Автор: Александр Дюбченко (добавляйтесь в друзья: VK, FB). В 2014 году закончил КНЭУ по специальности "Экономическая кибернетика". Более 10 лет пишу об инвестировании и финансовой грамотности, параллельно веду Telegram-канал. Также изучаю Excel, SEO, монетизацию текстов. Материалы блога не являются инвестиционной рекомендацией - я рассказываю о своем опыте и могу ошибаться.

Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.

Чтобы упростить ее понимание, разобьем на несколько несложных элементов.

Между переменными определяется сильная прямая связь.

Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.

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

Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.

Изобразим наглядно корреляционные отношения с помощью графиков.

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

Если связь имеется, то влечет ли увеличение одного параметра повышение (положительная корреляция) либо уменьшение (отрицательная) другого. Корреляционный анализ помогает аналитику определиться, можно ли по величине одного показателя предсказать возможное значение другого.

Коэффициент корреляции обозначается r. Варьируется в пределах от 1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.

Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.

Для нахождения парных коэффициентов применяется функция КОРРЕЛ.

Задача: Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.

Ставим курсор в любую ячейку и нажимаем кнопку fx.

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

На практике эти две методики часто применяются вместе.

Теперь стали видны и данные регрессионного анализа.

Пример 1. В таблице Excel содержатся данные о курсе доллара и средней зарплате сотрудников фирмы на протяжении нескольких лет. Определить взаимосвязь между курсом валюты и средней зарплатой.

B3:B13 – диапазон ячеек, в которых хранятся данные о среднем курсе доллара;
C3:C13 – диапазон ячеек со значениями средней зарплаты.

Полученный результат близок к 1 и свидетельствует о сильной прямой взаимосвязи между исследуемыми величинами. Однако прямо пропорциональной зависимости между ними нет, то есть на увеличение средней зарплаты оказывали влияние и прочие факторы.

Пример 2. Два сильных кандидата на руководящий пост воспользовались услугами двух различных пиар-агентств для запуска предвыборной компании, которая длилась 15 дней. Ежедневно проводился соцопрос независимыми исследователями, которые определяли процент поддержки одного и второго кандидата. Респонденты могли отдавать предпочтение первому, второму кандидату или выступать против обоих. Определить, насколько влияла каждая предвыборная кампания на степень поддержки кандидатов, какая из них оказалась более эффективной?

A3:A17 – массив ячеек, содержащий номера дней предвыборной кампании;
B3:B17 и C3:C17 – диапазон ячеек, содержащие данные о проценте поддержки первого и второго кандидатов соответственно.

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

Однако затем уровень поддержки стал снижаться, и к 15-му дню упал ниже начального значения. Отрицательное значение коэффициента корреляции свидетельствует о негативном эффекте кампании. Однако на события могли оказывать влияние различные факторы, например, опубликованные компрометирующие материалы.

массив1 – обязательный аргумент, содержащий диапазон ячеек или массив данных, которые характеризуют изменения свойства какого-либо объекта.
массив2 – обязательный аргумент (диапазон ячеек либо массив), элементы которого характеризуют изменение свойств второго объекта.

Примечание 2: Коэффициент корреляции представляет собой количественную характеристику степени взаимосвязи между двумя свойствами объектов. Этот коэффициент может принимать значения из диапазона от -1 до 1, при этом:

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

Примечание 3: Для понимания смысла коэффициента корреляции можно привести два простых примера:

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

Регрессионный анализ в Excel

Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.

Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.

линейной (у = а bx);
параболической (y = a bx cx 2 );
экспоненциальной (y = a * exp(bx));
степенной (y = a*x^b);
гиперболической (y = b/x a);
логарифмической (y = b * 1n(x) a);
показательной (y = a * b^x).

Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

У = а0 а1х1 … акхк.

Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.

В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).

Активируем мощный аналитический инструмент:

Теперь займемся непосредственно регрессионным анализом.

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Читайте также: