solved - XML request to REST api

xingyi052387

solved - XML request to REST api

Gurpreet

I wrote this sample code to get the Elektron Timeseries Extraction request for a sample ISIN instrument. You can take it as a starting point and implement your own. You will need a valid authentication token which can be retrieved by invoking the https://hosted.datascopeapi.reuters.com/RestApi/v1/Authentication/RequestToken endpoint.

Sub ExtractWithNotes()

    Dim mxm As Object
    Dim requestMsg As String
    Dim response As String
    Dim authToken As String

    authToken = "***"

    requestMsg = "{""ExtractionRequest"": {""@odata.type"": ""#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.ElektronTimeseriesExtractionRequest""," & _
        """ContentFieldNames"": [""Ask"",""Bid"",""High"",""Last"",""Low"",""Trade Date""]," & _
        """IdentifierList"": {""@odata.type"": ""#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.InstrumentIdentifierList""," & _
        """InstrumentIdentifiers"": [{""Identifier"": ""US0378331005"",""IdentifierType"": ""Isin""}]," & _
        """ValidationOptions"": null,""UseUserPreferencesForValidationOptions"": false}," & _
        """Condition"": {""StartDate"": ""2015-12-01T13:00:00.000Z"",""EndDate"": ""2015-12-02T13:05:00.000Z""}}}"

    Set mxm = CreateObject("MSXML2.XMLHTTP")

    Debug.Print "Sending Request..."

    With mxm
        .Open "POST", "https://hosted.datascopeapi.reuters.com/RestApi/v1/Extractions/ExtractWithNotes", True
        .SetRequestHeader "Authorization", "Token " & authToken
        .SetRequestHeader "Prefer", "respond-async"
        .SetRequestHeader "Content-Type", "application/json; odata=minimalmetadata"

        .Send (requestMsg)
        ' waiting for response
        While mxm.readyState <> 4
            DoEvents
        Wend
        response = .ResponseText
    End With

    Debug.Print response

End Sub

Alex Putkov.1

DSS is an enterprise product. It's highly unusual for someone to want to retrieve data from DSS into VBA. It is of course possible, but we don't have a tutorial written for it. Here's an external resource you may find useful. It describes how to send HTTP requests from VBA.
https://codingislove.com/http-requests-excel-vba/
Combined with the tutorials on DSS REST API this should be sufficient for you to be able to retrieve data from DSS into VBA.

Gurpreet

@xingyi052387 There is an official .NET library for DSS, but it is not "COM interop" enabled, and hence can't be used with VBA. Your only option is to invoke raw HTTP requests follwoing the links posted by Alex.

xingyi052387

Sub Reuters_api()

Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String, Login64 As String

Login64 = "MTUwNzMdOkRCdTAyGjAsw" 

myurl = "https://hosted.datascopeapi.reuters.com/RestApi/v1/Extractions/ExtractWithNotes"

xmlhttp.Open "POST", myurl, False

xmlhttp.setRequestHeader "Authorization", "Basic " & Login64

xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded'"

xmlhttp.send "Request= Get,XS1991242345, (BID)" <---- please help with this part.. I did a lot of research and not sure what the syntaxes are to retrieve a security's Bid price based on the ISIN and EJV source.

MsgBox (xmlhttp.responseText)

Hi Refinitiv staves,

Are you able to help with the syntax for retrieving security prices based on ISIN + EJV source?

xmlhttp.send " codes here"

Thank you so much!

xingyi052387

Thank you Gurpreet, you are a genius, I will take a look right now!