Simple Moving Average in Google Sheets | Easy Guide

Reading Time: 4 minutes

Syntax
=AVERAGE(value1, [value2, ...])

value1 – The first value or range to consider when calculating the average
value2, ... – [Optional] Additional values or ranges to consider

Sample Usage
=AVERAGE(B2:B10)

//This will return the moving average from the range B2:B10

=AVERAGE(QUERY(GOOGLEFINANCE("NSE:AXISBANK", "close",TODAY()-3,TODAY()),"Select Col2"))

//Returns the simple moving average of Axis Bank stock for the last three days.

Calculating Simple Moving Average of a company's stock prices

How to Calculate Simple Moving Average

Learn how to calculate Simple Moving Average in Google Sheets.

Introduction

You can use the Simple Moving Average to track the changes in a value over time. Let’s say you’re a stock market trader who wants to use historical data to assess market trends. In this situation, we can calculate the Simple Moving Average of the stock prices for any chosen time period. This will assist you in identifying any patterns in stock prices as well as offer you useful market insight.

Simple Moving Average in Google Sheets is often used by financial traders to compare the current value of a stock price with its average value. A moving average requires you to specify the time periods – for example, last 5 weeks, last 1 year etc. Simple Moving Average is a common tool in technical analysis to make financial stock purchases and trading decisions. The 50-day simple moving average (SMA) is especially popular among traders because historical analysis shows it to be an effective indicator of stock prices.

You can also perform demand forecasting in supply chain management using the Simple Moving Average function in Google Sheets.

While you may be familiar with the Average function, automatic Simple Moving Average in Google Sheets requires a basic usage of GoogleFinance function – so let’s get right to it!

Fetching Stock Data using GOOGLEFINANCE Function

To start working with Simple Moving Average, we first need to access the stock data using the GOOGLEFINANCE Function. You can learn more on how to use this function here

To demonstrate this, I have considered the closing stock prices for Axis Bank over the past 20 days.  To fetch the desired stock data, select a cell and type in the following formula-

=GOOGLEFINANCE(“NSE:AXISBANK”,”close”,TODAY()-20,TODAY())

In the above formula, the TODAY() function returns today’s present date and TODAY()-20 returns the date 20 days before today. 

As you can see in the figure below, the screen displays Axis Bank’s stock data for the past 20 days. 

Past 20 days stock prices of Axis Bank
Fig 1: Axis Bank Stock Prices

You will notice that the data shows a list of 14 days instead of 20. This is because the GOOGLEFINANCE function doesn’t return any information regarding the non-trading days. 

Calculating Simple Moving Average using:-

GOOGLEFINANCE and AVERAGE Functions

Now let’s say we want to calculate the 3-day Simple Moving Average for the Axis Bank stock data shown above. 

  • To do this we first create another column titled SMA in the Google spreadsheet (short for Simple Moving Average). 
Adding a column for Simple Moving Average in the spreadsheet
Fig 2: Adding SMA column | Example – Simple Moving Average in Google Sheets
  • Next, select the fourth cell in this column and type in the following formula- 

=AVERAGE(B2:B4)

  • As you can see in the figure below, cell C5 displays the Simple Moving Average for the first three entries. 
Applying Simple Moving Average formula in cell C5
Fig 3: Calculating SMA value using GOOGLEFINANCE and AVERAGE Functions
  • We can now fill the Simple Moving Average values in the remaining cells by simply dragging down the fill handle of cell C4.
Filling the Simple Moving Average values in remaining cells by dragging down the fill handle
Fig 4: Filling the Simple Moving Average values in remaining cells
  • The Simple Moving Average values for all the corresponding cells is displayed. 
All Simple Moving Average values displayed
Fig 5: SMA values | Example – Simple Moving Average in Google Sheets

GOOGLEFINANCE and QUERY Functions

We can even directly display the value of the Simple Moving Average in Google Sheets for any stock over the desired number of past days without first having to display the Dates and the corresponding closing stock price values and then calculating the SMA. This can be done using a combination of the GOOGLEFINANCE and QUERY functions. 

  • To demonstrate this, I consider a scenario of calculating the 3-day Simple Moving Average values for the stock prices of Axis Bank.
Creating a Ticker and corresponding SMA column for Axis Bank
Fig 6: Axis Bank Ticker | Example for Simple Moving Average in Google Sheets
  • Simply type in the following formula to calculate the SMA for the past 3 days up to the current date 

=AVERAGE(QUERY(GOOGLEFINANCE(A2,”close”,TODAY()-3,TODAY()),”Select Col2″))

  • As shown in the figure below, the Simple Average Value is displayed. On refreshing the page, the SMA values will automatically update themselves. 
Calculating SMA value using GOOGLEFINANCE and QUERY Functions
Fig 7: Calculating SMA value using GOOGLEFINANCE and QUERY Functions

Conclusion

In this article, we learnt how to calculate Simple Moving Average in Google Sheets. Remember that if you are using SMA for averaging stock prices, you will first need a basic understanding of the GoogleFinance function to fetch stock-market prices (and know the ticker for the stock!).

Want to learn more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics. Enjoy reading!

See More

Using GoogleFinance in Google Sheets to convert currency – Fetch real-time stock market prices

Create CandleStick Charts in Google Sheets – Track stock market price movement