Can I find the default Eikon for Excel RHistory fields for an instrument using the COM APIs and VBA?

Ripley79

Find more posts tagged with

eikon

excel

vba

eikon-com-api

Accepted answers

DevTrev

Yes, but it depends on whether the AdxRtHistory Class of the AdxRtLib library (older methodology, Interday and beyond only) or the RhistoryAPI library (newer methodology, all time periods) is being used.

For RHistoryAPI there is no direct way to get the actual field names being used, but by using the field list as "" (to request ALL fields) and the display parameter "CH:Fd", when the data is returned the first row of the array will be the names of the fields.
In the Visual Basic Editor, Tools, References for the RHistoryAPI (or look for RHistoryAPI.dll in the X/Bin, Y/Bin or Z/Bin of the TR Eikon installation directory)

Private myRHistoryManager As RHistoryAPI.RHistoryManager 
Private myRHistoryCookie As Long 
Private WithEvents myRHistoryQuery As RHistoryAPI.RHistoryQuery 
Private Declare Function CreateReutersObject Lib "PLVbaApis.dll" (ByVal progID As String) As Object 

Private Sub getHistory() 
Set myRHistoryManager = CreateReutersObject("RHistoryAPI.RHistoryManager") 'Create.the Manager object 
myRHistoryCookie = myRHistoryManager.Initialize("MY BOOK") 'Initialise 
Set myRHistoryQuery = myRHistoryManager.CreateHistoryQuery(myRHistoryCookie) ' Create the Query object. 

With myRHistoryQuery 
	.InstrumentIdList = "VOD.L" 
	.FieldList = "" ' To request ALL fields 
	.RequestParams = "NBROWS:1 INTERVAL:TICK" 
	.RefreshParams = "FRQ:10S" 
	.DisplayParams = "SORT:D CH:Fd" 
	.Subscribe 
End With 
End Sub 



Private Sub myRHistoryQuery_OnUpdate(ByVal a_historyTable As Variant, ByVal a_startingRowIndex As Long, ByVal a_startingColumnIndex As Long, ByVal a_shiftDownExistingRows As Boolean) 
Dim rw As Integer, col As Integer 

For rw = LBound(a_historyTable, 1) To UBound(a_historyTable, 1) 
For col = LBound(a_historyTable, 2) To UBound(a_historyTable, 2)             
Debug.Print a_historyTable(rw, col) 
Next col 
Next rw 
End Sub

From the names of the fields returned one can infer the actual fields being used, examples below;

Field Header refers to Category

Trade Close refers to TRDPRC_1

Bid Close refers to BID

Bid Yield Close refers to BID_YIELD

VALUE refers to ECONOMIC (e.g. GBCBIS=ECI - the Timestamp field header "CH:Fd" returns as UPPER CASE TIMESTAMP)

For more details on using AdxRtHistory, go to the Tutorial 5 in the COM APIs page and for the RHistoryAPI, Tutorial 9 COM Learning

All comments

DevTrev

Private myRHistoryManager As RHistoryAPI.RHistoryManager 
Private myRHistoryCookie As Long 
Private WithEvents myRHistoryQuery As RHistoryAPI.RHistoryQuery 
Private Declare Function CreateReutersObject Lib "PLVbaApis.dll" (ByVal progID As String) As Object 

Private Sub getHistory() 
Set myRHistoryManager = CreateReutersObject("RHistoryAPI.RHistoryManager") 'Create.the Manager object 
myRHistoryCookie = myRHistoryManager.Initialize("MY BOOK") 'Initialise 
Set myRHistoryQuery = myRHistoryManager.CreateHistoryQuery(myRHistoryCookie) ' Create the Query object. 

With myRHistoryQuery 
	.InstrumentIdList = "VOD.L" 
	.FieldList = "" ' To request ALL fields 
	.RequestParams = "NBROWS:1 INTERVAL:TICK" 
	.RefreshParams = "FRQ:10S" 
	.DisplayParams = "SORT:D CH:Fd" 
	.Subscribe 
End With 
End Sub 



Private Sub myRHistoryQuery_OnUpdate(ByVal a_historyTable As Variant, ByVal a_startingRowIndex As Long, ByVal a_startingColumnIndex As Long, ByVal a_shiftDownExistingRows As Boolean) 
Dim rw As Integer, col As Integer 

For rw = LBound(a_historyTable, 1) To UBound(a_historyTable, 1) 
For col = LBound(a_historyTable, 2) To UBound(a_historyTable, 2)             
Debug.Print a_historyTable(rw, col) 
Next col 
Next rw 
End Sub

From the names of the fields returned one can infer the actual fields being used, examples below;

Field Header refers to Category

Trade Close refers to TRDPRC_1

Bid Close refers to BID

Bid Yield Close refers to BID_YIELD

VALUE refers to ECONOMIC (e.g. GBCBIS=ECI - the Timestamp field header "CH:Fd" returns as UPPER CASE TIMESTAMP)

For more details on using AdxRtHistory, go to the Tutorial 5 in the COM APIs page and for the RHistoryAPI, Tutorial 9 COM Learning

piotr

Hello,

I have additional question related to the topic.

With myRHistoryQuery
.InstrumentIdList = "VOD.L" - there are RIC or multiple RICs included inside the query.

However, I would like to retrieve data for multiple instruments in Range of cells. For example in column A.

Could someone advise?

Thank you,

Piotr

Jirapongse

@piotr

You can use the following code to get multiple instruments from Range of cells (Column A).

Dim Lastrow As Integer
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

With myRHistoryQuery 
        .InstrumentIdList = ""
        For Each c In Range("A1:A" & Lastrow)
            If IsEmpty(c.Value) = True Then
                Exit For
            End If
            .InstrumentIdList = .InstrumentIdList + c.Value + ";"
        Next c
	.FieldList = "" ' To request ALL fields 
	.RequestParams = "NBROWS:1 INTERVAL:TICK" 
	.RefreshParams = "FRQ:10S" 
	.DisplayParams = "SORT:D CH:Fd" 
	.Subscribe 
End With

The above code will retrieve multiple items from column A1 to the last row of column A or until the cell is empty.

EXPLORE OUR SITES