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.
Time series plots of stock prices
We begin with the share price of Microsoft (Figure 1). We observe an 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 can observe 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.
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 monthly returns of Kellogg shares are less volatile than those of Microsoft shares. We’ll investigate this further in our next post.
Here is a Youtube video we created (“Plotting stock prices and returns | Analyzing stock returns #2”) to demonstrate how we produce the charts displayed above using Excel.
What is next?
This post is part of the series on analyzing stock returns. The next post in the series is creating a histogram of stock returns. The previous past was downloading stock price data.
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.
