Historical Volatility Calculator
When you work with options, you often need to quickly calculate historical volatility of a security.
Unfortunately, most of the common tools, including highly priced trading software, have serious limitations:
- You can't calculate zero mean (non-centered) HV, which many volatility traders actually use.
- There is no documentation and you don't know which exact formula the particular software is using.
- Changing the HV period takes a lot of clicking or isn't possible at all.
I have created this Excel calculator to address these limitations, originally for my own needs.
- Centered and non-centered HV calculation (+1 more bonus method)
- Any period length
- Use your own data or download historical data automatically from Yahoo Finance
- Very simple navigation – you can start using it immediately
- PDF guide explaining the calculations, the 3 methods, and HV interpretation
- Works in all versions of Excel
Screenshots and Features
This is the main calculation sheet:
You need historical data to calculate historical volatility. With the calculator you have 2 options:
- You can use your own data if you have it.
- If you don't have market data, the calculator can automatically download data from Yahoo Finance for a symbol you specify.
This is how you select the data source:
When using your own data, you enter it in the sheet "Data". You simply paste your data there and push a button:
When using Yahoo Finance data, all you need to enter is the symbol (cell B11) and how long history you want (cell B13). Then you click on the button below and the download will start:
Clicking the button will start a macro that will download the data...
... then it will check the data for errors, sort it, import it to the calculation sheet, and build the formulas for historical volatility calculation. You will get a message with the result:
You can see historical volatility charts in the sheet "Chart". You can display up to 3 different indicators at the same time (with different lookback period lengths or historical volatility calculation methods). In the combos in the top right corner you select which of the indicators you want to display:
In the calculation sheet you can also see the exact historical volatility values for any date. Values for the last row are also displayed at the top in cells H17/I17/J17:
Historical Volatility PDF Guide
The PDF guide provides instructions and notes for using the calculator and also explains historical volatility calculation and interpretation for those not familiar with it. You can find an overview of the contents below:
Excel Versions and Macros
The default version of the calculator uses Excel macros to make it convenient and easy to use. If you can't or don't want to use macros, there is also a macro-free version available.
All Excel Versions:
- HV_Calculator.xlsm = the default version (all features, easiest to use)
- HV_Macro-Free.xlsx = macro-free version (if you can't use macros)
- HV_for_Excel_97-2007.xls = Excel 97-2007 version
- HV_for_Excel_97-2007_Macro-Free.xls = macro-free Excel 97-2007 version
Every user gets access to all versions plus the PDF guide.