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 |
Examples
=StandardDeviationOnClosePrice("AAPL")=StandardDeviationOnClosePrice("AAPL", "20")=StandardDeviationOnClosePrice("TSLA", "14")=StandardDeviationOnClosePrice("SPY", "30")=StandardDeviationOnClosePrice("AAPL", "20", DATE(2024,1,15))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
Q: Why is StdDev returning "NA"? A: 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
Q: How do I interpret the value? A: 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%).
Q: What period should I use? A: Common periods:
- 14 days is a common default
- 20 days is used for standard Bollinger Bands
- 30+ days for longer-term volatility
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today
▶
How does MarketXLS work?