What Is the GOOGLEFINANCE Function?
GOOGLEFINANCE pulls live and historical financial data from Google Finance directly into your spreadsheet. You can retrieve current stock prices, market caps, P/E ratios, trading volumes, currency exchange rates, and more. For investors, analysts, or anyone tracking financial markets, it turns a Google Sheet into a lightweight, always-updated financial dashboard without any API keys or paid subscriptions.
Syntax
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
| Parameter | Description |
|---|---|
| ticker | The stock ticker symbol (e.g., "GOOG", "AAPL"). Use exchange prefix for non-US stocks: "LON:BP", "TYO:7203". |
| attribute | Optional. The data point to retrieve. Defaults to "price". See common attributes below. |
| start_date | Optional. The start date for historical data. |
| end_date or num_days | Optional. Either an end date or the number of days of data to return. |
| interval | Optional. "DAILY" or "WEEKLY". Defaults to daily. |
Common Attributes
| Attribute | Returns |
|---|---|
| "price" | Current price (default) |
| "priceopen" | Opening price for today |
| "high" | Today's high |
| "low" | Today's low |
| "volume" | Current day's trading volume |
| "marketcap" | Market capitalization |
| "pe" | Price-to-earnings ratio |
| "eps" | Earnings per share |
| "high52" | 52-week high |
| "low52" | 52-week low |
| "change" | Price change since previous close |
| "changepct" | Percentage change since previous close |
Basic Examples
Current Stock Price
=GOOGLEFINANCE("AAPL")
Result: The latest price for Apple Inc. This updates automatically during market hours.
Getting Multiple Data Points
| A | B (Formula) | B (Result) | |
|---|---|---|---|
| 1 | Metric | Value | Value |
| 2 | Price | =GOOGLEFINANCE("MSFT","price") | 425.30 |
| 3 | Market Cap | =GOOGLEFINANCE("MSFT","marketcap") | 3.16E+12 |
| 4 | P/E Ratio | =GOOGLEFINANCE("MSFT","pe") | 35.2 |
Currency Exchange Rate
=GOOGLEFINANCE("CURRENCY:USDEUR")
Result: The current USD to EUR exchange rate. Use the format CURRENCY:XXXYYY where XXX is the source currency and YYY is the target.
Advanced Examples
Historical Price Data
To get Apple's daily closing prices for the past 30 days:
=GOOGLEFINANCE("AAPL", "close", TODAY()-30, TODAY(), "DAILY")
This returns a two-column table with dates in the first column and closing prices in the second. The data spills into adjacent cells, so make sure there is enough room below and to the right of the formula cell.
Building a Portfolio Tracker
Set up your holdings in columns A and B, then let GOOGLEFINANCE calculate the rest:
| A | B | C (Formula) | D (Formula) | E (Formula) | |
|---|---|---|---|---|---|
| 1 | Ticker | Shares | Price | Value | Day Change |
| 2 | AAPL | 50 | =GOOGLEFINANCE(A2) | =B2*C2 | =GOOGLEFINANCE(A2,"changepct") |
| 3 | GOOG | 20 | =GOOGLEFINANCE(A3) | =B3*C3 | =GOOGLEFINANCE(A3,"changepct") |
| 4 | AMZN | 15 | =GOOGLEFINANCE(A4) | =B4*C4 | =GOOGLEFINANCE(A4,"changepct") |
Add a SUM in row 5 for column D to see your total portfolio value. Column E shows the percentage change for each stock today, formatted as a percentage.
Converting Invoice Amounts with Live Rates
=B2 * GOOGLEFINANCE("CURRENCY:EURUSD")
If B2 contains an invoice amount in euros, this converts it to US dollars at the current exchange rate. Useful for international billing sheets that need approximate conversions.
Common Mistakes
- Using wrong ticker formats. US stocks use plain tickers ("AAPL"), but international stocks need exchange prefixes ("LON:SHEL" for Shell on the London Stock Exchange). Mutual funds and some ETFs may not be supported at all.
- Expecting real-time data. GOOGLEFINANCE data is typically delayed by 15-20 minutes during market hours. It should not be used for time-sensitive trading decisions.
- Not leaving room for historical data. Historical queries return multi-row, multi-column results that spill downward and to the right. If there is existing data in those cells, you get a
#REF!error. Always place historical GOOGLEFINANCE formulas in an area with plenty of empty cells.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.