GOOGLEFINANCE Function: An Easy Beginner’s Guide

The GOOGLEFINANCE function is an amazing tool for people who need to work with real-time finance or stock-related data. This single function gives you not only the current prices of stocks and conversion rates of currencies, but also historical data of these securities over days, weeks, months or years.

In this tutorial, we will show you how to use the GoogleFinance function to work with stocks as well as financial currency.

What Does the GOOGLEFINANCE Function do in Google Sheets?

The GOOGLEFINANCE function accepts a ticker symbol representing a particular security and returns the current or historical information relating to that security from Google Finance. You can use this function to fetch data about both stocks as well as currencies.

As such you can apply it to research investment opportunities, keep track of stock portfolio, convert between currencies, analyze and more. You can also use the fetched data to build visualizations, perform subsequent calculations and build dashboards.

A common use of this function is in computing the moving average for assets over a given time period. Similarly, you can use it to visualize trends in stock prices and make forecasts for the future.

Syntax for the GOOGLEFINANCE Function

Syntax for the GOOGLE Finance function is as follows:

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

Here,

  • ticker is the ticker symbol corresponding to the security that you want to fetch data for. It consists of initials that represent the security, along with the exchange symbol. For example, “NASDAQ:GOOG” represents Google stocks from the NASDAQ exchange. However, you could also simply use the symbol “GOOG” for this parameter.
  • The attribute parameter is a single string that specifies the data that you want to access relating to the given ticker. Some examples of attribute parameter strings include “price”, “high”, “low”, “volume”, etc. This is an optional parameter and its default value is “price”.
  • start_date specifies the starting date from which you want your data fetched. This parameter is optional and you can use it when you want to fetch historical data relating to a ticker.
  • end_date specifies the ending date up to which you want your data fetched. This parameter is also optional. Instead of a date, you can also specify the number of days from the start_date for which you want your data fetched.
  • interval is also an optional parameter, and it is used when fetching historical data. This parameter specifies the intervals between dates fetched in your historical data. The parameter can be either “daily” or “weekly”. If you want the data in intervals of 2 days, 3 days, etc., you could specify a number between 1 and 7 for the interval parameter. Numbers other than these are not allowed.

How to Use the GOOGLEFINANCE Function

Now that we have explained the basic syntax of the GOOGLEFINANCE function, let us see how we can apply the function in different scenarios

Using the GOOGLEFINANCE Function to Work on Current Stocks Data

Let us apply the GOOGLEFINANCE functions to fetch the current price of a stock, say the Google stocks (from the NASDAQ exchange). The formula in this case will be:

=GOOGLEFINANCE(“NASDAQ:GOOG”)

Notice that the above formula has only the required parameter. We did not specify any of the optional parameters. This means the attribute parameter will have the default value of “price” and since all other parameters like start_date, end_date and interval are absent, the function understands that we only want the current price of the stock (no historical data).

Here’s the result we get:

the GOOGLEFINANCE function

Now let’s say you want to see other attributes relating to the stock, like the price, volume, highest and lowest rates for the current day. Then you have to mention the attribute you want in the second parameter, as shown in the screenshot below:

 price, volume, highest and lowest rates

Notice the first parameter in the formulae of the above screenshot. Since we are using two different cell references to compose the ticker parameter, we used a concatenation operator (&) to concatenate the reference containing the exchange (B2), followed by a colon (:), followed by the reference containing the security ticker symbol (B3).

In column D of the above screenshot you can see some of the different attributes that can be used to fetch stock data for the current day.

Using the GOOGLEFINANCE Function to Work on Historical Stocks Data

If you want historical data relating to a stock, you will need to mention the third and fourth parameters in the GOOGLEFINANCE function. Say you want the closing prices of 10 days starting from February 27, 2021. Your formula would then be:

=GOOGLEFINANCE(“NASDAQ:GOOG”,”close”,DATE(2021,2,27),10)

In the above formula, we specified the third parameter as the starting date, DATE(2021,2,27) and the fourth parameter as a number. So the GOOGLEFINANCE function knows that this is the number of days. If we specified a date value, GOOGLEFINANCE would take this as an ending date value rather than the number of days from the starting date.

