Google Finance sector function

GOOGLEFINANCE Function: An Easy Beginners Guide

The GOOGLEFINANCE functionis 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 functionto work with stocks as well as financial currency.

What Does theGOOGLEFINANCE Functiondo 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 averagefor assets over a given time period. Similarly, you can use it to visualize trends in stock prices andmake forecasts for the future.

Syntax for the GOOGLEFINANCE Function

Syntax for the GOOGLE Finance function is as follows:

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_datespecifies 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_datefor 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:

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).

Heres the result we get:

Now lets 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:

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 Functionto 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:

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:

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

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:

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:

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

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:

Here, cell B5 contains the formula:

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:

You can also generalize the formula as shown below:

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 dont 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 ownautomated Google Finance Sheet. We tried to make this tutorial as detailed and clear as we could. We hope you find it useful.