Модели прогнозирования с функцией автообновления

Тема этой рассылки - настройка модели прогнозирования (например, ARMA, GARCH, регрессионной) таким образом, чтобы вы (или кто-то другой) могли легко поддерживать ее по мере появления новых точек данных и автоматического обновления прогнозов.

Для демонстрации мы определили GARCH-модель для месячной доходности S&P 500 ETF, откалибровали значения параметров и построили терминную структуру прогноза волатильности на следующие 36 месяцев. Затем мы ввели новую точку данных и наблюдали, как модель переносит дату начала прогноза и обновляет прогнозные значения.

Почему это должно вас волновать?

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

Готовы, приступайте!

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

В этом учебном пособии мы используем ежемесячные логарифмические показатели доходности S&P 500 ETF (SPY) за период с января 2010 года по ноябрь 2019 года:

Сначала нужно добавить к текущему набору данных заполнители. Для этого мы перенесем даты (например, декабрь 2019 года, январь 2019 года) и введем в качестве их значений "#N/A".

Моделирование

Мы выбрали GARCH(2,2) с инновациями студента, чтобы отразить изменяющуюся во времени динамику месячной волатильности для этого набора данных. Чтобы определить модель, найдите пустую ячейку на рабочем листе, а затем щелкните на значке "GARCH" на панели инструментов "NumXL", после чего откроется мастер GARCH.

В разделе "Входные данные" выберите диапазон ячеек с месячными доходами, включая ранее добавленные вами заполнители. Не волнуйтесь, функции NumXL отбросят наблюдения со значениями "#N/A" на обоих концах диапазона входных ячеек.

Выберите ARCH и GARCH в разделе "Характеристики модели" и установите порядок на 2. Нажмите на "t-распределение Стьюдента" в разделе "Инновации". Теперь нажмите на кнопку "OK", чтобы подтвердить выбор.

Мастер GARCH генерирует таблицу модели и инициализирует значения ее параметров в допустимые, но неоптимальные значения. Чтобы найти оптимальный набор значений для параметров GARCH, выделите ячейку заголовка таблицы модели и нажмите на значок "Калибровка" на панели инструментов "NumXL". На экране появится программа Microsoft Solver со всеми заданными значениями. Нажмите кнопку Решить

Решатель Microsoft ищет оптимальное решение для процесса GARCH.

Исследование данных

Ранее мы упоминали, что функции NumXL отбрасывают все наблюдения в диапазоне входных ячеек со значением, равным "#N/A". Как узнать, где заканчивается набор данных?

Чтобы ответить на поставленный выше вопрос, необходимо сначала вычислить размер набора данных с помощью двух функций: RMNA(.) и COUNT(.). RMNA(.) отбрасывает точки данных с "#N/A" и возвращает массив всех не пропущенных наблюдений, а COUNT(.) возвращает размер этого массива.

Вы также можете рассчитать дату окончания набора данных, используя встроенную в Excel функцию ИНДЕКС(.) и указанный выше размер набора данных.

Таким образом, у вас есть набор данных из 119 не пропущенных значений, а последнее наблюдение выпало на ноябрь 2019 года.

Прогнозирование

В этом учебном пособии вам нужно построить прогноз месячной волатильности на следующие 36 месяцев. Для этого выделите строку заголовка таблицы модели и нажмите на значок "Прогноз" на панели инструментов NumXL.

В разделе "Последние наблюдения" выберите диапазон ячеек в столбце "B", включая наблюдения с отсутствующими значениями (т.е. #N/A).

Для волатильности оставьте это значение пустым, и NumXL будет использовать волатильность внутри выборки, рассчитанную самой моделью GARCH. Нажмите кнопку "ОК".

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

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

Чтобы вычислить соответствующую дату для каждого шага, мы используем функции NxEDATE(.) и CONCAT(.), как показано ниже:

Используя дату окончания набора данных, функция NxEDATE(.) возвращает данные, которые выпадают после N месяцев, где N - количество шагов в таблице прогнозов.

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

Вот и все! Давайте рассмотрим таблицу прогнозов и кривую термической структуры месячной волатильности.

Модель GARCH показывает низкую волатильность относительно исторического уровня (3,8%), но прогнозирует устойчивый рост волатильности в ближайший год.

Добавление точек данных

Давайте рассмотрим, что произойдет, если мы получим месячную доходность декабря 2019 года, скажем, -0,6%.

Шаг 1: Введите новое значение в набор данных.

Шаг 2: Посетите сайт

Размер входного набора данных увеличился до 120, а дата окончания - декабрь 2019 года.

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

Шаг 3: Расширенный

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

В качестве аргумента предположим, что вы хотите перекалибровать модель, но сохранить порядки ARCH/GARCH (т.е. P и Q) и распределение инноваций (например, student t) прежними.

Как и ранее, найдите и выделите строку заголовка таблицы модели GARCH и нажмите на значок "Калибровка" на панели инструментов NumXL.

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

Нажмите на кнопку "Solver", чтобы начать процесс оптимизации.

После завершения работы Solver отобразит диалог "Найти решение". Нажмите кнопку OK, и он скопирует значения новых параметров в таблицу модели.

Чтобы оценить влияние перекалибровки на ваш прогноз, посмотрите вниз на ваши прогнозные значения в таблице (и на графике).

Заключение

В этом руководстве мы рассмотрели два шага по настройке автообновления моделей:

• Добавление точек данных-заместителей в конец входного набора данных,
• Выберите набор данных с точками-заместителями в модели и прогнозе.

С течением времени и появлением новых наблюдений мы вносим их фактические значения в набор данных (вместо #N/A), а Excel обновляет расчеты во всех ссылающихся ячейках (например, в таблице прогнозов).