Option Prices in Google Sheets: Pulling Live Quotes, Chains, and Greeks with GOOGLEFINANCE and MarketXLS

Google Sheets cannot pull option prices on its own: GOOGLEFINANCE has no attribute for an option strike, expiration, implied volatility, or Greeks. To get option prices in Google Sheets, install the MarketXLS add-on and use formulas such as =QM_Last(OptionSymbol("AAPL","2026-05-30","Call",230)) for one contract or =QM_GetOptionChainActive("AAPL") for a chain. In Google Sheets the values refresh on demand rather than streaming. Options data is end-of-day on the MarketXLS Standard plan and real-time on Advanced and Business.

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan.

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

This guide covers what GOOGLEFINANCE can and cannot do, the MarketXLS option formulas, a template with a pricing dashboard, live chain, Greeks, and strategy sheets, and when Excel desktop streaming is the better fit.

What Google Sheets Gives You Out of the Box

GOOGLEFINANCE is the only first-party way to pull market data into a Google Sheet without an add-on. Its full signature is =GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]). The attribute argument is a closed list: "price", "priceopen", "high", "low", "volume", "marketcap", "tradetime", "datadelay", "volumeavg", "pe", "eps", "high52", "low52", "change", "beta", "changepct", "closeyest", "shares", and "currency" for equities, plus a handful of historical and mutual-fund options. There is no "optionprice", no "delta", no "iv", and no way to request a specific option contract.

A few realistic examples of what GOOGLEFINANCE handles well:

These are the right tool when you need a quick equity dashboard, a watchlist, a portfolio cost-basis tracker, or a currency converter. For all of those, GOOGLEFINANCE is free.

Where Option Prices in Google Sheets Hit a Wall

GOOGLEFINANCE cannot return any of the following, with any combination of arguments:

Three common workarounds exist, and each has problems:

1. Custom Apps Script functions that fetch a public options page and parse the HTML. This is a scraping pattern that violates the terms of service of the underlying site, is fragile to layout changes, and risks IP blocking of your sheet.
2. Third-party Apps Script libraries that wrap an unofficial endpoint. Same problems, plus you are trusting an unknown library with your sheet.
3. Manual paste-and-pray. Copying a chain by hand at 9:31 AM Eastern is not a workflow, it is a chore.

A licensed data add-on avoids these problems. MarketXLS provides a Google Sheets add-on (refresh on demand) and an Excel desktop add-in (streaming on the Advanced and Business plans). Google Sheets uses the same pricing as regular MarketXLS. The rest of this guide covers the MarketXLS option formulas.

How MarketXLS Extends a Spreadsheet for Options

MarketXLS has 1000+ functions, and the ones for option prices fall into five groups:

1. Equity context for the underlying: price, beta, sector, dividend yield.
2. Implied volatility metrics on the underlying: 30-day at-the-money IV, IV rank, IV percentile.
3. Single-contract pricing: last, bid, ask, volume, open interest by option symbol.
4. Full and filtered option chains: active, near-term, at-the-money, weekly, in/out of the money slices.
5. Greeks and theoretical value: delta, gamma, theta, vega, and Black-Scholes price.

These are the MarketXLS formulas used throughout the template. Some functions differ by platform, so confirm availability for Google Sheets on /formulas:

=QM_Last("AAPL")                              -> last equity price
=Beta("AAPL")                                 -> beta vs market
=Sector("AAPL")                               -> sector name
=ForwardAnnualDividendYield("AAPL")           -> forward dividend yield
=ImpliedVolatility("AAPL")                    -> 30-day ATM IV
=ImpliedVolatilityRank1Y("AAPL")              -> IV rank over the last year
=IsOptionable("AAPL")                         -> "Yes" if options listed
=OptionSymbol("AAPL","2026-05-30","Call",230) -> build option symbol
=QM_Last(OptionSymbol("AAPL","2026-05-30","Call",230)) -> last option price
=QM_GetOptionChainActive("AAPL")              -> full active chain spill
=QM_GetOptionChainAtTheMoney("AAPL")          -> ATM only
=QM_GetOptionChainNearTerm("AAPL")            -> nearest expiry
=QM_GetOptionChainWeeklies("AAPL")            -> weekly expirations only
=QM_GetOptionChainOutOfTheMoney("AAPL")       -> OTM only
=QM_GetOptionQuotesAndGreeks("AAPL")          -> chain with Greeks per row
=QM_GetOptionMarketStats("AAPL")              -> aggregate option market stats
=OPT_Delta(StockPrice, OptionPrice, Expiry, Type, Strike)
=OPT_Gamma(StockPrice, OptionPrice, Expiry, Type, Strike)
=OPT_Theta(StockPrice, OptionPrice, Expiry, Type, Strike)
=OPT_Vega(StockPrice, OptionPrice, Expiry, Type, Strike)
=BlackScholesOptionValue(OptionSymbol, RiskFreeRate)

