How to gather 5 seconds bars into 5 min bars from IB's API Excel

[luiscristhian97]

Hello,

I recently installed the IB's Excel API with VB programming language.

The strategy I'm trying to develop looks for the real-time 5 min bars for entry. So the thing to do is to accumulate these 5-seconds bars about 60 times to come up with a real-time 5 min bar, without losing the real-time activity in the bar.

I found some material about this topic in previous threads, but since I'm a beginner in programming, it would be very helpful to have your insights or some sample code I can build from.

Hopefully, I made the idea understandable for you guys

Many thanks for your support

 

[tommcginnis]

I presume by "bars" you mean "candles"? So, Open High Low Close?

1) I would make it work in Excel, *and*then* simplify what you need in VBasic.

2) I would build a candle from a 'landing area.' {easiest}

3) {not hard} I would build a landing area from a data-writer
-- like, on 1-sec, IF(ISVALUE(Cell);value(Cell);N/A):
Open = first_value
High = @MAX(data-writer1..data-writerN)
Low = @MIN(data-writer1..data-writerN)
Close = last_value

{and now it get interesting...}
4) I would build a data-writer -- a thousand different ways, depending on how much data you wish to refresh (BID, ASK, MID, LAST*, VOLUME, OPEN_INTEREST, BIDv, ASKv, LASTv, ____?)
but use of nested IFs that look at whether --
-- a prior cell has been "printed" before changing from N/A to that value.
-- a prior time slot has been completed (for example, the old "End_of_Line"= "Y") idea.
and then you drop down to print the next line (row) of values...

Just spit-ballin'. Thanks for letting me play.

 

[luiscristhian97]

@tommcginnis
Thanks for your reply Tom. It gave me some insight.

So, the IB Excel API gives me the real-time 'OPEN, HIGH, LOW, CLOSE, TIMESPAN' of the security over a 5 second period (5 sec bar). Then it resets for another 5 sec period.

I was thinking about your option 2, which is [if I'm not mistaking], plotting the 5 sec data in different rows as they come in, and then simply accumulate them 60 times to make a 5 min bar

 

[tommcginnis]

@tommcginnis
Thanks for your reply Tom. It gave me some insight.

So, the IB Excel API gives me the real-time 'OPEN, HIGH, LOW, CLOSE, TIMESPAN' of the security over a 5 second period (5 sec bar). Then it resets for another 5 sec period.

I was thinking about your option 2, which is [if I'm not mistaking], plotting the 5 sec data in different rows as they come in, and then simply accumulate them 60 times to make a 5 min bar

In thinking about it just now, *your* comment suggests to me a possibly easy way to do it:
divide the data-writing into two steps:
• have the API write the full row, and then generate a signal to a "row-writer".
• upon seeing that signal (like, "EOL"="Y"), the "row-writer" would copy ALL values to the first line of the "data-writer" {parking lot!} as described earlier, and then
• increment the row-writer to Row-2.
• The API, in the meantime, is free to erase the row-writer, since you've just re-written the data in the data-writer/parking-lot/accumulator.

"Bingo!"
{and then you have to spit like Strother Martin in Butch Cassidy & The Sundance Kid}

 

[onesmith]

Hello,

I recently installed the IB's Excel API with VB programming language.

The strategy I'm trying to develop looks for the real-time 5 min bars for entry. So the thing to do is to accumulate these 5-seconds bars about 60 times to come up with a real-time 5 min bar, without losing the real-time activity in the bar.

I found some material about this topic in previous threads, but since I'm a beginner in programming, it would be very helpful to have your insights or some sample code I can build from.

Hopefully, I made the idea understandable for you guys

Many thanks for your support

convert time (t) into ttm which is (time to minutes) past midnight so for instance

1am becomes 60,
2am=120,
3am=180 (mins past midnight) ect.

then use various combos of integer portion and fractal portion
of the value returned by dividing ttm by 5

using the function mod(ttm,5);

 

[tommcginnis]

convert time (t) into ttm which is (time to minutes) past midnight so for instance

1am becomes 60,
2am=120,
3am=180 (mins past midnight) ect.

then use various combos of integer portion and fractal portion
of the value returned by dividing ttm by 5

using the function mod(ttm,5);

Given TWS (and the API's) issues with time and data accumulation in specific bits, having an outside imposition of time might really avoid a good number of problems.