solved - XML request to REST api
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
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.
@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.
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!
Thank you Gurpreet, you are a genius, I will take a look right now!