HOW TO START USING RTD IN EXCEL?

garyw

Active member
Gold Member
Paul: I do all my calcs from the BID/ASK prices. I have not noticed TOS messing those up yet! ;-)
I am deriving IV via my gradual understanding of Steve Speer's work.
I code in Perl (old school), and have the Perl code build the RTD spreadsheets on the fly as needed. I can't remember stuff, so have to code it if I need it to be correct.
 

status1

Well-known member
To Paul
do you have any issues with multiplying the reference cell containing the opra code
I know you probably are not using TOS to track the greeks but I was just wondering if that works for you as it's not working for me so I am not sure if I am doing it wrong or maybe it's not working because I am using excel 2003
Your code works as is for the MARK it's only when I try to use it for GAMMA where I have to multiply by 100 and if I multiply the opra code directly in the cell it's working but if I try to use a reference cell I am getting an error
 

Paul Demers

New member
I think the bid/ask prices will work what ever they do to calculate their modified greeks. I use VBA functions to create a raw Black Scholes IV. Just trying to keep it simple.
 

Paul Demers

New member
I use separate cells to do the multiplication. You may try to do the math after the RTD code instead of inside it.
example
=RTD(RTD,,$H$7,$V8) * 100
 

garyw

Active member
Gold 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


status1:
I just tested this on a different machine running Excel 2003, and it is working for me. -- sometimes TOS can get confused with RTD. If so, killing both TOS and Excel, then bringing each back up in sequence will resolve --- IFF your issue is a TOS RTD failure.
Also, an easier method may be to increase your precision to 5 digits in the TOS settings.
Sorry but I don't know what an "office libre" is!
BTW: The spreadsheet I posted labeled the references, so if you are not using that spreadsheet you will have to make modifications.
 

garyw

Active member
Gold Member
I use separate cells to do the multiplication. You may try to do the math after the RTD code instead of inside it.
example
=RTD(RTD,,$H$7,$V8) * 100

Paul: The multiplier within the RTD function informs TOS to multiply the value prior to sending. This multiplication occurs PRIOR to TOS's rounding to the "number of digits" prior to sending via RTD (If I understand correctly). So this helps address some round-off of precision.
 

Paul Demers

New member
I understand now why you do it. I never needed it calculating the greeks using the spreadsheet. I never liked the idea that TOS can change the way they calculate the greeks when ever they wanted so I never use them.
 

status1

Well-known member
I think I got it to work
I see that garyw has 2 & and 2 " symbols surrounding the * so I tried that and it seems to work I just used those symbols and a simple cell reference and it works It still has an issue with the names but I can work around that
Sure would have been nice if I could get the sim price
Maybe Tasty works will have something usable when they open up
 

tom

Well-known member
Administrator
Interested discussion... Let me ask you a "silly thing"... I am having problems with RTD and decimals for LAST PRICE, I am not having the 2 decimals on last price and getting numbers like 43955 on #AA instead of 43.95.
Anyone could tell me what I am doing wrong? Is there a RTD function to format decimals?
Thanks in advance

Posted by Panoramia
 

garyw

Active member
Gold Member
Can you post the RTD formula "exactly" so we can determine if that is the issue?
For example, if looking for last price of SPX, S/B -->=RTD("tos.rtd",,"LAST","SPX")<--
You may be trying multiplication on the TOS side via the RTD reference that may not receive consistent support by TOS.
 

status1

Well-known member
Try to export the trade tab to excel than click on the LAST and compare that to your formula
Are you trying to get the last price only or to calculate your p/l ?
As garyw suspected you may be trying to get the p/l or are doing some calculations so a 10 lot at 43.95 would be 43950 not sure where the other extra 5 is coming from unless you added something else in there
 

Neil

