HI, I´m new intrading options. . I´ d read a lot about using rtd in excel. Can some explain how to start using rtd in excell or where I can learn how to do it ? thanks
1st, you must have TOS (I assume you do); To observe what is possible, and a good example, Open a Trade Tab with a SPX or your preferred underlying, then open the indicated drop down: And Export... -> To Microsoft Excel, then follow the prompt to paste into Excel This will fill your spreadsheet with RTD references, which you may observe as an example. --------------------------------- Most people using RTD, create their sheets in personalized ways. I use Excel as an interface between my Perl code and TOS, and don't manually create or modify the sheets relating to RTD. The RTD is created as needed in my case. ------------------------ There are some nuances about using RTD, regarding overloading TOS, but you should not have to worry about that initially. I hope this is of some help. Regards, Gary
Andres, you can start here: https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-1-of-4/ Simple, practical approach, step by step, with easy examples.
Thanks Guys for your links... they were very helpful. May I ask where I can find examples or "How To do" spreadsheets?. Basically I'm trying to make butterflys and follow the mark prices an calculate the p/L for the position so I understand how the movement in the spread price works in my p/L. Thanks again !
Here's an Excel spreadsheet I started to accomplish just that. It's still a work in progress, but you can get an idea of how it works by highlighting each cell. Of course you need to have ThinkorSwim running on your desktop so the spreadsheet can read the values it needs. Hope this helps.
I like that spreadsheet I was wondering if somehow the P/L open that is shown on the price slice could be calculated although I am not sure which one would be more accurate Right now the current price for this fly is 1.25 which is exactly the same as the entry price so the gain would be zero however on the price slice on the P/L open it shows a gain of $63 so I am not sure how that price is calculated Of course buying it today is not the same as buying it on Dec 16 I used on demand to go back to buying it on Dec 16 and assuming I could buy it at that time for $1.00 so today at 1.25 theoretically it should show a gain of $25 but the P/L open shows it as $37.68 which is closer to reality but it's not exactly the same I asked TOS about that and they said that they will try to find out but of course they never got back to me I am guessing they don't know unless they talk to the actual programmer or they want to keep it a secret I am guessing they have some kind of fudge factor in there that throws the price off I am trying to automate my daily data entry for the greeks and p/l for tracking my trades but I just can't get the same data that is shown on the price slice Of course exporting the data from the price slice to excel would be ideal but of course that is the one thing that is not exportable from TOS
The problem with the P/L in TOS is that it is only accurate for an original trade, as soon as you modify the trade, it's no longer applicable. The price on the spreadseet is taken from the Mark, or Mid price, which in itself is not always accurate when the bid / ask spread is really wide, so it's an estimate at best. You can derive the greeks for the individual strikes from the option code shown on the spreadsheet, for example: on my spreadsheet the option code is in cell B11, so the formula would be RTD("TOS.RTD",,"DELTA",UPPER(B11))
I understand but even the if it was the original trade I still can't match it exactly to the price slices I am guessing this is probably due to rounding errors This probably not make much difference on large numbers like the vega but on small numbers it makes quite a bit of difference Using your formula I am getting -0.47, -0.32 and -0.18 I understand that these numbers have to be converted by multiplying them by 100 and making the -0.32 positive but if I look at the delta numbers individually on the price slice I am getting 47.03, 32.40 and 17.57 respectively so as you can see the 32.40 was rounded down while the 17.57 was rounded up and the 47.03 is only a little bit off even just increasing the contracts to 2 the 32.40 is showing at 64.81 so even the figure on the price slice is rounded of from a longer number So adding up the delta numbers shown using the RTD I would get (.47+.18-2x.32) x 100 = 1 While adding up the price slices values 47.03+17.57- 2x32.40 = 0.20 which is what the price slice shows as a butterfly So there is quite a difference between 1 and 0.20 And the Gamma is much worse because it's so small so you might as well not even use it because all you get is zeros
status1: I am guessing a bit, so take with grain of salt. If you are trying to make the TOS Price Slices match something else, make sure you have the Commission and the Date set identically. Also, if those prices are real, you can get additional precision in the RTD feed by passing multiplier in the RTD command, then account for the multiplication in the spreadsheet. example: =RTD("tos.rtd",,"MARK","100*.SPXW170228P2235") This may help avoid some rounding errors prior to the RTD delivery. Just FYI, I may not be observing same issue as you.
Thanks garyw I am not touching the commissions so that is not an issue even if I did the commissions have no effect on the price slices only on the expiration line The date is set for the same day I am checking the prices at EOD I am trying to use the price slices as a tracking device to track my trades I am not trying to match anything I am just trying to find out how else I can get the price slices since exporting them to excel does not work So the only way to do it so far is to get the individual data for each option and make my own calculation Whether I get the data already multiplied before I make the calculation or I get the data and them multiply it I get the same number which is pretty close although not exact 0.21 vs 0.20 for delta but I can live with that Surprisingly your formula works better for GAMMA when I get the data already multiplied I guess because once you get such a small number I guess excell rounds it down to zero and than multiplying that by 100 is still zero So thanks for giving me fresh ideas Just one thing in your formula I would not need to multiply the MARK since that is already correct this would be just for the delta, gamma,theta and vega Now if I could get the p/l to be that close that would be great One other thing when I was trying to use it with the cell reference it did not work but maybe I missed something in my formula so I will keep working on that You said "I may not be observing same issue as you" Are you saying that your p/l on the price slice matches your mark or you meant something else by that ?
status1: I do NOT use the TOS Price Slice PnL. But was curious, so poked around a bit. If you click on the P/L Open value, you should see something similar to: My guess is the "Sim Price" you see here accounts for the difference. I would GUESS, that during trading hours, the price you observe for MID/MARK may more closely match what TOS would show here as the "Sim Price" as there would be no reason for them to incorporate IV (or Time) in computing price! (This assumes, they don't play games with the Mid/Mark). Note that if you (as a test only), alter the Date, this value changes, providing some hint to what they may be doing behind the scenes! -- As for that RTD formula: Since I only access BID/ASK prices, and integer values, I do not use it. (I do my own Greeks) -- If you use TOS greeks, you may wish to tweak your RTD references similar to this to get better precision: =RTD("tos.rtd",,<nugget>,"100*<opra>")/100 Where <nugget> is your desired item, such as DELTA, IMPL_VOL, etc. Note: TOS has been known to alter their handling of IV without disclosing, so this may be a moving target (one reason I stay away from it).
You are correct the SIM price is probably what they use to figure out the p/l I entered that number as a closing trade and I got pretty close to the p/l shown on the price slice with just a minor difference because I cannot enter the rest of the fractions in the sim price So of course the next question would be how do I get the "sim price" ? About the formula I still can't get it to work with the cell reference I guess when I multiply by 100 the cell reference so I am not has a different meaning so I am not sure if it needs "" or () I am still fairly new to this RTD funcrion to know all the tricks In your formula I am replacing <nuget> with "DELTA" or a cell reference ? Same question for the opra do I enter the option code or a cell reference ? One thing I am not sure I understand why you are dividing the result by 100
Here is a simple sample of that RTD reference for adding precision to GAMMA (change to whatever you like). Let me know if you still have issues.
I build the OPRA codes in a cell and just reference it. Here is what is in the OPRA code cell. Here is the lookup table that has the dates for the OPRA code. It is crude but I am too lazy to fix it as it works. The table is coded to update to the current expiration's every time the sheet is opened. Hope this helps.
Paul: Curious: are you using the extra decimal places in your settings, or are you using the RTD multiplier for getting the precision (or perhaps both)? (I'm guessing the former)
Ah! I forgot! You mentioned that before. I also don't use TOS greeks. Pardon my senior moments, they are very common now!
I have the same senior moments. One thing to note with using mark prices. One of the things I noticed when TOS changes their risk management is that they will change the mark prices on the puts to reflect the ask price. I am in need to rebuild the spreadsheet to get the bid/ask prices and calculate the mark price right on the spreadsheet to get accurate Greeks.
To garyw Thanks but it's still not working I am not sure if maybe it's not working because I am using excel 2003 so I used office libre to open your file but for some reason it doesn't like it when the option code is multiplied So in the cell next to modified OPRA in the cell it shows 1000*.SPXW170228P2185 while in the formula bar it shows =PrecisionMultiplier&"*"&OPRA so if I copy the formula to excel I get #NAME? The earlier formula you gave me works fine as is=RTD("tos.rtd",,"MARK","100*.SPXW170228P2235") I just changed the MARK to GAMMA It's just when I try to use a cell reference instead of the option code I get an error Sorry about the font size change It changed when I pasted from excel and I don't see any font sizes to change it back