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.

overview of 50 day moving average in google sheets


A Sample of Practice Spreadsheet

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


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")

example of googlefinance function for moving average in google sheets

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.

how to use googlefinance function

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())

googlefinance function usage

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

data range for 50 days of stock

  • 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)

how to use average in 50 day moving average in google sheets

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

Read More: How to Use AVERAGE Function in Google Sheets (4 Examples)


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.

dataset for query function

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"))

application of average, query and googlefinance function in google sheets

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.
  • how to get 50 day moving average

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

    result from 50 day moving average in google sheets

    Read More: How to Find 7-Day Moving Average in Google Sheets


    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.

    dataset for 200 days of sma in google sheets

    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.

    final output for 200 days sma in google sheets

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

    Read More: How to Find 200-Day Moving Average in Google Sheets (2 Ways)


    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.


    Related Articles

    Ishrak Khan

    Ishrak Khan

    Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

    We will be happy to hear your thoughts

    Leave a reply

    OfficeWheel
    Logo