My client’s Excel sheet uses the RTD function to get stocks data from Schwab’s ThinkOrSwim.
Example: =RTD("tos.rtd", , "BID", "AAPL")
When the cell changes, a Worksheet_Calculate event copies the values to a table.
We discovered that ToS updates its info several times a second, but my macro isn’t capturing every one. It’s been more like one every few seconds.
I’m limited with the experimentation I can do, because I personally can’t get RTD to work. (But that’s another posting.) I have to send my versions of the workbook to my client and let him them himself. For this issue, my guess is that either:
- The changes come in only every few seconds, because the transfer can’t keep up with ToS’s speed.
- The changes come into the feed several times per second, but the Worksheet_Calculate event can’t trigger that fast.
- The worksheet calculates several times per second, but my macro can’t keep up with Worksheet_Calculate.
If the last point is, in fact, the problem, I could change the macro so that instead of pasting the rows one at a time, it saves them in batches to an array and pastes them.
Do we have reason to believe that the issue is one of the other points?
Very slow, but depends on your internet and whether the file is on cloud or local.