Retrieving Brent Futures data at specific times - Excel Plug in

dshalashova

Hello, I am currently using the formula builder in excel to
pull Brent Futures historical data. I`m interested in a formula which would
generate prices for Brent Futures at specific times during the day, as opposed
to the closing price of each day.

I`m using the below formula where B1 is my product of
interest, B2 the start date, B3 the end date and I want basically B4 to be a
set time. (i.e. Brent Futures prices each day at 16:00).

=RHistory($B$1,".Timestamp;.Close","START:"&$B$2&"
END:"&$B$3&" TIMEZONE:LON
INTERVAL:1D",,"TSREPEAT:NO",B6)

Ideally the formula will refer to the time cell which I
could then change to retrive prices at a different time.

Thank you for your time

Best Regards,

Dasha

Zhenya Kovalyov

@dshalashova Hi Dasha,

The easiest way to achieve this that I can think of is this.

1. The 60 minute interval formula will give you 2000 data points =RHistory("LCOc1",{"TRDPRC_1.Timestamp","TRDPRC_1.Close"},"NBROWS:2000 INTERVAL:60M",,"TSREPEAT:NO CH:Fd")
2. Create a logical formula to mark the row with the 16:00:00 value: =IF(ROUND(A2-INT(A2), 7)=ROUND(TIME(16, 0,0),7), "X", "")
3. Filter by this flag

I also suggest that you should seek advise from your local Thomson Reuters support desk, as all other options I can think of involve the use of the APIs.

samplexlsx.zip