How the GOOGLEFINANCE Function Actually Works
GOOGLEFINANCE pulls stock prices into Google Sheets. Here are the attributes worth knowing, a working portfolio formula, and the four ways it breaks.
If you track investments in Google Sheets, GOOGLEFINANCE is the function doing
the work. It fetches market data straight into a cell, which is why a Sheets
portfolio can update itself while an Excel one sits there going stale.
It is also poorly documented, quietly delayed, and it fails in ways that look like your formula is wrong when it isn't. Here is the whole thing.
The basic syntax
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date], [interval])
Only the first argument is required. Left alone, it returns the current price:
=GOOGLEFINANCE("AAPL")
=GOOGLEFINANCE("AAPL", "price")
Both give the same number. Point it at a cell instead of a hard-coded string and the formula becomes reusable down a column:
=GOOGLEFINANCE(A2, "price")
For anything outside US exchanges, prefix the ticker with the exchange —
"NASDAQ:AAPL", "LON:VOD", "TSE:SHOP". Bare tickers usually resolve to the
US listing, which is not always the one you own.
The attributes worth knowing
There are dozens. These are the ones that earn a column in a real tracker:
| Attribute | Returns |
|---|---|
price | Current price, delayed up to 20 minutes |
priceopen | Price at today's open |
high / low | Today's range |
closeyest | Yesterday's closing price |
changepct | Percent change since yesterday's close |
volume | Shares traded today |
name | Full company name — useful for labelling rows |
currency | Currency of the quote |
closeyest is the one people miss. Your day change is price minus closeyest,
and computing it yourself is more reliable than parsing changepct, which
returns a number that already means percent and will double-count if you format
the cell as a percentage.
A portfolio formula that works
Say column A holds tickers, column B holds share counts, and column C holds what you paid per share. Four formulas turn that into a live tracker:
| Column | Formula | What it gives you |
|---|---|---|
| D — Cost basis | =B2*C2 | What the position cost |
| E — Current price | =GOOGLEFINANCE(A2,"price") | Live quote |
| F — Market value | =B2*E2 | What it is worth now |
| G — Unrealized gain | =F2-D2 | Paper profit or loss |
Wrap the price lookup so a bad ticker doesn't cascade #N/A through every column
that depends on it:
=IFERROR(GOOGLEFINANCE(A2,"price"), "")
If you would rather not build this at all, StoxDeck does the same job without the formulas. Build your first deck →
You hold 15 shares of AAPL bought at $180.
D2 = 15 × $180 = $2,700— your cost basisE2 = GOOGLEFINANCE("AAPL","price")returns$210F2 = 15 × $210 = $3,150— market valueG2 = $3,150 − $2,700 = $450— unrealized gain
Reopen the sheet tomorrow and E2 has moved, so F2 and G2 move with it.
Historical prices
Pass a date and the function switches from a single value to a range of results,
with headers. This is the part that surprises people — the formula spills into
neighbouring cells and will error with #REF! if anything is in the way.
=GOOGLEFINANCE("AAPL", "price", DATE(2026,1,1), DATE(2026,6,30), "WEEKLY")
To pull a single historical closing price rather than a table, wrap it in INDEX
and take the second row of the second column:
=INDEX(GOOGLEFINANCE("AAPL","price",DATE(2026,1,2)), 2, 2)
That is the standard trick for reconstructing what a holding was worth on a particular date — useful if you are trying to work out the cost basis of shares you bought years ago.
The four ways it breaks
Now the honest part, because none of this is in the tooltip.
Quotes are delayed. Up to 20 minutes for most exchanges. Fine for checking a portfolio on a Sunday evening; not fine for anything where the current price actually matters.
It returns #N/A without explanation. Mutual funds, many ETFs outside the
US, and anything recently delisted simply have no data. The formula is correct;
the data isn't there.
Nothing recalculates while the file is closed. A spreadsheet is only as fresh as the last time you opened it. Come back after three months and every figure on screen is three months old until it refreshes.
It only knows prices, never your history. This is the real limitation.
GOOGLEFINANCE can tell you what AAPL costs right now. It has no idea that you
bought more in March, or that a dividend was reinvested in June — and both of
those change your cost basis, which is the number
every gain is measured from.
Warning
The last one is what quietly ruins hand-built trackers. The price column stays correct forever while the cost column drifts, so the sheet looks healthy and reports the wrong gain.
When a spreadsheet is the right answer
Genuinely, often. If you hold a handful of positions, buy rarely, and enjoy the
control, a Sheets tracker with GOOGLEFINANCE costs nothing and does the job. We
publish a free template with these formulas
already wired in.
It stops being the right answer at the point where you are maintaining it more than reading it — several accounts, regular purchases, reinvested dividends, lots to keep straight. That is the work StoxDeck was built to take over: enter each holding once, and the basis, the prices and the gains stay current on their own.