Connecting ThinkOrSwim to Excel

Part 1 of a 4-Part Series

You can’t beat a spreadsheet for turning big chunks of data into actionable information. Without some
experience in programming, however, it isn’t always the easiest way to manipulate real-time data.

Let’s write (get it?) that wrong! It is easy to have ThinkOrSwim (TOS) stream live data into Excel but it’s
not well-documented. (There’s a harder way, too, that’s not well-documented. You’ll get an overview of
both, you crazy kids, but we’ll focus on the easier.) So, let’s go through a few explanations, show you
how to connect the two programs and give a couple examples to get you on your way.

Why would you want to know how to do this stuff? Because you probably have a couple of ideas or
questions swimming around. If you’ve got imagination and take a little time to learn, you can create a
real-time model in Excel that proves (or disproves) your hypothesis or gives you answers.

And it’s fun.

On with the show!

Grab Some Coffee and Stay Awake

Let’s slog through some technical explanations. I’ll try to make it as painless as
possible BUT if you can learn to understand the difference between a Call and Put,
you’re certainly able to understand the difference between DDE and RTD.

If you don’t drink coffee and couldn’t care less about technical details, jump to the
next section and wait for us there.

DDE and RTD are two different mechanisms that allow Excel to talk with the outside world. Specifically,
DDE (Dynamic Data Exchange) is an older technology that links Microsoft products and RTD (Real-Time
Data) retrieves real-time data from a program that supports COM automation but there’s overlap for
what we’re trying to do.

(I know. You’re thinking, “Where can I read more about this exciting stuff?!?!” Slow down, Turbo; you
can visit http://msdn.microsoft.com/en-us/library/aa140061%28office.10%29.aspx after reading the rest
of this article.)

Which method is better? Well, as with all things technological, the answer is “it depends.” (This is when
most sane people throw up their arms in exasperation with most technology people. But stick with me.)

Both are kind of real-time. DDE refreshes an Excel cell when TOS updates its data; this is known as
“interrupt driven.” RTD is interrupt driven, too, but Excel isn’t interrupted by TOS changing data; the
interrupt comes from a clock timer, and the timer can be changed.

You can imagine that DDE may get updated faster than RTD because TOS data may change before the
clock timer goes off. Is that bad? Not really if you consider we’re talking about milliseconds.

1 Copyright © 2016, Locke In Your Success

Two more items you should know:

1. RTD puts less of a load on the ThinkOrSwim servers, so you can see why they want to move from
DDE to RTD. (They support both mechanisms right now.)

2. DDE needs “explicit data,” which means you need to give it exactly what it wants. If you want
the last price of RUT in cell G2, you need to ask for the last price of RUT in cell G2. If you want
the last price of SPX, you have to completely change what you ask in cell G2.

RTD can use explicit data but it can also use “implicit data.” That means you can say, “Hey, G2,
give me the last price for the underlying that’s in cell B2.” Better, you can change B2 from RUT
to SPX anytime, without doing anything to cell G2, and G2 will return the last price of SPX.

Why is that good? Because Excel can make do different things with the live data that will soon
be streaming into your spreadsheet. For a non-programmer, RTD is easier to use than DDE.

What’s the conclusion? DDE may be technically faster but, for simplicity and user-level flexibility, RTD is
the way to go. We’re big easy fans so let’s get moving with RTD.

One Example to Hook You for Next Week

In the previous section I talked about my old friends, cells B2 and G2, so we’ll use them in our example.

