Anything better than Excel for data analysis?

ogarbitrage · Jul 25, 2011

Excel has capacity for about 1.02 million rows, but becomes unstable with anything over about 500,000. My average file runs about 700,000 rows, but I occasionally exceed the 1.02 million limit and all data beyond that point is null.

Is an upgrade available or does anyone have any recommendations on other software to analyze and create charts with very granular data?

rosy2 · Jul 25, 2011

this has been discussed thousands of times.

http://www.elitetrader.com/vb/search.php?s=

Thomas Paine · Jul 25, 2011

You should check out Matlab. It is the best tool for data analysis.

ogarbitrage · Jul 25, 2011

Searched. Found nothing of promise.

kcgoogler · Jul 25, 2011

check this out: http://powerpivot.com/

But i think it needs external data sources to work through the limitation; not sure.
http://social.technet.microsoft.com/Forums/en/office2010/thread/ef8eaae1-8215-4f78-8333-7325319c4d68

lionline · Jul 25, 2011

Quote from ogarbitrage:

Excel has capacity for about 1.02 million rows, but becomes unstable with anything over about 500,000. My average file runs about 700,000 rows, but I occasionally exceed the 1.02 million limit and all data beyond that point is null.

Is an upgrade available or does anyone have any recommendations on other software to analyze and create charts with very granular data?

For non-real-time analysis - use a database (SQL or my SQL)

you can then filter the data in your queries and export to excel with a more limited number of rows for charts

using a million datapoints won't give you anything of value taking a look visually.
(i.e. you won't be able to see it).

but prepping data into an acceptable form - a DB is best bet.

Good luck

ogarbitrage · Jul 25, 2011

Thank you both.

The data is usually several day's worth or 24 hours of TAQ data, so it does take some work to clean it up and make it presentable. But even 1 million+ rows can be malleable.

flip · Jul 25, 2011

I would use something like R or Matlab for stuff like that

uexkuell · Jul 25, 2011

Quote from ogarbitrage:

Is an upgrade available or does anyone have any recommendations on other software to analyze and create charts with very granular data?

If you ever wrote some VBA code in Excel (which normally has to be done to do some serious analyzes) then why not completely switch to Visual Basic?

It's much more versatile and faster.

Excel formulas quickly get completely cryptic with increasing complexity.

After switching to a proper programming language everything becomes possible.

ogarbitrage · Jul 25, 2011

I have no programming experience, it's all done by my IT partners.

My needs are met by Excel for formulas, my only issue is the stability of Excel with large data. It makes working through TAQ data very tedious.

Appreciate the help everyone.