Hey guys,1.what's the best way to push real time data to Excel as well as sending data from Excel to real time source...How do you guys implement such system usually?Mind sharing?Do you guys use a RTD server or what?2. How do you get/send data from/to a Database in Excel? DO you create an add-in using C# or VB to handle it? or just using VBA ?THanks

We built an RTD Server to provide realtime data to Excel; straightforward and works well. You can use the same protocol to send data from Excel to some other program, but it is considerably less elegant.

ADO can be used to get/send data to database. you can use vba or more fancy, .net

RTD is the way to go. It's a million times better than DDE.Please NOTE that RTD should only be used to get prices into Excel. You should never try to use RTD to export prices to other sources. If you need to do this then Excel is not the right choice. You should be creating a server application in C++/Java/.Net etc ... If you follow the Excel approach then you will be hacking a solution together which I am sure will get you cursed by several people in the future who want to use your application.Excel can very easily import data from a database. There is a wizard for this. You can also use VBA or add this as part of the RTD interface.

You could use Cascade DataHub to move data between Excel and web browser, between Excel and Excel on a network or between custom applications and Excel. It uses DDE and TCP as the underlying mechanisms. I know that people here generally claim that RTD is better, but it isn't clear-cut. For single-value transmissions, DDE and RTD are similar in speed. It's hard to make a claim that one is better than the other.RTD does not support ranges, so every data item must be transmitted individually. For systems that move a lot of data, DDE with ranges is hugely more efficient. We have seen systems moving 4000 cells, 5 times per second, from Excel on one computer to 8 connected Excels on separate computers on a LAN. There is simply no way that RTD could do that.Also, because RTD is a COM interface, it gets preferential treatment over timers and user interface in Excel, causing Excel to delay computation and timers in favor of RTD communication. When you combine the lack of range support with this preferential processing of RTD, it is actually quite easy to put Excel into a state where it spends most of its time servicing the RTD events. This causes timers and DDE communication to glitch, and computation to be delayed.DDE Advise is asynchronous, where RTD is synchronous. This has a lot of meaning in (pseudo)real-time systems using Excel. If an RTD server transmits data more rapidly than Excel can handle it, the result is that Excel will get further and further behind "real" time. If the RTD server does not handle this, then the result is that Excel is constantly late. If the server gets its data from a downstream TCP feed, for example, this will place Excel into a state where it is permanently as far behind as the data held in the TCP socket buffer (typically 64K of data). DDE includes a mechanism where Excel will skip intermediate values in favor of the most up-to-date values, on a per-item basis. Consequently if your data comes faster than Excel can process it the older data will be skipped in favor of the newer data. You will always have the most up-to-date information for each value even if you did not see every change.Generally I think that the RTD behavior in overload is bad. DDE does the "right" thing for real-time systems. Combine that with DDE's support for ranges and I think that DDE probably has the better overall behavior and certainly the better bulk performance.Ultimately the decision rests on the behavior you want, but don't think that RTD is better just because it's newer.

QuoteDDE Advise is asynchronous, where RTD is synchronousRTD is asynchronous as well, I am not sure where you got the idea that it wasn't. The fact that you are sharing data between Excel instances etc ... is not very efficient and Excel was not intended for this use. RTD and DDE are intended for the insertion of data into Excel. A more scalable approach would be to implement this functionality into a client / server architecture and use Excel as the light-weight client only.Casade does look interesting. Do you currently use it? I bet it causes a nightmare for IT if it's widely used.If used correctly RTD will significantly reduce the load on Excel compared to DDE.If you want more than a few updates per second in Excel than you should really be thinking about another way to implement your functionality because Excel is the wrong choice. Excel is good for prototyping functionality but the end game should be to move to a more stable distributed solution.

QuoteOriginally posted by: MarineQuoteDDE Advise is asynchronous, where RTD is synchronousRTD is asynchronous as well, I am not sure where you got the idea that it wasn't. RTD appears asynchronous to the user, but the underlying communication mechanism is synchronous. All COM calls are synchronous - a server thread cannot continue until the message is delivered to the client and response is returned. In Excel, all communication and computation activity is synchronized through a single thread, even if the COM call itself is handled in a separate thread. I've heard rumors that Microsoft is trying to improve that, but so far have seen no evidence.QuoteThe fact that you are sharing data between Excel instances etc ... is not very efficient and Excel was not intended for this use. RTD and DDE are intended for the insertion of data into Excel. A more scalable approach would be to implement this functionality into a client / server architecture and use Excel as the light-weight client only.Perhaps, but the reality is that many people use Excel as both a source and sink of data. It may not be the intended use, but it can be made pretty fast, and once it's running it can be reliable as well.The scalability problem is solved by moving the transmission infrastructure away from Excel. If you use Excel as a server you're asking for trouble, but if you use Excel as a data source and move the communication to another application then you can have any number of clients using Excel-sourced data. I've seen a single Excel serve data for 900 traders from a single machine at a rate of 50 data changes per second. You may not need that load, but the point is that Excel is only a bottleneck if you implement it as the bottleneck.QuoteCasade does look interesting. Do you currently use it? I bet it causes a nightmare for IT if it's widely used.I work for the company that make Cascade DataHub. We spend a fair amount of time with clients to implement some pretty complex systems that include Excel into the mix. People are using it for things like ETF arbitrage, real-time risk analysis, futures trading, energy trading and more. It doesn't normally cause a nightmare for IT because it is generally IT who implements it. The traders are unlikely to set up the system themselves. They just want the analytical data in milliseconds instead of minutes.QuoteIf used correctly RTD will significantly reduce the load on Excel compared to DDE.It depends on the nature of the data. Sometimes it will, sometimes it won't. As the data set grows DDE can swiftly overtake RTD for performance by using ranges. COM calls are not notoriously fast, just more flexible than DDE.QuoteIf you want more than a few updates per second in Excel than you should really be thinking about another way to implement your functionality because Excel is the wrong choice. Excel is good for prototyping functionality but the end game should be to move to a more stable distributed solution.That happens. We've seen people create entire automated trading systems using Excel and Cascade DataHub, then just replace the Excel component with custom computation applications in .Net while retaining the DataHub communication infrastructure. Frankly, we've seen a lot more people just stick with the Excel based system because it tends to work fine once it's designed. It saves a lot of development if you don't have to re-write your analytics in custom code.

