MarketXLS

Total Open Interest

Returns the total options open interest for an underlying stock. Open interest represents the total number of outstanding option contracts.

Parameters

Parameter Required Default Description
Underlying Yes - Stock ticker symbol
OptionType No all 'all', 'call', or 'put'
ExpirationDate No all Specific date or 'all'
Strike No all Specific strike or blank

Understanding Open Interest

  • OI = total outstanding contracts
  • High OI indicates significant market positioning
  • OI accumulates over time
  • OI decreases when positions are closed

Notes

  • Open interest is updated end of day by OCC
  • OI can indicate support/resistance levels
  • Large OI at strikes = market maker hedging activity

Syntax

=opt_TotalOpenInterestOptions(Underlying, [OptionType], [ExpirationDate], [Strike])
Excel Desktop (Windows)

Parameters

underlyingstringRequired
optionTypestring
expirationDatedate
strikestring

Returns

number

Total options open interest

Examples

=opt_TotalOpenInterestOptions("AAPL")
All AAPL options OI
=opt_TotalOpenInterestOptions("AAPL", "call")
AAPL call OI only
=opt_TotalOpenInterestOptions("AAPL", "put")
AAPL put OI only
=opt_TotalOpenInterestOptions("SPY", "all", "2024-01-19")
SPY OI for specific expiry
=opt_TotalOpenInterestOptions("TSLA", "call", "all", 250)
TSLA call OI at $250 strike

When to Use

  • Analyzing market positioning
  • Identifying support/resistance from options
  • Measuring overall options market interest
  • Comparing call vs put positioning
  • Analyzing OI by expiration

When NOT to Use

Scenario Use Instead
Need volume opt_TotalVolumeOptions()
Need top contracts TopOptionsByOpenInterest()
Need put/call OI ratio opt_PutCallOIRatio()
Need historical OI opt_TotalOpenInterestOptionsHistorical()

Common Issues & FAQ

Why is OI different from volume?

Volume = contracts traded today (resets daily). OI = total outstanding contracts (accumulates over time).

When is OI updated?

Open interest is calculated and published by the OCC at end of each trading day.

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.

Excel Templates Using Total Open Interest

These ready-made MarketXLS templates call opt_TotalOpenInterestOptions() in their worksheet formulas. Open one to see the function working inside a complete model.

Related Formulas

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

See opt_TotalOpenInterestOptions used in a complete workbook: MCP Options Order Flow Server: Read Options Flow in Claude and Excel (2026)

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?

Related Functions