Calculating and charting moving averages in Google Sheet is quite simple. By using the GoogleFinance function to pull historical data and a simple average formula, you can build some very handy spreadsheets to monitor your favorite stocks. Let’s look at how to calculate and chart moving averages using Google Finance spreadsheets.
First to get started, we will setup a few cells to enter in the stock ticker as well as the start and end date for the time frame you want to consider. Then, we’ll use the following GoogleFinance formula to pull the historical data for the inputted stock and timeframe. By setting it up this way, you can change the dates and stock at any time to update the data.
The formula to use is: =GoogleFinance(ticker, “price”, start date, end date, “daily”). So, if I want to get Amazon historical data from Jan 1, 2018 to Jan 1, 2019, I’d use =GoogleFinance(“AMZN”, “price”,”01/01/2018″,”01/01/2019″,”daily”).
In the example below, we replace the ticker, start date and end date with the cell identifier.
When you put in the GoogleFinance formula, you should see the data fill out below, one row for each day during your time frame. If the data doesn’t populate, you might need to double check your formula you entered.
Now that we have the data, we want to add a column for the moving average. In our example, we will use a 10-day moving average and a 20-day moving average in order to provide a thorough demonstration.
With a data set such as this, the moving average will start on the 10th day for a 10-day moving average because you need ten days of data. You can see the first entry in the 10-day moving average column can be done like this:
Then you can copy that formula down through the remainder of the column to calculate every value.
Next, let’s create the column and the formula for the 20-day moving average column.
As we did before, extend that formula down so that it is copied to every cell from then on for the 20-day moving average column.
We now have the full historical data along with the moving averages we want.
Now that we have the data, let’s create a chart that shows all three data sets: the closing price, the 10-day moving average and the 20-day moving average for Netflix across the timeframe we inputted. Google Sheets have a built-in chart function that works super well for this.
To get started, goto the INSERT menu at the top and select CHART. When you do this, you’ll get a blank chart added to your screen with the options for the chart on the right.
First, we need to change the Chart Type to a Line Chart. Then, we need to tell the chart what cells on the Google Sheet to use for data. We want to include the headers, so we’ll go from A5 all the way to D487 (the last row of data).
Now, we need to tell the chart what to use for its x-axis. We want to use Date. Then we remove Date from the Series options. Lastly, we will check “Use row 5 as headers.” And there we go!
To clean this up a bit, I’d probably also format column A to make the date data a bit more pretty (get rid of the time component). Also, the 10-day and the 20-day moving averages aren’t overly interesting because they tend to move pretty closely with the stock price. A more interesting example would be to do a 50-day and a 200-day moving average and chart both against closing price for a particular stock. Why don’t you try that yourself?