If you need to calculate the simple moving average, this can be done pretty easily in Google Sheets with a combination of several functions.
In this tutorial, I will show you how to calculate the simple moving average in Google Sheets.
Table of Contents
What is a Simple Moving Average?
A simple moving average (SMA) calculates the average of a range of prices in a number of different periods. It is usually calculated with closing prices for stocks as a way to make a technical analysis of the market for trading and investing.
It’s called a “moving” average because it is used to track change over time.
How to Calculate Simple Moving Average
To calculate the moving average we will be using the GOOGLEFINANCE and AVERAGE functions.
The GOOGLEFINANCE function is used to fetch the current or historical securities information from Google Finance.
The syntax of this function is:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
- ticker – this is the ticker symbol for the security that you want to consider. You have to use both the exchange symbol and ticker symbol. If you leave out the exchange symbol, the function will decide on its own which to choose
- attribute – this is an optional argument that will be set to price by default.
- start_date – this is an optional argument. It is the start date to use when fetching historical data
- end_date – this is an optional argument. It is the end date to use when fetching historical data
- interval – this is an optional argument. It is the frequency of returned data (DAILY or WEEKLY)
The AVERAGE function returns the average of a dataset.
The syntax of the function is:
AVERAGE(value1, [value2, …])
- value1 – the first value or range to use for calculating the average value
- value2 – this is an optional argument. You can add additional values or ranges to calculate the average value
Now that we have gone over the basics of the functions we will use, let’s look at how to actually apply them together for calculating the simple moving average.
Here are the steps:
1. The very first step is going to be to enter your GOOGLEFINANCE function into the cell where you want your data returned. Here is my function: “=GOOGLEFINANCE(“NASDAQ:GOOG”,”price”,TODAY()-30,TODAY(),”DAILY”)”. This formula is set to get the daily price for the last 30 days. You will notice my start date and end date arguments use the TODAY() function which gets the current date.
2. In the next step we will be using the AVERAGE function to get a 7-day AVERAGE. Enter the AVERAGE function into the cell where you want the average to return and input the last 7 days into the range of the function. My formula is: “=AVERAGE(D2:D8)”. Press Enter when you are done and your average will calculate.
3. Once you have your first average. Copy and paste the AVERAGE formula down to the rest of your data
You will now have your moving averages calculated in your spreadsheet.
The simple moving average is a fairly simple calculation to make in your spreadsheet. The hardest part of this is understanding the GOOGLEFINANCE function if you are new to using it.
After you have mastered that function, you will find that making this kind of calculation is incredibly simple. Hopefully, you found this tutorial helpful!
More Google Sheets Tutorials:
How to Create a Candlestick Chart