Google Finance Script: Real-Time Data in Your Spreadsheet

Google Finance (GOOGLEFINANCE) is a powerful built-in function within Google Sheets that allows you to retrieve real-time market data directly into your spreadsheets. This means you can access up-to-date stock prices, historical data, currency conversions, and mutual fund information without manual data entry or relying on external APIs that require subscriptions.

Basic Syntax and Usage

The core syntax of the GOOGLEFINANCE function is:

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

Let’s break down each parameter:

• ticker: (Required) The stock ticker symbol (e.g., “GOOG” for Google, “AAPL” for Apple) or currency pair (e.g., “USDJPY” for US Dollar to Japanese Yen). For mutual funds, use the ticker symbol followed by “Mutual Fund” (e.g., “MUTF_US:VTSAX” for Vanguard Total Stock Market Index Fund Admiral Shares).
• attribute: (Optional) The specific piece of information you want to retrieve. Common attributes include:
  • "price": The current price.
  • "high": The current day’s high price.
  • "low": The current day’s low price.
  • "volume": The current day’s volume.
  • "marketcap": The market capitalization.
  • "pe": The price/earnings ratio.
  • "eps": Earnings per share.
  • "high52": The 52-week high.
  • "low52": The 52-week low.
  • "change": The price change from the previous day’s close.
  • "changepct": The percentage price change from the previous day’s close.
  • "closeyest": The previous day’s closing price.

  If no attribute is specified, the function defaults to returning the current “price”.

• start_date: (Optional, only for historical data) The start date for retrieving historical data. Must be a date object or a text string that can be parsed as a date (e.g., “1/1/2023”).
• end_date | num_days: (Optional, only for historical data) Either the end date for retrieving historical data or the number of days of historical data to retrieve. If using a date range, this must be a date object or a text string that can be parsed as a date. If using a number, it represents the number of days back from today.
• interval: (Optional, only for historical data) The frequency of the data. Possible values are “DAILY” (default) or “WEEKLY”.

Examples

• Get the current price of Google stock: =GOOGLEFINANCE("GOOG")
• Get the 52-week high of Apple stock: =GOOGLEFINANCE("AAPL", "high52")
• Get the historical daily prices of Microsoft (MSFT) from January 1, 2023, to January 31, 2023: =GOOGLEFINANCE("MSFT", "price", "1/1/2023", "1/31/2023")
• Get the past 30 days of daily prices for Tesla (TSLA): =GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY())
• Convert 100 US dollars to Euros: =GOOGLEFINANCE("USDEUR", "price") * 100

Limitations and Considerations

• Data Delay: The data is typically delayed by up to 20 minutes. It’s not suitable for high-frequency trading.
• Data Availability: The availability of specific attributes may vary depending on the ticker.
• Rate Limiting: Google imposes rate limits on the GOOGLEFINANCE function. Excessive use may result in errors. Spread out your calls and avoid constantly refreshing the sheet unnecessarily.
• Error Handling: Be prepared to handle errors using IFERROR or similar functions, as data may be unavailable or the ticker symbol may be invalid.
• Currency Conversion: Ensure correct ticker format (e.g., “USDEUR” not “USD/EUR”).

The GOOGLEFINANCE function provides a simple and effective way to integrate financial data into your Google Sheets, making it a valuable tool for investment tracking, portfolio analysis, and financial modeling.