Last Updated on 08/04/2024 by Ndanileka

WHAT IS A MOVING AVERAGE

A moving average is a statistical calculation used in data analysis and time series forecasting. It is widely employed in finance, economics, and various other fields to comprehend trends, patterns, and changes in data over time.

A moving average is used in stock price analysis to make sense of price movements and identify trends. The primary purpose of using a moving average in stock analysis is to filter out the day-to-day noise and volatility in a stock’s price. This smoothing effect allows traders to focus on the broader trends and patterns in the stock’s performance.

MOVING AVERAGE CALCULATION

A moving average is calculated by taking the average (mean) of a set of a stock’s closing prices over a specific period, often referred to as the “look-back,” “window,” or “length.” This calculation is performed continuously as new daily closing prices become available, creating a moving dataset.

For example, consider ADOBE with the below close price from the previous 10 trading days.

Day	1	2	3	4	5	6	7	8	9	10
Close Price	507.03	518.42	516.44	526.68	529.29	532.72	549.91	559.63	548.76	550.74

Using the above prices, the 10 Simple Moving Average can be calculated as follows:
= Sum( Close Price) /10
= 5339.62/10
= 533.96

The calculation can be simplified further using the Google Sheets built-in Average() function:
Average(Close Price) = 533.96

MOVING AVERAGE GOOGLE SHEETS FORMULA

In the example below, we will calculate the 10-day moving average for ABODE.

Cell B1 = “ADBE” (ABODE)

Step 1 – Request historical close prices from the previous 20 Days.
We request more days than required to account for weekends and holidays. More about this here.

=GOOGLEFINANCE(B1,"close",TODAY()-20,TODAY())

Step 2 – Arrange the prices in descending order based on their dates, remove both the date column and header rows, and restrict the number of values returned to 10.

To achieve this data-cleaning process, we wrap the formula from Step 1 within the Query() function using the following statement:
SELECT Col2 (Select the Close Price column)
ORDER BY Col1 DESC (Arrange by the date column in descending order)
LIMIT 10 (Ensure that only the ten most recent close prices are included.)

=QUERY(GOOGLEFINANCE (B1,"close",TODAY()-20,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 10")

Step 3 – Complete the formula by wrapping the formula from Step 2 with the Average() function.
SMA = Average(Step 2)

=AVERAGE(QUERY(GOOGLEFINANCE (B1,"close",TODAY()-20,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 10"))

ADJUSTING THE LENGTH OF THE MOVING AVERAGE

To adjust the length of the moving average, you should ‘LIMIT’ the prices returned to the desired length. Additionally, you may want to include a buffer of extra days beyond the limit. This ensures that the formula calculates over the intended length
Here are some complete formulas for the most commonly used moving averages:

10-SMA – The 10-SMA is relatively short-term and reacts quickly to price changes.

=AVERAGE(QUERY(GOOGLEFINANCE (B1,"close",TODAY()-20,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 10"))

20-SMA – It’s slightly longer-term than the 10-SMA and provides a smoother trend indication.

=AVERAGE(QUERY(GOOGLEFINANCE (B1,"close",TODAY()-40,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 20"))

50-SMA – It provides a longer-term perspective on the price trend and is often used to identify medium-term trends.

=AVERAGE(QUERY(GOOGLEFINANCE (B1,"close",TODAY()-80,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 50"))

100-SMA – It provides a longer-term view and is commonly used to identify more significant trends.

=AVERAGE(QUERY(GOOGLEFINANCE (B1,"close",TODAY()-150,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 100"))

200-SMA – It is one of the longest-term moving averages traders and investors use to identify major, long-term trends.

=AVERAGE(QUERY(GOOGLEFINANCE (B1,"close",TODAY()-330,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 200"))

USING THE MOVING AVERAGE AS A TREND MONITOR

Once we’ve obtained the formula for calculating the moving average value, we can pair this value with the current stock price to generate a cross-over signal. This signal helps determine whether the current price is above (indicating a potential buying opportunity) or below (indicating a potential selling opportunity) the moving average. This filter serves as a starting point for deeper analysis, and when applied to a portfolio of stocks, it can offer valuable insights into market trends.

For instance, a 10-SMA might signal short-term bullishness, suggesting a potential uptrend, while a 200-SMA may indicate a longer-term bullish trend.

TREND INDICATOR EXAMPLE:

=IF(D2>AVERAGE(QUERY(GOOGLEFINANCE (A2,"close",TODAY()-20,TODAY())," SELECT Col2 ORDER BY Col1 DESC LIMIT 10")),"Above","Below")

We set up the filter using the ‘IF()‘ function. This filter compares the current price in cell D2 to the average of the last 10 closing prices obtained from the 10-SMA formula for stock ‘A2’. If the current price in D2 is greater than this average, the filter categorizes it as ‘Above.’ Otherwise, it categorizes it as ‘Below.

It’s important to note that the moving average is derived from historical price data, making it a lagging indicator. Therefore, when using it as a buy/sell signal, pairing it with further confirmation from the price itself is advisable. This is because price action can take various forms, and in situations like a ranging market, relying solely on moving averages can result in false signals.

WATCHLIST TREND MONITOR EXAMPLE:
We use the S&P 100 as our template example, observing the short-term, mid-term, and long-term moving averages of the constituent stocks. This analysis helps us gauge the current price relative to the corresponding moving average values, offering insights into the overall market’s trend.

S&P100 Index Price Trend Monitor Spreadsheet

S&P 100 Price Trend Monitor Chart