Currency Exchange Rates

[#1]

This post has nothing to do with Linux; it is about using JMP to download currency conversion data.

The currency buttons on the Linux calculator caught my attention recently.

Calculator showing the currency conversion buttons

How do they get up-to-date conversion factors? Google took me here, which took me here, which took me to this European Central Bank page containing the rates and a link to PDF, CSV, and XML downloads. Click the XML link and your browser will probably open something like this:

screenshot of FireFox showing an XML file of exchange rates

A quick look suggests these rates are all based on the Euro, because the Euro does not appear and there is no 1.0000 factor in the table. That's also what the referring page said:

based on the euro

Let's load the XML into a data table. JMP 15's XML Wizard can help build the JSL to do this. First, save the XML from the browser to your machine, then use File->Open, pick the XML extension, select your file, and open it in the wizard.

Choose the XML extension to see the downloaded file

The XML Wizard opens and it is time to decide what columns and rows are interesting. Open the stat columns to see what keys the repetitive data is under and close the name columns to make the display fit the screen better:

opening the XML file with the wizard

You could also use the Tall Guess to get the same thing. The Tall Guess notices the 32 repeating entries and selects that key to make rows, then selects all (both) the sub-keys to make columns. It uses the Sampler data to determine if the sub-keys should make numeric or character data.

Now rename the columns by switching off the Stat Cols and turn the Name Cols back on. Touch up a few other things as well, make the Modeling type for Currency be Nominal. Time, Name, Subject are described after the picture...

Changing column names, adding some meta data

There are three other columns of meta-data: time, name, subject. Time seems most useful; let's keep the time on every row. The name and subject should only be kept as table variables; they will show up after OK:

The source script and the two table variables are part of the imported table

Click the table variables to be reminded where the table came from.

To reproduce this import next week, with the current data from next week, grab the source script and edit in the URL:

Open(
	//"E:\eurofxref-daily.xml",
	"https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml",
	XML Settings( Stack( 0 ),
		Row( "/gesmes:Envelope/Cube/Cube/Cube" ),
		Col("/gesmes:Envelope/gesmes:subject",
			Column Name( "gesmes:subject" ), Fill( "Use Forever" ),
			Type( "Table Variable" ), Format( {"Best"} ), Modeling Type( "Continuous" ) ),
		Col("/gesmes:Envelope/gesmes:Sender/gesmes:name",
			Column Name( "gesmes:name" ), Fill( "Use Forever" ),
			Type( "Table Variable" ), Format( {"Best"} ), Modeling Type( "Continuous" ) ),
		Col("/gesmes:Envelope/Cube/Cube/@time",
			Column Name( "time" ), Fill( "Use Forever" ),
			Type( "Numeric" ), Format( {"yyyy-mm-dd", 10} ), Modeling Type( "Continuous" ) ),
		Col("/gesmes:Envelope/Cube/Cube/Cube/@currency",
			Column Name( "currency" ), Fill( "Use Once" ),
			Type( "Character" ), Format( {"Best"} ), Modeling Type( "Nominal" ) ),
		Col("/gesmes:Envelope/Cube/Cube/Cube/@rate",
			Column Name( "rate" ), Fill( "Use Once" ),
			Type( "Numeric" ), Format( {"Best"} ), Modeling Type( "Continuous" ) )
	),
	XML Wizard( 0 )
);