Written and contributed by Rich Kaczmarek

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

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 1 st RTD parameter. It’s the quote-enclosed name of a ThinkOrSwim function that’s loaded into memory when you run ThinkOrSwim. RTD is much more robust and flexible than DDE. For instance, with DDE you need to 'hard-code' symbols and field names in your spreadsheet formulas. The Hoadley Finance Add-in for Excel on the other hand does not require the 'hard coding' of symbols and field names in formulas.

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

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

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.

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 1^st comma OTHERWISE do what follows the 2^nd
  1. What follows the 1^st 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 2^nd 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.

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 1000to 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*100, “”)

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:
  • 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)
  • C2<>0 will return TRUE if there’s a number other than 0 in column C (we named that column Qty)
  • 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*100 multiplies the quantity times the option delta, for the total number of deltas ThinkOrSwim is holding for that option, then multiplies the result by 100 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 100 (qty*delta*100) …otherwise, do nothing.”

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

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!

Excel For Mac Rtd Functions Think Or Swim Trunks

(If you’d like, you can download the complete instructions and completed LockeRTD Excel file from https://members.lockeinyoursuccess.com/resources/. It has everything you did plus a little more.)

Rtd In Excel

Written and contributed by Rich Kaczmarek