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

Обновлено: 05.07.2024

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

Ключевые выводы

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

Построение модели ценообразования

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

Прежде всего, модель требует некоторых предварительных гипотез. Мы предполагаем, например, что дневная доходность или «r (t)» этих активов обычно распределяется со средним значением «(μ)» и сигмой стандартного отклонения «(σ)». Это стандартные предположения, которые мы будем использовать здесь, хотя есть много других, которые можно использовать для повышения точности модели.

р(т)знак равноS(т)-S(т-1)S(т-1)знак равноμδт+σϕδтжчере:δтзнак равно1 дайзнак равно1365 оф а йеарμзнак равномеанϕ≅N(0,1)σзнак равноппулягедоболтяляту \ begin & r (t) = \ frac = \ mu \ delta t + \ sigma \ phi \ sqrt \\ & \ textbf \\ & \ delta t = 1 \ \ text = \ frac \ \ text \\ & \ mu = \ text < mean>\\ & \ phi \ cong N (0, 1) \\ & \ sigma = \ text \\ \ end Взаимодействие с другими людьмиг(т)знак равноS(t-1)

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

Чтобы вычислить μ, то есть среднее значение дневной доходности, мы берем n последовательных прошлых цен закрытия и применяем, что является средним из суммы n прошлых цен:

Вычисление исторической волатильности в Excel

В этом примере мы будем использовать функцию Excel «= НОРМСТОБР (СЛЧИС ())». На основе нормального распределения эта функция вычисляет случайное число со средним значением, равным нулю, и стандартным отклонением, равным единице. Чтобы вычислить μ, просто усредните урожайность с помощью функции Ln (.): Логнормального распределения.

