MarketXLS

Top Options Contract By Open Interest

Returns the option contracts with the highest open interest for a given underlying symbol. Open interest represents the total number of outstanding option contracts.

Parameters

Parameter Required Default Description
Symbol Yes - Underlying stock ticker (e.g., AAPL, TSLA)
NumberOfOptions No 10 Number of top contracts to return

Understanding Open Interest

  • Open Interest = total outstanding contracts not yet closed
  • High OI indicates significant market positioning
  • OI accumulates over time as positions are opened
  • OI decreases when positions are closed

Notes

  • Returns an array that spills into multiple cells in Excel
  • Results include both calls and puts
  • High OI often indicates key support/resistance levels

Syntax

=TopOptionsByOpenInterest(Symbol, [NumberOfOptions])
Excel Desktop (Windows)

Parameters

symbolstringRequired

Underlying stock ticker symbol

numberOfOptionsnumber

Number of top contracts to return

Returns

array

Array of top option contracts sorted by open interest

Examples

=TopOptionsByOpenInterest("AAPL")
Top 10 AAPL options by OI
=TopOptionsByOpenInterest("AAPL", 5)
Top 5 AAPL options by OI
=TopOptionsByOpenInterest("TSLA", 20)
Top 20 TSLA options by OI
=TopOptionsByOpenInterest("SPY", 10)
Top 10 SPY options by OI
=TopOptionsByOpenInterest(A1)
Symbol from cell reference

When to Use

  • Identifying key option positions in the market
  • Finding support/resistance levels from options
  • Analyzing market maker positioning
  • Spotting significant option accumulation
  • Building options flow analysis tools

When NOT to Use

Scenario Use Instead
Need top by volume TopOptionsByVolume()
Need total symbol OI opt_TotalOpenInterestOptions()
Need specific contract price Option_Last_Price()
Need put/call OI ratio opt_PutCallOIRatio()

Common Issues & FAQ

Why is open interest different from volume?

Volume = contracts traded today. Open interest = total outstanding contracts. OI changes only when new positions are opened or existing ones are closed.

How do I use OI for support/resistance?

Large put OI at a strike can act as support. Large call OI at a strike can act as resistance. Market makers hedge these positions, creating price magnetism.

When does OI update?

Open interest is updated at end of day by the OCC. Intraday data shows previous day's OI.

Related Formulas

More MarketXLS Options formulas you can use in the same worksheet:

See TopOptionsByOpenInterest used in a complete workbook: Options Profit Calculator: Excel-Based Options P&L Analysis With Real-Time Data

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 Top Options Contract By Open Interest and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions