Hi, this is my first post here and I'm looking for some guidance regarding data management software choice. Basically I'm looking for a tool that would allow me to clean up raw data (remove empty columns, missing values), add custom/calculated columns, summarize intraday data to daily data and filter it with different criteria (kinda like pivot tables) and plot charts. It seems like excel would be the perfect solution, unfortunately the maximum rows limit and the fact that its impossible to include moving averages in pivot tables makes it useless for this task. Also, if possible, I'd rather be using something that wouldn't require much programming or scripting. Thanks in advance, goodfella
You're actually talking about a custom programming effort (forget Excel for this task). It's not particularly complex though and a decent program could do it in a few hours. You could decide on the programming language that you could understand and then use a web site like: http://www.odesk.com/ to find people to do the job. Programming rates are $10-$40/hr on there.
For the first thing BUY EXCEL 2010 and INSTALL 64 BIT VERSION. You will be kind of surprised by the row limit.
Thanks for the replies guys. I'm meeting a friend next week who might program it for me, until then I'm gonna be checking Excel 2010 (cause apparently the PowerPivot add-in can handle millions of rows since it uses SQL Server).
I see in Excel 2010 x64 there is a "greater than 2GB RAM allocation..." But it does not say what max is. Is it now unlimited just like the OS on x64?
Actually x64 is not unlimited. The limit is just unrealistic, much as the 640 kb once were. It basically can allocate as much as a 64 bit allocation can, which EFFECTIVELY means you can have fun with a 64gb workstation. Excel + Access are pretty much the only reasons to ever go with 64 bit office (64 bit needing word documents are not realistic).
If you are talking about tick data for "busy" instruments, I would recommend to pass on Excel. For each cell with data Excel holds information about the colour, number format etc... which is way more memory than just a number. You'd probably be better off with a database. Then all programming that you'd need is a few SQL queries.
Excel 2010 Specs: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx Jack