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.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
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:
- =PROFEXCurrentExchangeRate(“USD”,”EUR”)
- =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:
- The currency from.
- The currency to.
- 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 FinanceYahoo 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 FinanceYahoo 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.
Hallo Henrik
Bei mir funktionert der Professor_Excel_Currency_Converter leider nicht. Das Pugin erscheint zwar im Menü aber er kennt die Formel =PROFEXCurrentExchangeRate(“USD”,”EUR”) nicht.
Ich habe Windows10 und Excel 2016 Proffesinal Plus
Viele Grüße
Rene
Hallo Rene,
was für eine Fehlermeldung erhältst du denn (#NAME, #VALUE etc.)? Darüber hinaus: Wann hast du das Add-In runtergeladen (vor etwa zwei Wochen habe ich eine Version mit Fehlerbehebungen hochgeladen)?
Du kannst schon mal die folgenden Dinge probieren:
Es wäre toll, wenn du mir kurz eine Rückmeldung geben könntest, ob das Add-In jetzt funktioniert.
Vielen Dank und beste Grüße
Henrik
Hallo Henrik
Danke für den Tipp. Mit dem Semikolon funktioniert es super!
Gibt das auch als plug in für MS Excel auf Mac. Leider nutze ich meine Excelsheet über die Dropbox auch oft mobil mit meinem Macbook. Dort läuft MS Excel 2016.
Gibt es dafür auch etwas. Wenn nicht, dann mach eich es lieber manuell mit Tabellen sonst funktioniert die Datei ja auf dem Mac nicht.
Viele Grüße
René
I love your tool! I was wondering if the date can be used as an monthly average and if so, how can that be done?
Thanks again!!
Dear Marie-Claude,
What about setting up a new worksheet, having the dates from 1st of January 2017 (or whatever date you need to start with) until today in column A. Column B has the number of month, column C the year and Column D has the exchange rate with from the currency conversion tool. Then you could use the AVERAGEIFS formula for calculating the average exchange rate. Does that solve your question?
Best regards,
Henrik
Dear Hendrik,
I believe you developed a great tool.
Is it possible to extend the current currency list. Secondly, is there a way to include cryptocurrencies?
Thanks in advance. Your time is appreciated.
Regards,
Confucius
Dear Confucius,
Thanks for the feedback! Unfortunately, the current currency list is determined by the data input, which is provided by the European Central Bank. I can only add more currencies if the daily input file contains more currencies.
Concerning cryptocurrencies: I don’t expect the ECB includes them into their exchange rate files. Which currencies are you looking for? Probably you’d also need intra-day exchange rates for them…?
However: Do you know another source for historical exchange rates, which already provides the data in a “manageable” format (e.g. csv, xlsx)? If yes, I could take a look at it.
Best regards,
Henrik
Hi Hendrik,
Thank you for an excellent piece of software.
I have been using the add-in and up until recently it has been working fine, but the last few days excel is locking up and not responding after the data is updated. I have to reload the spreadsheet.
Please advise.
Many thanks,
Peter
Hi Peter,
Thanks for the message, I’ll take a look at it and update the version if necessary.
Best regards,
Henrik
Halo Henrik,
I’ve been using your currency converter with good success for the first couple days. After that clicking the “Update” button in Excel failed. I emailed you the error messages, one of them is Error 150, where it says the update failed. I am using Office 365.
After the update fails, the formulas are changed to a directory link to the .xlam file. Something seems to have confused Excel in how to handle the updates. When typing a formula manually and NOT clicking the update button, the correct conversion is returned.
I would like to either make a small donation or purchase the Excel Tools if you could please provide me some support with this issue.
Thank you,
Stephen
Hi Stephen,
I’ve sent instructions to you by e-mail for the particular problem. I’ve also updated the version for all other users here so that it should hopefully not happen again.
Best regards,
Henrik
G’day Henrik from AUS,
I have been using PROFEX successfully but, today the calculations have failed their update. The exchange rate is not updating into the cell – the current value is “0”. I cant find a solution in help. could you please advise?
Hi Ian,
Which formula is not working? PROFEXCurrentExchangeRate or PROFEXExchangeRate?
Best regards,
Henrik
Hi Ian,
I’ve updated the add-in. Now it should work. Just download it and install it again (don’t forget to close Excel before). Please let me know if you run into any other problems.
Best regards,
Henrik
Hi Henrik,
I have just installed your utility. Thank you.
I am having the same issue as Ian.
PROFEXExchangeRate returns a value OK.
PROFEXCurrentExchangeRate returns 0.
Thanks in anticipation.
Colin
Hi Colin,
I’ve updated the add-in. Now it should work. Just download it and install it again (don’t forget to close Excel before). Please let me know if you run into any other problems.
Best regards,
Henrik
What’s the syntax for the date field? I want to get an exchange rate for April 20, 2018. Would I use:
mm/dd/yyyy
dd/dd/yy
Something else?
Thanks! love the tool!
Hi Henrik, Having some issues.
1/ New worksheet, completely blank,, nothing entered, “Remove PROFEX” finds 3 formula cells to be replaced.
2/ Pasting in the formula “=PROFEXCurrentExchangeRate(“USD”,”EUR”)” gives #VALUE1. “Update” does not change this.
David
Excel 2010
I installed the add-ins last week and they worked great. However this week neither function is updating. I downloaded the file again today and reinstalled it, but the function is still not updating.
To be more specific, for dates of April 30, 2018 and later the function returns the same value, for dates prior to April 30 the function works fine.
Hi again Henrick,
same problem as before I’m afraid. Value remains “0” after selecting “Update Data”. I noticed that when i select “Update Data” a receive an xls showing currency calculations. This data enters the clipboard. I then receive a XLS message requesting me to delete the large data in the clipboard. In an effort to help you and although I am in AUS I work for a NL company. Can direct email or TeamViewer with you outside normal hours.
Hi, I’ve downloaded your tool. Checked the trust center to make sure it’s ok. When I click update a screen with currencies and rates flashes up for a few seconds then disappears leaving me with a blank workbook. I’m using office 2016. Can you help?
Hi Henk
Id just like to inform you that the currency conversion tool is not extracting the correct currency data. The result reflected by the Excel 2016 function =PROFEXExchangeRate(“USD”,”ZAR”,TODAY()) and any other specified date does not reflect the correct corresponding value according to Yahoo Exchange or Google exchange data rates. Is it possible that the date calculation information is incorrect?
I found when using the historical date range you need to use Excel’s 5 digit date(serial) code…
for example July 31 2019 is 43677
=PROFEXExchangeRate(“cad”,”usd”,43677)
Hello Henrick,
Thank you for this very useful tool, and impressive skills!
Is there any way you can update this to include 2018 and 2019 data? Happy to make a donation!
Thanks and best regards,
Saif
Hi Hendrik,
erst mal vielen Dank fuer dein Tool.
Ich benutze es, bzw. benutze es fuer meine Depotuebersicht.
Leider hat irgend etwas gecrasht auf meinem Notebook. Moeglicherweise ein Office Update Office 365 2016?
Die Funktion wurde nicht mehr erkannt. Aber in den Zellen steht jetzt folgendes:
=’C:\Program Files\Microsoft Office\root\Office16\LIBRARY\ANALYSIS\Professor_Excel_Currency_Converter.xlam’!PROFEXCurrentExchangeRate(O28,”EUR”)*O27
Also der komplette Pfad zu der xlma. Nur ist diese Datei dort nicht (mehr).
Ich habe die App deinstalliert und heute 27.12.2019 neu runtergeladen und neu installiert.
Es installiert etwas, finde es auch unter windows Applist, aber die xlma ist nirgendwo auf meinem Notebook.
Beim Oeffnen meines Depot workbook kommt ein Hinweis,
This workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to getthe latest data. Otherwhise you can keep working with the datat you have.
wenn ich auf update klicke kommt ein weiterer Dialog :
We can’t update some of the links in your workbookright now. You can continue ….
Mit Edit links erscheint ein Dialog mit dem fehlendem source zu Professor_Excel_Currency_converter/Error source not found.
Wie gesagt , nach erneuter Installation kann ich diese Datei nirgends finden , obwohl das Installationsprogramm sagte alles paletti.
Was wurde installiert , bzw wo ist diese Datei nun? .XLMA und Professor.* ergaben keine Treffer auf meiner kompletten Harddisk.
Und Nun?
Vielen Dank im voraus und guten Rutsch ins neue Jahr
Michael
Dear Heinrick,
Thank you for an excellent tool. Unfortunately the update function seems to have stopped working (I have been using it successfully for over a year). When I click update, I now get an error message: Update not successful. Please try later. {Error code 130)
Any help would be much appreciated.
Best,
Mark
Hi Mark,
I’ve just tried it, it seems to be working for me. You could try to uninstall and re-install it?
Best regards,
Henrik
Servus Henrik,
bei mir wird die Formel erkannt. Die Ausgaben für die vergangenen Daten sind allerdings immer gleich. Funktioniert das Add In allgemein noch? Ich würde mich extrem freuen, wenn du mir helfen könntest.
Beste Grüße
Tim
Hallo Tim,
welche Formel wird nicht erkannt? Ggfs. müssen die Daten aktualisiert werden (per “Update Data”).
Viele Grüße
Henrik
works well on WIN7 64BIT, MS OFFICE 2010 32bit, just a little slow.
Hi Joddy,
Thanks for replying!
Best regards,
Henrik
What is the formula for the date? is it yyyy-day-month?
Hi asdfasdf,
Just a “normal” Excel data (so with an underlying number), format doesn’t matter.
Best regards,
Henrik
Funktioniert bestens, DANKE!
Hello Henrik, thank you very much for letting us use this tool. Unfortunately it does not seem to run properly on my Excel as it always returns the rate only for today… it doesn’t matter what is the date in the formula. Do you have any suggestion?
Hi Ciro,
Have you tried pressing the update button? And just making sure here: You have entered dates in the past, right?
Best regards,
Henrik
The historical forex data is quite inaccurate. For example it gives for usd/cad on dec 20 as 1.27206, but the rate was like 1.2942 on bank of canada website and almost everywhere else. This is inaccurate by a huge factor! How to fix?