Formula documentation: QM_Last, Beta, Sector, ForwardAnnualDividendYield, ImpliedVolatility, ImpliedVolatilityRank1Y, IsOptionable, OptionSymbol, QM_GetOptionChainActive, QM_GetOptionChainAtTheMoney, QM_GetOptionChainNearTerm, QM_GetOptionChainWeeklies, QM_GetOptionChainOutOfTheMoney, QM_GetOptionQuotesAndGreeks, QM_GetOptionMarketStats, OPT_Delta, OPT_Gamma, OPT_Theta, OPT_Vega, BlackScholesOptionValue

That block covers every gap in the GOOGLEFINANCE list above.

A Working Pricing Dashboard

The template that ships with this post (download links below) builds a six-sheet workbook for option pricing. The first real sheet is the Pricing Dashboard, which scores a watchlist on whether it is currently a candidate to sell premium against or to buy options on.

Inputs (yellow cells) are portfolio size, max risk per trade, and the minimum IV rank you want before you sell premium. Everything else is a formula. The eight-row watchlist in the sample template (AAPL, MSFT, NVDA, SPY, QQQ, TSLA, AMD, META) is just a starting point; the live template version drops =QM_Last, =ImpliedVolatility, =ImpliedVolatilityRank1Y, =IsOptionable, =Beta, and =Sector into the row so every metric refreshes on recalculation.

The premium-tilt column is a single IF that classifies each name:

=IF(E8>=$B$4,"Sell premium candidate",IF(E8>=20,"Watch","Buy options or skip"))

Where E8 is IV rank and $B$4 is the minimum-IV-rank input. The threshold is yours to set; the formula screens the whole watchlist in one pass.

The Live Option Chain Sheet

The Live Option Chain sheet pulls chains that GOOGLEFINANCE cannot. It has a single yellow input cell (the underlying ticker) and five spill formulas underneath:

=QM_GetOptionChainActive(B3)        -> every active strike, calls + puts
=QM_GetOptionChainAtTheMoney(B3)    -> ATM strikes only
=QM_GetOptionChainNearTerm(B3)      -> nearest expiration
=QM_GetOptionChainWeeklies(B3)      -> weekly contracts
=QM_GetOptionMarketStats(B3)        -> aggregate option volume, OI, IV

Formula documentation: QM_GetOptionChainActive, QM_GetOptionChainAtTheMoney, QM_GetOptionChainNearTerm, QM_GetOptionChainWeeklies, QM_GetOptionMarketStats

Each of those spills a table. You can change the ticker in B3 and every section refreshes. There is no equivalent in native Google Sheets.

The columns returned per row include the option symbol, expiration, type, strike, bid, ask, last, volume, open interest, and implied volatility for that contract.

Greeks Without a Trading Terminal

The Greeks sheet computes delta, gamma, theta, vega, and a Black-Scholes value for each contract you list, using the per-contract OPT_ functions. You drop an underlying, expiration, type, and strike into rows. The template then computes:

Stock Last      = QM_Last(A5)
Option Last     = QM_Last(OptionSymbol(A5,B5,C5,D5))
Delta           = OPT_Delta(E5,F5,B5,C5,D5)
Gamma           = OPT_Gamma(E5,F5,B5,C5,D5)
Theta           = OPT_Theta(E5,F5,B5,C5,D5)
Vega            = OPT_Vega(E5,F5,B5,C5,D5)
Theoretical     = BlackScholesOptionValue(OptionSymbol(A5,B5,C5,D5), $B$2)

Formula documentation: QM_Last, OptionSymbol, OPT_Delta, OPT_Gamma, OPT_Theta, OPT_Vega, BlackScholesOptionValue

$B$2 is your risk-free rate input. Now you have a side-by-side of the market option price and the theoretical Black-Scholes value, with all four primary Greeks, in a spreadsheet that you fully control.

That comparison is worth pausing on. When QM_Last on the option diverges from BlackScholesOptionValue, the gap can reflect a stale quote, an event such as earnings that the model does not know about, or a real mispricing. Having both numbers in the same row makes the gap visible.

Strategy Builder: Covered Calls and Cash-Secured Puts

The Strategy Builder sheet models two common premium-selling structures: the covered call and the cash-secured put. For each row you provide ticker, strike, expiration, and type. The template computes the live stock price, the live option price, the premium as a percent of underlying, the static yield versus the strike, and the forward dividend yield for context.

Premium %          = F5/C5
Static Yield       = F5/D5
Forward Div Yield  = ForwardAnnualDividendYield(B5)

Formula documentation: ForwardAnnualDividendYield

This is educational scaffolding only. The point of the sheet is to make it cheap to ask "what would this trade look like" across a watchlist before you ever click into a broker. Note: nothing here is a trade recommendation, none of the strikes or expirations in the file are picks, and the numeric examples in the sample workbook are static snapshots, not live quotes. Always verify in your broker before trading.

Google Sheets vs MarketXLS: Capability Side by Side

The main difference is streaming. Google Sheets does not stream. MarketXLS inside Google Sheets refreshes on demand, which suits a daily or hourly snapshot, not for an open position you are actively managing. In Excel desktop, the QM_Stream_ functions update automatically while streaming is on (Advanced and Business plans). Streaming uses one live symbol subscription per option contract, and options tracking is limited to 300 symbols. If you are a financial advisor or analyst running scheduled spreadsheets, Google Sheets refresh-on-demand is often plenty.

