[rabcnesbit]

(Hope I have posted this in the right category)


Interactive Broker's TwsActiveX API spreadsheet provides realtime bars as 5-second bars. I would like to have this as 3-minute bars and 5-minute bars. Has anybody found a a way around this, or got any suggestions? I have a reasonable knowledge of excel, very limited knowledge of VBA, and no clue at all about VB. Many thanks.

Dan

[uexkuell]

If you know how to do things in Excel just take chunks of 36 (=3x12) realtime bars to produce 3 minute bars resp. 60 (=5x12) for 5 minute bars.
Compute for these chunks e.g. Mininum resulting in Low, Maximum resulting in High and so forth.
Same way as it would be done in a proper programming language.


Here comes the free additional hint that probably nobody wants to hear:
- IB's realtime data is much better than what people usually say. Bars not needed.
- Aggregating data (building chunks of longer timeframes) should be absolutely avoided. It results in loosing important information

[rabcnesbit]

Originally Posted by uexkuell »

If you know how to do things in Excel just take chunks of 36 (=3x12) realtime bars to produce 3 minute bars resp. 60 (=5x12) for 5 minute bars.
Compute for these chunks e.g. Mininum resulting in Low, Maximum resulting in High and so forth.
Same way as it would be done in a proper programming language.


Here comes the free additional hint that probably nobody wants to hear:
- IB's realtime data is much better than what people usually say. Bars not needed.
- Aggregating data (building chunks of longer timeframes) should be absolutely avoided. It results in loosing important information

Thanks uexkuell for your reply. Could you explain further please? The real time bars data is displayed in excel in the following columns: Open, High, Low, Close, Vol, WAP, Count. So, are you saying that I create a variable for each and say store 60 x 5 second figure for each - Open, High, Low, Close etc. Now, how do I 'compute' say the Open for the 60 x 5-second bars (the 5-minute bar)? I don't think it is a case of adding them up and dividing by 60 or something, is it? Thanks

[uexkuell]

Originally Posted by rabcnesbit »

Now, how do I 'compute' say the Open for the 60 x 5-second bars (the 5-minute bar)?

Now you got me on the one I didn't explain.

Fortunately this is the easiest:
Open is just the Open of the first bar.
Close is of cause the Close of the last bar.
(Low is minimum of all Lows, High is maximum of all Highs).

If it's still not clear you can post your worksheet and I can see if I can do something.

[rabcnesbit]

Originally Posted by uexkuell »

Now you got me on the one I didn't explain.

Fortunately this is the easiest:
Open is just the Open of the first bar.
Close is of cause the Close of the last bar.
(Low is minimum of all Lows, High is maximum of all Highs).

If it's still not clear you can post your worksheet and I can see if I can do something.

Thanks uexkuell. I did manage to figure out the above as well. Just to check, will the volume be the total volume of all the bars, and count (the number of trades) be the total count of all the bars? The only one that I haven't got a clue about is the WAP (weighted average price) i.e. how would I compute the WAP for say the 5-minute bar from the 60 x 5-second bars?

Thanks