Pull data from website: Economic announcements

[JackDanIce]

Hi,

First time I'm trying to pull data from a website into Excel and struggling after watching YouTube videos, so apologies for anything previously asked.

Every Sunday. I would like to download economic calendar alerts that have an Impact value of High from this site: Economic Calendar


In Excel, I've tried to download this data using: Data->From Web and URL: https://www.myfxbook.com/forex-economic-calendar

From here, Navigator opens and I can tell I need Table1 but when this opens in Power Query, the layout is a a mess!

It's not loaded in a normalised table and I'm afraid I'm unsure how to process the abnormality


Ideally, I would like to create a connection to a spreadsheet that returns just the Date, Event and Impact columns, eventually I'll drop Impact as it should only return those with a High rating.

Can anyone help with the steps or process please?

Thank you in advance,
Jack

 

[diddi]

Sub ScrapeNow2()
    Dim xmlhttp As XMLHTTP60
    Dim oDom  As HTMLDocument
    Dim objXML As MSXML2.DOMDocument60
    Dim ele As IHTMLElement

    Set xmlhttp = New MSXML2.ServerXMLHTTP60
    Set objXML = New MSXML2.DOMDocument60
    Set oDom = New HTMLDocument
    
    MyURL = "https://www.myfxbook.com/forex-economic-calendar"

    With xmlhttp
        .Open "GET", MyURL, False
        .send
        oDom.body.innerHTML = .responseText
    End With
    
    Row = 1
    With oDom.getElementsByTagName("table")(1)
        For Each orow In .Rows
            Row = Row + 1
            col = 0
            For Each oCell In orow.Cells
                col = col + 1
                Cells(Row, col) = oCell.innerText
            Next oCell
        Next orow
    End With
End Sub

 

[JackDanIce]

Hey @diddi that's awesome and my fault, I didn't state, but is there a non-VBA solution? I image it is possible in Power Query...

 

[diddi]

not my area... quite possibly there is. i would heartily recommend you try this code tho. it dumps all the data onto a sheet in the time it takes for the internet to reply. you can then change to remove info you dont need

 

[JackDanIce]

Thank you, I will try it and I'm not against the benefits you list, it would be faster etc. However, I'm building this for someone else and preference to avoid VBA!

It might be I need to move this question to the Power BI area...