<

Go Premium for a chance to win a PS4. Enter to Win

x

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

Published on
11,560 Points
5,360 Views
2 Endorsements
Last Modified:
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
2
Comment
Author:nepaluz
0 Comments

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Join & Write a Comment

Suggested Articles

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month