Creating a histogram of stock returns with Excel

Creating a histogram of stock returns with Excel

An easy way to visualize the distribution of stock returns is to plot a histogram. In this tutorial, we will show how to create a histogram using Excel. Moreover, we will explain how to interpret a histogram of stock returns.

Using Excel’s “data analysis” tool to plot a histogram

We will be working with monthly returns of two US stocks: Microsoft (MSFT) and Kellogg (K). To plot a histogram in Excel, you need to go to the “Data” tab and then click on the “Data Analysis” box. Note that if you haven’t used Data Analysis before, you need to make sure that the “Analysis ToolPak”, which is an Excel add-in, is enabled. Otherwise, the Data Analysis box would not appear under the Data tab.

Once you click on the Data Analysis box, choose the “Histogram” tool. You’ll then have to specify the “Input Range”, which are the cells in which the stock returns are stored. You can (but don’t have to) specify your own bins (the “Bin Range”) for the histogram. For example, if you generated a bin from 10% to 5%, the histogram would give you the number of return observations that fall within that range in the sample. It’s often a good idea to check the “Chart Output” option (again you don’t have to) so that Excel automatically produces the histogram chart.

Interpreting histograms of stock returns

Below you can see the histograms of monthly returns for Microsoft (Figure 1) and Kellogg (Figure 2) for the period between January 1, 1990 and January 1, 2020. We observe that for both stocks most monthly return observations fall within the —5% to 10% range. However, there are differences between the return distributions of these two stocks as well: The distribution of Kellogg returns is more tightly centered around the mean compared to Microsoft returns, which are more spread out. In particular, there are more observations both below —5% and above 10% for the Microsoft stock compared to the Kellogg stock. This means that Microsoft shares had higher return volatility than Kellogg shares during the sample period.

histogram of stock returns using Excel (MSFT)
Figure 1: Microsoft monthly return distribution (Jan 1990 to Jan 2020)
Histogram of stock returns using Excel (K)
Figure 2: Kellogg monthly return distribution (Jan 1990 to Jan 2020)

We have also created a Youtube video titled “Creating a histogram of stock returns | Analyzing stock returns #3”. It shows how the two histograms above were produced. You can watch this video below.

Creating a histogram of stock retur...
Creating a histogram of stock returns | Analyzing stock returns #3

Creating histograms with other statistical software

Of course, you can plot a histogram of stock returns using statistical software other than Excel as well. Here is a list of commands for popular statistical packages:

  • Matlab: Enter hist(x) to create a histogram of the elements in x. You can add the option ‘nbins’ for the number of bins.
  • R: Use the hist(x) function where x is a vector of numbers. Option ‘breaks’ can be used to define bar widths.
  • SAS: You can use the HISTOGRAM statement after following the SGPLOT procedure and inputting your dataset.
  • Stata: Use the histogram command (the option ‘bin’ specifies the number of bins, ‘width’ sets the width of bins, and ‘start’ indicates the starting pointing of first bin).
What is next?

This post is part of the series on analyzing stock returns. The previous post explained how to plot stock prices and returns. In the next post, we will show how to obtain descriptive statistics for stock returns using Excel.

Feel free to share this post if you enjoyed reading it. Also, please leave a comment below if you noticed any errors or have any suggestions/questions.

If you would like to find out more about histograms and how they can be used in areas other than finance and investments, have a look here: https://www.mathsisfun.com/data/histograms.html.


Leave a Reply

Your email address will not be published.