QM_GetOptionChainInTheMoney to Retrieve In-The-Money Option Chains

The QM_GetOptionChainInTheMoney function in MarketXLS helps you pull in-the-money option quotes for a given ticker symbol directly into Excel. Rather than manually searching for individual calls and puts that are in the money, you can simply specify the symbol, and MarketXLS will display the relevant option chain data in your spreadsheet. You also get Greek values (delta, gamma, theta, etc.), which can be crucial for more advanced options analysis. This saves time, reduces manual errors, and helps you visualize in-the-money options at a glance, including potentially expired contracts.

Why Use This Function?

• Quickly view all in-the-money options for a given stock or ETF.
• Include important greeks and option metrics directly in Excel.
• Analyze and compare calls/puts that are already at or above intrinsic value.
• Simplify your workflow by avoiding multiple tools/webpages—everything is in Excel.
• Handy for creating trading strategies based on in-the-money options.
• Ideal for historical or research purposes because expired options are included.

How to Use in Excel

=QM_GetOptionChainInTheMoney("Symbol")

1. In any worksheet cell, type the formula above.
2. Replace "Symbol" with the stock (or ETF) ticker symbol you wish to retrieve in-the-money option data for (e.g., "AAPL", "TSLA").
3. Press Enter. A table of in-the-money options will populate starting from your active cell, showing columns such as strike, bid, ask, contract high/low, open interest, and various Greeks.
4. Note that rows and columns will auto-expand down and right of the formula cell. Make sure sufficient space is available.

The function automatically retrieves:
• Calls and puts that are currently in the money.
• Expired contracts are also included (inclExpired = True).
• Detailed metrics including last price, volume, delta, gamma, theta, vega, implied volatility, etc.

If there is no available data for the selected symbol, you will see "No Data Retrieved."

Parameters Explained

There is only a single required parameter, Symbol.

Example Usage

Basic Examples

1. =QM_GetOptionChainInTheMoney("MSFT")
   • Retrieves all in-the-money options for Microsoft.
   • Results display rows for each option contract, including strike, call/put indicator, last price, open interest, and Greeks.

2. =QM_GetOptionChainInTheMoney("TSLA")
   • Quickly see Tesla’s in-the-money calls and puts.
   • Useful for analyzing open interest levels or potential deep-in-the-money leaps.

In each case, the data will begin populating from the cell where you place the formula, expanding downward and to the right.

Advanced Scenarios

• Combining with Other Functions:
If you want to automatically calculate theoretical profit/loss for these in-the-money options, you can place Excel formulas in adjacent columns (e.g., referencing the output from =QM_GetOptionChainInTheMoney("AAPL") to run your own custom analyses).

• Historical Research:
Because the function includes expired contracts, you can evaluate past in-the-money options for backtesting strategies. For instance, if you apply the function to a ticker that has older traded options, it will also list those expired options.

• Integrate With Macros:
Advanced users can create VBA macros to refresh the data daily or copy the in-the-money chain to a separate sheet for additional modeling.

Common Questions and Troubleshooting

1. Why don’t I see any data?
   • Ensure that the symbol is valid. If it’s delisted or the data provider doesn’t have information on it, “No Data Retrieved” may appear.
   • Also, confirm that your MarketXLS subscription/license is active. An invalid license can produce error messages.

2. Why are expired contracts shown?
   • The function purposely sets inclExpired = True to help you reference historical contracts.

3. Can I specify only calls or only puts?
   • Not with QM_GetOptionChainInTheMoney alone. The function is designed to fetch both calls and puts that are in the money. Filter them in Excel if desired.

4. Is there a way to display out-of-the-money or all options?
   • This specific function only displays in-the-money options. MarketXLS has separate functions for other option chain parameters.

5. What if I need more advanced formatting or rearranging?
   • The function prints directly into your spreadsheet. Re-sort or filter the returned data, or move it elsewhere once pulled in.

By leveraging QM_GetOptionChainInTheMoney in Excel with MarketXLS, you can seamlessly integrate up-to-date in-the-money options data into your trading research or portfolio management workflow. This function’s fresh data and convenient display of vital metrics (including Greeks) make analyzing options faster and more efficient—enhancing decision-making for your options strategies.