Well here are our some crude results from a simulation we ran on the maximum quantity of serialized price data updates we can process using excel /dde/rtd. By no means is this any type of a scientific in depth study... Just the maximum pieces of data we can reliably receive and process using our trading algo's. Dual core AMD Turion 3GB ram Excel 2007. Method 1: Circular References and Iterative calculations enabled and set at 250 Over 20 minutes sample period... 1200 seconds received and processed 58172 price updates from a local test server. 48 excel cell updates per second. 1 instrument 5 levels of DOM. Method 2: Iterative calcs disabled... DDE cell upates. Only able to process 3324 updates... 3 cell updates per second. Obviously the worksheet has been optimized for method 1 largely due to the lag experienced previously from method 2. There is a noticeable stall at the end of the iterative cycle and as orders trigger. Just error checking and data validation overhead. Considered coding portions as VBA UDF's but found the dynamically adjusting counter provided a better solution for our needs and methodology. My point is you can receive and process data 3 x faster than you can reliably execute. TT order processing varies from 20ms to 100 ms or more... out of our control. Just have to wait until our fills are verified. OEC is trickier to analyze as a positional order ID is instantly generated locally and status changes to completed upon verified fill over essentially the same varying time frames as TT. Just had to SWAG the order fill times on the safe side. Placed delays for acting on data to 100ms after placing orders so net positions and fills can be verified. I suppose if you colocate at equinox you can get data even faster directly from the exchange but you are still time cuffed to the fill servers.
Sorry...totally baffled by differences of "Method 1" and "Method 2"...please provide more technical details. I really don't understand what was different between the two. The difference is significant...of course.
Method 1: In cell a1 place formula =a1+1. Enabling iterative calculations continually updates and recalculates the cell as fast as excel and your computer can go. Enable and set parameters under excel options - formulas - 250 recalcs and precision to .0001. Hit F9 and you'll see a1 count to 250... similiarly DDE cells update very fast as well as recalcs on cells dependent on the data. Various three step circular referenced triggers perform calculations, filter tick data, data loggers, order triggers, notifications etc. We're off of time referenced increments and process pure price data inflections. Method 2: Standard DDE links with iterative recalcs disabled. DDE data stream is handled by excel... same overhead issues you have already identified. Iterative calculations is the trick to getting the data in quickly... circular referenced triggers control processing, validation and verification near real time. Just a control mechanism ... ie tick counters, OCO's... Like i said earlier in this thread... hard to explain... kind of got to try it...
Yes, and thanks...this is quite interesting. I've never fooled around with that iterative recalc setting at all. I would have thought that turning it off would make Excel perform sheet-related "normal" (non-recursive) calcs much faster.
Circular Reference Cell Calculation Sequence When your workbook contains circular references and Iteration is switched on (Application.Iteration is True) Excel has to use a slightly different calculation algorithm: The first step is the same as a recalculation (or full calculation, depending how the calculation was invoked) that does not involve circular references. This enables all the cells that come before the circular reference to be properly calculated and the list of cells in the circular reference to be identified together with their dependents. During this first step Application.Iteration is False. The second step occurs once on each iteration: The list of cells in the circular reference is calculated sheet by sheet, in alphabetic sheet name sequence (even in Excel 2002 & 2003). Within each sheet the list of cells and array formulae in the circular reference, and all cells that are dependent on them, is calculated from left to right and top to bottom. (all the columns in the first row from left to right, then the next row ...). Each array formula in the list is calculated as a single block of cells from left to right and top to bottom, so if you want to use calculation by columns rather than by rows you have to use a multi-cell column array formula. This calculation ignores the dependency tree and relies on the iterative calculation process to resolve dependencies. Volatile cells that are not dependent on the cells in the circular list are NOT recalculated on each iteration. This step is repeated until the iteration limits (maximum number of iterations and maximum cell change per iteration) are reached. During this second step Application.Iteration is True.
Did Excel 2010 introduce some interesting enhancement or features that should be worthwhile to be considered vs. Excel 2007?