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

Option prices in Google Sheets - if that is what brought you here, you have almost certainly already tried =GOOGLEFINANCE("AAPL","price") and discovered that there is no equivalent argument for an option strike, no built-in expiration parameter, and no way to coax a full option chain out of the function. The GOOGLEFINANCE function is excellent at one thing - free, lightly delayed equity, mutual fund, and currency quotes - and it is genuinely useful as the entry point for a spreadsheet-driven trading workflow. The trouble starts the moment you want a call price for a specific strike and expiration, the implied volatility on that contract, or the delta you would need to size a hedge. This guide walks through exactly what Google Sheets can do natively, what it cannot, and how MarketXLS plugs in to give you real option prices, full chains, Greeks, and a working Black-Scholes engine inside the same spreadsheet ergonomics you are used to.

What Google Sheets Gives You Out of the Box

The GOOGLEFINANCE function 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 hard to beat on price.

Where Option Prices in Google Sheets Hit a Wall

The wall is not subtle. None of the following can be pulled by GOOGLEFINANCE in any form, on any spreadsheet plan, with any combination of arguments:

If you search this problem you will find three categories of workaround on the internet, and all three are problematic enough that we will name them and move on:

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.

The answer that scales is a licensed data add-on. MarketXLS is one - it provides a Google Sheets add-on for refresh-on-demand workflows and a full streaming experience in Excel desktop. The rest of this guide focuses on the formulas you get the moment MarketXLS is connected.

How MarketXLS Extends a Spreadsheet for Options

MarketXLS exposes more than a thousand functions through its add-on, with options data being one of its deepest categories. The functions you care about for option prices break into five groups:

1. Live 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 OCC 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.

Here are the exact MarketXLS formulas, all verified against the live Function Docs catalog, that you will use day to day:

=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 OCC 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)

That single block of formulas already replaces every gap in the GOOGLEFINANCE list above. The interesting part is what you can build on top.

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 simple - 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 updates with the market.

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 point is not the threshold - it is that the spreadsheet does the screening for you in one pass, rather than your eyeballing eight charts in your broker.

The Live Option Chain Sheet

This is where Google Sheets gives up and MarketXLS shines. The Live Option Chain sheet 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

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 - this is the function that the GOOGLEFINANCE attribute list is missing.

The columns you get back per row include the OCC option symbol, expiration, type, strike, bid, ask, last, volume, open interest, and implied volatility for that contract. That is more granular than most retail brokers expose in their default chain UI.

Greeks Without a Trading Terminal

The Greeks sheet pairs the spill chain with the per-contract 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)

$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, you are looking at a market-implied edge - sometimes it is real, sometimes it is a quote-stale artifact, and sometimes it is the market pricing in an earnings event the model does not know about. Having both numbers in the same row is the spreadsheet equivalent of holding two calipers up against a piece of stock.

Strategy Builder - Covered Calls and Cash-Secured Puts

The Strategy Builder sheet is the bridge from "I have data" to "I can model an idea". It scaffolds 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)

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

That last row is the one most options-focused users underweight. Google Sheets, by design, does not stream. MarketXLS inside Google Sheets is a refresh-on-demand experience - good for a daily or hourly snapshot, not for an open position you are actively managing. The same MarketXLS formulas inside Excel desktop stream tick by tick. If you are an active options trader, the desktop side is where the data model fully unlocks. 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 - The Honest Two-Path Picture

There is no single answer to "how do I get option prices into a spreadsheet" because the right tool depends on where you live.

Path 1 - You live in Google Sheets. Use GOOGLEFINANCE for equities and currencies, then install the MarketXLS add-on for everything options-related. You will be making explicit refresh calls rather than streaming, but every formula in this guide will work. This is the right path for analysts, advisors, and anyone running scheduled reports.

Path 2 - You live in Excel. Use MarketXLS directly in Excel desktop. You get the same formulas plus a streaming feed, plus a desktop ribbon that hides the function syntax behind dialog boxes when you want it. 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 functions return the same data as in Excel, with one important difference: Google Sheets does not support streaming, so the values are refresh-on-demand rather than tick by tick. For analysts, advisors, and most non-active users this is perfectly fine. 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 is the current implied volatility expressed as a percentile of the last 52 weeks. 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. Premium-selling strategies (covered calls, cash-secured puts, credit spreads) are typically more attractive when IV rank is elevated, because options are richer. Premium-buying strategies are typically more attractive when IV rank is depressed. =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 - useful for backtesting strategies, studying earnings reactions, and reconstructing what option pricing looked like at a moment in history. This is one of the harder data sets to license, and it is a strong reason analysts often migrate from a free GOOGLEFINANCE workflow to a paid options data add-on.

What is an OCC option symbol and why does MarketXLS use it?

The Options Clearing Corporation defines a standard 21-character symbol that uniquely identifies a listed option contract - root symbol, expiration date (YYMMDD), call/put indicator, and strike price encoded in thousandths. MarketXLS uses OCC symbols as the primary key for any per-contract function. You rarely have to type one - the OptionSymbol(Ticker, Expiry, Type, Strike) helper builds the OCC symbol from human-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" reference boxes act as a built-in tour. 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

The native GOOGLEFINANCE function is genuinely excellent at equities, ETFs, currencies, and mutual funds. It is not excellent at options, and that is not a gap you can close with a clever attribute trick - the function simply does not expose option data. The honest answer to "how do I get option prices in Google Sheets" is to keep using GOOGLEFINANCE for the equity layer of your spreadsheet, and to layer MarketXLS on top for the option chains, the implied volatility surface, the Greeks, and the theoretical pricing engine.

The template files in this post are the fastest way to see 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.

Explore the full MarketXLS function library and book a live walkthrough at marketxls.com and marketxls.com/book-demo.