on

 

How to Use GOOGLEFINANCE Function in Google Sheets?

Luciano Marzoni

Are you researching investment opportunities or stocks? If so, you’re going to find the GOOGLEFINANCE function very useful. This tool connects you with Google Finance, a platform that has become extremely popular with analysts who want to collect information on stocks, Exchange Traded Funds (ETFs), and financial news. The function allows you to bring all the data you want from Google Finance onto your spreadsheet and perform your analysis with minimal error and maximum efficiency.

By the end of this article, you’ll know how to use the GOOGLEFINANCE function by understanding its syntax and parameters, especially the attribute parameter which allows you to retrieve real-time and historical data on stocks. Lastly, you’ll see how more advanced attributes can help you analyze mutual funds and current exchange trends.

GOOGLEFINANCE Syntax in Google Sheets

GOOGLEFINANCE Syntax in Google Sheet

The GOOGLEFINANCE syntax, as explained in Google’s Docs Editors Help, is as follows:

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

It is important to understand the role of each parameter in the syntax as this will help you avoid formula parse errors.

  • ticker: This parameter relates to publicly traded securities which may contain letters, numbers, or both. For the company, Apple Inc., the ticker is “AAPL”, whereas for 888 Holdings PLC the ticker is a number, “888”. To avoid confusion, the trade exchange for the given stock can also be specified: “NASDAQ:AAPL” for Apple Inc. and “LON:888” for 888 Holdings PLC. Keep in mind that in all cases it is important to place the ticker and the trade exchange information between quotation marks.
  • attribute: An optional parameter that can retrieve various types of information, amongst which are price and earnings per share. Similar to the GOOGLEFINANCE ticker parameter, the attribute is read as text so it needs to be placed between quotation marks (“”). If no attribute is given, Google Sheets retrieves the price information by default.

However, you should know that this parameter becomes mandatory when you provide a date value.

GOOGLEFINANCE Attribute unspecified

If we input any date-related parameter, we will receive an #N/A message.

GOOGLEFINANCE Attribute unspecified + Date specified
  • start_date: Also optional, this parameter can retrieve historical data from a given date onwards. If no value is indicated, the information retrieved will be from that same day. If a value is given for start_date but not num_days|end_date parameter, it will only retrieve data for that day.
  • num_days|end_date: This optional parameter fetches stock data for a specific time frame. It is also important to note that dates are treated as noon UTC time. Any exchange that closes before this time will be shifted by one day.
  • interval: This final and optional parameter determines a weekly or daily frequency by using the two following inputs, “WEEKLY” and “DAILY”.
How To Import Yahoo Finance Data Into Google Sheets

How To Import Yahoo Finance Data Into Google Sheets

You want to analyze Yahoo Finance data in Google Sheets but are still using copy-paste? Here’s how to import Yahoo Finance data into Google Sheets instead.

READ MORE →

How to use the GOOGLEFINANCE Attributes in Google Sheets?

Retrieve Real-Time Data on Google Finance Stocks

GOOGLEFINANCE Attributes for Real-Time Stock Data

For example, to access the price quote for Apple Inc., the formula would be:

=GOOGLEFINANCE(“NASDAQ:AAPL”)

We would obtain 175.65.

But what if you want to retrieve real-time of all the above attributes? You can do this by simply pasting this table onto your Google Sheet without quotation marks. Then, you can adapt the formulas by referring to the cell and not the value itself:

GOOGLEFINANCE All Real-time Data Attributes

Retrieve Historical Data on Google Finance Stocks

These attributes allow you to retrieve data for deeper analysis on Google Finance stocks.

GOOGLEFINANCE Attributes for Historical Stock Data

As in the previous exercise, include the value for each parameter in the cell and then simply refer to it in the GOOGLEFINANCE formula. Don’t forget to include all remaining parameter values since we are interested in a specific time range.

For instance, to retrieve all the historical data attributes for the most recent quarter (Q3) for Apple Inc. this year (2021) we would use the following formula:

GOOGLEFINANCE All Historical Data Attributes

If you get a #REF! error, it’s probably because of how the information is distributed in Google Sheets. The program is telling us that it needs more space to display all the information since we asked for an interval of 7 days. In this case, we use a numeric value instead of “WEEKLY”.

You will soon see that it takes only a matter of seconds to receive all the necessary data to start your comparative analysis.