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

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

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.

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.

Yahoo!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

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).

European Central Bank (ECB)

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.

• The link: https://www.ecb.europa.eu/stats/
  policy_and_exchange_rates/
  euro_reference_exchange_rates/html/index.en.html
• The direct link for the CSV download: https://www.ecb.europa.eu/stats/
  eurofxref/eurofxref-hist.zip

Please note the following comments:

• No current rates (please refer to Google or Yahoo! above).
• All rates are quoted against the Euro. So if you want to convert USD to GBP, you have to divide them yourself.

US Federal Reserve Bank

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.

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.

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

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.

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

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):

• Either use the last available exchange rate.
• Or return an error message (“#VALUE!”)

Specifications in detail

• Sources: ECB rates for the historical rates and Google Finance Yahoo Finance exchange rates for the current rates.
• Updates: Rates are updated 4 pm each working day from ECB, live (some minutes delay though) from Google Finance Yahoo Finance.
• Period: Historical rates available from 4th of January 1999 until yesterday (not for all currencies though, check the ECB link above for more information).
• Currencies available:

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

Download

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