Have you ever needed to convert currency in Google Sheets? Well, look no further! In this article, we’ll explore how you can use the powerful GoogleFinance function to easily convert currency in Google Sheets. Not only that, but we’ll also learn how to get the latest auto-updating currency exchange rates directly in your spreadsheet. So, let’s dive in!
GoogleFinance Function: A Brief Overview
Before we get into the nitty-gritty of currency conversion, let’s take a moment to understand what the GoogleFinance function is all about. This function is a game-changer for those dealing with stock markets as it allows you to import current and historical security information into Google Sheets. But that’s not all! You can also use GoogleFinance to fetch current and historical currency exchange rates. It’s like having a finance expert right at your fingertips!
The syntax of the GoogleFinance function is as follows:
GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])
For the purpose of this article, we’ll focus on the currency exchange part of the GoogleFinance function. So, let’s get started with converting currency in Google Sheets!
Get Latest Currency Exchange Rates
If your business operates globally, it’s crucial to stay updated with the latest currency exchange rates. Knowing how much one currency is worth in relation to another is essential for making informed financial decisions. Fortunately, with GoogleFinance, you can effortlessly retrieve the latest currency exchange rates directly in your Google Sheets.
To convert currency, you need to know the currency code for each currency you’re working with. For example, the currency code for the US Dollar is USD, and for the Indian Rupee, it’s INR. Armed with these codes, you can use the GoogleFinance function in Google Sheets to convert currency.
Here’s an example formula to convert USD to INR:
=GoogleFinance("CURRENCY:USDINR")
The above formula will return the current exchange rate between USD and INR, which at the time of writing this article is 63.575. However, keep in mind that exchange rates are constantly fluctuating, so the rate you get may be different. GoogleFinance is a volatile function similar to Date and Time functions. So, make sure to update your sheet to get the latest rates!
You can also substitute the currency codes to convert between other currencies. For example, you can convert USD to EUR by using the formula =GoogleFinance("CURRENCY:USDEUR").
Import ISO 4217 Currency Codes
To make currency conversion even easier, you can import all the world currencies and their corresponding ISO 4217 Currency Codes into Google Sheets. This way, you’ll have a handy reference for all the currency codes you need.
You can use the IMPORTHTML function in Google Sheets to import the currency codes from the website www.xe.com. Here’s an example formula to import the currency codes:
=IMPORTHTML("https://www.xe.com/currencytables/?from=USD", "table", 1)
By using this formula, you can retrieve a comprehensive list of currency codes along with their associated country names. With the currency codes at your disposal, you can easily perform currency conversions using the GoogleFinance function.
Create Currency Exchange Rates Table
Now that you have imported the currency codes and understand how to convert currency using GoogleFinance, let’s take it a step further. You can create a dynamic currency exchange rates table in Google Sheets to get the exchange rates for all the currencies in the world.
In column C of your sheet, where you imported the currency codes, you can apply the GoogleFinance formula to fetch the current exchange rates. For example, if you want to get the exchange rates for 1 USD, you can use the following formula in cell C3:
=GoogleFinance("CURRENCY:"&$C$2&A3)
By copying and pasting this formula down the column, you’ll get the exchange rates for all the currencies.
Historical Currency Exchange Rates
But what if you need historical currency exchange rates? Not to worry, GoogleFinance has got you covered! You can specify a specific date or a time period to retrieve historical currency exchange rates.
To get the currency exchange rate of a specific date, use the following formula:
=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2017,9,1))
The above formula returns the exchange rate of USD with EUR on September 1st, 2017.
If you want to get currency exchange rates for a time period, use the formula below, specifying the start and end dates:
=GOOGLEFINANCE("CURRENCY:USDINR", "price", DATE(2017,12,1), DATE(2017,12,31), "DAILY")
The above formula returns the currency exchange rates of USD with INR for the month of December 2017.
Automate Currency Conversion and Sum
Let’s say you have income sources from different countries, each in a different currency. You can automate the conversion of all these currencies to your preferred currency and even sum them up!
In the example below, we have income sources in USD, ILS, and AUD, which we want to convert to INR and then sum up:
| Currency | Amount |
|---|---|
| USD | 100 |
| ILS | 200 |
| AUD | 150 |
We can achieve this by using simple formulas. For example, to convert USD to INR, you can multiply the amount in USD by the exchange rate:
=B2 * GoogleFinance("CURRENCY:USDINR")
You can apply similar formulas for the other currencies and then use the SUM function to get the total converted amount.
Removing Header Labels in GoogleFinance Historical Data
When importing historical exchange rates using the GoogleFinance function, you may encounter column labels like “Date” and “Close.” If you want to remove these labels, you can use the QUERY function in Google Sheets.
Here’s an example formula to remove the column labels:
=QUERY(GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2017,12,20)),"SELECT Col1, Col2 label Col1 '', Col2 ''")
And there you have it! We’ve explored how to convert currency, import currency codes, create currency exchange rate tables, fetch historical currency exchange rates, automate currency conversion, and even remove header labels using GoogleFinance in Google Sheets.
Now you have the power to effortlessly handle currency conversions in your spreadsheets. Happy calculating!
Remember to check out Crawlan.com for more tips and tricks on Google Sheets and other helpful resources.
Disclaimer: This article provides information and guidance but does not constitute financial advice. Always consult with a financial professional for specific financial decisions.
