Complex Analysis Tool (CAT): Analyzing and Recording Think or Swim DDE Data in Excel

Donors rejoice: It’s finally here! My Complex Analysis Tool (CAT) for Think or Swim. This tool is an extension of my TOS_Excel_DDE tools, with the addition of macros 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 posts.

The CAT tool comes pre-configured to take the S&P 500 and calculate an Advancing / Declining Volume for the index in real time. You can get data as often as every second. If the price of a stock now is above the price at the last time step, then the volume for that stock since the last step is positive. Vice versa for down. The result is summed for the whole list and then plotted. Here’s an example plot:

You could adapt this tool to other calculations or stock watch lists. Instructions are in the file; hopefully it’s self explanatory, but I can answer any questions you may have as you try to modify it to your needs.

The CAT tool is for donors only. You can download the file “CAT_TOS_DDE_Recorder.zip” from the Donors Only folder under the “Released Tools” section on my Google Site. If you haven’t yet become a blog donor, you can do so now by clicking the donate button. Donate an amount you feel my tools are worth to you.

If you want me to make you a custom version of the CAT tool, you can also email me for a price quote. Thanks for your support!

Tags: , , , ,

49 Responses to “Complex Analysis Tool (CAT): Analyzing and Recording Think or Swim DDE Data in Excel”

  1. Fergal Says:

    Great Job on the CAT tool,

    Is there anyway that you can set it to start the recording at a certain time for example 9.30 in the morning or whatever time you wanted?

    thanks again

  2. Prospectus Says:

    Sure, that’s easy. I’ll make a mod when I get the chance and add that in.

  3. Fergal Says:

    thanks, appreciate it, is their any reason why the recording stops @260 entries..

    Maybe its the way I have it set-up?

    thanks again..

  4. Prospectus Says:

    Send me a copy of your sheet so I can look at it. It should record and plot up to 30,000 datapoints.

  5. CAT Tool Plot and Update: Dollar Weighted OBV for ES « Read the Prospectus Says:

    [...] I updated the dollar weighted CAT tool to record and plot the ES price (or whatever you put in your list as the first symbol), as well as [...]

  6. Linking Excel and Think or Swim for Streaming Real-Time Calculations « Read the Prospectus Says:

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

  7. Tom Says:

    I need to keep track of a hand full of funds. To track there percentage gains or losses over time. To be able to set some date in the past as a start point and have it update as the fund or stock changes price. I don’t care about volume or anything else. Do you have such a spreadsheet? I have TOS so I can DDE to it.
    Thanks,
    Tom

  8. Seltzer Says:

    Tom, go see an example at excelios.com, that might be what you are looking for!

  9. Tony Goudie Says:

    Hello:

    I want to configure program to load tick data at 1 minute intervals, for example: 20 – 1 minute periods. Is there a way to get this data to flow into a histogram chart that plots the increase/decrease over time?

    Thanks,

    Tony

  10. Prospectus Says:

    If I follow what you’re saying, then yes you should be able to do that.

  11. me yu Says:

    is the CAT compatible with Open Office Calc?

  12. Prospectus Says:

    I don’t think so. It uses VBA and I don’t believe that open office supports that. Does open office even have programmable macros at all?

  13. me yu Says:

    Do you have any screen shots that show some of things the CAT can do?

    Can it capture live data streams for any instrument and in what format, etc.

    It looks like an interesting tool, I’m just not sure if i understand it’s functionality.

    Thanks

  14. Prospectus Says:

    Yes, good idea

  15. Jeff Izzo Says:

    will the file open in Excel 2010?

  16. Prospectus Says:

    I don’t see why not. I had one report of it not opening, but I asked the person to check their macro security settings and I haven’t heard back yet.

  17. Prospectus Says:

    Got word back, if it doesn’t open, you have to check your macro security settings. Then it will work.

  18. stan Says:

    I am experiencing several problems with this app.

    When I change the time to 1 minute on the record page, it doesn’t record.

    I get runtime error 13, type mismatch.Fields update, so link working. It won’t allow me to add a record in the Generator.

    Causes excel to crash. Using 2007. Very irritating.

  19. Prospectus Says:

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

  20. Ky Vargas Says:

    I am trying to capture futures data:
    /RB gasoline, /HO heating oil, /CL crude
    When I enter these symbols, I get #REF.
    Any suggestions?

  21. Prospectus Says:

    Email me your file and I’ll take a look.

  22. Ky Vargas Says:

    Oh Great One.
    I find myself humbly apologizing for my impetuous cry for help.
    After I saw your quick reply (within 4 minutes of my post!) I decided that this was probably an ID10T error and I should work harder to understand before bothering you. I failed to enable the linked data in the Excel dialog box. This works nicely. Thank you so much for your effort and expertise.
    Ky

  23. Ky Vargas Says:

    If I needed to capture the close of three futures every five minutes, how would you suggest I use CAT to do that?

  24. Prospectus Says:

    You set the time interval to 5 minutes, and on the generate worksheet you choose “close” from the dropdown list. It’s not going to match the TOS chart candles exactly, just FYI. Try following the instructions in the comment in the cell, but let me know if you still need help!

    I still need to make a version that is more general and easy to use. It’s on my list.

  25. Ky Vargas Says:

    Pro:
    Got it. Do I need to run separate instances of the Excel workbook in order to get three different datasets? I tried copying the worksheets, but only the original populated. :(

  26. Prospectus Says:

    For now, yes. The general mod will let you do more.

  27. Roger Says:

    Hi,

    Is there a way to download and monitor not only TOS DDE price data, but data genrated by TOS studies? I would like to plot historical and live streaming signal price points with the price data in a signal(s) field.

  28. Prospectus Says:

    Data from TOS studies is very limited at the moment. It doesn’t update live like the built-in data fields do. Also, it’s not possible to backfill historical data with DDE. It’s only what’s currently streaming now.

    You can calculate your own signals on the excel side, but if you need high accuracy on your open/high/low/close data, then it’s not going to be very satisfactory because the DDE is relatively slow and your reading may differ slightly from the “official” bar on the TOS servers.

  29. Michael Says:

    Hi:

    This may be a question which is similar to the last post: Does the Complex Analysis Tool permit one to export historical data (daily closing prices, indicator values from such date – like RSI and stochastics) from TOS to Excel? Many thanks.

  30. Prospectus Says:

    No it does not. You can only record and save live data. TOS doesn’t support backfill.

  31. Paolo Filippi Says:

    Is this module useful (customizable) for saving (and graphing) other (than ThinkDesktop DDE) streaming DATA?

  32. Prospectus Says:

    Not really. It’s pretty specialized. Is there something in particular you are trying to do?

  33. Paolo Filippi Says:

    I’d like to save the DDE streaming data from another platform in a file.
    (Quicktrade.exe the source an Italian trading platform).
    Don’ t you think is necessary only the modification of the DDE link?
    (excuse me,but I’ m a really newby in DDE and VBA managing of excel so probably a dummy question…)

  34. Prospectus Says:

    Yes there would be some code to change, basically the format of the DDE query.

  35. Paolo Filippi Says:

    Thanks.

    Curosity.
    Are you collecting the data for backtesting on options?

  36. Prospectus Says:

    Not personally, no, but you could do that. The only thing is that the data is dirty. I mean it’s only a sample every second or so, and only while you have the tool running. You don’t get every tick either, meaning you could miss a very rapid spike. If you are backtesting on a larger timeframe and don’t need really small resolution on the quotes then maybe that’s ok.

  37. Michael Says:

    Great job!! I am wondering if values of other indicators included in TOS can be exported to Excel – for example RSI, Stochastics, %Rank?

  38. Prospectus Says:

    Not at this time. The custom fields don’t update through DDE. I’ve told the TOS developers that we want that, so we’ll see what happens.

  39. Paolo Filippi Says:

    Thanks for your work.
    I’ m trying to save the data export from ThinkDesktop in your
    CAT_TOS_DDE_Recorder file, exactly in the Recorder Sheet.
    This are the streaming data I want record

    https://picasaweb.google.com/116996436871989141640/28Mar2011#5589124687339097746

    Can you explain me the step to
    save data?
    Where can I define the resulting saved filename? Ex. YM28032011_1,etc?

    I’ ve tried to follow the instruction you have included but no result.

    Thanks

    Paolo

    Be patient, I’ m an excel newby.;-)

  40. Prospectus Says:

    Did you enter the tickers into the generator sheet and click generate?

  41. Paolo Filippi Says:

    Yes.

  42. Prospectus Says:

    Send me the file you’re working with

  43. Paolo Filippi Says:

    Pro Let me understand.
    i)From the ThinkDesktop print icon I choose “Export to Excel”
    ii)In a GENERIC excell sheet i CTRL-V and observe the streaming data

    so data is streaming.DDE is ok.

    So,assuming for exemple I’d like to save VIX (ES,YM,whatever) options chains stram data using DDE recorder.

    i)cancel all symbols from “DDE generator” sheet and insert only VIX.
    I find the correct Last/volume on “DDE generator” sheet.,
    ii)IN the “DDE recorder” sheet a number of #RIF symbols.
    iii)Which is next correct step?

  44. linh wynn Says:

    My excel request security “enabling” then crash (“Not Responding”) after I enable to get updated data with your TOS Template. CAT seems very useful, but how do I prevent crashes?

  45. Linh Says:

    I downloaded your CAT program. It seem to work, but if you pick intervals of say 10min (or 15min) it generate 3 or 4 data point at a time every 10 ( or 15 min). How do I correct this so it only genrate one data point?

  46. Prospectus Says:

    Send me a screenshot of what it’s doing

  47. myshortpencil Says:

    The custom fields still do not appear to be updating. Is there a way to configure Excel to create a variable that shows the volume traded during the past 5 minutes? Thanks

  48. open source Says:

    open source…

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

  49. DDE to RTD Conversion | Read the Prospectus Says:

    […] working on an update to the CAT tool that will use the new RTD components instead of the old broken DDE ones. Stay tuned for more […]

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 107 other followers

%d bloggers like this: