MarketXLS

Simple Moving Average (SMA)

Returns the Simple Moving Average, which calculates the arithmetic mean of closing prices over a specified period. SMA is one of the most widely used technical indicators for trend analysis.

Common SMA Periods

Period Usage
10-day Short-term trend
20-day Short-term trend
50-day Medium-term trend (golden/death cross)
200-day Long-term trend (golden/death cross)

Parameters

Parameter Description Default
Symbol Stock ticker symbol Required
Days Number of periods for average 30
StartDate Calculate SMA as of this date Today

Golden Cross / Death Cross

  • Golden Cross: 50-day SMA crosses above 200-day SMA (bullish signal)
  • Death Cross: 50-day SMA crosses below 200-day SMA (bearish signal)

Syntax

=SimpleMovingAverage(Symbol, [Days], [StartDate])
Excel Desktop (Windows)

Parameters

symbolstringRequired

Stock ticker symbol

daysstring

Number of periods for SMA calculation

startDatedate

Start date for calculation (defaults to today)

Returns

number

Simple moving average price

Examples

=SimpleMovingAverage("AAPL")
30-day SMA for Apple
=SimpleMovingAverage("AAPL", "50")
50-day SMA for Apple
=SimpleMovingAverage("MSFT", "200")
200-day SMA for Microsoft
=SimpleMovingAverage("SPY", "20")
20-day SMA for SPY
=SimpleMovingAverage("AAPL", "50", DATE(2024,1,15))
Historical SMA
=SimpleMovingAverage(A1, B1)
Symbol and days from cells

When to Use

  • Identify trend direction (price above/below SMA)
  • Calculate support and resistance levels
  • Golden cross and death cross analysis
  • Compare short-term vs long-term trends
  • Smooth out price volatility for trend analysis

When NOT to Use

Scenario Use Instead
Need faster response to price changes ExponentialMovingAverage()
Need momentum oscillator RelativeStrengthIndex() or Momentum()
Need volume-weighted average AverageDailyVolume()
Need volatility measure StandardDeviationOnClosePrice()

Common Issues & FAQ

Why is SMA returning "NA"?

Check that:

  • The symbol is valid and actively traded
  • There is sufficient historical data for the period specified
  • The stock has been trading for at least the number of days requested

What period should I use?

Common periods:

  • 10-20 days for short-term trading
  • 50 days for medium-term analysis
  • 200 days for long-term trend identification

How does SMA differ from EMA?

SMA gives equal weight to all prices in the period. EMA gives more weight to recent prices, making it more responsive to new information.

Excel Templates Using Simple Moving Average (SMA)

These ready-made MarketXLS templates call SimpleMovingAverage() in their worksheet formulas. Open one to see the function working inside a complete model.

Related Formulas

More MarketXLS Technical Indicators formulas you can use in the same worksheet:

See SimpleMovingAverage used in a complete workbook: AI Stock Analysis: How to Use Artificial Intelligence for Real-Time Financial Research

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Simple Moving Average (SMA) and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions