Excel Formula Reference: Options & Greeks
Excel Price Feed enables you to stream Interactive Brokers option Greeks, implied volatility and related option computation fields directly into your Excel spreadsheet via simple Excel formulas.
Data Subscriptions
Data availabilty is subject to your current IB data subscriptions.
For example: To receive live, streaming US options data, you need the OPRA (US Option Exchanges) Level 1 feed, which currently costs $1.50/month for non-professional accounts.
Overview
Interactive Brokers provides option Greeks through option computation ticks. Excel Price Feed exposes these as named fields that you pass into the market data streaming formulas.
Fields are grouped by the computation source:
- Model - computed from Interactive Brokers' model option computation
- Bid - computed from the bid-side option computation
- Ask - computed from the ask-side option computation
- Last - computed from the last-side option computation
For each source, Excel Price Feed provides Delta, Gamma, Theta, Vega, implied volatility (IV), option model price, underlying price and present value of dividends.
Excel Formulas
Formulas can include either the Contract Id or full details of the option.
For example these two formulas are equivalent:
=EPF.IB.Stream_MarketData_ConId("ModelOptPrice", "765596868")=EPF.IB.Stream_MarketData("ModelOptPrice","QQQ","OPT","USD","SMART","","20271217","700","","C","QQQ")For more information on the market data Excel formulas: Market Data Formulas
You can also Use the contract search screen to find option contracts.
All Greeks Fields
This is the list of all currently available Greeks and related option computation fields.
Field names are not case sensitive and spaces between words are optional, so "ModelDelta", "model delta" and "Model Delta" are all equivalent and will work.
Model
| Field | Description |
|---|---|
| ModelDelta | Option delta from model option computation. |
| ModelGamma | Option gamma from model option computation. |
| ModelTheta | Option theta from model option computation. |
| ModelVega | Option vega from model option computation. |
| ModelIV | Implied volatility from model option computation. |
| ModelOptPrice | Option model price from model option computation. |
| ModelUndPrice | Underlying price from model option computation. |
| ModelPvDividend | Present value of dividends from model option computation. |
Bid
| Field | Description |
|---|---|
| BidDelta | Option delta from bid option computation. |
| BidGamma | Option gamma from bid option computation. |
| BidTheta | Option theta from bid option computation. |
| BidVega | Option vega from bid option computation. |
| BidIV | Implied volatility from bid option computation. |
| BidOptPrice | Option model price from bid option computation. |
| BidUndPrice | Underlying price from bid option computation. |
| BidPvDividend | Present value of dividends from bid option computation. |
Ask
| Field | Description |
|---|---|
| AskDelta | Option delta from ask option computation. |
| AskGamma | Option gamma from ask option computation. |
| AskTheta | Option theta from ask option computation. |
| AskVega | Option vega from ask option computation. |
| AskIV | Implied volatility from ask option computation. |
| AskOptPrice | Option model price from ask option computation. |
| AskUndPrice | Underlying price from ask option computation. |
| AskPvDividend | Present value of dividends from ask option computation. |
Last
| Field | Description |
|---|---|
| LastDelta | Option delta from last option computation. |
| LastGamma | Option gamma from last option computation. |
| LastTheta | Option theta from last option computation. |
| LastVega | Option vega from last option computation. |
| LastIV | Implied volatility from last option computation. |
| LastOptPrice | Option model price from last option computation. |
| LastUndPrice | Underlying price from last option computation. |
| LastPvDividend | Present value of dividends from last option computation. |
Field Aliases
For convenience, some shorter aliases map to the Model fields:
| Alias | Resolves To |
|---|---|
| Delta | ModelDelta |
| Gamma | ModelGamma |
| Theta | ModelTheta |
| Vega | ModelVega |
| IV | ModelIV |
| ImpliedVol | ModelIV |
| ImpliedVolatility | ModelIV |
| OptPrice | ModelOptPrice |
| UndPrice | ModelUndPrice |
For example, the following are equivalent:
=EPF.IB.Stream_MarketData_ConId("Delta","765596868")
=EPF.IB.Stream_MarketData_ConId("ModelDelta","765596868")
Related use case
For an overview of streaming Interactive Brokers holdings, market data and option Greeks into Excel, see the Interactive Brokers Holdings & Market Data use case.