Written and contributed by Rich Kaczmarek
Here are some times that I copied from the web that lists the swim times. 58.46 51.2 46.37 01:07.2 57.93 59.75 59.7 01:01.1 01:04.2 54.36 When I format these times in mm:ss.0 I get the following. Another approach, suitable at least for passive applications, is to monitor real time market data within Excel using the RTD('tos.rtd'.) function. 'tos' is an acronym for Think Or Swim, an option trading subsidiary of TDAmeritrade. The market data, once in Excel, can be accessed from Mathematica using NetLink. TraderXL sounds interesting. Another approach, suitable at least for passive applications, is to monitor real time market data within Excel using the RTD('tos.rtd'.) function. 'tos' is an acronym for Think Or Swim, an option trading subsidiary of TDAmeritrade. The market data, once in Excel, can be accessed from Mathematica using NetLink. TraderXL sounds interesting.
The last article covered some DDE versus RTD technobabble and left you with an example of how to use RTD in Excel to get the last price of RUT from ThinkOrSwim (TOS). Not bad for a day's work but let's take it to the next level.
Before we begin, bring up ThinkOrSwim and the LockeRTD Excel spreadsheet you saved last week and make sure RUT (not SPX) is in cell B2, like this:
If you ran ThinkOrSwim then LockeRTD and the last price was either blank or #N/A, you've encountered the annoying problem. (If you have Excel 2010 or prior versions, you probably haven't seen it.)
It's well-known and documented but still annoying, and if you search the web you'll find a wide range of work-arounds. Here's a fast and easy method we use every day:
Step 1: Open up a blank Excel spreadsheet
Step 2: Type =RTD('TOS.RTD','LAST','RUT') in any cell and press
(If TOS is running, you should now see the last price of RUT.)
Step 3: Open LockeRTD or any other spreadsheet you have that uses RTD
That's it. Since the newly opened spreadsheet works, any successively loaded RTD spreadsheet will work. You could even close that first spreadsheet and, as long as you keep at least one RTD spreadsheet always running, load more RTD spreadsheets. (Typically we just minimize the Step 1 spreadsheet so we can open and close others with impunity.)
Yes, it's like last century when you had to warm up television tubes. Yes, it's annoying. No, I don't know why it hasn't yet been fixed.
But I'm sure the helpful Microsoft support staff would be more than happy to answer your question.
This little world of connecting ThinkOrSwim to Excel has two players: ThinkOrSwim, which has the data, and Excel, which wants the data. We're using RTD as the mechanism whereby they can talk with each other. (For the literati, think of RTD as a Technobabble Fish.)
You may recall the following RTD command you entered in cell G2 from the last article:
=RTD('TOS.RTD','LAST',B2)
Breaking it down:
- = is how we let Excel know that what follows is something it needs to do.
- RTD is an Excel function that, for our purposes, opens up communications with ThinkOrSwim. RTD can act upon information sent between parenthesis in a particular order:
- 'TOS.RTD' is sent as the 1st RTD parameter. It's the quote-enclosed name of a ThinkOrSwim function that's loaded into memory when you run ThinkOrSwim. The purpose of TOS.RTD is to respond to RTD requests with data from ThinkOrSwim.
- The 2nd RTD parameter is…nothing! See those two commas next to each other in our equation? The commas separate parameters and when they're consecutive, there's obviously nothing between them – so that's how we let RTD know there's no 2nd parameter.
- 'LAST' is in the position of the 3rd RTD parameter. According to RTD Rules, everything sent from the 3rd parameter on is called a 'topic,' and everything from the 4th parameter on is optional.That's nice but ThinkOrSwim always uses the 3rd and 4th parameter. 'LAST' as the 3rd parameter means we're looking for the last price of…whatever we send as the 4th parameter.
- B2 is the 4th RTD parameter. Why use B2 instead of typing 'RUT'? Because we're telling Excel to use the contents of cell B2 as the 4th Then we can just change the contents of B2 from RUT to SPX for the last price of SPX, instead of modifying the equation and potentially goofing it up.
Here's a graphic of the conversation between the programs that may help:
From now on, all your RTD equations will look the same EXCEPT we're about to get clever with the 3rd and 4th parameters.
We used B2 to implicitly reference data for the RTD equation but we still had to explicitly type 'LAST'. Why not replace 'LAST' with another implicit data reference so we don't have to keep retyping 'LAST'? And wouldn't you know it – there it is in cell G1!
Go back into cell G2 and edit the formula, replacing 'LAST' with G$1, so it looks like this:
=RTD('TOS.RTD',G$1,UPPER(B2))
When you hit the key, nothing will appear to happen but behind the scenes Excel is using the contents of cell G1, LAST, as the 3rd parameter. You'll see why this is important in a couple paragraphs.
Did you notice we're now using the UPPER function? This Excel function converts whatever we typed in B2 to uppercase. ThinkOrSwim needs to receive everything in uppercase so we're going to let Excel verify it gets sent in uppercase. This way it doesn't matter if we type rut or RUT or any combination of upper and lower case letters.
Did you notice that we didn't use G1 but G$1? Excel uses the $ to pin down the 1 so if we copy and paste cell G2 (our equation) into a different location, the G will change to the column we move it to but the row will always be 1.
Try it now. Copy cell G2 and paste it to cell G3, then look at the equation:
=RTD('TOS.RTD',G$1,UPPER(B3))
See? The G$1 stayed the same (because we're still in column G and we pinned row 1) but B2 became B3. Why? Because we didn't put a $ in front of the 2 to keep it pinned to the 2nd row.
Next step: Pin B2 and B3 to column B, like this:
Cell G2 =RTD('TOS.RTD',G$1,UPPER($B2))
Cell G3 =RTD('TOS.RTD',G$1,UPPER($B3))
And, just because, enter SPX into cell B3 so your spreadsheet looks something like this:
What happened?
The equation in cell G2 took LAST from cell G1 for the 3rd parameter, looked in its row to column B and plucked RUT from B2 for the 4th parameter, then used RTD to throw all the parameters to ThinkOrSwim. ThinkOrSwim caught the request, got the last price of RUT and threw it back as the answer to the equation in cell G2.
Then cell G3 did the same thing for SPX.
Why is this good?
Because instead of manually typing the equation multiple times, you can get the last price for a BUNCH of symbols by copying cell G2 (or G3), pasting as far as you want down in column G, then simply entering a symbol in column B. You copy & paste; Excel does the heavy lifting by modifying the formulas.
We'll leave on a cliffhanger by streaming live options pricing into Excel. I'm going to use an April 2016 option but you can use any one you'd like by following these directions:
Step 1: Go to the Analyze tab in ThinkOrSwim and pick an option, any option. I'll pick the APR16 1000
RUT Put:
Step 2: Hover the mouse over the Call option line and click the right mouse button. A sub-menu pops up, then you'll click on Copy .RUT160415P1000 (or whichever option you picked):
Step 3: Return to Excel and click the right mouse button on the top of SPX. Next, click the far right icon under Paste Options (aka Match Destination Formatting (M)):
(In some older versions of Excel, it's Paste Special, Text)
When SPX gets overwritten, you'll see this…
…as cell G2 displays the last price paid for your option.
Browse liteon+etdu108+driver+download on sale, by desired features, or by customer ratings. Liteon dvd rom drive model etdu108 drivers for mac. LITE-ON offers an array of optical disk drives (ODD) from DVD-ROM, DVDRW to Blu-ray Drives meeting the needs for various applications. Whether you are building a gaming PC, desktop PC, or laptop, Lite-On has the perfect storage solution for you.
Save your LockeRTD spreadsheet because…
But I'm sure the helpful Microsoft support staff would be more than happy to answer your question.
This little world of connecting ThinkOrSwim to Excel has two players: ThinkOrSwim, which has the data, and Excel, which wants the data. We're using RTD as the mechanism whereby they can talk with each other. (For the literati, think of RTD as a Technobabble Fish.)
You may recall the following RTD command you entered in cell G2 from the last article:
=RTD('TOS.RTD','LAST',B2)
Breaking it down:
- = is how we let Excel know that what follows is something it needs to do.
- RTD is an Excel function that, for our purposes, opens up communications with ThinkOrSwim. RTD can act upon information sent between parenthesis in a particular order:
- 'TOS.RTD' is sent as the 1st RTD parameter. It's the quote-enclosed name of a ThinkOrSwim function that's loaded into memory when you run ThinkOrSwim. The purpose of TOS.RTD is to respond to RTD requests with data from ThinkOrSwim.
- The 2nd RTD parameter is…nothing! See those two commas next to each other in our equation? The commas separate parameters and when they're consecutive, there's obviously nothing between them – so that's how we let RTD know there's no 2nd parameter.
- 'LAST' is in the position of the 3rd RTD parameter. According to RTD Rules, everything sent from the 3rd parameter on is called a 'topic,' and everything from the 4th parameter on is optional.That's nice but ThinkOrSwim always uses the 3rd and 4th parameter. 'LAST' as the 3rd parameter means we're looking for the last price of…whatever we send as the 4th parameter.
- B2 is the 4th RTD parameter. Why use B2 instead of typing 'RUT'? Because we're telling Excel to use the contents of cell B2 as the 4th Then we can just change the contents of B2 from RUT to SPX for the last price of SPX, instead of modifying the equation and potentially goofing it up.
Here's a graphic of the conversation between the programs that may help:
From now on, all your RTD equations will look the same EXCEPT we're about to get clever with the 3rd and 4th parameters.
We used B2 to implicitly reference data for the RTD equation but we still had to explicitly type 'LAST'. Why not replace 'LAST' with another implicit data reference so we don't have to keep retyping 'LAST'? And wouldn't you know it – there it is in cell G1!
Go back into cell G2 and edit the formula, replacing 'LAST' with G$1, so it looks like this:
=RTD('TOS.RTD',G$1,UPPER(B2))
When you hit the key, nothing will appear to happen but behind the scenes Excel is using the contents of cell G1, LAST, as the 3rd parameter. You'll see why this is important in a couple paragraphs.
Did you notice we're now using the UPPER function? This Excel function converts whatever we typed in B2 to uppercase. ThinkOrSwim needs to receive everything in uppercase so we're going to let Excel verify it gets sent in uppercase. This way it doesn't matter if we type rut or RUT or any combination of upper and lower case letters.
Did you notice that we didn't use G1 but G$1? Excel uses the $ to pin down the 1 so if we copy and paste cell G2 (our equation) into a different location, the G will change to the column we move it to but the row will always be 1.
Try it now. Copy cell G2 and paste it to cell G3, then look at the equation:
=RTD('TOS.RTD',G$1,UPPER(B3))
See? The G$1 stayed the same (because we're still in column G and we pinned row 1) but B2 became B3. Why? Because we didn't put a $ in front of the 2 to keep it pinned to the 2nd row.
Next step: Pin B2 and B3 to column B, like this:
Cell G2 =RTD('TOS.RTD',G$1,UPPER($B2))
Cell G3 =RTD('TOS.RTD',G$1,UPPER($B3))
And, just because, enter SPX into cell B3 so your spreadsheet looks something like this:
What happened?
The equation in cell G2 took LAST from cell G1 for the 3rd parameter, looked in its row to column B and plucked RUT from B2 for the 4th parameter, then used RTD to throw all the parameters to ThinkOrSwim. ThinkOrSwim caught the request, got the last price of RUT and threw it back as the answer to the equation in cell G2.
Then cell G3 did the same thing for SPX.
Why is this good?
Because instead of manually typing the equation multiple times, you can get the last price for a BUNCH of symbols by copying cell G2 (or G3), pasting as far as you want down in column G, then simply entering a symbol in column B. You copy & paste; Excel does the heavy lifting by modifying the formulas.
We'll leave on a cliffhanger by streaming live options pricing into Excel. I'm going to use an April 2016 option but you can use any one you'd like by following these directions:
Step 1: Go to the Analyze tab in ThinkOrSwim and pick an option, any option. I'll pick the APR16 1000
RUT Put:
Step 2: Hover the mouse over the Call option line and click the right mouse button. A sub-menu pops up, then you'll click on Copy .RUT160415P1000 (or whichever option you picked):
Step 3: Return to Excel and click the right mouse button on the top of SPX. Next, click the far right icon under Paste Options (aka Match Destination Formatting (M)):
(In some older versions of Excel, it's Paste Special, Text)
When SPX gets overwritten, you'll see this…
…as cell G2 displays the last price paid for your option.
Browse liteon+etdu108+driver+download on sale, by desired features, or by customer ratings. Liteon dvd rom drive model etdu108 drivers for mac. LITE-ON offers an array of optical disk drives (ODD) from DVD-ROM, DVDRW to Blu-ray Drives meeting the needs for various applications. Whether you are building a gaming PC, desktop PC, or laptop, Lite-On has the perfect storage solution for you.
Save your LockeRTD spreadsheet because…
Next: Squeezing more out of ThinkOrSwim.
How To Use Rtd Excel
Orange you glad?
Excel For Mac Rtd Functions Think Or Swim Trunks
Written and contributed by Rich Kaczmarek
