IB & Excel: No "Today's OPEN" via DDE -> ActiveX?

Hi

Interactive Brokers (IB) API question: Excel's VBA and ActiveX.

It is confirmed by IB that there is no (Today's) OPEN available via DDE for Excel. I do not have to express how ridiculous this is, not to have such fundamental trading information available in any API!

As it is available via ActiveX, I would like to ask any of my fellow trading colleagues if one of you could explain to me what exactly to do to make ActiveX work in Excel via VBA. At least, how to get the OPEN data automatically into my sheet (as soon as it exists). This would be crucial for my Gap play.

I managed to build a Visual Basic mini application (by copying it from a sample video from somebody else), but I cannot take that over 1:1 into Excel's VBA. I don’t know how the “Private Sub xxx yyy …” line has to look like in Excel, and also the commands seem to differ from VB where it is AxTws1.xxx, but not so in Excel’s VBA – at least I think so.

I also would like to avoid (another) third party application to make this available, to understand the API and ActiveX (in Excel's VBA) and also to avoid yet another third (or fourth or fifth or …) party software.

So anyone who could post some needed lines or make a simple sample Excel sheet available would be not only helping me, but many more desperate traders as I run into a lot of postings here and there all suffering from the non-available OPEN via DDE.

Thanks a lot!

 

I can't help you Bill but I do agree with you. Not having the open price in the Excel API really is annoying. I actually subscribed to Yahoo Real Time Data and imported in my spreadsheet via Web Query. That seems to work for me but I shouldn't have to do that because it would seem to be so easy to put the open in the API.

eric

 

First of all you need ActiveX component (Tws.ocx).
Then make it available for Excel VB (go to VB, then in menu use Tools/References... and there mark Tws ActiveX control module with checkbox, use Browse button and browse to the Tws.ocx module if you do not see such component in list of Available references already).

Then declare variable of type Tws in the module of some Sheet (do not use distinct Module, use module associated with some Sheet of your Workbook)

Code:

Dim WithEvents t As Tws

then you will have object t available from dropdown box above the code window, choose t there and in the dropdown to the right choose historicalData event. This event will receive information (including Open price) upon your request (t.reqHistoricalData)

then initialize variable with something like

Code:

Sub init()
    Set t = New Tws
    DoEvents
    t.Connect "", 7497, 0 'use demo account (port matters :)
End Sub

and then make the request for data by calling t.reqHistoricalData with respective parameters (don't forget to use constantly incrementing request ids).
Actually i'd not recommend you to use their ActiveX, because it can miss some events. I had to give up the try to use its bid/ask data for automated trading via Excel because it lost events and bid/ask data feeding from Tws ActiveX could hang.

 

Why wouldn't the first trade detected after 9:30 be good enough?

 

@R0bert

Thank you very much for your input! I started to work with that but failed so far as I am just beginning to learn about VB, VBA, ActiveX, etc. My last programming experiences lays 20 years behind me by entering 10: Input X, 20: Print X in my C-64 ;-)

Maybe I can make some progress over the weekend. Any furhter hint of course is always welcome.

QuoteLink:

Meanwhile, as a temporary solution I subscribed to QuoteIn's QuoteLink which, btw, did not provide "Today's Open" either via IB or TDA, lol! I told them and they provided a new/corrected release (0.74.27) within a couple of hours (great gyus). Now I get the Open via IB and TDA.

But I want to learn to use IB's API directly and I want to avoid yet another software utility I have to run. Maybe there will be the day when the owner of IB who just became a billion dollars richer due to the IPO will spend $500 on one of his porgrammers to implement an "exotic" trading information like 'Today's Open' in IB's API via DDE.

@stock777

If you can let me know how to programmtically put the open value of the first trade at/after 9:30 into an Excel cell using DDE I am more than glad to apply that - this exactly what I am trying to achieve. What I need is that soon as it exits it gets put into the cell as this calculates my entry quantitty including my stop and exit. My problem is, that sometims the Internet connection is not perfect (I am traveling, often have to use satellite Internet), and if I do not receive data for 1 to 2 minutes e.g. during the open I still have to (programmatically) get the open value.