Ask Slashdot: Spreadsheet With Decent Programming Language? 332
First time accepted submitter slartibartfastatp writes "Spreadsheets are very flexible tools for data analysis and transformations, the obvious options being MS Excel and LibreOffice. However, I found increasingly infuriating to deal with the VBA--dialect functions or (even worse) its translated versions. Is there any spreadsheet that allows usage of a decent programming language in its formulae? I found PySpread intriguing, but still very beta (judging from its latest release version 0.2.3). Perl or even javascript would be better options than =AVERAGE(). Do you know any viable alternatives?"
Re:Just do it the other way around (Score:4, Interesting)
Re:Version numbers... (Score:5, Interesting)
"Not all software is versioned with the assumption that 1.0 = finished."
This is the problem. This should be true: version 1.0 should be a statement by the author that the software has reached maturity for its initial, core feature set. Users should be able to rely on this. Version 0 should be when you write your first line of code; less than 1.0 should be initial implementation effort; 1.0 should mean first stable, complete public release; and full-number versions thereafter should indicate compatibility shifts or otherwise large functional jumps.
Let's get together on this, open source nerds. Give up on the fetish for teensy weensy version numbers. I know you think it's cool, but it's not, it makes obvious that you have no regard for the meaning of version numbers. (On the flip side, software like Chrome should stop using full-number increments for every single release. That also razes the meaning of version numbers.)
Re:Are you sure you want to use a spreadsheet? (Score:4, Interesting)
I mostly agree, but MySQL is probably the wrong choice of database. For most things that you would consider using a spreadsheet for, you probably aren't concerned with multiple users with concurrent access, so you don't need a DB server, and SQLite is a much better choice. If you do need a DB server for some reason to back your spreadsheet-like analysis, PostgreSQL is probably a much better choice (if nothing else, because of the much richer query functionality; CTEs, particularly, are very useful for analysis.)
Re:Just do it the other way around (Score:4, Interesting)
(submitter here)
Yes, I end up doing this once in a while. I also use R, Perl, PHP, or even bash to process some data. However, in some cases it is handy to have a view of the data while you're processing it.
For instance, suppose you need to run a regexp function over the 12nd column of a matrix; usually I save data as a CSV, cat file.csv | perl -ne '@a=split/,/; $tmp = $a[11]; .... ' > new_file.csv, load the new CSV, check for errors, debug, repeat... sometimes is just a one-time task I need to do.
Re:What are you doing? (Score:4, Interesting)
What are you doing with a spreadsheet that you find the built-in functionality so limiting?
When the only tool you have is a hammer, every screw looks like a nail. When the only tool you know how to use is a hammer, it is the screw's fault that it won't go in like a nail when you hit it.
I remember many many years ago, the entire corporate sales database where I worked was kept in a text editor. When I pointed out that there was something called "ingres" and that maybe it would be better to keep the data in that, I was told that the person who managed the "database" knew how to use the text editor and didn't want to learn anything else. I was also told that I wasn't hired to write database code for the administration of the company, I was hired to write scientific data processing code.
Part of the problem was that DEC supplied the text editor with the system. That made it the tool of choice, even for problems it was very poor at handling. Likewise, if "large computer company" provides few or no real programming tools other than extensions to a spreadsheet, then the spreadsheet becomes the programming tool of choice. I've had several recent projects that I've had to code in javascript/HTML simply because I couldn't expect them to be used if they were programmed in perl, because web browsers come with the system and perl does not.
Re:Definition (Score:2, Interesting)
A "decent" language is one which does not use any of the following keywords:
Then
Dim
Sub
End
Just use R (Score:4, Interesting)
Spreadsheets are bad at just about everything. Use R instead. If you really need a spreadsheet, there are modules that act like a spreadsheet. But you'll be doing yourself a favor if you wean yourself off the spreadsheet teat.
R is better suited to this type of task than general purpose languages like Python. Most variables and functions in R are vectorised. It's very rare to ever have to write a for loop, which makes the language much more readable.
R is so good at this kind of thing that you don't need anything special to do a pivot table. Just use tapply() and sum(). There's also a 'reshape' package that is far more flexible than anything found in Excel.
Mathematica is vastly superior to Matlab (Score:4, Interesting)
For symbolic math, Mathematica is vastly superior to Matlab. In my comprehensive exam, Matlab said if I increased the gain of a control system to 1E8, then the following error would be zero. However, for that particular control system, I knew that this result had to be wrong. For modestly large gains, the average of the absolute value of the error should have been a constant, and unaffected by the gain. At gains of 1E8, most physical systems go unstable. The issue shook my confidence on the written portion of my comprehensive exam.
SPICE and Mathematica computed the correct result. The key difference is Mathematica is a symbolic solver. It solves the formulas, without making unnecessary approximations. Spice is absolutely amazing for control system work. It analyzes stuff that most users would be unable to model with Matlab. In particular, SPICE models output to input capacitive coupling correctly, where most other models ignore the issue. Thus, SPICE will frequently predict that a system will be unstable if the gains are sufficiently large, whereas Matlab will often predict everything is good. Additionally, after knowingly blowing the results on the written, I verified the result on a physical system. I wanted to be really sure I had the correct answers for the oral portion of the comprehensive exam.
Matlab is a numeric computation package. In the case of control systems, it quietly converts Laplace transforms into discrete time z-Transforms before computing the system response. Never trust numeric results when they disagree with the theory. To this day, I still wonder if the professor that asked that particular exam question knew about this bug in Matlab, and deliberately asked the exam question from hell.
Re:MATLAB? (Score:2, Interesting)
You can also connect R to Excel. That way you get Excel layouts and those tools, but can utilize R for the calculations and advanced features.
Excel won because it was better? .. (Score:2, Interesting)
Then why did Microsoft have to expend so much energy in killing Lotus 1-2-3
"Why was Lotus told that the shell would not be OLE enabled [edge-op.org] when In fact it is? Why was Lotus not given earlier warning if there was a change of plan? We're still lacking useful documentation on OLE in the shell - is there any"?
"OLE Forms are a counterpart to OLE controls and a cornerstone of the Cairo user interface architecture. We were recently informed by a Microsoft employee that responsibility for development of this operating system feature has been transfered to the Microsoft Office applications group [edge-op.org]."
"I'd be glad to help tilt lotus into into the death spiral [edge-op.org]"
Re:My problem is quite the opposite. (Score:5, Interesting)
Excel was okay. Word was acceptable.
Wordperfect was excellent, 123 was excellent.
Wordperfect + 123 was twice the price of Word+Excel+Windows, wasn't integrated and couldn't multitask.
Microsoft outcompeted Wordperfect and Lotus by combining the marketplaces so that they couldn't compete. By the time Wordperfect (+Quattro) and Lotus (+Amipro) created their office suites and targetted the Windows platform, it was already too late.
Re:SIAG - Grid in a Browser (Score:3, Interesting)
Similar concept, but the other end of the technological timeline is the ExtJs grid control [sencha.com] (comes with some excellent docco [sencha.com])
You pick up a whole lot of complexity with the ExtJs framework, but you can pretty much implement a whole spreadsheet on it (someone has! [sencha.com]), and it's all with Javascript since it's in the browser...
Might not be the same experience as local spreadsheets (no saving to a file :-( ), but it is extensible.
Re:My problem is quite the opposite. (Score:4, Interesting)
I heard an interesting story from, oddly enough, a MathWorks rep (the people who make MATLAB) about the early days of Microsoft Excel. I don't know if it's true...I think he said he'd either heard it or read it online from an early Excel developer, but my quick Google-fu didn't turn anything up. In any case, he said that after Microsoft first released Excel, they went out to their business customers to figure out how they could improve it. They were flabbergasted to find out that people were using it in completely different ways than they imagined. Even though (I believe) it was originally designed for data analysis, a great number of people weren't even using it for calculations at all. They were using it for to-do list tracking, calendars, structured text documents, presenting tabular data, etc. That's why Microsoft was the first one to have a spreadsheet that allowed the user flexibility to change its appearance: fonts, colors and the like.
He was explaining this as part of his justification for coming out and talking to us, but I think it's also telling that their customers weren't using it like they expected. I guess this is really just a long way of saying that once you get to the point where quick Excel formula isn't cutting it, it stops being the right tool for the job.
With R you need a spreadsheet to do what R doesn't (Score:4, Interesting)
Like when I want to actually look at my data in column format (scrolling, frozen panes, column hiding, conditional cell colouring anyone?). Or when I need to edit it (e.g. convert ascii strings to something numeric using search/replace). Or when I want to do a quick interactive pivot table. Or a quick sum or count. Or when I want to try out one or two formulas or expressions before I start coding them. O r when I just need a small table to look good for insertion into a document (the best Latex table editors that I know are plug-ins for Excel or Calc: format in spreadsheet, push button, copy-paste Latex code; works every time).
Of course it's possible to do most of those things in R too, if your time time has no value and if you love writing one-off code. I prefer to select the best tool for the job, and use that. Even if that sometimes means using VBA.
Interestingly I find myself using RExcel (integrating R and Excel) sometimes.
Most of the time however I have no time for zealots who tell me that I don't need X,Y, or Z because I supposedly can make do with A,B, or C too. They can e entertaining though, as long as you recognise them for what they are: rants from zealots.