Many dde link qualifiers don't work

[Esteban]

Hi, I'm new to eSignal and my main use of the program will be through the dde link to Excel, if I can get it to do what I need. I'm trying to build a time and sales table, basically preserve the snapshot quotes that come in so I can sum the ticks and volume. I'm working with the nasdaq 100 emini.

The problem I'm having is that trades that occur at the same time and same volume as the previous one don't trigger a calculate event in Excel, so the trade or trades is missed every time more than one trade of the same size occurs in the 1 second time stamp.

Some of the qualifiers listed in the help that I thought would be unique to each trade don't work. I tried tick, ticks, vol, and got errors. TotalVol gave me a number that doesn't change.

Any ideas appreciated.

Esteban

[ScottJ]

Hi Esteban,

Our DDE links are not really set-up to capture each tick and re-create something like Time and Sales. The DDE links work great when you're trying to capture data every 10 seconds or each minute but not tick by tick.

For more on our DDE links, you might want to look at our DDE FAQ's

Thanks.

[Esteban]

Scott,

You should inform your sales team about this. I was assured that not only could I build time and sales with vba in excel, but that time and sales tables are directly supported, as they are in QLink, which I currently use. This was the one basic question I wanted answered correctly when I signed up, and I made that clear at the time. The rep even checked with someone else to verify what he told me when I insisted.

Back to the details of the DDE. I don't see why you consider the eSignal DDE to be unsuitable for collecting information tick by tick. It updates instantly with each tick, and doesn't skip any that I can tell. There is no additional load on your servers, as I would be building everything in vba from the streaming quote, hardly any load at all. I would be set to go if only the volume qualifier updated as it should giving the total volume for the day based on each tick size. That way every time the volume was greater than previous, I would know that the new trade, even the same size and time as the previous one, should be added to my total.

Too bad I've started on the wrong foot with eSignal. I like the speed and accuracy of your data. However ...

Esteban

[ScottJ]

Hi Esteban,

Your initial testing is interesting. A few customers in the past have been unable to capture each tick in the manner they wanted. As a result, we tried to set expectations a bit lower in our FAQ's based on those experiences. In the end, actual results may vary by PC speed, # of symbols being tracked, volatility of the issues, etc.

We'll do some more testing in-house and perhaps surprise ourselves that it works better than expected. If you can do the same and post your results here, that would be great.

Thanks.

[jeffch]

Hi Scott, would the symptom of DDE slowness be missing ticks? What's the source of the slowness, is it DDE itself, or the client application (i.e. Excel) along with general computer and network speed? I assume the eSignal client uses DDE to talk to the data manager, and it seems plenty fast enough to keep up with tick data. Of course it's probably an optimized compiled program, unlike interpreted VB, spreadsheet macros, etc.

In that case the discalimer may be appropriate since you guys have no control over the speed of the customer's computer, network, or non-eSignal DDE client, which may indded be slow enough to drop ticks.

[DionLoy]

DDE is unfortunately unreliable for moving tick by tick data, especially with today's market volume. DDE was created by Microsoft originally as a method to facilitate automatic movement of clipboard-like data between applications. Windows itself does not guarantee the delivery of each DDE message and will sometimes drop messages if there are too many.

The eSignal client does not use DDE to communicate with the data manager and is therefore not affected by the limitations of DDE.

[Esteban]

Scott,

I'll report results when I've got some. It's hard to find time outside of trading hours, during which I can't be distracted.

Today for the first time I'm using the eSignal DDE in my trading logic. I have to do some testing before I comment, but initially my previous statements concerning speed etc. seem valid.

Esteban

[Esteban]

No wonder you've had trouble in the past capturing every tick in Excel with the DDE. The dde is triggering sheet calculate events far more often than it should! This leads to high cpu usage, making it impractical to run vba routines off the DDE.

Here's a test I just ran with a slow market after hours to track what is happening. I opened a new workbook that contains one sheet with one cell (b:1) that is a dde link to the last price field. Then triggered off the worksheet calculate event a very simple routine with only one line: range("b3").value = range("b3").value +1. This accumulates a count of 1 each time the procedure is called. Keep in mind there are no other worksheets or workbooks open in Excel.

I can see by the accumulation of the number in B:3 that the calculate event is called exponentially more times than there are changes in cell B:1. It's not just some multiple of the times there is a change in the dde cell either. B:3 will increment without a change in cell b1, in bursts. I'm guessing that anytime there is any change to any field in the symbol tracked, a sheet calculate event is transmitted to Excel.

It's the opposite problem to the one I originally posted about. Too many events. No way to control them. One high volume symbol will peg the cpu if one tries to run any vba routine that triggers off a change in the dde tracked cell, because there are so many calls per second.

Esteban

[JRR]

Hmmm. That's not what I've seen, Esteban. I have a worksheet that I'm testing with that tracks trades, quotes, volume, highs, lows, spreads, etc. in real-time with Data Manager hot DDE links.

It follows the major indices and ten of the most heavily traded equities. The calculations it performs are relatively simple...primarily deltas. Many of the cells are set up with conditional formatting so that the cell backgrounds change color under certain conditions. In some ways, a lot more enlightening than some Level II windows.

Anyway, it seems to hang right with eSignal, MetaStock, and TradeStation tick for tick, running on a P4 2.0 GHz w/1 GB and the CPU never gets over about 55%, just due to all the Excel recalculation overhead. I'm writing a DLL that ultimately will process all the data to avoid that.

The one problem I'm having is finding the right topic for the date of the last trade. DAY, YEARUPDATE, YEARREALTIME all generate errors. Any luck there?


JRR

[Esteban]

JRR,

What event are you using to trigger your vba routines? If all calculations are done in Excel cells, there won't be a problem with cpu overload.

Since no one from eSignal replied, including the dde guru whom I emailed, I have just put this to rest. If the dde is on their list to look at, it is probably way down on the list. I'll just cancel my account before the free trial runs out.

Can't help you with the day qualifier. Most of the time based qualifiers don't work.

Esteban

[JRR]

No VBA on my end, just formulae. I was just highlighting the fact that I was having success capturing high frequency data with the Data Manager. Of course, I'll see just how close they get to tick-by-tick once it's hitting the callback function in my DLL.

The worksheet was having not difficulty tracking these indices and equities during the opening hour this morning, with Microsoft leading the way at an average of ~6 trades/sec.:

$indu
$compq
$spx
$mid
$sml
$oex
$comp

msft
orcl
dell
aol
ebay
hd
qqq
spy
yhoo
sunw

Captured these fields for the above in real-time:

ask
asksize
bid
bidsize
high
last
low
open
prev
tick
ticks
timerealtime
totalvol
tradesize

Pretty impressive performance for DDE, I think.


JRR

[Esteban]

JRR,

I don't know if I'd call that impressive. You're only requesting basic quotes for 17 symbols. If you had them on a quote sheet in the eSignal program, you wouldn't think twice about overloading anything with that list. What is unimpressive is that if you run a vba routine off of anything but a timer, the cpu will peg. Doesn't work for me, I'll miss too many ticks using a timer.

I'm glad DDE it's working out for you. There's lots one can do with data in a spreadsheet. You could do even more if you could run routines off the change in values in cells.

Esteban

[JRR]

Impressive in the sense that DDE is old and tired technology.