Staying up to date with the latest conversion rates can be a daunting task. But it doesn’t have to be. Read on to learn how to use Google Sheets for currency conversion and automate this problem away.
Whether you want to convert traditional currencies and crypto. We will cover three ways to automate this process so the data you need is readily available and updated.
Make a copy of this spreadsheet and follow along!
How to use Google Sheets for currency conversion
3 ways to convert currencies with Google Sheets
There are many ways to convert currencies on a spreadsheet. Clearly you could do this by hand, plugging in conversion rates on a need-to basis. But that is a lot of wasted effort, below we go over three ways to automate this task.
1. GOOGLEFINANCE function
The GOOGLEFINANCE function retrieves real-time financial information, such as stock prices, market values, and currency exchange rates. It can also convert currencies by fetching the latest exchange rates directly into your Google Sheets.
Read our post on GOOGLEFINANCE to learn all its applications.
Convert values
If you only need to convert a handful of values you could use GOOGLEFINANCE to convert them all. In this example we will convert Euros to US Dollars.
=A2 * GOOGLEFINANCE(“CURRENCY:EURUSD”)
Keep in mind that this function requests information from an external source (a Google API), and that makes it resource intensive.
If you have a lot of values to convert, using several formulas with GOOGLEFINANCE could slow down your spreadsheet. The next method solves this issue.
Retrieve conversion rates
If you have to convert a lot of values, you can create a formula to include the conversion rate in your spreadsheet. Then refer to that conversion rate within other formulas to calculate the conversion.
For example, the formula below gives you the conversion rate from Canadian Dollars into US Dollars.
=1 * GOOGLEFINANCE(“CURRENCY:CADUSD”)
You could also get the reverse conversion rate by inverting the currency codes.
=1 * GOOGLEFINANCE(“CURRENCY:USDCAD”)
After this all you have to do is multiply the desired value by the corresponding conversion rate.
In terms of processing power, this is the most efficient way to use GOOGLEFINANCE to get up-to-date conversions.
2. IMPORTXML function
GOOGLEFINANCE is great, but it does have one major drawback: it doesn’t work for all currencies. This is particularly true for crypto, as it doesn’t work for all tokens.
This is where IMPORTXML can be great, because it can fetch data straight from a website.
Let’s say you want to check the current price of the DOGE token. You could scrape CoinMarketCap for this information using the formula below.
=IMPORTXML(“https://coinmarketcap.com/currencies/dogecoin/”, “//span[@class=’sc-f70bb44c-0 jxpCgO base-text’]”)
This formula relies on the website to keep working, and that is a major drawback. If this site changes its structure or has downtime, your spreadsheet will cease to work correctly.
Another issue with this function is that it is quite complicated. For an easier way to get it to work you could get a little help from AI.
If you want to learn everything about this function, check out this step-by-step guide on IMPORTXML.
3. API integration
The last way to convert currencies is to get information straight from an API. This method solves the issues of the last two:
- The right API can complement or replace GOOGLEFINANCE.
- These services usually provide more stability than using IMPORTXML.
This solution leverages Sheetgo to bring current exchange rates from an API into your spreadsheet.
The hardest part is to find the right open API to suit your needs. After that you just have to follow a few simple steps:
2️⃣ Create a New workflow
3️⃣ Select HTTP Request as your source
4️⃣ Set the URL for the API (we used https://restcountries.com/v3.1/currency/cop?fields=currencies).
If this isn’t an open API, set your credentials in the header. Unfortunately closed APIs without header authentication are not compatible with Sheetgo.
5️⃣ Proceed to Next step, add a data processor if you want
6️⃣ Finally, select the destination
Convert currencies effortlessly
Currency rates change constantly and keeping up with them can be a repetitive task. We covered some of the best ways to automate this and when to use each one.
