Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Calculate Total Open Interest for Options in Excel

The opt_TotalOpenInterestOptions function allows you to quickly retrieve the total open interest (OI) of options on a given underlying security directly in Excel. This function can filter options by option type (calls or puts), expiration date, and strike range. Use it to streamline your options analysis and swiftly compare open interest across various parameters—all without leaving Excel.

Why Use This Function?

  • Precisely filter your options by type (put, call, or both) for more meaningful OI data.
  • Narrow your search with optional expiration date and strike inputs to get highly specific OI results.
  • Retrieve the total open interest for complete option chains when your analysis calls for a broader scope.
  • Avoid manual data collection from multiple sources, saving time and reducing potential errors.
  • Combine with other MarketXLS functions to enrich your Excel-based trading models.

How to Use in Excel

=opt_TotalOpenInterestOptions(Underlying, [OptionType], [ExpirationDate], [Strike])
  1. Enter the function name in any cell in Excel.
  2. Provide the Underlying symbol (e.g., "AAPL").
  3. (Optional) Specify the OptionType as "CALL" or "PUT" (not case-sensitive). Leaving it blank or as "0" fetches both call and put data combined.
  4. (Optional) Provide the ExpirationDate. You can type the date or reference another cell. If left blank or passed as "0", no expiration filter is applied.
  5. (Optional) Indicate a Strike. Passing "0" means no strike-based restriction. Special: If a value < 1 is used, the function calculates a range around the underlying’s last price based on that fraction (for instance, 0.10 for ±10%).

When executed, this function fetches total open interest from MarketXLS’s backend. If the data is unavailable or there is a license issue, the function will return "NA" or a relevant message.

Parameters Explained

Parameter Description Example Values Notes
Underlying The base ticker symbol (e.g., for Apple, use "AAPL"). "AAPL" Required. Must be a valid symbol recognized by MarketXLS.
OptionType Defines whether to filter by calls or puts. If blank, both are included. Accepted values include "CALL" or "PUT". "CALL", "PUT", "0" Optional. Not case-sensitive. If passed incorrectly (e.g., "X"), you may see an error.
ExpirationDate The expiration date for your options filter. If blank or "0", all expiration dates are included. "08/18/2023", "12/15/2023" Optional. MarketXLS internally converts the date to a "YYYY-MM-DD" string. If the date can’t be parsed or is invalid, the total open interest defaults to all dates.
Strike A specific strike price (or a fractional range) for filtering. If blank or "0", no strike filter is applied. "150", "=A2", "0.10" Optional. If < 1, the function uses a +/- range based on the underlying’s last price. For example, "0.10" is ±10% of the last price. If you provide a normal strike (e.g., "150"), that exact strike (or range if you also pass a fraction) is used. If invalid, "NA" is returned.

Example Usage

Basic Examples

  1. Retrieve total OI for all options on AAPL (no filters): =opt_TotalOpenInterestOptions("AAPL")
    • Returns the sum of open interest for both calls and puts across all strikes and expirations.

  2. Retrieve total OI for only CALL options on TSLA for a given expiration: =opt_TotalOpenInterestOptions("TSLA", "CALL", "12/15/2023")
    • Sums the open interest for all TSLA call options expiring on December 15, 2023.

  3. Retrieve total OI for only PUT options on MSFT with no expiration filter: =opt_TotalOpenInterestOptions("MSFT", "PUT")
    • Ignores strike and expiration date for all put options on MSFT.

Advanced Scenarios

  1. Specifying a Strike for Targeted Analysis
    =opt_TotalOpenInterestOptions("AMD", "CALL", "10/20/2023", "100")
    • Returns the total OI for AMD call options at a 100 strike expiring on October 20, 2023.

  2. Using a Fractional Strike for Range Analysis
    =opt_TotalOpenInterestOptions("SPY", "", "09/15/2023", "0.10")
    • Interprets 0.10 as a ±10% range around the last traded price of SPY, then sums OI for both calls and puts within that strike range for the specified date.

  3. Pulling Data Without OptionType or Expiration Filter but a Strike Range
    =opt_TotalOpenInterestOptions("AMZN", , ,"90")
    • Leaves OptionType blank, meaning both calls and puts.
    • No expiration date filter—includes all expirations.
    • Strike set to "90" covers that exact strike.

  4. Combining with Other Excel Functions
    • For instance, reference the result in an IF statement or a chart to show how OI changes over multiple expirations.

Common Questions and Troubleshooting

  1. Why do I get "NA"?
    • This occurs if MarketXLS’s backend cannot return valid data or if your input parameters are invalid (e.g., an incorrect date or strike format).

  2. My data sometimes shows "Refreshing". Why?
    • The API may be temporarily updating or queued. Once the refresh completes, the function will return the correct OI value.

  3. Handling Expiration Dates as Excel Dates or Strings
    • If your date cell references are in Excel’s date format, the function internally converts them to the required format. Ensure the cell truly contains a date.

  4. What happens if I pass in an OptionType string other than "CALL" or "PUT"?
    • The function attempts to parse it. If it can’t match "CALL" or "PUT," it treats it as "0", returning combined call and put data.

Remember:

  • Carefully format the date parameter so it’s recognized correctly by MarketXLS.
  • Use fractional strike values (<1) only when you want a percentage range around the underlying’s price.
  • If you do not need a specific filter, leaving the parameter blank or setting “0” can simplify your analysis.

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Total Open Interest and Other Financial Formulas
How does MarketXLS work?