Linking Excel and Think or Swim for Streaming Real-Time Calculations

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:

Choose_Watchlist

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”:

Export_Menu

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:

Paste_Notice

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”:

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: , , , , , ,

268 Responses to “Linking Excel and Think or Swim for Streaming Real-Time Calculations”

  1. bryan Says:

    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

  2. Prospectus Says:

    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.

  3. donahchoo Says:

    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.

  4. Prospectus Says:

    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!

  5. donahchoo Says:

    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.

  6. OptionGal Says:

    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!

  7. jenks Says:

    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

  8. Prospectus Says:

    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.

  9. jenks Says:

    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

  10. Prospectus Says:

    Send me the file you are using: readtheprospectus AT yahoo.com

  11. jenks Says:

    on its way, thanks

  12. Prospectus Says:

    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.

  13. Status Update: Complex Analysis Tool (CAT) for Excel and Think or Swim « Read the Prospectus Says:

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

  14. Kyle Says:

    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.

  15. Kyle Says:

    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’

  16. Prospectus Says:

    I’ll fix this today and update my files.

  17. Excel DDE Template Files Updated « Read the Prospectus Says:

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

  18. goldenz Says:

    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?

  19. Prospectus Says:

    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.

  20. goldenz Says:

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

  21. Prospectus Says:

    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?

  22. goldenz Says:

    I rebooted my PC and it works now. Thanks

  23. goldenz Says:

    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?

  24. Prospectus Says:

    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)

  25. Kurt Says:

    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!

  26. Prospectus Says:

    Probably. Intraday average volume or average volume from daily?

  27. PROOST Says:

    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

    .

  28. Prospectus Says:

    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.

  29. PROOST Says:

    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.

  30. PROOST Says:

    Make that ‘/ES’ etc.

    On second try the % change does appear to work correctly.

  31. EsTrader Says:

    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!

  32. Prospectus Says:

    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.

  33. PROOST Says:

    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.

  34. EsTrader Says:

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

  35. Prospectus Says:

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

  36. EsTrader Says:

    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…

  37. ADKTrader Says:

    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.

  38. Prospectus Says:

    I don’t know. Send me a sceenshot of what you’re seeing. See my about page for email address

  39. PROOST Says:

    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.

  40. Douglas Karabasz Says:

    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

  41. Prospectus Says:

    I’ll send you an email

  42. Babak Says:

    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

  43. Roger M Says:

    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.

  44. Prospectus Says:

    Send me an email with a screenshot and the symbols.

  45. Jerry Cohen Says:

    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

  46. Prospectus Says:

    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.

  47. Prospectus Says:

    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.

  48. Complex Analysis Tool (CAT): Analyzing and Recording Think or Swim DDE Data in Excel « Read the Prospectus Says:

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

  49. ECO Says:

    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?

  50. Prospectus Says:

    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…

  51. Steve Says:

    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.

  52. Prospectus Says:

    Send me a copy of your sheet so I can look at it.

  53. Steve Says:

    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

  54. Roger M. Says:

    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.

  55. Jerry C. Says:

    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.

  56. Jerry C. Says:

    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.

  57. Prospectus – Complex Analysis Tool (CAT): Analyzing and Recording Think or Swim DDE Data in Excel » ThinkOrSwim | marketHEIST.com Says:

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

  58. stephen Says:

    how do i get “previous close” from TOS into Excel?

  59. stephen Says:

    Actually, I figured it out from one of your earlier posts.

    Thanks

  60. Steve Says:

    Anyone using the new “hotkeys” to send orders from excel using VB yet?

  61. George Westfall Says:

    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

  62. Prospectus Says:

    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.

  63. George Westfall Says:

    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?

  64. Prospectus Says:

    I sent you the password in an email. Let me know if you have any problems.

  65. George Westfall Says:

    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.

  66. Prospectus Says:

    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.

  67. Prospectus Says:

    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…

  68. George Westfall Says:

    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.

  69. George Westfall Says:

    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.

  70. George Westfall Says:

    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.

  71. George Westfall Says:

    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?

  72. Prospectus Says:

    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?

  73. Ben C. Says:

    Does this work on Microsoft Office for Mac?

  74. Prospectus Says:

    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.

  75. Ben C. Says:

    Ok, thank you

  76. Kyle A. Says:

    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.

  77. Prospectus Says:

    That’s cool, thanks for sharing!

  78. Kyle A. Says:

    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.

  79. Kyle A. Says:

    O.K. Disregard my previous posts. My solution does not allow the DDE links to be ‘live’.

  80. Prospectus Says:

    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.

  81. Kyle A. Says:

    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.

  82. Steve R Says:

    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.

  83. Prospectus Says:

    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.

  84. Adrian Says:

    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

  85. Prospectus Says:

    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.

  86. Kris Says:

    Can you export the option call price for a particular underlying at a given date, time and strike price?

  87. Rob Says:

    Does TOS support exporting historical Option price data ?
    If so how?
    If not any sources you would recommend ?

    Thanks

  88. Prospectus Says:

    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?

  89. William Says:

    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

  90. Prospectus Says:

    I haven’t found a way either. I need the help of a real java expert, I think.

  91. tiplex Says:

    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

  92. daburs Says:

    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

  93. Prospectus Says:

    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.

  94. aleks Says:

    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!

  95. Prospectus Says:

    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.

  96. Gary Says:

    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

  97. Prospectus Says:

    Send me the file you are using and I’ll take a look

  98. Gary Says:

    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

  99. Prospectus Says:

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

  100. Gary Says:

    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

  101. Prospectus Says:

    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.

  102. Gary Says:

    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

  103. Guy Says:

    Can’t seem to find a code for average daily volume. Can you help me with that?

  104. Prospectus Says:

    Try here: http://readtheprospectus.wordpress.com/2010/02/02/daily-average-volume-study-with-alerts-for-think-or-swim/

  105. Spra Says:

    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

  106. Domenic curko Says:

    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

  107. adolfo Says:

    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?

  108. NEELABH NARAYAN Says:

    good

  109. TheTribe Says:

    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

  110. TheTribe Says:

    ^ this is during trading hours as well so there is volume

  111. PJ Says:

    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.

  112. Prospectus Says:

    Check your email

  113. Spra Says:

    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.

  114. Spra Says:

    That was two months ago!

  115. Prospectus Says:

    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.

  116. Dietmar Georg Says:

    Could you please call/email me?

    D. Georg, (O) 407-233-1999
    dietmargeorg@gmail.com

  117. Allen Says:

    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?

  118. Allen Says:

    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.

  119. Prospectus Says:

    Really? How?

  120. Allen Says:

    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.

  121. Prospectus Says:

    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.

  122. ronb107 Says:

    Is there a way to graph the Mark on a Vertical spread as it changes?

    Thanks,
    Ron

  123. Prospectus Says:

    Yes, probably. If not the mark, then you could graph the bid/ask of the respective legs and calculate your own mark.

  124. Adam Says:

    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

  125. Prospectus Says:

    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.

  126. Burt Thombs Says:

    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?

  127. Giorgio Says:

    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! :)

  128. Robb H. Says:

    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?

  129. Mel Says:

    Can ToS read from a file? Could I use the excel sheet or a file for a ThinkScript to get the input values from?

  130. @nymeca (Lynn) Says:

    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.

  131. bill Says:

    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.

  132. soncne elektrarne Says:

    soncne elektrarne…

    [...]Linking Excel and Think or Swim for Streaming Real-Time Calculations « Read the Prospectus[...]…

  133. Ansil Says:

    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

  134. vclogic Says:

    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,

  135. Prospectus Says:

    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 :(

  136. Pip Investment Website Says:

    Pip Investment Website…

    [...]Linking Excel and Think or Swim for Streaming Real-Time Calculations « Read the Prospectus[...]…

  137. Armando Says:

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

  138. Prospectus Says:

    Do you use google chrome?

  139. Armando Says:

    yes!

  140. Prospectus Says:

    This is probably the problem: http://readtheprospectus.wordpress.com/2011/08/05/excel-2010-and-tos-dde-problems/

  141. Armando Says:

    Thanks!!!! It was Chrome’s fault….I appreciate the help…

  142. Daniel Wall Says:

    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.

  143. Daniel Wall Says:

    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.

  144. Brian Says:

    I can’t seem to get my Excel updates to reflect after hours prices. The last trade is still from the regular session.

  145. Prospectus Says:

    Futures prices or stock prices?

  146. Hugh Bryant Says:

    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.

  147. alan Frost Says:

    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.

  148. Prospectus Says:

    Sent you an email

  149. Jerry Says:

    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.

  150. Prospectus Says:

    Do you have google’s chrome browser open?

  151. Steve Says:

    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

  152. Darrell Says:

    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

  153. brent Says:

    I want to make a IV tracking spreadsheet, can the TOS DDE do that?

  154. Paul Says:

    Impressive!

    One question: is there a way to hook up the TOS API into .NET?

  155. Prospectus Says:

    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

  156. Prospectus Says:

    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

  157. Ali Says:

    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.

  158. Prospectus Says:

    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

  159. Randy Zz Says:

    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.

  160. Randy Zz Says:

    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?

  161. Prospectus Says:

    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

  162. Randy Zz Says:

    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.

  163. Prospectus Says:

    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

  164. AtpAse Says:

    To run Excel in Administrator Mode:

    1.Click the Start Button
    2.Type “Excel”
    3.Press SHIFT + CTRL + Enter

  165. robinson Says:

    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.

  166. robinson Says:

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

  167. Prospectus Says:

    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

  168. Cesar774 Says:

    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 ?

  169. RandyZz Says:

    Cesar774, see my post above from February 5, 2013 at 4:42 pm

  170. Prospectus Says:

    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

  171. Cesar774 Says:

    It worked. Excel needs to be run as an administrator. Thanks.

  172. Rob Says:

    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.

  173. Prospectus Says:

    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

  174. Robin Says:

    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.

  175. Prospectus Says:

    Sure, glad it worked!

    On Mar 13, 2013, at 4:17 PM, Read the Prospectus

  176. David Says:

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

  177. Prospectus Says:

    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

  178. Click to open menu Says:

    Appreciation to my father who stated to me concerning this
    web site, this web site is truly awesome.

  179. Greg Says:

    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?

  180. Prospectus Says:

    I noticed the same thing. I’m looking in to it.

  181. Rich Fitzpatrick Says:

    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

  182. fshary Says:

    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 ?

  183. ai roboform coupon Says:

    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!

  184. Daniel Says:

    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.

  185. Daniel Says:

    Sorry, it’s XLQ from Qmatix, http://www.qmatix.com/XLQ.htm

  186. Jack Webster Says:

    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

  187. Prospectus Says:

    Did you read through this comment thread and try all the fixes?

    On Jul 6, 2013, at 6:30 PM, Read the Prospectus

  188. Jack Webster Says:

    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

  189. Prospectus Says:

    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

  190. Jack Webster Says:

    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

  191. Prospectus Says:

    Sure, let us know if you find a solution so we can document it here.

  192. Jack Webster Says:

    It did not work on my other Win 8 computer. I think win 8 dose not support DDE.
    Jack

  193. EcK Says:

    Using this tool could I pull commodity data (high/low/close/volume) off TOS going back 10 years?

  194. Prospectus Says:

    No. ToS only supports streaming of live data. No historical backfill. Sorry. :(

    On Jul 23, 2013, at 11:16 AM, Read the Prospectus

  195. EcK Says:

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

  196. Prospectus Says:

    Yes you could.

  197. rrhandle Says:

    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.

  198. Prospectus Says:

    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

  199. rrhandle Says:

    TOS is open. Windows 7 64bit. Excel 2003. Sometimes use Chrome.

  200. rrhandle Says:

    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.

  201. rrhandle Says:

    After a reboot, and running only TOS and Excel, it works. I took the extra step of running Excel as Administrator.

  202. Prospectus Says:

    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

  203. Doug Regelin Says:

    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?

  204. Prospectus Says:

    Can you send me a screenshot of the error? Readtheprospectus at gmail

    On Aug 12, 2013, at 2:37 PM, Read the Prospectus

  205. rrhandleRob Says:

    Can you download 5-minute data?

  206. Prospectus Says:

    You can record data every 5 minutes, but you can’t download historical data.

  207. text back went Says:

    This page definitely has all the information I wanted
    concerning this subject and didn’t know who to ask.

  208. Greg Says:

    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

  209. Mitch Says:

    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

  210. Gus Says:

    Prospectus:

    Excel hangs when I paste the export from tos, can you help ?

  211. Prospectus Says:

    What OS? Have you rebooted? Using google Chrome?

  212. Johan Says:

    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!

  213. Dan Says:

    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.

  214. Dan Says:

    I figured it out. I had to open the Excel spreadsheet as the administrator.

  215. Prospectus Says:

    Johan, no it’s not possible to access your positions through DDE. You’d need to make your lists manually.

  216. John Chapman Says:

    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

  217. Prospectus Says:

    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.

  218. Johan Says:

    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

  219. Prospectus Says:

    Check through the comment string above. Many problems can be solved by making sure you are running excel as administrator.

  220. Johan Says:

    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

  221. Prospectus Says:

    Try back when market hours are open.

  222. Johan Says:

    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

  223. Rob Says:

    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.

  224. Julia Hewitt Says:

    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.

  225. Rob Says:

    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!

  226. Prospectus Says:

    Is this since the big ToS update?

  227. Rob Says:

    yes it started yesterday morning.

  228. Steve Says:

    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

  229. Prospectus Says:

    Ok, that’s what I thought. Just hold right then :/

  230. Rob Says:

    Thanks for the info Steve! Odd that it is still working on my other PC though.

  231. Julia Hewitt Says:

    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.

  232. Steve Says:

    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

  233. Johan Says:

    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?

  234. matt ward Says:

    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.

  235. Prospectus Says:

    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 :(

  236. Prospectus Says:

    Check that your formula is actually including the row that your new data is in.

  237. Alex Says:

    Hello Please let me know how I can find out the cost of your services on the production scripts in Excel-DDE-TOS.

  238. Rob Says:

    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.

  239. Prospectus Says:

    Awesome, thanks for the update

  240. Fred Says:

    Rob is it still working on your old PC?

  241. Rob Says:

    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.

  242. Fred Says:

    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!

  243. Fred Says:

    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.

  244. sgfee123 Says:

    Fred:

    I couldn’t agree more!! If you find a reasonable alternative, please post what it is.

  245. Fred Flintstoner Says:

    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.

  246. Prospectus Says:

    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.

  247. Fred Says:

    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.

  248. Rob Says:

    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.

  249. Rob Says:

    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.

  250. Matt W Says:

    Rob

    Could you please share your open office file with me? My email is tahatan@gmail.com.

    Many thanks

    Matt.

  251. Rob Says:

    sure, I’ll send now.

  252. Prospectus Says:

    If you want, CC me and I’ll post it here on the blog

  253. Rob Says:

    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.

  254. Matt W Says:

    OK Thanks Rob

  255. Rakatoa Says:

    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.

  256. Rakatoa Says:

    Can anybody help with the question above? I would be very thankful.
    Regards

  257. Johan Says:

    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

  258. Jack Webster Says:

    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.

  259. Jack Webster Says:

    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

  260. Nik Says:

    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.

  261. Nik Says:

    …about my previous comment, the formula edits for the time intervals are made in Think or Swim, not Excel.

  262. Daniel Says:

    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.

  263. Diego Truninger (@diegotruninger) Says:

    I changed to openoffice calc and it works perfect. The formula instead is =DDE(“TOS”;”LAST”;”GOOG”)

  264. Jack Webster Says:

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

  265. theprofitcy Says:

    Reblogged this on The Profitcy's Prophets.

  266. theweedwizard Says:

    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.

  267. David Jameson Says:

    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

  268. Daniel Says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: