Option Symbol
Builds the QuoteMedia option symbol format (@TICKER YYMMDD[C/P]00STRIKE) from the underlying ticker, expiration date, call/put flag, and strike price. This is the symbol the QM_ option contract functions require.
Format
@+ ticker padded to 6 charsYYMMDDexpiryC(call) orP(put)- strike x 1000, zero-padded to 8 digits
Syntax
=OptionSymbol(Symbol, ExpiryDate, CallPut, Strike)Parameters
The underlying stock ticker symbol, e.g. "AAPL"
Option expiration date, e.g. "2026-07-17" or a cell with a date
Option type: "Call"/"C" or "Put"/"P"
Strike price, e.g. 450 or 252.50
Returns
A QuoteMedia-format option symbol, e.g. "@AAPL 260717C00450000"
Examples
=OptionSymbol("MSFT","2021-07-23","P",252.50)=OptionSymbol("AAPL","2026-07-17","Call",450)=QM_Last(OptionSymbol("AAPL","2026-07-17","C",450))When to Use
Constructing the option symbol needed by QM_ contract functions (QM_Last, QM_Strike, QM_OpenInterest, etc.).
When NOT to Use
When you already have the option symbol, or when you want the full chain (use QM_GetOptionChain).
Common Issues & FAQ
Expiry date must be a valid date. Call/Put must start with C or P. Strike is the dollar strike (e.g. 252.50), not x1000.
Excel Templates Using Option Symbol
These ready-made MarketXLS templates call OptionSymbol() in their worksheet formulas. Open one to see the function working inside a complete model.
- NDX100 Options Matrix: Comprehensive Risk-Weighted Analysis for Optimal Option Selection across All
- SPXW Options
- Real-Time Analysis: Options with Highest Open Interest, Change, and Volume
- Option Chain Matrix
- ^SPX Real time Option Chain with SPXW
- Option Chain Excel Sheet (SPY Example)
- Template for Accessing Real-Time Data on US Stock Option Bids, Greeks, Open Interest, and Unusual Op
- Options Rollover Strategy Example Template
Related Formulas
More MarketXLS Options formulas you can use in the same worksheet:
- Options Change in OI Leaders
- Options Change in Volume Leaders
- Options Delta
- Options Gamma
- Options Implied Volatility
- Options OI Leaders
- Options Rho
- Options Theta
See OptionSymbol used in a complete workbook: Live Option Chain Data in Google Sheets: What GOOGLEFINANCE Covers and Where MarketXLS Picks Up
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today