Status Update: Complex Analysis Tool (CAT) for Excel and Think or Swim

As I wrote before, I’m working on a tool for my blog donors that will allow recording of the DDE data from Think or Swim in Excel for doing time-based advanced calculations. I’ve got the data recording working now.

I put the recording logic into my example file for calculating the average bid / ask spread of the S&P500, and hooked it up to a plot. The logic records the DDE values once per second, and saves the value if it changed from the prior saved value. The plot updates in real time as the new values come in. Here’s a snapshot of the average bid / ask data for the S&P500 stocks from today, starting at about 2:15 PM ET (13:15 CT in the plot) and going until after the close:

baspread_large

Can you tell where the market closed without looking at the time axis? I watched it real time–it was cool to actually “see” the close unfold.

Here’s a zoomed-in view:

baspread_zoom

It was interesting to me that the spreads narrowed going into the close, indicating more trading liquidity, and then blew out after the bell as order flow stopped. Nothing too earth-shattering discovered here, but it’s a demo of the recording and plotting capability. There’s no reason you’d want to plot price in this tool–it’s only good for the kinds of things you CAN’T plot in Think or Swim. Next to come are the alerts and a couple of other things. Stay tuned!

Tags: , ,

12 Responses to “Status Update: Complex Analysis Tool (CAT) for Excel and Think or Swim”

  1. Adrian Says:

    Hi Pro,

    How do you record the DDE values?
    Is it possible to get previous values by using DDE (ex. previous day’s close price)?

    Thanks.

  2. Prospectus Says:

    I’m recording the DDE values by writing a VBA macro that puts all of the current data values of interest into another location in the spreadsheet, where I keep them for plotting. Then the macro tells itself to do it again 1 second in the future. It’s not elegant, but it works okay. I haven’t figured out how to record every single change as they stream in, but that may be beyond the scope and speed of working with Excel anyway.

    You can get the previous day’s close by using one of the “custom” datafields. in Think Desktop, you go to the watchlist screen, and right-click where it says “Symbol” and choose “Customize”. Then scroll down in the list and click on the paper icon next to one of the custom fields (Custom1 for example). Enter this code: “close[1]” and click “Day” in the Aggregation box. Then you can use my template file to grab that Custom1 field along with any others you want.

  3. Adrian Says:

    Can you share this VBA macro? I’m not good at VBA …

  4. Prospectus Says:

    This tool is on the back burner. I’ll share it when I clear out some other projects that I’m working on.

  5. Adrian Says:

    Can you check the tool with OpenOffice? Some people are using this very powerful office suite and not MS Office.

  6. Prospectus Says:

    It looks like it would work with Open Office. There would be some re-write work needed, though. Something to look at in the future.

  7. Donald Kagan Says:

    Neat stuff, I have built a very similar system in VB which watches for Bollinger band breaks and generates an alert when there is an event.

    I am having a bit of an issue with a new project, I am attempting to create another alert system but one based off of certain ATR values, I am trying to build a single system which will be able to scan currency pairs as well as single stocks, but that may be impossible. Time will tell.

  8. Fergal Says:

    Has the Tool for Recording and plotting become available..

    thanks

  9. Prospectus Says:

    Not yet. It’s been sidetracked by other things, but still on the list.

  10. Steve Says:

    Excel is not really efficent at capturing DDE data, but it does work well for real-time data mining with complex formulas. I find it provides a serious edge when combined with thinkscript. I’m working on having excel generate custom order files with auto execution by the platform. Anyone else try this?

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

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

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

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

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


%d bloggers like this: