# GOOGLEFINANCE in Google Sheets

> GOOGLEFINANCE brings market data into a cell: the current price of a stock, a fact about it such as its P/E ratio or 52-week high, an exchange rate, or a table of past prices.

Source: https://spreadsheetpractice.com/google-sheets/googlefinance · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** GOOGLEFINANCE brings market data into a cell: the current price of a stock, a fact about it such as its P/E ratio or 52-week high, an exchange rate, or a table of past prices.
- **Syntax:** `=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])`
- **Returns:** One value for a current attribute. With a start date, a table with a header row: the date and time of each close in the first column, then the prices.
- **In Excel:** Excel has no GOOGLEFINANCE function. Microsoft 365 has the Stocks and Currencies data types for current values and STOCKHISTORY for past prices.

GOOGLEFINANCE brings market data into a cell: the current price of a stock, a fact about it such as its P/E ratio or 52-week high, an exchange rate, or a table of past prices. Give it a ticker and, optionally, what you want to know about it.

> Prices on this page come from a fixed, made-up price history so every result is repeatable. They are not market data. In Google Sheets, GOOGLEFINANCE returns live data, which Google says may be delayed by up to 20 minutes and is not for trading.

## Syntax

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

| Argument | Required? | What it is |
|---|---|---|
| `ticker` | Required | The ticker in quotes or in a cell, ideally with its exchange, such as "NASDAQ:GOOG" or "NYSE:KO". For an exchange rate, use "CURRENCY:" and two currency codes, such as "CURRENCY:USDEUR". |
| `attribute` | Optional | Optional. What to return. Leave it out for "price". For a current value, use names like "high52", "pe" or "marketcap". For history, use "close", "open", "high", "low", "volume" or "all". |
| `start_date` | Optional | Optional. Turns the formula into a history request starting on this date. Use DATE(2026,10,1) or a cell that holds a date. |
| `end_date\|num_days` | Optional | Optional. The last date of the history, or a number of days from start_date. Leave it out to get just the start date. |
| `interval` | Optional | Optional. "DAILY" (the default) or "WEEKLY". You can also write 1 or 7. |

## The sample data

Every example below runs on this sheet; results are calculated by the formula engine in Google Sheets mode.

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Ticker | Shares | Cost per share |  | Setting | Value |
| 2 | NASDAQ:GOOG | 40 | $142.10 |  | Start date | 10/1/2026 |
| 3 | NASDAQ:MSFT | 15 | $388.50 |  | End date | 10/9/2026 |
| 4 | NYSE:KO | 120 | $61.20 |  | Ticker to chart | NASDAQ:NVDA |
| 5 | NASDAQ:NVDA | 60 | $118.40 |  | Amount in USD | $2,500 |
| 6 | NYSE:JPM | 25 | $201.75 |  |  |  |

## The current price, and other facts about a stock

With just a ticker, `GOOGLEFINANCE` returns the latest price. Add a second argument to ask for something else: `"priceopen"`, `"high"` and `"low"` for today, `"high52"` and `"low52"` for the past year, `"pe"`, `"eps"`, `"marketcap"`, `"volume"` and more.

`"change"` is the move since yesterday's close in dollars. `"changepct"` is the same move as a percent, written as a plain number: 0.93 means 0.93%, not 93%.

`=GOOGLEFINANCE("NASDAQ:GOOG")`

The latest price. "price" is the default attribute

Result:

`188.4`

`=GOOGLEFINANCE("NASDAQ:GOOG","pe")`

The price-to-earnings ratio

Result:

`23.4`

`=GOOGLEFINANCE("NASDAQ:GOOG","changepct")`

Today's move as a percent number: 0.93 means up 0.93%

Result:

`0.93`

## Tickers and exchange prefixes

Write the exchange before the ticker, separated by a colon: `"NASDAQ:GOOG"`, `"NYSE:KO"`. A bare ticker often works, but the same symbol can trade on more than one exchange, so the prefix removes the guesswork.

Keep tickers in a column and point the formula at the cell. Then one formula, filled down, prices the whole list.

`=GOOGLEFINANCE(A3)`

The ticker comes from cell A3 (NASDAQ:MSFT)

Result:

`431.7`

`=GOOGLEFINANCE("KO")`

A bare ticker can work when there is only one match

Result:

`68.9`

`=GOOGLEFINANCE("NYSE:GOOG")`

The wrong exchange returns #N/A: GOOG trades on NASDAQ

Result:

`#N/A`

## Currency conversion

For an exchange rate, use `"CURRENCY:"` followed by two three-letter codes with nothing between them. `"CURRENCY:USDEUR"` is how many euros one US dollar buys.

