Stock Market trading relies heavily on 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.
In this tutorial we are going to see how you can use Google Sheets to calculate a Moving Average (mainly Simple Moving Average) for one or more assets over a given period of time.
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 we are going to concentrate solely on the Simple Moving Average.
The Simple Moving Average is the average of stock prices (usually closing stock prices) over a selected period of time. This period of time 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.
Let us understand 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.
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.
Click here to learn more about what the GOOGLEFINANACE function can do.
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 average simply involves finding the average for a given number of days. Let’s say we want to calculate 5-day SMA for GOOG assets over the past month.
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/2021 to 8/30/2021.
Now we want to calculate the 5-day moving average starting from 8/24/2021 right up to 9/22/2021. 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.
The formula you can use is as follows:
=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 worked, 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, simply 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 both the date and the prices, but we need only the stock prices, which are 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 time period, by just changing the value 5 to the number of days you need.
Conclusion
In this tutorial we showed you 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.
We hope this tutorial was helpful for you and easy to follow.
