Do you know that Google Sheets has a function that makes currency conversion easy for you all? Yes! If you are a person who is shuffling your ideas to find a way to convert currencies more easily, this article is going to be a lifesaver!

The Power of GOOGLEFINANCE

Did you hear power? Yes! We can very easily conclude that GOOGLEFINANCE is one of the most powerful tools in Google Sheets, built for your rescue! For real-time exchange rates, the GOOGLEFINANCE function is such a huge relief. All you have to do is simply put on the formula

=GOOGLEFINANCE(“CURRENCY:SourceCurrencyTargetCurrency”)

Let’s take an example. You need to convert USD to EUR; simply replace the source and target currency in the formula, as:

GOOGLEFINANCE(“CURRENCY: USDEUR”)

Beyond the Basics

• Multiple Conversions: Create a conversion table by using multiple Google Finance formulas in adjacent columns, displaying rates for various currencies.
  
  

• Error Handling: Consider incorporating error handling to account for situations where Google Finance can't retrieve a rate.
  
  

• Formatting: Apply custom formatting to your converted amounts for a clear and consistent presentation.

Understanding Google Sheets currency conversion

If you are wondering how to convert currency in Google Sheets and what the whole formula means, here is a clear breakdown of it:

=GOOGLEFINANCE("CURRENCY:<CurrencyCode1><CurrencyCode2>")

For example,

1. Choose the cells that need the formula to be applied
   
   

   

   
   

2. Type the formula:
   
   

   =GOOGLEFINANCE("CURRENCY:USDINR")

   
   

   

• =GOOGLEFINANCE: This is a function that makes sure the data is retrieved from Google Finance to the Google Sheets.
  
  

• CURRENCY: This is to represent that we are working with currencies.
  
  

• <CurrencyCode1>: This is the field where you should replace the three-letter code of the currency you are converting from.
  
  

• <CurrencyCode2>: This is the field where you should replace the three-letter code of the currency you are converting to.

How to Use GOOGLE FINANCE to Fetch Historical Exchange Rates?

Do you know that Google Sheets also allows you to get historical exchange rates? Yes! All you have to do is enter a start_date and an end_date (which is optional)

Here goes the syntax:

=GOOGLEFINANCE(“CURRENCY:USDGBP”, “price”, DATE(YYYY,MM,DD), DATE(YYYY,MM,DD), “DAILY”) or “WEEKLY”

If you want to fetch historical exchange rates, you can also customize the syntax.

GOOGLEFINANCE ("CURRENCY:<source_currency_symbol><target_currency_symbol>", [attribute], [start_date], [number_of_days|end_date], [interval])

Here,

• The attribute parameter shows the type of data that needs to be retrieved. It is a string value and by default, the value is “price”
  
  

• The start_date parameter represents the date from when we want historical data to begin
  
  

• In the fourth parameter, either the end_date or the number of days from the start_date can be entered.
  
  

• The interval parameter represents the frequency of the returned data. You can enter “DAILY” or “WEEKLY” in this column, based on your requirements.

GOOGLEFINANCE to Fetch Currency Exchange Rates Over a Time Period

To understand how GOOGLEFINANCE fetches currency exchange rates over a period of time, let’s see an example:

You are in need to fetch currency rates from November 1st of 2023 to November 15th of 2023, USD TO INR:

1. Select a cell from where you want to start displaying the exchange rates.
   
   

   

   
   

2. Type:
   
   

   =GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, DATE(2023,11,01), DATE(2023,11,15), “DAILY”)

   
   

3. Hit the Enter key
   
   

• Here, The first column contains dates from Nov 1 to Nov 15, 2023. The second column has Google Finance exchange rate data for that day.
  
  

• The “DAILY” keyword in the formula is used to represent the daily rates. For weekly rates, you can replace the keyword with “WEEKLY”.

Google Sheets Currency Conversion Attribute Values

Do you know that there are certain Google Sheets currency conversion attribute values that can be used? Let us have a look at them:

For any real-time data, you can choose from the following:

• volume – If you want that day’s trading volume

• marketcap – If you want the market capitalization of the stock

• priceopen – If you want to get the price when the market opens.

• high – If you want that day’s highest price

• low – If you want that day’s lowest price

For any historical data, you can use the below attributes. Also, these can be used for any specific date, and not just that day:

• high – If you want a high price 

• low – If you want the low price

• open– If you want the opening price

• close– If you want the closing price

• volume– If you want the volume

• all – If you want all of the above-mentioned information

Three-Letter Currency Codes in Google Finance

If you want to convert the values to another currency, you must know a certain set of codes. They are the three-letter codes. Take a look at some of the most commonly used GOOGLEFINANCE currency codes:

Customizing Currency Formatting

Managing Google Sheets currency conversion over time:

1. Fetch Historical Exchange Rates:
   
   

• Use the GOOGLEFINANCE function to fetch historical exchange rates for specific dates. For example:
  
  

=GOOGLEFINANCE("CURRENCY:USDINR", "price", DATE(2023,1,1), DATE(2023,12,31), "DAILY")

This formula fetches daily exchange rates between USD and INR for the year 2023.

2. Perform Currency Conversion:
   
   

• Multiply the amount in the original currency by the historical exchange rate to get the equivalent amount in the target currency.

=Original_Amount * Historical_Exchange_Rate

Advanced Tips and Tricks for using GOOGLEFINANCE in Google Sheets

Here are some pro tips and tricks for Google Sheets currency conversion:

• Use array formula

• Automate Currency Conversion

• Handle errors using functions like IFERROR 

• Use currency symbols

Thinking of choosing a good platform to get all these day-to-day international transactions done automatically in a minute? Superjoin is here to help you out! Reach out to Superjoin, book your calendars with us and schedule a demo for a live session! We are here to deliver the difference!