Use current and historical options data

Start by deciding what one row or cell should represent. MarketXLS uses different functions for a current option chain, one exact contract, and a historical snapshot.

Choose the task

In these examples, cell A2 contains the exact option symbol. Create it with Create and convert option symbols or copy it from a returned chain.

Retrieve a current option chain

Use the smallest chain that fits your task:

On Microsoft 365, add mxls. to each function name.

QM_GetOptionChainActive doesn't mean “every currently listed contract.” It sends a request that excludes zero-volume contracts. If a valid contract is missing from the active chain, try the full QM_GetOptionChain result.

Chain functions return a table. Enter the formula in an empty area with room for the result. If Excel returns #SPILL!, clear the cells that block the table and try again.

Build an expiration and strike sequence

Use these formulas when you build or extend a workbook around selected contracts:

• ExpirationNext(symbol,number_of_expirations) returns an expiration by its offset.
• StrikeNext(symbol,number_of_strikes,expiration_date) returns a strike by its offset. Use a negative offset for lower strikes and a positive offset for higher strikes.

For example, =StrikeNext($B$1,-9) keeps the underlying in B1 fixed while a template extends a lower-strike row. On Microsoft 365, use =mxls.StrikeNext($B$1,-9).

Don't drag a hard-coded option symbol and expect its strike digits to change. Build the strike or expiration in a separate cell, then pass it to OptionSymbol.

Screenshot needed · TEMPLATE-WORKBOOK-01

Capture a current option-chain template with instructional cells and safe example symbols. Don't use customer positions.

/images/docs/shared/template-workbook.webp

Request one current contract

Use a streaming formula when you need live ticks. Use an on-demand formula when you need a value only when the cell recalculates.

Add mxls. on Microsoft 365. Streaming requires an active QuoteMedia connection and consumes one live symbol subscription for each unique option contract. A large chain can therefore use many more subscriptions than a stock watchlist with the same number of underlyings.

Request historical options data

Historical options questions fall into three groups:

1. Use OPT_HistoricalOptionChain(underlying,date) for the available chain on one past date.
2. Use Bid_Historical or Ask_Historical with an exact option symbol for one contract's past bid or ask.
3. Use OPT_DeltaHistorical, OPT_GammaHistorical, OPT_ImpliedVolatilityHistorical, OPT_ThetaHistorical, OPT_VegaHistorical, or OPT_RhoHistorical for one contract's past Greek.

The Microsoft 365 add-in also provides QuoteMedia-sourced QM_Bid_Historical and QM_Ask_Historical. These are separate from the MarketXLS-sourced Bid_Historical and Ask_Historical functions. Don't compare the two providers without also comparing the date, timestamp, and field definition.

QuoteMedia historical functions return NA for a weekend, market holiday, date before the contract was listed, or another date with no trading session. They don't automatically roll the request back to the previous trading day.

Use a date cell instead of typing a locale-dependent date as text. For example, prefer DATE(2026,8,14) or a cell that Excel recognizes as a date.

Understand zero, NA, and settlement

A numeric 0 is a valid bid, ask, volume, or change. It isn't the same as NA.

NA means that the function didn't receive a usable value. Common options causes include:

• A typo in the underlying or option root
• An expiration, strike, or call/put combination that doesn't identify a listed contract
• A historical date with no trading session or no stored contract data
• An expired contract used with a live streaming formula
• Missing options entitlement or incomplete exchange agreements
• A metric that the provider doesn't publish for that contract

AM- and PM-settled index options are distinct listed contracts. OptionSymbol doesn't select settlement with a separate argument. Retrieve the chain, match the exact root and expiration, and then use the returned contract symbol. Don't substitute the current index level for the contract's official settlement value.

Troubleshoot missing or unexpected values

1. Test the same function with a liquid, current option from the full chain.
2. Compare the exact root, expiration, type, and strike with the returned chain.
3. For streaming, confirm that streaming is on and test QM_Stream_Last("MSFT") or its mxls. equivalent.
4. If stock quotes work but every option fails, check the options entitlement and exchange agreements.
5. If only a historical date fails, try a confirmed trading day and verify that the contract existed then.
6. If a quote differs from a broker, compare bid, ask, last, timestamp, market session, and whether either source is delayed.

Read Fix QuoteMedia real-time data for connection and entitlement checks. Read Fix stale, delayed, or unexpected data before reporting a price difference.