Excel: easily get the latest currency exchange rates

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Here’s a ‘no fuss, no muss’ way to grab the latest exchange rates and use them in Excel.  No need to lookup a rate and type it in, let Excel do all that work for you.

You might expect Excel to do this out of the box.  After all, Microsoft has been banging on about Internet integration for years and their main rival, Google, has an exchange rate function in their spreadsheets (see GoogleFinance() ).

2330 Real time Excel small 212x300 - Excel: easily get the latest currency exchange ratesReal Time Excel

Real-Time Excel – get live stock, fund and bond prices, currency rates and more includes working spreadsheets for this tip and many other examples of getting live information into Excel. Buy and get it today (just a few minutes from now)

 

Tip: If you have Excel 365 there’s a much easier way see Exchange Rate support in Excel 365

There are many places on the web which supply exchange rate information in a computer readable format.  Excel can grab that data and put it into cells for the worksheet.   For this article, we set some limitations:

  • No VBA code or addons. This removes security concerns or hassles.  Our example uses a simple .xlsx worksheet.
  • Simple system that’s easy to explore and understand.
  • Free access to the exchange rate information
  • Xrates in XML format (which Excel understands).

Microsoft once provided a connection to their MSN data source but that’s been dropped.

For our examples, we’ll use data from FloatRates.com because their XML data format is simple and uncomplicated compared to others.  The web link is simple, needs no changing parameters and no registration.

For the latest US Dollar exchange rates go to http://www.floatrates.com/daily/usd.xml this is an XML data feed which looks like a web page via a XML style sheet.  Choose ‘View Source’ in your browser to see the underlying data which is what Excel will copy into a worksheet.

excel easily get the latest currency exchange rates 10680 - Excel: easily get the latest currency exchange rates

FloatRates.com web page (left) and source page as XML (right)

FloatRates.com also has exchange rates with other currencies as a base.

Canada: http://www.floatrates.com/daily/cad.xml

UK: http://www.floatrates.com/daily/gbp.xml

Euro: http://www.floatrates.com/daily/eur.xml

Australia: http://www.floatrates.com/daily/aud.xml

New Zealand: http://www.floatrates.com/daily/nzd.xml

For the full list go to http://www.floatrates.com/feeds.html

Data Connection

The standard recommendation for web data importing is Data | Get External Data | From Web which works for web pages with tables (the <TABLE> tag) but that doesn’t work if the XML data is changed with a style sheet.

So, we dig a little deeper to Get External Data | From Other Sources | From XML data import.  This tells Excel to ignore the style sheet formatting and use the XML data only.

excel easily get the latest currency exchange rates 10681 - Excel: easily get the latest currency exchange rates

Paste the web link (complete with http:// or preferably https:// prefix) in the Select Data Source dialog. See above for a list of options.

excel easily get the latest currency exchange rates 10682 - Excel: easily get the latest currency exchange rates

Excel will warn if there’s no XML schema available, that’s OK, let Excel create one for you.  An XML schema is the structure of the XML data feed which, officially, should always be supplied.  In practice, many XML feeds don’t have a schema because it’s obvious from the feed itself.

excel easily get the latest currency exchange rates 10683 - Excel: easily get the latest currency exchange rates

Next, tell Excel where to put the data, most likely a New worksheet.

excel easily get the latest currency exchange rates 10684 - Excel: easily get the latest currency exchange rates

Before clicking OK, check out the Properties.

excel easily get the latest currency exchange rates 10685 - Excel: easily get the latest currency exchange rates

Name: make sure the Query name is self-explanatory

Refresh Control:  Make the refresh rate suitable to the data feed. In this case the data is only updated every 12 hours, so there’s no point in refreshing every hour (the default).

Now there’s a worksheet with 92 current conversions from US Dollar to international currencies in your worksheet.

excel easily get the latest currency exchange rates 10686 - Excel: easily get the latest currency exchange rates

Use Vlookup() to grab the exchange rate you want for your calculation.

excel easily get the latest currency exchange rates 10687 - Excel: easily get the latest currency exchange rates

Note: Vlookup requires the source data to be sorted on the column you’ll be using for the lookup.  Do that from the Data | Sort tab.

excel easily get the latest currency exchange rates 10688 - Excel: easily get the latest currency exchange rates

You have to re-sort the list after each time the data is refreshed.  Real-Time Excel – get live stock prices, currency rates and more shows how to get around that Excel limitation.

Check your calculation!

It’s too easy to get an exchange rate ‘turned around’.   Even experts can get the Buy and Sell rates for a currency confused.  Here’s a snippet of the ‘USD dollar’ rate feed.

excel easily get the latest currency exchange rates 10691 - Excel: easily get the latest currency exchange rates

These rates apply if you have US dollars and what you’ll get if you buy the other currency.  For example, if you have USD$1 you’ll get  0.89 Euro,  77 UK pence or $1.311 Aussie dollars.

The common mistake is the multiply the rate given when you want to buy (convert into) the base currency.  That’s wrong and will give you very misleading answers.

Reverse xrate: if you want to convert from other currencies to the base currency (US dollars in this case) divide the rate into 1. ( = 1/rate ).  Do that by making a ‘parallel’ column that converts the rate.

excel easily get the latest currency exchange rates 10692 - Excel: easily get the latest currency exchange rates

Now you can convert from and to the base currency. Just make sure you choose the correct column/rate!

Double check: check your worksheet logic by doing tests against one of the many currency conversion web sites.

 

That’s just the basics of online data import into Excel for currency conversions.   Real-Time Excel – get live stock prices, currency rates and more has useful extra info you should get with the currency data.  Also, other online data that Excel can grab.

subs profile e1563205311409 - Excel: easily get the latest currency exchange rates
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address