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
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: http://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: http://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 ?