Pull data from website: Economic announcements

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,305
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,305
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,870
Members
417,174
Latest member
diegomuser

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
Top