Calculating Historical Volatility
Tutorials InvestingThe inspiration for my S&P500 Volatility Timing model came from rereading portions of Mandelbrot’s book, The (Mis)Behavior of Markets, and trolling around the Internet for Nassim Taleb’s research work on risk. I think both guys push the envelope on truly understanding unseen risk and those pesky financial asteroids. Since my model is currently being developed, I thought it would be worth my while to truly learn and understand how historical volatility (HV) is calculated. I first searched the Internet for any free data downloads of HV but came across several pay for data download sites.
One of them, iVolatility.com, seemed to have comprehensive data but it was expensive. One year’s worth of HV for one asset price would’ve cost me $5! So what does any engineering type person do in the face of expensive solutions? He (or she) build’s their own cheaper solution. I decided to calculate HV on my own after reading about it on wikipedia. Now, I’m submitting this analysis for peer review as I’m treading in unfamiliar waters. Please feel free to correct me if my computations or understanding of the material is wrong.
Wikipedia defines HV as:
The annualized volatility Ïƒ is the standard deviation Ïƒ of the instrument’s logarithmic returns in a year.
Furthermore,
The generalized volatility Ïƒ_{T} for time horizon T in years is expressed as:
.
Note: There’s a flaw in the Wikipedia’s formula logic after the generalized volatility formula above as pointed out by C++ Trader (thanks for the catch). Please see the related links below for more information on the correct calculation of HV.
Note that the formula used to annualize returns is not deterministic, but is an extrapolation valid for a random walk process whose steps have finite variance. So the first step is to calculate the S&P500’s logarithmic returns for a year.
I’ll be using the weekly time series and I’ll analyze it in a handy Excel spreadsheet here: HV Example.xls
Once again I’ll turn to wikipedia for an explanation of logarithmic returns:
Academics use in their research natural log return called logarithmic return or continuously compounded return. The continuously compounded return is asymmetric thus clearly indicating that positive and negative percent returns are not equal. A 10% return results in 9.53% continuously compounded return while a 10% results in 10.53%. This clearly indicates that the investment will result in a dollar amount loss corresponding to the difference between the absolute values of the two numbers: 1% (this is an approximate equality).

V_{i} is the initial investment value

V_{f} is the final investment value

ROI_{Log} > 0 is profit

ROI_{Log} < 0 is a loss

Doubling occurs when:

Total loss occurs when:
This should be straightforward and I will calculate the weekly ROI for the S&P500. Why? Well I’m interested in calculating weekly HV so my Vi will be the week(1)’s closing price and Vf will be week(2)’s closing price. For the next iteration Vi will be the week(2)’s closing price and Vf will be week(3)’s closing price and so forth.
Next I created an Excel Macro that would calculate the natural log and simultaneously calculate the HV for 10, 20, and 30 days using the standard deviation of the daily logarithmic returns multiplied by 252 (see related links below).
There you have it, your very own weekly HV! Feel free to download the Excel macro and play with it. By all means, please critique my analysis and let me know if my logic is flawed! The more I learn about this, the more my ATS takes shape!
Update: The Excel Macro matches the output from iVolatility.com for the 10, 20, and 30 day HV’s. Check!
Calculate Historical Volatility in Python
I never use Excel anymore, instead I use Python to calculate a rolling volatility now because it’s so easy.
The simple code is just this:
df.pct_change().rolling(window_size).std()*(252**0.5)
Where window_size
is the period of time you’re looking at.
So how does that look for a 5 day volatility calculation in practice?
import pandas as pd
import yfinance as yf
def get_ticker_data(tckr):
tmp = yf.Ticker(tckr)
df = tmp.history(period="240mo")
return df
df = get_ticker_data('XOM')
# use a window of 5 days
window_size = 5
df['5 Day Volatility'] = df['Close'].pct_change().rolling(window_size).std()*(252**0.5)
df.tail()```
Here we import pandas and yfinance to build a dataframe and download price data for XOM. The we calculate the rolling percent change and standard deviation over 252 trading days. We create a new column called 5 Day Volatility in the dataframe and run the code.
The end result when you do a `df.tail()` on it is the following:
## Related:
 [Historical Volatility](/_wpcontent/uploads/2007/05/volatility_calculation.pdf)
 [Calculating Volatility](https://www.optiontradingtips.com/options101/volatility.html)
 [Using Historical Volatility To Gauge Future Risk](https://www.investopedia.com/articles/06/historicalvolatility.asp)
 [An Introduction to Volatility and how it can be Calculated in Excel](https://www.quantonline.co.za/Articles/article_volatility.htm)
 [Stochastic Process  Advanced Mathematical Finance](https://www.math.unl.edu/%7Esdunbar1/Teaching/MathematicalFinance/Lessons/BlackScholes/ImpliedVolatility/impliedvolatility.shtml)