Do you have the need to get daily updated exchange rates in Excel and you’re like me and don’t want to type the exchange rates manually every day? Then this is the solution for you.
Create a new Excel file and select Data and From Web:
Next type in the URL to the Xavier Finance API for Exchange Rates. This API is updated daily for free with exchange rates. The URL you should use is http://api.finance.xaviermedia.com/api/EUR/latest.xml where EUR can be replaced with any of the following base currencies EUR, USD, JPY, GBP, CZK, DKK, HUF, LTL, PLN, SEK, CHF, NOK, BGN, HRK, RON, RUB, AUD, CAD, CNY, HKD, IDR, KRW, MYR, NZD, PHP, SGD, THB or ZAR.
Click on Go to load the page and when you see the XML page in the window you click on Import to import the XML file into Excel.
On next screen you get to choose where to save the data. I use XML table in existing worksheet when I use this feature, but it’s up to you 😉
Once you’ve pressed OK you get a table in Excel with exchange rates. To make the table refresh every time you open the file you just select Connection Properties in the Design tab under Refresh.
Check the “Refresh data when opening the file” checkbox to have the table collect new exchange rates whenever you open the excel file. Since the exchange rates are only updated daily in the API there’s no need to check “Refresh every X minutes” because you will only get the same rates returned every time.
Now when you have your table with exchange rates you can use it for various calculations. I’ve an example for you to play around with if you’re interested:
Download Example Here
In this example I also added in an exchange rate calculation where you can select currency and it will convert between euro and the selected currency. The table in the excel file will connect to the Xavier Finance API upon opening of the file to get new an refreshed exchange rates. You can see in the date column which date is the latest. The exchange rates in the API are only updated Monday to Friday so on Saturday and Sunday you get the exchange rate from Friday.