Stock market trading relies heavily on the analysis of past data. It requires observing trends and identifying patterns to make the right decisions. Indicators like market breadth, market sentiment, balance volume, and moving averages form a major part of market analytics. Below, I’ll show you how to use Google Sheets to calculate a moving average for one or more assets over a given period.
Table of Contents
What is the Simple Moving Average (SMA)?
Moving averages are the most frequently used intraday market indicators. They indicate the momentum and trends in the market. There are many types of moving averages that can be used as market indicators, but in this tutorial I’m going to concentrate solely on the Simple Moving Average.
Here’s what it looks like with the function and an auto-complete. I’ll cover step-by-step examples of how to get this below.
The Simple Moving Average is the average of stock prices (usually closing stock prices) over a selected period. This period is usually the number of trading days over which the SMA is calculated. For example, an SMA calculated over 5 days is called a 5-day SMA. Similarly, you can have a 10-day SMA, 20-day SMA and so on.
The SMA can help in determining if the price of a stock will continue a bull or bear trend or if it will reverse direction.
The average is said to be ‘moving’ because it tracks the change in prices over time. It is frequently used by traders because it’s easy to understand and easy to calculate.
The Google Sheets Moving Average and How to Calculate it
If you want to calculate a simple moving average in Google Sheets you will need to first access stock data, which is usually the closing prices of your required stock over a given period of time.
With Google Sheets, you can directly import this information using the GOOGLEFINANCE function. Once you get your required stock data, you can then go ahead and use the AVERAGE function to compute the Simple Moving Average. Note that this differs from the Google Finance website. There, you can build a portfolio of stocks. The spreadsheet function, in contrast, pulls data directly into your workbook.
Here’s how these two functions – GOOGLEFINANCE and AVERAGE – work in Google Sheets.
The GOOGLEFINANCE Function
The GOOGLEFINANCE function is specifically meant to fetch data on stock prices. It basically accesses current or historical stock information from the Google Finance website. And we can pull this information in less than five seconds. Here’s how it looks.
The syntax for this function is as follows:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
Here,
- ticker is the symbol or initials corresponding to your required stock. This can consist of both the exchange symbol as well as the ticker symbol of the stock. For example, “NASDAQ:GOOG” represents “GOOG” stocks. You could also simply use the symbol “GOOG”.
- attribute is the value that you want to access relating to the given ticker, for example, “price”,”high”,”low”, “volume”, etc. If you don’t specify this attribute, the function fetches the price relating to the given ticker.
- start_date is the starting date from which you want the attribute retrieved
- end_date is the ending date till which you want the attribute retrieved. num_days is the number of days for which you want the attribute retrieved. You can either specify an end_date or num_days value for this parameter.
- interval specifies how frequently you want data returned. It can be either ‘daily’ or ‘weekly’.
So to fetch closing prices of the “GOOG” stocks, we use the GOOGLEFINANCE function as follows:
=GOOGLEFINANCE(“NASDAQ:GOOG”,”close”,DATE(2021,2,27),10)
The above function fetches the closing prices of NASDAQ:GOOG starting from 2/27/2021 and up to 10 days after that.
Learn more about what the GOOGLE FINANCE function can do with my guide on currency conversion.
The AVERAGE Function
This function finds the average of a given set of values. Syntax for this function is as follows:
AVERAGE(value1, [value2, ...])
Here,
- value1 is the first value to consider in the average calculation
- value2, … are additional values that you want to consider in the average calculation. These values are optional.
So to find the average of values in the range A2:A6, we use the AVERAGE function as follows:
=AVERAGE(A2:A6)
Calculating Moving Average in Google Sheets using a Formula with GOOGLEFINANCE and AVERAGE Functions
Calculation of moving averages involves finding the average for a given number of days. Let’s say we want to calculate a 5-day SMA for GOOG assets over the past month. You could go an manually pull that information from Yahoo Finance, but we already discussed the faster method.
For this we need to first import the closing prices of the GOOG stock for the past month. The TODAY() function returns the current date, so we can use this function to get the stock prices for the past month, as follows:
=GOOGLEFINANCE("NASDAQ:GOOG","close",TODAY()-30,TODAY())
Here, TODAY() returns the current date and TODAY()-30 returns the date 30 days before the current date.
Here’s what the above formula should return:
Notice that the number of rows displayed is less than 30. This is because the GOOGLEFINANCE function only returns details about trading days. Any non-trading days are omitted.
Now let us calculate the 5-day moving average in column C. Select the 5th cell of the column, and type the formula, followed by the return key:
=AVERAGE(B2:B6)
This will display the 5-day moving average from date 8/24 to 8/30.
Now we want to calculate the 5-day moving average starting from 8/24 right up to 9/22. So we need to copy the formula to the rest of the cells in the column by dragging down the fill handle of cell C6.
As you can see, the moving average of 5 days at a time is displayed in column C. Each cell of column B displays the average for the previous 5 days.
We can now use the results in column C to understand the changes in closing prices of the given stock.
Calculating Moving Average in Google Sheets using a Formula with GOOGLEFINANCE and QUERY Functions
The above method is great when you want to analyze or visualize prices of just one or two assets. However, if you need to compare stock price SMAs for multiple assets, then displaying all the stock prices for a large number of days is not very feasible. It will end up taking too much space and will make your worksheet look overcrowded with too many dates and numbers.
A more efficient way would be to directly display the SMAs for each stock over the past N days. You can make the formula dynamic, so that the value gets always updated depending on the current date, and displays the SMA starting from N days before the current date.
So if you want to display, say 5-day SMAs, for the following 6 assets, you can use a combination of the GOOGLEFINANCE and QUERY functions.
Here’s a formula I wrote that helps you complete the calculation. Note that you’ll update the “-5” to whichever date range you’d like.
=AVERAGE(QUERY(GOOGLEFINANCE(A2,"close",TODAY()-5,TODAY()),"Select Col2"))
The above formula will calculate the SMA for the last 5 trading days up to the current date, and will always get updated with the latest values after every 15 minutes, or whenever the page is refreshed. To get 5-day SMAs for all the assets, simply drag down the formula.
As you can see from the above screenshot, it becomes easy to see at a glance and compare the SMA values for different stocks in this way.
Explanation of the Formula
To understand how the above formula works, let us go over it layer-by-layer, starting from the innermost formula:
GOOGLEFINANCE(A2,"close",TODAY()-5,TODAY())
This function, as explained in the previous section, extracts the closing stock prices of the stock ticker that is specified in cell A2 for the last 5 days up to the current date.
QUERY(GOOGLEFINANCE(A2,"close",TODAY()-5,TODAY()),"Select Col2")
The GOOGLEFINANCE function returns the date and the prices, but we need only the stock prices in column 2. So we use the QUERY function to extract only the second column.
The QUERY function helps us run SQL-like queries on a given table. We pass the range or table that we want to apply the query on as the first parameter and the query that we want to apply as the second parameter.
Here, we passed the table returned by the GOOGLEFINANCE function as the first parameter. To extract column 2 from this table, we specified the query “Select Col2”.
AVERAGE(QUERY(GOOGLEFINANCE(A2,"close",TODAY()-5,TODAY()),"Select Col2"))
The QUERY function returns the second column’s values (which consist of closing stock prices of the past 5 trading days). So we apply the AVERAGE function on these returned values to obtain the 5-day SMA.
Note: You can adjust this formula to display SMA for any period, by just changing the value 5 to the number of days you need.
Why It Matters
If you’re building an investment spreadsheet, it’s helpful to calculate moving average automatically. This gives a quick look on whether your chosen stock is underperforming or outperforming its past few trading days.
Some traders use this information to inform their trades. It’s called moving average forecasting. This kind of forecasting can provide insight into the stock’s performance over a rolling period of time, so it’s valuable as a consideration.
Conclusion
In this tutorial, I showed how the Google Sheets Moving Average works and two ways to calculate it. The first method is helpful if you want to observe the trends in prices of one or more assets, while the second method can be used when you want to compare SMAs for multiple assets.
