The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity)
<gesmes:Envelope>
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2011-09-09">
<Cube currency="USD" rate="1.3817"/>
<Cube currency="JPY" rate="107.48"/>
<Cube currency="BGN" rate="1.9558"/>
<Cube currency="CZK" rate="24.430"/>
<Cube currency="DKK" rate="7.4473"/>
<Cube currency="GBP" rate="0.86590"/>
</Cube>
</Cube>
</gesmes:Envelope>
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
Select allOpen in new window
There are three files available
HERE (and updated once daily) that contain:
1. The days FX rates
2. The last 90 days' FX rates
3. All daily FX rates since 1999
The XML file provided is by no means a regular XML file, and the code provided in the developer section is PHP. After several days' research and search for simple code to parse the XML file from the ECB, it became clear that there needs to be a simpler way to parse these files.
I set out to resolve this by utilising the
WebClient Class to download the files and parse them using the
XMLTextReader Class.
Firstly, I use the webclient's
DownloadDataAsync method and add handlers for the progress changed (for monitoring rogress) and another handler for when the method completes. This is inside a Boolean function which requires an integer value (0,1 or 2) to select the file to download (daily, 90 day and since 1999 respectively). Heres the code:
Function GetECB_FX(ByVal XMLFile As Integer) As Boolean
If My.Computer.Network.IsAvailable Then
Dim xURI As String = Nothing
Select Case XMLFile
Case 0
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"
Case 1
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml"
Case 2
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml"
End Select
Try
Using qClient As New Net.WebClient
qClient.Headers.Add("Accept-Encoding", "gzip")
qClient.DownloadDataAsync(New Uri(xURI))
AddHandler qClient.DownloadProgressChanged, AddressOf FXDownloadProgressCallback
AddHandler qClient.DownloadDataCompleted, AddressOf FXDownloadComplete1
End Using
Catch ex As Exception
Label3.Text = "Error downloading FX Rates. " & Err.Number & ": " & Err.Description
Return False
End Try
Else
Label3.Text = "Please connect to the internet to download FX Rates."
Return False
End If
Return True
End Function
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
Select allOpen in new window
Next are the handlers. For the progress changed, I have a label placed strategically on my form (Label3) to which I update myself of the progress of the download. Since I am using the DownloadDataAsync method, it is non blocking and therefore will not freeze the UI while the download is taking place. Heres the code for the prgress handler.
Sub FXDownloadProgressCallback(ByVal sender As Object, ByVal e As Net.DownloadProgressChangedEventArgs)
xTimer.Interval = 10000
Dim xRec, xTot As Double
xRec = FormatNumber(e.BytesReceived / 1048576, 2)
xTot = FormatNumber(e.TotalBytesToReceive / 1048576, 2)
Label3.Text = "FX Rates download: " & e.ProgressPercentage & "% Completed. (" & xRec & "MB of " & xTot & "MB)"
End Sub
1:
2:
3:
4:
5:
6:
7:
Select allOpen in new window
The next handler which is executed when the download of the file is complete, is the most important one. It carries out two tasks.
1. It parses the downloaded XML file.
2. It sets up the datatable to hold the data and populates it.
1. Parsing the XML fileThe
DownloadDataAsync method downloads the specified resource as a Byte array and does not block the calling thread. The resource is then returned as the result in the
DownloadDataCompletedEventArgs. Since it is returned as Byte Array, we need to convert this to a stream so as for it to be read by the XmlTextReader. We therefore read it using a new
MemoryStream, thus:
I also utilise a couple of
dictionaries in parisng the XML. They are the
xDic which holds the individual rates as the file is parsed. The key is the currency and the value is the rate to the EURO, and the
FXDic whose key is the FX date and value is the
xDic above.
Due to the way the file is laid out, we use the XmlTextReader to read a node at a time using its
Read method. The ONLY nodes of interest are name
Cube and for our values, these node SHOULD have one or two attributes. We use
Select to determine this and:
a) If ther is one node - This MUST contain the date. Parse the value of the date string and add it to our FXDic as a key, i.e
xDate = Date.ParseExact(xReader.Value, "yyyy-MM-dd", CultureInfo.InvariantCulture)
FXDic.Add(xDate, Nothing)
1:
2:
Select allOpen in new window
Since we are iterating through the nodes, we also need to check whether values for the FX rates have been populated (see b below), and also to reset the Dictionary for those values, and then move to the time attribute that holds the date i.e
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
xDic = New SortedDictionary(Of String, Double)
xReader.MoveToAttribute("time")
1:
2:
3:
Select allOpen in new window
xReader.MoveToAttribute("currency")
Dim GCur = xReader.Value
xReader.MoveToAttribute("rate")
Dim GRate = xReader.Value
xDic.Add(GCur, GRate)
1:
2:
3:
4:
5:
Select allOpen in new window
When reading the file is complete (at the end of the While xReader.Read()) we need to add the last xDic to the FXDic because after reading the last two attribute Code node, we never encounter another one attribute code node, thus
2. Setting up the DataTable and populating it.
First, I initialize a new list of string and add ALL the Keys in each xDic. This may seem futile but ensures that should the ECB include another currency in the future, this code still functions. Heres the code for that:
Dim HDList As New List(Of String)
Array.ForEach(FXDic.Keys.ToArray, Sub(x) If Not FXDic.Item(x) Is Nothing Then HDList.AddRange(FXDic.Item(x).Keys.ToArray))
1:
2:
Select allOpen in new window
Next I use the list above to create and name DataColumns to add to the table, but first, Ihave toadd the column to hold the date and make it unique. Heres the code for that:
Dim FXTable As DataTable = New DataTable
With FXTable
.TableName = "ECB_FXTabe"
.Columns.Add(New DataColumn("Date") With {.DataType = GetType(System.DateTime), .ColumnName = "Date", .Unique = True})
Array.ForEach(HDList.Distinct.OrderBy(Function(x) x).ToArray, Sub(x) .Columns.Add(New DataColumn(x) With {.DataType = GetType(System.Double), .DefaultValue = 1.0, .ColumnName = x}))
End With
1:
2:
3:
4:
5:
6:
Select allOpen in new window
NOTE:I use distinct items (and sort them) from the list to create the table columns, i.e HDList.Distinct.OrderBy(Function(x) x).
Finally, it is a matter of populating the the datatable with our values, by first declaring a new datarow and then populating it with the values.
Dim xRow As DataRow
For Each q In FXDic.Keys 'q is the value for date column
xRow = FXTable.NewRow()
xRow.Item("Date") = q
Array.ForEach(FXDic.Item(q).Keys.ToArray, Sub(x) xRow.Item(x) = CDbl(FXDic.Item(q).Item(x)))
FXTable.Rows.Add(xRow)
Next
1:
2:
3:
4:
5:
6:
7:
Select allOpen in new window
And here is the complete code listing.
Function GetFX(Optional ByVal xURI As String = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml") As Boolean
If My.Computer.Network.IsAvailable Then
Select Case My.Settings.FXLink
Case 0
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"
Case 1
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml"
Case 2
xURI = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml"
End Select
Try
Using qClient As New Net.WebClient
qClient.Headers.Add("Accept-Encoding", "gzip")
qClient.DownloadDataAsync(New Uri(xURI))
AddHandler qClient.DownloadProgressChanged, AddressOf FXDownloadProgressCallback
AddHandler qClient.DownloadDataCompleted, AddressOf FXDownloadComplete1
End Using
Catch ex As Exception
Label3.Text = "Error downloading FX Rates. " & Err.Number & ": " & Err.Description
Return False
End Try
Else
Label3.Text = "Please connect to the internet to download FX Rates."
Return False
End If
Return True
End Function
Sub FXDownloadProgressCallback(ByVal sender As Object, ByVal e As Net.DownloadProgressChangedEventArgs)
xTimer.Interval = 10000
Dim xRec, xTot As Double
xRec = FormatNumber(e.BytesReceived / 1048576, 2)
xTot = FormatNumber(e.TotalBytesToReceive / 1048576, 2)
Label3.Text = "FX Rates download: " & e.ProgressPercentage & "% Completed. (" & xRec & "MB of " & xTot & "MB)"
End Sub
Sub FXDownloadComplete1(ByVal sender As Object, ByVal e As Net.DownloadDataCompletedEventArgs)
Dim FXDic As New Dictionary(Of Date, SortedDictionary(Of String, Double))
Dim xDic As New SortedDictionary(Of String, Double)
Dim xDate As Date = Nothing
Using xReader As New XmlTextReader(New MemoryStream(e.Result))
While xReader.Read()
If String.IsNullOrEmpty(xReader.Name) Then Continue While
If Not xReader.Name = "Cube" Then Continue While
Select Case xReader.AttributeCount
Case 1
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
xDic = New SortedDictionary(Of String, Double)
xReader.MoveToAttribute("time")
xDate = Date.ParseExact(xReader.Value, "yyyy-MM-dd", CultureInfo.InvariantCulture)
FXDic.Add(xDate, Nothing)
Case 2
xReader.MoveToAttribute("currency")
Dim GCur = xReader.Value
xReader.MoveToAttribute("rate")
Dim GRate = xReader.Value
xDic.Add(GCur, GRate)
End Select
xReader.MoveToNextAttribute()
End While
If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic
End Using
Dim HDList As New List(Of String)
Array.ForEach(FXDic.Keys.ToArray, Sub(x) If Not FXDic.Item(x) Is Nothing Then HDList.AddRange(FXDic.Item(x).Keys.ToArray))
Dim FXTable As New DataTable
With FXTable
.TableName = "ECB_FXTabe"
.Columns.Add(New DataColumn("Date") With {.DataType = GetType(System.DateTime), .ColumnName = "Date", .Unique = True})
Array.ForEach(HDList.Distinct.OrderBy(Function(x) x).ToArray, Sub(x) .Columns.Add(New DataColumn(x) With {.DataType = GetType(System.Double), .DefaultValue = 1.0, .ColumnName = x}))
End With
Dim xRow As DataRow
For Each q In FXDic.Keys 'q is the value for date column
xRow = FXTable.NewRow()
xRow.Item("Date") = q
Array.ForEach(FXDic.Item(q).Keys.ToArray, Sub(x) xRow.Item(x) = CDbl(FXDic.Item(q).Item(x)))
FXTable.Rows.Add(xRow)
Next
End Sub
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
Select allOpen in new window
Please note, depending on what you want to do with the ECB data, there are some copyright considerations. Using it as above presents no problems for your own use. However, if you intend to publish or distribute then you do need to be familiar and compliant with the ECB terms :
http://www.ecb.europa.eu/home/html/disclaimer.en.html
