Connecting ThinkOrSwim to Excel-Part 3 of 4 - Locke In Your Success (2023)

Written and contributed by Rich Kaczmarek

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

In the last article, you found that there were RTD parameters passed from Excel to ThinkOrSwim (TOS), the 3^rd parameter was something TOS had to return and the 4^th 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 4^th 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 3^rd parameter.

The 3^rd 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.

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

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.

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!

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

ADDED May 2017! TOS Custom Scripts and Excel Instructions have been added to the Resources page in the Locke Options Community.

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

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

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

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

All Data Export Fields (quantity 432)

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

* 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/

Written and contributed by Rich Kaczmarek