MarketXLS

Historical Gamma (Options)

Returns the gamma Greek for an option contract on a specific historical date. Gamma measures the rate of change of delta 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 Gamma

Characteristic Description
Always positive Both calls and puts have positive gamma
Highest at ATM Gamma peaks at-the-money
Increases near expiry Gamma accelerates as expiration approaches
Measures convexity Shows how quickly delta changes

Notes

  • Gamma is highest for at-the-money options
  • Near expiration, gamma can spike significantly
  • High gamma means delta changes rapidly with price moves

Syntax

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

Parameters

symbolstringRequired
onDatedateRequired

Returns

number

Historical gamma value on the specified date

Examples

Using OptionSymbol() - RECOMMENDED
=opt_GammaHistorical(OptionSymbol("AAPL",DATE(2026,3,15),"Call",170),DATE(2025,12,15))
Using raw OCC symbol
=opt_GammaHistorical("AAPL240315C00170000", DATE(2025,12,15))
Put option gamma (also positive)
=opt_GammaHistorical(OptionSymbol("AAPL",DATE(2026,3,15),"Put",170),DATE(2025,12,15))
Using cell references
=opt_GammaHistorical(A1, B1)
Calculate position gamma
=opt_GammaHistorical("AAPL240315C00170000", DATE(2025,12,15)) * 100 * 10
10 contracts

When to Use

  • Backtest gamma-scalping strategies
  • Analyze how gamma evolved over time
  • Study gamma risk near expiration
  • Calculate historical position Greeks
  • Analyze convexity in options positions

When NOT to Use

Scenario Use Instead
Need current gamma opt_Gamma()
Need historical delta opt_DeltaHistorical()
Need historical theta opt_ThetaHistorical()
Need all Greeks at once opt_HistoricalOptionChain()

Common Issues & FAQ

Why is gamma the same for calls and puts?

At the same strike and expiration, calls and puts have identical gamma values due to put-call parity.

Why did gamma spike near expiration?

Gamma increases dramatically for at-the-money options as expiration approaches. This is normal behavior.

How do I calculate dollar gamma?

Multiply gamma by (underlying price)^2 / 100 to get dollar gamma per 1% move.

Excel Templates Using Historical Gamma (Options)

These ready-made MarketXLS templates call opt_GammaHistorical() 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_GammaHistorical used in a complete workbook: Live Option Chain Data in Google Sheets: What GOOGLEFINANCE Covers and Where MarketXLS Picks Up

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

Related Functions