Plot Stock Returns Histogram in Excel

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.

AAPL Returns distribution in Excel with Automatic data download from Yahoo Finance

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.

Summary Statistics for Stock Returns in Spreadsheet

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.

Skew and Kurtosis of Google and Coca Cola CompanyThe 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


8 thoughts on “Plot Stock Returns Histogram in Excel

  1. 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

  2. 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

    1. 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

  3. 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

Leave a Reply to james Cancel reply

Your email address will not be published. Required fields are marked *

What is 5 + 4 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)