MarketXLS

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

Syntax

=opt_DeltaHistorical(Symbol, OnDate)
Excel Desktop (Windows)

Parameters

symbolstringRequired
onDatedateRequired

Returns

number

Historical delta value on the specified date

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)) * 100

When 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

Why is my delta negative?

Put options have negative delta (typically -1 to 0). This is expected behavior.

What does a delta of 0.5 mean?

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.

How do I get all Greeks at once?

Use opt_HistoricalOptionChain() to get a complete snapshot including all Greeks.

Excel Templates Using Historical Delta (Options)

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

Related Formulas

More MarketXLS Options formulas you can use in the same worksheet:

See opt_DeltaHistorical used in a complete workbook: SPX Options Historical Data: Complete Guide to In-Depth Analysis and Trends

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 Historical Delta (Options) and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions