Plot the historical returns histogram from prices automatically downloaded from Yahoo Finance, all in Excel.
A historical market histogram is key to understanding the potential performance of your investments.
A returns distribution plots the historical frequency of returns over your chosen time period.
You can use this chart to view the clustering of your returns, and to understand the potential impact of extreme (or “Black Swan”) events.
You can also predict your maximum potential loss over a given time horizon with historical simulation of value at risk.
- type in a ticker, and a start and end date,
- specify whether you want daily, weekly or monthly return,
- enter the number of bins in your histogram,
- and click the Download Data and Plot Histogram button
The spreadsheet connects to Yahoo finance, and downloads the historical stock returns. Some nifty VBA then
- calculates the continuously compounded returns,
- calculates the frequency of returns that fall in each bin,
- and plots historical returns distribution.
All of this is automatic – you just enter a few parameters, point and click.
You also get the mean, standard deviation, skew and kurtosis of the returns.
The skew and kurtosis of a normally-distributed data set are zero. Those distributions with large values of skew and kurtosis are peaky (or flat), and are off-center.
For example, here are the returns distributions and summary statistics for The Coca-Cola Company (KO) and Google (GOOG). These histograms use weekly returns between 21st March 2010 and 20th March 2014.
Click this link to download the spreadsheet, and let me know if you have any comment