For Excel Aficionados: Solution to "2 Second" Update bug in RTD link in Excel

[Giraffe]

Anyone trying the RTD link in NSE NOW/NEST Trader to send data to MS Excel might have noticed this bug (not really a bug, just a weird default setting in Excel).....

"The quotes in NSE NOW market watch might update more than once per second, but the quotes in MS Excel updates after a fixed interval of 2 second"

Finally after searching for many days, I found the solution to this trouble, and hence sharing here as a separate thread. Many might know already, but for those like me who get struck due to this "weird" default setting of MS Excel, here it is:

In Excel, go to the Visual Basic Editor (by pressing ALT+F11 or clicking Visual Basic Editor from the Macro menu (Tools menu)).

In the Immediate window (press CTRL+G or click Immediate Window on the View menu), type this code:

Application.RTD.ThrottleInterval = 0

Make sure your cursor is on the line that you just typed, and then press ENTER.

To verify that it is set correctly, type this line of if code in the Immediate window:
? Application.RTD.ThrottleInterval

If you put your cursor at the end of this line and press ENTER, it should display '0'. Then you know that your throttle interval is set correctly.

For details check this link (I found it here only):
http://msdn.microsoft.com/en-us/lib...ce.10).aspx#odc_xlrtdfaq_howconfigrtdthrottle

 

[carnot11]

Hello,

Below from the link -

If the RTD throttle interval is set to zero, Excel checks for updates every chance it gets.

If the RTD throttle interval is set to something greater than zero, Excel waits at least that number of milliseconds between checks for updates.

Excel has the notion of a throttle for RTD. By default this throttle is set at 2,000 milliseconds (two seconds). What this means is that Excel only checks to see if it has been notified of an update at most once every two seconds. If Excel is busy, it may not check it for longer than two seconds, but if is not busy, it basically checks for updates every two seconds. This throttle interval can be modified.

The throttle interval is set very high. If the throttle interval is set very high, Excel doesn't check for updates for a long time. By default the throttle interval is set to 2,000 milliseconds.

When Excel is in edit mode, it does not check for updates. A cell is being edited,
Excel is busy. If Excel is showing a modal dialog box or is in the middle of a calculation, it does not check for updates. The Excel calculation mode is set to manual. In manual mode you don't see updates until a calculation is triggered

Caution - If updates come in so frequently that Excel is continuously updating values and doing calculations, Excel might end up in a state where it never gives the user a chance to do anything, effectively getting in a hung state. If this happens, set the Excel throttle interval higher.

Hence, I understand a value of 1000 milliseconds (1 sec) should be more than enough.

Enjoy,
CArnot

 

[Giraffe]

Yup could be, but in case someone needs every tick update, then interval=0 is must.

I am running it with this setting, haven't noted any problem so far. Running some pretty complicated things, but all quotes are updated decently. Excel always finds time to check for updates as most codes are executed very quickly...
I earlier had settings like 100, then 50 then 25.....then finally tested 0. Didn't notice any difference with 50 or 25 also, as they too would be checking 20 or 40 times per second respectively. But then made it zero as it was also working good.

If someone wants tick by tick accuracy, inteval=0 is needed. Moreover, nowadays, the processors and RAM is pretty good, and don't think that would be a limiting factor.

 

[Giraffe]

Hello,

Caution - If updates come in so frequently that Excel is continuously updating values and doing calculations, Excel might end up in a state where it never gives the user a chance to do anything, effectively getting in a hung state. If this happens, set the Excel throttle interval higher.

Hence, I understand a value of 1000 milliseconds (1 sec) should be more than enough.

Enjoy,
CArnot

But excel stops updating the quotes when user does anything. The moment an cell is activated for editing, my updates stop and resume when the editing is complete.

Maybe there would be some way around it, but
So one can't touch the sheet to edit during live market without missing few quotes.

Maybe there would be some way around it, but I don't know......Don't have any formal eduction in computers.......

 

[josh1]

When Does Excel Check for Updates?

Excel never really checks for updates— only gets updates after the RTD server has told Excel that it has updates. Excel only gets updates when it is in a "good state" and it waits at least the number of milliseconds specified by the RTD throttle interval. Excel does not get updates while a modal dialog box is displayed, while a cell is being edited, or while it is busy doing other things. Basically, when Excel is not totally swamped and it is in a state where cell values can change, it glances at its "RTD clock." If the throttle interval has passed, it calls the RefreshData method on each of the RTD servers that has notified it of an update by calling its UpdateNotify method.