Site icon Professor Excel

Currency Conversion in Excel: Tutorial + Free Excel Add-In

exchange, rates, excel, currency, currencies, conversion

Exchange Rates in Excel

In today’s business world, there is hardly any Excel workbook not  using currencies and exchange rates. Example: When I want to know my revenue for the last month – let’s say of my Excel add-ins or books – I receive an Excel file containing data in various currencies. Because I’m located in Germany, I want to convert everything to Euro. That’s when the trouble starts: Which exchange rate do I use for example for converting US Dollar to Euro? And more importantly: Where do I get the currency exchange rate in a convenient and fast way?

Free sources

Let’s start with the possible sources. There are many sources for exchange rates online and the all offer them in a more or less convenient way. Here are some selected sources.

Google Finance

A standard Google search returns such result.

Actually there are two approaches with Google. The first one is just to use the standard Google search. Just type “USD to EUR” and you’ll receive the exchange rate.

Google’s currency converter

The second option has the advantage, that Google provides a very compact tool which you could use for some VBA automation. The link is quite straight-forward: https://www.google.com/finance/converter?a=1&from=USD&to=EUR. For converting other currencies than USD to EUR, just replace the respective symbols within the link. That’s it. You can furthermore define the amount by changing a=1 within the link: If you want to convert 2 USD to EUR, just say a=2 so that the complete link would be https://www.google.com/finance/converter?a=2&from=USD&to=EUR.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Yahoo!Finance

Yahoo!Finance offers a similar functionality as Google Finance.

Similar to Google Finance, Yahoo!Finance also offers exchange rates. You can find them here:

https://finance.yahoo.com/quote/USDEUR%3DX

In this link, you can simply replace the USD and EUR symbols by other currencies. If you for example want to convert GBP to AUD, then the link would be

https://finance.yahoo.com/quote/GBPAUD%3DX

Oanda.com

Oanda offers a convenient interface but only limited features.

If you search for an exchange rate on a specific day, you could use oanda.com. The complete link is https://www.oanda.com/currency/converter/ and you can simple type your currencies and set the day (up to 180 days back).

Unfortunately, you can’t download time series of exchange rates for free. You’d need a Pro Account (75 USD per month). So if you just quickly need to look up an exchange rate within the last 180 days, this is a convenient tool. Otherwise you should rather use one of the other sources (below).


Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...

Boost your Excel skills: Learn the best Excel tricks and tutorials!


European Central Bank (ECB)

The European Central Bank offers a CSV download for the historical exchange rates.

If you are looking for a free and comprehensive database, maybe one of the large banks is best for you. The European Central Bank offers daily exchange rates, always updated at 4 pm (CET). You can download the last 18 years as a CSV file.

Please note the following comments:

US Federal Reserve Bank

The FED offers the same type of data but is due to the missing download function not as convenient as the ECB.

Similar to the ECB, the Federal Reserve Bank provides long time series of exchange rates. The format is not as handy as the CSV download of the ECB though.

The link: https://www.federalreserve.gov/releases/h10/hist/. You have to select your desired currency here first and get a long website for each currency.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

How to work with exchange rates in Excel

Once you’ve found the exchange rates you need, there are some useful tips & tricks of how to integrate them into your Excel workbook. First you have to decide, if you want to use a fixed rate for all conversions or if you rather want to use daily rates.

Fixed rates

Fixed rates – that means you use the same rate for each currency conversion independent of the date – have one big advantage: They are much easier to integrate into your Excel sheet. Just find one rate (usually from either Google Finance, Yahoo!Finance or Oanda.com as shown above) and integrate it into your workbook.

Use named fields for referring to the global exchange rate in your Excel workbook.

Please note the following advice: It’s recommended to set up one worksheet containing the basic assumptions. In this case, you’d have a worksheet containing all exchange rates. Whenever you use an exchange rate, link to the respective cell.

Also, try to name the cells, for example “ExchangeRateUSDEUR”. Now you can easily integrate the exchange rate into your formula: =A1*ExchangeRateUSDEUR

Daily rates

Feel free to download the example workbook.

If you want to use daily exchange rates, you have to work a little bit harder. Recommendation: Download the exchange rates from the ECB. Open the CSV file by double-clicking on it and copy the sheet into your Excel workbook. Now use the 2-dimensional lookup INDEX/MATCH/MATCH for lookup up the exchange rate you need.

As you can see on the screenshot, the formula is quite long. Because all the exchange rates given in the CSV file are converting to EUR, you need to look up two exchange rates and divide them by each other.

Please feel free to download the example workbook here.


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Free Excel add-in for all currency exchange rates

 – Update March 24, 2018 – Because Google removed the simple Google Finance website which provided the current exchange rates, we are switching to Yahoo Finance now. If your current version (1.0 and 1.1 of the add-in) have problems with the formula “PROFEXCurrentExchangeRate”, please download the current version below and use it instead.

We have a gift for you. Completely free! Our Excel add-in “Professor Excel Currency Converter”. Get up-to-date exchange rates directly into your Excel workbook. Once installed, you will see a new section on the right-hand side of the “Home” ribbon of Excel.

How to use the free Excel add-in

Using the Excel add-in “Professor Excel Currency Converter” is quite easy.

For installing the add-in, download the setup file below, click on it and follow the instructions.

Once installed, you can simply use it by typing these two formulas:

  1. =PROFEXCurrentExchangeRate(“USD”,”EUR”)
  2. =PROFEXExchangeRate(“USD”,
    “EUR”,TODAY()-3)

Get the current exchange rate

The first formula, “=PROFEXCurrentExchangeRate(“USD”,”EUR”)” has two arguments: The currency from and the currency to. Quite simple, isn’t it?

Please note: The current exchange rates are only updated when you press the “Update Data” button. (The reason is, that – if you use it multiple times – your Excel might freeze if the exchange rates are updated permanently.)

Get historical exchange rates

The second formula =PROFEXExchangeRate(“USD”,”EUR”,TODAY()-3) has a three arguments:

  1. The currency from.
  2. The currency to.
  3. The date.

Very easy, isn’t it? You can further define within the settings, what happens if an exchange rate is not available (e.g. for weekends, public holidays):

Specifications in detail

AUD CNY EEK HUF ISK LVL NOK ROL SGD TRL
BGN CYP GBP IDR JPY MTL NZD RON SIT TRY
BRL CZK HKD ILS KRW MXN PHP RUB SKK USD
CAD DKK HRK INR LTL MYR PLN SEK THB ZAR
CHF


var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 );

script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Download

Please feel free to download the Excel Curency Converter Add-In here: Download.

Exit mobile version