Historic Volatility Calculator
Getting a grip on historic volatility is of critical importance for options traders. Relying on implied volatility alone is risky. Implied volatility simply tells you how options are currently priced, but not whether they are realistically priced. Historic volatility, on the other hand, can help you understand whether or not options are currently cheap or expensive.
The Historic Volatility Calculator will calculate and graph historic volatility using historical price data retrieved from Yahoo Finance, Quandl or from a CSV text file. Click picture below to enlarge.
Yahoo Finance: Historical prices for many stock exchanges around the world (US, Australia, London, Germany, Singapore and many more) are held on Yahoo and the Historic Volatility Calculator will access these data without the need to browse the web or download files. One mouse click will retrieve all data, calculate volatility, and produce volatility and price graphs. See Yahoo exchange list for exchanges covered by Yahoo.
Quandl: The Quandl service is a
repository of free historical data covering a large number
of subject areas: futures (including continuous futures
contracts), currencies (including Bitcoin), interest rates (including constant maturity time
series data), commodities, world equity indices and more. Like for
Yahoo, the Historic Volatility Calculator will retrieve data from Quandl
without the need to browse the Quandl web site or download files.
The calculator is tightly integrated with "Quandl favourites" which can
optionally be set up on the Quandl web site to simplify dataset
CSV Files: For assets and markets not held on Yahoo Finance or Quandl, the Historic Volatility Calculator will import data from a CSV file. The columns containing the data, the date formats and and the CSV filename can all be specified by the user.
Volatility is calculated using the functions contained in the Hoadley Finance Add-in for Excel add-in, using both equally-weighted and weighted (EWMA, GARCH) volatility models.
Asset codes, date ranges, sampling frequencies (daily, weekly, monthly) are specified by the user. Historical prices are then automatically retrieved from the web and volatility calculated and graphed.
Note that no guarantee can
be given that stock or other asset price data will be available from the Yahoo
site or that Yahoo will not change the format of their data without notice in the
The Historic Volatility Calculator contains a forecasting module which estimates and graphs forward volatilities using the GARCH(1,1) model. It provides a volatility term structures (to answer the question: "what volatility should I use if pricing an option with a term of 20 weeks"). Click picture below to enlarge.
A volatility cone analysis is also produced to provide another approach to the forecasting of future volatility.
Volatility cones can help
determine whether current implied volatility (eg from the
Implied Volatility Calculator) is high or
low compared with historical volatility measured over the same periods. The
Implied Volatility calculator will combine both historical (statistical) and
implied volatilities on the one volatility cone chart.
It is a well known phenomenon that volatilities implied by market prices are a function of an option's strike price and time to maturity. This is called the volatility smile, or volatility skew, due to the fact that often deeply out of the money or deeply in the money options have significantly higher implied volatilities than options at the money.
Taking the analysis of the historic data in terms of skewness and excess kurtosis as the starting point, the volatility calculator estimates and graphs the volatility smile for each asset and associated option prices. The risk-neutral probability distribution implied by the smile is also graphed.
Click the picture above enlarge.
Assumptions regarding kurtosis, skewness, term, volatility and other factors can then be dynamically varied, using slider-bar controls, to see the implications for option prices and the volatility smile. This technique can also be used to calibrate the volatility smile (and hence skewness, kurtosis and volatility) with observed market prices.
See FAQs for more information and references to some of the research behind the approach adopted here for volatility smile modelling.
See the Historic
Volatility Calculator demo for an example of volatility smile modelling.
The volatility calculator automatically produces a sheet containing advanced GARCH diagnostic information.
This include an analysis of the extent to which price samples diverge from a log-normal distribution (as measured by skewness and excess kurtosis of returns), the strength of volatility clustering (measured by autocorrelation in squared returns), the confidence intervals of all GARCH parameters (as measured by their standard errors and associated t-stats), and the confidence interval bounds for the one step ahead volatility forecast (as measured by the standard deviation of the variance).
The diagnostics sheet
automatically highlights (using color and narrative) any issues with the data
based on these analyses, and this can greatly help the user make an informed
judgment about the reliability of future volatility forecasts
Microsoft Excel 2003 or later (32-bit and 64-bit) running under Microsoft Windows XP or later.
The Hoadley Finance Add-in for Excel must be installed before using the Historic Volatility Calculator.
The Excel application is
not password protected. It can therefore be used as a template for developing
other applications using the options Excel add-in.
Additional information on the historic volatility calculator and approach taken to volatility modelling:
The Historic Volatility Calculator is included free of charge with the full version of the Finance Add-in. It is not available with the trial version.