value1 – The first value or range to consider when calculating the average
value2, ... – [Optional] Additional values or ranges to consider
//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
Learn how to calculate Simple Moving Average in Google Sheets.
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-
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.
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).
- Next, select the fourth cell in this column and type in the following formula-
- As you can see in the figure 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 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.
- Simply type in the following formula to calculate the SMA for the past 3 days up to the current date
- As shown in the figure below, the Simple Average Value is displayed. On refreshing the page, the SMA values will automatically update themselves.
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!
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