The purpose of this tutorial is to show you how to plot time series of stock prices and returns using Excel. And, we will also explain how to interpret these plots to gain a better understanding of stock prices and returns.
We will use Microsoft Corporation (MSFT) shares and Kellogg Company (K) shares as examples. We downloaded data on the historical monthly prices of these two stocks for the 30-year period between January 1, 1990 and January 1, 2020 from Yahoo! Finance. Let’s plot time series of these prices and the returns implied by them.
Contents
Time series plots of stock prices
We begin with the share price of Microsoft (Figure 1). We observe impressive growth over the 30-year period, especially after 2012. However, it would be wrong to say that the increase in the share price has been smooth over the years. In fact, we detect clear downturns, for example, during 2000 (around the burst of the Dot-com Bubble) and 2008 (the Credit Crunch).
We observe an upward trend over the 30-year period for Kellogg shares (Figure 2) as well. Again, although the long-run trend is upward, there are lots of ups and downs along the way. This pattern is typical across stocks as they are sensitive to broad market movements.
Time series plots of stock returns
How about the returns? When we examine the monthly returns of Microsoft (Figure 3) and Kellogg (Figure 4), we don’t observe an obvious trend, which was the case for monthly prices. Instead, we see sort of a noise process.
For Microsoft, most monthly returns lie within the [−30%, +40%] band. This doesn’t mean at all that more extreme returns won’t be observed. It just means that most returns fell into this interval in the past, but there is always a risk of a monthly return of less than −40%.
For Kellogg, the corresponding band is [−20%, +20%], which is narrower than the one for Microsoft. This hints to us that the monthly returns of Kellogg shares are less volatile than those of Microsoft shares. We’ll investigate this further in our next post.
Step-by-step guide for producing time series plots in Excel
The steps you need to follow to produce the charts shown in Figures 1-4 are as follows:
- Store in one column the stock prices (or returns).
- In the adjacent column, store the dates (daily, monthly, etc.).
- Go to the Insert tab and click on the “line chart” icon.
- Next, click Select Data under the Chart Design tab.
- When the pop-up menu shows up, in the Chart data range field, enter the column with the stock prices (or returns).
- In the same pop-up menu, in the Horizontal (Category) Axis Labels field, enter the column with the dates.
- Click OK to produce the chart.
- You can use Add Chart Element (under the Chart Design tab) to edit the chart title, axis titles, and so on.
Video tutorial
Below you can find a summary of the discussion above in a video format.
What is next?
This tutorial is part of the series on analyzing stock returns.
- Next tutorial: We will offer a step-by-step guide for creating a histogram of stock returns using Excel.
- Previous tutorial: We showed how to download stock price data from Yahoo! Finance.
Feel free to share this post if you enjoyed reading it. Also, please get in touch with us if you noticed any errors or have any suggestions/questions.
