question

Upvotes
Accepted
488 13 13 26

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

eikoneikon-com-apiexcelvba
icon clock
10 |1500

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

Upvotes
Accepted
791 10 8 14

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

icon clock
10 |1500

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

Upvotes
1 0 0 0

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

icon clock
10 |1500

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

Upvotes
45.2k 103 43 60
@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.

icon clock
10 |1500

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