Historical Delta (Options)
Returns the delta Greek for an option contract on a specific historical date. Delta measures how much the option price changes for a $1 change in the underlying stock price.
Parameters
| Parameter | Required | Description |
|---|---|---|
| Symbol | Yes | Option symbol (OCC format) |
| OnDate | Yes | Historical date (DATE function or string) |
Input Requirements
Use OptionSymbol() to generate the option symbol:
| Parameter | Source | Example |
|---|---|---|
| Symbol | OptionSymbol() output |
OptionSymbol("AAPL",DATE(2026,3,15),"Call",170) |
Understanding Delta
| Delta Range | Interpretation |
|---|---|
| 0.8 - 1.0 | Deep in-the-money call |
| 0.4 - 0.6 | At-the-money |
| 0.0 - 0.2 | Out-of-the-money |
| -0.8 to -1.0 | Deep in-the-money put |
| -0.4 to -0.6 | At-the-money put |
| -0.2 to 0.0 | Out-of-the-money put |
Notes
- Calls have positive delta (0 to 1)
- Puts have negative delta (-1 to 0)
- Delta also approximates probability of expiring in-the-money
Examples
Using OptionSymbol() - RECOMMENDED
=opt_DeltaHistorical(OptionSymbol("AAPL",DATE(2026,3,15),"Call",170),DATE(2025,12,15))Using raw OCC symbol
=opt_DeltaHistorical("AAPL240315C00170000", DATE(2025,12,15))Put option delta (will be negative)
=opt_DeltaHistorical(OptionSymbol("AAPL",DATE(2026,3,15),"Put",170),DATE(2025,12,15))Using cell references
=opt_DeltaHistorical(A1, B1)Calculate delta equivalent shares
=opt_DeltaHistorical("AAPL240315C00170000", DATE(2025,12,15)) * 100When to Use
- Backtest delta-hedging strategies
- Analyze how delta evolved over time
- Study option pricing on specific dates
- Calculate historical position Greeks
- Build historical Greeks time series
When NOT to Use
| Scenario | Use Instead |
|---|---|
| Need current delta | opt_Delta() |
| Need historical gamma | opt_GammaHistorical() |
| Need historical theta | opt_ThetaHistorical() |
| Need theoretical delta | BlackScholesOptionDelta() |
Common Issues & FAQ
Q: Why is my delta negative? A: Put options have negative delta (typically -1 to 0). This is expected behavior.
Q: What does a delta of 0.5 mean? A: The option price moves about $0.50 for every $1.00 move in the underlying. It also roughly indicates a 50% probability of expiring in-the-money.
Q: How do I get all Greeks at once?
A: Use opt_HistoricalOptionChain() to get a complete snapshot including all Greeks.
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?