Web scraping with excel VBA returns no values for certain sites

Question

I'm trying to scrape data from Oslo Børs related to 3months NIBOR using excel VBA. However my code do not return any values back to excel. The same code is able to retrieve table data from other similar websites, but could it be related to the ui-view set-up of the html language? I'm not very skilled on html language, so I do come to short here. Any help would be much appreciated. Thanks

Sub NIBOR3M_oslobors()

Dim ie As InternetExplorer
Dim InternetExplorer As Object
Dim strURL As String
        Dim ws As Worksheet
        Dim rng As Range
        Dim tbl As Object
        Dim rw As Object
        Dim cl As Object
        Dim nextrow As Long
        Dim I As Long

strURL = "https://www.oslobors.no/markedsaktivitet/#/details/NIBOR3M.NIBOR/overview"

Set ie = CreateObject("InternetExplorer.Application")
With ie
    .navigate strURL
    Do Until .READYSTATE = 4: DoEvents: Loop
        Do While .Busy: DoEvents: Loop
            Set doc = ie.Document

        Set ws = Worksheets("Interest rates")

        For Each tbl In doc.getElementsByTagName("table")
            nextrow = nextrow + 1
            Set rng = ws.Range("M8:M8" & nextrow)
            For Each rw In tbl.Rows
                For Each cl In rw.Cells
                    rng.Value = rw.innerText
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next cl
                nextrow = nextrow + 1
                Set rng = rng.Offset(1, -I)
                I = 0
            Next rw
        Next tbl

            .Quit
        End With

 ActiveSheet.Range("M8").Select

End Sub

Answer 1

The tables you are trying to scrape are generated by scripts, so you will not find them in the HTML document. To get your hands on the data you will need to send an XHR request.

To find out how this request should look like, you have to inspect the network traffic when the page is loaded. You can do that through your browser's developer tools (Ctrl+Shift+E if you're using Firefox).

There you will see several requests of different types being sent. In your case, you should look for XHR requests of type json. There's a few of them. If you go through the responses of these requests, you will eventually find the one you need, as it will contain the data you want.

Here's how it looks like:

If you go through the Headers and the Params of the request you will see how the url, the body and the headers should look like. In this particular case, all the parameters are encoded into the url and the headers are not essential to the success of the request, so all you need is the url.

The response's payload is in json format. You can inspect its structure using a tool like this. Here's how it looks like:

To parse a response like that you need a parser (look at the end of this post).

TL;DR :

Here's how to get the data from the "Nibor 3 month" table:

Option Explicit

Sub oslobors()
Dim req As New WinHttpRequest
Dim respJSON As Object
Dim key As Variant
Dim url As String
Dim results()
Dim i As Integer
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Name of your worksheet")
url = "https://www.oslobors.no/ob/servlets/components?type=nibor&source=feed.index.nibor.INDICES_FIXING&view=DELAYED&columns=ITEM%2C+CLOSENZ%2C+TRADE_TIME%2C+CLOSE_DATE%2C+CHANGENZ%2C+CHANGENZ_PCT&filter=ITEM_SECTOR%3D%3DsNIBOR3M.NIBOR&channel=e6da3e88c3464b7ad9620824b8d39c95"
With req
    .Open "GET", url, False
    .send
    Set respJSON = JsonConverter.ParseJson(.responseText)
End With
ReDim results(1 To respJSON("rows")(1)("values").Count, 1 To 2)
i = 1
For Each key In respJSON("rows")(1)("values").Keys
    results(i, 1) = key
    results(i, 2) = respJSON("rows")(1)("values")(key) 'write the results in an array
    i = i + 1
Next key
sht.Cells(1, "A").Resize(UBound(results, 1), UBound(results, 2)) = results 'Print the results array in one go. The results will be printed to an area/range starting with cell A1 and expanding as much as it is needed

End Sub

For demonstration purposes, the data is printed in your immediate window:

You will need to add the following references to your project (VBE>Tools>References):

Microsoft WinHTTP Services version 5.1
Microsoft HTML Objects Library
Microsoft Scripting Runtime

You will also need to add this JSON parser to your project. Follow the installation instructions in the link and you should be set to go.

To get the data from the "Historical Prices" table you just have to change the url to this:

url = "https://www.oslobors.no/ob/servlets/components?type=table&source=feed.index.nibor.INDICES_FIXING&view=DELAYED&columns=CHANGE_1WEEK_PCT%2C+HIGH_1WEEK%2C+LOW_1WEEK%2C+CHANGE_1MONTH_PCT%2C+HIGH_1MONTH%2C+LOW_1MONTH%2C+CHANGE_YEAR_PCT%2C+HIGH_YEAR%2C+LOW_YEAR%2C+CHANGE_1YEAR_PCT%2C+HIGH_1YEAR%2C+LOW_1YEAR&filter=ITEM_SECTOR%3D%3DsNIBOR3M.NIBOR&channel=283044a7d182ca196a16337ba79f089c"

EDIT

Modified the code to print the results in a worksheet named "Name of your worksheet".