vb question (DDE Excel + IB's TWS)

Why isn't it updating the data?

In order to calculate Support and Resistance (D2 and D4) my sheet uses C2 and C4 (today's H & L) and I thought I told my vb code that if it saw any changes in those two cells within the time C3 (US market open, CET), it would have to change D2 and D4, automatically.

Well, it does so, but only if I paste different values on C2 and C4 - not if such values get changed by the source live data (from Interactive Brokers).

How do I make the sheet react to the data changing other than by pasting it each time on the cell?

I am attaching the file in my next message.

 

Here is the attached file about which I had my question.

 

I just found a post by H2O asking the same question:

http://www.elitetrader.com/vb/showthread.php?s=&threadid=60297&perpage=20&pagenumber=2

 

there was a thread a couple of weeks ago looking to capture a dde link value at a specific time.. - Don Bright i think had some comments (or questions...) - i can't remember the name of the thread but perhaps you can browse through the Software section - if you don't find it, i think you need VB code which monitors the time and copies and pastes your desired dde link values at a specified time or time range - i'd google for "excel clock vb code" and adapt whatever code you can find - post your code here if you're still having a problem - good luck.

 

Thanks for the help - I will look into it. My code is indeed here, once again this is the whole file:

 

It has been a while since I messed with this type of thing. DDE is not really "updating" the cell, the cell is just displaying the updating data. But that data can be used in formulas as you know.

So,
You can write some VBA to do a timer that will snap shot the data (copy/paste) every second or so.

And I believe you can write some crazy code if you turn iterations in excel. I did it a couple of years ago to calc a trailing stop using DDE data. Here is a link to my posts when I was confused.
http://elitetrader.com/vb/showthread.php?s=&threadid=24537&highlight=iterations

And I apologize if what I posted is completely inaccurate...

 

Just to add to what Strategy said, specifically if your vb is coded within the "Change" function, it will not be triggered by a DDE change. You can use the "calculate" function to capture MOST changes. Since you are looking for a specific cell's change in value, you will have to code a way to capture the intended cell. If you are super sensitive to all changes, the "calculate" function may be a problem since often, value changes in the DDE link will be skipped over by the "calculate" function. In other words, DDE changes are not queued... If a DDE value change occurs while vb "calculate" code is being evaluated, the DDE change will simply be skipped by the "calculate" function. For most, this does not affect their logic. For me it did, and thus I had to resort to socket connections whose changes CAN be queued, in other words, all changes can be processed...

REgards,
MAK

 

DDE will not work to capture every change. You will have to use a polling method to monitor the cell and will miss some price changes.

You might want to look at the IB activeX control. You can register it in excel and then the various IB events will be in the VBA editor just like the excel events are.

If it throws a price change event (or whatever they call it; I haven't looked at the documentation but this is approximately how it should work), then you can execute your code. Look at the VB sample that IB gives you and just cut and paste into excel.

 

The "Calculate" method freezes my excel sheet, and then I have to terminate the program, the "Change" method ignores the DDE changes, as you all pointed out.

It seems that I do need a polling method as most people here are pointing out, and by the way thank you all very much for this precious help.

Now, since I don't need fast polling, nor fast recording of changes, and since there will be only a few distant changes, if any, in today's pre-open high and low, can anyone please give me the few excel vb lines of code necessary to do this polling?

Or even more, could anyone modify the above file and post it back here? I am starting to flip...

 

Personally I would focus on figuring out why the sheet freezes using the Calculate method. Check for integers that are trying to take strings (or floats) as inputs, maybe explicitly name the sheets in your cell references if you are using more than one sheet, and look for any error messages when it freezes (either in the program itself, if it gives you the option to debug, or in your windows event logs if you're using windows.

If you can't get that to work, here is the code to run a sub routine at a particular time:


Public RunWhen As Double
Public Const cRunIntervalSeconds = 25 ' twentyfive seconds
Public Const cRunWhat = "dothis"


Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=True

End Sub

Sub StopTimer()

On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, schedule:=False

End Sub


Sub dothis()

Application.ScreenUpdating = False
'do stuff here
Application.ScreenUpdating = True

End Sub

Hope that helps a bit,

- The New Guy