How to Find 50 Day Moving Average in Google Sheets

The 50 day moving average is a useful tool for identifying trends in data. It is calculated by taking the average of a security’s price over the past 50 days. In Google Sheets, the AVERAGE function can be used to quickly and easily calculate the 50 day moving average. This indicator is particularly helpful for analyzing stock prices and other types of data.

Table of Contents

A Sample of Practice Spreadsheet

You can copy our spreadsheet that we’ve used to prepare this article.

50 Days Moving Average

What Is Simple Moving Average in Google Sheets?

Simple Moving Average (SMA) is a technical analysis tool that calculates the average price of a security over a specific period by continuously adding up the closing price of the security and dividing the total by the number of periods. In Google Sheets, SMA can be calculated using the AVERAGE function over a range of cells.

Short Introduction to GOOGLEFINANCE Function in Google Sheets

The GOOGLEFINANCE function in Google Sheets allows users to retrieve current or historical financial information from a variety of markets and exchanges. This function can be used to track stock prices, currency exchange rates, and other financial metrics.

Syntax:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Here is an example of the GOOGLEFINANCE function to retrieve the stock price for Apple Inc. on a given date, the formula would be:

=GOOGLEFINANCE("AAPL","price","3/1/2022","6/1/2022")

In the “Stock Price (AAPL)” column, the GOOGLEFINANCE function would return the closing price of the stock for each respective date in the table.

2 Simple Methods to Find 50 Day Moving Average in Google Sheets

Google Sheets offers two easy ways to calculate the 50 day moving average, a commonly used indicator in stock market analysis. Both methods offer a quick and efficient way to find this important trend line in your data.

1. Using GOOGLEFINANCE with AVERAGE Function

Assume that we are going to find the 50 day moving average of TESLA Inc. (TSLA) stock.

Step:

Firstly, we are going to find the stock rate of the last 50 days using the following formula in cell B5.

=GOOGLEFINANCE("TSLA","close",TODAY()-50,TODAY())

Formula Breakdown:

The “TSLA” in the formula is the stock’s ticker symbol for Tesla.
“close” specifies that we want the closing stock price.
The date range for retrieving stock prices is set to the past 50 days up to today using “TODAY()-50” and “TODAY()”.

Use the fill handle to find the closing values for the last 50 days.
However, in this case, the dataset is limited to the last 33 days, as only trading days are considered in the calculation of the previous 50 days

Now, we are simply going to use the AVERAGE function in cell C40 to get the average of the stock price for the last 50 days using the following formula.

=AVERAGE(C5:C38)

In summary, we have determined that the average stock price of TESLA over the past 50 trading days is 152.71.

2. Applying GOOGLEFINANCE with QUERY Function

Considering that we have a dataset of 5 energy companies, we need to find the average price of these energy stocks for the last 50 days.
This time, we will use the QUERY function to calculate the SMA of the last 50 days for each company along with GOOGLEFINANCE.

Steps:

We are going to choose cell D6 first.
Now we will use the following formula to retrieve the average price of the Basic Energy Service stock.

=AVERAGE(QUERY(GOOGLEFINANCE(C6,"close",TODAY()-50,TODAY()),"Select Col2"))

Formula Breakdown:

GOOGLEFINANCE(C6,”close”,TODAY()-50,TODAY()): The formula retrieves the closing prices for a specific stock for the past 50 days until today by using the GOOGLEFINANCE function. The TODAY() function returns the current date. The start date range is specified using the TODAY()-50, which is 50 days in the past.
QUERY(…,”Select Col2″): QUERY function selects and filters a specific column “Col2” from the GOOGLEFINANCE results.
AVERAGE(QUERY(…)): The AVERAGE function takes the QUERY result and calculates the average of all values in the column.

We will finally use the fill handler feature of Google Sheets for the remaining energy stocks.

Finally, we can see that the formula has derived the average price of the stocks for the last 50 days.

Similar Readings

Can You Calculate 200 Days of Simple Moving Average?

Yes, we can.

Think about the situation where we need to determine the 200-day SMA for BlackBerry stocks. To reach the outcome, we can take some easy steps.

Steps:

First, we need to select the cell, C6.
Afterward, we are going to use the following formula:

=AVERAGE(QUERY(GOOGLEFINANCE("BB","close",TODAY()-200,TODAY()),"Select Col2"))

Note that the only real change we have brought from the previous formula is that we changed 50 to 200.

Finally, we can see that it has retrieved the average price of the BlackBerry stock, which is 5.17 over the last 200 days.

Conclusion

In conclusion, utilizing the 50 day moving average in Google Sheets is a valuable tool for professionals to analyze and track market trends. It serves as a reliable indicator for making informed decisions. The QUERY formula is undoubtedly the best approach. This powerful function can significantly aid in the monitoring of investments and overall financial performance. For additional assistance, visit OfficeWheel.