VB .NET Retrieving FX Rates from ECB site and parsing XML into a datatable

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>

Open 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

Open 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

Open 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 file
The 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:

Using xReader As New XmlTextReader(New MemoryStream(e.Result))

Open in new window

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)

Open 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")

Open in new window

b) As soon as we enter this statement, we move to the currency attribute to get the ISO 4217 currency and funds name and code elements and get its value, then move to the rate attribute to get the FX rate, finally adding these to the xDic, i.e

xReader.MoveToAttribute("currency")
                      Dim GCur = xReader.Value
                      xReader.MoveToAttribute("rate")
                      Dim GRate = xReader.Value
                      xDic.Add(GCur, GRate)

Open 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

If xDic.Keys.Count > 0 Then FXDic.Item(xDate) = xDic

Open in new window

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))

Open 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

Open 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

Open 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

Open 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

VB .NET Retrieving FX Rates from ECB site and parsing XML into a datatable

Comments (0)