How to Use GOOGLEFINANCE in Google Sheets: Comprehensive Guide

GOOGLEFINANCE pulls financial data from Google Finance straight into a sheet: current stock prices, historical price tables, exchange rates and key figures such as market cap or P/E ratio. It is the quickest way to build a portfolio tracker, a currency converter or a price chart without any add-on.

Syntax

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

ticker: The security, in quotes. Add the exchange to avoid ambiguity: "NASDAQ:GOOG", "NYSE:KO". For a currency pair, use "CURRENCY:USDEUR".
attribute [Optional]: What to fetch. Defaults to "price".
start_date [Optional]: The first date for historical data.
end_date or num_days [Optional]: The last date, or a number of days after the start date.
interval [Optional]: "DAILY" (or 1) or "WEEKLY" (or 7) for historical data.

Current data

Without dates, GOOGLEFINANCE returns a single, current value.

=GOOGLEFINANCE("NASDAQ:GOOG", "price")

Returns the latest price. "price" is the default, so =GOOGLEFINANCE("NASDAQ:GOOG") does the same.

Common real-time attributes:

"price": the current price, "priceopen": the day's opening price
"high", "low": the day's high and low
"volume", "volumeavg": trading volume, and average volume
"marketcap": market capitalization
"pe", "eps": price-to-earnings ratio, earnings per share
"high52", "low52": 52-week high and low
"change", "changepct": the day's change in value and in percent
"closeyest": the previous close
"currency": the currency of the listing
"tradetime", "datadelay": the time of the last trade, and the data delay in minutes

Quote data can be delayed (up to about 20 minutes), and not every attribute is available for every security.

Historical data

Add a start date to get a table:

=GOOGLEFINANCE("NASDAQ:GOOG", "close", DATE(2026, 1, 1), DATE(2026, 3, 31), "DAILY")

Returns two columns, Date and Close, with a header row, one row per trading day.

Use num_days instead of an end date:

=GOOGLEFINANCE("NASDAQ:GOOG", "close", TODAY() - 30, 30)

Historical attributes: "open", "close", "high", "low", "volume" and "all" (all of them). Weekends and market holidays are not included, so the number of rows is less than the number of calendar days.

Currency exchange rates

=GOOGLEFINANCE("CURRENCY:USDEUR")

Returns how many euros one US dollar buys. To convert an amount:

=A2 * GOOGLEFINANCE("CURRENCY:USDEUR")

Historical exchange rates work as well:

=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2026, 1, 1), TODAY(), "DAILY")

For crypto, use the pair format, such as "CURRENCY:BTCUSD".

Practical patterns

Portfolio tracker

With tickers in column A and share counts in column B, put this in row 2 and drag it down:

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

GOOGLEFINANCE takes one ticker per call, so use one formula per row rather than a whole range. Add columns for "changepct", "pe" and "high52" the same way, and total the position values with SUM.

Get just one value from a historical table

The historical call returns a table with a header. To pick the last close:

=INDEX(GOOGLEFINANCE("NASDAQ:GOOG", "close", TODAY() - 7, TODAY()), ROWS(GOOGLEFINANCE("NASDAQ:GOOG", "close", TODAY() - 7, TODAY())), 2)

Or the first value, skipping the header row: =INDEX(GOOGLEFINANCE(...), 2, 2).

Price on a specific date

=INDEX(GOOGLEFINANCE("NASDAQ:GOOG", "close", A2), 2, 2)

With a single date, you get a header and one row, so INDEX(..., 2, 2) returns the price.

Chart a price history

Create the table with the historical formula and insert a line chart based on the two columns. Use SPARKLINE for a tiny in-cell version:

=SPARKLINE(GOOGLEFINANCE("NASDAQ:GOOG", "close", TODAY() - 90, TODAY()), {"charttype", "line"})

Percent change over a period

=LET(
  p, GOOGLEFINANCE("NASDAQ:GOOG", "close", TODAY() - 30, TODAY()),
  (INDEX(p, ROWS(p), 2) - INDEX(p, 2, 2)) / INDEX(p, 2, 2)
)

Common errors

#N/A "Unable to parse ticker" or no data. The ticker is wrong or ambiguous. Add the exchange prefix, such as "NASDAQ:AAPL".
#N/A for an attribute. That attribute isn't available for the security. Real-time and historical attributes are different lists, so don't mix them.
A table where you expected one value. Historical calls return a header row and several rows. Use INDEX to pick a cell.
Prices don't update immediately. There is a data delay, and the sheet refreshes periodically. It is not for live trading.
Loading.. Large requests or many GOOGLEFINANCE calls slow the sheet down. Fetch the history once and refer to it.
No data for some exchanges or older periods. Coverage varies by market and by security.

Limits to keep in mind

GOOGLEFINANCE is for personal analysis, not trading decisions: data may be delayed, and some securities, markets and historical periods may be missing. It works only in Google Sheets, so it won't carry over to an Excel export.

Frequently asked questions

Can I get dividends? Not through a dedicated attribute. Use another data source or add them manually.

How often does it refresh? Periodically, with a data delay of up to about 20 minutes for most markets.

Can I use it for cryptocurrencies? Some pairs work, such as "CURRENCY:BTCUSD", but coverage varies.

Related Functions

SPARKLINE: Draw a small in-cell chart.
INDEX: Pick one value from a table.
IMPORTHTML: Import tables from web pages.
IFERROR: Replace errors with a fallback value.
TODAY: Return the current date.

GOOGLEFINANCE