Sishir Roy
The Simple Moving Average (SMA) constantly adds up an agency’s closing price and divides the sum by the number of periods to get the average price of the stock over a certain time. For examining stock prices and other forms of data, this indicator is quite useful. Using the 7-day moving average, you may spot trends in data. It is computed by averaging the price of a security over the previous seven days. In this article, I’ll demonstrate 2 simple ways to find the 7-day moving average in Google Sheets. Here is an overview of what we will achieve:
2 Simple Ways to Find 7-Day Moving Average in Google Sheets
An important market indicator that is also quite easy to compute is the SMA. Calculating SMA is possible with the AVERAGE function. Moreover, using Google Sheets’ GOOGLEFINANCE function, you may examine market movements using real stock prices or the closing prices of a variety of globally recognized corporations. For a corporation to understand market trends and predict markets, we will locate the 7-day moving average of the stock price here.
1. Combining GOOGLEFINANCE and AVERAGE Functions
Users may access recent or past financial data from a number of marketplaces and exchanges using the GOOGLEFINANCE feature in Google Sheets. Tracking stock values, currency exchange rates, or other financial data are all possible with this function. We’ll first retrieve recent or past financial data using the GOOGLEFINANCE function. Afterward, the AVERAGE function will calculate the 7-day moving average.
Steps:
- Firstly, select a cell where you want to apply the formula for retrieving recent and past financial data. Then, enter the formula below and press Enter. In our case, we selected Cell B4 and we wanted to retrieve the recent 20 days of data of Nasdaq, Inc.
=GOOGLEFINANCE("NASDAQ:GOOG","Price", TODAY()-20,TODAY(),"DAILY")
Formula Breakdown
- TODAY()
Here, the TODAY() function returns the current data.
- TODAY()-20
And, the 20th day in the past from the present date is represented by the TODAY()-20 function.
- GOOGLEFINANCE(“NASDAQ:GOOG”,”Price”, TODAY()-20,TODAY(),”DAILY”)
Next, the GOOGLEFINANCE function retrieves the stock prices for shares of Nasdaq, Inc. for the last 20 days up to the present. The ticker for the stocks being considered in this scenario is NASDAQ: GOOG. The start date range is supplied using the TODAY()-7 function. The TODAY() function returns the end date.
- As a result, you will get the recent 20 days of data of Nasdaq, Inc.
- Now, to calculate the 7-day moving average, we’ll use the AVERAGE First, select Cell D12, and enter the formula below. We wanted to calculate the 7-day moving average. Therefore, we have taken 7 rows of data inside the AVERAGE function.
=AVERAGE(C5:C11)
- Next, drag the Fill handle icon down to the bottom to apply the formula to the remaining cells.
- Thus, you will receive the 7-day moving average of stock prices of Nasdaq, Inc.
Read More: How to Find 200-Day Moving Average in Google Sheets (2 Ways)
2. Merging GOOGLEFINANCE and QUERY Functions
We can even combine the GOOGLEFINANCE and QUERY functions to get a 7-day moving average of data in Google Sheets. Suppose, we have a list of financial corporation companies and we wanted to calculate the 7-day moving average of these companies. We can quickly get the result by utilizing the GOOGLEFINANCE and QUERY functions. We will use the dataset below to demonstrate this example. The dataset contains a list of financial corporation companies in one column and a ticker for each company in another column.
Steps:
- Firstly, select a cell where you’re going to apply the formula. In this instance, we selected Cell D5. Next, input the following formula and hit Enter–
=AVERAGE(QUERY(GOOGLEFINANCE(C5,"close",TODAY()-7,TODAY()),"Select Col2"))
Formula Breakdown
- GOOGLEFINANCE(C5,”close”,TODAY()-7,TODAY())
The GOOGLEFINANCE function first obtains the closing prices for Nasdaq, Inc. shares for the last seven days up to the present. The ticker for the securities to be considered in this case is Cell C5. The TODAY()-7 function, which represents the 7 days in the past from the present, is used to provide the start date range. The end date for obtaining data is the current date, which is returned by the TODAY() function.
- QUERY(GOOGLEFINANCE(C5,”close”,TODAY()-7,TODAY()),”Select Col2″)
Afterward, the QUERY function executes a Google Visualization API Query Language search to pick and filter a particular column Col2 from the results of the GOOGLEFINANCE function.
- AVERAGE(QUERY(GOOGLEFINANCE(C5,”close”,TODAY()-7,TODAY()),”Select Col2″))
Finally, the AVERAGE function determines the average of every value in the column using the QUERY result as input.
- As a result, you will get the latest 7-day average of the stock price of Nasdaq, Inc. Now to apply the formula to the remaining companies, drag the Fill Handle icon downward.
- Thus, you’ll receive the latest 7-day average of the stock price of the listed companies.
Read More: How to Find 50 Day Moving Average in Google Sheets
Conclusion
To sum up, utilizing Google Sheets’ 7-day moving average is a helpful tool for experts to evaluate and track changes in the market. You can use any of the two methods to get the 7-day moving average. Please feel free to any queries or suggestions in the comment section below. To explore more of these helpful articles on Google Sheets, visit our website Officewheel.com.