New member
I'm a bit late this discussion because I just got back from 2 weeks vacation (saw the eclipse in totality
:)
[/URL] ). Anyway, there was a Cap Disc Round Table on Mar 8 discussing the basics of TOS RTD and Excel (it's available in the Library -


It might help some of the traders watching this thread.
 

tom

Well-known member
Administrator
NOTES FROM THE ABOVE WEBINAR:

WEBINAR LINKS

WEBINAR NOTES
  • Mac Excel cannot do RTD.
  • Set display settings in TOS to 5 decimals places to minimize rounding errors.
  • F9 refreshes a spreadsheet
  • RTD.ThrottleInterval defaults to two seconds. If it is changed to -1 (manual), RTD will not update automatically.
USEFUL EXCEL FUNCTIONS
Example RTD format: =RTD("tos.rtd","tos.rtd","LAST", ".SPX170421P2400" )
=TEXT(Al,"00") will format an integer to two digits
=CONCATENATE("."&$C$41,TEXT($C$42,"yymmdd"),"P",C43) will convert any date to the correct OPRA format
 

status1

Well-known member
Hi Tom,
I was wondering if maybe it would be a good idea to make a separate forum just for excel
Ofcourse this post would be the first one to go in there

There are a few other that I can think of that could go in there like the Kevin Lee's video about using excel for options and the skew driver and any other discussions about using excel or any good tips or websites that someone may want to add

The software that Dan is using that he showed using excel in the recent Trading group looked very good
Too bad it was only manual entry

Just by coincidence a couple of weekends before that I started working on coming up with some way to import my trades into an excel spreadsheet
There are many programs that show how to keep a journal on stocks but very few if any about options

So I started with my Fidelity account which does not have any export features so I just used the pdf confirmation and selected to copy all the text and place it into an excel spreadsheet than using some macros I extracted all the information into a table format

So now I have 2 separate spreadsheets one for the opening and one for closing and I am working on combining the two into one which is going to be more difficult since I have not done it before so I am not sure exctly what is the best way of doing it
I know I have to do some kind of matching

If that works than I can do all my other accounts but I have to customize each one since they all have different formats but once I get them into a spreadsheet I can do it the same way to match the transactions

Ideally I could get it into a format like the Ninja trader that could be imported from a local file into the options trading log or any other trading log and hopefully also use it at the end of the year to make it into a format that can be used on the 8949 tax format for equities only but for now just baby steps one thing at a time
 

status1

Well-known member
I just got a reply from TJS about the question I sent them if there is a way to import from a local file and this was the response

"Thank you for your inquiry of the Trading Journal Spreadsheet TM.
  1. It may be possible if you are Coping and Pasting info from Column-to-Column, or if you were to design a source file that had all columns matched up with the columns in the TJS file. Then, you’ll want to make sure that you (only) use the Paste Special > Values function, so as not to compromise the TJS file with the source formatting.
2. Ninja trader is the only trader platform that has had any type of export file conducive to trade-tracking, where they use to offer an export file that had all of their Buys and Sells on one row of the file. If the export file has Buys and Sells on individual rows, then it becomes an absolute mess trying to import that data and matching up the individual transactions."

So the posibility exists I just have to come up with that format
 

JerseyJIm

New member
Gold Member
Using Excel .RTD functions, and am trying to retrieve values for /GC futures, but I think I need to enter the symbol as /GC:XXX, with XXX being the exchange. I think it is COMEX, how do I find out what abbreviation to use for it in the formula?

Thanks.
 

status1

Well-known member
The easiest way to find the symbol is to go to the options and right click on any option and you will have a dropdown list where you have the option to copy the symbol and place it into an excel spreadsheet So just as an example I got this symbol ./G4MN22P1885:XCEC
So from that you can see that the extension is CEC so the symbol than is /GC:XCEC
 

JerseyJIm

New member
Gold Member
The easiest way to find the symbol is to go to the options and right click on any option and you will have a dropdown list where you have the option to copy the symbol and place it into an excel spreadsheet So just as an example I got this symbol ./G4MN22P1885:XCEC
So from that you can see that the extension is CEC so the symbol than is /GC:XCEC
Thank you!
 
Top
Contact Us