MarketXLS

Standard Deviation

Returns the standard deviation of closing prices, measuring the dispersion of price data from its mean. Higher values indicate greater volatility.

Understanding Standard Deviation

  • Low StdDev: Prices are clustered close to the mean (low volatility)
  • High StdDev: Prices are spread out (high volatility)
  • Increasing StdDev: Volatility is expanding
  • Decreasing StdDev: Volatility is contracting

Bollinger Bands Connection

Standard deviation is used to calculate Bollinger Bands:

  • Upper Band = SMA + (2 x StdDev)
  • Lower Band = SMA - (2 x StdDev)

Parameters

Parameter Description Default
Symbol Stock ticker symbol Required
Days Number of periods 14
StartDate Calculate as of this date Today

Syntax

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

Parameters

symbolstringRequired

Stock ticker symbol

daysstring

Number of periods for calculation

startDatedate

Start date for calculation (defaults to today)

Returns

number

Standard deviation of closing prices

Examples

=StandardDeviationOnClosePrice("AAPL")
14-day StdDev for Apple
=StandardDeviationOnClosePrice("AAPL", "20")
20-day StdDev (Bollinger default)
=StandardDeviationOnClosePrice("TSLA", "14")
14-day StdDev for Tesla
=StandardDeviationOnClosePrice("SPY", "30")
30-day StdDev for SPY
=StandardDeviationOnClosePrice("AAPL", "20", DATE(2024,1,15))
Historical
=StandardDeviationOnClosePrice(A1)
Symbol from cell reference

When to Use

  • Measure price volatility over time
  • Calculate Bollinger Bands components
  • Compare volatility across stocks
  • Identify volatility expansion/contraction
  • Risk assessment and position sizing

When NOT to Use

Scenario Use Instead
Need intraday volatility (high-low-close) AverageTrueRange()
Need price direction/trend SimpleMovingAverage()
Need momentum oscillator RelativeStrengthIndex()
Need VIX-style volatility Market volatility functions

Common Issues & FAQ

Why is StdDev returning "NA"?

Check that:

  • The symbol is valid and actively traded
  • There is sufficient historical data for the period
  • The stock has enough price variation to calculate

How do I interpret the value?

Standard deviation is in price units (dollars). Compare it to the stock price or historical values for context. A $5 StdDev on a $50 stock (10%) is more volatile than $5 on a $200 stock (2.5%).

What period should I use?

Common periods:

  • 14 days is a common default
  • 20 days is used for standard Bollinger Bands
  • 30+ days for longer-term volatility

Excel Templates Using Standard Deviation

These ready-made MarketXLS templates call StandardDeviationOnClosePrice() 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 StandardDeviationOnClosePrice used in a complete workbook: Low Volatility Factor Screener Excel: Defensive Equity Screen for May 2026

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 Standard Deviation and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions