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.


TableCell – Beta Testers needed

Last year I posted a review of the QueryCell Excel add-in from Oak Focus Software. A few weeks ago, the developer of QueryCell let me know about a new add-in he’s developed called TableCell, a simplified version of QueryCell. Actually it was several weeks ago, sorry Sam – I’ve been a lot slower getting to this than I hoped.

Anyway, TableCell requires beta testers to do beta testing, so if you would like to help out (and get yourself some free trial software) head on over to TableCell and take a look. Basically the idea is to allow users to read from and write to database tables via ODBC. I’ve had a play and it will allow you to do just that, but not a lot more – it’s not a developer tool, it’s more aimed at the user who needs to do simple monitoring and updating without in-depth knowledge of either SQL or the data model in the DB. I see the same strength in the UI that I see in QueryCell, i.e. it’s polished, intuitive and requires minimal effort to get used to. Connecting to a DB is very easy – the one thing I would have liked to see and didn’t was a list of available tables. I know that introspection adds on a bit of overhead but in my view given the audience I see this add-in aimed at it’s probably worth it. It also requires the user to enter the name of the table’s primary key to extract data.

That’s all for now – upcoming posts over the next month include a review of some open data sources, a new Python/Excel hookup, and a brief how-to on XML data sources in Excel, which I’ll be using to build a simple currency converter.