Scraping data from website using vba

There are several ways of doing this. This is an answer that I write hoping that all the basics of Internet Explorer automation will be found when browsing for the keywords "scraping data from website", but remember that nothing's worth as your own research (if you don't want to stick to pre-written codes that you're not able to customize).

Please note that this is one way, that I don't prefer in terms of performance (since it depends on the browser speed) but that is good to understand the rationale behind Internet automation.

1) If I need to browse the web, I need a browser! So I create an Internet Explorer browser:

Dim appIE As ObjectSet appIE = CreateObject("internetexplorer.application")

2) I ask the browser to browse the target webpage. Through the use of the property ".Visible", I decide if I want to see the browser doing its job or not. When building the code is nice to have Visible = True, but when the code is working for scraping data is nice not to see it everytime so Visible = False.

With appIE    .Navigate "http://uk.investing.com/rates-bonds/financial-futures"    .Visible = TrueEnd With

3) The webpage will need some time to load. So, I will wait meanwhile it's busy...

Do While appIE.Busy    DoEventsLoop

4) Well, now the page is loaded. Let's say that I want to scrape the change of the US30Y T-Bond:What I will do is just clicking F12 on Internet Explorer to see the webpage's code, and hence using the pointer (in red circle) I will click on the element that I want to scrape to see how can I reach my purpose.

5) What I should do is straight-forward. First of all, I will get by the ID property the tr element which is containing the value:

Set allRowOfData = appIE.document.getElementById("pair_8907")

Here I will get a collection of td elements (specifically, tr is a row of data, and the td are its cells. We are looking for the 8th, so I will write:

Dim myValue As String: myValue = allRowOfData.Cells(7).innerHTML

Why did I write 7 instead of 8? Because the collections of cells starts from 0, so the index of the 8th element is 7 (8-1). Shortly analysing this line of code:

• .Cells() makes me access the td elements;
• innerHTML is the property of the cell containing the value we look for.

Once we have our value, which is now stored into the myValue variable, we can just close the IE browser and releasing the memory by setting it to Nothing:

appIE.QuitSet appIE = Nothing

Well, now you have your value and you can do whatever you want with it: put it into a cell (Range("A1").Value = myValue), or into a label of a form (Me.label1.Text = myValue).

I'd just like to point you out that this is not how StackOverflow works: here you post questions about specific coding problems, but you should make your own search first. The reason why I'm answering a question which is not showing too much research effort is just that I see it asked several times and, back to the time when I learned how to do this, I remember that I would have liked having some better support to get started with. So I hope that this answer, which is just a "study input" and not at all the best/most complete solution, can be a support for next user having your same problem. Because I have learned how to program thanks to this community, and I like to think that you and other beginners might use my input to discover the beautiful world of programming.

Enjoy your practice ;)

Other methods were mentioned so let us please acknowledge that, at the time of writing, we are in the 21st century. Let's park the local bus browser opening, and fly with an XMLHTTP GET request (XHR GET for short).

Wiki moment:

XHR is an API in the form of an object whose methods transfer databetween a web browser and a web server. The object is provided by thebrowser's JavaScript environment

It's a fast method for retrieving data that doesn't require opening a browser. The server response can be read into an HTMLDocument and the process of grabbing the table continued from there.

Note that javascript rendered/dynamically added content will not be retrieved as there is no javascript engine running (which there is in a browser).

In the below code, the table is grabbed by its id cr1.

In the helper sub, WriteTable, we loop the columns (td tags) and then the table rows (tr tags), and finally traverse the length of each table row, table cell by table cell. As we only want data from columns 1 and 8, a Select Case statement is used specify what is written out to the sheet.

Sample webpage view:

Sample code output:

VBA:

Option ExplicitPublic Sub GetRates()    Dim html As HTMLDocument, hTable As HTMLTable '<== Tools > References > Microsoft HTML Object Library        Set html = New HTMLDocument          With CreateObject("MSXML2.XMLHTTP")        .Open "GET", "https://uk.investing.com/rates-bonds/financial-futures", False        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT" 'to deal with potential caching        .send        html.body.innerHTML = .responseText    End With        Application.ScreenUpdating = False        Set hTable = html.getElementById("cr1")    WriteTable hTable, 1, ThisWorkbook.Worksheets("Sheet1")        Application.ScreenUpdating = TrueEnd SubPublic Sub WriteTable(ByVal hTable As HTMLTable, Optional ByVal startRow As Long = 1, Optional ByVal ws As Worksheet)    Dim tSection As Object, tRow As Object, tCell As Object, tr As Object, td As Object, r As Long, C As Long, tBody As Object    r = startRow: If ws Is Nothing Then Set ws = ActiveSheet    With ws        Dim headers As Object, header As Object, columnCounter As Long        Set headers = hTable.getElementsByTagName("th")        For Each header In headers            columnCounter = columnCounter + 1            Select Case columnCounter            Case 2                .Cells(startRow, 1) = header.innerText            Case 8                .Cells(startRow, 2) = header.innerText            End Select        Next header        startRow = startRow + 1        Set tBody = hTable.getElementsByTagName("tbody")        For Each tSection In tBody            Set tRow = tSection.getElementsByTagName("tr")            For Each tr In tRow                r = r + 1                Set tCell = tr.getElementsByTagName("td")                C = 1                For Each td In tCell                    Select Case C                    Case 2                        .Cells(r, 1).Value = td.innerText                    Case 8                        .Cells(r, 2).Value = td.innerText                    End Select                    C = C + 1                Next td            Next tr        Next tSection    End WithEnd Sub

you can use winhttprequest object instead of internet explorer as it's good to load data excluding pictures n advertisement instead of downloading full webpage including advertisement n pictures those make internet explorer object heavy compare to winhttpRequest object.