Are you using the GoogleFinance function to fetch historical data from Google Finance? Then you might want to know how to calculate the simple moving average (SMA) in Google Sheets.
But that doesn’t mean my formula works only with securities information returned from the Google Finance website. I took the corresponding function (GoogleFinance) to import some historical data for our test.
The formula just requires data points (numbers) in a column to work.
In this tutorial, you will get a dynamic Query and GoogleFinance combo formula for calculating the simple moving average in Google Sheets.
Don’t worry about the complexity of the formula. You can use it out of the box.
SMA: Understand the Term
Simple Moving Average (SMA) is the average of a set of given numbers. Unlike average calculation, In SMA, the given set of numbers keeps moving with new data.
We get SMA by adding up the LAST n days’ closing prices (if we consider stock data) and dividing that number by n.
We will use the GoogleFinance function to get the closing price of a particular ticker symbol for a given period and calculate the simple moving average.
Let’s forget about stock data and understand SMA with a basic example.
Here is an example of calculating the simple moving average of the last three data points in a range in Google Sheets.
In cell C3, I have calculated the average of the LAST three data points in column A using the following Average formula.
=average(A6:A8)
After that, a new value is available in cell A9. So we must drop one oldest data points (cell A6) and add the new one (A9). That’s the second formula in the image above.
=average(A7:A9)
The above is an example of a simple moving average calculation in Google Sheets.
But how do we automate the same? I mean, avoid modifying the range manually in the formula.
How to Calculate the Simple Moving Average Dynamically in Google Sheets
As I mentioned above, to calculate the simple moving average in Google Sheets, you want a formula, that can extract the last N values in a column or row.
We can use the following formula for the same.
=ArrayFormula(iferror(average(query(if(len(A1:A),{ROW(A1:A),A1:A},),"Select Col2 where Col2>0 order by Col1 Desc limit <strong>3</strong>"))))
In this formula, the N is 3. Please see the highlighted part at the end of the Query.
For details, please check this tutorial: Find the Average of the Last N Values in Google Sheets.
So you have learned to calculate simple moving averages in Google Sheets.
Now let us import some historical data from Google Finance and dynamically calculate simple moving averages in Google Sheets.
Because SMA is part of the trading strategy and helps determine if an asset price will carry on with a bull or bear trend or reverse. It helps traders ignore day-to-day price fluctuations.
Simple Moving Average that Involves Query and GoogleFinance in Google Sheets
Here I am going to calculate the SMA by adding the closing price of the security, ticker HDFC, for the last 10 days.
Syntax of GoogleFinance: GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
ticker: “HDFC”
attribute: close
start_date: today()-10
end_date: today()
interval: “daily”
Formula (to populate the stock data) in Cell A1:
=GOOGLEFINANCE("HDFC","close",today()-10,today(),"daily")
Result:
You will see a different result, even if the ticker symbol is the same since the start_date and end_date won’t be the same when you test this example.
Note:- To your surprise, you can see that there are only fewer data points! It is because the GoogleFinance formula skips the trading off days. I’ll suggest a workaround for this later.
Now using Query, we can calculate the simple moving average of the data points in column B, because it can remove the “Date” column A and aggregate the “Close” column B.
Formula (to calculate a simple moving average from the stock data):
Wrap the above formula with Query as below.
=query(GOOGLEFINANCE("HDFC","close",today()-10,today(),"daily"),"Select Avg(Col2) label Avg(Col2)''")
Solution When GoogleFinance Returns Fewer Data Points
As you can see in the above example, the GoogleFinance returned fewer data points.
If you are particular about the number of data points, you must modify today()-10 part of the formula.
Replace 10 with 30, i.e., today()-30 or a more significant number. So GoogleFinance will import stock data for more than ten days.
Here we require to follow two steps to calculate the simple moving average in Google Sheets.
Step 1:
Please insert the below formula in cell A1 to get the stock data for the last 30 days (we want ten days, though).
=GOOGLEFINANCE("HDFC","close",today()-30,today(),"daily")
Step 2:
We will use a variation of the formula under the subtitle (please scroll up to see) “How to Calculate the Simple Moving Average Dynamically in Google Sheets” as below.
=ArrayFormula(iferror(average(query(if(len(A1:A),{ROW(A1:A),B1:B},),"Select Col2 where Col2>0 order by Col1 Desc limit 10"))))
I’ve already mentioned where to look for this formula explanation. That’s all about calculating the simple moving average in Google Sheets.
