How to Grab Bid & Ask at Certain times using Excel add on

Settlements
Settlements Newcomer
edited September 23 in Eikon COM

Hi,

I've been trying to make a formula to try and grab the Bid and Ask price at certain times using the Excel add on for Refinitiv.

I've tried to use the =RHistory formula to do this using preset data to the left of formula as seen in the example below.

=INDEX(RHistory($D2,"TRDPRC_1","START:"&TEXT($A2,"dd-mmm-yyyy")&":"&TEXT($B2,"hh:mm:ss")&" END:"&TEXT($A2,"dd-mmm-yyyy")&":"&TEXT($B2+TIME(0,1,0),"hh:mm:ss")&" INTERVAL:1M",,),1,5)
Unfortunately, it seems this formula isn't what I need or not the correct formula to use for that function.

Is there a recommended way to do to this using the Windows 11 excel add on version?

Screenshot 2025-09-23 at 13.57.36.png


Many thanks!
James

Tagged:

Answers

  • Jirapongse
    Jirapongse ✭✭✭✭✭

    @Settlements

    Thank you for reaching out to us.

    For the LSEG Workspace Excel, it should be like this:

    =RDP.HistoricalPricing($D$2,"TRDPRC_1","START:"&$A$2&" END:"&$C$2&" INTERVAL:PT1M SOURCE:RFV",,)
    image.png
  • Settlements
    Settlements Newcomer

    Thank you Jirapongse, this has worked great!

    Obviously this needs a start and end time for the formula to work and below is what I've manage to get to work

    =RDP.HistoricalPricing($E$2,"BID","START:"&$F$2&" END:"&$G$2&" INTERVAL:PT1M SOURCE:RFV TIMEZONE:UTC",,)

    I've added the timezone as I was unable to reconcile the figures at first and this made it easier.

    But, is there a way to only use 1 time and have that be a range and find the nearest bid and ask to that time instead of Start and End?

    Reason being, lets say 15:00:00 UTC (to have US and UK markets open) and then the formula finding the closest Ask or Bid in that range giving only 1 result for each?

    In that the range I would set would be start 18/09/2025 15:00:00 and End 15:00:00 but not all asset would have a BID/ASK in that 1 min period and sometimes give back a 'NA'

    For example, if I use a bunch of OTC stocks - I will sometimes get a BID and not an ASK and Vice versa - I need to increase the interval to 5 mins to get a result - Which is fine but I wanted to know if there was a better way.

    E.g. RIC - KNRRY.PQ / VSTTF.PQ

    Many thanks!

  • Jirapongse
    Jirapongse ✭✭✭✭✭

    @Settlements

    RDP.HistoricalPricing is used to retrieve time series data within a specified range and interval. If it returns data outside the defined parameters, this may indicate a potential issue with the function.

    However, please contact the Workspace Excel support team directly via LSEG Support to verify or enhance its behavior.

Categories