How to Use GOOGLEFINANCE Function in Google Sheets?

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

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.

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

• 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 use the GOOGLEFINANCE Attributes in Google Sheets?

Retrieve Real-Time Data on Google Finance Stocks

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:

Retrieve Historical Data on Google Finance Stocks

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

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:

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.

Other GOOGLEFINANCE Formulas: Mutual Funds and Currency Exchange

Now, let’s say you would like data related to Mutual Funds; more specifically, you would like to know more about Fidelity Contrafund (FCNTX) as it seems like a good choice for investors right now. In that case, include the values in the cells, as in the previous examples:

For a more comprehensive analysis, you can also import net asset values (NAVs), or other attributes, using the Google Sheet Add-on for Mutual Funds, which retrieves information from Morningstar as well as similar data sources. Using this add-on with the GOOGLEFINANCE function will give you a powerful analytical toolkit, not only mutual funds but also stocks, Exchange Traded Funds (ETFs), bonds, etc.

Another very common use for the GOOGLEFINANCE function is to analyze currency exchange. Users can perform calculations based on a constant up-to-date currency exchange rate which is retrieved using the following syntax:

=GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>")

For example, if you would like to know the currency conversion of US dollars (USD) into euros (EUR), we recommend that you input the following:

However, if you're interested in converting prices from USD into EUR, you can enter the prices in the USD column and refer back to the price cell in the formula:

So far, we have illustrated the exchange rate for one day, i.e. present day. However, a fixed date currency exchange rate can also be pulled for the same purpose if you don’t need a daily update in your Google Sheets.

Moreover, you can retrieve currency exchange rates during a period of time; the data will be illustrated as a line graph within each cell and give you a quick view at the rate comparison of USD with respect to other currencies. For example, here is a comparison for the last 30 days:

Conclusion

The use of the GOOGLEFINANCE function is not recommended for real-time investment decisions, given its 20-minute update delay. Nonetheless, Google Sheet users have access to financial data that can be used to keep financial reports or up-to-date calculations with live currency and stock information.

Understanding the role of each parameter in the GOOGLEFINANCE syntax will allow you to unlock the full potential of this formula in your financial analyses. We’ve illustrated examples with the most common attributes to track real-time data on stocks as well as retrieve historical data for a given time period. Furthermore, the attributes related to mutual funds and currency exchange can provide more valuable information for investors. 

Overall, the GOOGLEFINANCE function is another example of a highly useful free tool that is accessible to everyone.