TWSDDE Excel

[rols]

Have been playing around with the IB TWSDDE.xls and can get live data and historical data download as per the pdf instructions.

Does anybody have the macro or the add on for creating new bars, ie autoupdating OHLC values for say 15 min bars?

I have searched the IB TWSapi forum and the yahoo group and cannot find the solution.

Any help would be much appreciated.

 

[rols]

In case any other is trying to do the above there is a solution!

Here it is.....

 

[Trader333]

It is always useful to have something like this so thanks for the attachment. How did you get the answer in the end ?


Paul

 

[rols]

It is always useful to have something like this so thanks for the attachment. How did you get the answer in the end ?


Paul

This xl worksheet was posted on the yahoo twsapi forum but was originally wrongly coded and I reexamined it reading the posts and worked out how to use it.

The IB api forum is useful too.

The IBexelbeginnersapi.pdf is useful but amazingly doesn't tell you how to gather real time bars, in fact there is very little info anywhere and it to me it seems a crucial element for excel number crunching especially if you are using dynamic fibs etc.

Next is the ordering part!

 

[Trader333]

The IB api forum is useful too

Yes I have previously used it quite extensively.


Paul

 

[Spam Man]

The provision of real-time bars by the TWS API is a new thing, as I understand it, since build 9.2 - I've always used solutions similar to the one you posted Rols, which tracks the live price and constructs bars purely from that. The problem with your sheet is that it looks at the price every second to work out bar highs + lows, meaning you could miss a high or low anywhere within that second. Even with more elegant/faster solutions, the fact that IB provide aggregated data via DDE several times a second, rather than sending every tick, still means you could miss a high or low tick, particularly at busy times.

Anyway, I think the facility now exists to subscribe to real-time bars at a frequency of five seconds. If you combine this with your real-time feed and it all works perfectly then you should never miss anything. There's some Java examples for doing this on the IB website, but as far as I can see nothing for DDE, though I haven't searched very hard. It's something I plan to look at in the next few weeks, so I'll post again when I found out if/how this can be used in Excel - in the meantime if anyone else is further down the line than me then I'd like to hear about it.

By the way Rols, welcome to the terrifying world of Excel API nerdishness. There is no going back.

 

[rols]

The provision of real-time bars by the TWS API is a new thing, as I understand it, since build 9.2 - I've always used solutions similar to the one you posted Rols, which tracks the live price and constructs bars purely from that. The problem with your sheet is that it looks at the price every second to work out bar highs + lows, meaning you could miss a high or low anywhere within that second. Even with more elegant/faster solutions, the fact that IB provide aggregated data via DDE several times a second, rather than sending every tick, still means you could miss a high or low tick, particularly at busy times.

Anyway, I think the facility now exists to subscribe to real-time bars at a frequency of five seconds. If you combine this with your real-time feed and it all works perfectly then you should never miss anything. There's some Java examples for doing this on the IB website, but as far as I can see nothing for DDE, though I haven't searched very hard. It's something I plan to look at in the next few weeks, so I'll post again when I found out if/how this can be used in Excel - in the meantime if anyone else is further down the line than me then I'd like to hear about it.

By the way Rols, welcome to the terrifying world of Excel API nerdishness. There is no going back.

The api release notes say this is not available for dde.

I know from my previous experience of working for three years with an excellent programmer that the world of tick data and historical data is a black hole of unreality and despair. The api forums spend much time discussing the quality of data and it all seems very fuzzy to me. I suspect if one wanted to autotrade and scalp (which is not my intention) then it would be worth paying for the best feed available.

Good post below form Max on ET - especially the last line!

Quote from maxpi:

I wrestled with the data feed and filtering problem for awhile. I concluded that a good strat can run with a lot of bad ticks and the best way to filter them is right in the strategy, not pre- filtering ahead of the strategy. If you are worried about getting an entry due to a bad tick put in the requirement that you see two ticks at that level before the strat can fire off something. That slows things by an imperceptible amount, the time it takes to check for a second tick. If you are concerned about bad ticks affecting an indicator, build psudo bars in arrays in your strategy with the same two tick requirement for a tick to go into the array. Generate your indicators from those if it won't eat up too many cpu cycles, and with modern computers it is hard to use up the cpu available cycles in a lot of instances imo... indicators that use high and low values are way more likely to be affected by a bad tick than those using closes simply due to the fact that the chances of a bad tick on the close are small compared to anytime during the bar.

Good luck with all that stuff, automation development drove me to manual trading!!

Max

Found this site recently
http://www.automatedtrader.net/articles.xhtm

Perhaps attempting to autotrade with Twsapi dde would be a bit like carrying out brain surgery with a penknife.

 

[Spam Man]

The api release notes say this is not available for dde.

Must have missed it. Do you have a link? I was looking here http://www.interactivebrokers.com/en/software/apiReleaseNotes/api9.2.php?ib_entity=uk, and having glanced at that again I would have assumed that it is just support for 5 second bars that has been added, and they can be subscribed to in the same way that you subscribe to any historical data. Thanks.

Perhaps attempting to autotrade with Twsapi dde would be a bit like carrying out brain surgery with a penknife.

As with most things trading-related, there's no shortage of people to tell you it's impossible. I'd agree that if the odd bad tick screws your system up then you've got worse problems than bad data, but I've been quite happily auto-trading with TWS API for the best part of two years now (I use IQFeed as a back up but it's usually slower when the market gets fast). There is a lot of work involved, and it certainly isn't a short cut.

 

[Trader333]

I'd agree that if the odd bad tick screws your system up then you've got worse problems than bad data

I agree, if it is that critical then something else is wrong with the strategy.

I use IQFeed as a back up but it's usually slower when the market gets fast

That is something that I have also noticed with IQ. For news trading on FX, IB prices react almost instantly and in many ways it gets me into a trade more quickly than I know others are being able to.


Paul

 

[rols]

Must have missed it. Do you have a link? I was looking here http://www.interactivebrokers.com/en/software/apiReleaseNotes/api9.2.php?ib_entity=uk, and having glanced at that again I would have assumed that it is just support for 5 second bars that has been added, and they can be subscribed to in the same way that you subscribe to any historical data. Thanks.
/QUOTE]

http://individuals.interactivebrokers.com/en/software/apiReleaseNotes/apiBetanotes.php?ib_entity=llc

Sorry my mistake - there is no dde excel for snapshot data..

Have you any opinion on TWS link?

http://www.trade-commander.org/twslink/twslink.htm

 

[A Dashing Blade]

Hmmmmmm, that's a frighteningly fragile bit of XL/vba coding.
you guys know it's possible to fire off a macro every time (or every nth time) a dde link is updated?

 

[rols]

Hmmmmmm, that's a frighteningly fragile bit of XL/vba coding.
you guys know it's possible to fire off a macro every time (or every nth time) a dde link is updated?

You're right.

It's next to useless really. As soon as you open another worksheet it locks up or disconnects.

I find a similar thing with the TWSdde excel doc - very flakey.

 

[Spam Man]

Rols, I sense your heart isn't really in this. If you are serious about auto-trading you'll need to get intimately acquainted with the concept of fixing things which don't work first time.