Software environment

Additional information




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. 

View: View Historic Volatility Calculator demo.

Historic volatility calculation & charting

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.

Hoadley Historic Volatility Calculator

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 selection

Visit the Quandl web site for more information.

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 equally-weighted models (close-close and GKYZ open-high-low-close) 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 future.

Volatility forecasting & term structures

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.

Hoadley Historic Volatility Calculator Volatility Forecast

Volatility cones

A volatility cone analysis is also produced to provide another approach to the forecasting of future volatility. 

Volatility Cone

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.  More details.

Volatility smile/skew modelling

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.

Volatility Smile Analysis

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.

GARCH "Diagnotistics"

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

Software Environment

This application requires Microsoft Excel (32-bit or 64-bit) running under Microsoft Windows.  For detailed systems requirements, including supported versions of Windows and Excel see systems requirements.

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

Additional information on the historic volatility calculator and approach taken to volatility modelling:


View Historic Volatility Calculator demo.


Overview of the Hoadley Add-in volatility functions


Volatility FAQ


Approach to volatility smile modelling


Accuracy of the Hoadley options add-in GARCH function - benchmarks



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.


Finance Add-in pricing details.


Download and purchase add-in (which includes the Historic Volatility Calculator).

 White Dot