Syntax=AVERAGE(value1, [value2, ...])value1 – The first value or range to consider when calculating the averagevalue2, ... – [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.
How to calculate simple moving average in Google Sheets
Learn how to calculate simple moving average in Google Sheets.
Introduction
We can use the simple moving average to track the changes in a value over time. Suppose that you’re a stock market trader and you want to use historical data to assess market trends. In this situation, you can calculate simple moving average of the stock prices for any chosen time period. This will assist you in identifying patterns and oddities in stock prices as well as offer you useful market insight.
A 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 the GOOGLEFINANCE function – so let’s get right to it!
Fetching stock data using GOOGLEFINANCE function
To start working with simple moving average in Google Sheets, 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, let us consider 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 previous 20 days.
As you can see in the figure below, the screen displays Axis Bank’s stock data for the past 20 days.
You will notice that the data shows a list for 14 days and not 20. This is because the GOOGLEFINANCE function doesn’t return any information regarding the non-trading days.
Now that we have that, let’s move on to how we can calculate simple moving average in Google Sheets.
Calculate simple moving average in Google Sheets using the AVERAGE function
GOOGLEFINANCE and AVERAGE Functions
Using the data above, let’s calculate the 3-day moving average of the Axis Bank data.
- Give a heading name. We’ll use SMA (short for simple moving average)..
- Next, select the fourth cell in this column and type in the following formula.
=AVERAGE(B2:B4)- As you can see in the image below, cell C5 displays the simple moving average for the first three entries.
- We can now fill the simple moving average values in the remaining cells by simply dragging down the fill handle of cell C4.
- The simple moving average values for all the corresponding cells is displayed.
GOOGLEFINANCE and QUERY Functions
We can also 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, let us consider a scenario of calculating the 3-day simple moving average values for the stock prices of Axis Bank.
- Simply type in the following formula to calculate the Google Finance moving average 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 simple moving average values in Google Finance will automatically update themselves.
We can similarly calculate the Google Finance moving average for any ticker and for any number of days, ie, 5-day moving average, 7-day moving average, 10-day moving average, and so on.
Conclusion
In this article, we learnt how to calculate simple moving average in Google Sheets. Remember that if you are using a simple moving average for averaging stock prices, you will first need a basic understanding of the Google Finance function to fetch stock-market prices (and know the ticker for the stock).
If you want to learn more tips and tricks in Google Sheets or how to import stock and crypto data into Google Sheets and track them live with charts and other visualisations tools from Google Sheets, you can do no better than to visit our blog. You’re in for a treat!
Or start with one of the articles below
Import Yahoo Finance Data Into Google Sheets
Track NSE & BSE Live Data in Google Sheets