Download exchange rate using web scrapping

gubertu

Board Regular
Joined
May 24, 2015
Messages
148
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

 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:
Power Query:
(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:

Power Query:
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:

1709068823546.png
 
Last edited:
Upvote 0
Solution
Here's a VBA alternative...

VBA Code:
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Hello,

I trust that you are well.

as you helped me with exchange rates of BCE, I would like to know if could it be possible to download them from Banco Central de Uruguay:

The steps I follow are the following:

1) Entering in the following web page: https://www.bcu.gub.uy/Estadisticas-e-Indicadores/Paginas/Cotizaciones.aspx
2) Select currency "EUR" + "ARBITRAJES INTERNACIONALES" + Date = Desde 01/05/2024 Hasta 31/05/2024"

1717254933148.png


3) Press button "Excel" to download the data.

Would it be possible to automate this?

Thanks a lot for your help!
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top