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
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 * 10When 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
Q: Why is gamma the same for calls and puts? A: At the same strike and expiration, calls and puts have identical gamma values due to put-call parity.
Q: Why did gamma spike near expiration? A: Gamma increases dramatically for at-the-money options as expiration approaches. This is normal behavior.
Q: How do I calculate dollar gamma? A: Multiply gamma by (underlying price)^2 / 100 to get dollar gamma per 1% move.
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?