You paste a stock ticker into a budget sheet, type the current price in the next cell, and a week later that number is a small lie. Prices move. A hand-typed cell does not. GOOGLEFINANCE closes that gap by reading quotes straight from Google Finance into a cell that recalculates on its own — no API key, no add-on, no copy-paste from a brokerage tab.
The catch is that “live” means something specific here, and a couple of quirks trip up almost everyone in the first week. Here is how the function works, which attributes earn their place, and what to do when a cell stubbornly shows #N/A.
What GOOGLEFINANCE pulls, and the 20-minute catch
The function queries the same data that powers the Google Finance website and drops it into your sheet as a formula result. Ask for a price and you get a number that updates without you touching it. Ask for a 52-week high and you get that instead. One function, dozens of data points.
“Live” is the word that misleads people. Real-time quotes from GOOGLEFINANCE are delayed by up to 20 minutes, and the cell recalculates roughly every few minutes during market hours rather than ticking every second. According to Google’s GOOGLEFINANCE reference, the price attribute is explicitly described as delayed by up to 20 minutes. That is fine for a watchlist or a budget. It is not a day-trading feed, and Google’s terms say as much.
Your first live-price formula
The simplest call takes a ticker and an attribute. The attribute is optional and defaults to price, but spell it out so the formula reads clearly to the next person who opens the sheet.
=GOOGLEFINANCE("NASDAQ:AAPL", "price")The first argument is the security, written as EXCHANGE:TICKER. The second is the data point you want — here, the last traded price. Drop the exchange prefix and you are gambling: a bare ticker can match the wrong listing on a different market, and you will never see an error, just a quietly wrong number. Always pin the exchange.
Here is what a small price block looks like once you wire the ticker in column A to a formula in column B:
| A | B | |
|---|---|---|
| 1 | Ticker | Price |
| 2 | NASDAQ:AAPL | =GOOGLEFINANCE(A2,”price”) |
| 3 | NYSE:KO | =GOOGLEFINANCE(A3,”price”) |
Point the formula at the cell instead of hard-coding the ticker, then fill down. One column of symbols, one column of prices, and you never edit a formula again.
The attributes worth knowing
The price attribute is the headline act, but the function returns far more than that. You pass the attribute name as plain text in quotes, and it is case-insensitive. A handful cover most real needs.
| Attribute | Returns | Good for |
|---|---|---|
price |
Last trade, delayed | Watchlists, portfolio value |
changepct |
Percent change today | Daily movers, conditional colors |
high52 / low52 |
52-week high and low | Range context, buy signals |
pe |
Price-to-earnings ratio | Quick valuation screens |
currency |
Trading currency code | Multi-market portfolios |
So =GOOGLEFINANCE("NASDAQ:MSFT","changepct") returns today’s percentage move, and feeding that into a conditional-formatting rule paints gainers green and losers red without a single manual edit. Pair the daily change with a 52-week range and a small watchlist tells you, at a glance, what is cheap and what is running hot.
Pulling historical prices into a range
Add a start date and the function flips from a single number to a table. This is where most people get surprised, because the result is not one cell — it is an array that spills down and to the right from where you typed it.
=GOOGLEFINANCE("NASDAQ:AAPL", "close", TODAY()-30, TODAY())The third and fourth arguments set the window: thirty days ago through today. The result is two columns — a date and the closing price for each trading day — with its own header row. The same spilling behavior powers other array formulas in Google Sheets, and the failure mode is identical.
#REF! if anything sits in the cells it needs to spill into. Put the formula where the rows below and the column to its right are empty, or it refuses to load at all.
One more limit catches portfolio builders: historical results cannot be read through Apps Script or the Sheets API. If you want to log yesterday’s close every morning, you copy the spilled values to a static range — a script can move the numbers, but it cannot call the historical function directly.
Exchange rates and crypto in the same function
GOOGLEFINANCE is not only stocks. The CURRENCY: namespace turns it into a live foreign-exchange converter, which is handy the moment a sheet mixes dollars and euros.
=GOOGLEFINANCE("CURRENCY:USDEUR")That returns how many euros one US dollar buys, updated on the same delayed schedule as stock prices. Swap the codes for any pair — CURRENCY:GBPJPY, CURRENCY:USDKRW, and so on. Multiply a foreign price by the rate and your totals stay honest no matter which market the security trades on.
A few crypto pairs work too, written the same way:
=GOOGLEFINANCE("CURRENCY:BTCUSD")This returns Bitcoin in US dollars. Coverage is thin — only the major coins resolve, and the same 20-minute delay applies — so treat crypto support as a bonus, not a trading tool. For anything exotic, a dedicated data feed beats GOOGLEFINANCE.
Build a watchlist that updates itself
Once the pieces click, a self-refreshing watchlist takes about two minutes. The trick is to drive every formula off a ticker cell so the sheet maintains itself.
- In column A, list your tickers with their exchange prefix, one per row.
- In B2, enter
=GOOGLEFINANCE(A2,"price")and fill down the column. - In C2, add
=GOOGLEFINANCE(A2,"changepct")for the daily move, then fill down. - Select column C and add a conditional-formatting rule: greater than 0 turns green, less than 0 turns red.
That is a working dashboard with zero maintenance. To pull tickers or holdings from a master file instead of retyping them, you can sync data across spreadsheets with IMPORTRANGE, then feed those cells straight into the price formulas. Scale the same idea up and you have the foundation for a real-time KPI dashboard that tracks value, daily change, and your own targets side by side.
When the price won’t load
Most GOOGLEFINANCE failures fall into four buckets, and each has a different fix. Run down this list before you assume the function is broken.
- ✓
#N/Aalmost always means an unknown ticker or an unsupported exchange — check the symbol on Google Finance first. - ✓ A blank cell usually means the attribute is not available for that security, or the market is closed.
- ✓ A “stale” price is the 20-minute delay doing its job, not a bug — the value will refresh on its own.
- ✓
#REF!on a historical query means the spill range hit existing content — clear the cells below and to the right.
The single most common mistake is the missing exchange prefix. "AAPL" may resolve, may not, and may quietly grab a foreign listing; "NASDAQ:AAPL" never leaves room for doubt. When a number looks wrong rather than missing, check the prefix before anything else.
Where to start
EXCHANGE:TICKER form. Every formula then points at a cell, so adding a stock is one row, not a formula edit.
Open a blank sheet, type =GOOGLEFINANCE("NASDAQ:AAPL","price"), and watch a real quote land in the cell. Add a second ticker, point both formulas at column A, and color the daily-change column. You now have a watchlist that maintains itself for as long as the sheet stays open — built from one function, no add-ons, and a clear-eyed view of what “live” really means.