Calculating Simple Moving Average (SMA) in Google Sheets

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)
Understanding SMA Google Sheets

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 3"))))

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 GoogleFinace: 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.

Calculating Simple Moving Average Using Google Finance Data

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"))))
Getting Fewer Data Points in Moving Average when Using GoogleFinance Function: Solution

I’ve already mentioned where to look for this formula explanation. That’s all about calculating the simple moving average in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. Any chance in providing an example google sheets with the above in it? I’m having a difficult time grasping the above.

  2. Would there be a way to use your dynamic formula but rather than point to a specific stock within the formula, point to a cell instead.

    Then that cell would contain a stocks ticker symbol? Then you could just copy that formula down the page for a list of hundreds of stocks, rather than replacing each stock within the formula?

    • Hi, Phillip Batch,

      That you can do as below.

      Enter the ticker symbols in A1:A. Then use this GoogleFinance formula in cell C1.

      =GOOGLEFINANCE(A1,"close",today()-30,today())

      Now, modify the above formula in cell C1 to include the Query.

      =Query(GOOGLEFINANCE(A1,"close",today()-30,today()),"Select Col2 where Col2 is not null order by Col1 desc limit 10 label Col2''")

      This Query will return historical data for the last 10 days. Here instead of using the ROW function, I have sorted the historical data in descending order (the timestamp column) and limit the number of rows to 10.

      Here is SMA

      =iferror(average(Query(GOOGLEFINANCE(A1,"close",today()-30,today()),"Select Col2 where Col2 is not null order by Col1 desc limit 10 label Col2''")))

      You can copy this formula down to change the ticker.

  3. your dynamic array formula is clever:) I was stuck with the disability of “array forumlating” the query function, but your is a much better generalisation given the query constraints!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.