When you press the return key, your result would look something like this:

 googlefinance function with date value

Let’s use a more general-purpose version of the formula, so that you can customize it to your requirements:

 googlefinance formula

Notice that we specified the attribute parameter as “all”, so the GOOGLEFINANCE function shows all 5 historical data attributes:

  • Open
  • High
  • Low
  • Close
  • Volume

Moreover, since we specified the interval parameter as “daily”, the function returned daily data for 10 days starting from the given date (Feb 27, 2021), which makes 10 rows of data.

If instead of daily, you wanted to display the data in weekly intervals, you would get 2 rows of data, as shown below, since there are just 2 financial weeks in 10 days:

weekly intervals

Using the GOOGLEFINANCE Function to Work with Currencies

The GOOGLEFINANCE function also lets you get current and historical conversion rates between a number of global currencies.

To get the conversion rate between two currencies, you need to specify this in the first parameter of the function as follows:

  • The keyword “Currency
  • Followed by a colon (:)
  • Followed by the ticker symbol of the currency you want to convert
  • Followed by the ticker symbol of your target currency.

For example, the ticker symbol for US Dollars is USD while the ticker symbol for UK Pound is GBP (you can find these ticker symbols by searching on Google or by using a tool like Yahoo Finance).

To convert USD to GBP, your ticker symbol would be “Currency:USDGBP”. Note that the original currency should always come before the target currency.

The formula to get exchange rates to convert USD to GBP would then be:

=GOOGLEFINANCE(“Currency:USDGBP”)
formula to get exchange rates to convert USD to GBP

If you want to use a more general-purpose version of the formula, you can customize it to your requirements as follows:

 you can customize it to your requirements

Notice we used two different cell references to compose the ticker parameter. We used a concatenation operator (&) to concatenate the keyword “Currency”, followed by a colon (:), followed by the reference containing the original currency symbol (B2) followed by the reference containing the target currency symbol (B3).

Now you have a formula that can fetch the conversion rate of any currency to any other currency.

If you want to use this formula to convert a value from one currency to another, or to build a simple currency converter, all you need to do is import the exchange rate and then multiply it with the value that you want to convert. An example of a currency converter is shown below:

 currency converter

Here, cell B5 contains the formula:

=B4*GOOGLEFINANCE("Currency:"&B2&B3)

This formula simply multiplies the original amount with the exchange rate of converting the currency in cell B2 to the currency in cell B3 (obtained by the GOOGLEFINANCE function).

You can replace the symbols in cells B2 and B3 with your required currency symbols.

Using the GOOGLEFINANCE Function to Import Historical Currency Exchange Rates

The GOOGLEFINANCE function can also help get historical exchange rates between two currencies over a period of time.  This can give us information on how currencies have behaved in the past.

To get historical data about currency conversion rates, you will need to specify the second, third and fourth parameters too.

For example if you want to see historical exchange rates of converting US to GBP in the last 10 days, your formula would look like this:

=GOOGLEFINANCE(“Currency:USDGBP”,”price”,TODAY()-10,TODAY())
Using the GOOGLEFINANCE Function to Import Historical Currency Exchange Rates

You can also generalize the formula as shown below:

generalized formula

You should now see two new columns returned by the function – dates and closing exchange rate for each day.

You could similarly see the data in weekly intervals, simply by adding the fifth parameter “weekly”:

Note: Currencies don’t have trading windows, so you will not get any data returned attributes open, low, high, and volume.

GOOGLEFINANCE Function Not Working?

The GOOGLEFINANCE function might sometimes not work and you might find it return an #N/A error. There is not much that you can do about this, as it is one of the limitations of the function.

In fact, this function is not meant for professional use. It is mainly targeted at casual Google Sheets users who want to simply track their portfolio stocks, do some simple market analysis, or convert between currencies.

In case you need market data for professional use (for example, if you are a financial industry professional or work for a government entity), you might need to purchase additional licensing from a third-party data provider.

Conclusion

In this tutorial, we showed you some examples of how to fetch current and historical market data using the GOOGLEFINANCE function. You can use the examples we used to build your own automated Google Finance Sheet. We tried to make this tutorial as detailed and clear as we could. We hope you find it useful.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.