GOOGLEFINANCE Function: Syntax, Attributes and Formulas

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 thinly documented, delayed, and it fails in ways that look like your formula is wrong when it isn't. The formulas come first.

Copy-paste GOOGLEFINANCE formulas#

Every attribute below is taken from Google's own list for the function. Swap NASDAQ:AAPL for the ticker you hold, with the exchange prefix, which Google says to include for reliable results.

The last one returns a table rather than a single number. More on that below.

The basic syntax#

=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

Only the first argument is required. Left alone, it returns the current price:

=GOOGLEFINANCE("NASDAQ:AAPL")
=GOOGLEFINANCE("NASDAQ: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")

Google's documentation says to use both the exchange symbol and the ticker: "NASDAQ:AAPL", "NYSE:KO", "LON:VOD", "TSE:SHOP". A bare ticker usually resolves to a US listing, which is not always the one you own, and it is the first thing to fix when a formula returns #N/A.

The attributes worth knowing#

Google lists nineteen real-time attributes. These are the ones that earn a column in a real tracker:

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:

Wrap the price lookup so a bad ticker doesn't cascade #N/A through every column that depends on it:

=IFERROR(GOOGLEFINANCE(A2,"price"), "")

These four columns are what our free portfolio tracker template ships with, already wired up, if you would rather start from a working sheet.

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 basis
• E2 = GOOGLEFINANCE("NASDAQ:AAPL","price") returns $210
• F2 = 15 × $210 = $3,150, market value
• G2 = $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("NASDAQ:AAPL", "price", DATE(2026,1,2), DATE(2026,1,31), "DAILY")

The interval is "DAILY" or "WEEKLY" (Google also accepts 1 or 7). The fourth argument can be an end date or a number of days from the start. Give a start date on its own and you get that single day's data.

To pull one historical closing price rather than a table, wrap it in INDEX and take the second row of the second column:

=INDEX(GOOGLEFINANCE("NASDAQ: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 historical attributes are a smaller set: open, close, high, low, volume, or all for every column at once.

The four ways it breaks#

None of this is in the tooltip.

Quotes are delayed. Google's own note: quotes are not sourced from all markets and may be delayed by up to 20 minutes. Fine for checking a portfolio on a Sunday evening; not fine for anything where the current price matters.

It returns #N/A without explanation. Mutual funds, many ETFs outside the US, and anything recently delisted have no data, and Google says outright that most international exchanges are unsupported. 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. That is the one that matters most for a tracker. 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.

When a spreadsheet is the right answer#

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. The demo deck on that page needs no signup.