I'm trying to analyze a mechanical trading system I developed using Excel, but I'm beginning to think XL may not be the best program to use. The the size of the file is approaching 30mb and contains over 25 worksheets. This represents the long side trades only. I had to put the short trades into their own file to keep my computer from slowing to a crawl. The system is designed to scan a population of around 500 stocks and trade a basket of them with an approximately equal number of long and short positions. I'm currently testing the system over the last 5 years using daily OHLC data. My main problem, so far, is that I've just now finished all the necessary equations, but I'm only scanning the ten stocks I picked to test that the equations were correct and the file size is already over 60mb total. I realize that I may be asking a bit much from a celeron with 512mb of ram. So I'm running into both system resource limits and limits within XL. My version only has a max of 256 columns. Each worksheet will need to have a matrix of approximately 1400 days x 500 stocks. Am I using the wrong program to do the analysis? I like to actually see the data and work with it as opposed to having a program just spit out trading results for a set of parameters. What software do system traders use in order to develop and test strategies? Any suggestions would be greatly appreciated. Jeff.
What you need is to carve out the calculation and raw data into a separate program that does the number crunching and data storage, and use Excel as your main user interface.
"Carve out?" What would be a good choice for a program to do the number crunching. I first stared using Excel, because it could do a fair amount of number crunching. I do agree that I need to keep the raw data in a separate file from the file or program that does the number crunching. The final output or trade decisions should also be kept in a separate file. The calculations are all simple; mostly averages, stdev's, if then statements. Its just the number of cells involved that make it cumbersome. What is a good program to perform simple calculations on a mass of data? The lighter the programming skills involved the better.
Since you prefer Excel, you can use VBA as your programming language. You can declare a few large matrixes to hold the data read from a text file, then your Excel/VBA operates on the matrixes. This approach is similar to what you are doing with a spreadsheet but more efficiently.