Historical Chains

Use SF_OPTIONS_PRO() historical chains when you want to view a full calls, puts, or calls-and-puts chain from a previous trading date instead of the current market snapshot. This is useful for backtesting workflows, comparing old surface conditions, and reviewing how a chain looked ahead of an event.

For a complete function overview, parameter guide, and dataset map, see Options.

=SF_OPTIONS_PRO(symbol, dataType, expirationDate, strike, tradeDate, endDate, metrics, options)

• symbol is the underlying ticker symbol, for example "AAPL".

• dataType is usually "calls", "puts", or "calls&puts".

• expirationDate sets the option expiry you want to review in YYYY-MM-DD format.

• strike is optional for full chain requests, but required if you want to target one specific historical contract without using an OCC symbol.

• tradeDate sets the historical start date or snapshot date. Use YYYY-MM-DD format, an Excel date serial or Google Sheets date formatted cell.

• endDate is optional and can be used to request a historical date range. Use YYYY-MM-DD format, an Excel date serial or Google Sheets date formatted cell. See Contract & Strike Time-series for more details on building historical time-series requests.

• metrics selects the fields to return. Leave blank or use "all" to display all available fields. Chain together specific fields with & such as "contractSymbol&strike&mid&impliedVolatility&delta". See the full list of available metrics in the Metrics section below.

• options allows additional output formatting or sorting options. Options can be chained together with &.

Parameters

Symbol (symbol)

Use the underlying ticker symbol for a historical chain request, for example "AAPL". You can also pass a fully qualified OCC contract symbol such as "AAPL251219C00200000" when you want a specific contract and want SheetsFinance to infer the option type, expiration date, and strike automatically.

Data Type (dataType)

Use dataType to choose which side of the historical chain to return:

• "calls" returns call-side contracts only.

• "puts" returns put-side contracts only.

• "calls&puts" returns both sides together in one output.

• "strikes" also aliases to "calls&puts".

Expiration Date (expirationDate)

expirationDate sets the target historical expiry in YYYY-MM-DD format or as an Excel date serial. You do not need to match the exact listed expiry perfectly because the function will use the date entered or the next upcoming expiration date immediately after it.

Strike (strike)

Leave strike blank for a full historical chain. If you provide strike together with dataType and expirationDate, the function will return the matching historical contract for that expiry. If you provide strike but omit expirationDate, the function will return that strike across the available expiration dates for the underlying symbol.

Trade Date (tradeDate)

tradeDate is the historical moment-in-time date for the full chain snapshot. If you also supply endDate, it becomes the start date for a historical time-series range.

End Date (endDate)

endDate is used to create a historical time-series range when supplied together with tradeDate. It is only applied for a single contract or when both strike and expirationDate are supplied. When used, it sets the end of the historical range and must be after the tradeDate. See Contract & Strike Time-series for more.

Metrics (metrics)

The available historical chain metrics are:

• All ("all")

• Contract Symbol ("contractSymbol")

• Strike ("strike")

• Type ("type")

• Value ("value")

• Mid ("mid")

• Prev Close Value ("prevClose")

• Value Change ("change")

• Value Percent Change ("percentChange")

• Volume ("volume")

• Open Interest ("openInterest")

• Volume/OI Ratio ("volumeOIRatio")

• Bid ("bidPrice")

• Bid Size ("bidSize")

• Ask ("askPrice")

• Ask Size ("askSize")

• Implied Volatility ("impliedVolatility")

• Delta ("delta")

• Gamma ("gamma")

• Theta ("theta")

• Vega ("vega")

• Rho ("rho")

• Phi ("phi")

• Driftless Theta ("driftlessTheta")

• Bid IV ("bidIv")

• Ask IV ("askIv")

• Ext Value ("extValue")

• Residual Rate ("residualRate")

• SMV Vol ("smvVol")

• Ext SMV Vol ("extSmvVol")

• In The Money ("inTheMoney")

• Underlying Symbol ("underlyingSymbol")

• Stock Price ("stockPrice")

• Spot Price ("spotPrice")

• Expiration Date ("expirDate")

• Expiry Time of Day ("expiryTod")

• DTE ("dte")

• Trade Date ("tradeDate")

• Quote Date ("quoteDate")

• Snapshot Date ("snapShotDate")

• Snapshot Estimated Time ("snapShotEstTime")

• Updated ("updatedAt")

Options (options)

Use options for output formatting or request modifiers. Chain multiple options together with &.

Formatting options:

• "NH" removes the header row.

• "-" reverses the output order.

Value display options:

• "changeType=mid" calculates change values using the mid price.

• "changeType=value" calculates change values using the contract value.

Range filters:

• "range=ntm"

• "range=20ntm"

• "range=50ntm"

Output grouping:

• "groupBy=strike"

• "groupBy=expiration"

• "groupBy=tradeDate"

Examples

Example 1 - Historical chain with all metrics

Both calls and puts for AAPL with 2026-10-16 expiration as of the 2026-04-01 trading date.

=SF_OPTIONS_PRO("AAPL", "calls&puts", "2026-10-16", "", "2026-04-01")

Example 2 - Historical puts with select metrics

=SF_OPTIONS_PRO("AAPL", "puts", "2026-10-16", "", "2026-04-01", "", "contractSymbol&strike&mid&volume&openInterest")

Example 3 - Historical chain, select metrics and only near the money contracts

=SF_OPTIONS_PRO("AAPL", "calls&puts", "2026-10-16", "", "2026-04-01", "", "contractSymbol&type&strike&mid&delta&vega&tradeDate&stockPrice", "range=ntm")

Example 4 - Direct contract snapshot for a historical date

=SF_OPTIONS_PRO("AAPL261016C00200000", "", "", "", "2026-04-01", "", "tradeDate&contractSymbol&strike&mid&value&impliedVolatility&delta&stockPrice")