Upgrade from Eikon -> Workspace. Learn about programming differences.

For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
benoit.laurent2 avatar image
1 ●0 ●1 ●1

How to show results of a RHistory query with VBA into an Excel sheet?

I would like to write into the cell A1 of an Excel sheet the result of the query below, but I don't know what I need to do :

Set myRHistoryManager = CreateHistoryManager

myRHistoryCookie = myRHistoryManager.Initialize("MY BOOK")

Set myRHistoryQuery = myRHistoryManager.CreateHistoryQuery(myRHistoryCookie)

' Set the query parameters accordingly to your needs

With myRHistoryQuery

.InstrumentIdList = "VOWG5YEUAM=R" ' Range("G6").Value

.FieldList = "MID_SPREAD.Timestamp;MID_SPREAD.Close" ' Range("G7").Value 'Or of the form "TRDPRC_1.TIMESTAMP;TRDPRC_1.VALUE;TRDPRC_1.VOLUME"

.RequestParams = "START:" & CDbl(ActiveSheet.Range("A1").Value) & " END:" & CDbl(ActiveSheet.Range("A2").Value) & " INTERVAL:1D" ' Range("G8").Value

.RefreshParams = "FRQ:5S" ' Range("G9").Value

.DisplayParams = "" ' ActiveSheet.Range("G10").Value '"TSREPEAT:YES CH:Fd"

.Subscribe

End With

Exit Sub


Where do I have to write the cell on which I want the result?


Thanks


eikoneikon-data-apipythonrefinitiv-dataplatform-eikonworkspaceworkspace-data-apieikon-com-api
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.

zoya faberov avatar image   zoya faberov

Hello @benoit.laurent2,

Thank you for your participation in the forum.

Is the reply below satisfactory in resolving your query?

If yes please click the 'Accept' text next to the reply. This will guide all community members who have a similar question. Otherwise please post again offering further insight into your question.

Thanks,

-AHS

zoya faberov avatar image   zoya faberov

Hello @benoit.laurent2,

Please be informed that a reply has been verified as correct in answering the question, and has been marked as such.

Thanks,

-AHS

4 Answers

· Write an Answer
Upvote
Accepted
Jirapongse avatar image
79.1k ●250 ●52 ●74

I have modified the Basic.sample.xlsm to display the result at Cell A1.

Private Sub m_rhistoryQuery_OnImage(ByVal a_historyTable As Variant)
    ' TODO: Use the data in the array a_historyTable
    Range(Cells(1, 1), Cells(UBound(a_historyTable, 1), UBound(a_historyTable, 2))).Value = a_historyTable
End Sub

Private Sub m_rhistoryQuery_OnUpdate(ByVal a_historyTable As Variant, ByVal a_startingRowIndex As Long, ByVal a_startingColumnIndex As Long, ByVal a_shiftDownExistingRows As Boolean)   
    Range(Cells(1, 1), Cells(UBound(a_historyTable, 1), UBound(a_historyTable, 2))).Value = a_historyTable
End Sub

Basic.sample.zip


basicsample.zip (26.1 KiB)
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.

benoit.laurent2 avatar image benoit.laurent2

Hi,

Is it possible to include this sub into a loop? I would like to query historical data of many assets with VBA to avoid writing RHistory in my Excel sheets for each. When I create a loop, the first lap works, but on the second one, the ".suscribe" functionality doesn't work and no data can be requested.

Do I need to to put something particular?


Thank you.

Upvotes
zoya faberov avatar image
32.2k ●40 ●11 ●20

Hello @benoit.laurent2,

You may find useful RHistory API Examples, Single-Query Sample Excel book conveys output via Cells.

Does this help?

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
benoit.laurent2 avatar image
1 ●0 ●1 ●1

Thank you, but it doesn't help me to understand where I have to give the cell where the answer of the query must be written. By default, in RHistoryAPI.xlsm, there is no place in the code to display the results in termsheet.

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
benoit.laurent2 avatar image
1 ●0 ●1 ●1

Ok thanks. I didn't understand the use of .subsrcibe. I can see this property calls the sub myRHistoryQuery_OnImage when I put a stop on this sub.

Maybe it's not clear enough in the tutorial.

Many thanks for your help.

Best regards.

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.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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