XmlHttp request to get a ticker from Morningstar

[S_White]

Hi All,
I'm trying to read the ticker (called F00000LWVH) used on Morningstar on a fund with this query based on an ISIN:

Sub MstarTicker()
Dim Url As String, XmlHttp As New MSXML2.XmlHttp, PayloadStr As String, RespVar As Variant

Url = "https://www.morningstar.co.uk/uk/util/SecuritySearch.ashx?source=nav&moduleId=6&ifIncludeAds=True&usrtType=v"
PayloadStr = """q""=""gb00b3x7qg63"",""limit""=""100"",""timestamp""=""" & Left(Split((DateDiff("s", "01/01/1970", Date) + Timer) * 1000, ".")(0), 13) & """,""preferedList""="""
With XmlHttp
    .Open "POST", Url, False
    .setRequestHeader "Content-Type", "text/plain; charset=utf-8"
    .send PayloadStr
    If Not .Status = 200 Then Exit Sub
    RespVar = Split(.responseText, "|")
End With
End Sub

From the traffic, I can see that it's a POST request with a payload, but for some reason the responseText is always blank even though the Status is 200? I suspect that it is the timestamp (UTC), but I've done it in accordance with the payload.

Am I missing something in this query?

Thanks in advance

 

[John_w]

Your URL displays a blank page for me. How are you searching for the ticker in a browser?

 

[S_White]

When I insert the ISIN, gb00b3x7qg63, in the search box, top left corner on Morningstar Financial Research, Analysis, Data and News, the POST request seems to run and when I look at the Response, I get something like the image I've attached. For some reason, I cannot replicate the POST method in my code

 

[John_w]

I found 2 errors in your code.

1. The payload string should be raw data, with an ampersand character separating each parameter.
2. The Content-Type header should be "application/x-www-form-urlencoded; charset=UTF-8", because the POST request is sending form data.

This works for me:

Sub MstarTicker2()
    Dim Url As String, PayloadStr As String, RespVar As Variant
    #If VBA7 Then
        Dim XmlHttp As MSXML2.XMLHTTP60
        Set XmlHttp = New MSXML2.XMLHTTP60
    #Else
        Dim XmlHttp As MSXML2.XmlHttp
        Set XmlHttp = New MSXML2.XmlHttp
    #End If
    
    Url = "https://www.morningstar.co.uk/uk/util/SecuritySearch.ashx?source=nav&moduleId=6&ifIncludeAds=True&usrtType=v"
    PayloadStr = "q=gb00b3x7qg63&limit=100&timestamp=" & DateDiff("s", "01/01/1970", Now) & "&preferedList="
    With XmlHttp
        .Open "POST", Url, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .send PayloadStr
        If Not .Status = 200 Then Exit Sub
        Debug.Print .responseText
        RespVar = Split(.responseText, "|")
    End With
End Sub

 

[S_White]

Well, the short answer must be that if it works for you, @John_w, it works for me and everybody else as well as you're a true genius when it comes to xmlhttp requests.
I've tried it out and of course it works, but the use of '&' instead of ',' in the payload string I find hard to understand because it's not what it seems like when I look at it from the traffic on the site?

Sorted - thanks

 

[John_w]

You need to use the raw Request payload string, not the 'formatted' one:

In addition, any parameter names or values which contain the characters " %<>=&!@#$^()+{[}]|\;:'"",/?" (includes space), must each be encoded separately. In this case you can see from the data that encoding isn't necessary, however it's good practice to do it anyway.

Excel 2013 and later versions have the URLENCODE function which can be called from VBA, so the proper code to encode the payload string would be:

    PayloadStr = Encode("q") & "=" & Encode("gb00b3x7qg63") & "&" & _
                 Encode("limit") & "=" & Encode("100") & "&" & _
                 Encode("timestamp") & "=" & Encode(DateDiff("s", "01/01/1970", Now)) & "&" & _
                 Encode("preferedList") & "=" & Encode("")

Private Function Encode(ByVal param As String) As String
    Encode = Application.WorksheetFunction.EncodeURL(param)
End Function

 

[S_White]

Even better. Thx @John_w