Coming to terms with SpreadSheet software

[K.I.L.E.R]

I'm slowly coming to terms with Spreadsheets, as they seem very powerful nowadays. I've just been playing with OpenOffice.org Calc and found Basic Scripting, database access and commands along with much more that I need to learn about.

An interesting question comes up. For data analysis purposes, is it better nowadays to leverage the power of Spreadsheets than using common analysis environments like R and S+? For everyday use on trivial matters I believe this is the case, but what about complex cases? Has anyone had any experience with this?

I've personally been using various environments to do my work but I'm believe a Spreadsheet may be better for these purposes. Some research staff made mention that they don't use things like S+ but instead prefer Excel.

 

[Simon F]

Kruno,
I've often used excel for prototyping, say, mathematical calculations. The graphing facility is very useful and being able to trivially import "comma separated value" files (.csv) can be very handy.

 

[Captain Chickenpants]

Definatly worth using spreadsheets. They can be pretty powerful. Another handy feature is the fact that you know most people can read them.

If you want a slightly different useage of spreadsheet functionality (perhaps not the type of feature you were thinking of), check this out.
http://ouseful.wordpress.com/2008/10/14/data-scraping-wikipedia-with-google-spreadsheets/
CC

 

[Cheezdoodles]

What kind of data are you analyzing?

For stocks all you need is excel and knowledge of markowitz mean-variance method.

 

[Saem]

One has to be careful especially of Excel, I can't remember the details, but I remember a stats prof once remarking it didn't entirely respect order of operations.

Also unless things have changed, Excel doesn't support large data sets, again that depends on what you're doing.

Generally speaking though, I love spreadsheets, their implementation could use some work, rather than having a giant spreadsheet, having lots of small ones that could work together easily would be nicer.

 

[K.I.L.E.R]

What kind of data are you analyzing?

For stocks all you need is excel and knowledge of markowitz mean-variance method.

No, nothing as serious as that. Just hobbyist analysis things.

 

[Simon F]

One has to be careful especially of Excel, I can't remember the details, but I remember a stats prof once remarking it didn't entirely respect order of operations.

IIRC floor and ceiling (or perhaps round) don't work correctly (or at least not how you'd expect).

 

[K.I.L.E.R]

Wouldn't it be awesome to have GPGPU in spreadsheets?

 

[Cheezdoodles]

Also unless things have changed, Excel doesn't support large data sets, again that depends on what you're doing..

How big data sets are considered large?

Im currently sitting with a spreadsheet containing 20 year daily returns for 70 stocks, so thats 5000rows and 70 collums. No problems. Only problem is when i tried to use excel solver to do markowitz optimization is that current CPU power is just far to low.

 

[Malo]

Excel doesn't support over 65k rows

 

[Davros]

Ac/Dc in ms excell

dl the xls file here
http://www.acdcrocks.com/excel/

 

[Bambers]

Excel doesn't support over 65k rows

in 2003, only 256 columns too


2007 supports 65k columns and 1 million rows.

 

[Cheezdoodles]

1 million rows should cover most needs i reckon

 

[Frank]

Spreadsheets are great up to the point where you want to add program code. Somewhere after that point you'll realize that it's not up to the job.

And for complex stuff, you should store the data in a database, not in cells.