Calculating Historical Volatility
Download the zipped version Historical Volatility (35 KB)
Download the Excel file Historical Volatility.xls (150 KB)
The above spreadsheet will download historical stock prices from the web and calculate the historical standard deviation for the range of values that you specify.
You can download stock data as far back as Yahoo!'s database allows and choose your own historical lookback period. And it's free.
Volatility is the most crucial of all option trading concepts. Volatility indicators provide traders with an estimate of how much movement a stock can be expected to make over a given time frame. This is crucial in determining whether an option is likely to expire in or out of the money by the expiration date.
Understanding volatility also helps traders understand whether an option is cheap/expensive relative to the historical facts of the underlying instrument.
There are two types of volatility that we will be looking at: Implied Volatility and Historical Volatility.
Volatility Definition
Historical Volatility is a statistical calculation that tells option traders how rapid price movements have been over a given time frame. The most common method of calculating historical volatility is called the Standard Deviation.
Standard Deviation measures the dispersion of a set of data points from its average. The more disperse (spread out) the data is, the higher the deviation. This deviation is referred by traders as volatility.
Don't get too caught up in trying to understand the how's and whys of the standard deviation, just accept that all traders use this method for determining historical volatility. However, if you want more of an explanation you can refer to Appendix C of Option Volatility & Pricing
Or, you can download the Historical Volatility.xls spreadsheet for an example of how to calculate historical volatility.
Assets that have large and frequent price movements are said to be volatile or said to be of high volatility. Consequently, assets whose price movements are slow and predictable are said be low volatile instruments. Take a look at the following examples of high and low volatile assets.
Take a look at the examples below of a highly volatile stock and a low volatile stock;
High Volatility
Low Volatility
Why is volatility so important to option traders? Because volatility is a measure of the possible price changes of the asset in the future. Assets that have high volatility can be expected to have large price changes in the future. As a result, options that are based on assets with high volatility can be expected to have higher prices.
The higher the volatility, the more likely it is that the underlying asset will trade higher (or lower) than the exercise price by the expiry date.
Implied Volatility
Implied volatility is the markets view of where volatility will be in the future. To determine an option's implied volatility, the trader must use a pricing model.
But for now, take a look at the following illustration;
Historical Volatility tells us how volatile as asset has been in the past. Implied Volatility is the markets view on how volatile as asset will be in the future.
We can tell how high/low implied volatility is by comparing the market price of an option to the options theoretical fair value. This is why we need to use an option pricing model - to determine the fair value of an option and hence know if the market price for the option is over/under valued.
When the market price of an option is higher than it's theoretical value (based off past information) it is considered expensive and so to if the market price of the option is less than the theoretical price, it is considered cheap.
The Volcone Analyzer
Another way to look at implied volatility is to compare the current level of implied volatility to the average level of implied volatility for the same option.
It's a sound approach, however, building your own database of implied volatility data for every US stock requires a huge investment of time and resources. If you're interested in this idea though, then I suggest you take a look at the Volcone by Options University. Better still, if you've got a few minutes, watch their training video below:
Have they changed the code?
Volatility Days certainly is used in the calculation...it's written into the Macro...not in the formula. When you hit the "Extract Data" button the array for the volatility calculation changes according to what you've entered into the "Volatility Days" cell.
Having said that...I do appreciate your formula below. The reason I included it in the Macro was because I didn't know how to do it with a formula ;-) But now I know, thanks a lot...very useful!
http://www.neuralmarkettrends.com/2007/05/29/calculating-histori cal-volatility/
I used and applied the online formula found on googling historical volatility formula.
I was trying to compare my result with yours.
thanks for the great work. I like your chart.
I tried other place like Ivolatility.com
but it is a joke nowaday that they force people to sign up just to get a stock quote. and they try to charge you money for data download for historical volatility where people can use yours spreadsheet or programmers like us that can generate it from google or yahoo finiance api using very simple and basic programming calculation.
Great spreadsheet
Happy Trading!
I have a macbook and vb macros don't work on mac office. Is there any other alternative? An online version or something?
Thanks...
http://www.willowsolutions.com/tips/tips_2002_08.shtml
It does the same thing.
That's a good question and to be honest I've never thought about it before ;-). All the stats books I've looked at always used STDEV only...perhaps because the data used is only a sample dataset. I believe STDEVP is used when the population data is 100% complete.
What are your thoughts? Do you think STDEVP is a better alternative?
http://www.willowsolutions.com/tips/tips_2002_08.shtml