Download the Templates

Download the templates:

• - Pre-filled with snapshot data so you can see exactly what each sheet looks like before connecting.
• - Live formulas. Drop a ticker in the yellow input cell and every dashboard recalculates.

Both files include a "MarketXLS Functions Used in This Sheet" box at the bottom of every tab so you can see exactly which formulas drive which numbers, and copy the right one into your own workbook.

Setting It Up: Google Sheets or Excel

The right setup depends on which spreadsheet you use and whether you need streaming.

Path 1: Google Sheets. Use GOOGLEFINANCE for equities and currencies, then install the MarketXLS add-on for options data. Values refresh on demand rather than streaming. This is the right path for analysts, advisors, and anyone running scheduled reports.

Path 2: Excel. Use the MarketXLS Excel desktop add-in on Windows. You get the same formulas plus streaming (Advanced and Business plans) and a ribbon with dialog boxes that build formulas for you. On Mac, use the Microsoft 365 add-in with the mxls. prefix. This is the right path for active options traders, derivatives desks, and anyone who needs Greeks updating intraday.

Either path uses the same template files above. The difference is in refresh cadence, not in formulas.

Frequently Asked Questions

Can GOOGLEFINANCE return option prices in any form?

No. GOOGLEFINANCE only supports the closed list of attributes Google has published, none of which target option contracts. There is no signature that takes a strike or expiration. The attribute list is hard-coded to equities, ETFs, mutual funds, indexes, and currency pairs. For options data inside Google Sheets you need an add-on with an options data feed.

Why does MarketXLS work inside Google Sheets at all? Is it limited?

MarketXLS provides a Google Sheets add-on that exposes its functions inside Sheets. The main difference from Excel is that Google Sheets does not support streaming, so values refresh on demand. Some functions differ by platform; check /formulas. For scheduled reports and daily snapshots this is usually enough. Active options traders generally prefer the Excel desktop experience for the streaming Greeks.

What is IV rank and why does it matter for option prices?

Implied volatility rank shows where current implied volatility sits between its 52-week low and high: (current IV minus 52-week low) divided by (52-week high minus 52-week low). IV percentile is a different measure: the share of days in the past year with lower IV. An IV rank of 0 means the underlying's implied volatility is at a one-year low, and 100 means it is at a one-year high. Option sellers (covered calls, cash-secured puts, credit spreads) often look for elevated IV rank, because premiums are richer. Option buyers often prefer lower IV rank. =ImpliedVolatilityRank1Y("AAPL") is the MarketXLS formula. There is no GOOGLEFINANCE equivalent.

Can I get historical option chains?

Not in vanilla Google Sheets. In MarketXLS, =OPT_HistoricalOptionChain(Ticker, Date) returns the option chain for a past date, which is useful for backtesting strategies, studying earnings reactions, and reconstructing past option pricing.

What option symbol format does MarketXLS use?

MarketXLS per-contract functions use the QuoteMedia option symbol, which follows the OCC layout: @, the root symbol padded to 6 characters, expiration date (YYMMDD), call/put indicator, and strike price times 1,000 padded to 8 digits (for example @AAPL 260717C00450000). You rarely have to type one. The OptionSymbol(Ticker, Expiry, Type, Strike) helper builds it from readable arguments, and you can wrap it inside any function that takes a symbol, such as =QM_Last(OptionSymbol("AAPL","2026-05-30","Call",230)).

Is this guide enough to start trading options?

This guide is about data plumbing, not about trading. The template ships with a Strategy Builder sheet because seeing live premium percentages and static yields is a useful way to learn how option pricing behaves; it is not a recommendation, and none of the numeric examples are trade picks. If you are new to options, treat the spreadsheet as a sandbox - pull live data, model scenarios, and pair the work with formal education before you put real capital on the line.

How do I evaluate MarketXLS before subscribing?

Download the static sample workbook linked above and open it in Excel or Google Sheets to see how every dashboard is laid out and which formulas power each cell. The "MarketXLS Functions Used in This Sheet" boxes list them. Then book a live walkthrough at marketxls.com/book-demo. The team will run the live template against tickers and option chains you care about so you can see the data quality before committing.

The Bottom Line

GOOGLEFINANCE handles equities, ETFs, currencies, and mutual funds but does not expose any option data. To get option prices in Google Sheets, keep GOOGLEFINANCE for the equity layer if you like, and add MarketXLS for option chains, implied volatility, Greeks, and Black-Scholes values.

The template files in this post show the difference: the sample workbook gives you a fully-formatted view of every sheet, and the live template plugs in to your MarketXLS subscription and refreshes against the current market. Drop a ticker in, watch the dashboard recompute, and decide for yourself whether the data depth matches your workflow.

See the function library at MarketXLS formulas, plans on the pricing page, or book a walkthrough at marketxls.com/book-demo.