Historical options data is the record of what contracts were actually quoted at on a given day, and it is the only honest starting point for testing an options idea. A payoff diagram tells you what a position would be worth at expiration. It does not tell you what you would have paid to get in, whether anyone was on the other side, or how the position behaved during the weeks before expiration. Only the recorded chain answers those questions. This guide shows how to pull that data into Excel, what each field means, where it misleads you, and how to build a workbook that replays a real chain instead of assuming one.
What historical options data contains
A single day of option chain history for one underlying holds far more than a price. Each contract carries its own quote, its own liquidity, and its own volatility reading. The table below is a real recorded chain, taken from Apple on 2026-08-14 for the 2026-09-18 expiry, with the underlying at $305.93 and 35 days left to run.
| Strike | Type | Bid | Ask | Volume | Open interest | Implied vol |
|---|---|---|---|---|---|---|
| $290 | Call | $19.25 | $20.05 | 65 | 9,448 | 23.62% |
| $295 | Call | $15.55 | $16.20 | 28 | 13,232 | 23.24% |
| $300 | Call | $12.40 | $12.70 | 569 | 27,367 | 23.07% |
| $305 | Call | $9.35 | $9.70 | 3,087 | 18,020 | 22.55% |
| $310 | Call | $7.00 | $7.20 | 3,105 | 20,162 | 22.42% |
| $315 | Call | $5.05 | $5.25 | 1,554 | 9,989 | 22.34% |
| $320 | Call | $3.55 | $3.70 | 3,284 | 35,293 | 22.27% |
| $290 | Put | $2.60 | $2.66 | 1,559 | 11,010 | 23.32% |
| $295 | Put | $3.75 | $3.85 | 528 | 11,623 | 22.87% |
| $300 | Put | $5.35 | $5.55 | 1,140 | 24,216 | 22.73% |
| $305 | Put | $7.35 | $7.60 | 1,090 | 6,712 | 22.41% |
| $310 | Put | $9.95 | $10.25 | 115 | 6,707 | 22.46% |
| $315 | Put | $13.00 | $14.10 | 62 | 6,061 | 23.56% |
| $320 | Put | $15.70 | $17.90 | 14 | 3,890 | 22.98% |
The implied volatility column here is solved from the bid and ask mid of each contract, so every reading reprices its own quote exactly. That matters more than it sounds. Vendors compute implied volatility with different interest rate and dividend assumptions, and two sources can report noticeably different numbers for the same contract on the same day. If you take a volatility figure from one place and a price from another, your analysis inherits a mismatch you cannot see.
Four things this chain tells you immediately
Read across the table rather than down a single column, and the recorded data starts to speak.
Volatility is not flat across strikes. The $290 put shows 23.32% while the $320 call shows 22.27%. Downside strikes carry a higher volatility charge than upside strikes. That asymmetry is the equity skew, and it exists because demand for protection is persistent. Any test that prices every strike off one volatility number will misprice the wings.
Liquidity collapses away from the money. The $310 call traded 3,105 contracts with a spread of twenty cents. The $320 put traded 14 contracts with a spread of $2.20. Both rows look equally solid in a spreadsheet. Only one of them is a price you could have transacted near.
Open interest and volume tell different stories. The $320 call carries the largest open interest in the table at 35,293, which is positioning built up over time. Volume is what changed hands that day. A strike with heavy open interest and no volume is an old crowd, not a new one.
Puts and calls were not equally active. Total call volume was 27,693 against put volume of 11,283, a put/call volume ratio of 0.41. Open interest was more balanced, with 373,112 calls against 286,178 puts for a ratio of 0.77. The flow that day leaned toward calls more than the standing position did.
The implied against realized comparison
The single most useful thing historical options data lets you do is compare what the option market charged for volatility against what the underlying then delivered.
On 2026-08-14 the at-the-money implied volatility was 22.48%. The trailing 30-day realized volatility on the underlying was 32.06%, and the trailing 15-day reading was 35.83%. Realized volatility over the recent past was running roughly 9.6 volatility points above what the next 35 days were being priced at.
That gap is a question, not a signal. It can mean the market expected conditions to calm down. It can mean a known event had passed. It can also mean the recent past contained a one-off move that will not repeat. The number tells you where to look. It does not tell you what to do.
To see why a single reading is a weak forecast, the workbook compares each month-end trailing 30-day realized volatility against the volatility that actually arrived over the following 30 days. Across 23 completed months of Apple history, the next 30 days were calmer than the trailing window 8 times and wilder 15 times, with an average change of positive 3.09 volatility points. A trailing volatility figure describes the recent past accurately and predicts the near future poorly. Historical options data is valuable precisely because it lets you measure that gap instead of assuming it.
Greeks are not constants, and history proves it
The other thing a recorded chain gives you is the ability to follow one contract through time. Below is the same Apple $310 call from 2026-09-18 expiry, tracked from mid May to mid August.
| Date | Days to expiry | Delta | Gamma | Vega |
|---|---|---|---|---|
| 2026-05-15 | 126 | 0.4711 | 0.00958 | 0.7011 |
| 2026-07-31 | 49 | 0.5330 | 0.00868 | 0.4498 |
| 2026-08-14 | 35 | 0.4806 | 0.01312 | 0.3774 |
Delta ended close to where it began, near the middle of its range, because the underlying finished close to the strike. Everything else changed shape. Gamma rose from 0.00958 to 0.01312, so the same move in the underlying moved delta more sharply at the end than at the start. Vega fell from 0.7011 to 0.3774, close to a halving, because a shrinking window leaves less room for a volatility change to matter in dollar terms.
This is why a position that felt stable when it was opened can behave very differently in its final weeks. Nothing about the strategy changed. The passage of time changed the Greeks underneath it. A backtest that applies entry-date Greeks across the whole holding period will describe a position that never existed.
Note that the Greek figures in the sample workbook are modeled from real historical spot and real trailing realized volatility, and they are labeled as such inside the file. The template version replaces them with the values recorded on each date, which is what you want for real work.
Separating a price move from a volatility move
Historical options data also lets you decompose a profit or loss into its causes. The workbook prices the $310 call across a grid of underlying prices and volatility levels, holding the 35 days constant.
| Underlying | IV 14.4% | IV 18.4% | IV 22.4% | IV 26.4% | IV 30.4% |
|---|---|---|---|---|---|
| $275.34 | $0.02 | $0.13 | $0.41 | $0.86 | $1.47 |
| $290.63 | $0.53 | $1.24 | $2.17 | $3.24 | $4.40 |
| $305.93 | $4.11 | $5.60 | $7.10 | $8.61 | $10.11 |
| $321.23 | $13.79 | $14.94 | $16.22 | $17.58 | $18.99 |
| $336.52 | $27.75 | $28.13 | $28.77 | $29.61 | $30.60 |
The center cell is $7.10, which is the recorded mid of that contract. A five percent rise in the underlying alone takes it to $16.22. Holding the underlying still and adding four volatility points takes it to $8.61, while removing four points takes it to $5.60.
Two lessons fall out of the grid. First, direction and volatility are separate risks that can offset each other. A correct call on direction can still lose money if implied volatility falls at the same time, which is the ordinary experience of buying options into a scheduled event. Second, the volatility effect is largest near the money and shrinks deep in the money, where the contract increasingly behaves like the underlying itself.
Pulling historical options data into Excel with MarketXLS
Every figure above comes from functions that return recorded values into a worksheet cell. The starting point is the chain itself.
=OPT_HistoricalOptionChain("AAPL", "2026-08-14")
That returns the recorded chain for one date and spills down and across, so leave room below and to the right of the cell. For contract level work you first build the contract symbol.
=OptionSymbol("AAPL", "2026-09-18", "Call", 310)
The result is a QuoteMedia style symbol with two spaces between the ticker and the date. Every contract level function takes that symbol as its first argument.
=OPT_ImpliedVolatilityHistorical(B9, "2026-08-14")
=OPT_DeltaHistorical(B9, "2026-08-14")
=OPT_GammaHistorical(B9, "2026-08-14")
=OPT_VegaHistorical(B9, "2026-08-14")
=OPT_ThetaHistorical(B9, "2026-08-14")
For the market wide readings, the historical aggregates take the underlying symbol and a date.
=OPT_PutCallVolRatioHistorical("AAPL", "2026-08-14")
=OPT_PutCallOIRatioHistorical("AAPL", "2026-08-14")
=OPT_TotalVolumeOptionsHistorical("AAPL", "2026-08-14", "Call")
=OPT_TotalOpenInterestOptionsHistorical("AAPL", "2026-08-14", "Put")
=OPT_Vol_OI_Historical("AAPL", "2026-08-14", "Call")
The underlying side pairs with the option side on the same dates.
=Close_Historical("AAPL", "2026-08-14")
=Volume_Historical("AAPL", "2026-08-14")
=StockVolatilityThirtyDays("AAPL")
=StockVolatilityFifteenDays("AAPL")
Current volatility context helps you judge whether a historical reading was high or low at the time.
=ImpliedVolatility30d("AAPL")
=ImpliedVolatilityRank1y("AAPL")
=ImpliedVolatilityPct1y("AAPL")
And a set of contract descriptors turns a raw symbol into something readable.
=OPT_DaysToExpiration(B9)
=OPT_Strike(B9)
=OPT_ExpirationDate(B9)
=OPT_Moneyness(B9, 305.93)
=OPT_IntrinsicValue(B9, 305.93)
=OPT_TimeValue(B9, 7.10, 305.93)
=OPT_MaxPain("AAPL", "2026-09-18")
For your own what-if pricing, rather than a recorded value, the Black-Scholes function takes explicit inputs.
=BlackScholesOptionValueWithUserInputs(305.93, 310, "Call", "2026-09-18", 0.04, 0.0034, 0.2242)
The arguments are spot, strike, type, expiry, risk-free rate, dividend yield and volatility. Leave the volatility argument at zero and it substitutes historical volatility, which is convenient but worth being deliberate about.
Where historical options data misleads you
Recorded data is honest about what it recorded. It is silent about everything it did not.
A quote is not a fill. The $320 put above shows a mid of $16.80. With 14 contracts of volume and a $2.20 spread, treating that mid as an execution price would flatter any test that used it. Test against the side of the spread you would actually have crossed.
Wide spreads distort implied volatility. The $315 put shows 23.56%, higher than both its neighbours. That is very likely the spread rather than genuine demand, since the quote runs from $13.00 to $14.10. Volatility solved from a wide mid inherits the width.
Stale quotes survive in the record. An untraded contract can carry a quote from earlier in the session. Cross-check volume before you trust a far strike.
Expired contracts do not come back. Open interest at a strike tells you what was held, not what was profitable. Contracts that expired worthless leave the same footprint as contracts that were closed at a gain.
Corporate actions rewrite strikes. Splits and special dividends adjust contract terms. A raw historical strike may not mean what today's chain means for the same nominal number.
One name is not a market. Every figure in this guide comes from a single underlying over a single window. It illustrates method, not a general result. Widen the sample before drawing any conclusion.
What is inside the workbook
The template has six sheets, and each one carries a box listing the MarketXLS functions used on that sheet.
| Sheet | What it does |
|---|---|
| How To Use | Explains each sheet and states where every number comes from |
| Main Dashboard | Four yellow input cells drive the workbook, with the recorded snapshot and the full chain |
| Scenario Analysis | The spot by volatility grid for the focus contract |
| Contract History | One contract tracked week by week with price and Greeks |
| Position Sizing | Turns account size and risk percentage into a contract count |
| Vol Comparison | Month-end realized volatility against the volatility that followed |
Change the symbol, the as-of date, the expiry or the strike on the Main Dashboard and every other sheet re-prices from those cells. The sample workbook holds real static values with the matching formula shown in a cell comment, so you can see exactly which function produced each number before you commit to the live version.
Download the templates:
- Static version with MarketXLS formula reference contains the recorded Apple chain and volatility history
- MarketXLS formula version pulls recorded data live for any symbol and date
Building a defensible study
If you are moving from a single chain to an actual study, a few habits make the difference between a result and an artifact.
Start with the question, not the data. "Did selling 30-day at-the-money puts on this name pay for its risk" is testable. "What works in options" is not.
Use one volatility source for the whole test. Mixing implied volatility from one vendor with prices from another introduces a bias you will never isolate.
Price entries and exits against the bid and ask, not the mid, and record the spread you crossed. If a result only survives at mid, it is a result about mids.
Sample across volatility regimes. A study that covers only calm months will conclude that selling volatility is close to free money. The Vol Comparison sheet exists to make regime shifts visible before you draw that conclusion.
Keep the raw pull separate from the analysis. When a number looks wrong, and eventually one will, you want to know whether the data or your formula produced it.
For a broader introduction to the subject on our main site, see historical options data analysis in MarketXLS. If you want to see how these readings apply to specific positions, the covered call strategy guide and the iron condor strategy guide both depend heavily on the volatility context this data provides.
Frequently asked questions
What is historical options data? It is the recorded market information for option contracts on past dates. It includes bids, asks, last traded prices, volume, open interest, implied volatility and the Greeks as they stood on each date, for every strike and expiry in the chain.
How far back does historical option chain data go? Coverage depends on the underlying and the data provider. Liquid large cap names and major index products generally have the deepest history. Thinly traded names and newer listings have less. Check coverage for your specific symbol before designing a study around a long window.
Why does implied volatility differ between two sources for the same contract? Because implied volatility is solved, not observed. Each provider picks its own interest rate, dividend assumption, pricing model and input price, whether that is the bid, the ask, the mid or the last trade. Different assumptions produce different answers from identical quotes. Use one source throughout a study.
Can I use historical options data to backtest a strategy? Yes, and it is the correct data for that job. The discipline is in the details. Price against the side of the spread you would have crossed, check that volume existed at the strikes you used, account for corporate actions, and test across more than one volatility regime before you trust the result.
What is the difference between implied and realized volatility? Implied volatility is what the option market is charging for expected future movement. Realized volatility is what the underlying actually did, measured after the fact. Comparing the two across history is one of the most informative things historical options data supports.
Do I need to know the option symbol format?
Not if you use OptionSymbol, which builds the correct symbol from the underlying, expiry, type and strike. If you are reading symbols by hand, note the two spaces between the ticker and the date portion.
The bottom line
Historical options data replaces assumption with record. The chain above shows a market charging 22.48% for the next 35 days while the trailing 30 days had delivered 32.06%, pricing downside strikes above upside strikes, and quoting some contracts so thinly that their prices should not be trusted. None of that is visible in a payoff diagram, and none of it can be recovered after the fact from a model.
The workbook is built so that the recorded numbers stay separate from the modeled ones, and so that every figure names the function that produced it. That is the standard worth holding to. Data you cannot trace is data you cannot defend.
Nothing here is investment advice. The figures are a single underlying over a single window, shown to demonstrate method. Options carry the risk of losing the entire premium paid, and short option positions can lose more than the premium received.
To use these functions on your own symbols and dates, see MarketXLS, review the plans and pricing, or book a demo and we will walk through a historical study with you.