0

Thomson Reuters Eikon used:

I need to get data from reuters regarding fx spot prices. This is easily done with a reuters formula. However, I need to use this data in a VBA script. Part of the VBA script inserts the formula, reuters then retrieves data but this can take a while (10-20 seconds).

How do i make the VBA script pause until reuters has retrieved the data? (in the first 10 seconds the cell of the reuters data says: 'retrieving....'

Range("B2").value = Application.Evaluate("RtGet(" & IDN & "," & RWAL & "," & MIDPRICE & "," & TYPENUM & ")")

This code is now used to insert the formula where RWAL is the exchange rate identifier (such as EUR=)

Luuklag
  • 3,897
  • 11
  • 38
  • 57
user3417414
  • 45
  • 2
  • 6
  • You can make a `Do While` loop which will repeat something like `Sleep 1000` until value of cell changes (compared to previous value stored in a variable). – ttaaoossuuuu Apr 29 '14 at 10:45
  • Sleep (and wait) hold up the whole excel right? So that would mean that the formula also stops trying to retrieve reuters data. – user3417414 Apr 29 '14 at 11:26
  • Do you only need a one time snapshot of the prices or do you need the prices continuously streaming into your Excel sheet?. The function `RtGet()` is for the latter scenario. Also please update your question with what desktop software you are using (e.g. *Reuters 3000 Xtra* or *Thomson Reuters Eikon*). – peterh Apr 29 '14 at 11:44
  • I needa one time snapshot at current time (and they way i get this now is by streaming and then copy paste value). I use Thomson Reuters Eikon – user3417414 Apr 29 '14 at 11:50
  • If you do coding, you might better you proprietary Thomson Reuters data access interfaces like AdxRtList for realtime data. – Rustam Sep 18 '14 at 13:09

1 Answers1

1

We need to use application.run to use the RtGet function in code as its really a worksheet function. Because its a worksheet function, the data will not be retrieved while code is running.

To get round this we use Application.RTD.RefreshData to pull the data from the RTD server

Function getField(ric As String, field As String) As String
    Data = Application.Run("RtGet", "IDN", ric, field)
    Do While VBA.Left(CStr(Data), 3) = "Ret" 'ie loop while it is retrieving data
        Application.RTD.RefreshData
        DoEvents
        Data = Application.Run("RtGet", "IDN", ric, field)
        Debug.Print Data
    Loop
    getField = Data
End Function

Public Sub testGetField()
    Dim test
    test = getField("EUR=", "BID")
End Sub
Neil Shah
  • 173
  • 8