Stock market trading in 2026 relies on real-time data analysis. While AI tools can now predict trends, understanding the fundamental indicators, like market breadth and moving averages, is essential for any serious trader’s dashboard. Below, I’ll show you how to use Google Sheets to automate a Simple Moving Average (SMA) calculation that updates automatically.
What is the Simple Moving Average (SMA)?
Moving averages are the most frequently used intraday market indicators. They smooth out price data to create a single flowing line, making it easier to identify the direction of the trend.
There are two main types you should know:
- Simple Moving Average (SMA): Treats all days equally. Good for long-term trends (e.g., 200-day SMA).
- Exponential Moving Average (EMA): Gives more weight to recent prices. Better for short-term trading.
In this tutorial, we will focus on the SMA. Here is the final result we are building:
Quick Reference: The Formulas
| Goal | Formula |
|---|---|
| Basic 30-Day SMA | =AVERAGE(B2:B31) |
| Dynamic SMA (Single Cell) | =AVERAGE(QUERY(GOOGLEFINANCE("GOOG","close",TODAY()-30,TODAY()),"Select Col2 label Col2 ''")) |
Method 1: The Basic Calculation (Step-by-Step)
If you want to see the daily data alongside the average, use this method. It requires two functions: GOOGLEFINANCE to get the data, and AVERAGE to do the math.
Step 1: Get the Data
We use GOOGLEFINANCE to fetch historical data. The syntax is:
=GOOGLEFINANCE("NASDAQ:GOOG", "close", TODAY()-30, TODAY())Note for 2026: I used TODAY()-30 instead of a hardcoded date. This ensures your spreadsheet always shows data for the last 30 days relative to when you open it.
To use this function effectively, you must understand its full syntax: GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]). The ticker identifies the asset (e.g., “NASDAQ:GOOG”), while the attribute specifies the data point, such as “close.”
The interval is optional but powerful; setting it to “weekly” allows you to calculate long-term weekly moving averages instead of daily ones. For a deep dive into every parameter, check out our complete GOOGLEFINANCE function guide. Since moving averages depend entirely on the quality of your historical data, you may also find our tutorial on importing stock prices helpful for troubleshooting data gaps.
Step 2: Calculate the Average
Once the data is populated in Column B, go to Column C and use the standard average function for the first 5 days (for a 5-day SMA):
=AVERAGE(B2:B6)Drag this formula down to calculate the moving average for each subsequent day.
Method 2: The Professional “Single Formula” SMA
The method above is great for analysis, but it clutters your sheet. If you are building a Stock Watchlist, you want the SMA in a single cell without seeing all the historical rows.
We can achieve this by nesting GOOGLEFINANCE inside a QUERY function.
The Optimized Formula
=AVERAGE(QUERY(GOOGLEFINANCE(A2, "close", TODAY()-30, TODAY()), "Select Col2 label Col2 ''"))How It Works (Logic Breakdown)
- GOOGLEFINANCE: Pulls the raw data for the last 30 days (change
-30to-50for a 50-day SMA). - QUERY: We use
"Select Col2"because we only want the prices, not the dates.- Pro Tip: I added
label Col2 ''to remove the header row “Close”. This ensures the AVERAGE function only processes numbers, preventing errors.
- Pro Tip: I added
- AVERAGE: Crunches the numbers returned by the query into a single value.
Why It Matters for Your Portfolio
Building an investment tracking spreadsheet gives you control that apps often hide. By calculating your own SMA, you can detect:
- Trend Reversals: When price crosses the SMA.
- Golden Crosses: When your calculated 50-day SMA crosses above your 200-day SMA.
Frequently Asked Questions
How do I calculate the 200-day moving average in Google Sheets?
To calculate the 200-day SMA, pull a larger date range first (to account for non-trading days), then average only the most recent 200 closing prices. Use:
=AVERAGE(QUERY(GOOGLEFINANCE("TICKER","close",TODAY()-300,TODAY()),"select Col2 offset 1 limit 200",0)).
Why is my GOOGLEFINANCE moving average showing an error?
A common issue is averaging the header text along with the numbers. Fix it by extracting only the price column and skipping the header row using QUERY with offset 1. Also, try adding the exchange prefix (for example, "NASDAQ:AAPL" instead of "AAPL").
How do I visualize the moving average on a chart?
Create columns for Date, Close Price, and SMA. Select the Date, Close, and SMA columns, then go to Insert > Chart and choose a line chart. This overlays the SMA as a second series, which is clearer than relying on a chart trendline.
Can I calculate Exponential Moving Average (EMA) in Google Sheets?
Yes, but there is no single native EMA function like SMA. You calculate it iteratively using the multiplier 2/(N+1):
=(Close - Previous_EMA) * (2/(N+1)) + Previous_EMA.
SMA is still popular because it is easier to audit in a spreadsheet.
How do I track multiple stocks' moving averages in one sheet?
List tickers in Column A, then compute the SMA next to each ticker using a compact formula. For a 20-day SMA with a buffer:
=AVERAGE(QUERY(GOOGLEFINANCE(A2,"close",TODAY()-40,TODAY()),"select Col2 offset 1 limit 20",0)).
Drag it down to cover your portfolio.
How can I automatically highlight when a price crosses the SMA?
Use Conditional Formatting. Select the row or cell you want to highlight, go to Format > Conditional formatting, choose Custom formula is, and use:
=B2>C2 (price above SMA) or =B2<C2 (price below SMA), assuming B is Price and C is SMA.
Can I calculate a Weighted Moving Average (WMA) instead?
Yes. Use AVERAGE.WEIGHTED with a price range and a weights range. This is useful when you want recent days to matter more than older ones.
Does this formula work for cryptocurrency?
Often, yes. GOOGLEFINANCE supports many major crypto pairs using the CURRENCY: format. Example:
=AVERAGE(QUERY(GOOGLEFINANCE("CURRENCY:BTCUSD","price",TODAY()-30,TODAY()),"select Col2 offset 1",0)).
If a specific pair does not return data, it is a coverage limitation.
How do I identify a golden cross in Google Sheets?
A golden cross occurs when the 50-day SMA rises above the 200-day SMA. Calculate both (for example, 50-day in B and 200-day in C), then use:
=IF(B2>C2,"GOLDEN CROSS","Neutral").
Is the Google Finance data real time?
Not exactly. GOOGLEFINANCE quotes can be delayed (commonly up to about 20 minutes). For moving averages, this delay is usually fine, but it is not suitable for time-sensitive trading.
How do I exclude weekends and holidays from the count?
GOOGLEFINANCE automatically omits non-trading days in its historical results. When you request a window like TODAY()-50, add a buffer (for example, 75 to 100 calendar days) so you reliably capture enough trading days for your SMA limit.