How to Use GOOGLEFINANCE Function in Google Sheets

Google has its own subdomain for financial data called Google Finance. It consolidates data from several financial markets in the US, Europe, and Asia, and it also includes finance and business-related news. You can search for a specific company and view their performance in a specific financial market. For our examples, we will import data from Netflix as they perform in the NASDAQ market. 

How can we import this vast wealth of useful financial data? Fortunately, Google Sheets has its own built-in function called GOOGLEFINANCE! You no longer need to access an API through a custom code loaded in Google Apps Script or install add-ons to import relevant financial data. We will learn in this tutorial how to use the GOOGLEFINANCE function. Are you ready?

How to find the ticker symbols for GOOGLEFINANCE Google Sheets Function From Google Finance

The GOOGLEFINANCE function requires you to specify the ticker symbol for a company in a financial market in the following format, which includes both the exchange symbol and the ticker symbol.

MARKET:COMPANY

Where MARKET and COMPANY are both examples of a ticker symbol used for the financial market and company, respectively. One way to find it is by looking at the URL of the page for the company:

Here, NASDAQ is the code for NASDAQ market while NFLX is the ticker symbol for Netflix. So, for the ticker that we will use in GOOGLEFINANCE, it will be as follows:

NASDAQ:NFLX

How to import real-time stock data using GOOGLEFINANCE Function

Syntax

The GOOGLEFINANCE function for importing real-time data has the following syntax:

GOOGLEFINANCE(ticker, [attribute])

Where

• ticker is the code that contains both the company and the market in the following format: MARKET:COMPANY
• attribute is any data associated with the ticker such as price, volume, daily high and low, etc. 

The minimum information you need for GOOGLEFINANCE to work is the ticker. This will give you the latest price in the specified market. Both the ticker and the attribute should be enclosed in quotation marks. 

Note that GOOGLEFINANCE function does not allow combining two or more attributes in the same formula. So if you want to display two or more real-time attributes, you have to write two GOOGLEFINANCE formulas in different cells of your Google Sheet.

Import the latest price 

There are two ways to do this. Simply specifying the ticker is enough:

=GOOGLEFINANCE("ticker")

Where ticker follows the MARKET:COMPANY format.

You can also specify the attribute price, but you will get the same result:

=GOOGLEFINANCE("ticker", "price")

Import daily high and low

To import the daily high, use the high attribute:

=GOOGLEFINANCE("ticker", "high")

To import the daily low, use the low attribute:

=GOOGLEFINANCE("ticker", "low")

List of real-time attributes

Here is the list of real-time attributes you can use in GOOGLEFINANCE:

• "price" - Real-time price quote, delayed by up to 20 minutes.
• "priceopen" - The price at 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 stock 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.

Combine GOOGLEFINANCE Function with drop-down box

You can add these attributes in a drop-down box and then point GOOGLEFINANCE function to that box so you can change the attribute to whatever you need to check:

=GOOGLEFINANCE("ticker",  cell_containing_attribute)

The result will look like as follows:

Learn how to add a drop-down box in Google Sheets. 

How to import historical stock data using GOOGLEFINANCE Function

Syntax

GOOGLEFINANCE also allows you to import historical data. The syntax is as follows:

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

Where

• ticker is the code that contains both the company and the market in the following format: MARKET:COMPANY
• attribute is any historical data associated with the ticker such as price, volume, daily high and low, etc. 
• start_date can either be used to specify the specific date for the data or as the start date for the range of dates that you want to import to your sheet
• end_date is the end date of the range
• num_days is the number of days included on the range to be imported to the sheet
• interval is the interval of each row in the output, which can either be daily, weekly, or monthly

The attributes are as follows:

• "open" - The opening price for the specified date(s).
• "close" - The closing price for the specified date(s).
• "high" - The high price for the specified date(s).
• "low" - The low price for the specified date(s).
• "volume" - The volume for the specified date(s).
• "all" - All of the above.

