Building an Excel currency converter

My wife and I will be leaving the country later this year to see some friends tie the knot, which is the sort of thing which gets you thinking about exchange rates (the traveling, not the wedding – weddings very rarely make me think about exchange rates). There are of course a number of websites to go to for the latest exchange rates, which usually include a built-in currency converter. These are in essence quite simple applications – all that’s required to make them work is a lookup table to hold exchange rates, and a form to select the currency type and to enter the amount into. Multiply the amount by the selected rate, badabing, badaboom.

So I went to one of these sites, and noticed a bunch of rss feeds, including one of rates for New Zealand. As these are just xml documents, they can quite easily be imported into Excel.

So here’s how to get xml data into Excel. Actually there’s a couple of ways to skin this cat in Excel 2007. You could try the Data tab -> Get External Data -> From Other Sources -> From XML Data Import. Or you can go through the Developer tab -> XML -> Import. Either way you get the same dialog pop up:

Import XML Dialog

Enter the link to the xml file into the “File name:” box and hit “Open”. Excel will think for a few seconds and then in many cases pop up a message:

Infer XML Schema

Click OK and Excel will create a schema based on the file and import it. By default, it will be mapped to a table:

Initial view of xml data

But the map is unsatisfactory for a number of reasons. Taking a look at the XML Source pane (Developer tab -> XML -> Source), we can see the structure of the xml schema that Excel built for us. All of the elements in the schema have been mapped (indicated by the fact they’re in boldface), to a column in the table.

XML Source Pane

There is a mixture of repeating and non-repeating elements in here. Repeating elements (that is, elements of which there are more than one in each instance of the schema) are fine to put in a table in general – we can map each instance of an element to a row in the table. We can see that the simple element ‘version’, the complex element ‘channel’ and most of its members are non-repeating, while the complex element ‘item’ is repeating. Looking down the rows in the table, each of the columns corresponding to a member of ‘item’ is distinct, while the other columns are identical all the way down. We can also see a handy wee white-on-blue triangle in the folder icon representing the ‘item’ element, indicating that it is repeating. These can go into a table, whereas non-repeating elements occur once only and can be mapped to a single range.

So we need to change the xml map, and the first step is to clear what’s already been mapped. In the XML Source pane, you can right-click on individual elements and select ‘Remove element’ from the context menu. However that gets pretty tedious. Quick way to remove all elements at once: convert the table to a range. Click inside the table, then go to Table Tools -> Design -> Tools -> Convert to Range. Excel checks that you really want to convert the table to a normal range, you say ‘Yes’, and then all the elements in the map go from bold to regular in the Source pane.

I then start from scratch: add a new sheet, delete the old one, and map elements from the XML Source pane to the new sheet. You can do this via the context menu from a right-click on individual elements, selecting ‘Map element…’, and then specifying whereabouts to place the element on the sheet. Or you can drag’n’drop the element straight onto the sheet. Matter of taste as far as I know, both ways get the job done. Once the location of an individual element is specified, a popup menu appears beside the cell which allows you to place the heading above or to the left of the cell mapped to, and Excel will supply it from the xml schema. Or you can add your own heading wherever you like (you do not have to use the names from the xml source). I selected all the non-repeating elements from ‘channel’ except for the complex element ‘image’:

After placing some of the non-repeating elements at the top of the worksheet, I can place the ‘title’, ‘link’, ‘description’ and ‘category’ members from the repeating element ‘item’. Placing them a couple of rows below the non-repeating elements, Excel knows that these elements are repeating and automatically formats them as columns in a table. Refresh the data (Data -> Connections -> Refresh All) and the data is pulled from the feed into the table.

Completed XML Map

And that’s it for now – the xml source file has been imported, so we have a lookup table to hold exchange rates and a means to refresh it. Next time I’ll finish the converter by putting in a mechanism to select, enter and calculate using the data in the table.