Google Sheets GOOGLEFINANCE Formulas: Examples and Tips

In Google Sheets, use =GOOGLEFINANCE("NASDAQ:GOOG","price") to fetch the latest available price for Alphabet’s stock, or replace the ticker and attribute for other supported data. GOOGLEFINANCE can return current market information, currency exchange rates, and historical prices, but its coverage and update timing vary, so it is best treated as a convenient data source rather than a guaranteed real-time feed.

What GOOGLEFINANCE does

GOOGLEFINANCE is a Google Sheets function that retrieves financial information from Google Finance. Its basic form is GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]). The ticker identifies the security or currency pair; the optional attribute says what information to retrieve. For a current quote, you usually need only a ticker and an attribute.

For example, a stock ticker commonly includes its exchange prefix, such as NASDAQ or NYSE, followed by a colon and the symbol. This reduces ambiguity when similar ticker symbols exist on different exchanges. Google Finance’s coverage is not universal: some securities, exchanges, and data fields may be unavailable, and a ticker that works on a finance website is not guaranteed to work in Sheets.

GOOGLEFINANCE is a spreadsheet function, not a trading connection. It does not place orders, confirm that a quote is executable, or provide a dependable source for time-critical decisions. Google notes that some market data can be delayed, potentially by up to 20 minutes, depending on the exchange and data source. The cell may also take time to calculate or refresh.

Basic formula structure and ticker symbols

For a single current value, start with a clearly qualified ticker and the attribute name in quotation marks. If you keep tickers in cells, reference the cell instead of typing each symbol into the formula. That makes a watchlist easier to maintain and lets you change a symbol without editing multiple formulas.

With the ticker NASDAQ:GOOG in cell A2, use this formula in B2 to request its latest available price: =GOOGLEFINANCE(A2,"price"). The result is the price value returned for that security, in the currency reported by the source. It changes when Google Sheets refreshes the data; it is not a fixed value copied into the sheet.

Use the exchange prefix Google Finance recognizes for the listing you intend to track. A company can have multiple listings, and a bare ticker may resolve differently than expected. If the result is an error, check the exchange-symbol format, spelling, and whether Google Finance supports that listing before assuming the spreadsheet formula itself is wrong.

Useful current-data attributes

The attribute tells GOOGLEFINANCE what to return. Common current-data attributes include price, priceopen, high, low, volume, marketcap, tradetime, datadelay, currency, change, changepct, and closeyest. Availability depends on the security and data source; a valid attribute name does not guarantee that every ticker has a value for it.

With NASDAQ:GOOG in A2, put this in B2 to retrieve the reported market capitalization: =GOOGLEFINANCE(A2,"marketcap"). With the same ticker in A2, this formula in C2 requests the percentage change: =GOOGLEFINANCE(A2,"changepct"). GOOGLEFINANCE supplies that percentage as a numeric value; apply percentage formatting only after checking how the returned value is displayed, rather than multiplying it automatically.

For a dashboard, put labels in the first column and values in adjacent columns. For example, one row can hold a ticker, price, and quote currency. Use currency to identify the currency associated with a supported security’s price; do not assume every price is in US dollars. A displayed currency symbol is formatting, not proof that the underlying quote has been converted.

Getting historical prices

To request a price history, add a start date and an end date or a number of days. Historical results are returned as a small table with column headings and dates, rather than always as one scalar value. Leave empty cells below and to the right of the formula so the result can expand. If other content blocks the output, Sheets may report an array expansion error.

With the date range January 1–5, 2024, supplied directly in the formula, enter this in an otherwise empty area to request daily closing prices for Alphabet: =GOOGLEFINANCE("NASDAQ:GOOG","close",DATE(2024,1,1),DATE(2024,1,5),"DAILY"). The result is a date-and-close table for available trading days in the range; weekends and market holidays do not create ordinary trading-day prices. The requested end date is not a promise that a row exists for every calendar day.

Historical attributes include open, close, high, low, and volume. Use "WEEKLY" instead of "DAILY" for weekly intervals where supported. Historical data availability and supported attributes can differ from current quote availability. For reproducible analysis, remember that a live formula can recalculate later; if you need to preserve a particular snapshot, copy the returned cells and use Edit > Paste special > Values only.

Currency exchange rates

GOOGLEFINANCE can also request a currency conversion rate. The pair is written as a currency ticker using the form CURRENCY: followed by the three-letter source and target codes. A quote for one currency against another is a rate, not a conversion of an amount by itself.

To request the current US-dollar-to-euro rate in a cell, use: =GOOGLEFINANCE("CURRENCY:USDEUR"). The output represents the quoted EUR amount for one USD, subject to available data and refresh delays. The result can change, and it should not be confused with the exact rate a bank, card provider, or payment service will apply, since those providers may use different rates and fees.

