Getting data from TWS whilst VBA running.

W

Wooden Horse

I'm trying to set up some alerts using Excel's DDE to fetch data from IB's TWS.

The problem I'm getting is that the data from IB does not update whilst the VBA code is running, it only updates when the code has finished. As the VBA code is constantly running I never get fresh data.

I've successfully integrated IB's requestMarketData into my code.

Can anyone help?

:confused:
 
Quote from ColinRiche:

is there some reason why you dont want to use a 3rd party front end ?
More...

I can't find any that do exactly what I want. I have written a huge amount of analysis software using VBA. I like it because I can completely control the output. Thus it would be nice to interface this to a trading platform without having to learn a new programming language, especially one that's application specific.

I have tried using Metatrader but I've found that code that works perfectly in sim doesn't behave the same way when trading a demo account.

Many thanks for your reply.
 
Quote from libertine:

Does your VBA code need to run constantly? Why not just trigger it when necessary?
More...

Very good question. No it doesn't need to run constantly, only needs to update every minute. Unfortunately I don't know how to automatically trigger it and then get updated data. If I could do that it would certainly solve the problem.

I've tried using the OnTime event but couldn't get it to update.

Have you any suggestions?

Many thanks for your reply.
 
There is no reason why Ontime should not help to solve the problem. The idea is that you call all of your code from a small routine that itself is triggered from the Ontime event, say every minute. The DDE should update in between the calls.

Two thoughts though - firstly - maybe you need to refresh the DDE link in some way in between calls to your code. Secondly, I hope you are using Doevents. This function makes VBA hand control back to, well, anything that wants to use resources. I would not be surprised if the DDE does not spring to life if you put a few Doevents inside any tight loops you have in your code.
 
OnTime should solve your problem. I guess you are not using it correctly.

Experiment with this, and you should get the picture :

Sub OnTimeTest()

RunAgain = MsgBox("Run again in 15 seconds?", vbYesNo)

If RunAgain = 6 Then
Application.OnTime Now + TimeValue("00:00:15"), "OnTimeTest"
End If

End Sub

Clicking Yes on the msgbox will run the routine again in 15 seconds, during which time Excel is free to do other stuff including continually updating DDE links.
 
Libertine and Swandro many thanks for your replies, very much appreciated.

Swandro, after reading your post I tried playing around with OnTime and discovered that I wasn't using it correctly (you were right Libertine).

I think I've sorted out the problem now. I hadn't come across DoEvents before - thanks for that. I'll experiment with it more when I have time.

Thanks for the code Libertine, it demonstrated it well.:)
 
You must log in or register to reply here.
Back
Top