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.
This Excel spreadsheet plots a returns histogram for your chosen ticker (this includes stocks, ETFs, mutual funds and more). It uses historical prices from Yahoo Finance. You just
- 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.
The returns histogram for Google is characterized by more kurtosis, and is hence more peaky.
Click this link to download the spreadsheet, and let me know if you have any comment
Download Excel Spreadsheet to Plot Returns Distribution From Yahoo Finance Data
Sounds awesome … but doesn’t seem to work in excel 2007?
Can yo help ..
Works fine for me on Excel 2010 and 2013 using Windows 7.
Hi, great spreadsheet! I have a small question about the calculation of Normal pfd on the Data tab. Lets say i want 30 bins, then on the data tab i will see 29 returns spaced out equally by bucket width and only 28 percentage number for Normal pdf for those 29 return points. Why is that? Is it related to the fact that when you create bins you also do not look at Max and Min returns? Thank you
hey Samir, a few days ago, i left you a comment here for the second time and again it was not published. I don’t care if it’s published or not but would have liked to have my question answered. If you take the time to review and remove my post, why not replying to persons who appreciate your work? thanks
What do numbers in column I represent and how were they calculated?
Columm I contains the midpoints of each frequency bin, and is used to label the histogram x-axis.
The bin size is (the maximum return minus the minimum return) / number of bins
The bin labels (column I) = minimum return + 0.5 * bin size + (i-1)*bin size where i goes from 1 to the number of bins
Any way I can see the underlying code? I’m troubleshooting a sheet myself and would like to see where I’m going wrong.
tks
Make a donation (what you think it’s worth to you) and I’ll send you an unlocked copy