Excel For Mac Rtd Functions Think Or Swim

Posted By admin  On 15.09.21

I was talking with a workmate of mine and he mentioned that he is a swimming coach.
He told me that it’s difficult to record swimming times in Excel.
His approach was to record Minutes, Seconds and Splits (Split Seconds) as separate columns. Now, I can only imagine what sort of frustrating (if not impressive!) formulas this led to.

  1. RealTimeToExcel allows loading real-time data from databases and Yahoo! Finance data into Microsoft Excel. Just use regular Microsoft Excel formulas.
  2. Here is a Days to Earnings Watchlist that I have been using for years. Hope this helps someone # TDE - Trading days til earnings # Displays the trading days til earnings. After-market earnings add 0.5 to the count. # This code is meant to be pasted into a custom watchlist column # # Author.

I explained to him that Excel can store Split Seconds right out of the box.
Just format the cell as mm:ss.00

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. Excel already allows you to define custom functions, but only by writing them in an entirely different language such as JavaScript. In contrast, LAMBDA allows you to define a custom function in Excel’s own formula language. Moreover, one function can call another, so there is no limit to the power you can deploy with a single function call. Click 'Install thinkorswim' above to automatically select the installer appropriate for your operating system and click 'Run'. The download may take anywhere from a couple of minutes to half an hour depending upon the speed of your Internet connection.

Great! That’ll work.

Being the thinker, he suddenly realised typing in the times would be even more difficult than before. It’s that fiddly colon key :

“Is there a way to type the times with a decimal point as a separator?” he asked.

Fair enough question. His times on paper are written as dot separated.

Fun! Let’s write a formula!

I figured out there were 3 formats of time:
23 which means 23 seconds
23.45 which means 23.45 seconds
1.23.45 which means 1 minute and 23.45 seconds
The hour portion of the time is never reached.

So for a time typed into A1, the following formula turns it into an Excel time.
=IF(LEN(A1) – LEN(SUBSTITUTE(A1, “.”, “”)) = 2, TIMEVALUE(“00:” & SUBSTITUTE(A1, “.”, “:”, 1)), TIMEVALUE(“00:00:” & A1))

Again, the cell has a custom format of mm:ss.00

Written and contributed by Rich Kaczmarek

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

Rtd

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)), “”)

Excel For Mac Rtd Functions Think Or Swim

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

Let’s build a Butterfly!

Excel For Mac Rtd Functions Think Or Swim Trunks

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

Excel For Mac Rtd Functions Think Or Swim Team

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

Excel

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 Suit

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

How To Use Rtd Excel

Written and contributed by Rich Kaczmarek