How to Use Google Finance Function in Google Sheets (Beginner’s Guide)

In this article, we will learn how to use Google Finance Function in Google sheets.

We have seen many things and learned many formulas and functions in google sheets so far, now it’s Google Finance Function to learn, it’s a very advanced and modern function that fetches data from the server and produce accurate results on your google sheets file.

Google Finance Function helps you in many ways, you can bring currencies data in seconds, you can exchange between currencies quickly, you can see the stock market data on your sheet, even you can make a future forecast about different markets like crypto, and stocks

This function is very powerful and it makes it easy to produce and analyze real-time results and the fluctuation coming in the data, you can use it to monitor the trend about the insights of different markets and take a good financial decision if you’re a finance professional.

For casual google sheets users, this Google Finance Function helps you to learn currency exchanges, oversee the market data, reading and analyze trends using daily up and down. Let’s see how it works and how we use this function

Use case of using Google Finance Function in Google Sheets

There are plenty of reasons we need to learn the Google Finance Function, its a very complex function and there are a lot of things we do use this Function, for example, we can bring real-time currency data in our google sheets, we can convert between all currencies, we can bring the price of any currency from a specified data to a specified data. We can see the stock prices from a specific date to a specific date, we can analyze trends of data coming in USD, Bitcoin, the Stock market, and any of the sub-categories.

Using custom formula within the Function we can bring large data and the data is auto-updated when it’s updated in real. This is the power of the Google Finance Function. We will see in detail we will see all the attributes and what are optional and mandatory attributes we will learn everything about it. For now, I think you have understood why we need to learn – How to use Google Finance Function in Google sheets.

How to use Google Finance Function in Google Sheets

To learn Google Finance Function step-by-step, we will first see the basic attributes of the function, we will see the optional and mandatory attributes, and we will see what each attribute is doing in the formula. And, of course, we will see multiple use cases to learn it completely and enable you to use it on your own and bring some great data from Google Finance using Google Finance Function, let’s move on to practical learning.

How to use Google Finance Function for Real-time Currency Conversion

In this section, we will learn how to use Google Finance Function for real-time currency conversion, we normally directly use the internet to see the currency exchange rates, we search their teams like USD to CAD, to find out the exchange rates, but what if you have google sheets that have the exchange rate data for as many currencies as you want and it auto-updates in every 20 minutes? Isn’t it excited? I think it is. So, we are going to learn how easy it is to do this in your google sheet file. Follow to below steps to learn along with me practically.

Step 1

Open the Google sheets file

Step 2

In any cell, write the Google Finance Function

=GOOGLEFINANCE()

Step 3

Now pass the attribute value as “Currency (Ticker is the value to tell google sheets, what exactly you want to do)

Step 4

After “Currency, within the same double quotes then a colon: then pass the two currency names i.e. USDCAD without space in between.

Step 5

Now close the double quotes, and parentheses the function will be

=GOOGLEFINANCE(“Currency:USDCAD”)

Step 6

Hit Enter you and will get the value of 1 USD against 1 CAD

This is how simply we can make currency conversion in google sheets, with an auto-update feature every 20 minutes.

Now, similarly, you can use a list of currency codes and directly converts them simply using the cell attributes in Google Finance Function.

Step 1

Write some Currency codes in your google sheets file

Step 2

In the adjacent column’s cell, write the Google Finance Formula

Step 3

Now you can pass the cell addresses instead of passing currency codes in the Google Finance Function using Ampersand &

This is how the function will look like

=GOOGLEFINANCE(“Currency:”&A2&B2)

Step 4

Drag down the function for the entire column

You saw how easily we converted from many-to-many currencies using a one-time function and dragging for the entire column.

But this is not the only thing you can do using Google Finance Function.

Now, in the next method, we will learn a new thing.

How to use Google Finance Function to Retrieve Market information and Data

In this section, we will learn how to use Google Finance Function to retrieve market information and data, in this section we will how to fetch real-time market data by various means, we can bring data for today, for 1 week back, and also the predicted data for 1 week in future. This helps us to see the overall market trend and help us to end up making proficient financial decision for personal or business.

Understanding the General syntax of Google Finance Function 

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

Ticker – The ticker is the first parameter, it’s mandatory to use to avoid discrepancies. i.e. using “NASDAQ:GOOG” instead of “GOOG.” only. both work the same but for better results “NASDAQ:GOOG” is recommended.

Attribute – The attribute is an optional argument, (its price by default), it is the exact property to fetch about the specified ticker, or Google Finance will automatically use a ticker that fits best for your problem (it can cause discrepancies).

Attributes that can be used in Google Finance Function are following

In this example, we will how to fetch the data for different stocks

Step 1

Write the Google Finance Function

Step 2

Pass the “Exchange:Symbol” to fetch the price

Step 3

Hit Enter key, and you get the price for a specific company in a specific exchange

Step 4

You can use other attributes than the price

This is how you can use any attribute instead of (default attribute) price

How to use Google Finance Function to Retrieve Historical Market Data

Attributes that can be used in Google Finance Function are following

In this section, we will learn how to use Google Finance Function to retrieve historical market data in google sheets, we will see fetching the data from a specific date to a specific date. For this example, I have an example we will see practically how it is done.

The Google Finance syntax for this example will be

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

NASDAQ: is the exchange

GOOG: is the symbol

CLOSE: is an attribute

DATE(): from the date

5: number of days after the specified date

Using a similar formula, we will pull the Bitcoin value against USD for the last 7 days

Step 1

Write the Google Finance Function

Step 2

Write the symbol for BTC to USD in double quotes “BTCUSD”

Step 3

Add a comma, and pass the attribute in double quotes (in this example I used “Closed”

Step 4

Add a comma, and pass the Date(yy,mm,dd) (i passed Date(2022,07,10)

Step 5

Add a comma, and write the number of days, (here I will use 8 as my date is 9 days behind the current date)

Step 6

The Function will be like this

=GOOGLEFINANCE(“BTCUSD”,”CLOSE”,DATE(2022,6,20),20)

Press Enter key, and you’re done.

Similarly, you can do the same for currencies like USD to CAD, etc.

Similarly, you can use other symbols than only currencies like exchange “New York Stock Exchange”, and company names like “New Market Corporation” (NEU)

Notes

FAQ

How Google Finance function works?

Google Finance function retrieves the data from its server where all the data and information are kept, it retrieves real-time data and auto updates it every 20 minutes, since both are Google products so we can expect accurate results.

Conclusion

So today we learned a great function which is the Google Finance function, we learned how to use Google Finance Function in google sheets, and we learned various scenarios that started from the basics and ended up fetching real-time data for currencies, crypto, stocks, etc. I hope you guys like this article, please comment below if you have any problems see you soon with another great article. Thank you. Keep learning with Office Demy and don’t forget to subscribe to us.