To convert an amount, multiply it by the rate. Any pair of codes works, not only pairs that include the dollar.

`=GOOGLEFINANCE("CURRENCY:USDEUR")`

Euros per US dollar

Result:

`0.9215`

`=F5*GOOGLEFINANCE("CURRENCY:USDEUR")`

The $2,500 in F5, converted to euros

Result:

`2303.75`

`=GOOGLEFINANCE("CURRENCY:EURGBP")`

Pounds per euro: neither side has to be the dollar

Result:

`0.8368963646`

## Historical prices: a table, not a number

Add a start date and GOOGLEFINANCE returns a table instead of a single value: a header row, then one row per trading day with the date and time of the close in the first column. The table spills down and to the right, so leave those cells empty.

The fourth argument is either an end date or a number of days from the start. Add `"WEEKLY"` for one row per week. Ask for `"all"` to get open, high, low, close and volume together.

`=GOOGLEFINANCE(A2,"close",F2,F3)`

Daily closes for the ticker in A2, between the dates in F2 and F3

Result:

| Date | Close |
|---|---|
| 10/1/2026 16:00:00 | 216.91 |
| 10/2/2026 16:00:00 | 205 |
| 10/5/2026 16:00:00 | 201.87 |
| 10/6/2026 16:00:00 | 202.37 |
| 10/7/2026 16:00:00 | 199.52 |
| 10/8/2026 16:00:00 | 201.65 |
| 10/9/2026 16:00:00 | 203.86 |

`=GOOGLEFINANCE("NASDAQ:GOOG","all",DATE(2026,10,19),5)`

Five days from October 19, every column at once

Result:

| Date | Open | High | Low | Close | Volume |
|---|---|---|---|---|---|
| 10/19/2026 16:00:00 | 195.33 | 198.93 | 195.32 | 198.63 | 20188077 |
| 10/20/2026 16:00:00 | 199.46 | 201.12 | 193.08 | 193.96 | 29220924 |
| 10/21/2026 16:00:00 | 193.1 | 194.68 | 193.06 | 193.64 | 19278854 |
| 10/22/2026 16:00:00 | 193.39 | 195.31 | 191.04 | 194.11 | 17254427 |
| 10/23/2026 16:00:00 | 194.39 | 194.78 | 185.02 | 186.89 | 21203227 |

`=GOOGLEFINANCE(F4,"close",DATE(2026,8,3),DATE(2026,10,28),"WEEKLY")`

One close per week for the ticker in F4

Result:

| Date | Close |
|---|---|
| 8/7/2026 16:00:00 | 102.73 |
| 8/14/2026 16:00:00 | 105.28 |
| 8/21/2026 16:00:00 | 111.42 |
| 8/28/2026 16:00:00 | 122.78 |
| 9/4/2026 16:00:00 | 124.97 |
| 9/11/2026 16:00:00 | 125.9 |
| 9/18/2026 16:00:00 | 122.77 |
| 9/25/2026 16:00:00 | 123.99 |
| 10/2/2026 16:00:00 | 128.59 |
| 10/9/2026 16:00:00 | 130 |
| 10/16/2026 16:00:00 | 136.46 |
| 10/23/2026 16:00:00 | 149.26 |
| 10/28/2026 16:00:00 | 142.6 |

## Pull one number out of the history

Because a history request returns a table, wrap it in `INDEX` when you want a single number. Row 2, column 2 is the first close under the header, so `INDEX(…,2,2)` gives the close on the start date.

A row number of 0 returns a whole column. `INDEX(…,0,2)` is every close at once, which `MAX`, `MIN` or `AVERAGE` can then summarize. The header text in that column is ignored.

`=INDEX(GOOGLEFINANCE("NASDAQ:GOOG","close",DATE(2026,10,1)),2,2)`

The close on October 1 as one number

Result:

`216.91`

`=MAX(INDEX(GOOGLEFINANCE(F4,"close",F2,F3),0,2))`

The highest close between the dates in F2 and F3

Result:

`130`

`=MIN(INDEX(GOOGLEFINANCE(F4,"close",F2,F3),0,2))`

The lowest close over the same days

Result:

`123.45`

## A portfolio value column

Put tickers in one column and share counts in the next. Then each row needs one formula: shares times the current price. Fill it down and the column updates whenever prices do.

Write one formula per row and fill it down. GOOGLEFINANCE is built to look up one ticker at a time, so a single ARRAYFORMULA over the whole ticker column is not a reliable shortcut.

`=B2*GOOGLEFINANCE(A2)`

Market value of the 40 GOOG shares in row 2

Result:

`7536`

`=(GOOGLEFINANCE(A2)-C2)*B2`

Gain on that position against its cost per share in C2

Result:

`1852`

`=GOOGLEFINANCE(A4)/C4-1`

Return on the KO position as a fraction: format it as a percent

Result:

`0.1258169935`

## A price chart in one cell

Google Sheets can draw a small chart inside a single cell with `SPARKLINE`. Feed it the price column of a history request: `=SPARKLINE(INDEX(GOOGLEFINANCE("NASDAQ:GOOG","close",DATE(2026,1,2),DATE(2026,10,28)),0,2))` draws the year so far as a line.

Our practice grid doesn't draw sparklines, so there is no live result here. Try it in a real sheet.

## GOOGLEFINANCE cheat sheet

| Task | Formula | Note |
|---|---|---|
| Latest price | `=GOOGLEFINANCE("NASDAQ:GOOG")` | price is the default |
| Price for a ticker in a cell | `=GOOGLEFINANCE(A2)` | Fill down to price a list |
| 52-week high and low | `=GOOGLEFINANCE(A2,"high52")` | Or "low52" |
| Today's change in percent | `=GOOGLEFINANCE(A2,"changepct")` | 0.93 means 0.93% |
| P/E ratio | `=GOOGLEFINANCE(A2,"pe")` | Also "eps" and "marketcap" |
| Exchange rate | `=GOOGLEFINANCE("CURRENCY:USDEUR")` | Euros per dollar |
| Convert an amount | `=F5*GOOGLEFINANCE("CURRENCY:USDEUR")` | Amount times the rate |
| Daily closes between two dates | `=GOOGLEFINANCE(A2,"close",F2,F3)` | Returns a table with a header row |
| A number of days of history | `=GOOGLEFINANCE(A2,"close",DATE(2026,10,1),30)` | Days counted from the start date |
| Weekly closes | `=GOOGLEFINANCE(A2,"close",F2,F3,"WEEKLY")` | One row per week |
| Close on one date, as a number | `=INDEX(GOOGLEFINANCE(A2,"close",F2),2,2)` | Row 2, column 2 skips the header |
| Highest close in a period | `=MAX(INDEX(GOOGLEFINANCE(A2,"close",F2,F3),0,2))` | Row 0 means the whole column |

## Common GOOGLEFINANCE errors

| What you see | Why, and how to fix it |
|---|---|
| #N/A for a ticker you know exists | The exchange prefix is probably wrong, or the ticker has a typo. Check where it trades: "NASDAQ:GOOG", not "NYSE:GOOG". For currencies, write the two codes together: "CURRENCY:USDEUR". |
| A table appears where you wanted one number | Any formula with a start date returns history as a table with a header row. For one value, wrap it: INDEX(GOOGLEFINANCE(…),2,2). For today's value, leave the dates out. |
| #REF! on a history formula | The table needs empty cells below and to the right to spill into. Clear the cells in its way, or move the formula somewhere with room. |
| The wrong dates come back | Dates typed as text can be read differently depending on the spreadsheet's locale. Use DATE(2026,10,1) or a cell formatted as a date. |
| Prices don't match your broker | GOOGLEFINANCE is not real-time. Google says quotes may be delayed by up to 20 minutes and don't come from every market. |
| Using it to place trades | Google provides this data for information only, not for trading or advice. Check prices with your broker before you act on them. |

## Questions

### How often does GOOGLEFINANCE update?

Prices refresh on their own while the spreadsheet is open. Google says quotes may be delayed by up to 20 minutes, so treat them as close to current, not live.

### How do I get a stock's price on a specific date?

Ask for the close on that date and take the number out of the table: =INDEX(GOOGLEFINANCE("NASDAQ:GOOG","close",DATE(2026,10,1)),2,2). On a weekend or holiday there is no close to return.

### Can GOOGLEFINANCE convert currencies over time?

Yes. Give a currency pair a start date, like =GOOGLEFINANCE("CURRENCY:USDEUR","close",DATE(2026,10,1),DATE(2026,10,9)), and you get one rate per day.

### Does GOOGLEFINANCE work in Excel?

No. Microsoft 365 uses the Stocks and Currencies data types for current values and the STOCKHISTORY function for past prices.

### Why does historical data show #N/A when I read it with Apps Script or the API?

Google doesn't allow GOOGLEFINANCE historical data to be downloaded or read through the Sheets API or Apps Script. It only shows in the spreadsheet itself.

### Why are the prices on this page different from today's?

This page uses a fixed, made-up price history so the examples and exercises give the same answer every time. In Google Sheets, the same formulas return real market data.

## Practice

3 graded GOOGLEFINANCE exercises are on [the GOOGLEFINANCE guide](https://spreadsheetpractice.com/google-sheets/googlefinance), checked in Google Sheets mode and re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
