How to Use GOOGLEFINANCE in Google Sheets

Google Sheets offers a powerful built-in function called GOOGLEFINANCE that allows you to easily fetch real-time or historical stock prices, market indices, and currency exchange rates, directly into your spreadsheet.

Tip: A Google Sheets function is a built-in formula that helps you perform calculations, manipulate data, and automate tasks in your spreadsheet. You can use functions to quickly analyze data, create complex formulas, and save time on repetitive tasks without having to write the underlying calculations yourself.

Also, here are several free investing spreadsheets to help you get started with GOOGLEFINANCE.

What data is available via the GOOGLEFINANCE function?

Real-Time Data

• Stock Prices:
  • Current price (delayed by up to 20 minutes)
  • Price at market open
  • Day’s high
  • Day’s low
  • Trading volume
  • Market capitalization
  • Time of the last trade
• Currency Exchange Rates: Real-time or near real-time conversion rates between currency pairs.
• Some Mutual Fund Data: Likely limited to real-time net asset value (NAV)

Historical Data

• Stocks:
  • Open, high, low, close prices
  • Trading volume
• Mutual Funds: Less robust historical data is typically available compared to stocks.
• Historical Currency Rates: Exchange rates between currency pairs over a specified period.

Additional Data Points (often real-time or nearly real-time)

• Technical Indicators:
  • Price/Earnings ratio (P/E)
  • Earnings per share (EPS)
  • Beta
  • 52-week high/low
• Other:
  • Dividend information (may not be real-time)

Note:

• Not all attributes might be available for every type of security.
• Real-time data is sourced differently than historical data, which may affect accuracy and timeliness.
• Quotes are not sourced from all markets and may be delayed up to 20 minutes. 
• Information is provided ‘as is’ and solely for informational purposes, not for trading purposes or advice.
• Google treats dates passed into GOOGLEFINANCE as as noon UTC time. Exchanges that close before that time may be shifted by a day.

GOOGLEFINANCE Syntax and Arguments

The Google Docs help documentation maintains an updated, complete list of GOOGLEFINANCE attributes.

The basic syntax for the GOOGLEFINANCE function is as follows:

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

Let’s break down each argument:

• ticker (required): This is the stock symbol, mutual fund, or currency pair you want to fetch data for. For stocks, use the ticker symbol (e.g., “AAPL” for Apple). For mutual funds, use the fund symbol. For currency pairs, use the format “CURRENCY:XXXYYZ” where XXX is the base currency and YYZ is the target currency (e.g., “CURRENCY:USDEUR” for US Dollar to Euro).
• attribute (optional): This specifies what type of data you want to retrieve. If omitted, it defaults to “price”. Other common attributes include “volume”, “high”, “low”, “open”, “close”, “marketcap”, “pe”, “eps”, “dividend”, and more. You can find the full list of supported attributes in the Google Sheets documentation.
• start_date (optional): If you want to fetch historical data, this is the start date of the range. You can enter the date directly or use a formula like TODAY()-30 to specify a date relative to the current date.
• end_date|num_days (optional): This is either the end date of the historical range or the number of days from the start_date to retrieve. If using an end date, it should be on or after the start_date. If using a number of days, it should be a positive integer.
• interval (optional): For historical data, this specifies the frequency of data points to retrieve. Valid values are “DAILY” (default), “WEEKLY”, or “MONTHLY”.

GOOGLEFINANCE Usage Examples

Here are a few more examples to illustrate how to use GOOGLEFINANCE in various scenarios:

* Fetch the current price and day's volume for Microsoft stock: 
     =GOOGLEFINANCE("MSFT") 
     =GOOGLEFINANCE("MSFT", "volume")

* Get the current exchange rate of Japanese Yen to US Dollar: 
     =GOOGLEFINANCE("CURRENCY:JPYUSD")

* Retrieve weekly historical prices and volume for Amazon over the past year: 
     =GOOGLEFINANCE("AMZN", "price", TODAY()-365, TODAY(), "WEEKLY") 
     =GOOGLEFINANCE("AMZN", "volume", TODAY()-365, TODAY(), "WEEKLY")

* Fetch the current market cap and P/E ratio for Google: 
     =GOOGLEFINANCE("GOOG", "marketcap") 
     =GOOGLEFINANCE("GOOG", "pe")

Tips and Tricks

• You can use GOOGLEFINANCE in conjunction with other Google Sheets functions like AVERAGE, MIN, MAX, etc., to perform calculations and analysis on the retrieved financial data.
• To fetch data for multiple stocks or currencies at once, you can use an array formula by enclosing the GOOGLEFINANCE function in curly braces and providing a range of ticker symbols.
• Keep in mind that GOOGLEFINANCE retrieves real-time data, so your spreadsheet will recalculate and update the values whenever it is opened or refreshed.
• If you encounter an error like “#N/A” or “#ERROR!”, double-check your ticker symbol and attributes for accuracy. Also, note that Google Sheets has usage limits for GOOGLEFINANCE, so if you exceed those limits, you may need to wait before trying again.

GOOGLEFINANCE is a fantastic tool for anyone who needs to work with financial data in Google Sheets.

With its straightforward syntax and wide range of supported data types, you can easily pull in real-time and historical information for stocks, mutual funds, currencies, and more.

Whether you’re building financial models, creating dashboards, or just need quick access to market data, GOOGLEFINANCE is a valuable tool to have in your spreadsheet arsenal.

Tip: Beyond automatically tracking your positions in Google Sheets, you can also create a free portfolio with the Google Finance website.