Excel Trading right from Spreadsheet?
- Thread starter scooke
- Start date
Well, at least someone else said it. Excel is a tool, not a trading platform. Could it be made into a Trading platform ? Of course it could. However, it's single-threaded architecture limits it's potential to handle a large number of symbols.
re: IB datafeed.....
Theoretically IB's RealTime bar feature eliminates the problem of the missing ticks that are present in it's sampled data feed.
I don't use IB and I have never used their VWAP function. Every time I've calculated VWAP I have used the formula and raw data.
Sorry.
http://en.wikipedia.org/wiki/VWAP
^^^for reference.
This information simply is not true.
http://msdn.microsoft.com/en-us/library/bb687868.aspx
2007 & 2010 have the ability to use between 1 and 1024 threads. See the link above to MSDN article. I agree that DDE and RDP are slow and not ideal however for what you get with Excel 2007 or 2010 (realtime DDE/RDP, multi-threadding, VBA integration, tabs, Access integration, etc.) its not a bad starting point and for most they don't have the execution platform or the data to ever need or use more horsepower than what Excel delivers.
I wish people would spend 5 minutes Googling this stuff.
How do they do that? By creating or editing the bars after the fact? With TCP data you simply can not capture every tick 100% of the time. Even with UDP data packets are missed and that data is gone forever.
It can be done but it is not easy stock excel.
You need to break it down into a few distinct processes:
Excel Tick Engine:
Excel by default limits DDE updates to 100ms.
Takes a lot of coding work and testing taking control of the calulation engine in excel to break this barrier.
I posted some sample code here:
http://www.elitetrader.com/vb/showthread.php?s=&threadid=216959
Next issue is storing the volume of tick data:
Ended up using thecommon.net 's toolset to use sqlite as the data store for our excel applications.
The above will take some time but once completed you have your a single threaded ticker plant that can capture and process multiple data feeds. We lock it down into an EXE using XLtoEXE and it runs in it's own space.
Order and position Management
Create another excel workbook application for managing and processing orders. You want to use your ticker plant as its data source and run this in another instance of excel. Both applications are single threaded but run concurrently in different excel instances and can even run on different machines . You build one of these apps for each broker you use and feed them into a global risk management workbook app. Once again store all position and order data off to sqlite db.
Excel is used as a calculation engine but no data is actually stored in any of the workbook applications. Using multiple instances is a band aid to get past the single threaded architecture... remember I said band aid... All tick and order data is stored in sqlite db.
Our main benefit of going through this trouble was to create an excel add-in for other non-programmers to use excel to analyze both real time and historic data for testing and develop trading algos.
You need to break it down into a few distinct processes:
Excel Tick Engine:
Excel by default limits DDE updates to 100ms.
Takes a lot of coding work and testing taking control of the calulation engine in excel to break this barrier.
I posted some sample code here:
http://www.elitetrader.com/vb/showthread.php?s=&threadid=216959
Next issue is storing the volume of tick data:
Ended up using thecommon.net 's toolset to use sqlite as the data store for our excel applications.
The above will take some time but once completed you have your a single threaded ticker plant that can capture and process multiple data feeds. We lock it down into an EXE using XLtoEXE and it runs in it's own space.
Order and position Management
Create another excel workbook application for managing and processing orders. You want to use your ticker plant as its data source and run this in another instance of excel. Both applications are single threaded but run concurrently in different excel instances and can even run on different machines . You build one of these apps for each broker you use and feed them into a global risk management workbook app. Once again store all position and order data off to sqlite db.
Excel is used as a calculation engine but no data is actually stored in any of the workbook applications. Using multiple instances is a band aid to get past the single threaded architecture... remember I said band aid... All tick and order data is stored in sqlite db.
Our main benefit of going through this trouble was to create an excel add-in for other non-programmers to use excel to analyze both real time and historic data for testing and develop trading algos.
I agree. They do create the bars "after the fact".....so it's not truly "Realtime". That being said, it does effectively capture every tick, but the ticks are "packaged" into 5 second bars.
If your algo trades every 20 seconds or so, this is not a good solution. However, if your average time-in-trade is 5-10 minutes, this is not a bad way to go.
If you are dropping packets you are doing something wrong.
Also, usually data is not "gone forever." In most UDP feeds you can request missing packets or messages be resent. See, for example, the moldupd spec attached.
I'm not sure what to tell you - I think its virtually impossible to capture every packet. I don't know anyone who captures 100% of the data. It could also be a function of our data provider and the method of delivery - we use multiple NICs across a variety of ports to spread out the data delivery (across an Intel quad NIC). Its still fed to the same central data parser but we decided it was easier to call stale quotes via TCP (from Bloomberg over T1) than it is to keep track and fight with our data provider to call back historical UDP data.
Either way its historical data at that point and not worth it. The only time we call on stale quotes is in very thin stocks that may not print in 5-10min so we monitor those situations and call TCP data to make sure we didn't miss that one UDP packet that updated the thin stock.
That said - none of this is done in an excel spreadsheet. Its all Linux CLI or Server 2k8 (shell) on colo machines so probably a better topic for another thread.
Do you have an approximate capture/drop rate? We drop/lose roughly 3,500-5k packets per day.
Use Total volume as your check sum.
If your trying to capture last,ask,bid, ask_size,Bid_size,Last_trade.
Compare the running totals in your bar to total volume from the start of the bar and adjust your data by volume.
Your going to have a head ache dealing with the order of processing data if you rely on retrying.
If your trying to capture last,ask,bid, ask_size,Bid_size,Last_trade.
Compare the running totals in your bar to total volume from the start of the bar and adjust your data by volume.
Your going to have a head ache dealing with the order of processing data if you rely on retrying.