Google Finance Explained

The Template Guide

How is GOOGLEFINANCE Formula used?

The GOOGLEFINANCE enables you to tap into the data from Google Finance like stocks, currencies, indexes etc.. right into Google Sheet. It’s super powerful and versatile, but also intimidating at first.

ℹ️ The Template Guide will break down the formula syntax with examples, how to use the Google Finance Formula and the powerful use cases it brings to Google Sheets.

GOOGLEFINANCE Formula’s Syntax

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

//example
=GOOGLEFINANCE("USDGBP", "price", "01/01/2021", "08/31/2021", "daily")
  • ticker: The abbreviation used on financial markets to identify a financial product. Ex. USDGBP stands for the exchange rate for 1 USD to GBP and TSLA is the ticker for Tesla’s stock.
    • You must use the ticker in quotes like “USDGBP” or “TSLA” in the GOOGLEFINANCE Formula.
    • For more accuracies, you can decide which market data you’d like for a given ticker (if available). For example, “NASDAQ:TSLA” gives you the price of Tesla’s stock on the NASDAQ exchange.
    • How to find a ticker on Google Finance Website
      1. Head to Google Finance
      2. Search for the financial product you’re interested in (e.g. Tesla)
      3. Click on the relevant result suggested by Google
      4. Copy the ticker in the URL or Search box https://www.google.com/finance/quote/**TSLA:NASDAQ**
      5. Note: the market information NASDAQ is added after the ticker on Google Finance websites while it’s used before in the GOOGLEFINANCE Function (e.g. “NASDAQ:TSLA”)
      6. Commonly used ticker
        • Currencies: USDGBP
        • Stocks: APPL, MSFT,
        • Indexes: INDEXSP (S&P 500)
        • Cryptocurrencies: BTCUSD
  • [attribute]: this optional field indicates which information about the financial product you’d like the function to return. By default, it will be “price” which gives you the price of the financial product, but you might be interested by the “marketcap” or “eps” (earning per share) of a stock for example.
    • List of attributes of the GOOGLEFINANCE function
      • "price" – Real-time price quote, delayed by up to 20 minutes.
      • "priceopen" – The price as of market open.
      • "high" – The current day’s high price.
      • "low" – The current day’s low price.
      • "volume" – The current day’s trading volume.
      • "marketcap" – The market capitalization of the stock.
      • "tradetime" – The time of the last trade.
      • "datadelay" – How far delayed the real-time data is.
      • "volumeavg" – The average daily trading volume.
      • "pe" – The price/earnings ratio.
      • "eps" – The earnings per share.
      • "high52" – The 52-week high price.
      • "low52" – The 52-week low price.
      • "change" – The price change since the previous trading day’s close.
      • "beta" – The beta value.
      • "changepct" – The percentage change in price since the previous trading day’s close.
      • "closeyest" – The previous day’s closing price.
      • "shares" – The number of outstanding shares.
      • "currency" – The currency in which the security is priced. Currencies don’t have trading windows, so open, low, high, and volume won’t return for this argument.
  • [start_date]: e.g. 01/01/2021
  • [end_date|num_days]:
    • [end_date]: The end date of the time period you’re interested in (e.g. 08/31/2021).
    • [num_days]: The number of days after the start date you’re interested (e.g. 28 = 28 days or 4 weeks after the start date)
  • [interval]: “DAILY” or “WEEKLY”

Examples of GOOGLEFINANCE Top Functions in Google Sheets:

ℹ️ You can find the Google Sheet Template with all the GOOGLEFINANCE functions examples at the end of the page.

Using Currency Exchange Rates

Let’s say that you have sales data in foreign currencies (e.g. GBP) and you’d like to convert it. Having the GBP-USD exchange rate in cell D2 would enable you to multiply the GBP Price by the Exchange rate and get the USD equivalent in cell D5.

The Ticker is therefore “GBPUSD” as you’d like to know how much 1 GBP = in USD. We’re interested in the “price” of the currency today.

Here’s the formula in D4:

=GOOGLEFINANCE("GBPUSD")

Now, let’s say that the sales did not happen at the same date and we’d like to convert each sale price with the exchange rate of the day of the sale. We’re now referencing the date of the sale in the GOOGLEFINANCE function:

=GOOGLEFINANCE("GBPUSD","price",D20)

But as you can see, the GOOGLEFUNCTION on cell D14 has generate a 4×4 cell array with DATE and CLOSE as header and the respective value.

It would be much easier to manipulate in this case if we could get the “price” directly returned by the GOOGLEFINANCE function instead of an array. By using the function INDEX, we can get to the exchange rate directly. As we’re looking for the value in the second column and second row of an array, the simplified formula’s expression looks like this:

=INDEX("array", 2,2)

Now it means that we can use the formula inline with multiple sales rows to scale the table and make it more user friendly:

Now, let’s imagine that we have sales in multiples currencies and we’d like to convert everything back to USD. We can reference the origin currency and dynamically build the ticker name “GBPUSD”, “CADUSD” …etc with a concatenate function.

=CONCATENATE(D38,”USD”) = GBPUSD

📣 We’ve now been able to use the GOOGLEFINANCE Formula in Google Sheet to fetch the exchange rate between two currencies at a specific date to translate sales in foreign currency at a given date into US Dollars.

Nice isn’t it? That’s only a simple example of what the GOOGLEFINANCE Formula is capable of.

GOOGLEFINANCE Template

GOOGLEFINANCE Explained

ℹ️ How to duplicate a Google Sheet:

  1. Open the Spreadsheet link while connected to your Google Account
  2. In the Menu, click on File > Make a copy
  3. Pick a name and destination for the file in your Drive and click OK