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

Settlements

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?

Many thanks!
James

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",,)

Settlements

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

@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.