Get Structured Option Chain Data (with Greeks) in Excel

The fn_00986 (internally named QM_GetOptionChainStructured) feature in the MarketXLS Excel Add-in allows you to seamlessly retrieve comprehensive option chain data, complete with Greeks, directly into your Excel worksheet. This saves time, helps you analyze multiple strikes at once, and simplifies your options research without leaving your spreadsheet.

With this function, you can:
• Instantly populate a structured table of calls, puts, expiration dates, strikes, and even the Greeks (?, ?, ?, ?, Vega, Implied Volatility).
• Customize your views by selecting different underlying symbols (e.g., AAPL, SPY) to see real-time or delayed quotes (depending on your subscription).
• Simplify tracking and comparing options strategies for numerous expiration dates in one place.

Why Use This Function?

• Gain a full snapshot of a symbol’s option chain in a single step.
• Quickly compare call and put strikes at once.
• Analyze implied volatility (IV) and Greek values without scripting or external tools.
• Save time manually gathering data from multiple sources—MarketXLS fetches it for you.
• Ideal for options trading strategies, risk assessment, and scenario planning in real-world market conditions.

How to Use in Excel

Below is the basic usage for fn_00986, which is accessed in Excel by calling QM_GetOptionChainStructured. Simply provide the ticker symbol you wish to analyze. MarketXLS will insert the results as a structured table starting from your active cell.

=QM_GetOptionChainStructured("AAPL")

1. Select a cell in your worksheet where you want the option chain to start.
2. Type the function =QM_GetOptionChainStructured("SYMBOL") (e.g. "AAPL").
3. Press Enter. MarketXLS will populate the chain with columns for strike, contract volume, Greeks, and more, beneath and to the right of your active cell.

Note: The function automatically includes Greeks by default.

Parameters Explained

(Internally, the function also sets “greeks=True,” “inclExpired=False,” “money=All,” and “structured=True.” These are not user-configurable parameters from Excel but are crucial to returning a complete, formatted option chain with Greek values.)

Example Usage

Basic Examples

1. AAPL Option Chain:
   =QM_GetOptionChainStructured("AAPL")
   • Automatically displays all calls and puts for AAPL’s next available expirations.
   • Includes columns like strike, bid/ask, open interest, volume, and the Greeks.

2. SPY Option Chain:
   =QM_GetOptionChainStructured("SPY")
   • Shows structured calls and puts for the SPY ETF.
   • Useful for analyzing broad market option strategies.

Both examples populate the data starting from your chosen cell, extending downward. You’ll see calls and puts aligned by strike for easier scanning.

Advanced Scenarios

• Multi-Worksheet Analysis: If you want separate sheets for different underlyings, simply go to a new worksheet tab, select the top-left cell, and enter the formula for another symbol. This helps segregate option data for multiple symbols.
• Intraday Updates: With a subscription that supports real-time or frequent updates, the option chain can reflect near real-time changes.
• Strategy Evaluation: Combine these option chain outputs with other Excel functions to model your payoff diagrams or track risk across multiple strikes.

Common Questions and Troubleshooting

• “What if no data appears?”
– Make sure your MarketXLS license is valid and the symbol is spelled correctly. The function returns “No Data Retrieved” if the symbol is invalid or if there’s a data issue.

• “Can I include expired options?”
– In this structured function, expired options are set to “False” in the background. Currently, this cannot be changed in the Excel call.

• “Why am I seeing #NULL cells?”
– Blank cells or invalid data points from the provider are handled by placing an #NULL error. This ensures Excel recognizes missing data properly.

• “Does it override existing spreadsheet data?”
– The function writes below and to the right of the active cell. Select an empty area or a new worksheet to avoid overwriting data.

Remember:

• Be mindful of selecting a cell range that can accommodate the expanded option chain columns and rows.
• Combine this data with other MarketXLS functions to track broad market moves, build watchlists of different underlyings, or monitor Greeks for your active positions.
• Use real-world US market symbols like AAPL, MSFT, SPY, and others for your analysis.

Leverage fn_00986 (QM_GetOptionChainStructured) to enrich your Excel-based trading research and options strategies with comprehensive, well-organized data, all inside your familiar spreadsheet environment.