tos.rtd spreadsheet

Mark17

Member
Hi all,

I haven't done this in a while but I'm trying to make a tos.rtd spreadsheet for my positions.

I have TOS open and I'm inputting proper formulae in the spreadsheet. At some point, though, it seems the connection freezes (?) because I start getting #VALUE! in the rtd cells. If I close out the spreadsheet and reopen, then they all get populated.

Does anyone know what's going on?

Incidentally, it's after hours now (4:56 PM ET). I started working on this around 4:10 PM ET.

Thanks!
 

garyw

Active member
I observe similar issues while creating/modifying RTD links. My solution is to create the links, then save, and re-open, as you seem to have found. If you do not modify the links, the links are reliable.
 

jim leahy

Member
with the rtd protocol, the server (thinkorswim in this case) only sends data to the client on value changes.
if the value doesn't change there's no transfer. this generally happens after hours and on weekends
when there's no data updates. reloading the spreadsheet will force an rtd update because it's new data.
that's the solution i use as well.
 

Mark17

Member
with the rtd protocol, the server (thinkorswim in this case) only sends data to the client on value changes.
if the value doesn't change there's no transfer. this generally happens after hours and on weekends
when there's no data updates. reloading the spreadsheet will force an rtd update because it's new data.
that's the solution i use as well.

Ahh... that makes sense.

I used to use Dan Harvey's Open Office spreadsheet and in there, I would periodically delete links because any link ever used would be saved and the number would grow and grow. I'm not sure if that actually hurt the performance of the spreadsheet or whether someone told me I should do that from time to time, etc. Is there any similar concern here with Excel?
 

status1

Active member
I have seen similar issues but only when updating a link The link stays the same I only change the strike price and sometimes not all the time I get the #VALUE! you mentioned so I have to close and open and it works after that

I started seeing a more weird behavior though lately
I have a cell with the RTD link to the VIX price and without changing anything it just comes up as N/A the next day I open the spreadsheet Taking a closer look as it opens it shows the vix price for a split second and than it changes to N/A
Sometimes opening the sheet for the second time it works Yesterday I tried it about 5 time in a row and it would not work so I had to save the sheet with the N/A and than the next time I opened it than it worked

About the performance
Opening the file and looking at the links should not have any performance issues
If you are doing any programing with vba like saving the data to another worksheet I have observed a slowdown
At the beginning of the year I start a new sheet so that brings back the performance as far as the transfers are concerned
 

jim leahy

Member
Ahh... that makes sense.

I used to use Dan Harvey's Open Office spreadsheet and in there, I would periodically delete links because any link ever used would be saved and the number would grow and grow. I'm not sure if that actually hurt the performance of the spreadsheet or whether someone told me I should do that from time to time, etc. Is there any similar concern here with Excel?
excel throttles updates to 2 seconds (i think), by default. every link you have will use some bandwidth but
if you're not using a huge number of links it's likely not going to be a problem. my largest spreadsheet only
has a 300-400 links and i don't see a problem. some people use thousands of links and that may cause
performance problems. you can change the throttle value in a registry setting or by setting a parameter in
the vba editor. neither is convenient, but not difficult. you can find instructions on how to do this on the web.
 

jim leahy

Member
I started seeing a more weird behavior though lately
I have a cell with the RTD link to the VIX price and without changing anything it just comes up as N/A the next day I open the spreadsheet Taking a closer look as it opens it shows the vix price for a split second and than it changes to N/A
Sometimes opening the sheet for the second time it works Yesterday I tried it about 5 time in a row and it would not work so I had to save the sheet with the N/A and than the next time I opened it than it worked

About the performance
Opening the file and looking at the links should not have any performance issues
If you are doing any programing with vba like saving the data to another worksheet I have observed a slowdown
At the beginning of the year I start a new sheet so that brings back the performance as far as the transfers are concerned
i haven't seen a problem with vix, but when using /vx futures i did see problems. sometime in the past it
seems thinkorswim changed the symbols for the futures by appending :XCBF to the symbol. without using
the addition it sometimes works, but i haven't seen ant problem after i updated my links with it.

when you transfer data to another cell in vba, it causes a reclaculation of all cells. you can prevent the recalc
by adding Application.ScreenUpdating=False before the transfer, and then set it to true after the transfer.
remember to set it to true when you've finished the transfer or it will prevent and further recalculations.
this solved the performance problem for me.
 

status1

Active member
I already have that screen updating in the vba but I think that is just so you don't see the screen flash as it is updating
Not sure why the cells need to be recalculated since I am not doing any calculations I just copy and paste to another workbook

I think the main thing is that I save the data daily and than transfer it to another worksheet so that it does not change the next day when the new data is coming in The thing is that it opens and closes the workbook for each account so it has to do that 8 times
I am sure it could be done more efficiently but this works for me
At the beginning of the year when there is small amount of data it works fast but by the end of the year it's noticeable slower but it's not enough to bother me It's just something I noticed

I also add a screenshot at the EOD with just a hyperlink to a folder where the screenshots are located so maybe that also adds to the data volume and slows down the opening and closing of the workbook but as I mentioned it's not a problem if it's just for one year
 

status1

Active member
It did it again today
I saw the vix price 27.95 as I opened the spreadsheet and than after about a second or less it changed to N/A
If I just close the spreadsheet and open it than it keeps doing it
I have to save the spreadsheet with the N/A and than after I open it again than it stays there

None of the other rtd links (delta theta vega gamma) are doing this only the VIX and it just started recently maybe a few months ago I observed it doing it randomly but at that time I could just close and open it and would work now it seems like it's doing it every day and I have to save the file in order to keep showing the VIX
 

