CODEWITHSUNDEEP
vbaexcelweb-scraping
Naucle
Naucle

Reputation: 626

Scraping a HTML POST request using VBA

I'm trying to scrape quotes of Moroccan stocks from this website using VBA :

http://www.casablanca-bourse.com/bourseweb/en/Negociation-History.aspx?Cat=24&IdLink=225

Where you select a security, check "By period", specify the date interval and finally click the "Submit" button.

I went first with the easy method : using an Internet Explorer object :

Sub method1()

    Set IE = CreateObject("InternetExplorer.Application")

    IE.Visible = False
    IE.Navigate "http://www.casablanca-bourse.com/bourseweb/Negociation-Historique.aspx?Cat=24&IdLink=302"

    Do While IE.Busy
        DoEvents
    Loop

    'Picking the security
    Set obj1 = IE.document.getElementById("HistoriqueNegociation1_HistValeur1_DDValeur")
    obj1.Value = "4100  " 'Security code taken from the source html

    'Specifying "By period"
    Set obj2 = IE.document.getElementById("HistoriqueNegociation1_HistValeur1_RBSearchDate")
    obj2.Checked = True

    'Start date
    Set obj3 = IE.document.getElementById("HistoriqueNegociation1_HistValeur1_DateTimeControl1_TBCalendar")
    obj3.Value = "07/03/2016"

    'End date
    Set obj4 = IE.document.getElementById("HistoriqueNegociation1_HistValeur1_DateTimeControl2_TBCalendar")
    obj4.Value = "07/03/2016"

    'Clicking the button
    Set objs = IE.document.getElementById("HistoriqueNegociation1_HistValeur1_Image1")
    objs.Click

    'Setting the data <div> as an object
    Set obj5 = IE.document.getElementById("HistoriqueNegociation1_UpdatePanel1")
    s = obj5.innerHTML

    'Looping until the quotes pop up
    Do While InStr(s, "HistoriqueNegociation1_HistValeur1_RptListHist_ctl01_Label3") = 0
        Application.Wait DateAdd("s", 0.1, Now)
        s = obj5.innerHTML
    Loop

    'Printing the value
    Set obj6 = IE.document.getElementById("HistoriqueNegociation1_HistValeur1_RptListHist_ctl01_Label3")
    Cells(1, 1).Value = CDbl(obj6.innerText)

    IE.Quit
    Set IE = Nothing

End Sub

This webpage being dynamic, I had to make the application wait, until the data pops up (until the data pops in the HTML code), and that's why I used that second Do while loop.

Now, what I want to do, is to use the harder way : sending the form request through VBA, which is pretty easy when it comes to GET requests, but this site uses a POST request that I found pretty hard to mimic in VBA.

I used this simple code :

Sub method2()

    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "http://www.casablanca-bourse.com/bourseweb/Negociation-Historique.aspx?Cat=24&IdLink=302"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("encoded request params go here")
    Cells(1, 1).Value = objHTTP.ResponseText

End Sub

I used the Chrome DevTools (F12) to record the POST request. But I had a hard time figuring what the params should be (The form data is too long, i couldn't make a screenshot or copy it here, so please feel free to record it yourself). I went with the only params that I needed (security code, the radiobox and the two dates), but the request response didn't match the DevTools one, and it didn't contain any usable. Here are the params that I used :

HistoriqueNegociation1$HistValeur1$DDValeur=9000%20%20&HistoriqueNegociation1$HistValeur1$historique=RBSearchDate&HistoriqueNegociation1$HistValeur1$DateTimeControl1$TBCalendar=07%2F03%2F2016&HistoriqueNegociation1$HistValeur1$DateTimeControl2$TBCalendar=07%2F03%2F2016

Obviously, I'm not getting something (or everything) right here.

Upvotes: 0

Views: 1435

Answers (1)

Naucle
Naucle

Reputation: 626

Actually, I can't just pick "some of the params", I have to send all of them. I didn't do that at first because the params line that I got from the DevTools was too long (47012 characters), Excel-VBA doesn't acccept a line that long. So I copied the params to a text file and then sent the request using that file, and It worked.

Upvotes: 1

Related Questions