VBA code to grab data from a website (ASP.NET)

Question

There's this website: https://mwatch.boursakuwait.com.kw/default.aspx/AllShares

There's a stock market table that I want to import into my Excel workbook.

I found this code on a website and tried to edit it:

Option Explicit
Sub gethtmltable()
Dim objWeb As QueryTable

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;https://mwatch.boursakuwait.com.kw/default.aspx/AllShares", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

I get a message saying that the query returned no data.

Can anyone please help? I'm using the latest version of Excel on an iMac.

There is no "Import data from a website" option.

Ohhh if only it was that easy, but it's not... and I'm afraid I can't help you because I can't read arabic. — dwirony, Dec 27 '18 at 22:04
I don't see any kind of API or ability to connect via a query, you'd have to webscrape the whole thing which would be a giant task in itself. — dwirony, Dec 27 '18 at 22:11
Are you using Excel on Windows? I think there's an "Import Data fom a website" option. Can you try doing that and copy>paste the code? — nowifi6952, Dec 27 '18 at 22:16
It looks like immediately when the page loads, there's indeed "No data available in table.". However, if you wait some seconds (say 10), the data loads. You might just need to add a "wait" line in the code that allows the data to load before pulling it. — BruceWayne, Dec 27 '18 at 22:17
Yes there is. Please click "English" at the top then the "All Share [sic]" tab. There is a table with ticker name, number, closing price etc. — nowifi6952, Dec 27 '18 at 22:17
Please be aware of the time delay in loading the webpage, and the page actually loading your data. — BruceWayne, Dec 27 '18 at 22:18
@nowifi6952, From what I can see in Chrome, whilst the page loads, it makes a POST request to `https://mwatch.boursakuwait.com.kw/default.aspx/getData` (look for `getData`) and the server responds with a JSON string, which I think populates the page's HTML (I searched the JSON string for strings/words that appear in the final table and they were present). My point is that instead of waiting for the final HTML, it might be quicker just to work with the JSON. Unfortunately, seems like the typical ways of scraping the web on Windows (WinHttp, XmlHTTP, Power Query) aren't available on Mac. — chillin, Dec 28 '18 at 01:10
This might be of use to you: https://stackoverflow.com/questions/33963099/send-data-in-http-post-from-vba-excel-for-mac/34003425#34003425, I don't know. — chillin, Dec 28 '18 at 01:15

QHarr · Answer 1 · 2018-12-29T01:48:49.463

0

Windows machine:

As someone has mentioned there is a POST request made that returns JSON you can parse. The dictionary returned has a weird layout so you will need to invest time looking into that. I use a json parser (jsonconverter.bas) which after adding to your project you also need to go to VBE > Tools > References > Add a reference to Microsoft Scripting Runtime.

Option Explicit
Public Sub GetInfo()
    Dim sResponse As String, json As Object

    With CreateObject("MSXML2.XMLHTTP")
        .Open "POST ", "https://mwatch.boursakuwait.com.kw/default.aspx/getData", False
        .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
        .setRequestHeader "Content-Type", "application/json; charset=utf-8"
        .send
        sResponse = .responseText
    End With
    Set json = JsonConverter.ParseJson(sResponse) 'dictionary
    'handle code
    Stop
End Sub

If you print JSON("d") you get a string which makes clear items:

Example:

If you want to use a timed loop with Internet Explorer there is an example below:

Option Explicit

Public Sub GetInfo()
    Dim ie As New InternetExplorer, t As Date, table As Object, clipboard As Object, ws As Worksheet
    Const MAX_WAIT_SEC As Long = 20
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ie
        .Visible = True
        .Navigate2 "https://mwatch.boursakuwait.com.kw/default.aspx/AllShares"

        While .Busy Or .readyState < 4: DoEvents: Wend

        t = Timer
        Do
            DoEvents
            On Error Resume Next
            Set table = ie.document.querySelector("#tblMarketData")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While InStr(.document.body.innerHTML, "No data available in table") > 0
        If Not table Is Nothing Then
            Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            clipboard.SetText table.outerHTML
            clipboard.PutInClipboard
            ws.Cells(1, 1).PasteSpecial
        End If
        .Quit
    End With
End Sub

Mac:

I would switch languages. For example to python. I am happy to add a script for that if desired.

edited Dec 29 '18 at 01:48

answered Dec 28 '18 at 17:27

QHarr

83,427
12
54
101

1

Since the user mentioned they are on Mac, I don't think `MSXML2.XMLHTTP` or the IE approach will work. Leaving this link, in case is of any help. https://stackoverflow.com/a/15994195/4839827 – Ryan Wildry Dec 29 '18 at 01:42
I didn’t actually notice that. My bad. I will leave up for windows users. There is little selectivity with Mac. You tend to get everything! I would definitely switch languages. – QHarr Dec 29 '18 at 01:48
Hello, thanks for the answer. I'm on an iMac machine. Your code returns a compile-error. – nowifi6952 Dec 29 '18 at 12:17
If you switch languages (python) would that work on my excel worksheet? I'm desparate for anything that scrapes the data on that website. – nowifi6952 Dec 29 '18 at 12:18
I will even buy you a $20 amazon/netflix/iTunes gift card as a "thank you" gesture if you help me with this code – nowifi6952 Dec 29 '18 at 12:18
@nowifi6952 if you are ok with python code, consider making a new question with the python tag. Will get more responses there :) – Ryan Wildry Dec 29 '18 at 15:22
I don't need a thank you present. You may get a faster response (and certainly should python tag for python answer) as I am currently in hospital without a mac. Am happy to write a python script to write to Excel when I am out. It is very easy to write the base part of mirroring xmlhttp to requests library in python with requests post. You may need xlwings/xlrd for writing to Excel. – QHarr Dec 29 '18 at 15:33
Please remember to tag the actual question with mac (or other users could do this too!) – QHarr Dec 29 '18 at 15:36
OK. That went way over my head. As long as it writes to excel I'm happy with it. Wishing you or whomever you're visiting a speedy recovery. – nowifi6952 Dec 29 '18 at 17:13

VBA code to grab data from a website (ASP.NET)

1 Answers1