Excel displays the Format Data Series task pane at the right side of the screen. Choose Format Data Series from the Context menu.The steps are slightly different in Excel 2013 and later versions: The Line Style options of the Format Data Series dialog box. Click Line Style at the left side of the dialog box.Excel displays the Format Data Series dialog box. In your chart, right-click on the data series that you want to smooth.Follow these steps if you are using Excel 2007 or Excel 2010: (This makes sense the lines are meant to connect the points.) You can give your graphs a more professional look by simply smoothing out the curves Excel uses at each data point. The returns histogram for Google is characterized by more kurtosis, and is hence more peaky.When you create line charts in Excel, the lines drawn between data points tend to be very straight. These histograms use weekly returns between 21st March 2010 and 20th March 2014. Those distributions with large values of skew and kurtosis are peaky (or flat), and are off-center.įor example, here are the returns distributions and summary statistics for The Coca-Cola Company (KO) and Google (GOOG). The skew and kurtosis of a normally-distributed data set are zero. You also get the mean, standard deviation, skew and kurtosis of the returns. and plots historical returns distribution.Īll of this is automatic – you just enter a few parameters, point and click.calculates the frequency of returns that fall in each bin,.calculates the continuously compounded returns,.The spreadsheet connects to Yahoo finance, and downloads the historical stock returns.

and click the Download Data and Plot Histogram button.

enter the number of bins in your histogram,.

specify whether you want daily, weekly or monthly return,.

type in a ticker, and a start and end date,.

It uses historical prices from Yahoo Finance. This Excel spreadsheet plots a returns histogram for your chosen ticker (this includes stocks, ETFs, mutual funds and more). You can also predict your maximum potential loss over a given time horizon with historical simulation of value at risk. You can use this chart to view the clustering of your returns, and to understand the potential impact of extreme (or “Black Swan”) events. Plot the historical returns histogram from prices automatically downloaded from Yahoo Finance, all in Excel.Ī historical market histogram is key to understanding the potential performance of your investments.Ī returns distribution plots the historical frequency of returns over your chosen time period.

Excel 2010 histogram