HOW TO START USING RTD IN EXCEL?

Discussion in 'Beginner Traders' started by Andres, Dec 15, 2016.

  1. Andres

    Andres Active Member

    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
     
  2. garyw

    garyw Well-Known Member

    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:
    upload_2016-12-15_7-41-41.png
    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
     
    Wolfgang and MajorTom like this.
  3. Marcas

    Marcas Well-Known Member

    Wolfgang likes this.
  4. NoMoWork

    NoMoWork Member

    Here's an excellent youtube video on it.
     
  5. Andres

    Andres Active Member

    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 !
     
  6. NoMoWork

    NoMoWork Member

    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.
     

    Attached Files:

    • RTT.xlsx
      File size:
      12.7 KB
      Views:
      107
  7. status1

    status1 Well-Known Member

    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
     
  8. NoMoWork

    NoMoWork Member

    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))
     
  9. status1

    status1 Well-Known Member

    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
     
  10. garyw

    garyw Well-Known Member

    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.
    upload_2017-1-1_17-24-51.png
    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.
     
  11. status1

    status1 Well-Known Member

    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 ?
     
  12. garyw

    garyw Well-Known Member

    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:
    upload_2017-1-2_9-45-23.png
    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).
     
  13. status1

    status1 Well-Known Member

    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
     
  14. garyw

    garyw Well-Known Member

    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.
     

    Attached Files:

  15. Paul Demers

    Paul Demers Well-Known Member

    I build the OPRA codes in a cell and just reference it.
    RTH example.JPG
    Here is what is in the OPRA code cell.
    OPRA Code.JPG
    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.
    OPRA Code.JPG
    The table is coded to update to the current expiration's every time the sheet is opened.

    Hope this helps.
     

    Attached Files:

  16. garyw

    garyw Well-Known Member

    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)
    upload_2017-1-2_15-21-10.png
     
  17. Paul Demers

    Paul Demers Well-Known Member

    I am not using TOS for the Greek's and IV calculations, the spreadsheet calculates them.
     
  18. garyw

    garyw Well-Known Member

    Ah! I forgot! You mentioned that before. I also don't use TOS greeks. Pardon my senior moments, they are very common now!
     
  19. Paul Demers

    Paul Demers Well-Known Member

    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.
     
  20. status1

    status1 Well-Known Member

    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
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice