Download exchange rate using web scrapping

[gubertu]

Hi all,

I´m trying to use web scrapping to download the exchange rates from below web.

What I would like to do is to download to Excel the content of the file "CSV (.zip)" that is on "Time series" header. Is it that possible?

Thanks in advance for your help

Euro foreign exchange reference rates

The euro foreign exchange reference rates (also known as the ECB reference rates) are published by the ECB at around 16:00 CET. Reference rates for all the official currencies of non-euro area Member States of the European Union and world currencies with the most liquid active spot FX markets...

www.ecb.europa.eu

 

[ExcelToDAX]

You could use Power Query to extract the .zip file from the web page URL. Here is a link to help you.

Create a function called unzipCSV:

(ZIPFile) =>
let
    Header = BinaryFormat.Record([
        MiscHeader = BinaryFormat.Binary(14),
        BinarySize = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileSize   = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        FileNameLen= BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian),
        ExtrasLen  = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16, ByteOrder.LittleEndian)
    ]),
 
    HeaderChoice = BinaryFormat.Choice(
        BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32, ByteOrder.LittleEndian),
        each if _ <> 67324752             // not the IsValid number? then return a dummy formatter
            then BinaryFormat.Record([IsValid = false, Filename=null, Content=null])
            else BinaryFormat.Choice(
                    BinaryFormat.Binary(26),      // Header payload - 14+4+4+2+2
                    each BinaryFormat.Record([
                        IsValid  = true,
                        Filename = BinaryFormat.Text(Header(_)[FileNameLen]),
                        Extras   = BinaryFormat.Text(Header(_)[ExtrasLen]),
                        Content  = BinaryFormat.Transform(
                            BinaryFormat.Binary(Header(_)[BinarySize]),
                            (x) => try Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) otherwise null
                        )
                        ]),
                        type binary                   // enable streaming
                )
    ),
 
    ZipFormat = BinaryFormat.List(HeaderChoice, each _[IsValid] = true),
 
    Entries = List.Transform(
        List.RemoveLastN( ZipFormat(ZIPFile), 1),
        (e) => [FileName = e[Filename], Content = e[Content] ]
    )
in
    Table.FromRecords(Entries)

And then Invoke the custom function using the web url that you can copy from the zip URL in the website.


Here is what your output would look like:

let
    Source = unzipCSV(Web.Contents("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip")),
    Content = Source{0}[Content],
    #"Imported CSV" = Csv.Document(Content,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Type"}}),
    #"Cleaned Text" = Table.TransformColumns(#"Renamed Columns",{{"Type", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Type", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Value", type number}, {"Date", type date}, {"Type", type text}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Value", null}}),
    #"Sorted Rows" = Table.Sort(#"Replaced Errors",{{"Date", Order.Ascending}, {"Type", Order.Ascending}})
in
    #"Sorted Rows"

Make sure you authenticate using Anonymous.


Here is what the output would look like:

 

[Domenic]

Here's a VBA alternative...

Option Explicit

Sub test()

    Dim url As String
    Dim saveAsFilename As String
    Dim errorMessage As String
    
    url = "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip?b111d87bb5f7a3bedd816c217906dc14"
    
    saveAsFilename = "c:\users\john\desktop\eurofxref-hist.zip" 'change the path as desired

    errorMessage = ""
    If Not DownloadFile(url, saveAsFilename, errorMessage) Then
        MsgBox errorMessage, vbCritical, "Error"
        Exit Sub
    End If
    
    MsgBox "Download complete!", vbExclamation
    
End Sub

Public Function DownloadFile(ByVal url As String, ByVal saveAsFilename As String, ByRef errorMessage As String) As Boolean

    On Error GoTo errorHandler
    
    Dim xmlHttpRequest As Object
    Set xmlHttpRequest = CreateObject("MSXML2.XMLHTTP")
    
    With xmlHttpRequest
        .Open "GET", url, False
        .send
        Do While .readystate <> 4
            DoEvents
        Loop
        If .Status <> 200 Then
            errorMessage = "Error " & .Status & ": " & .statustext
            Set xmlHttpRequest = Nothing
            DownloadFile = False
            Exit Function
        End If
        Dim response() As Byte
        response() = .responsebody
    End With
        
    Dim fileNumber As Long
    fileNumber = FreeFile()
    
    Open saveAsFilename For Binary As #fileNumber
        Put #fileNumber, , response
    Close #fileNumber
    
    DownloadFile = True
    
exitHandler:
    Set xmlHttpRequest = Nothing
    Exit Function
    
errorHandler:
    errorMessage = "Error " & Err.Number & ":" & vbCrLf & vbCrLf & Err.Description
    DownloadFile = False
    Resume exitHandler
    
End Function

Hope this helps!

 

[gubertu]

Thanks very much. Both options are valid for what I wanted.

Thanks!

 

[HeinrichPaul]

Thanks very much. Both options are valid for what I wanted.

Thanks!

Hello,
Did you figure out how to change the ending of it that you could always use the macro? I mean after the ?. I tried in a cell with &"*", but that downloaded it when clicking on the cell, but not when trying to download it via macro.

 

[HeinrichPaul]

Hello,
Did you figure out how to change the ending of it that you could always use the macro? I mean after the ?. I tried in a cell with &"*", but that downloaded it when clicking on the cell, but not when trying to download it via macro.

Solved Thank you.