Построение корреляционной матрицы

Тема этого выпуска была навеяна вопросом поддержки: как эффективно построить корреляционную матрицу для доходности N активов?

NumXL имеет множество функций для вычисления кросс-корреляции между двумя временными рядами, но как это сделать, например, для 20 переменных без промежуточных вычислений? Не беспокойтесь, в Excel есть несколько функций: MATCH(.) и INDEX(.), которые мы можем использовать в наших формулах, чтобы легко и быстро построить матрицу корреляции. Именно это мы и рассмотрим в этом выпуске.

В общем, мы хотим преобразовать следующую таблицу входных данных:

В эту корреляционную матрицу:

Интересуетесь? Давайте приступим!

Подготовка данных

В этом учебном пособии мы будем использовать логарифмические недельные доходности 16 ETF за период с 28 сентября 2015 года по 17 февраля 2020 года (как раз перед эпидемией волатильности на рынке COVID-19). Мы выбрали 16 ETF, чтобы охватить рынок акций США (например, S&P 500, Dow Jones Industrial Average, Russell 1000/2000/3000 и т. д.), рынок энергетики США (нефть и природный газ), металлы (золото, серебро и промышленные цветные металлы), и, наконец, ультракороткие долговые инструменты (наличные).

Затем мы скопировали данные о еженедельных доходах в отдельный рабочий лист, каждый в отдельную колонку, но выровняли все активы с помощью общего поля даты (колонка A).

Теперь давайте определим имя для диапазона ячеек таблицы данных:

• Выберите или переключитесь на вкладку Формулы
• Выберите диапазон ячеек ввода, а затем нажмите "Определить имя".

• Появится диалоговое окно "Определить имя" с несколькими предварительно заполненными полями (например, "Ссылаться на" содержит диапазон ячеек таблицы данных, а область видимости установлена на "Рабочая книга").
• Измените название в диалоговом окне "Определить название" на что-то осмысленное, поэтому мы выбрали "RETURNS".

• Нажмите OK.

Давайте создадим еще одно определенное имя (скажем, SYMBOLS") для тикеров символов активов (строка 2). Повторите те же шаги, что и выше, но для верхней строки с символами тикеров.

Теперь, если вы посмотрите на диспетчер имен, у вас должно получиться следующее:

Корреляционная матрица

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

Подсказка: Вы можете использовать функцию "транспонировать" в "Опциях вставки", чтобы вставить скопированную строку (например, строку символов тикера) в столбец.

Ссылки на временные ряды

В корреляционной таблице каждая ячейка представляет собой кросс-корреляцию между доходностями двух активов: столбца и строки. Например, на рисунке ниже серая ячейка - это корреляция между IWB и DIA.

Теперь нам нужно использовать символ тикера для ссылки на временной ряд в таблице данных:

Шаг 1: Преобразуйте символ тикера в числовое смещение. Используйте функцию MATCH(.) для оценки индекса тикера в определенном имени SYMBOLS. Мы делаем это как для заголовка столбца, так и для заголовка строки. Например:

Шаг 2: Ссылка на соответствующий временной ряд

Используйте функцию INDEX(.) для ссылки на один столбец, но на все строки в определенном имени RETURNS. Мы делаем это как для заголовка столбца, так и для заголовка строки, но с использованием его смещения.

Шаг 3: Объедините функции MATCH(.) и INDEX(.)

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

Шаг 5: Скопируйте приведенную выше формулу в другие ячейки (строки и столбцы), чтобы вычислить всю корреляционную матрицу.

Заключение

В этом уроке мы использовали встроенные функции excel: MATCH(.) и INDEX(.), и построили корреляционную матрицу без промежуточных вычислений, всего за несколько простых шагов. Кроме того, мы использовали функцию "определенные имена" для инкапсуляции набора данных и упростили не только формулы, но и работу с исходными данными: добавление наблюдений и/или активов.