Step 1: Run ThinkOrSwim. (Get it at https://mediaserver.thinkorswim.com/installer/install.html.)

Step 2: After ThinkOrSwim is running, run Excel. (We use Excel 2016 for Windows but this works in Excel
2007, too. No guarantees for any other operating system or any other spreadsheet program.)

Step 3: To keep it all straight in our head, let’s create some headers. Make cells B2 and G2 in your
spreadsheet look like this:

Step 4: In cell B2 (under Symbol), type RUT.

Step 5: Ready for the magic of RTD? Paste the following into cell G2 (under LAST):

=RTD("TOS.RTD",,"LAST",B2)

Feel free to type it in yourself if you don’t want to copy & paste, but make sure you don’t forget
the 2 commas between "TOS.RTD" and "LAST"; don’t forget the double quotes; LAST must be
capitalized and remember to press the <Enter> key to accept the equation (and leave cell G2).

2 Copyright © 2016, Locke In Your Success

If you entered everything correctly, your spreadsheet should look something like this:

The number may not match because we’re looking at RUT on different days and times. Other than that
– CONGRATULATIONS! What you see is TOS is giving Excel live data, the last price paid for RUT.

Now change RUT to SPX in B2 to get the last price for SPX. It worked? You’re a superstar!

Good enough for now. Experiment a little, then save this workbook as LockeRTD. Next week we’ll build
on top of what you did today, make Excel do more work than you, and delve into derivatives.

(By the way, if you have about 30 seconds and would like to view ThinkOrSwim’s documentation on RTD,
click here.)

3 Copyright © 2016, Locke In Your Success

 Connecting ThinkOrSwim to Excel
Part 2 of a 4-Part Series

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:

An Annoying Problem You May Have Encountered

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 <Enter>

(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.

4 Copyright © 2016, Locke In Your Success

Behind the Curtain
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:

1. "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

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

3. "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 parameters. "LAST" as the 3rd
parameter means we’re looking for the last price of…whatever we send as the 4th
parameter.

4. 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 parameter. 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.

5 Copyright © 2016, Locke In Your Success

Let Excel Do the Work
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 <Enter> 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 2 nd 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.

6 Copyright © 2016, Locke In Your Success

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.

Let’s Get Some Option Data

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):

7 Copyright © 2016, Locke In Your Success

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.

Save your LockeRTD spreadsheet because…

NEXT WEEK: Squeezing more out of ThinkOrSwim.

Orange you glad?

8 Copyright © 2016, Locke In Your Success

 Connecting ThinkOrSwim to Excel
Part 3 of a 4-Part Series

Part 2 explained Excel’s RTD function and how it interacted with ThinkOrSwim (TOS). Excel’s UPPER
function was introduced, we indirectly referenced a symbol and a ThinkOrSwim directive, and you
learned a clever way to copy equations to new cells so Excel would modify just the parts you wanted
modified (pinning via the $). Oh, yeah, we got live option pricing fed into the spreadsheet, too.

If you thought that was a lot of fun, wait until you see what’s in store for today.

(Before we begin, bring up ThinkOrSwim and the LockeRTD Excel spreadsheet you saved last week.)

ThinkOrSwim Data Export Fields

In the last article, you found that there were RTD parameters passed from Excel to ThinkOrSwim (TOS),
the 3rd parameter was something TOS had to return and the 4th parameter was the symbol for which it
was to return that something. (=RTD("TOS.RTD",, "LAST","RUT"), for example, returns the last price of
RUT.)

It should be obvious that the 4th parameter can be any symbol you can look up in ThinkOrSwim but
there’s much, much more than the LAST price that TOS can return for the 3rd parameter.

The 3rd parameter ThinkOrSwim receives is called a “Data Export Field,” as in “this is the data I wish to
export from ThinkOrSwim.” What are the Data Export Fields? This is the only explanation I could find:

 To use RTD, bring up a watchlist on the Quotes sub-tab under the MarketWatch tab,
click on the Printer icon in the top right, and choose “Export to Excel”.
 Because RTD is a function in Excel, you can take full advantage of the power of Excel
with functionality such as cell referencing.
 To see all of the functions supported in exported data, click on the “Data Export Help”
option on the Printer menu.
https://tlc.thinkorswim.com/center/release/rel-04-26-2014.html#RTD

Ah, yes. The Printer icon. Of course. And that would be…where, exactly?

A lot changed since those words were written, including the removal of the Printer icon. Unfortunately,
that’s pretty much it for TOS documentation. Fortunately, you’re reading this article.

9 Copyright © 2016, Locke In Your Success

Since we’re already under the Quotes sub-tab under the MarketWatch tab (MarketWatch | Quotes),
we’ll start from here. You don’t have to – you could start under Trade | All Products, Analyze | Add
Simulated Trades, Analyze | Risk Profile, Analyze | Probability Analysis or many other locations in the
software. (It isn’t in every sub-tab, though, so you may have to hunt for it.)

Look near the top right corner and you’ll see the box that’s circled in the picture. Click it with the left
mouse button, click Export, then click Help on data export:

For your dining and dancing pleasure, all the Data Export fields can be found
at the end of this article in the Bonus (?) section.

Just to keep you on your toes, even though the menu says Help on Data Export,

1. it doesn’t actually provide any help and

2. they aren’t all Data Export fields.

More Option Data

You can find a little more about the Data Export Fields in the next section but let’s use a few of them
now to get additional options data. Here’s how the last article left the LockeRTD spreadsheet:

Let’s add in a few new column headers, like this:

Make sure your spelling is correct, everything is CAPITALIZED, and don’t forget the underscore (_) in
PERCENT_CHANGE, NET_CHANGE and EXPIRATION_DAY.

10 Copyright © 2016, Locke In Your Success

For those familiar with Excel, select G2:G3 and paste it into E2:F3, then H2:N3. For those not familiar
with Excel, follow these steps:

1. Select cells G2 and G3 by clicking into cell G2 and, keeping

the left mouse button pressed down, drag it into G3, then
release the mouse button. 

2. Now we’re going to copy those cells. I press the <Ctrl-c> combination on my keyboard but you
can also click the right mouse button and select Copy from the sub-menu. Whichever you do,
you’ll see the G2:G3 selection highlighted by dashed lines instead of solid lines.

3. Go to cell E2 and click into it, then drag into E3. (The same way
you did in step 1.) 

4. I paste what I copied by pressing the <Ctrl-v> combination on my keyboard but you can also click
the right mouse button, then select either Paste Options, Paste (circled in red) or Paste Options,
Formula (circled in blue) from the sub-menu:

5. Go to cell H2 and click into it, then drag into N3. (The same way you did in step 1 and step 3.)

6. Paste by using <Ctrl-v> on the keyboard or the Paste Options, Formula (circled in red, above)
from the sub-menu, and you’ll end up with something like this:

Option-specific information (such as EXPIRATION) isn’t really relevant for RUT, hence the reason you see
some inapplicable information in row 2 (which we’ll clean up in the next article), but everything in row 3
is usable and streaming live from ThinkOrSwim.

If you want to experiment with other symbols, select the entire row by clicking the 2 or 3 row number
(circled in green), which will highlight it. Copy it (using your favorite method), click on an empty row
number to select it (4 is good), and paste (using your favorite method).* Change the symbol in column B
and away you go!

11 Copyright © 2016, Locke In Your Success

Bonus (?): Data Export Fields with Examples
There are 6 reference tables (with examples) to be found as you scroll down:

1. Data Export Fields returning any result for options or non-options (quantity 86)

2. Data Export Fields returning any result for options (quantity 85)

3. Data Export Fields returning any result for any underlying other than options (quantity 86)

4. Data Export Fields which don’t return any result when used with RTD (quantity 327)

5. All Data Export Fields (quantity 432 but 19 are CUSTOM fields, which won’t be addressed in
this series of articles)

6. Data Export Fields of April 1, 2014 (quantity 69)

One question you may have is, “Why are there 327 Data Export Fields that don’t return anything?”
That’s a great question. They mostly look like Studies and Strategies for charting which, for some
reason, was dumped into Help on Data Export. If it doesn’t actually export data via RTD, then why is it
there? I don’t know but I’m sure the helpful ThinkOrSwim support staff would be more than happy to
answer your question.

One observation you may have is that a Data Export Field may return a result for an option but not
return a result (or return a bogus result) for something not an option (or vice versa). Why does DIV
return N/A for an option and DIV_FREQ return --? Shouldn’t they both return nothing or at least return
the same thing? Don’t worry – we’ll do some exception handling for spreadsheet modeling in the next
article.

Warning 1: Make sure you know the format of result you’re expecting! If you expect a delta to
be 55.32 and the result is .5532, panic may ensue.

Before panicking or fixing problems, determine if it’s real. Did it ever work and, if so, how?

Warning 2: Sometimes you’ll get a different result during Regular Trading Hours (RTH) and After
Market Close (AMC). As an example, TOS recently returned an option BID_SIZE of 127300.00%
After Market Close and a more understandable 889 during Regular Trading Hours.

Warning 3: It is possible Data Export Fields which don’t appear to work actually do work but
they’re not being used the right way (or they’ll work in future revisions). If you find any
documentation, let us know!

Final Thoughts:

1. These lists were verified using ThinkOrSwim version 1880.75 during Regular Trading Hours and
the results presented were captured live. Any other version of ThinkOrSwim may have a
different list of Data Export Fields and/or different results (including nothing when you expect
something) may present at any time.

12 Copyright © 2016, Locke In Your Success

2. CUSTOM fields were eliminated in all but table 5, the list of all Data Export Fields. (CUSTOM
connects thinkscript® results to Excel and that’s a topic for another article.)

3. In all tables below, the equation for the first cell returning a result is =RTD("TOS.RTD",,$A2,B$1).
All other cells use the same formula, adjusted for their respective row and column.

Data Export Fields returning any result for options or non-options (quantity 86)

TOS Data Export Field T .T160318C36

52HIGH 37.12 N/A
52LOW 30.97 N/A
ASK 36.36 1.09
ASK_SIZE 50 162
ASKX 36.36 N 1.09 I
AV_TRADE_PRICE - -
AX N I
BA_SIZE 36 x 50 101 x 162
BACK_EX_MOVE ±1.003 N/A
BACK_VOL 20.97% N/A
BETA 0.3085 N/A
Beta 0.3085 N/A
BID 36.35 1.07
BID_SIZE 36 101
BIDX 36.35 N 1.07 N
BX N N
CALL_VOLUME_INDEX 0.402 N/A
CLOSE 36.65 1.31
COVERED_RETURN N/A 19.03%
DELTA 1 0.5529
DESCRIPTION AT&T INC COM T 100 MAR 16 36 CALL
DIV 0.48 N/A
DIV_FREQ Q --
EPS 0.9485 N/A
EX_DIV_DATE 1/6/16 N/A
EX_MOVE_DIFF ±0.782 N/A
EXCHANGE COMPOSITE COMPOSITE
EXPIRATION N/A MAR 16
EXPIRATION_DAY N/A 2016-03-19
EXTRINSIC N/A 0.72
FRONT_EX_MOVE ±0.627 N/A
FRONT_VOL 24.22% N/A
FX_PAIR T .T160318C36
GAMMA 0 0.1882
HIGH 36.74 1.13
HTB_ETB ETB

13 Copyright © 2016, Locke In Your Success

IMPL_VOL 21.41% 18.80%
INTRINSIC N/A 0.36
LAST 36.36 1.08
LAST_SIZE 900 1
LASTX 36.36 D 1.08 N
LOW 36.26 1.08
LX D N
MARK 36.36 1.08
MARK_CHANGE -0.29 -0.235
MARK_PERCENT_CHANGE -0.79% -17.87%
MARK_PERCENT_UNDERLYING 100.00% 2.97%
MARKET_CAP 223,687 M N/A
MAX_COVERED_RETURN N/A 19.03%
MRKT_MKR_MOVE ±0.373 N/A
MT_NEWS * *
NET_CHANGE -0.29 -0.23
OPEN 36.65 1.13
OPEN_INT 0 24666
OPTION_VOLUME_INDEX 0.614 N/A
P_L_DAY - -
P_L_OPEN - -
P_L_PERCENT - -
P_L_YTD - -
PE 38.34 N/A
PERCENT_CHANGE -0.79% -17.56%
PERCENT_IN_THE_COLUMN N/A 1.00%
PERCENT_OUT_THE_MONEY N/A 0.00%
POSITION_N_L - -
POSITION_QTY - -
PROB_OF_EXPIRING N/A 52.06%
PROB_OF_TOUCHING N/A 88.52%
PROB_OTM N/A 47.94%
PUT_CALL_RATIO 2.095 N/A
PUT_VOLUME_INDEX 0.821 N/A
QUOTE_TREND instrument=T instrument=.T160318C36
RHO 0 0.0171
ROC N/A N/A
ROR N/A N/A
SHARES 6152000000 N/A
STOCK_BETA 0.3085 N/A
STRENGTH_METER Range Bound N/A
STRIKE N/A 36
SYMBOL T .T160318C36
THETA 0 -0.0091
VEGA 0 0.0459
14 Copyright © 2016, Locke In Your Success
 VOL_DIFF 3.25% N/A
VOL_INDEX 21.41% N/A
VOLUME 11636907 88
WEIGHTED_BACK_VOL 19.46% N/A
YIELD 5.28% N/A

Data Export Fields returning any result for options (quantity 85 but less than 50 are useful)

TOS Data Export Field .T160318C36

52HIGH N/A
52LOW N/A
ASK 1.11
ASK_SIZE 379
ASKX 1.11 X
AV_TRADE_PRICE -
AX X
BA_SIZE 442 x 379
BACK_EX_MOVE N/A
BACK_VOL N/A
BETA N/A
Beta N/A
BID 1.08
BID_SIZE 442
BIDX 1.08 M
BX M
CALL_VOLUME_INDEX N/A
CLOSE 1.31
COVERED_RETURN 18.89%
DELTA 0.5568
DESCRIPTION T 100 MAR 16 36 CALL
DIV N/A
DIV_FREQ --
EPS N/A
EX_DIV_DATE N/A
EX_MOVE_DIFF N/A
EXCHANGE COMPOSITE
EXPIRATION MAR 16
EXPIRATION_DAY 2016-03-19
EXTRINSIC 0.715
FRONT_EX_MOVE N/A
FRONT_VOL N/A
FX_PAIR .T160318C36
GAMMA 0.1889
HIGH 1.13
IMPL_VOL 18.72%
15 Copyright © 2016, Locke In Your Success
INTRINSIC 0.38
LAST 1.11
LAST_SIZE 8
LASTX 1.11 C
LOW 1.09
LX C
MARK 1.095
MARK_CHANGE -0.22
MARK_PERCENT_CHANGE -16.73%
MARK_PERCENT_UNDERLYING 3.01%
MARKET_CAP N/A
MAX_COVERED_RETURN 18.89%
MRKT_MKR_MOVE N/A
MT_NEWS *
NET_CHANGE -0.2
OPEN 1.13
OPEN_INT 24666
OPTION_VOLUME_INDEX N/A
P_L_DAY -
P_L_OPEN -
P_L_PERCENT -
P_L_YTD -
PE N/A
PERCENT_CHANGE -15.27%
PERCENT_IN_THE_COLUMN 1.06%
PERCENT_OUT_THE_MONEY 0.00%
POSITION_N_L -
POSITION_QTY -
PROB_OF_EXPIRING 52.45%
PROB_OF_TOUCHING 87.83%
PROB_OTM 47.55%
PUT_CALL_RATIO N/A
PUT_VOLUME_INDEX N/A
QUOTE_TREND instrument=.T160318C36
RHO 0.0172
ROC N/A
ROR N/A
SHARES N/A
STOCK_BETA N/A
STRENGTH_METER N/A
STRIKE 36
SYMBOL .T160318C36
THETA -0.009
VEGA 0.0458
VOL_DIFF N/A
16 Copyright © 2016, Locke In Your Success
 VOL_INDEX N/A
VOLUME 84
WEIGHTED_BACK_VOL N/A
YIELD N/A

Data Export Fields returning any result for any underlying other than options (quantity 86)

TOS Data Export Field T

52HIGH 37.12
52LOW 30.97
ASK 36.4
ASK_SIZE 86
ASKX 36.40 N
AV_TRADE_PRICE -
AX N
BA_SIZE 26 x 86
BACK_EX_MOVE ±1.007
BACK_VOL 21.03%
BETA 0.3085
Beta 0.3085
BID 36.39
BID_SIZE 26
BIDX 36.39 N
BX N
CALL_VOLUME_INDEX 0.394
CLOSE 36.65
COVERED_RETURN N/A
DELTA 1
DESCRIPTION AT&T INC COM
DIV 0.48
DIV_FREQ Q
EPS 0.9485
EX_DIV_DATE 1/6/16
EX_MOVE_DIFF ±0.781
EXCHANGE COMPOSITE
EXPIRATION N/A
EXPIRATION_DAY N/A
EXTRINSIC N/A
FRONT_EX_MOVE ±0.635
FRONT_VOL 24.48%
FX_PAIR T
GAMMA 0
HIGH 36.74
HTB_ETB ETB
IMPL_VOL 21.48%
17 Copyright © 2016, Locke In Your Success
INTRINSIC N/A
LAST 36.39
LAST_SIZE 100
LASTX 36.39 Y
LOW 36.26
LX Y
MARK 36.39
MARK_CHANGE -0.26
MARK_PERCENT_CHANGE -0.71%
MARK_PERCENT_UNDERLYING 100.00%
MARKET_CAP 223,871 M
MAX_COVERED_RETURN N/A
MRKT_MKR_MOVE ±0.387
MT_NEWS *
NET_CHANGE -0.26
OPEN 36.65
OPEN_INT 0
OPTION_VOLUME_INDEX 0.613
P_L_DAY -
P_L_OPEN -
P_L_PERCENT -
P_L_YTD -
PE 38.37
PERCENT_CHANGE -0.71%
PERCENT_IN_THE_COLUMN N/A
PERCENT_OUT_THE_MONEY N/A
POSITION_N_L -
POSITION_QTY -
PROB_OF_EXPIRING N/A
PROB_OF_TOUCHING N/A
PROB_OTM N/A
PUT_CALL_RATIO 2.15
PUT_VOLUME_INDEX 0.827
QUOTE_TREND instrument=T
RHO 0
ROC N/A
ROR N/A
SHARES 6152000000
STOCK_BETA 0.3085
STRENGTH_METER Range Bound
STRIKE N/A
SYMBOL T
THETA 0
VEGA 0
VOL_DIFF 3.45%
18 Copyright © 2016, Locke In Your Success
 VOL_INDEX 21.48%
VOLUME 11344564
WEIGHTED_BACK_VOL 19.42%
YIELD 5.28%

Data Export Fields which don’t return any result when used with RTD (quantity 327)

ACT_WARNING IntradayMomentumIndex RelativeVolatilityIndex

ADX InvertedHammer RelativeVolumeStDev
ADXCrossover KeltnerChannels ReverseEngineeringMACD
ADXR Kicking ReverseEngineeringRSI
ATR KlingerHistogram RibbonStudy
ATRTrailingStop KlingerOscillator RisingThreeMethods
AbandonedBaby LBR_PaintBars STARCBands
AccDist LBR_SmartADX SVEPivots
AccelerationBands LBR_ThreeTenOscillator SVEZLRBPercB
AccelerationDecelerationOsc LegacyEMA SectorRotationModel
AccumDistBuyPr LinearRegCh100 SemiCupFormation
AccumDistPrVol LinearRegCh50 SentimentZoneOscillator
AccumulationSwingIndex LinearRegChVar SeparatingLines
AdvanceBlock LinearRegCurve SeriesCount
AdvanceDecline LinearRegTrendline ShootingStar
AdvanceDeclineCumulativeAvg LinearRegrReversal SideBySideWhiteLines
Alpha2 LinearRegressionSlope SimpleMovingAvg
AlphaJensen LongHaulFilter Spearman
AroonIndicator LongLeggedDoji SpectrumBars
AroonOscillator LookUpHighest Spreads
AwesomeOscillator LookUpLowest StandardDevChannel
BalanceOfMarketPower LowPriceGappingPlay StandardDeviation
BeltHold MACD StandardError
Beta2 MACDHistogram StandardErrorBands
BollingerBands MACDHistogramCrossover StandardErrorChannel
BollingerBandsCrossover MACDTwoLines StickSandwich
BollingerBandwidth MACDWithPrices StochRSI
BollingerPercentB MESASineWave StochasticCrossover
Breakaway MajorGannLevels StochasticFast
CCI MarkerIndicator StochasticFull
CCIAverage MarketForecast StochasticMomentumIndex
CSI Marubozu StochasticSlow
CamarillaPoints MassIndex StressIndicator
ChaikinMoneyFlow MatHold SwamiAccDist
ChaikinOsc MatchingLow SwamiConvolution
ChaikinOscillator McClellanOscillator SwamiIntradayFisher
ChaikinVolatility McClellanSummationIndex SwamiIntradayImpulse
ChandeMomentumOscillator MedianAverage SwamiIntradayVolume
19 Copyright © 2016, Locke In Your Success
 CloseLocationValue MedianPrice SwamiLaguerreTrend
ConcealingBabySwallow MeetingLines SwamiMarketMode
Correlation MktFacilitationIdx SwamiPredict
CumulativeVolumeIndex Momentum SwamiRelativePerformance
CyberCyclesOscillator MomentumCrossover SwamiSwingWave
DEMA MomentumPercent SwamiVolatility
DIMinus MomentumSMA SwamiVolume
DIPlus MoneyFlow SwingIndex
DMA MoneyFlowIndex SymbolRelation
DMI MoneyFlowIndexCrossover TAC_ADX
DMI_Oscillator MonkeyBars TAC_DIMinus
DMI_ReversalAlerts MorningDojiStar TAC_DIPlus
DMI_StochasticExtreme MorningStar TEMA
DailyHighLow MovAvgAdaptive TMV
DailyOpen MovAvgEnvelope TPOProfile
DailySMA MovAvgExpRibbon TRIX
DarkCloudCover MovAvgExponential TTM_LRC
DarvasBox MovAvgTriangular TTM_Squeeze
Deliberation MovAvgTwoLines TTM_Trend
DemandIndex MovAvgWeighted TTM_Wave
DetrendedPriceOsc MovingAvgCrossover TheoreticalOptionPrice
DisparityIndex MultiCurrencyCorrelationOsc ThreeBlackCrows
DisplacedEMA NegativeVolumeIndex ThreeInsideDown
Displacer Next3rdFriday ThreeInsideUp
Doji OnBalanceVolume ThreeLineStrike
DoubleSmoothedStochastics OnNeck ThreeOutsideDown
DownsideGapThreeMethods OnsetTrendDetector ThreeOutsideUp
DownsideTasukiGap OpenInterest ThreeStarsInTheSouth
DynamicMomentumIndex OptionDelta ThreeWhiteSoldiers
EaseOfMovement OptionGamma Thrusting
EhlersDistantCoefficientFilter OptionRho TimeSeriesForecast
EhlersRoofingFilter OptionTheta TradeVolumeIndex
EhlersStochastic OptionVega TrendNoiseBalance
EhlersSuperSmootherFilter PPS TrendPeriods
ElliotOscillator PairCorrelation TrendQuality
Engulfing PairRatio TriStar
ErgodicOsc ParabolicSAR TrueRangeIndicator
Ermanometry ParabolicSARCrossover TrueRangeSpecifiedVolume
EveningDojiStar PercentChg TrueStrengthIndex
EveningStar PercentR TwoCrows
FAKE_THINKSCRIPT_COLUMN PersonsPivots TypicalPrice
FW_CCI_Advanced PiercingLine UlcerIndex
FW_CCI_Basic PolarizedFractalEfficiency UltimateOscillator
FW_DPO_MOBO PolychromMtm UniqueThreeRiverBottom
FW_MMG PositiveVolumeIndex UniversalOscillator
20 Copyright © 2016, Locke In Your Success
 FW_MOBO_Basic PriceActionIndicator UpsideGapThreeMethods
FW_SOAP PriceAndVolumeTrend UpsideGapTwoCrows
FallingThreeMethods PriceAverageCrossover UpsideTasukiGap
FastBeta PriceChannel VWAP
FisherTransform PriceOsc VariableMA
ForceIndex PriceRatio VerticalHorizontalFilter
ForecastOscillator PriceVolumeRank VolatilityStdDev
FreedomOfMovement PriceZoneOscillator VolatilitySwitch
GatorOscillator ProjectionBands VolumeAccumulation
HACOLT ProjectionOscillator VolumeAvg
Hammer QStick VolumeFlowIndicator
HangingMan RSI VolumeOsc
Harami RSICrossover VolumeProfile
HaramiCross RSquared VolumeRateOfChange
HeikinAshiDiff RainbowAverage VolumeWeightedMACD
HighPriceGappingPlay RandomWalkIndex VolumeZoneOscillator
HistoricalVolatility RangeBands VortexIndicator
HomingPigeon RangeIndicator WeaknessInAStrongTrend
HullMovingAvg RateOfChange WeightedClose
IFT_StochOsc RateOfChangeCrossover WildersSmoothing
Ichimoku Ray WilliamsAD
IdenticalThreeCrows RayBearPower WilliamsAlligator
ImpVolatility RayBullPower WilliamsPercentR
Impulse RelativeMomentumIndex WoodiesPivots
InNeck RelativeRangeIndex ZigZagTrendPercent
Inertia RelativeStrength ZigZagTrendSign

All Data Export Fields (quantity 432)

TOS Data Export Field T .T160318C36

52HIGH 37.12 N/A
52LOW 30.97 N/A
ACT_WARNING
ADX
ADXCrossover
ADXR
ASK 36.43 1.16
ASKX 36.43 N 1.16 C
ASK_SIZE 42 2427
ATR
ATRTrailingStop
AV_TRADE_PRICE - -
AX N C
AbandonedBaby
AccDist
21 Copyright © 2016, Locke In Your Success
AccelerationBands
AccelerationDecelerationOsc
AccumDistBuyPr
AccumDistPrVol
AccumulationSwingIndex
AdvanceBlock
AdvanceDecline
AdvanceDeclineCumulativeAvg
Alpha2
AlphaJensen
AroonIndicator
AroonOscillator
AwesomeOscillator
BACK_EX_MOVE ±1.02 N/A
BACK_VOL 21.27% N/A
BA_SIZE 59 x 42 467 x 2,427
BETA 0.3085 N/A
BID 36.42 1.11
BIDX 36.42 N 1.11 C
BID_SIZE 59 467
BX N C
BalanceOfMarketPower
BeltHold
Beta 0.3085 N/A
Beta2
BollingerBands
BollingerBandsCrossover
BollingerBandwidth
BollingerPercentB
Breakaway
CALL_VOLUME_INDEX 0.451 N/A
CCI
CCIAverage
CLOSE 36.65 1.31
COVERED_RETURN N/A 18.74%
CSI
CUSTOM1
CUSTOM10
CUSTOM11
CUSTOM12
CUSTOM13
CUSTOM14
CUSTOM15
CUSTOM16
CUSTOM17
22 Copyright © 2016, Locke In Your Success
CUSTOM18
CUSTOM19
CUSTOM2
CUSTOM3
CUSTOM4
CUSTOM5
CUSTOM6
CUSTOM7
CUSTOM8
CUSTOM9
CamarillaPoints
ChaikinMoneyFlow
ChaikinOsc
ChaikinOscillator
ChaikinVolatility
ChandeMomentumOscillator
CloseLocationValue
ConcealingBabySwallow
Correlation
CumulativeVolumeIndex
CyberCyclesOscillator
DELTA 1 0.5649
DEMA
DESCRIPTION AT&T INC COM T 100 MAR 16 36 CALL
DIMinus
DIPlus
DIV 0.48 N/A
DIV_FREQ Q --
DMA
DMI
DMI_Oscillator
DMI_ReversalAlerts
DMI_StochasticExtreme
DailyHighLow
DailyOpen
DailySMA
DarkCloudCover
DarvasBox
Deliberation
DemandIndex
DetrendedPriceOsc
DisparityIndex
DisplacedEMA
Displacer
Doji
23 Copyright © 2016, Locke In Your Success
DoubleSmoothedStochastics
DownsideGapThreeMethods
DownsideTasukiGap
DynamicMomentumIndex
EPS 0.9485 N/A
EXCHANGE COMPOSITE COMPOSITE
EXPIRATION N/A MAR 16
EXPIRATION_DAY N/A 2016-03-19
EXTRINSIC N/A 0.71
EX_DIV_DATE 1/6/16 N/A
EX_MOVE_DIFF ±0.794 N/A
EaseOfMovement
EhlersDistantCoefficientFilter
EhlersRoofingFilter
EhlersStochastic
EhlersSuperSmootherFilter
ElliotOscillator
Engulfing
ErgodicOsc
Ermanometry
EveningDojiStar
EveningStar
FAKE_THINKSCRIPT_COLUMN
FRONT_EX_MOVE ±0.64 N/A
FRONT_VOL 24.59% N/A
FW_CCI_Advanced
FW_CCI_Basic
FW_DPO_MOBO
FW_MMG
FW_MOBO_Basic
FW_SOAP
FX_PAIR T .T160318C36
FallingThreeMethods
FastBeta
FisherTransform
ForceIndex
ForecastOscillator
FreedomOfMovement
GAMMA 0 0.187
GatorOscillator
HACOLT
HIGH 36.74 1.13
HTB_ETB ETB
Hammer
HangingMan
24 Copyright © 2016, Locke In Your Success
Harami
HaramiCross
HeikinAshiDiff
HighPriceGappingPlay
HistoricalVolatility
HomingPigeon
HullMovingAvg
IFT_StochOsc
IMPL_VOL 21.56% 18.87%
INTRINSIC N/A 0.425
Ichimoku
IdenticalThreeCrows
ImpVolatility
Impulse
InNeck
Inertia
IntradayMomentumIndex
InvertedHammer
KeltnerChannels
Kicking
KlingerHistogram
KlingerOscillator
LAST 36.425 1.13
LASTX 36.425 D 1.13 M
LAST_SIZE 100 3
LBR_PaintBars
LBR_SmartADX
LBR_ThreeTenOscillator
LOW 36.26 1.09
LX D M
LegacyEMA
LinearRegCh100
LinearRegCh50
LinearRegChVar
LinearRegCurve
LinearRegTrendline
LinearRegrReversal
LinearRegressionSlope
LongHaulFilter
LongLeggedDoji
LookUpHighest
LookUpLowest
LowPriceGappingPlay
MACD
MACDHistogram
25 Copyright © 2016, Locke In Your Success
MACDHistogramCrossover
MACDTwoLines
MACDWithPrices
MARK 36.425 1.135
MARKET_CAP 224,087 M N/A
MARK_CHANGE -0.225 -0.18
MARK_PERCENT_CHANGE -0.61% -13.69%
MARK_PERCENT_UNDERLYING 100.00% 3.12%
MAX_COVERED_RETURN N/A 18.74%
MESASineWave
MRKT_MKR_MOVE ±0.382 N/A
MT_NEWS * *
MajorGannLevels
MarkerIndicator
MarketForecast
Marubozu
MassIndex
MatHold
MatchingLow
McClellanOscillator
McClellanSummationIndex
MedianAverage
MedianPrice
MeetingLines
MktFacilitationIdx
Momentum
MomentumCrossover
MomentumPercent
MomentumSMA
MoneyFlow
MoneyFlowIndex
MoneyFlowIndexCrossover
MonkeyBars
MorningDojiStar
MorningStar
MovAvgAdaptive
MovAvgEnvelope
MovAvgExpRibbon
MovAvgExponential
MovAvgTriangular
MovAvgTwoLines
MovAvgWeighted
MovingAvgCrossover
MultiCurrencyCorrelationOsc
NET_CHANGE -0.225 -0.18
26 Copyright © 2016, Locke In Your Success
NegativeVolumeIndex
Next3rdFriday
OPEN 36.65 1.13
OPEN_INT 0 24666
OPTION_VOLUME_INDEX 0.473 N/A
OnBalanceVolume
OnNeck
OnsetTrendDetector
OpenInterest
OptionDelta
OptionGamma
OptionRho
OptionTheta
OptionVega
PE 38.4 N/A
PERCENT_CHANGE -0.61% -13.74%
PERCENT_IN_THE_COLUMN N/A 1.18%
PERCENT_OUT_THE_MONEY N/A 0.00%
POSITION_N_L - -
POSITION_QTY - -
PPS
PROB_OF_EXPIRING N/A 53.22%
PROB_OF_TOUCHING N/A 86.48%
PROB_OTM N/A 46.78%
PUT_CALL_RATIO 1.124 N/A
PUT_VOLUME_INDEX 0.495 N/A
P_L_DAY - -
P_L_OPEN - -
P_L_PERCENT - -
P_L_YTD - -
PairCorrelation
PairRatio
ParabolicSAR
ParabolicSARCrossover
PercentChg
PercentR
PersonsPivots
PiercingLine
PolarizedFractalEfficiency
PolychromMtm
PositiveVolumeIndex
PriceActionIndicator
PriceAndVolumeTrend
PriceAverageCrossover
PriceChannel
27 Copyright © 2016, Locke In Your Success
PriceOsc
PriceRatio
PriceVolumeRank
PriceZoneOscillator
ProjectionBands
ProjectionOscillator
QStick
QUOTE_TREND instrument=T instrument=.T160318C36
RHO 0 0.0173
ROC N/A N/A
ROR N/A N/A
RSI
RSICrossover
RSquared
RainbowAverage
RandomWalkIndex
RangeBands
RangeIndicator
RateOfChange
RateOfChangeCrossover
Ray
RayBearPower
RayBullPower
RelativeMomentumIndex
RelativeRangeIndex
RelativeStrength
RelativeVolatilityIndex
RelativeVolumeStDev
ReverseEngineeringMACD
ReverseEngineeringRSI
RibbonStudy
RisingThreeMethods
SHARES 6152000000 N/A
STARCBands
STOCK_BETA 0.3085 N/A
STRENGTH_METER Range Bound N/A
STRIKE N/A 36
SVEPivots
SVEZLRBPercB
SYMBOL T .T160318C36
SectorRotationModel
SemiCupFormation
SentimentZoneOscillator
SeparatingLines
SeriesCount
28 Copyright © 2016, Locke In Your Success
ShootingStar
SideBySideWhiteLines
SimpleMovingAvg
Spearman
SpectrumBars
Spreads
StandardDevChannel
StandardDeviation
StandardError
StandardErrorBands
StandardErrorChannel
StickSandwich
StochRSI
StochasticCrossover
StochasticFast
StochasticFull
StochasticMomentumIndex
StochasticSlow
StressIndicator
SwamiAccDist
SwamiConvolution
SwamiIntradayFisher
SwamiIntradayImpulse
SwamiIntradayVolume
SwamiLaguerreTrend
SwamiMarketMode
SwamiPredict
SwamiRelativePerformance
SwamiSwingWave
SwamiVolatility
SwamiVolume
SwingIndex
SymbolRelation
TAC_ADX
TAC_DIMinus
TAC_DIPlus
TEMA
THETA 0 -0.009
TMV
TPOProfile
TRIX
TTM_LRC
TTM_Squeeze
TTM_Trend
TTM_Wave
29 Copyright © 2016, Locke In Your Success
TheoreticalOptionPrice
ThreeBlackCrows
ThreeInsideDown
ThreeInsideUp
ThreeLineStrike
ThreeOutsideDown
ThreeOutsideUp
ThreeStarsInTheSouth
ThreeWhiteSoldiers
Thrusting
TimeSeriesForecast
TradeVolumeIndex
TrendNoiseBalance
TrendPeriods
TrendQuality
TriStar
TrueRangeIndicator
TrueRangeSpecifiedVolume
TrueStrengthIndex
TwoCrows
TypicalPrice
UlcerIndex
UltimateOscillator
UniqueThreeRiverBottom
UniversalOscillator
UpsideGapThreeMethods
UpsideGapTwoCrows
UpsideTasukiGap
VEGA 0 0.0458
VOLUME 10171530 76
VOL_DIFF 3.32% N/A
VOL_INDEX 21.56% N/A
VWAP
VariableMA
VerticalHorizontalFilter
VolatilityStdDev
VolatilitySwitch
VolumeAccumulation
VolumeAvg
VolumeFlowIndicator
VolumeOsc
VolumeProfile
VolumeRateOfChange
VolumeWeightedMACD
VolumeZoneOscillator
30 Copyright © 2016, Locke In Your Success
VortexIndicator
WEIGHTED_BACK_VOL 19.72% N/A
WeaknessInAStrongTrend
WeightedClose
WildersSmoothing
WilliamsAD
WilliamsAlligator
WilliamsPercentR
WoodiesPivots
YIELD 5.27% N/A
ZigZagTrendPercent
ZigZagTrendSign

Data Export Fields on April 1, 2014 (qty 69)

TOS Export Field T .T160318C36

52HIGH 36.82 N/A
52LOW 30.97 N/A
ASK 36.87 1.43
ASKX 36.87 N 1.43 X
ASK_SIZE 308 247
AX N X
BACK_EX_MOVE ±0.863 N/A
BACK_VOL 20.16% N/A
BA_SIZE 177 x 308 139 x 247
BETA 0.3085 N/A
BID 36.86 1.39
BIDX 36.86 N 1.39 I
BID_SIZE 177 139
BX N I
CALL_VOLUME_INDEX 0.959 N/A
CLOSE 36.53 1.12
COVERED_RETURN N/A 12.56%
DELTA 1 0.6404
DESCRIPTION AT&T INC COM T 100 MAR 16 36 CALL
DIV 0.48 N/A
DIV_DATE
DIV_FREQ Q --
DT
EPS 0.9485 N/A
EXCHANGE COMPOSITE COMPOSITE
EXPIRATION N/A MAR 16
EXTRINSIC N/A 0.545
EX_MOVE_DIFF ±0.45 N/A
FRONT_EX_MOVE ±0.737 N/A
FRONT_VOL 55.97% N/A
GAMMA 0 0.1768
HIGH 37.04 1.5

31 Copyright © 2016, Locke In Your Success

 HTB_ETB ETB
IMPL_VOL 19.74% 18.30%
INTRINSIC N/A 0.865
LAST 36.865 1.38
LASTX 36.865 N 1.38 Q
LAST_SIZE 100 4
LOW 36.53 1.25
LX N Q
MARK 36.865 1.41
MARKET_CAP 226,793 M N/A
MAX_COVERED_RETURN N/A 12.56%
MRKT_MKR_MOVE ±0.722 N/A
MT_NEWS * *
NET_CHANGE 0.335 0.26
OPEN 36.59 1.25
OPEN_INT 0 24606
OPTION_VOLUME_INDEX 1.119 N/A
PE 38.87 N/A
PERCENT_CHANGE +0.92% +23.21%
PROB_OF_EXPIRING N/A 60.20%
PROB_OF_TOUCHING N/A 73.66%
PUT_CALL_RATIO 1.276 N/A
PUT_VOLUME_INDEX 1.288 N/A
RHO 0 0.0207
ROC N/A N/A
ROR N/A N/A
SHARES 6152000000 N/A
STRENGTH_METER Range Bound N/A
STRIKE N/A 36
SYMBOL T .T160318C36
THETA 0 -0.0075
VEGA 0 0.0465
VOLUME 32228662 209
VOL_DIFF 35.81% N/A
VOL_INDEX 19.74% N/A
YIELD 5.21% N/A

* For those that have read this far, thank you. Did you notice how clever it was to modify the RTD
equation the way we did in Part 2 so we could simply copy & paste it all over the place in Part 3?

/insert pat on the back/

32 Copyright © 2016, Locke In Your Success

 Connecting ThinkOrSwim to Excel
Part 4 - Finale

If you go back to Part 1, the very first sentence you read was:

You can’t beat a spreadsheet for turning big chunks of data into actionable information.

Over the course of this article, we’ve reviewed different mechanisms which allow ThinkOrSwim to
communicate with Excel, written equations that use RTD, learned a bit about how to manipulate data
with Excel, and uncovered a raft (maybe even a boatload) of live and streaming information we could be
getting in Excel via ThinkOrSwim.

So, really, the goal of “Connecting ThinkOrSwim to Excel” was accomplished in Part 3. Well done! Pats
on the back all around!

In this series conclusion, we’re going to clean up the LockeRTD spreadsheet, then turn a chunk of live,
streaming ThinkOrSwim data into actionable information.

(Before we begin, bring up ThinkOrSwim and the LockeRTD Excel spreadsheet you saved last week.)

Clean-Up
Except for the updated values, here’s how we last left LockeRTD:

It’s ok but check out all the inapplicable option information for RUT in row 2. If we’re going to simply
copy & paste the formulas, letting Excel do all the heavy lifting, let’s put a little intelligence into the
equation so it only displays options data if the symbol entered in column B is actually an option.

Yes, it’s faster and easier to make it look good by simply deleting the equations from H2 to N2 but that
reduces flexibility. What does that mean? It means you couldn’t just add a symbol into column B, willy-
nilly, and let Excel do the work. You’d have to copy the equations in columns H through N only when
you enter an option in column B, then delete them when you change the symbol from an option to a
stock.

Ugh. Too much work. So let’s think like a contortionist and insert some flexibility into this act.

Pop Quiz: What’s the big difference in the symbol that separates options from everything else?

If you said, “the dot at the first position of the symbol,” good thinking! That means if there’s a dot in the
leftmost position, we want the option data and if there’s not a dot in the leftmost position, we don’t
want RTD to do anything.

33 Copyright © 2016, Locke In Your Success

The first offender is cell H2, so let’s change the equation in H2 from this:

=RTD("TOS.RTD",,H$1,UPPER($B2))

To this:

=IF(LEFT($B2,1) = ".", RTD("TOS.RTD",,H$1,UPPER($B2)), "")

The new part of the equation is in red, so you can see what was added.

Breaking it down:

 IF is a logical function that will return the first value if the condition is true and the second value
if the condition is false. Since it’s a function, like RTD or UPPER, notice that what follows – the
parameters – is enclosed in parenthesis.

 LEFT($B2,1) is the LEFT function. The LEFT function returns some number of leftmost characters
from the beginning of some text. In this case, we want LEFT to return 1 character starting at the
leftmost character of the contents in cell B2. (Remember, we used $B to “pin” the column to B.)

 IF(LEFT($B2,1) = ".", is the LEFT function inside the IF function. What it means is we want to look
at the leftmost 1 character in cell B2 (it’s the R from RUT) and if it’s a dot (.), then do what
follows the 1st comma OTHERWISE do what follows the 2nd comma.

1. What follows the 1st comma is the RTD equation we’ve been using. You already know
how it works but, if you don’t remember, go back to Part 2.

2. What follows the 2nd comma is "". Those two double straight quotes (not curly “smart
quotes” because, of course, they’re different ASCII characters) next to each other are
interpreted as “nothing – do nothing at all.”

Bottom Line: The equation can be translated to “If the first character in cell B2 is a dot, it must be an
option so go get its EXPIRATION from ThinkOrSwim…otherwise, do nothing.”

After you modify the equation in H2 and press <Enter>, you should see:

H2 is blank, which is exactly what we wanted. Copy & paste H2 over H2 through N3 (following the
instructions in Part 3) and you’ll see:

Much easier to read!

Now copy row 3 to rows 4 and 5 (revisit Part 3, More Option Data if you don’t remember how) for the
next step.

34 Copyright © 2016, Locke In Your Success

Working with Complex Options
Let’s build a Butterfly!

I’m already using a RUT APRIL 1000 Put so we’ll add a 950 as the center and a 900 as the lower leg. I
could certainly return to TOS to copy & paste the option code into column B but, since the symbol for
1000 is already there, it’s easier to simply edit B4 and change the 1000 to 950, then change the 1000 to
900 in B5:

Add column headers and quantities so column C and D looks like this:

Enter this equation in cell D2 to find the total deltas for the quantity of individual options:

=IF(AND(LEFT($B2,1)=".",C2<>0),C2*J2*10, "")

Hey! That’s similar to what we did in H2 to N5, isn’t it? Let’s break it down:

 The AND function will result in TRUE if all the parameters passed to it are true. In this case:

o LEFT($B2,1)= "." will return TRUE if the first character of the symbol (column B) is a dot
(in other words, column B is an option)

o C2<>0 will return TRUE if there’s a number other than 0 in column C (we named that
column Qty)

o If you follow this logic, the AND function will return FALSE in row 2 because RUT isn’t an
option and, incidentally, there isn’t a quantity in column C. It will, however, return TRUE
for the next 3 rows because they’re options and there is some number other than 0 for
quantity.

 C2*J2*10 multiplies the quantity times the option delta, for the total number of deltas
ThinkOrSwim is holding for that option, then multiplies the result by 10 so deltas are consistent
with our other option modeling tools (like OptionVue or ONE).

 "" is, of course, nothing (as in, “do nothing if it isn’t an option”)

The equation can be translated to, “If there’s an option in column B of this row, and there’s some
quantity, multiply the quantity times the current delta supplied by ThinkOrSwim times 10 (qty*delta*10)
…otherwise, do nothing.”

35 Copyright © 2016, Locke In Your Success

Copy cell D2 into cells D3, D4 and D5. Your spreadsheet should look like this:

Let’s add the total number of deltas ThinkOrSwim has for the Butterfly by placing this equation into D6:

=SUM(D2:D5)

Add a top border to the cell to make it look nice (go to Excel’s Home | Font and look for Border):

Now it’s easy to see the current price for RUT and how it’s changed since the previous trading day. Ditto
for all our Butterfly options, plus other interesting stuff (like the greeks), including the total deltas our
Butterfly is holding (according to ThinkOrSwim).

Conclusion
Many trades in the Locke In Your Success programs depend on taking action based on the greeks, like
when delta is too much or ratios exceed their proscribed limit. You can use the powerful tools within
Excel (like conditionally formatting the delta sum to be red if it’s over 100) to alert you when the
ThinkOrSwim greeks are telling you it’s time to look at adjusting.

The example here was simple, summing Butterfly deltas, but imagine the possibilities:

 Compare synthetic pricing for verticals

 Choose which of many covered writes best fits your goals

 Decide which pattern of adjustments most keep your greeks under control

 Summarize and track multiple existing trades all on one screen

In general, you can quickly summarize a mountain of live, streaming data so you’re able to prove or
disprove theories, and move faster while making better decisions. The possibilities are limited only by
your imagination.

You’ve started down the trail of how to turn big chunks of data into actionable information. Enjoy your
journey!

(If you’d like, you can download the completed LockeRTD Excel file from www.LockeInYourSuccess.com.
It has everything you did plus a little more.)

36 Copyright © 2016, Locke In Your Success