В ячейке F4 введите «Ln (P (t) / P (t-1)».

В ячейке H20 введите «= СРЕДНЕЕ (G4: G17).

В ячейке H22 введите «= 365 * H20», чтобы вычислить годовую дисперсию.

В ячейке H22 введите «= КОРЕНЬ (H21)», чтобы вычислить среднегодовое стандартное отклонение.

Итак, теперь у нас есть «тренд» прошлой дневной доходности и стандартное отклонение ( волатильность ). Мы можем применить нашу формулу, указанную выше:

В ячейке K2 введите «0».
В ячейке L2 введите «95».
В ячейке K3 введите «1».
В ячейке L3 введите «= L2 * (1 + $ F $ 19 * (1/29) + $ H $ 22 * SQRT (1/29) * NORMSINV (RAND ()))».

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

Эта модель позволяет нам найти имитацию активов до 29 заданных дат с той же волатильностью, что и предыдущие 15 выбранных нами цен, и с аналогичной тенденцией.

Наконец, мы можем нажать «F9», чтобы начать другое моделирование, поскольку у нас есть функция rand как часть модели.

Я получил вопрос от читателя, который спросил: "Можно ли рассчитать волатильность в Excel?" Ответ: Да, можно. Однако, есть несколько вещей, о которых вы должны знать. Не особо углубляясь в детали, скажу лишь, что есть много способов рассчитать волатильность. Два из наиболее распространенных способа касаются подразумеваемой и исторической (или статистической) волатильности. Историческая довольно-таки проста для расчета в Excel, и я покажу вам, как это делается в этом посте. Расчет подразумеваемой на порядок сложнее, и хотя вы можете посчитать её в Excel, но эту тему оставим на следующий раз, потому как она касается опционов, а это не простая тема.

Сегодня же давайте просто посмотрим, как рассчитать простую историческую волатильность в Excel.

Данные в столбцах open, high, low, adj close, volume нам не нужны. Можете или скрыть столбцы или удалить их вовсе. Т.е. нас интересуют только колонки Date и Close. Немного навёл красоты:

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

А затем это проделываем для каждой строки и получается вот так:

И преобразуем в процентный вид:

3. Теперь нам нужно посчитать стандартное отклонение. Если коротко, то это то, насколько что-то отклоняется от нормы. Ну примерно, если говорить простыми словами. Т.е. насколько сегодняшняя цена отличается от цен за какой-то период. Чтобы это посчитать, нужно воспользоваться соответсвующей формулой в Excel, называющейся STDEV или СТАНДОТКЛОН.В.

Как мы видим, здесь расчёт выбран за 10 дней, но это сделано только для иллюстрации. Вы можете выбрать любой период.

4. В заключении, нам нужно сделать одно — узнать, какова же историческая волатильность, а она — это по сути ежегодное стандартное отклонение. Для этого, мы возьмём волатильность за неделю, т.е. 5 дней, когда открыты рынки. Затем, умножим на корень из 52. Почему 52? Потому что в году 52 торговые недели. Таким образом получается:

Сегодня же давайте просто посмотрим, как рассчитать простую историческую волатильность в Excel.

А затем это проделываем для каждой строки и получается вот так:

И преобразуем в процентный вид:

В начале 2017 года я сделал расчет, в котором сравнил различные способы расчета HV.
Свои выводы я представил на завтраке инвестора у Алины Ананьевой.
Были рассмотрены восемь активов на различных рынках и период с 2010 по 2016 гг.
Методы сравнивались по критерию наименьшей ошибки прогноза будущей волатильности.
Лучшими подходами по моему мнению оказались методы господина Твардовского, господина Механизатора, экспоненциальный способ, а также усреднение этих трех прогнозов. Сейчас мне стало любопытно, насколько я оказался прав тогда в своих выводах.

Для теста я взял часовые свечки различных активов с января 2017г. по январь 2021г.
Основные выводы представлены ниже, объяснения и таблички следуют за ними.

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

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

3. Экспоненциальный метод также доказал свою полезность, заняв третье место в общем рейтинге. Для американских акций прогнозы в рамках данного подхода оказались самыми точными.

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

5. Для часового таймфрейма популярные подходы Parkinson, Yang-Zhang, Rogers-Satchell и Garman-Klass в большинстве случаев оказались хуже даже базового метода расчета исторической волатильности. Впрочем, для дневного таймфрейма показатели этих методов (в частности, подхода Garman-Klass) не такие уж и плохие.

Подробнее о расчетах:

Для периода с января 2017 по январь 2021 посчитаны ошибки прогноза реализованной волатильности.
Ошибки представляет собой сумму квадратов разностей ошибок для каждого прогноза, нормированные на множитель масштаба.
Прогноз делается на будущий период из 240 часовых свечек для всех рынков кроме commodities, для commodities прогноз делается на 480 свечек вперед.
Окно расчета волатильности в прошлом меняется, оно равно 240, 480, 720 и 960 часовых свечек. Для каждого метода в рейтинге указана длина окна в прошлое.
Из commodities взяты: Brent, Gold, Copper.
Индексы включают: Kospi, Shanghai, S&P 500, CAC 40, FTSE 100, РТС.
Акции: SBER, GAZR, ROSN, GMKN, NVTK, AAPL, FB, MSFT, IBM, INTC, T, Google1.
Валюты: Si, ED.
IV в качестве прогнозной меры в данном тесте не участвует.

Взяты следующие подходы:

HV — простая HV
RV0 — HV без дрифта,
Exp — экспоненциальная волатильность,
Park — Parkinson,
RS - Rogers-Satchell,
GK - Garman-Klass,
GK-YZ - Garman-Klass с расширением Yang-Zhang,
YZ — Yang-Zhang,
Tvard — подход г-на Твардовского с подобранным мной коэффициентом 0.7: 0.7 EMA(H-L,n)/EMA(C,n)
Primus - подход г-на Механизатора с моим множителем 0.6, 0.6 Σ Ln(Max(H(i);C(i-1))/Min(L(i);C(i-1))) / (n-1)
Range — еще один подход г-на Механизатора с моим множителем 0.61: 0.61 Σ Ln(H(i)/C(i)) / (n-1)
AV — простое среднее прогнозов Tvard, Exp и Primus,

В конце каждого обозначения подхода указан период его расчета в прошлое. Например, HV 240 означает стандартное отклонение доходности на периоде из 240 часов.

Итоговые таблички

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

Я также сделал расчеты для дневного таймфрейма для прогноза вперед на 20 рабочих дней, ниже табличка с результатами. Справа от аббревиатуры метода указано, как и раньше, окно в прошлое. Например, Tvard 10 означает прогноз вперед на 20 дней, рассчитанный на 10 днях в прошлом.

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