After extensive testing of low latency feed handling we can vouch a properly designed excel using DDE can outperform RTD/COM significantly.In our specific case we are able to process 140 symbols via DDE, calculate 2200 synthetic Arbs and write the results off to SQL db using excel at a precision of 5ms.Precision in our context is the frequency Excel is ready to process new market data. This takes some skills as there is a plethora of misinformation and generalized statements on the web regarding RTD/DDE.Running the same feed and process using optimized RTD (On Calculate instead of setlinkondata) the precision drops to 120ms. DDE is not necessarily 25x faster but excel is able to accept new data 25x more frequently.RTD pushing data into Excel is very similar to writing a VBA loop to load an array back to a range. The general rule for performance is to minimize these types of operations and move in blocks (Redim array to range).DDE links are essentially memory pointers and operate similar to a formula recalculation if implemented properly. The key is using setlinkondata to trigger a volatile calculation... ie. timestamp.This will force excel to "recalculate" dde links and load all of the latest updates in one shot/one calc cycle.

QuoteOriginally posted by: isaaccoolHey guys,1.what's the best way to push real time data to Excel as well as sending data from Excel to real time source... How do you guys implement such system usually? Mind sharing? Do you guys use a RTD server or what?What kind of data volume are you talking about? RTD usually is sufficient for human use (how fast can a human catch a flashing cell?) and RTD is very easy to write using C# too. However if you plan to build a robot for auto-trading, Excel may not be the good start point anyway (as Excel may be the best user interfacing / interaction tool, but not the fastest analytic engine.)Quote2. How do you get/send data from/to a Database in Excel? DO you create an add-in using C# or VB to handle it? or just using VBA ?There are many ways. I guess the most common are UDFs written using either C# or VBA. If you are reading from an ODBC data source, you can just use the built-in range query function too ....You can find ready-to-compile C# examples for RTD and UDF here

QuoteAfter extensive testing of low latency feed handling we can vouch a properly designed excel using DDE can outperform RTD/COM significantly.I can believe that.But DDE is a form of interprocess communication, so I think using it in the same breath as low latency, is that not a kind of contradiction? What about context switching? I have not used DDE but it feel a bit clunky for low latency because of the shared data between processes? Have you compared DDE with other solutions besides RTD? xll? For heavy computation Excel is not fast and as mentioned zhouxing C# RTD does suffice in many cases. It all depends on what the requirements are...

Low latency was the wrong phrase but in the context of this thread relates to pushing data in and out of Excel comparing DDE versus RTD.

QuoteOriginally posted by: IQuant1Low latency was the wrong phrase but in the context of this thread relates to pushing data in and out of Excel comparing DDE versus RTD.Have you published your work (public domain?)

No we have not, If this is an area of particular interest to you send me a PM.

Above is a 7 minute sample comparing DDE to RTD for capturing and processing market data.For our particular case we:Receive Market DataCalculate ArbsPublish DataArchive to DB.The data range is a 2500 x 20 cells, both DDE and RTD processes are taking control of Excel calculations. (Breaks Copy-N-paste / Marching Ants)DDE updates are triggered by setlinkondata calling a DDE Pump to coerce all pending updates in one cycle.RTD uses worksheet_calculate event with RTD throttle set to 0.Data Cycles = Excel Calculation CyclesFor DDE 1333 Calculation cycles processed price changes and 49879 cycles updated just cells in the sheet.The price change processing / publishing has overhead but the overall sustained precision for DDE was 8.03ms.For RTD 1825 Calculation cycles processed price changes and 11104 cycles updated just cells in the sheet.RTD Precision was 32.44 ms.If we run the same RTD method and let Excel manage the calculation cycles our precision drops to 140ms.The push pull mechanism of RTD is much more user friendly and for most perceived real time applications more than sufficient.There is substantial overhead in the RTD shoulder taps and method used in Excel to fill cells when compared to DDE.DDE running at a higher frequency was able to capture more price changes as expected.The objective in our specific case was to increase the frequency at which excel can be made ready to accept and process new data.In our specific case the DDE method processes new data 4x as frequently as calculation optimized RTD and approx 20x faster than Excel managed RTD throttled at 0.MS published a document many years ago stating a single topic can be updated 200 times per second. This 5ms barrier appears to be in effect today as no matter how much hardware we throw at it we can't break 5ms.We are able to update entire ranges near this frequency and suspect this to be a windows messaging limit. The best sustained frequency for excel cell updates we have achieved is 5ms for DDE and 16ms RTD.This info is not intended to be authoritative, just observations from a specific real use application.

Great stuff IQuant1. Thanks for posting actual data.