I have been working on the EXCEL DDE sample worksheet and works fine and now I would to upgrade to ActiveX instead of DDE as I heard it is more robust but I found the refresh rate of ActiveX is even slower than the one in the DDE connection. Is there a way to change the smallest default value from "1 second to smaller value in order to speed up the refresh rate? the refresh rate of DDE can down to several milliseconds. Much appreciate if anyone could help me. Thanks
Don't use either
ActiveX, go with the excel
RTD server api.
It's the basis behind the
Bloomberg BDP plugin and we use it at work to push real time data to many spreadsheets.. It's now the recommended way to push data into a spreadsheet and it has a built in refresh rate parameter which can be any millisecond interval, or immediate if you want data pushed as fast as possible.
To answer the question about building an RTD server, follow this tutorial, its pretty trivial to do if you can write C#. However you'll need IB to give you an api to programatically get the quotes you want to populate the RTD server.
Long story short you shouldn't be using either ActiveX or DDE to push quotes to excel as this is antiquated technology.
In VBA Application.RTD.ThrottleInterval controls the refresh rate of RTD functions in Excel. Note that a value that is too small may take away too much CPU resource and makes Excel slow to respond. A nicer solution is have some in memory object/process to handle data update and print it out to Excel.