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. Give it a ticker and, optionally, what you want to know about it.
- 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.
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
| 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 our formula engine in 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")
| 188.4 |
=GOOGLEFINANCE("NASDAQ:GOOG","pe")
| 23.4 |
=GOOGLEFINANCE("NASDAQ:GOOG","changepct")
| 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)
| 431.7 |
=GOOGLEFINANCE("KO")
| 68.9 |
=GOOGLEFINANCE("NYSE:GOOG")
| #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")
| 0.9215 |
=F5*GOOGLEFINANCE("CURRENCY:USDEUR")
| 2303.75 |
=GOOGLEFINANCE("CURRENCY:EURGBP")
| 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)
| 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)
| 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")
| 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 |
| + 7 more rows | |
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)
| 216.91 |
=MAX(INDEX(GOOGLEFINANCE(F4,"close",F2,F3),0,2))
| 130 |
=MIN(INDEX(GOOGLEFINANCE(F4,"close",F2,F3),0,2))
| 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)
| 7536 |
=(GOOGLEFINANCE(A2)-C2)*B2
| 1852 |
=GOOGLEFINANCE(A4)/C4-1
| 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.
Try GOOGLEFINANCE live
The sample sheet with a formula in H1. Click it and change the arguments to see what happens.
Practice GOOGLEFINANCE
3 graded exercises. Each answer is checked in Sheets mode, then re-checked on changed data.
1. What each position is worth
C2, multiply the shares in B2 by the current price of the ticker in A2. Fill the formula down to C5.2. Distance from the 52-week high
B2, work out how far the ticker in A2 trades below its 52-week high: the current price divided by the 52-week high, minus 1. Fill it down to B5. The column is formatted as a percent.3. Best close in a period
B5, find the highest daily close for the ticker in B1 between the start date in B2 and the end date in B3.Lookups in Google Sheets, for building a portfolio tracker →
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.
Last reviewed October 2026. Every result on this page is calculated live by the Spreadsheet Practice formula engine in Sheets mode. Google Sheets is a trademark of Google LLC; this guide is independent.