To pull a live option chain into an Excel sheet, install the MarketXLS add-in and type =QM_GetOptionChain("AAPL") in an empty cell. The formula returns a table of current, non-expired call and put contracts for the ticker, with strikes, expirations, and prices, and spills into the cells below and to the right. On the Microsoft 365 add-in (Mac and Excel for the web) the formula is =mxls.QM_GetOptionChain("AAPL"). Options data is end-of-day on the Standard plan and real-time streaming on the Advanced and Business plans.
How to pull an option chain into Excel with MarketXLS
- Pick an empty area with room for a large table. If Excel shows
#SPILL!, clear the cells blocking the result. - Enter
=QM_GetOptionChain("AAPL")(replace AAPL with your ticker, or reference a cell). - Use a filtered chain when you need fewer rows:
QM_GetOptionChainActive(excludes zero-volume contracts),QM_GetOptionChainAtTheMoney,QM_GetOptionChainWeeklies, orQM_GetOptionChainMonthlies. - For live updates on specific contracts, put the option symbol in a cell and use
=QM_Stream_Last(A2). Each streamed contract uses one live symbol subscription, and options tracking is limited to 300 symbols.
Chain formulas return a snapshot that updates when Excel recalculates. See options data in the docs for filtered chains, single contracts, and historical chains, and real-time option pricing in Excel for plan details.
How to read option chain data
An option chain lists every call and put contract for an underlying, one row per strike and expiration. The columns below are the ones traders read first.
Volatility
Implied volatility (IV) is the volatility of the underlying that the option's current price implies. Historical volatility measures how much the underlying's price has actually varied, calculated as the standard deviation of its returns. An option chain shows IV per contract; higher IV means a higher premium for the same strike and expiration.
Delta
Delta measures how much an option's price changes for a $1 move in the underlying. Call deltas range from 0 to 1 and put deltas from 0 to -1. A call with a delta of 0.50 gains about $0.50 when the stock rises $1. Traders use delta to size positions and to estimate how an option will respond to price moves.
Strike Price
The strike price is the price at which the option holder can buy (call) or sell (put) the underlying. A chain lists strikes above and below the current price, and comparing strike to the underlying's price tells you whether a contract is in, at, or out of the money.
Premium
The premium is the price of the option contract, shown in the chain as bid, ask, and last. It depends on the underlying's price relative to the strike, time to expiration, implied volatility, and interest rates. Standard US equity options cover 100 shares, so the cost of one contract is the quoted premium times 100.
Calls & Puts
An option chain lists both calls and puts. A call gives the buyer the right to buy the underlying at the strike price. A put gives the buyer the right to sell the underlying at the strike price.
Expiration Date
The expiration date is the last day the option can be exercised. Options that are out of the money at expiration expire worthless; in-the-money options have intrinsic value. Chains group contracts by expiration (weekly, monthly, quarterly).
Risk/Reward Profile
The chain gives you the inputs to estimate a trade's risk and reward: strike, premium, delta, and expiration. To see a position's profit and loss across prices, enter those values in the free Options Profit Calculator. This article is educational and is not trading advice.
Related reading
Marketxls New Release Version 9.1
Understanding Open Interest in Options Trading
Get an Edge With Spy Options Chain
How To Find The Most Active Options (Marketxls’S Option Scanner)
Brand New Menu & Stock Rank Functions – (New Release 9.3.4.6)