Ever wanted to put your Think or Swim watchlists into Excel? Ever wanted to import your price quotes into Excel? How about the impossible dream–get data streaming into Excel from Think or Swim? Today is your day. First off, we should thank the folks at Think or Swim for making the interface work so well. It’s amazing to me how simply and easily it works! Usually, more complicated means more powerful, but ToS blows that paradigm out of the water with a remarkable frequency. On to the tutorial!
Importing Watch Lists to Excel and Saving the Quote Data
To move a watchlist from Think Desktop into Excel, go to your “MarketWatch” tab, choose “Quotes” and pull up the watchlist. You can build your own, or choose from a lot of built-in lists by clicking the “gear” icon in the upper right:
From that menu, either create a new watchlist or scan query, or choose a public list like the S&P 500. Next, click the “printer” icon, and you’ll see an option to “Export”:
If you click “Export”, you can save the current list, symbols and associated data as a .csv file. Then you can open it in Excel or your favorite text editor and see your symbols and saved data values. Now, saving these watchlists is nice and all, but we want the Holy Grail: performing real time calculations in Excel on streaming data for multiple symbols at once.
Streaming Real-Time Quotes in Excel
There are actually two ways to get this working–one from Think Desktop itself, and the other from within Excel.
From Think Desktop:
To create a linked Excel file, simply go into Think Desktop to the “MarketWatch” tab, then “Quotes” like before. Choose the watchlist you want to get data for, or create a new one, same as above. Then click that “printer” icon again, and this time choose “Export to Excel” (you know, the one you wanted to click before, but you waited until now, right?). You’ll get a message that your data is ready to paste into Excel:
Now go to your Excel file (an existing one or create a new one), choose a cell and paste (ctrl+v). You will see a bunch of #N/A’s at first, then after a few seconds the streaming will kick in. Your watchlist is now in Excel and streaming data! It will keep doing so as long as Think Desktop is open and connected to Think or Swim. If you save the Excel file and close it, you can use it again later. The links will remain, but you have to open Think Desktop and connect to Think or Swim first. When the Excel file is opened, it will ask you if you want to update links from another workbook–it’s talking about the links to Think Desktop. Click “Yes” or the data won’t update. And now you know the easy Think Desktop “Export to Excel” method!
From Inside of Excel:
The data linkage to Think Desktop is done through an Excel capability called Dynamic Data Exchange (DDE). Basically, you just enter a special formula in a cell with certain arguments and Excel does the rest. This is the way you can create a linked file from outside of Think Desktop. (Think Desktop actually does the same thing when you click “Export to Excel”–it builds these formulas for you according to how your “quotes” screen is set up and puts them on the clipboard ready to paste in Excel.)
To get the last price of GOOG, you would enter this formula into a cell:
=TOS|LAST!’GOOG’
“TOS” is an identifier for the Think Desktop program that is running (which is not coincidentally named “TOS.exe”). The “|” (the pipe character) is a separator that tells Excel that “TOS” is a program to use DDE with. The “LAST” is an available data field in Think Desktop; there are 70 different fields you can use currently, which can be seen by clicking the “printer” icon and choosing “DDE Help”:
Next in the formula, you put in a “!” as another separator, and then the string ‘GOOG’ tells Excel what symbol to get the data for. That’s it! As another example, to get today’s volume for AAPL, you would use the formula:
=TOS|VOLUME!’AAPL’
When you know this syntax, you can build your own quote sheet from scratch without even exporting from Think Desktop. Just type in the formula, and as long as Think Desktop is running and connected to Think or Swim servers, the link will just work. Amazingly awesome if you asked me.
So What?
You may be thinking, “So what’s the big deal? I can watch quotes from Think Desktop.” Well, here’s just a couple of examples to give you an idea of what you could do with a tool like this:
You could calculate a volume-weighted average put/call ratio for every stock in the Russell 3000 in real time.
You could show the average bid/ask spread for every stock in the S&P 500, watching the number for when average spreads widen or contract for a sense of broad market liquidity.
The possibilities are almost endless!
I’ve created a couple of Excel files that can help get you started. These files are freely available (though donations are appreciated) on my Google site, under the “Released Tools” section, in “TOS_Excel_DDE.zip”. The first file, “TOS_DDE_Examples”, has the two examples mentioned above. The second file, “TOS_DDE_Template” is a blank template sheet ready for you to enter your tickers, choose your fields from the drop downs, click “Generate” and go! In general, I’d recommend running no more symbols, fields or files than necessary to minimize processor and network overhead. Also, as in the examples file, I would separate the sheets that do the importing and the sheets that do any of your calculations.
Coming Soon: Complex Analysis for Tool (CAT) for Using Think or Swim Data in Excel
But wait!! There’s more! The tool above only shows you the most recent data values. Using some macros and other trickery we can save the historical data and calculate complex values back through time for every symbol in your watchlist all at the same time. In addition, we can use VBA and other controls to add capability for alerts, whether through a cell color change, a pop-up alert box, or even sending an email.
I’m working on I’ve released a Complex Analysis Tool (CAT) for my blog donors to do all of this. What could you do with this tool? For example:
You could watch for the highest value of RSI(10) for every stock in the S&P500 on a 5 minute bar timeframe and pop-up an alert when one breaches 90%.
You could watch for the top 5 stocks with the biggest change in volume over the last 10 minutes.
You could set an alert for every stock in the Dow, sending you an email when any stock touches its own daily pivot level.
You could calculate a real-time TICK for the S&P 500 for use with the ES futures.
The possibilities are even more almost endless!
This tool is not yet finished! But when the CAT tool is ready, I’ll make it available to blog donors only over on my Google site, in “Released Tools”, in the “Donors Only” folder. If you want to donate, just hit my Paypal link and throw me a Jackson ($20 for those from out of town). Or a Franklin, I’m not picky 😉
If you are interested in having me make a fully custom CAT tool for you according to your own specifications, send me an email for a price quote!
As always, questions are free for the do-it-yourself crowd, whether you’re trying to learn how to tweak my files for your own needs or build one of your own from scratch. Just leave a comment, I’m always glad to answer questions.
Tags: alerts, DDE, excel, quotes, streaming, vba, watchlists
Read the Prospectus 
September 6, 2009 at 8:54 pm
Thanks for the excellent post! I was totally oblivious of this capability until I read your post. Just wondering if its possible to record open and closing prices of every 5min price bar using this method? I browsed the fields provided in dde help and it seems there isn’t any easy way to do this….
Regards
bryan
September 6, 2009 at 9:27 pm
Bryan,
I don’t see an easy way to store historical values currently, but my CAT tool will have the capability to easily store historical values. However, in the interim, you could use four of the nine “Custom” fields to pass out the current 5 min bar open and bar close, and the last bar’s open and close. You could then have a macro copy and paste the data every 5 minutes. You just right click one of the field headings in “quotes” in Think Desktop and choose “customize”, add one of the “Custom” fields, and it opens an editor where you can add the appropriate Thinkscript code, like you do for the complex alerts.
September 7, 2009 at 10:23 am
So would it be possible to create volume by price charts using this? I’m an excel newbie but it seems like it should be, by having a macro or formula that would plot a point for every LAST and LAST_SIZE?
Don’t expect you to write this but any pointers would be awesome.
September 7, 2009 at 1:03 pm
Donachoo,
Yes, it would be possible, with a caveat–that the speed might not be fast enough to keep up with the price quotes in a very fast name. Excel/VBA is incredibly slow, but simple to use. Maybe it would work, but maybe not–you’d have to try it and see.
Here’s what I would try first: set up a file with two columns: LAST and LAST_SIZE. Set up ActiveX controls (say “labels”) linked to the cells that update from ToS. Then write code under the labels’ “OnValueChange” (or similar) event to paste the value somewhere else in the workbook for storage and plotting.
If you miss too many prints with that method, you could grab LAST and VOLUME, and then when you save a new LAST, calc a volume change by subtracting VOLUME from the prior value for VOLUME and put all of that simulated “last size” into the right bucket. This is a crude hack that might work should Excel not be fast enough for the first. You’d capture all the data, but you might not get the resolution in it that you want. Again, have to try it and see.
If neither of these work, then I’d say Excel is no good for this particular application. I’d suggest using a program that does do volume by price, like Ninja Trader or QuoteTracker, especially since they are free!
September 7, 2009 at 4:03 pm
I’ll give this a try. I’m not terribly worried about speed actually as long as its “close” to real time it should work.
I’d love to use Ninja Trader, but since TOS doesn’t tie into them its a no-go.
September 8, 2009 at 12:13 am
Great tutorial! Many TOS users are unaware of this capability and although I’ve been using their DDE links for a while, you showed me a few things I didn’t know. Keep up the good work!
September 13, 2009 at 5:44 pm
hi, in regard to real time quote in excel from tos-
1. once i do the transfer of x symbols how do i add a symbol without doing the entire transfer over?
2.in excel when the value in one cell is greater than another is there a way i can get a verbal alarm or sound? thanks
September 13, 2009 at 8:04 pm
Answers:
1. If you use my template file, you just type in new tickers into column B and click “generate” and it sets it up auto-magically. If you do it yourself, you have to enter in the DDE formulas as I described above in the post.
2. You can by linking the cell you are tracking to an active-x control and then writing a macro that fires when the value changes, calling a system API that plays a .wav file. Capability like this will be in my CAT tool eventually.
September 14, 2009 at 11:48 am
hi, i am using your template but if i later add a symbol into your spreadsheet and hit generate, all the values from the spreadsheet vanish
September 14, 2009 at 11:50 am
Send me the file you are using: readtheprospectus AT yahoo.com
September 14, 2009 at 11:59 am
on its way, thanks
September 14, 2009 at 12:07 pm
I see your problem. You shouldn’t include the info in rows 3 through 5. You should start with your first ticker (SVA) in cell B6. Secondly, in Row 2, you need to click on the black header bar in each column and choose what data fields you want to get from ToS. It didn’t create any links because you didn’t select any fields.
September 16, 2009 at 4:13 pm
[…] Update: Complex Analysis Tool (CAT) for Excel and Think or Swim By Prospectus As I wrote before, I’m working on a tool for my blog donors that will allow recording of the DDE data from […]
September 16, 2009 at 8:10 pm
WOW! Very cool.
Is there a way to pull options data? (bid, ask, mark etc)
The inclusion of vega, theta etc fields seems like there should be a way of doing so, but just putting GEWJQ in as the ticker symbol was not the intuitive solution that I thought it would be. I would love to have a spreadsheet that would give me all the trade management options for covered calls at a glance.
September 16, 2009 at 9:25 pm
Option data is possible. I can pull it from the Trade page in thinkorswim, so I now know that the cell formula would look like:
=TOS|MARK!’.GEWJQ’
but I can’t figure out the magic that Prospectus uses to get the ticker symbol into the formula and whether the addition of ‘ and . messes thing up in a unsolvable way.
If I put .GEWJQ or ‘GEWJQ’ into the TOS_DDE_TEMPLATE file as ticker symbol it gives a runtime error ‘1004’
September 17, 2009 at 6:50 am
I’ll fix this today and update my files.
September 17, 2009 at 2:05 pm
[…] By Prospectus I’ve done an update to my Excel DDE template and example files. Kyle left a comment saying that my template didn’t handle options tickers. And he was right! I’ve fixed […]
October 13, 2009 at 9:14 pm
I am trying creating my own live watchlist.
what i am trying to do is be able to add and change symbols really quikly.
this doesnt work but this is what im trying to do:
=TOS|LAST!’$A$5′
i have a column where i will be adding and chaning symbols when i need it.
and then i have all the other columns such as Last, High, Low, Net Change, Open…. i want them all to automatically pick up the new symbol. Any idea how to do this?
October 13, 2009 at 9:57 pm
Sure. You have to use VBA code to do it, though. You can use my “TOS_DDE_Template” file from my Google site to do it (link in article above). The DDE code can’t use a cell reference like $A$5, because it sends the literal string to the DDE server. You have to use a macro to set the cell formula to the exact DDE string programmatically. You can look at the source code in my file for the exact method I used. You might be able to set up something that uses the worksheet event that’s triggered when a cell is updated in Excel if you don’t like clicking a “Generate” button like I have in my file. Then you could truly just add a ticker and the DDE would change “automagically”, as it were.
October 14, 2009 at 11:06 am
ok i downloaded your example and trying it out.
i put in a few symbols..
selected the columns i want. then i clicked on Generate and it excel crashes..
October 14, 2009 at 11:13 am
Can you be any more specific on what happens when it crashes? Any error messages? What version of Excel are you using? Do you have Think or Swim open and connected at the time?
October 14, 2009 at 11:23 am
I rebooted my PC and it works now. Thanks
October 14, 2009 at 12:35 pm
I am trying to have it automatically sort for the % change… any idea how to do that? I dont like having to click on sort… Z-A. is there a way that i can automatically have it sorted at all times?
October 14, 2009 at 7:04 pm
You can use the Large() worksheet function on your array of price quotes. See here: http://lmgtfy.com/?q=sort+automatically+in+excel&l=1
(First result has an example .xls file)
November 3, 2009 at 5:21 pm
Hi, I am working with some DDE for excel and am trying to figure out how to calculate average volume for a stock. I want to compare average volume with current volume to spot stocks that are trading significantly more volume than average.
Is there a way to do this? Thanks!
November 7, 2009 at 10:24 am
Probably. Intraday average volume or average volume from daily?
November 7, 2009 at 1:35 pm
I have a few questions to help understand the TOS export capabilities.
I tried entering various markets into the S&P 500 Data tab of your Examples_v2 to get to know the workings and find it will retrieve data for any equity however will not on futures (/ES,etc) or currency pairs (EUR/USD,etc). Entering “/ES” or “EUR/USD” displays an error in the data cells.
Is there a simple process for appending the symbols in row “B” complete to the end of the formula in each of other data columns complete top to bottom rather than typing them in?
Do I understand correctly that all real-time market data normally available in MarketWatch (or the TOS servers in general) are available in real-time to Excel? It would appear your Examples workbook will retrieve anything available from their servers without having the mirrored data being displayed in MarketWatch first as you would have using the TOS “Export to Excel” button. If I understand correctly, you need only have TOS up and running.
Great work.
Thanks
.
November 7, 2009 at 1:51 pm
You might have to enter the symbols as “ESZ9” and “EURUSD”. I’ll have to look at it when I get a chance. It could be that I need to change some VBA code. Create a watchlist in Think Desktop with those symbols and export it to Excel. Look at the formula it gives you and you should be able to see the syntax. Sorry, I’m not near a pc so I can’t check.
I don’t understand your second question. My macro fills out the formulas.
The last one: yes, you only need TOS running, put the correct formula in Excel, and the DDE handles the rest. No need for a matching watchlist in Marketwatch in Think Desktop. I only show the export as an example of how to do it.
November 8, 2009 at 10:08 pm
I figured out the Futures as needing ‘ES’ etc to make them display. No luck with FX as they are not recognized even after using the same code from the normal TOS MarketWatch Excel Export button transfer.
I also can’t figure out how to get the 9 Custom Filters to display. As a side, I notice the TOS code must be a work in progress as the %Change field does not transfer data correctly.
The customs do work on the TOS MarketWatch page and do transfer to Calc correctly. The rub is they don’t update dynamically. I would like to dynamically graph the outputs of the customs however they only take a snapshot on first load. Some of the other fields also seem to exhibit this behavior.
Apparently the Nov release may offer us more to work with.
In reference to the 2nd question above, I may not understand the operation of the Generate button. It does not appear to do anything when activated on my setup.
November 8, 2009 at 10:35 pm
Make that ‘/ES’ etc.
On second try the % change does appear to work correctly.
November 9, 2009 at 1:42 am
Prospectus,
“God Bless Google”… I don’t remember what exactly I’ve been searching for, but “here I am”…
Even though I don’t use this DDE feature yet, it’s just a matter of time before I will be. Using Excell capabilities to augment think Desktop’s missing ones is Godsend.
Am I glad that I stumbled over your blog!!!
Keep up this great “DDE work”! Million kudos!
November 9, 2009 at 2:46 am
Proost: Check your macro security level in excel. Make sure you enable macros. Second, put whatever tickers you want in the first column, then click a cell in the header row. You get an in-cell dropdown where you can choose what field you want from TOS (including custom fields). Then click “Generate” and it builds all the formulas for you. If you have done all that and still see nothing, we’ve got more problems.
November 9, 2009 at 5:25 pm
Macro was set to high. It works now…even with FX. Customs still don’t work. Customs display as #N/A or #REF! depending on which of the two choices I select from Dynamic Data Exchange. You have to click through every one of the equities down the list to get the exchange to load on customs.
I changed all of my Customs to different names so I’m not sure that would affect the retrieval process. I tried changing one of them back to Custom1 but got the same results.
November 12, 2009 at 1:46 am
Prospectus,
I wonder… Is there a way to execute say a “Flat Now” on two positions
based on calculations in Excel?
Meaning, that the spreadsheet will send a command that will be interpreted by thinkDesktop as “Flat Now” (in the active Trader interface)…
November 12, 2009 at 6:56 am
I’m sure it is possible, but that goes WAY beyond my experience. You’d have to completely reverse engineer the TOS client/server protocols and work with undocumented API’s that they could change at any moment and break your tools. You’d have to also get into the low level TCP/IP communication stuff that I’m sure would be a nightmare from inside of Excel.
You should bug Think or Swim for a documented API so people can develop their own apps. TD Ameritrade has one that is quite good that I used with Excel a while back. It was relatively simple, pretty well documented and came with Active-X controls pre-built to handle the communication aspects. Or just use Ninja Trader and blow everything else away. 🙂
November 16, 2009 at 2:40 pm
Thanks Prospectus.
And trust me, I’m bugging TOS so much that I’m afraid that they’ll decide to send me over to one of their competitors… (now how do you add a grinning smile?) I wish I could go to the TradersExpo this week (Vegas) and hit both Toms (Sosnoff & Preston) with face-to-face “demands”!
Now, two issues:
1. How could one go about grabbing data feed from TOS into Ninja Trader? (I want to stay with TOS…)
2. There’s a piece of software called AutoHotKey (freeware) that could help and automate, among other stuff, Buy/Sell orders based on “whatever” but I’m concerned that I’ll be painfully slowing my system to a crawl with every additional piece of software that I add into the “equation”…
TOS’s coming out with two major releases… If I’m not mistaken they’re going to add significant automation capabilities… So I’ll wait until the Dec release is out…
November 17, 2009 at 8:28 pm
I’m only seeing an “export” choice in the drop down after I hit the print button. Is this something that is PC specific or is there something else I have to do for the mac version. Great Post.
November 17, 2009 at 8:39 pm
I don’t know. Send me a sceenshot of what you’re seeing. See my about page for email address
November 17, 2009 at 10:13 pm
I just checked…I have it on an old Power PC running Tiger…no go. They must not have links to Excel Mac. It looks like it is Windows only.
The only thing the export button does is export to a file.
It will not do what I want it to do on Windows until we can see dynamic output of Custom Scripts. I only get the original paste on Customs now with no changes that can be clearly seen happen in real-time on the TOS platform.
I hope they release this capability.
November 21, 2009 at 8:23 pm
Hi I would like a quote for doing some TOS/ excel work. I put a message up on this site before about it I did not hear back from you yet. So one more try. Please feel free to email at dnkarabasz@yahoo.com
Thank you,
Douglas
November 21, 2009 at 9:01 pm
I’ll send you an email
January 8, 2010 at 2:55 am
hey guys,
wondering if there is a dde command to track buying power effects??
i trade futures on TOS and I want to create an excel sheet that tracks what the different margin requirements are for each of the contracts. I used to to this by hand but the margin reqs change all the time.
Thanks to anyone who can help out.
Babak
January 21, 2010 at 10:42 am
Hi,
I have a problem with a TOS DDE link in excel showing an error message for a select few symbols in my watch list of about 100 symbols. Sometimes when you close the spreadsheet and reopen it it will update the problem symbols, but most of the time the very same symbols will not update and show an error while the majority of symbols are fine. The DDE link also appears to convert the symbol in the formula from upper case to lower case, and excel won’t let you change it back to upper case.
Any help would be greatly appreciated.
January 21, 2010 at 11:29 am
Send me an email with a screenshot and the symbols.
January 30, 2010 at 3:40 pm
Pro,
As a blog supporter, you were very helpful getting the DDE transfer from TOS to Excel working. So far TOS is still using the old Equity Option Symbols but claim that they will be using new symbols next month. Do you know when the changeover will occur and will the old symbol stop working with the TOS DDE VBA program.
I would normally use the Yahoo option listings which I would then manipulate to give me all the option symbols to input for your template page. Since Yahoo has changed their symbol format to their new format how would you suggest we handle the changeover. I good until this months options expire (Feb10), but will need the the March Symbols thereafter.
I did get the following 2 line timer to work, but I having trouble stopping it until I shut down the computer. It keeps starting up Excel on its own.
‘ dTime = Now + TimeValue(“00:02:00”)
‘ Application.OnTime dTime, “MyMacro”
thanks again,
Jerry Cohen
tiplex@ix.netcom.com
January 30, 2010 at 9:04 pm
Jerry,
Thanks for the kind words. I’m going to release my Complex Analysis Tool this weekend (finally). It has the macros required to record and plot DDE data. The reason your code continues to run is that it sets a system event. Even if Excel is closed, the macro will still run. In my CAT, I set a toggle button in the worksheet to act as a flag. I set a line of logic in my recording routine to trap if the button is false:
“If ActiveSheet.ToggleButton1.Value = False Then Exit Sub”
Then later on I make the delayed call to the record routine. If the toggle is false then it never gets there. You can see what I did in the VBA after I release it. The tool will be for blog donors only, but if you are a past donor you don’t need to donate again, unless you want to.
January 30, 2010 at 9:05 pm
I forgot: as far as new options symbols, I have no idea. As soon as TOS makes the change, I imagine that it will work.
January 31, 2010 at 12:59 am
[…] that record the data at time intervals you choose. I covered the full how-to on DDE linking in a previous post. An earlier version is what I used to create the plots in these two […]
February 2, 2010 at 5:07 pm
First of all thank you for a great blog.
I have been playing around with DDE feature. I wanted to define user defined fields ( using customs fields) and export them to excel. For example, lets say I want to track previous 5 minute volume (or average 5 minutes volume for last 30 minutes or whatever….) I take custom field and put there something like volume()[1], choose 5 minutes in aggregation and add it to current set to be exposed in a MarketWatch. After that I export it to excel. What is intresting that in MarketWatch those user defined fields working perfectly (being correctly updated all the time) but in excel they behave as a point in time data meaning never been updated after export. Did someone see this also?
February 2, 2010 at 5:16 pm
Yes, it seems that the custom formulas don’t update. You’ll have to do any calculations inside Excel on the price data. The trouble is, of course, that you don’t have any historical data…
February 4, 2010 at 2:01 pm
Same issue as below. Duplicated on multiple PC’s and spreadsheets, but it does pick random symbols to “NA” or force formula to lowercase. Tends to happen on big move days.
Hi,
I have a problem with a TOS DDE link in excel showing an error message for a select few symbols in my watch list of about 100 symbols. Sometimes when you close the spreadsheet and reopen it it will update the problem symbols, but most of the time the very same symbols will not update and show an error while the majority of symbols are fine. The DDE link also appears to convert the symbol in the formula from upper case to lower case, and excel won’t let you change it back to upper case.
Any help would be greatly appreciated.
February 4, 2010 at 4:10 pm
Send me a copy of your sheet so I can look at it.
February 4, 2010 at 4:28 pm
TOS support got back to me and it is a known issue. I’m told a patch for this and other DDE issues will be pushed this weekend. Thanks for the help offer.
Steve
February 7, 2010 at 9:50 pm
Thanks for all your help, I also had someone call TOS and found out that they were to fix this problem with a patch this weekend. Hope it works since I know of others I trade with who are also now having the same problems, and intermittently (meaning they would break, and then 10 minutes later come back). I’ve also noticed some wrong data being pulled for some symbols, such as beta or industry (my watch list says Visa is a steel company), so I’m hoping these will be fixed as well.
February 14, 2010 at 4:37 pm
Prospectus,
Your templates are working absolutely incredibly well with Think Or Swim(TOS) connected to TD Ameritrade as the feed. The changeover to the new options symbols feed worked without a hitch. If you need the symbols you can use the TOS Marketwatch(MW) to which you add the option symbols from the Trade Page (enter the underlying which brings up all the option months, then expand the month and right click the option to add to your watch list). Open the MW file and export to excel. You need to open a new Excel file and paste the page you exported from TOS MW. You can also use the Yahoo option symbols, but you have to edit their symbol because they use alot of zeros in the strike price.
Anyway the real reason for this message is that the Excel templates surprising work with TOS showing net spread value for two legged spreads, ie. Vertical Spreads, Calendar spreads, on a single line thereby
having to show the two legs separately on the Excel Spread sheet and having to do all the Spread calculations in the Excel SS. Of course, the visibility of each legs values is lost. example follows
Symbol Last Net Chng Bid Ask BS AS Volume Open High Low 52High 52Low
NTAP 31.46 -0.08 30.48 32.29 3 3 4397367 31.08 31.61 30.79 34.99 12.39
.NJQ100320C30-.NJQ100220C30 0.15 -0.39 0.45 0.65 53 16 6 0.55 0.55 0.15 N/A N/A
Shows Nasdaq Network Appliance, with a Calendar Spread Mar10 30Call and Feb10 30 Call. I’m assuming this will update reatime, the same way individual legs update.
Note the option symbol format TOS is using which works with the Excel templates. The Yahoo add 3 zeros in front and after the strike price. i.e. .NJQ100320C00030000 for Mar10 30 Call for NTAP. These have to be removed before it will work with TOS and the Excel templates.
February 14, 2010 at 4:43 pm
correction to previous message!
Anyway the real reason for this message is that the Excel templates surprising work with TOS showing net spread value for two legged spreads, ie. Vertical Spreads, Calendar spreads, on a single line thereby
not having to show the two legs separately on the Excel Spread sheet and not having to do all the Spread calculations in the Excel SS.
corrections to previous message. note you do not have to show each leg of a spread and then calculate the spread. TOS will do that for you with a single line.
March 10, 2010 at 3:22 am
[…] that record the data at time intervals you choose. I covered the full how-to on DDE linking in a previous post. An earlier version is what I used to create the plots in these two […]
April 3, 2010 at 12:22 pm
how do i get “previous close” from TOS into Excel?
April 3, 2010 at 12:44 pm
Actually, I figured it out from one of your earlier posts.
Thanks
April 4, 2010 at 10:44 am
Anyone using the new “hotkeys” to send orders from excel using VB yet?
May 6, 2010 at 8:50 am
Just found your website. I am very interested in the tools, community, becoming a member, donating, etc. Thought I’d try one of the free ones first. Downloaded the Cumulative Tick Indicator. Got all the script and commands and stuff, but what else do I have to do to make it actually work? Obviously, have to link it to TOS somehow, but it’s not obvious to me what I need to do in this regard. Please help. I’m eager to try this stuff out – especially your CAT tool. Thanks for your help.
George
May 6, 2010 at 10:30 am
George,
The cumulative tick indicator should be imported into Think or Swim and used on a chart. On a chart, click “studies” and “edit studies”, then at bottom left there’s a button called import.
May 7, 2010 at 8:41 am
Just donated via Pay-pal. Was able to download the CAT tool, but couldn’t open it because it is password protected. What do I do now?
May 7, 2010 at 8:42 am
I sent you the password in an email. Let me know if you have any problems.
May 11, 2010 at 10:01 am
Hello. I subscribed a few days ago so that I could use the CAT tool. I sent you an e-mail saying that I didn’t get a password. You responded that you had sent me one. But the e-mail you had sent me contained only my own e-mail address and a link to the blog subscription update site – NO PASSWORD.
Since then I have sent many e-mails asking for help and have heard nothing from you. Please take care of this for me. I am starting to feel ripped off.
May 11, 2010 at 10:13 am
George,
I’ll send the email again. Please check your spam folder to make sure that my responses aren’t in there. I haven’t seen any emails from you lately, so I’ll check my spam folders as well.
May 11, 2010 at 10:16 am
Just sent it. Also, if you don’t get that email from me, please post another comment here so I can figure out what’s going on…
May 11, 2010 at 10:17 am
Apparently, you don’t get the e-mails I send you when I just respond to e-mails you send me. I sent the last one (and this one) from the box on the “Read the Prospectus” web page. Your e-mails are not in my spam box.
May 11, 2010 at 10:38 am
Sent a reply a few minutes ago saying I got the password. Did you get that one? Sent it from my mailbox replying to your e-mail.Sending this one from the website. Is there a little user manual available? I have OK spreadsheet skills but a little help would be a good thing.
May 11, 2010 at 10:39 am
Sent a reply a few minutes ago saying I got the password. Did you get that one? Sent it from my mailbox replying to your e-mail.Sending this one from the website. Is there a little user manual available? I have OK spreadsheet skills but a little help would be a good.
May 11, 2010 at 11:16 am
I have the spreadsheet, and see the little instruction box. Can’t figure out how to do what I want, which is moving averages in various time frames for a list of 400 stocks.
For example, I’d like to have 5 minute moving averages with 10 and 50 periods, and I’d like to be able to sort the list to easily see things like when these lines are crossing and when prices are close to the line to identify potential trade candidates. How woul I do something like this?
May 11, 2010 at 11:52 am
I’ll help you get it set up, but we have to figure out this email thing. I still haven’t gotten anything from you. Did you send to readtheprospectus at gmail dot com?
June 2, 2010 at 4:21 pm
Does this work on Microsoft Office for Mac?
June 2, 2010 at 4:39 pm
I don’t know, I don’t have office for mac. You can always boot camp and run it in windows if it doesn’t.
June 2, 2010 at 4:44 pm
Ok, thank you
June 3, 2010 at 5:52 pm
I have found a simple solution the the DDE cell reference problem. Open vBasic and insert a new module. In the new module define this Function:
Function Eval(s)
Eval = Evaluate(s)
End Function
Then, in your excel formulas use it like this:
=Eval(“=TOS|LAST!”&A3)
where A3 has the ticker symbol.
It doesn’t always calculate on the first try, but you can force full recalculate by using CTRL+ALT+SHIFT+F9. Once all cells are properly calculated the DDE links seem to work until the spreadsheet is closed and opened again, at which point they may need recalculating again. Changing the ticker symbol appears to update all formulas that reference that cell.
Its a work in progress.
June 3, 2010 at 5:57 pm
That’s cool, thanks for sharing!
June 3, 2010 at 6:18 pm
Addendum.
It seems that that the recalculate need to be done for each new symbol or groups of new symbols added. I’m not a programmer, but it appears that the first evaluation of the cell on addition of a new symbol starts the DDE link with TOS, but it requires a second evaluation of the cell to get Excel connected to that link. Once the DDE links are established, changing the symbol updates properly. I.e., symbols MRK and IBM are entered. Then changing MRK to IBM updates the links properly without the need to recalculate.
June 4, 2010 at 10:44 am
O.K. Disregard my previous posts. My solution does not allow the DDE links to be ‘live’.
June 4, 2010 at 11:04 am
The macro in my file seems to be the best way I’ve found to set up the links. I’m just setting the formula of the cell to a string I build in the VBA code.
June 4, 2010 at 3:22 pm
Probably. I looked at your macro as well. I was hoping for something simpler. Oh well, back to the drawing board. Perhaps I’ll try to dissect your macro a little more.
June 23, 2010 at 1:03 pm
Is there a practical limit on the number of DDE links? I downloaded your TOS_DDE_Examples_V2. On the Russell 3000 worksheet, the Volume column has #N/A in many cells and N/A in others. I have seen similar problems with TOS downloads of my own.
June 23, 2010 at 1:17 pm
I don’t think so. The NA shows up of there hasn’t been any volume in the name during that day, I believe. It doesn’t return 0.
September 3, 2010 at 9:01 am
I am using the latest build of ToS, and under the ‘export’ tab, I only get the option to export as an HTML page or csv. There is no ‘Export to Excel’ option.
Can you identify how it’s done in the latest release?
Cheers,
Adrian
September 4, 2010 at 10:33 pm
I have build 1678, and it’s the same: you click the printer icon, then choose “Export to Excel”, the option just underneath “Export…” in the pop-up menu.
September 8, 2010 at 5:18 pm
Can you export the option call price for a particular underlying at a given date, time and strike price?
September 26, 2010 at 2:05 pm
Does TOS support exporting historical Option price data ?
If so how?
If not any sources you would recommend ?
Thanks
September 26, 2010 at 2:14 pm
How historical? Each contract expires and is never used again. You can see the data of existing contracts historically on charts as far back as that contract goes. I don’t know any other ways to get the data, and I don’t really have any recommended data sources. Any other readers know of anything?
October 8, 2010 at 3:25 pm
Do you know if it’s possible to export drawing tools data that is placed on a chart using this method? I have Price Level on my chart and would like to have that value update in excel as I make adjustments to it. I was thinking maybe there’s a way to have the drawing tool data show up in the watchlist but haven’t found a way to do it so far.
Thank you
October 8, 2010 at 6:00 pm
I haven’t found a way either. I need the help of a real java expert, I think.
November 22, 2010 at 11:19 am
Prospectus,
it’s been awhile since I posted. However, not a day goes by that I do not use your excel templates(data from TDA) to make sure the pricing on my vertical debit spreads is accurate and can trade the spread to the penny, even though the two options that make up the spread can only be traded to the closest .05 or .10. they have been particulately useful. Additional techniques that I would like to revisit are Calendar Spreads, Volatility Spreads & Credit Spreads..
Questions: Is there a way to get the underlying stocks historical volatility into your template so that the underlying stock HV value can help with timing trades.
Also anything in your templates to help specifically with the Calendar, Volatility and Credit Spreads.
Thanks again for a great set of templates.
tiplex
Supporter
January 26, 2011 at 5:46 pm
hey prospectus,
I apologize that im a total n00b to excel and TOS programming, so please bear with me.
I would like to be able to get the market opening price, market closing price, 10am, 1030 and 11 am prices for a different list of stocks for each day going back a year or so. I want to put this into my excel spreadsheet so I can do some calculations with that data.
Is there a way to paste in a list into marketwatch, and either have a few custom columns that excel grabs that data from, or have excel take all the opening and closing prices of each 30 minute bar for that day and then extract the data im looking for within the spreadsheet itself. Ideally, i would do that, then paste a new list of stocks, change the day and repeat?
is this possible using the DDE link capability of TOS? Would I be able to do this myself or would that be too complicated for someone like me.
please advise and again, i apologize if my question is nonsensical.
-daburs
January 26, 2011 at 5:50 pm
It’s a good question, you just won’t like the answer. You can’t get historical data from TOS. DDE is like having a DVR hooked up to live TV. If you didn’t record it when it was live, it’s too late now.
However, if you have a historical intraday data source then you could build a sheet that does that. I don’t know of any free ones, though.
March 24, 2011 at 8:23 pm
Great blog, so much useful information!
This is very useful, albreit windows-specific, but the (next) real question is, there a way to send orders/trades through DDE? I’ve googled to no end, and sans thinkpipes, i don’t see how the retail thinkorswim desktop client can programmatically place orders. I’m sure if anyone knows/has figured out how, you might. Please advise!
March 24, 2011 at 8:51 pm
No. DDE is a one-way communication in TOS. For simple rules, you can autotrade with the Prodigio platform, but it’s very limited in what you can do.
April 3, 2011 at 7:03 am
Great discussions !!
I am trying to create a custom field with my text and use DDE to get field into Excel. My prob;em is I want ToS to load a TEXT field I build in ToS. When I try to create it using concat, I get “useless function invocation” error and custom field is not added to DDE list.
Any ideas ?
Thanks,
Gary
April 3, 2011 at 12:34 pm
Send me the file you are using and I’ll take a look
April 3, 2011 at 2:25 pm
In Excel I have:
=TOS|LAST!’/ESH1′
=TOS|Custom 1!’/ESH1′
In ToS for Custom 1 I want:
concat(getSymbolPart(),” MACD-BB Study SELL SHORT ” ) ;
but I get the following error in ToS for Custom 1:
Useless function invocation
April 3, 2011 at 2:34 pm
ToS doesn’t live stream the “custom” fields. You can only stream out the pre-defined fields. So it’s not syntax; your syntax is actually correct. It’s that it won’t stream the custom fields. Makes them kind of useless
April 3, 2011 at 3:01 pm
Thanks for your reply !!!
I need a way to send a custom message to Excel from a ToS study; from Excel (using VBA) I can then send a text message to my cell phone, etc.
The ToS email capability is limited to their criteria which I can not adapt as I do in a study which sends an alert to the ToS message center which is local to computer and presumes I see it rather than a text message or email, etc.
Any ideas are welcome.
Gary
April 3, 2011 at 5:19 pm
I don’t see a way to do this currently. I’ve repeatedly told ToS developers that we want this. Email Tom Sosnoff and thinkscript@thinkorswim.com and tell them. If enough of us ask it’ll get done.
April 3, 2011 at 10:28 pm
One last idea:
Is there any field in ToS that I can set to a known value from a study (number or text) that is updateable from ToS to DDE to Excel ?
If I could set a variable in a study and DDE sends it to Excel, I could work with that.
I will also email ToS (again).
Gary
April 7, 2011 at 4:46 pm
Can’t seem to find a code for average daily volume. Can you help me with that?
April 7, 2011 at 4:56 pm
Try here: https://readtheprospectus.wordpress.com/2010/02/02/daily-average-volume-study-with-alerts-for-think-or-swim/
April 18, 2011 at 2:49 pm
Hello and thanks for sharing your work.
I need to export to Excel the PL curve of a strategy. Do you know if this is at all possible with TOS? I know I can export other stuff from various pages but I can’t see how to export historical data including the profit curve. I used to do that as a Tradestation user, but I am not anymore.
Thanks in advance.
Spra
April 19, 2011 at 10:32 am
Hi, I’m wanting to develop a simple RSI formula that can email me alerts when they happen. I reside in south africa I trade futures using the http://www.gt247.com ( global trader ) platform. I use netdania charts
Can you help
Regards
Domenic
27713019762
May 18, 2011 at 4:52 pm
Hey, Thanks for all the info. When I click paste on Excel it stops responding and crashes. its excel 2007 in windows vista. Do you know what might be wrong?
May 28, 2011 at 7:18 am
good
May 31, 2011 at 11:22 am
Hey, i saw someone else that was having this problem above but mines a tad different, i downloaded an example and i also used the tutorial. Every time i paste the data in all of my columns just give me #NA. every single one except the ticker symbols. I turned my macro security to low im on windows 7 64bit using calc so i use the export to calc instead of excel. any tips would be appreciated.
Thank you
May 31, 2011 at 11:22 am
^ this is during trading hours as well so there is volume
June 19, 2011 at 3:26 am
Hi, I just donated in order to use your CAT_TOS_DDE_Recorder but no password was sent to open the file, could you please provide it for me.
Also can I do a similar study with weighted put/call ratio or would you be able to do it for me, obviously not for free?
I appreciate your response.
June 19, 2011 at 9:46 am
Check your email
June 19, 2011 at 11:31 am
Those who don’t donate don’t worth even a negative reply?
I have asked a question about the possibility to get into Excel historical data along with a strategy’s P/L indicator values and I am still waiting for a reply.
June 19, 2011 at 11:33 am
That was two months ago!
June 19, 2011 at 11:52 am
Sorry, I missed your comment before. You can always email me or @prospectus on twitter to get my attention.
You can’t export historical data as I’ve said before. You could use thinkback to replay the data and record it with the CAT but the data won’t be clean like you’d get in tradestation.
You can use my free strategy post- processor to get results from a TOS report into excel, but it’s still kind of a manual process. Check out my blog post for more info.
June 20, 2011 at 12:31 pm
Could you please call/email me?
D. Georg, (O) 407-233-1999
dietmargeorg@gmail.com
July 3, 2011 at 10:37 am
Regarding “ToS doesn’t live stream the “custom” fields. You can only stream out the pre-defined fields”: TOS support informed me the problem is with Excel. So I was wondering if one wrote a DDE Client program in Visual Basic or C (and not the VBA excel uses) then would we be able to stream our “custom” fields?
July 7, 2011 at 7:21 pm
Correction to my post on July 3. Sorry, I misinterpreted what TOS support told me. I have since solved my issues and I am able to stream out the “custom” fields along with the pre-defined fields from TOS to excel.
July 7, 2011 at 8:19 pm
Really? How?
July 13, 2011 at 2:56 pm
The formula for the cell in excel as you know is
=TOS|Custom19!DD
for the stock DuPont. In order to use the Custom fields in TOS then you must have the stock symbol on a watchlist with of course one of the watchlist columns being Custom19. (If the stock symbol is not on a TOS Watchlist then the excel cell will show “loading” and which never loads. You are allowed to rename the Custom19 to something more descriptive but in excel you will still need to refer to the custom field with its original name of Custom19. See the following example where I started with Custom19 and renamed it %Gap and appended the formula with a comment so I will know what to call it in excel. (“#” indicates a comment)
100 * (Open – Close[1]) / Close[1] #Custom19
(If you have renamed a bunch of your Custom fields then just set up excel with 19 cells where they refer to Custom1 thru Custom19 and then match up the values for your Renamed Custom fields to the matching Custom1 thru Custom19.)
Or if you are using OpenOffice Calc then the cell formula is
=DDE(“TOS”,”CUSTOM19″;”DD”).
Some credit goes to TOS Support as they helped me figure it out because they told me that the Custom fields worked.
Although you can have a Custom script with multiple lines, I think it must be a single formula to work as a Custom field to excel or calc.
July 13, 2011 at 5:01 pm
That is spectacular. Thanks for sharing!! Using this, there may now be a workaround for getting an email alert for a scripted condition. If so, I’ll post a new tool to do just that.
July 22, 2011 at 1:17 pm
Is there a way to graph the Mark on a Vertical spread as it changes?
Thanks,
Ron
July 29, 2011 at 8:25 am
Yes, probably. If not the mark, then you could graph the bid/ask of the respective legs and calculate your own mark.
August 3, 2011 at 3:23 pm
Prospectus and others:
Awhile back I wanted to figure out how to dynamically link a TOS formula in Excel to a particular cell and not have to hardcode in the symbol in the formula. I don’t know if this has been solved, but I figured it out and it’s slick. Here is the excel formula:
=EVAL(CONCATENATE(“=TOS|BID!”,A6))
Where A6 is the cell that contains the symbol.
However, in order for this to work you need the Morefunc.xll plug-in for Excel. There’s plenty of websites that offer the morefunc plug-in, just google it.
Question though to Prospectus or others:
I need help.
1) Has anyone dynamically linked an excel worksheet or html page to TOS where (let’s say) the top 10 rows are shown. For example: I have a watchlist in TOS that has about 100 symbols. I have a few columns in the watchlist (symbol, bid, ask, net change, volume). When I am in TOS, I click on the column header and it sorts accordingly. Rather than having an excel worksheet with ALL 100 symbols in it and referencing (the symbol, bid, ask, net change, volume) using the TOS formulas (this bogs my computer down), I would like to somehow point or link my excel worksheet or an html page directly to the TOS screen and show the top 10 rows. Basically, I am trying to externally link two windows (one window being TOS and the other window is Excel).
ANY ADVICE would be greatly appreciated.
Thanks,
Adam
August 5, 2011 at 4:32 pm
Adam,
I don’t know of a way to do DDE with an html page. How are you sorting the columns? If you are only sorting on volume, then import the symbol and volume of 100 symbols, then you can write a macro that does an active sort of the symbol names and imports all the fields for the symbols that you want, if that makes sense.
September 8, 2011 at 7:32 am
Thanks for the great work. I wonder if you could tell me how to write a code in Excel to stream a numerical value for things such as a stochastic, macd and/or hull ma over different time frames for an assortment of symbols?
October 13, 2011 at 6:11 pm
Thank you for all the determined and hard work to streamline excel with TOS. I recently installed your custom study MAcrossover_w_alerts and customized the code so it would work with an ema of 3 and 13, which i must say works wonderfully. i have two questions that i hope you can shed some light on. First off, is it possible to insert my own custom alert sound, such as a .wav file, to be used with this study? and secondly, i was wondering if it is possible to feed the ema crossover data into excel. that is, alongside the recording of the price data over a certain time interval , i want the price and time to be recorded at the exact moment that the ema crosses, whether it be to the downside or upside. thanks so much for your hard work and i look forward to hearing back an answer from you! 🙂
November 7, 2011 at 4:37 pm
Hello,
First off thanks for all the work you have done, its very impressive.
All of this seems to stand on ToS being open at the same time as the excel file. Is there anyway that the excel file could reference ToS w/o that program being open? or that I could set ToS to open at the same time as that particular excel file?
January 4, 2012 at 8:55 am
Can ToS read from a file? Could I use the excel sheet or a file for a ThinkScript to get the input values from?
January 10, 2012 at 5:48 pm
I have used the ToS export feature before but didn’t realize until I read your post that I could essentially use an Excel spreadsheet to stream that data. This is lifechanging! I am now able to calculate my own dollar weighted put/call ratios in realtime while the market it open by just adding a few simple formulas to the worksheet to calculate ratio and totals by month/type. Using this for VIX and SPY has been incredibly useful to me. Thanks for great work and taking time to share it.
January 10, 2012 at 10:13 pm
Great Post! Thanks for all of info that you shared. When using the TOS DDE link in excel Is it possible to access the TOS “Sequential” indicator? I would like to have that on my excel sheet.
March 19, 2012 at 5:37 pm
soncne elektrarne…
[…]Linking Excel and Think or Swim for Streaming Real-Time Calculations « Read the Prospectus[…]…
March 29, 2012 at 5:31 am
Hi,
How do I calculate the dollar weighted Put/Call Ratio (for the Spx)? Can I also view historical dollar weighted Put/Call Ratios?
I saw @nymeca was able to do this
April 1, 2012 at 3:19 pm
Hi, I really appreciate this thread, very cool to be able to get that into excel, allows for some very fun work.
Is there any way to get your positoins with the trade prices? So I could track PnL, or do i have to manually enter them?
Thanks,
April 3, 2012 at 10:47 pm
I haven’t found a way to get to position info. Doesn’t mean it’s impossible, just that you need an expert in reverse engineering java and the undocumented API’s. Far beyond my current abilities 😦
April 23, 2012 at 12:37 am
Pip Investment Website…
[…]Linking Excel and Think or Swim for Streaming Real-Time Calculations « Read the Prospectus[…]…
April 24, 2012 at 10:44 am
I recently installed the Excel 2010 and since then I have not been able to use the live data feed from TOS:
=TOS|LAST!’GOOG’ .
Excel hangs (not responding) until I shut it down.
Any help that you can provide will be greatly appreciated!!!
April 24, 2012 at 10:49 am
Do you use google chrome?
April 24, 2012 at 11:19 am
yes!
April 24, 2012 at 11:34 am
This is probably the problem: https://readtheprospectus.wordpress.com/2011/08/05/excel-2010-and-tos-dde-problems/
April 24, 2012 at 1:54 pm
Thanks!!!! It was Chrome’s fault….I appreciate the help…
April 28, 2012 at 5:23 pm
By using the Print-Button, Export to Excel, under the Analyze tab I’m able to get option data into excel. I can customize the columns to get any data I need. I add the ‘Option Code’ column and that gives me the ticker for each call or put for the underlying. It also allows me to copy the tickers and paste them into your TOS_DDE_Template_v2, but your VB code doesn’t put the option ticker into the formula correctly. Can you add a generate button for Options symbols? The ticker needs to be inserted into the formula in the following format: ‘.OEX120427P550’ instead of OEX120427P550
I figure it will be easier to export the option symbols from TOS, then use your template to get the data I need, rather than having to customize the columns in TOS. I’m not a Visual Basic guy so I can’t do this myself.
Thanks, and I’m starting to hate Chrome.
April 29, 2012 at 11:26 pm
Hi, I was able to solve my problem by editing the Macro in TOS_DDE_Template_v2 and adding a period to the following line in Module1:
ddeformula = “=TOS|” & ddeaction & “!’.” & ticker & “‘”
I saved the spreadsheet under a different name as modifying this line will cause the Generate Button to incorrectly grab stock quotes, so now I have a template for Stocks, and one for Options.
Thanks for this thread and your work, much appreciated.
July 2, 2012 at 10:08 pm
I can’t seem to get my Excel updates to reflect after hours prices. The last trade is still from the regular session.
July 2, 2012 at 10:11 pm
Futures prices or stock prices?
July 4, 2012 at 1:56 pm
Hi, I just came across your live streaming to Excel info. Am currently trying it with market closed but using OnDemand, seems not to work. I want to catch up to your latest developments with this strategy. Any input would be appreciated. Any possibilities for being able to export directly to MS Access as opposed to Excel, or would one have to link to the Excel file from Access. Nice work, would like to follow your work more closely.
July 24, 2012 at 5:34 pm
prospectus – is there a way I can email you or you to me? you same very knowledgable about TOS and excel and I have some questions.
July 24, 2012 at 5:35 pm
Sent you an email
August 22, 2012 at 3:22 pm
Everytime I attempt this, Excel tells me that ‘Remote data not accessable’ and asks if I want it to start ‘TOS.EXE’ If I give it permission to run the app it tells me that it cannot run the app because it’s broken or inaccessable. Even though I have ThinkOrSwim desktop in the background. I’ve tried from the TOS platform print menu with the same problem. No communications. I am running Vista Ultimate and was wondering if DDE is depreciated and not available for this kind of work.
August 22, 2012 at 3:46 pm
Do you have google’s chrome browser open?
October 4, 2012 at 8:32 pm
Those who use Thinkorswim and excel might find this spreadsheet interesting:
http://www.elitetrader.com/vb/showthread.php?threadid=223105
sgfee123 10-04-12 11:08 AM
Dynamic Data Exchange (DDE) with Thinkorswim and Excel
See the attached spreadsheet for a Quick and easy way to use DDE with Thinkorswim and Excel.
Attachment: excel-dde.xls
November 5, 2012 at 6:27 pm
If I use the close column in market watch I get the settlement price
If I do a custom column with my script as: close[1] I get the last trade price of the regular session (at least the last price quote on TOS charts) IF I do this on ES/YM/NQ
(i.e. mondays last trade price is 1411,
its settlment close is 1412,
if i do close i get 1412 (settlement
if i do close[1] i get last trade price 1411.75 (like on charts bid/ask)
***However on Gold and all other futures it is giving some random number..?
What number is it giving?
How do I get it to give me the last trade quote (as displayed on chart) like you can do with ES TF YM when you do close[1] in marketwatch but not on all other futures
ie on Gold i am getting 1685.4
(settlment was 1683.2
(last trade price was 1675.4
so i don’t even know what 1685.4 is supposed to represent
January 21, 2013 at 10:03 am
I want to make a IV tracking spreadsheet, can the TOS DDE do that?
January 25, 2013 at 12:05 pm
Impressive!
One question: is there a way to hook up the TOS API into .NET?
January 25, 2013 at 12:33 pm
I’ve asked them for API info, but they don’t have any to give.
On Jan 25, 2013, at 11:06 AM, Read the Prospectus
January 25, 2013 at 12:33 pm
I think so. Implied volatility is one of the variables passed through DDE from ToS.
On Jan 21, 2013, at 9:03 AM, Read the Prospectus
January 28, 2013 at 1:44 am
Please help! I can’t export to Excel from TOS.
Excel shows that: Remote data not accessible. To access this data Excel needs to start another application. Some legitimate applications on your computer could be used maliciously to spread viruses or damage your computer. Only click Yes if you trust the source of this workbook and you want to let the workbookstart the application. Start application “TOS,EXE”?
When I cklick Yes it is not open becouse one of its compinents is damaged or Missing.
January 28, 2013 at 7:40 am
You have to have Think Desktop open and connected for the DDE to work. TOS.EXE is the name of the launcher. You should open ToS first and connect to streaming quotes before you open the excel file.
On Jan 28, 2013, at 12:44 AM, Read the Prospectus
February 5, 2013 at 4:42 pm
Ali, there was an update to Excel that kind of broke DDE. Now, DDE requires Excel to be run as administrator. Of course, runing Excel as administrator causes you to lose the ability to double-click a file from Windows Explorer and have it open in Excel. Excel will still open and you can open the file from Excel’s open menu, so, that’s the workaround for now. Hopefully, M$ will have a fix for this soon.
February 5, 2013 at 5:51 pm
Prospectus, I have recently just started trying to use this TOS DDE in excel and have run into a problem where all the custom fields never load and “loading” is displayed in the cell. I called TOS/TDA and they just say they haven’t changed their DDE and it’s an Excel issue and to call M$ (like I’d get an answer there). Anyway, upon initial export to Excel, the custom fields work and they might work during market hours, but after hours, the data all changes to “loading” for the custom fields (all the non-custom fields work correctly). In the TOS platform, the custom fields work all the time. Although sometimes “loading” is displayed there for a short time (less than a minute). I wonder if there is a way to get Excel (or the TOS DDE) to do it correctly after hours.
Any ideas?
February 5, 2013 at 6:06 pm
I’ve had luck with custom fields when they are already in a watch list inside ToS. Say you have a watch list open with GOOG, AAPL and RIMM, with a custom column calculating some formula. If you go to your Excel sheet, and set up DDE links for the custom formula for GOOG, NFLX, AAPL and RIMM, you should get values for the three but not for NFLX. I think this is because the custom values are calculated in think desktop, not calculated or fed from the data server. So if it’s not already being calculated inside ToS it doesn’t get any updated values for it. Make sense?
On Feb 5, 2013, at 4:51 PM, Read the Prospectus
February 5, 2013 at 6:42 pm
That’s an interesting observation/assumption. I wonder if it’s correct. I’ll have to do some testing. I thought the symbols were still in the watchlist on TOS and the custom fields were being calculated there (in the platform), but Excel just showed “loading”.
If that is true, then I wonder why the TOS support people don’t know that? I know, I know, I live in a fantasy world where support people are actually capable and know something about whatever it is they’re supporting.
February 5, 2013 at 6:51 pm
We know more about it because we are actually using it. We think up new ways to work with the tools that they never thought about. They know a lot about how it’s put together but have no clue what to do with it after. When you get developers highly coordinated with actual users, that’s when amazing things can happen. It’s not very common though, sadly.
On Feb 5, 2013, at 5:42 PM, Read the Prospectus
February 10, 2013 at 3:39 pm
To run Excel in Administrator Mode:
1.Click the Start Button
2.Type “Excel”
3.Press SHIFT + CTRL + Enter
February 19, 2013 at 11:37 am
As of this morning, it appears that there is another update to TOS that has paralyzed the TOS DDE template. When opening my excel spreadsheet, there is the usual request to update the data. When I click “update” the spreadsheet freezes and I get the “not responding” message at the top of the tool bar.
Any thoughts or ideas? Am I the only one experiencing this? My spreadsheet worked on Friday…
Thanks in advance for any help.
February 19, 2013 at 11:57 am
How embarrassing. I forgot the very first rule of computer use. When I rebooted my system and reloaded the TOS desktop and then my spreadsheet – everything worked perfectly.
Sorry for any inconvenience..;(
February 19, 2013 at 12:14 pm
No inconvenience!! Also, thanks for posting what worked for you to fix it. The info will help someone else down the road.
On Feb 19, 2013, at 10:57 AM, Read the Prospectus
March 8, 2013 at 4:37 pm
Prospectus, I’ve been trying this feature but I alway get a message that the Remote data is not accesible and then an error message saying that “Cannot run “TOS.EXE”. The program or one of its components is damaged or missing”. This happnes in 3 different computers. Is there an add-on to be used with this ?
March 8, 2013 at 4:53 pm
Cesar774, see my post above from February 5, 2013 at 4:42 pm
March 8, 2013 at 5:09 pm
You have to have Think or Swim opened and connected before you open the excel.
On Mar 8, 2013, at 3:37 PM, Read the Prospectus
March 8, 2013 at 7:28 pm
It worked. Excel needs to be run as an administrator. Thanks.
March 13, 2013 at 3:10 pm
Does this work on Windows 7? I have TOS running, but when the workbook tries to update, I get the “Remote data not accessible ….. Start application TOS.EXE” I’ve searched the computer for TOS.EXE but found nothing.
March 13, 2013 at 3:49 pm
There’s tips in the comment thread up above. Might have to run excel as administrator.
On Mar 13, 2013, at 3:10 PM, Read the Prospectus
March 13, 2013 at 4:17 pm
I saw that, and thought I was running in administrator mode, but I was wrong. Once in administrator mode, it did work. Thank you for the quick response.
March 13, 2013 at 5:28 pm
Sure, glad it worked!
On Mar 13, 2013, at 4:17 PM, Read the Prospectus
March 19, 2013 at 8:59 pm
Hi Prospectus, I truly appreciate the efforts that you have put forward and appreciate the continued engagement/discussion you provide. Between using dde/excel or api, which would you recommend and why? I have some ideas that I would like to develop and I’m not sure which provides better flexibility and access. Any insights would be appreciated..
March 19, 2013 at 10:36 pm
The TD API has more capability. You can pull down position info, orders, data, and you can even send orders with it. The problem is that it’s pretty unwieldy to use (since I worked with it last) and it only covers TD products, so no futures or anything like that.
Excel DDE is much easier to use and work with, but it’s more limited. It’s one way only (down from ToS to you) and the only data available to you is certain ticker info fields. You can’t get portfolio or order info with it.
On Mar 19, 2013, at 8:59 PM, Read the Prospectus
April 7, 2013 at 4:01 am
Appreciation to my father who stated to me concerning this
web site, this web site is truly awesome.
April 11, 2013 at 11:25 am
Great blog. I can’t seem to get DDE data to stream in excel for the available “technical” indicators. They are added into my watch/quote list and they are in the DDE help list of available fields, yet they will not show up in Excel. Symbol, last, volume, net change all work but I am getting N/A for studies like ADx, Market Forecast, RsIWilder, MACD……. They all have numerical values on my watch list. Any help or thoughts?
April 11, 2013 at 11:31 am
I noticed the same thing. I’m looking in to it.
April 11, 2013 at 1:02 pm
Hi Prospectus, Thanks for all of the information you’ve provided!
I’d like to be able to import Option trading data into MS Excel. I’m particularly interested in the Greeks, IV, HV, etc. What do you think is the best way for me to import that data?
Real-time or near real-time would obviously be preferred – but for how I’m going to use it – EOD data would probably work just fine.
Quality Free data would be best, but subscribing for data is on the table too.
Thanks for your Help, Rich
April 23, 2013 at 3:56 am
Hi Iam having a problem .When I try to paste in excel it freezes up and then says program not responding .So the DDE is not working for me .Can someone tell me how to fix this ?
June 10, 2013 at 1:38 pm
I need honest absolute quality information here. For the PAST exam, if you do not complete the minimal everything…are you out? Like, if you excel at swimming and running, can minimal the situps and push ups, but cant fully get all of the pullups, are you out? Or is it based on a point system? Please, I need 100% fact on this. I ship in a few days and may not complete all pull ups, though I will excel in running and swimming and be able to complere all of sit ups and pushups. Let me know how it all works! thank you much! Link me to your questions after your answers if you want me to look! THANKS!
June 10, 2013 at 1:57 pm
Hi, I also use a program called XLQ from Qmatrix for data retrieval directly into Excel. You can get live or delayed option and stock quotes from multiple sources, some free and some subscription. With a TD Ameritrade account you can get data but the account can’t be an original TOS account as they use different servers for data. I opened up a new TDA account specifically for data download and it works great. It even pulls in data for the new mini options.
June 10, 2013 at 2:00 pm
Sorry, it’s XLQ from Qmatix, http://www.qmatix.com/XLQ.htm
July 6, 2013 at 6:30 pm
Problem running Excel.
I get a Blank Screen. Change compatibility, privilege level “off”.
get, security warning option, enable this content. remote data not accessible “yes”. Cannot run “TOS.EXE”.
System Win 8 PRO.
Any help would be appreciated. I am willing to donate,
Thanks,
Jack
July 6, 2013 at 7:48 pm
Did you read through this comment thread and try all the fixes?
On Jul 6, 2013, at 6:30 PM, Read the Prospectus
July 6, 2013 at 9:04 pm
I just read through all fixes. Used IE , privilege on and off. Still can’t get it to work. Should I use your template?
Thanks, for your fast reply.
Jack
July 6, 2013 at 9:13 pm
I’ve never dealt with windows 8 before. Try my template and see what happens.
On Jul 6, 2013, at 9:04 PM, Read the Prospectus
July 7, 2013 at 1:26 pm
Template, also did not work on this computer. It did work on another win 7 computer. I have one other win 8 computer I will try it on, when I get some time.
Thanks, for your help,
Jack
July 7, 2013 at 1:57 pm
Sure, let us know if you find a solution so we can document it here.
July 7, 2013 at 3:49 pm
It did not work on my other Win 8 computer. I think win 8 dose not support DDE.
Jack
July 23, 2013 at 11:16 am
Using this tool could I pull commodity data (high/low/close/volume) off TOS going back 10 years?
July 23, 2013 at 11:35 am
No. ToS only supports streaming of live data. No historical backfill. Sorry. 😦
On Jul 23, 2013, at 11:16 AM, Read the Prospectus
July 23, 2013 at 6:38 pm
Using the tool can I save commodity and equity closing prices daily (based on the 5:15pm commodity close and the 4:00pm equity close)?
July 23, 2013 at 7:02 pm
Yes you could.
July 30, 2013 at 3:46 pm
Your workbooks do not work for me. Keep getting the error message “remote data not accessible. Start application ‘tos.exe’?” I click “yes”, and the see #ref! everywhere. I am sure I must be doing something wrong.
July 30, 2013 at 3:50 pm
Do you have Thinkorswim open and connected? What version of Windows and Excel are you using? Do you use Google’s Chrome browser? The comments in this thread also have a lot of troubleshooting info.
On Jul 30, 2013, at 3:46 PM, Read the Prospectus
July 30, 2013 at 3:54 pm
TOS is open. Windows 7 64bit. Excel 2003. Sometimes use Chrome.
July 30, 2013 at 3:58 pm
Message: “Cannot run ‘tos.exe.’ The program of one of its components is damaged or missing”
I see only one other mention of “#REF!” on this page. Doesn’t seem to apply to my problem.
July 30, 2013 at 4:18 pm
After a reboot, and running only TOS and Excel, it works. I took the extra step of running Excel as Administrator.
July 30, 2013 at 5:33 pm
I think it was the “run as administrator” that was the problem. Glad it’s working.
On Jul 30, 2013, at 4:19 PM, Read the Prospectus
August 12, 2013 at 2:36 pm
To make this work, I need to change the compatibility setting for Excel to run as administrator. The download will load the streaming data okay into a new Excel book, but if I save the spreadsheet, I can’t open it again. I get a message telling me there was a problem sending a command to the program. What’s the secret to saving a spreadsheet and then being able to re-open it?
August 12, 2013 at 2:58 pm
Can you send me a screenshot of the error? Readtheprospectus at gmail
On Aug 12, 2013, at 2:37 PM, Read the Prospectus
August 22, 2013 at 2:25 pm
Can you download 5-minute data?
August 22, 2013 at 2:54 pm
You can record data every 5 minutes, but you can’t download historical data.
October 19, 2013 at 7:22 pm
This page definitely has all the information I wanted
concerning this subject and didn’t know who to ask.
October 31, 2013 at 2:03 pm
I can get Think or Swim to import an options table, but only statically. I’m using Libre office Calc ( basically open office). So I just need to know how to make it so the DDE updates in real time. I don’t have any option in Think or Swim to “export to excel” or “export to calc” just “export” which makes me export it to an html file to then open after in calc. Thanks
November 13, 2013 at 2:37 pm
Prospectus, I would like to record high,low,close every minute in excel using TOS feed. Right now I only have feed coming into excel in real time…but no record. Is it possible to record every minute? Thanks
December 2, 2013 at 9:11 pm
Prospectus:
Excel hangs when I paste the export from tos, can you help ?
December 2, 2013 at 9:13 pm
What OS? Have you rebooted? Using google Chrome?
December 8, 2013 at 1:07 pm
Hi
Is it possible to stream quotes from my monitor page on active trades to Gdoc spreadsheets? I only need the mark quotes for the active options and the underlying.
Thanks!
December 13, 2013 at 4:12 pm
I find this fascinating and thank you in advance for your efforts. However I can’t get the Excel/TOS link to work. I’m entering TOS|LAST’GOOG’ and I get a message asking if I want to start TOS.EXE. When I click yes it says it cannot run TOS.EXE because one of the components is damaged or missing. I’m running Windows 7 and Excel 2010. I’ve just begun using Excel’s connection to MSN to get quotes and it works fine. Do I have to set up a connection to TOS? If so, how do I do that? Thanks so much for your help.
December 16, 2013 at 1:39 pm
I figured it out. I had to open the Excel spreadsheet as the administrator.
December 16, 2013 at 4:58 pm
Johan, no it’s not possible to access your positions through DDE. You’d need to make your lists manually.
December 31, 2013 at 5:06 pm
Hi,
Does anyone have any direction on how to get the equivalent functionality (TOS data in Excel 2008/2011) on a MAC platform. There is no DDE file on a MAC. Maybe someone has an equivalent file or function.
Thanks, and happy holidays to everyone,
John C
January 1, 2014 at 5:06 pm
Follow the syntax in this post to enter a DDE formula directly into a spreadsheet cell in Excel for Mac. That *should* work, even without the export options. I’ll try to test it myself later on.
January 9, 2014 at 5:24 pm
Thank you prospectus. Emailed TD and they are working on it.
A different question, my Excel says “cannot run TOS.EXE the program or parts of it are either missing or damaged” any idea what the problem is?
Is TOS itself damaged or is it something else?
Cheers!
Johan
January 9, 2014 at 5:39 pm
Check through the comment string above. Many problems can be solved by making sure you are running excel as administrator.
January 9, 2014 at 6:04 pm
Yep that got it sortet. Althoug when I open the file it says it encounterd a problem but it seems ok just to click ok on it.
I can get the stream for some of them now, but some still says REF? Like it streams Bid Ask but not delta or IV for example.
Cheers!
Johan
January 9, 2014 at 6:34 pm
Try back when market hours are open.
January 10, 2014 at 9:06 am
Thank you for the fast responses. Will try that.
Another question, on the calculations I have set up from the streaming is it possible to save those values once a day or something? To track them in a diagram?
Cheers!
Johan
January 21, 2014 at 4:17 pm
I need help! I have been streaming data from TOS into excel successfully for the past 2 years and all of a sudden it will no longer work on my desktop computer. Every time I hit “enable data” excel will freeze and I have to force close. The same spreadsheet still works on my laptop so there is nothing wrong with it. I haven’t changed anything that I know of, could a Windows update have messed this up? I am using Windows 7 if that helps. It will not work in Excel 2007 or 2010…freezes every time.
January 21, 2014 at 5:48 pm
I need help too! I have the same problem as Rob stated above. I am also using Windows 7 and Office 2010. As of this morning, excel freezes and I too have to force close through Windows Task Manager. I have made several attempts and several reboots. I hope someone can help.
January 22, 2014 at 10:39 am
I have tried everything under the sun and nothing works. That list includes:
– running excel and TOS as an administrator
– closing rundll32.exe
– closing chrome
– uninstalling chrome
– starting windows with only microsoft processes running
– doing a system restore to last Thursday when it was still working
– deleting the usergui folder in TOS
– reinstalling TOS
– reinstalling excel
– updating java
– downloaded openoffice calc and get Err:509 in every cell with a formula
Nothing works!
January 22, 2014 at 10:40 am
Is this since the big ToS update?
January 22, 2014 at 10:41 am
yes it started yesterday morning.
January 22, 2014 at 10:50 am
This is what Thinkorswim said:
Hello Steve,
Thank you for the email and I apologize for the inconvenience. Our developers have identified an issue with DDE not functioning after the latest release. We are currently working on a fix but do not have an ETA available. Again, I sincerely apologize for the issue. In the meantime, please utilize the thinkorswim platform to view the data you are looking for.
Should you have any other questions, please feel free to contact us.
Regards,
Katie Bailey
Client Liaison
866 839 1100 general
773 435 3232 fax
January 22, 2014 at 10:51 am
Ok, that’s what I thought. Just hold right then
January 22, 2014 at 11:07 am
Thanks for the info Steve! Odd that it is still working on my other PC though.
January 22, 2014 at 12:25 pm
Very timely feedback! Thank you all. I had just finished uninstalling three (3) Windows updates (which did not help either) when I checked for additional posts. Thanks again… I will wait then.
January 27, 2014 at 8:33 pm
RE: Dynamic Data Exchange (DDE)
Support@thinkorswim.com
Sent: Mon 1/27/2014 3:15 PM
Hello,
Thank you for the message and your continued patience regarding this issue. Unfortunately the development team is still working to correct this issue. I am sorry for the inconvenience. At this time I do not have an ETA for completion.
Should you have additional questions, please contact us again.
Thank you for your time,
North VanHooser
Client Liaison
866 839 1100 general
773 435 3232 fax
January 31, 2014 at 4:03 pm
So I have made some calculations that use the sum function to create a duplicate portfolio overview that streams from tos. But it seems that when I add new data into empty slots on rows that are summed elsewhere nothing happens to the end summation.
Anyone have any ideas?
February 2, 2014 at 9:35 pm
Hey Mate
I tried your Excel for loading TOS data via DDE and I get an error message saying “TOS.EXE” could not be loaded. My executable is actually called thinkTDA.exe. Could you please provide instructions on how I can change your excel file to look for this executable?
Many thanks
Matt.
February 3, 2014 at 3:07 pm
Do you have think desktop open and running before you open the excel file? I also know DDE is broken for the latest ToS update 😦
February 3, 2014 at 3:07 pm
Check that your formula is actually including the row that your new data is in.
February 5, 2014 at 10:18 am
Hello Please let me know how I can find out the cost of your services on the production scripts in Excel-DDE-TOS.
February 6, 2014 at 3:51 pm
I just talked to support and they said the latest update is that they are going to try to get a fix in for a patch that will go in over the weekend for 2/15/14.
February 6, 2014 at 5:50 pm
Awesome, thanks for the update
February 7, 2014 at 8:27 pm
Rob is it still working on your old PC?
February 8, 2014 at 10:19 am
Yes it still works perfectly on my old laptop. It doesn’t work on my desktop or new laptop though. Must have something to do with 64/32 bit processing, just a guess.
February 8, 2014 at 4:03 pm
I too had it working perfectly on an old PC. Then I made the big mistake of logging in and then I was forced to update the software. Now, I’m running the latest version (Build 1854.31 – Feb 7, 2014), and it still doesn’t work. This is getting very frustrating. Waiting almost a month now for an essential feature to get fixed is totally unacceptable. ThinkOrSwim/TD should be ashamed of themselves. They really need to make this a priority and get it fixed immediately!
February 18, 2014 at 9:23 am
Well it is now Feb. 18, and surprise, surprise, it still does not work. As pathetic as they are, I am beginning to believe that the incompetent Think Or Swim programmers cannot be this inept. There must be some ulterior motive for them to disable this feature. I am sick of waiting and I am now seriously considering a better alternative.
February 18, 2014 at 10:22 am
Fred:
I couldn’t agree more!! If you find a reasonable alternative, please post what it is.
February 18, 2014 at 10:40 am
Yahoo has something called SMF but I can’t speak to the functionality or reliability of it. There’s also QMatix which works better but you have to pay for it.
February 18, 2014 at 10:43 am
There is a TD Ameritrade API I used years ago, but it was a total pain in the ass to get ahold of. Maybe I should look into it again. I don’t know if you can get futures data through it though. It didn’t about 6 years ago anyway.
February 22, 2014 at 3:17 pm
I found an amazing broker called MB Trading http://www.mbtrading.com
They have an excellent API and a flat $4.95 commission fee. I’m currently playing with their paper account and API and it is very stable, and easy to learn.
Also, for those who haven’t given up on Think or Swim yet, I found a simple workaround. As suggested by Rob, if you have an old computer, it still works. The issue is not related to 32/64 bit OS, but rather the version of Excel that you are running. If you have Excel 2007 or earlier, it should work fine. The Export to Excel menu item still doesn’t appear in Think or Swim desktop, but the DDE commands work anyway in Excel.
If you don’t have an earlier version of Excel, Think or Swim recommends you try Open Office for free.
February 23, 2014 at 10:36 am
I don’t know what the deal is but I had Excel 2007 on my new computer and it didn’t work, also couldn’t get OpenOffice to work.
February 23, 2014 at 11:10 am
I actually have Openoffice working now. I didn’t realize the formulas for DDE were different. For example in excel, I would type
=tos|mark!’goog’
That same thing would look like
=DDE(“TOS”;”MARK”;”GOOG”) in openoffice. You also have to make sure you are running openoffice as an administrator.
February 23, 2014 at 11:19 am
Rob
Could you please share your open office file with me? My email is tahatan@gmail.com.
Many thanks
Matt.
February 23, 2014 at 11:33 am
sure, I’ll send now.
February 23, 2014 at 11:34 am
If you want, CC me and I’ll post it here on the blog
February 23, 2014 at 11:45 am
I could but it’s not something generic. It’s all the option positions I have open and looks kind of confusing. The commands are all the same just in a different format like I posted above.
February 23, 2014 at 12:00 pm
OK Thanks Rob
March 25, 2014 at 12:06 am
I can not figure out how to export stock’s IV and 22pma MA (on hourly chart) data directly to Excel. Can someone help me with this.
Thank you.
March 26, 2014 at 11:28 pm
Can anybody help with the question above? I would be very thankful.
Regards
April 13, 2014 at 1:15 pm
Anyone know how I can write a maro that can copy text from a cell to another with the DDE formula, where the originating textcell will be different?
I am trying to be have an automatic function for adding my trades, were a macro write the info like .AAPL140515500P into the =TOS|MARK!’xxxxxxxxxxx’ formulas I want to run. However I cant seem to be able to copy the specific text or the cell and paste it into a formula.
Since I have to copy the specific text and not the cell the macro records the actual text and not the comand to copy specific text from a specific cell. Which is what I really want
April 13, 2014 at 3:04 pm
Here is some interesting information from TOS.
Hello Jack,
Thanks for the e-mail and kind words. In regards to your inquiry about DDE, we now support the Microsoft Excel function RTD(Real Time Data) which works in the same way.
I could not attach the PDF file. I copied and pasted it. Contact TOS support for a copy.
Jack Webster
thinkorswim
RTD on thinkorswim
thinkorswim has the ability to use the Mircosoft Excel function RTD (Real Time Data.) This allows you to connect your Excel spreadsheet to thinkorswim for real time quote updates. To use this feature on thinkorswim bring up a watchlist with your desired symbols and columns. Then simply click on the printer icon in the top right of the list and choose Export to Excel.
Pasting Visual Basic script to update to RTD
If you have previously saved spreadsheets that use DDE you can update your spreadsheet to RTD. The following steps will show you how to run a Visual Basic script to perform this update. You may want to save your old DDE spreadsheet as a new document in case you want to reference the original document.
thinkorswim
Start by copying this script as we will need to paste it in Step 3
Sub ConvertFormulas()
Dim ws As Worksheet
Dim rng As Range
For Each Sheet In Worksheets
ConvertWorksheet (Sheet)
Next Sheet
End Sub
Function ConvertWorksheet(ByVal sht As Worksheet)
On Error Resume Next
For Each rng In sht.Cells.SpecialCells(xlCellTypeFormulas)
Dim text As String
Dim price As String
Dim symbol As String
Dim tosPrefix As String
Dim separatorPosition As Integer
Dim exclaimPos As Integer
Dim isTOS As String
text = rng.Formula
isTOS = InStr(text, “=TOS”)
separatorPosition = InStr(text, “|”)
exclaimPos = InStr(text, “!”)
If (separatorPosition > 0 And exclaimPos > 0 And isTOS > 0) Then
tosPrefix = Left(text, InStr(text, “|”))
price = Mid(text, InStr(text, “|”) + 1, exclaimPos – separatorPosition – 1)
symbol = Right(text, Len(text) – exclaimPos)
Dim result As String
result = “=RTD(“”TOS.RTD””,,””” + Ucase(price) + “””,””” + Ucase(symbol) + “””)”
rng.Formula = result
End If
Next rng
End Function
You will need the Developer Tab to perform this task. To show the Developer tab click on File and choose options. From the Excel Options1 window click on Customize Ribbon2 and check Developer3 in the Main Tabs section.
1. Now you need to paste the Visual Basic script into a VBA Project. To start this click on the newly added Developer Tab1 and then click the Visual Basic button2.
2. You need to paste the script into a specific location in Visual basic. Click on the ThisWorkbook Project1 in the right hand panel. This will bring up new VB (code) box2 where you want to paste your script. Select all of the default code in this box3 and paste your copied script.
Now run this script to update your DDE functions for the entire workbook. Click on the Run Menu1 at the top and chose Run. This will bring you to the Macros2 window where you will click on ThisWorkbook.ConvertFormulas and choose Run3.
You can close down the Visual Basic window and you will see your new updated spreadsheet. From here you can save the spreadsheet and use it as you have used DDE in the past.
May 3, 2014 at 6:37 pm
I am glad to see you a working DDE to RTD conversion and a new version of CAT. I will be one the first to donate, to get a copy.
In my earlier post 4/13 I have been using RTD. RTD has some problems. You can’t load for a short cut, and must load directly from
inside the Excel program. I am using Office 365 and Windows 8.1.
I do have a problem, backing up to OneDrive, sometimes it works other time it does not. Any help would be appreciated.
Again, looking forward to the new Cat.
Jack
May 12, 2014 at 6:37 pm
Hi, I just used the “Export to Excel” method but I just keep getting #N/As for all the cells, I waited for about 30 mins and they still never updated. I’ve tried about 4-5 times now and no luck.
The only change I’ve made is to edit the formulas in the various time interval columns.
Is there something I have to enable or a specific add-in I need to make it work? Or would I have to get rid of my custom formulas?
Please Help!
Thanks.
May 12, 2014 at 6:39 pm
…about my previous comment, the formula edits for the time intervals are made in Think or Swim, not Excel.
May 12, 2014 at 7:02 pm
I’ve had the same problem with the data not updating correctly. If I disabled my firewall (ZoneAlarm) and SpyBot and never start Chrome since reboot, I can sometimes get Excel to update data correctly but it’s sporadic and frustrating and doesn’t work everytime. I’ve tried getting an answer from TOS with no luck. The good news is that if Excel does start downloading data, I can restart the firewall, SpyBot, and Chrome and the data will continue to update in Excel until I close it. I’m running Windows 7.
May 22, 2014 at 2:11 pm
I changed to openoffice calc and it works perfect. The formula instead is =DDE(“TOS”;”LAST”;”GOOG”)
May 22, 2014 at 2:31 pm
I got this new way to enter formulas in to Excel cells.
Below is my question to tech support. They answered back “Yes, you can use this version”.
Just for your information.
Jack
I got it to work with ORCL options.
One question, Should I change the Excel cell from ” =TOS|BID!ORCL” to “=RTD(“tos.rtd”, , “BID”, “ORCL)”.
June 20, 2014 at 5:19 pm
Reblogged this on The Profitcy's Prophets.
July 9, 2014 at 11:30 pm
Reblogged this on The Weed Wizard's Guide to Day Trading on the Marijuana Exchange and commented:
Second time today i’ve come across your site for help.
July 28, 2014 at 4:32 pm
I cannot link tos to my 2002 excel., but that is not my question.
Can you script a column in tos that can designate the change of either %change or net change within a given frame of time.
For example, between clicks of scanner, or bars of a watchlist.
Preferably in a scanner. This may be easily possible, but I do not
know how. I am a little new to this and only use the standard parameters. To clarify, I need to set up a column that can designate the %change or net change withing a refresh of the scanner. May not be possible, but it would help, as it does the big boys…. Will be happy to donate if it works.
Simply stated, I am looking for an equity moving up at a faster pace then other…. Are not we all?
David
July 28, 2014 at 6:49 pm
David, I would suggest trying this web site;
http://www.thinkscripter.com/
If he doesn’t already have a script for what you want, he can build it if it makes sense. Good luck.
Daniel
September 20, 2014 at 12:50 pm
Have you ever considered about adding a little bit more than just your articles?
I mean, what you say is fundamental and all. But think of
if you added some great photos or video clips to give your posts
more, “pop”! Your content is excellent but with pics and videos, this website
could certainly be one of the greatest in its niche.
Good blog!
September 20, 2014 at 5:15 pm
Ok I am having a problem with getting the RTD to work.
The quotes on options dont work properly, a SPX option with a 12.00 BID will become 1200. I’ve tried to add “T” at the end but it only worked once. Is it a problem on my end or TOS?
And there seem to be a general problem with loading all data, even if I just import it by the printer option
December 4, 2014 at 5:26 am
is the CAT tool still available for download?
December 14, 2014 at 1:20 am
I would like a quote for the TOS to excel customization . Could you call me so I can explain what I need, Joe Deir 708-473-5726
December 14, 2014 at 2:21 am
I would like to send like to send you a donation …..Could you please send me instructions on how to download CAT for my TOS program ? I have some more questions on the capabilities of the CAT program. Please contact me asap.
joedeir@gmail.com
December 22, 2014 at 8:06 am
Email me: readtheprospectus at gmail
>
October 17, 2015 at 10:35 am
Hello. If running a watch list from TOS in excel, while in “On Demand” mode, is it possible to have that clock(On Demand Time) exported over to the excel work sheet instead of excel using the “Current Time”? Then be able to take 5 minute intervals, say from 3pm to 3:55pm and compare price differences?
Thanx for any help
March 3, 2016 at 11:36 pm
How do I insert any study in the Excel for real time analysis? Also, is there anyway can create my own Function and use in the script?
June 30, 2016 at 8:40 pm
I’d like a customization please email me back
November 24, 2016 at 9:03 am
Nice post! But all the data didn’t show up (only w N/A), I tried =TOS|VOLUME!AAPL It worked. Please help Thanks!
November 24, 2016 at 11:29 am
Send me an email readtheprospectus at gmail
>
August 8, 2018 at 7:24 pm
How do I contact you for custom work?
August 8, 2018 at 8:15 pm
readtheprospectus at gmail
November 5, 2019 at 3:09 pm
access Control reader protocol
Linking Excel and Think or Swim for Streaming Real-Time Calculations | Read the Prospectus
June 11, 2020 at 8:25 am
2020 and this post still works 🙂
I’ve been using TOS for quite a while, 1 problem that always bug me is that sometimes it works, sometimes reopen helps, sometimes it just doesn’t load. Any idea or suggestion?
Thanks.
June 11, 2020 at 9:06 am
Sometimes the RTD built in excel doesn’t work well. I don’t think Microsoft does anything to maintain it really. Reload and pray is about all we can do.
>
June 11, 2020 at 9:19 am
Hi, thanks for the quick reply. Is that Excel problem or TOS problem?
I’ve been wondering if we can work out something for on Google worksheet.
June 11, 2020 at 10:09 am
It’s kind of a both ends problem.
>
December 21, 2021 at 3:04 pm
Please email me (charles@riskdoctor.com) for a project quote.