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

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
dshalashova avatar image
1 ●0 ●0 ●1

Retrieving Brent Futures data at specific times - Excel Plug in

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

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikonpythonhistorical
icon clock
10 |1500

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

1 Answer

Upvotes
Accepted
Zhenya Kovalyov avatar image
4.6k ●26 ●7 ●22

@25d05fc5-db02-4b8d-b344-56f51747faa6 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


screen-shot-2018-09-19-at-131837.png (150.6 KiB)
samplexlsx.zip (53.7 KiB)
icon clock
10 |1500

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

Click below to post an Idea Post Idea