Discover Refinitiv
MyRefinitiv Refinitiv Perspectives Careers
Created with Sketch.
All APIs Questions & Answers  Register |  Login
Ask a question
  • Questions
  • Tags
  • Badges
  • Unanswered
Search:
  • Home /
  • DSS /
avatar image
Question by xingyi052387 · Jun 19, 2019 at 12:26 PM · vba

solved - XML request to REST api

People who like this

0 Show 0
Comment
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

5 Replies

  • Sort: 
avatar image
REFINITIV
Best Answer
Answer by Gurpreet · Jun 20, 2019 at 01:33 PM

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
Comment
xingyi052387

People who like this

1 Show 0 · Share
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

avatar image
REFINITIV
Answer by Alex Putkov. · Jun 19, 2019 at 12:51 PM

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.

Comment
xingyi052387

People who like this

1 Show 0 · Share
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

avatar image
REFINITIV
Answer by Gurpreet · Jun 19, 2019 at 01:27 PM

@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.

Comment
xingyi052387

People who like this

1 Show 0 · Share
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

avatar image
Answer by xingyi052387 · Jun 19, 2019 at 08:11 PM

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!

Comment

People who like this

0 Show 0 · Share
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

avatar image
Answer by xingyi052387 · Jun 20, 2019 at 02:51 PM

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

Comment

People who like this

0 Show 0 · Share
10 |1500 characters needed characters left characters exceeded
▼
  • Viewable by all users
  • Viewable by moderators
  • Viewable by moderators and the original poster
  • Advanced visibility
Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Watch this question

Add to watch list
Add to your watch list to receive emailed updates for this question. Too many emails? Change your settings >
13 People are following this question.
  • Feedback
  • Copyright
  • Cookie Policy
  • Privacy Statement
  • Terms of Use
  • Careers
  • Anonymous
  • Sign in
  • Create
  • Ask a question
  • Spaces
  • Alpha
  • App Studio
  • Block Chain
  • Bot Platform
  • Calais
  • Connected Risk APIs
  • DSS
  • Data Fusion
  • Data Model Discovery
  • Datastream
  • Eikon COM
  • Eikon Data APIs
  • Elektron
    • EMA
    • ETA
    • WebSocket API
  • Legal One
  • Messenger Bot
  • Messenger Side by Side
  • ONESOURCE
    • Indirect Tax
  • Open PermID
    • Entity Search
  • Org ID
  • PAM
    • PAM - Logging
  • ProView
  • ProView Internal
  • Product Insight
  • Project Tracking
  • Refinitiv Data Platform
    • Refinitiv Data Platform Libraries
  • Rose's Space
  • Screening
    • Qual-ID API
    • Screening Deployed
    • Screening Online
    • World-Check One
    • World-Check One Zero Footprint
  • Side by Side Integration API
  • TR Knowledge Graph
  • TREP APIs
    • CAT
    • DACS Station
    • Open DACS
    • RFA
    • UPA
  • TREP Infrastructure
  • TRIT
  • TRKD
  • TRTH
  • Thomson One Smart
  • Transactions
    • REDI API
  • Velocity Analytics
  • Wealth Management Web Services
  • World-Check Data File
  • Explore
  • Tags
  • Questions
  • Badges