Ever wonder how to connect TD Ameritrade’s thinkorswim (TOS) desktop trading platform with your Excel spreadsheet? How about how to use the thinkorswim RTD commands to live stream data into your Excel Watch-List?
The whole is greater than
– Aristotle
the sum of its parts
Commentary
This idiom is proven true when considering the complexity of a mechanical clock, a robot, or an airplane. It is also true when I combine TD Ameritrade’s Think or Swim trading platform with Microsoft Excel to make a kick-ass Watch List and Open Positions Monitor system.
In this week’s Journal Entry, I will walk through the steps of configuring a new laptop to run both my TD Ameritrade ThinkorSwim (TOS) desktop platform and my custom-configured Excel watchlist spreadsheet.
It is easy to have ThinkOrSwim live stream to Excel, but it is not well documented on how to do it. ThinkOrSwim (aka thinkorswim, aka ToS) takes full advantage of Microsoft’s RTD to simplify the effort.
If anyone is interested, for a small donation,
they can download a strip-down and unsupported version
of my Excel Watchlist.
Table of contents
New Laptop
I want a second “disposable” laptop to take with me to remote locations (such as pubs and parks) and continue to work on my trading plans. The second laptop needs to have the identical platform configuration as my office trading workstation, robust security, and low cost. It also needs to have complete data synchronization between the two computers – whatever I start on one system, I want to complete on the other.
Configuration
- Dell Inspiron 7306 2n1
- i5 10th Gen
- 8 GB Mem
- 512 GB Drive
- Fingerprint scanner
- Windows 10 Home
- Microsoft Office 365
Microsoft OneDrive
OneDrive is Microsoft’s cloud storage service that is free if you have Microsoft 365. It is not needed to get the most out of TOS but is necessary for my Excel watchlist. As long as I save the Excel spreadsheet in my OneDrive folder, then I can watch and edit it on both my workstations.
About Laptop Security
Being my “disposable” / “take with me anywhere” laptop, I need to be confident about security. I need to make sure I:
- Activate the fingerprint scanner
- Set a complicated Windows password/pin
- Set the computer to sleep when the lid is close
- Set the computer to require re-authentication when waking from sleep mode
- Enable Find My Device
If my laptop turns up missing, I can log in to my Microsoft Account from any other system and lock/disable the missing device.
TD Ameritrade ThinkorSwim (TOS)
Here is how to find, download, and install Think or Swim on my new laptop. Once installed, I will test the desktop software with my personal Ameritrade account to ensure all is right.
Prerequisite
This post assumes that I already have a TD Ameritrade brokerage account. And that I have already worked with the good folks at Ameritrade to set my account for Options Trading and approved for Standard Margin.
Note: HIGHLY RECOMMENDED that I have a dedicated Options trading account – separate from any of my buy/hold accounts. Doing so works as a firewall, so uncontrolled losses or wayward Options positions will not bleed my savings. The money deposited in the Options trading account will only be what I am willing to lose.
Installing ThinkorSwim
1. Log into my TD Ameritrade account.
2. In the “Search” box, type “thinkorswim”
3. Click the link “Get thinkorswim”
4. Scroll down the page until you see:
5. Click it…
6. Wait until the download is completed
7. Click “Open File” to start the installation
Waiting… Waiting…
8 . Select language – click “OK”
9. Click “Next >”
- Select your account provider (“TD Ameritrade”) then click “Next >”
11. Agree to the legal stuff, then click “Next >”
12. Make the selection you want. I selected “for all users” because I am the “ALL” for my laptop.
13. Click “Next >”
14. Click “Yes” to allow thinkorswim to be installed.
15. Take the default location to install the software. If you want to put the files somewhere else, now is the time to do it…
16. Click “Next >”
17. Some cosmetic choices. Select what you want and click “Next >”
The software installation status box zipped by way too fast for me to capture a screenshot. So don’t blink.
17. When software is completed, click “Finish”
Start Thinkorswim for the First Time
Find the “thinkorswim” icon on the desktop and double-click to start.
Whenever you start thinkorswim, it will reach out to the servers to download any updates or patches automatically. Doing so requires you to approve installing these updates
Click “Yes”
The first time you start TOS, it will download the bulk of the software. How fast this happens will depend on the speed of your Internet. This extended download is a one-time snoozer, so now is an excellent time to take a coffee break…
Moment of truth…
Enter your username and password, then click “Log in.”
Initializing your desktop…
Voila!!!
Microsoft Excel
Microsoft Excel provides a function called RTD (RealTimeData). This function allows for direct communications between running applications. In this situation, Excel will use RTD functions to retrieve live data from thinkorswim.
Configure Excel to Retrieve RTD from TOS
The really cool thing about this particular configuration is – it comes ready to go right out of the box. There is no need to register COM Objects, set up libraries, or such like in the old days. I don’t need to be a nerd to make this work.
The only requirement to make this work is to have both TOS and Excel running simultaneously. (For Excel to retrieve live data from TOS, TOS needs to be running to serve.)
Retrieve RTD Live Data From TOS
I do not intend for this post to be detailed documentation on using the TOS/RTD functions, but I do what to show how to get started.
The Excel/RTD syntax for requesting data from TOS is to enter the formula,
“=RTD(“tos.rtd”,, “[COMMAND]“,”[TICKER]“)”, into an Excel cell. It’s very straightforward.
Below is an image showing some of the RTD commands I can use right away. There are hundreds of these commands offered by thinkorswim.
TOS/RTD Commands
There are several hundred TOS/RTD commands that I can use to create my kick-ass Watch List. But it is not clear how to find them. This section will point to some commands.
Abbreviated Table of TOS/RTD Commands
| Ask | =RTD(“tos.rtd”,,”ASK”,[Options Code / Ticker]) |
| Ask Size | =RTD(“tos.rtd”,,”ASK_SIZE”,Options Code / Ticker) |
| AskX | =RTD(“tos.rtd”,,”AX”,[Options Code / Ticker]) |
| Back Expected Move | =RTD(“tos.rtd”,,”BACK_EX_MOVE”,[Options Code / Ticker]) |
| Back Vol | =RTD(“tos.rtd”,,”BACK_VOL”,[Options Code / Ticker]) |
| Beta | =RTD(“tos.rtd”,,”BETA”,[Options Code / Ticker]) |
| Bid | =RTD(“tos.rtd”,,”BID”,[Options Code / Ticker]) |
| Bid Ask Size | =RTD(“tos.rtd”,,”BA_SIZE”,[Options Code / Ticker]) |
| Bid Size | =RTD(“tos.rtd”,,”BID_SIZE”,[Options Code / Ticker]) |
| BidX | =RTD(“tos.rtd”,,”BX”,[Options Code / Ticker]) |
| Call Volume Index | =RTD(“tos.rtd”,,”CALL_VOLUME_INDEX”,[Options Code / Ticker]) |
| Change | =RTD(“tos.rtd”,,”NET_CHANGE”,[Options Code / Ticker]) |
| Close | =RTD(“tos.rtd”,,”CLOSE”,[Options Code / Ticker]) |
| Covered Return | =RTD(“tos.rtd”,,”COVERED_RETURN”,[Options Code / Ticker]) |
| Delta | =RTD(“tos.rtd”,,”DELTA”,[Options Code / Ticker]) |
| Description | =RTD(“tos.rtd”,,”DESCRIPTION”,[Options Code / Ticker]) |
| Dividend Share | =RTD(“tos.rtd”,,”DIV”,[Options Code / Ticker]) |
| Dividend Yield | RTD(“tos.rtd”,,”YIELD”,[Options Code / Ticker]) |
| EPS | =RTD(“tos.rtd”,,”EPS”,[Options Code / Ticker]) |
| Expected Move Diff | =RTD(“tos.rtd”,,”EX_MOVE_DIFF”,[Options Code / Ticker]) |
| Extrinsic | =RTD(“tos.rtd”,,”EXTRINSIC”,[Options Code / Ticker]) |
| Front Expected Move | =RTD(“tos.rtd”,,”FRONT_EX_MOVE”,[Options Code / Ticker]) |
| Front Vol | =RTD(“tos.rtd”,,”FRONT_VOL”,[Options Code / Ticker]) |
| Gamma | =RTD(“tos.rtd”,,”GAMMA”,[Options Code / Ticker]) |
| High | =RTD(“tos.rtd”,,”HIGH”,[Options Code / Ticker]) |
| High52 | =RTD(“tos.rtd”,,”52HIGH”,[Options Code / Ticker]) |
| Implied Volatility | =RTD(“tos.rtd”,,”IMPL_VOL”,[Options Code / Ticker]) |
| Intrinsic | =RTD(“tos.rtd”,,”INTRINSIC”,[Options Code / Ticker]) |
| Last | =RTD(“tos.rtd”,,”LAST”,[Options Code / Ticker]) |
| Last Size | =RTD(“tos.rtd”,,”LAST_SIZE”,[Options Code / Ticker]) |
| LastX | =RTD(“tos.rtd”,,”LX”,[Options Code / Ticker]) |
| Low | =RTD(“tos.rtd”,,”LOW”,[Options Code / Ticker]) |
| Low52 | =RTD(“tos.rtd”,,”52LOW”,[Options Code / Ticker]) |
| Mark | =RTD(“tos.rtd”,,”MARK”,[Options Code / Ticker]) |
| Mark Change | =RTD(“tos.rtd”,,”MARK_CHANGE”,[Options Code / Ticker]) |
| Mark Change | =RTD(“tos.rtd”,,”MARK_CHANGE”,[Options Code / Ticker]) |
| Market Cap | =RTD(“tos.rtd”,,”MARKET_CAP”,[Options Code / Ticker]) |
| Mark Percent Change | =RTD(“tos.rtd”,,”MARK_PERCENT_CHANGE”,[Options Code / Ticker]) |
| Mark Percent Change | =RTD(“tos.rtd”,,”MARK_PERCENT_CHANGE”,[Options Code / Ticker]) |
| Max Covered Return | =RTD(“tos.rtd”,,”MAX_COVERED_RETURN”,[Options Code / Ticker]) |
| Open | =RTD(“tos.rtd”,,”OPEN”,[Options Code / Ticker]) |
| Open Interest | =RTD(“tos.rtd”,,”OPEN_INT”,[Options Code / Ticker]) |
| Option Volume Index | =RTD(“tos.rtd”,,”OPTION_VOLUME_INDEX”,[Options Code / Ticker]) |
| PE | =RTD(“tos.rtd”,,”PE”,[Options Code / Ticker]) |
| Percent Change | =RTD(“tos.rtd”,,”PERCENT_CHANGE”,[Options Code / Ticker]) |
| Probability ITM | =RTD(“tos.rtd”,,”PROB_OF_EXPIRING”,[Options Code / Ticker]) |
| Probability OTM | =RTD(“tos.rtd”,,”PROB_OTM”,[Options Code / Ticker]) |
| Probability Touch | =RTD(“tos.rtd”,,”PROB_OF_TOUCHING”,[Options Code / Ticker]) |
| Put Call Ratio | =RTD(“tos.rtd”,,”PUT_CALL_RATIO”,[Options Code / Ticker]) |
| Put Volume Index | =RTD(“tos.rtd”,,”PUT_VOLUME_INDEX”,[Options Code / Ticker]) |
| Rho | =RTD(“tos.rtd”,,”RHO”,[Options Code / Ticker]) |
| Shares | =RTD(“tos.rtd”,,”SHARES”,[Options Code / Ticker]) |
| Strength Meter | =RTD(“tos.rtd”,,”STRENGTH_METER”,[Options Code / Ticker]) |
| Theta | =RTD(“tos.rtd”,,”THETA”,[Options Code / Ticker]) |
| Vega | =RTD(“tos.rtd”,,”VEGA”,[Options Code / Ticker]) |
| Vol Diff | =RTD(“tos.rtd”,,”VOL_DIFF”,[Options Code / Ticker]) |
| Vol Index | =RTD(“tos.rtd”,,”VOL_INDEX”,[Options Code / Ticker]) |
| Volume | =RTD(“tos.rtd”,,”VOLUME”,[Options Code / Ticker]) |
| Weighted Back Vol | =RTD(“tos.rtd”,,”WEIGHTED_BACK_VOL”,[Options Code / Ticker]) |
Copy/Paste TOS Data to Excel
Another niffy way to discover what RTD command to use in my Excel spreadsheet is to export data from TOS directly into Excel.
- Click the “Scan” tab at the top
- Apply a simple filter
- Click “Scan”
- At the near upper right corner of TOS, click the “Options Menu”
- Click “Export >”
- Click “To Microsoft Excel”
and “Paste”
Once the scanned data from TOS is loaded in the Excel sheet, click on any cell and look at the formula used in the formula bar.
Where Can I Find More?
Searching both high and low inside the TOS desktop platform has not yielded any good sources for the tos.rtd commands. The closes thing I found to comprehensive documentation of tos.rtd is from www.stock-data-solutions.com.
If any finds the “definitive” documentation for tos.rtd, please leave me a note below.
Application – My Custom Watchlist & Open Positions Monitor
Great tools are useless without a great application. To get started on using ThinkorSwim/Excel, consider reviewing my posts for creating a custom watchlist and open spread positions monitor –
Watchlist for Vertical Spreads
Custom Options Watchlist using Thinkorswim in Excel – PT 1
Custom Options Watchlist using Thinkorswim in Excel – PT 2
Watchlist for Cover Calls
Cover Calls WATCHLIST Using ThinkorSwim EXCEL
Was This Post Helpful?
Although blogging is great fun, any donation will let me know if I am actually helping someone else.
(Plus, it helps pay to keep my website active 😉)
Choose an amount
Or enter a custom tip
Your contribution is appreciated – Damocles
DonatePrevious Post from OptionsTradesByDamocles
Conclusion
Using Excel with thinkorswim is not hard to setup.
Disclaimer
Even though I have tried to make it clear that this blog is my journal, documenting my trek into Options Trading, it has been suggested by others that I, nevertheless, include a general disclaimer. So here goes…
“This blog and the information contained herein is not intended to be a source of advice or analysis concerning the material presented. The information and/or documents contained in the blog do not constitute investment advice.”
Contact Me
If you want to comment on this post’s topic, please use the “Leave a Reply” box below so it can be attached to the post for future reference. – Thanks
Hi:
I just downloaded your watchlist excel program. It looks terrific except when I put in a vertical bull put spread it came up with the short and long reversed. The long was higher than the short. If I made the spread negative it reversed the position, but the short was higher than the strike price. Your directions included a sheet where you put in the number of contacts negative and positive, but that column is missing from the sheet I downloaded. I know the sheet is unsupported, but you’ve clearly put a tremendous amount of work in it and I wanted to alert you.
Bob
Greetings Bob. Thank you so much for your feedback.
First, I’m not an Excel guru or an expert on ToS. So please take this response with a grain of salt. Also, please look at this spreadsheet only as a reference for your own awesome Watchlist.
I just downloaded the file to see if I can discover the issue you described. But everything seems to work for me. It is hard to tell where the spreadsheet is going awry without looking at your implementation. But please consider the drop-down menu in cell B51. The Short and Long legs can be reversed if it is not set to the intended Spread.
I hope this helps in some ways, and I wish you success in your trading efforts.
Thanks for your wonderful article on TOS RTD, we really appreciate it and learnt alot from it.
The article touched on how to get best bid/ask price and size but I am also trying to get the position of the remaining bid/ask prices and sizes, not just the best ones. E.g
Bid 1 / Ask 1
Bid 2 / Ask 2
Bid 3 / Ask 3 and so on
Do you happen to know how we can get this via TOS RTD into excel? Or do you happen to know anyway to achieve this in Excel?
Many thanks
Joy
Greeting Joy, and thank you for your kind words.
First, I am not that knowledgeable in TOS or Excel. But, if I understand what you are asking, you can retrieve those matrixes via RTD (please reference my posts CUSTOM OPTIONS WATCHLIST USING THINKORSWIM IN EXCEL – PT 1 & 2), use the Excel RANK function, then sort on the rank. I do this to list the best ARORs for this week’s Spreads prospects.
Good luck to you! 🙂
Is your watchlist available for download?
Not at this time.
Great job! I’m currently running a study (will be happy to share results if you’d like) on biotech penny stocks that have a T1 Halt (News Pending) where they get an FDA approval. I’ve done a few manually, but I’d like a much bigger sample size and would like to do this programmatically using Excel and TOS RTD, but need to query the TOS History Tick table by using the ticker symbol and ticker time as the predicates (WHERE clause) and pull the price of that stock at the datetime I feed the query. By doing this, I am pulling back the price one day prior to the halt, price at the halt, price at the resumption, the high that occurs for the remaining part of that trading day, how many seconds it took to reach the high, price at 1, 5, 10 and 15 minutes after resumption and the price 24 hours later. My hope is to get a sense of general behavior of penny biotech pricing as a result of FDA approvals. For instance so far, I’m noticing most experience the day’s high between 10 and 30 seconds after resumption – then fall back. Companies with strong financial scores and consistent product pipelines tend to experience a “second wind” upon market open or premarket the next day, while weaker companies with one hit wonders fall off the radar completely steadily but quickly in the days after getting juiced for just a few seconds (e.g. ELEV a few months back is a great example of the latter). But I just want a much larger sample size and this is the best way I know to do it.
Thanks for the feedback, and good luck with your study.
Hi Damocles
I am using Excel on Mac and the RTD function gives a #NA. Do you know if there is some tweaks needed for Mac Excels that is required to enale the RTD function for TOS to work? Thanks in advance
Hi Damocles
I use Excel on Mac and the RTD function on TOS returns a #NA. I am not sure if you can help or is aware of any tweaks I need to do to enable the function to work properly. Thanks in advance.
Greetings!
At first blink, it looks like a syntax error. If you can cut-n-paste your cell command, I will try it on my Windows version and see if I get the same error.
I double and triple-checked it. If i deliberately inserted a syntax error it returns a different error message. But even if I copied the syntax from a friends WIndows it showed the NA error
First, I am not that tech-deep in ToS and even less with Macs. So my advice is this: if all your attempts at any RTD functions return a #NA, your workstation configuration is likely out of whack. (A reference to the ToS RTD libraries is broken somewhere – either with Excel or ToS). Make sure everything is up-to-date (including MAC’s JAVA libraries).
(Note: in my Windows workstation, if I change “=RTD(“tos.rtd”,,”BID”,$E136)” to “=RTD(“ts.rtd”,,”BID”,$E136)” I get a #NA error since there are no references to a “ts.rtd” library.)
I hope this helps in some way – good luck!
Thanks for sharing your insight, this is easy to use and works nicely. Are there any TOS commands to import historical stock and option data into excel ?
Greetings Ron, Thank you for your kind words.
I’m not that deep into ThinkorSwim, so I’m not aware of a function to retrieve historical data in TOS short of writing a thinkScript program. But there are several Excel Add-ins/Templates that can pull market data. I haven’t researched any of these, but one you can look into is Yahoo Finance. Check out this site: https://www.tradinformed.com/how-to-download-price-data-from-yahoo-finance-into-excel/
Do you know if you can “feed” the earnings date for a security from TOS to excel?
First, I must confess that I am not that deep with ThinkorSwim/Excel. But ThinkorSwim has a dropdown box that will list all the available fields you can use with your Excel RTD commands. Open the “Scan” tab, click the top-most list icon (top right of the platform), select “Export,” then “Help on data export.”
Thank you. That’s something definitely helpful. Doesn’t appear I can accomplish easily what I want. You can put the ex-div date, that’s about it. Workaround is a watch list in TOS and click on the circles individually next to the symbol for the earnings dates.
Thanks again!
do you make your Excel chart available to use for free or purchase? I’d like to use it as a baseline for my own options tracking Excel sheet 🙂
My current spreadsheet is mired with old equations, what-ifs, and other doodlings that will make it way too much trouble to decipher. But the two post below is my documentation for recreating my Options Watchlist should I lose it. These posts were intended to be a cut-and-paste document.
Custom Options Watchlist using Thinkorswim in Excel – PT 1 – Options Trades by Damocles
Custom Options Watchlist using Thinkorswim in Excel – PT 2 – Options Trades by Damocles
Thank you for your message.
Hi
Thank you for your great comments and instructions. I was wondering if you know how to feed “margin required” field of ThinkorSwim from the “Account Statement” tab to import them into Excel spread sheet.
Kind regards,
Ali Daneshmand, NYC
Greetings Ali, thank you for your kind words.
After a brief look at the available RTD topics in the ThinkorSwim, I came up with nothing resembling my margin account balances. So, I will presume (at this point) that I cannot retrieve any personal account information from Ameritrade via my ThinkorSwim trading platform.
But a brief search on the Internet came up with “Money in Excel,” an Excel dynamic template that can import financial information from Ameritrade. Dissecting that could give you some good clues on how to get what you want.
Sorry for not being much help.