• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Locke In Your Success

Locke In Your Success

Learn how to create income from options trading

  • Home
    • About
    • Testimonials
    • Our Team
      • Sherri Locke
      • Stephen Hammit
      • John Locke
    • Contact
    • Privacy Policy
  • Coaching
    • Fundamentals In Trading Library
    • Options Trading for Income Weekly Webinar
    • GO Ask A Trader Sessions
    • Trading With The Pros
    • Trading Performance Sessions
    • Market Outlook Weekly Webinars
    • Personal Coaching Sessions
  • Become A Member
    • GO Membership
    • PRO Membership
    • Trading Performance Membership
    • Market Outlook Membership
    • Basic Membership
    • Membership Level Comparison
    • Trade Alert Services
  • Trading Courses
    • Options Trading Strategies
      • The Bull
      • Quick & Dirty Broken Wing Butterfly
      • The Super Bull
      • The Bear
      • Unbalanced Butterfly 1 and 2
      • M3.4u
      • V32
      • X4 System
      • Bearish Butterfly
      • M3
      • The ROCK
      • M21 System
    • Trading Performance Courses
      • Advanced Personal and Position Management
      • Broken Wing Butterfly Master Course
      • The Trading Triangle
      • Trader Transformation Workshop
      • Ultimate Income Trader Workshop
      • How To Best Utilize Calendar Spreads
    • Enriching Sessions
      • Performance Parking Portfolio
      • Boost Trading Results Through Proper Back Testing
      • Managing Risk When Entering, Exiting, or Rolling Positions
      • Far Out M3
      • How To Build Winning Trade Plans
      • Reading Your T+0 Line
      • Trading Lab – Design a Trade
      • Trading Lab – M3LT
      • ROCK’in 2018
      • Trading Lab – Roll Baby Roll Index
  • FREE Resources
    • Free Options Trading Consultation
    • Options Trading FAQ
    • Trading Performance Podcast
    • The Winning Trade
    • Successful Trader Presentations
    • TRADING SUCCESS BLUEPRINT
    • FREE Basic Membership
    • Calendar
    • Recommended Reading List
    • Brokerage Firm Selection Process
    • Fundamentals In Trading Definitions
    • Trader Inspiration
    • Financial Freedom Classroom
  • Blog
  • Log In

Connecting ThinkOrSwim to Excel-Part 1 of 4

February 16, 2016 by John Locke 2 Comments

Written and contributed by Rich Kaczmarek

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!

Blog 9_CW_coffee-beans_G12ITIYu

 

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 https://msdn.microsoft.com/en-us/library/aa140061(office.10).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.

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:

Blog 9_CW_TOS SS 1

 

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

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

Blog 9_CW_TOS SS 2

 

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

Written and contributed by Rich Kaczmarek

Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Pin on Pinterest
Pinterest
Email this to someone
email

Filed Under: Cynthia Sarver, Written blog post

Reader Interactions

Comments

  1. William Smith says

    February 18, 2016 at 3:37 am

    Rich – are you aware of a way to do this on a Mac? My understanding is that RTD doesn’t work on a Mac, and I’ve tried to figure it out without success.

    Log in to Reply
    • John Locke says

      February 19, 2016 at 3:41 pm

      We aren’t sure how it works on a Mac. Sorry…

      Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

Primary Sidebar

Recent Posts

  • The Winning Trade Episode 81
  • I DON’T KNOW
  • The Winning Trade Episode 80
  • Trading And Your Ego
  • The Winning Trade Episode 79

Categories

Search

Copyright © 2022 · Locke In Your Success, LLC. All rights reserved worldwide · Site Maintained by Nustart Solutions