Unlike real-time data, GOOGLEFINANCE has an option to display all the historical stock data in a table for the given range of dates. You will prefer importing all possible stock data, so you can use the following syntax:

=GOOGLEFINANCE(“ticker”, “all”, start_date, end_date)

Where you can get daily data for the OLHVC data:

The date column does not only contain the date but also the closing time of the market. 

List of historical data attributes for mutual funds

‍In addition to historical data for stocks, the GOOGLEFINANCE function in Google Sheets can return mutual fund data as well. Mutual funds only update their price once a day after the market closes, so attributes such as average daily trading volume are not available live, and only historical data is available. The attributes for mutual fund data are as follows:

• "closeyest" - The previous day's closing price.
• "date" - The date at which the net asset value was reported.
• "returnytd" - The year-to-date return.
• "netassets" - The net assets.
• "change" - The change in the most recently reported net asset value and the one immediately prior.
• "changepct" - The percentage change in the net asset value.
• "yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.
• "returnday" - One-day total return.
• "return(# of weeks)"- total return over either 52 weeks, 156 weeks (3 years), or 260 weeks (5 years)
• "incomedividend" - The amount of the most recent cash distribution.
• "incomedividenddate" - The date of the most recent cash distribution.
• "capitalgain" - The amount of the most recent capital gain distribution.
• "morningstarrating" - The Morningstar "star" rating.
• "expenseratio" - The fund's expense ratio.

Handling date formats in GOOGLEFINANCE Function

There are a wide variety of formats that you can use when specifying the dates. For starters you can use the DATE function with the following syntax:

DATE(year, month, day)

This is handy and helps you prevent most of the issues regarding date formats in Google Sheets. If you want to regularly import the last few days of historical data, you can use TODAY function in the following manner:

=GOOGLEFINANCE("ticker","attribute",TODAY()-n,TODAY())

where n is the number of days that you want included. For example, if you want the data from the last 30 days, then your formula will be:

=GOOGLEFINANCE("ticker","attribute",TODAY()-30,TODAY())

If you want to get 30 rows of data, however, you need to account for the weekends as several markets are closed during those times. Simply adjust the number to compensate for the weekends covered by the range.

How to import currency exchange data using GOOGLEFINANCE Function

Syntax

Google Finance also offers currency exchange data, which also includes cryptocurrency data. Unlike data for companies in financial markets, currencies have less real-time data and are usually confined to their current prices. 

‍

The ticker for currency exchange data is:

Currency:CUR1CUR2

‍

Where the price of CUR1 is specified in units of CUR2. For example, if we use the following ticker:

Currency:USDJPY

Then you will get the USD to JPY exchange rates.

‍

To import the latest exchange rate, you can simply specify the ticker symbol:

=GOOGLEFINANCE("ticker")

Import historical currency exchange rates

To import historical exchange rates, use the following format:

=GOOGLEFINANCE(“ticker”, “price”, start_date, end_date)

‍

Where the same options for specifying dates for historical data in the previous section are still applicable. 

If you use the all option for the attribute, you will get the following:

Unlike in the financial markets such as stock markets, Google Finance only stores the closing price for each day. 

Visualize historical currency data with SPARKLINE

You can combine GOOGLEFINANCE with SPARKLINE to visualize the data for the last n days. The syntax is:

=SPARKLINE(GOOGLEFINANCE(“ticker”, “price”, start_date, end_date))

‍

The result will look like as follows:

Conclusion

Google makes it very clear that their financial market data is not meant for use by professionals, and any use of the data by professionals is subject to licensing fees by a third party data provider. However, for most people, using the GOOGLEFINANCE function in Google Sheets is a very useful method to obtain historical stock information and inform your investing strategy. By following this Google Sheets tutorial, you will be able to easily pull specific Google Finance data about your favorite stocks into your Google Sheets spreadsheet.

Sample Sheet

To see GOOGLEFINANCE function in action, check out the following sample sheet:

Copy of GOOGLEFINANCE Sample Sheet