If the amount in A2 is 100 US dollars and the exchange rate in B2 is 0.92 euros per dollar, then with that layout this formula in C2 calculates the illustrative converted amount: =A2*B2. It returns 92 euros because 100 × 0.92 = 92. This example explains the arithmetic only; the live rate and the rate available to an actual customer can differ. Check the provider’s terms for a real transaction.

Build a simple watchlist

A basic watchlist separates input symbols from retrieved values. Put exchange-qualified tickers in A2:A4, for example, then put a heading such as Price in B1. In B2, with the ticker for the row in A2, enter =GOOGLEFINANCE(A2,"price") and fill the formula down. Each row then requests the price for its own ticker. Because this is a live data request, a long list may take longer to populate and may not refresh simultaneously.

You can calculate a change from two values already in the sheet without asking GOOGLEFINANCE for a second attribute. Suppose B2 contains a current price of 150 and C2 contains a previous closing price of 145. With those values in B2 and C2, use this formula in D2: =IFERROR((B2-C2)/C2,""). It returns about 0.03448, or 3.45% when D2 is formatted as a percentage, because (150 − 145) ÷ 145 = 0.03448. IFERROR leaves the cell blank if the denominator is missing or zero. This is a calculation example, not a claim about a current market quote.

For a clean sheet, use descriptive headers, keep historical output clear of other data, and distinguish live formula cells from manually entered assumptions. In Google Sheets, select the relevant cells and choose Format > Number > Percent to display a proportion as a percentage. Keep the raw ticker and any manually chosen comparison date visible so someone reading the sheet can understand what the result represents.

Common errors and practical limits

Invalid ticker: Confirm the exchange prefix and symbol, and try the exact listing supported by Google Finance. Similar symbols can refer to different securities.
Unavailable data: GOOGLEFINANCE does not support every exchange, security, attribute, or historical request. Try a different supported attribute or consult another source if the field is not provided.
Loading or calculation errors: A request may temporarily fail or take time to resolve. Avoid building a large sheet around repeated copies of the same external data request when a single result can be referenced elsewhere.
Array expansion error: Historical output needs empty cells for its rows and columns. Clear anything that obstructs the returned table.
Unexpected units or formatting: Check the quote currency and whether a number is a price, a percentage, or a rate. Number formatting changes appearance, not the underlying value.

Also distinguish a formula error from a data limitation. A correctly written formula can still return an error when the requested information is unavailable. Prices may be delayed, absent outside market hours, or updated at a different time from another service. GOOGLEFINANCE should not be treated as a complete financial database, and Google may change availability or supported behavior.

GOOGLEFINANCE compared with other options

For a small personal tracker, GOOGLEFINANCE is convenient because the data appears directly in cells and can feed ordinary spreadsheet calculations and charts. Its major advantages are simple formulas and no need to manually copy every quote. Its disadvantages are limited coverage, variable delay, and less control over the source and update schedule than a dedicated market-data service may offer.

Excel does not include Google Sheets’ GOOGLEFINANCE function, so the same formula will not work there. Excel has different data features, including linked data types for supported securities in some Microsoft 365 versions, and other import or provider options. Those features have their own coverage, sign-in, and refresh requirements. If you move a workbook between Sheets and Excel, treat GOOGLEFINANCE formulas as platform-specific and replace them rather than expecting them to translate.

For an auditable report or a process that depends on timely, consistent data, compare the function’s availability and delay with the requirements of the task. A commercial data provider may offer defined service terms and broader fields, often with costs or usage limits. For a simple educational chart, classroom exercise, or casual watchlist, the convenience may matter more than comprehensive coverage.

Frequently Asked Questions

Is GOOGLEFINANCE in Google Sheets real time?
Not reliably. Google says some market information is delayed, potentially by up to 20 minutes, depending on the exchange and source. Refresh timing and availability vary, so do not use the function as a guaranteed real-time feed.

Why does GOOGLEFINANCE return an error for my stock?
The ticker may be formatted incorrectly, or Google Finance may not support that exchange, listing, or requested attribute. Check the exchange-qualified ticker and try a common field such as price. A valid formula cannot retrieve data that is not available to the service.

How do I get historical stock prices in Google Sheets?
Add a start date and end date to GOOGLEFINANCE, along with a historical attribute such as close and an interval such as DAILY. The result usually expands into a date-and-value table, so leave enough empty space below and beside the formula.

Can GOOGLEFINANCE convert currencies?
Yes. A pair such as CURRENCY:USDEUR requests an exchange rate, which you can multiply by an amount in a separate cell. It is an indicative rate from the data source, not necessarily the rate or fee a bank or payment provider will use.

Can I use GOOGLEFINANCE in Microsoft Excel?
No. GOOGLEFINANCE is a Google Sheets function and is not an Excel formula. Excel users need an Excel-supported data type, an appropriate provider, or another import method; availability and refresh behavior vary by option.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.