garyw

Active member
FYI: If you wish more control over RTD, there are a couple settings I use and found to be useful.
1) "ThrottleInterval" (Default value is 2,000, with the units in milliseconds) To only refresh on your command, set to -1
2) "RefreshData' (Command use to force an update at your desired point in time)

For my observations on using this for many years, found that if the number of RTD links is kept below about 20K or 30K, and disabled ThrottleInterval (set to -1), then issuing a "RefreshData" command, then waiting 2 seconds, results in a reliable capture of the links. (I have a lot of memory and fairly powerful processing, so you may hit lower limits if your computing resources are lower) -- 40K RTD links caused problems with Excel and with TOS from my testing!

Some Perl statements illustrating how I use it:
$exc = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
Then:
Perl line for setting ThrottleInterval: $exc->Application->RTD->{'ThrottleInterval'}=-1;
Perl line for the RefreshData command for reference:" $exc->Application->RTD->RefreshData;

I expect a Python pkg has similar interface for those of us who want nothing to do with VBA. (any thing you can do with VBA you should be able to do via the package) -- I have not looked for a Python version, but would be surprised it it does not exist..
 

status1

Active member
Perhaps that may work for Mark17 since his issue was with #VALUE but I don't think that is going to work for me when it shows N/A
It has the correct value for a second or two and than it turns to N/A for no reason
 

status1

Active member
More oddball behavior
If I open excel first I see the yesterday's value for VIX and than when I open TOS the value updates to today's value but if I then close excel without saving than when I open again it turns into N/A
Than I just tried this today while it still shows N/A I close TOS and reopen it and than it updates to today's value

So basically in order to avoid this problem I have to open excel first and than TOS or just save it with N/A and reopen excel while TOS is open
I just checked my throttle interval and it's set to 2000
This is on the 64 bit windows 7 partition


Interestingly for some reason the throtle interval was set for 8000 on the 32 bit system partition and I don't remember having this problem but TOS is so slow on that system that it's virtually unusable anymore
 

status1

Active member
I just went back and checked on the 32 bit system it's not a problem so it looks like somehow it's related to the 64 bit system which I switched to around the beginning of the year

The mystery part is why it only affects the VIX because I have the RTD for the mark and all the other greeks and none of them have this problem It's only on the VIX
 

Mark17

Member
Perhaps that may work for Mark17 since his issue was with #VALUE but I don't think that is going to work for me when it shows N/A
It has the correct value for a second or two and than it turns to N/A for no reason

My spreadsheet has been performing well the last few days. Jim's comment about TOS only sending data to client on value changes helped--I think that's what was going on.

Hopefully I didn't just jinx myself in posting this!
 

status1

Active member
I found the problem with the VIX RTD

I was looking at VIX on my excel sheet and I see it going on and off between N/A and the price so I looked on tos and looks like the BID and ASK are N/A so since I was using the MARK there was no way to get a price for it so I switched it to LAST even though it's not as good as the mark at least it's working and gives me a ballpark figure just for reference
 

status1

Active member
Maybe I spoke too soon

I was watching my spreadsheet again and I still see it switching back and forth between a number value and N/A
I also checked on my 32 bit version and it is doing it on that too and also with the LAST price so it's not related to the system or if it's MARK or LAST
I am guessing it's just updating too slow on their system and I am getting N/A between the last value and the new value
I placed the excel sheet side by side with the TOS trade tab and watching when the VIX price is changing

It looks like while the old price is still on TOS the excel value changes to N/A and a few seconds after the new price shows up on TOS than the value changes from N/A to the new price than a few seconds later it changes to N/A and stays there until a new value shows up on TOS

None of the other RTD values behave this way only the VIX
 

garyw

Active member
Two thoughts:
1) Does the issue also occur with "BID" and "ASK" (as a substitute for "MARK")? --
2) Does the issue occur if you reduce the refresh rate to say 8 seconds? (to see if the issue is performance of something on you system or internet)
 

status1

Active member
I tried the BID and of course it shows N/A since it also shows N/A on TOS because the VIX is not traded

I changed the throttle interval to 10000 and I thought it worked because I saw 2 price changes but after that it happened again so I guess it could be a matter of timing or maybe something else unknown
 

garyw

Active member
My bad! I thought you were looking at tradeable items, not the VIX calculation result! (you used proper terminology, I failed to realize it)
It is not clear what interval is used for re-calculating the VIX value, however, the CBOE white paper indicates time is expressed "minutes" so any refresh interval less than 60 seconds may result in what you observe. {Note their time values "N..." are integers, not reals} So, IFF the calculation of the VIX value is done each 60-seconds, increasing the RTD to be 60 seconds or greater may address the issue, if this is the root cause. I do not know if TOS calculates it themselves, or receive the calculated value elsewhere (I would GUESS, they receive the value from another party).

Another solution is to calculate the VIX value yourself. The rub is you need to reference the necessary chains to implement, which may only be practical if you have them for other reasons.

PS: I THINK I recall the value being calculated much less frequently than 1 minute, but can't recall the source!
 
Last edited:

status1

Active member
I would guess they receive the value from somewhere else since using the LAST did not work and that is shown on TOS
So if it comes from somewhere else it's probably not synchronized with the TOS prices (just a guess)

I noticed that while the other prices did not change untill the next refresh the VIX did change to N/A so I am guessing that even if I set it to 60 seconds it could fall between a refresh cycle and still get N/A

The weird thing is that after I save the spreadsheet and open it again than it works Not sure if this is synchronized with something but after I do that all the values including the VIX change at the same time
 
Top