WinHttpRequest with POST times out

[S_White]

Hello all,

I'm trying to import some data for a fund listed on the Luxembourg SE. When I go to the site, I can insert the ISIN code, LU1883863935, in the search field and from there I can read the exchange symbol, 294814.

As I'm trying to get the data into Excel, I can see when I use Inspect, Network, Xhr that the symbol appears to be found utilizing this URL by a WinHttpRequest with a POST. A Request Payload seems to be added as well.

I thought that this code could be used to read the symbol (294814)

Sub GetSymbol()
Dim WinHttp As New WinHttpRequest, HtmlDoc As New HTMLDocument, BodyStr As String

BodyStr = "{query: ""LU1883863935"", affiner: []}"
With WinHttp
    .Open "POST", "https://www.bourse.lu/api/search/", False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    .send BodyStr
    
    HtmlDoc.body.innerHTML = .responseText
End With
End Sub

but each time, it times out? I have to admit that I'm not that familiar with POST requests, but looking at the network traffic, I think I'm close...

Anyone who can see what I'm missing in my request?

/S_White

 

[S_White]

Hello again,

Since my initial post, I've done some searching in order to see if I can sort this myself and with help from various ressources, I've managed to put this code together.
What it does is that it fetch a cookie in the first request and the put it in as a requestheader in the second, but it doesn't work

I've switched from WinHttpRequest to XmlHttp - that sorted the time out, and I've read that before I do a POST request that requires a cookie, I need to read that from a GET request. Is that right?

This is how my code looks like:

Sub GetSymbol()
Dim XmlHttp As New MSXML2.XmlHttp, URL As String, Data As Variant, strCookie As String

URL = "https://www.bourse.lu/api/suggest"
With XmlHttp
    .Open "GET", URL, False
    .send
    Data = .getAllResponseHeaders()
    strCookie = Split(Split(Data, "Set-Cookie:")(1), ";")(0)
    
    .Open "POST", URL, False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Cookie", Trim(strCookie)
    .send "query=LU1883863935"
    MsgBox .responseText
End With
End Sub

My code is based on that I've used Home, right clicked on the magnifying glass and selected "Inspect", "Network". Finally, I've pasted the ISIN, LU1883863935, into the search field and from there I've seen that the "suggest" URL has been triggered.

Anyone who can take me further? @John_w maybe?

 

[John_w]

You've omitted some quotes from the payload data (form data). The raw data is:

{"query":"LU1883863935","affiner":[]}

and you must repeat each quote character in a VBA string to get a quote in that position in the final string. Also, the Content-Type header should be "application/json".

Try this:

Sub GetSymbol()

    Dim WinHttp As New WinHttpRequest, BodyStr As String
  
    BodyStr = "{""query"":""LU1883863935"",""affiner"":[]}"
    Debug.Print BodyStr
  
    With WinHttp
        .Open "POST", "https://www.bourse.lu/api/search/", False
        .SetRequestHeader "Content-Type", "application/json;charset=UTF-8"
        .send BodyStr
        Debug.Print .ResponseText
    End With

End Sub

 

[S_White]

Ahhhh got it! I was confused by the POST request - I thought I was supposed to pass some cookies.

One finding though. The request seems to time out when done as WinHttpRequest, but changing it to MSXML2.XmlHttp did the trick.

@John_w - thanks for you help and clarification. You're such a capacity when it comes to this kind of requests.