Slashdot Mirror
Ask Slashdot: Spreadsheet With Decent Programming Language?
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?"
As old as the hills: http://siag.nu/siag/
Not a very elegant language, but way better than any spreadsheet that I know of.
www.r-project.org/
It's not exactly a spreadsheet, but Pandas is totally awesome and is useful for many tasks for which you might think of using a spreadsheet.
http://pandas.pydata.org/index.html
IPython Notebook is sort of like a combination of the normal ipython shell and an IDE. You interact via your browser but it connects to a normal python process on your local (or remote?) system.
http://ipython.org/ipython-doc/dev/interactive/htmlnotebook.html
I've used these tools together for many tasks for which I might otherwise have used a spreadsheet, particularly for "pivot tables" and time series analysis. Again, even combined they do not a spreadsheet make, but they are in many ways superior. They can handle very large data sets, and best of all you are doing it all in Python.
-73, de n1ywb
www.n1ywb.com
Instead of a spreadsheet with good programming just program and output a spreadsheet. CPAN has plenty of packages for this.
To my knowledge, C# can be used to write plugins for Excel, which should be able to handle the more complex macros.
Self proclaimed typo king, and inventor of the bear destroying coffee table (patent not pending).
https://datanitro.com/index.html
I've used it a bit and it's pretty fantastic
Don't assume that a "low" version number means it's unusable. The project has commits going back to at least December 2009. Not all software is versioned with the assumption that 1.0 = finished.
If you want a great spreadsheet: http://www.quantrix.com/
If you want to beef up the programming language but are fine with Excel: http://www.wolfram.com/products/applications/excel_link/
If you are talking non commercial: Siag (suggested above) is cool: http://siag.nu/index.shtml
This hasn't seen much activity in a decade but Haxcel: http://www.johanmalmstrom.se/haxcel/ is Haskell in a spreadsheet.
R is very powerful try this" http://www.omegahat.org/RGnumeric/
Yes, both outside COM automation and vSTO plugins that run in Excel http://msdn.microsoft.com/en-us/office/hh128771.aspx
What is the definition of a decent programming language?
It's a pretty ambiguous requirement.
C? Java? Python? Perl? Javascript?
Each is 'decent' in it's own way.
Another way to ask is this: What do you feel the shortcomings of the Excel VB language variant are?
Huh?
Both LibreOffice and OpenOffice.org support macros in Python or Javascript. Chances are you already know one of those, so you don't even need to learn a new language.
Beta is broken and the link to classic doesn't work. Stop wasting our time or there won't be anybody left here.
Spreadsheets are actually terrible tools for data analysis. It's virtually impossible to document what you did with a spreadsheet, and make it reproducible and debuggable.
What you want is R, the Free software language based on Bell Labs "S" programming language for doing statistics and data analysis. R is like the fully outfitted machine shop compared to a spreadsheet's screwdriver and a hammer in a plastic box.
http://www.r-project.org
Wow... quite possibly one option that would be WORSE than VBA.
Self proclaimed typo king, and inventor of the bear destroying coffee table (patent not pending).
What are you doing with a spreadsheet that you find the built-in functionality so limiting?
It's possible that perhaps you're getting to the "hairy edge" of what a spreadsheet is capable of. Depending on your application, perhaps you need a more specialized -- or more general purpose -- tool, here.
Others have suggested MATLAB. If not that, how about Mathematica?
Maybe you've outgrown the scope of a spreadsheet and need a general purpose programming language, perhaps one that you can get a reporting package that suits your requirements. If you're using lots of VBA, why not go all out and use VB, or any other general purpose solution (C, Java, Python, etc., etc., etc.)?
Are you doing signal processing or control or other engineering stuff? Perhaps DaDiSP.
Some more info on your particular needs might get a more specific and useful answer from someone here that's done the same thing.
I am not a crackpot.
Stop programming in your fucking spread sheet. It's not an application development system.
If you start having more code than you have data in there, you're doing it wrong.
On the other hand, I got paid a pretty penny to turn a spread sheet system into a real application not so long ago,
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.)
What, exactly is wrong with =AVERAGE()?
Excel can already use VBA, which in turn can use IronPython.
Done.
Awesome; but not quite done. At that point you can run an X86 emulator inside it and boot Linux. Then you can run Firefox inside it and finally, you will have access to a sensible language.
Actually, this is one of the best Ask Slashdots ever. A language war enclosed inside a user interface design war enclosed inside a programmer pet hate.
=~ s,(.*),<sarcasm>$1</sarcasm>,g if any_point_you_wish();
"How shall I make it hard for people to use Excel for just about anything."
I thought Microsoft did a pretty good job of that already.
Seriously... when Windows was still relatively new, 1-2-3 for Windows was pretty good, and there were some other very good spreadsheet programs available (sadly, absent from Wikipedia's spreadsheet history page). Then people started to actually use Microsoft Excel, which they greatly improved and stuck in their Office suite. But it wasn't that their product was better; they just had the OS and "Office" advantage, which pretty much guaranteed them market share.
Excel was already a robust and popular application on Macs. And the original 123 for Windows was a thin wrapper around the DOS app and not considered very good. The fact that "Office" was half the price of Lotus+WordPerfect didn't hurt either.
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.
Give me Classic Slashdot or give me death!
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.
I think OpenOffice/LibreOffice can be interfaced with a number of programming languages
It can. And even more APIs. Almost all of which are cryptic, cumbersome and/or poorly documented.
It can be worth it once you learn how ... assuming you have enough sanity left.
What, exactly is wrong with =AVERAGE()?
It's not too bad, but it's not too good either.
Excel is the Crescent Wrench of office software.
The wrong tool for (Almost) every job.
Why is it so hard to only have politicians for a few years, then have them go away?
And you can do it all in Emacs.
Rule of Slashdot #0: You and people like you are not representative of the larger population. - A.C.
No, Excel won because it was very much better .. at what the majority of users use spreadsheets for: keeping simple lists. Microsoft realized that early on and optimized for it. Excel is also terriffic these days as a simple graph-paper-oriented drawing program: make the cells square and you can outline and color quite easily. It also does a respectable job at turning a imple set of data into a pretty infographic-style graph.
Excels behavior as a tool for complex financial calculations is simple irrelevent for 99% of its users. It won because it was optimized for doing simple, visual stuff.
Socialism: a lie told by totalitarians and believed by fools.
If you can keep it to rslt = function(cell1, cell2, cell3) then it's OK, but in practice it seems to involve rslt = use.of.some.object.you.didnt.expect where goat.sacrifice(was.successful) [but.I.lied.to.you]
Protoplasm. Quiet Protoplasm. I like quiet protoplasm.
What, exactly is wrong with =AVERAGE()?
It's not too bad, but it's not too good either.
Try writing a replacement sometime from scratch, and see how hard it is.
It intelligently only averages cells that are filled with numeric values, allows easy input of multiple ranges of cells, allows direct input of numbers as function parameters, and has an easy to remember name.
If the built-in functions (which include some serious statistics and analysis functions) don't do the job, there are third-party add-ons that likely do. If you absolutely need something unique, then VBA is quite easy to use. The only real thing I don't like about Excel is the "error in a cell is propagated to all cells that reference it", with no way to disable it, and no formatting codes that hide errors. For example, there are a lot of times when I end up with divide by zero because a cell isn't filled in yet, but that's OK (like a table that calculates price/quantity, when a row hasn't been entered yet), and the only way around it is to use the "=IF(ISERROR(...))" construct. It would be much nicer if the existing "positive;negative;zero;text" custom formatting added ";error" to the end.
"No, Excel won because it was very much better"
..
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 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."
"I'd be glad to help tilt lotus into into the death spiral"
What are you on about - JS is quite a neat little language for solving small problems, and you can do functional programming in it if that's your thing. There's a reason MIT etc are turning from Scheme (which I love) to JS as a teaching lanuage.
The only part of JavaScript that really sucks is the first four letters, but don't be put off by that.
Socialism: a lie told by totalitarians and believed by fools.
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.
And you can do it all in Emacs.
Actually ... yes. Emacs 'calc' is very powerful and of course you can extend it any way you want in elisp. There are also a couple of spreadsheet modes; SES isn't bad for some purposes, and there is a sort of spreadsheet built in to org-mode tables.
I am only partly serious here; while I use Emacs spreadsheet modes for simple stuff (tracking and doing stats on exercise, for instance) it's certainly not meant for The Big Stuff. But Emacs calc---- that can do some pretty impressive work.
=Woosh() FTFY
Similar concept, but the other end of the technological timeline is the ExtJs grid control (comes with some excellent docco)
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!), 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.
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.
WordPerfect was great for DOS. The early Windows/WYSIWYG versions were not great. I remember going back to the DOS version for most things because the GUI was too clunky. It just didn't behave like a proper Windows application. By the time it worked well, it was too late. MS word had picked up marketshare for people wanting to use a Windows word processor.
I do remember finding it maddening when MS-Word would decide to do something unintended with styling, and I had no true "Reveal codes" function to fix it easily like I could in WordPerfect.
As a visual thinking I have found the most important tool for programming (which is just another type of solving interesting problems) is a pencil and pad.
While you have an catchy cliche & interesting point I use Excel / OOCalc as a cheap digital notepad which I find quite effective. I can jot partial formulas down, do quick graphs, and have some semi-table-structure while I finalize organization and equations before throwing it into the "real" tool.
There are times a "lab notebook" (whether physical / digital) IS the right tool -- when you can minimize the time spent wasting with the UI then you can focus on actually solving the problem. :-) THAT is one of the advantages of using a spreadsheet that too many completely overlook or dismiss as not being relevant.
Are you perhaps thinking of this Joel on Software article "How Trello is different", in which Joel writes:
Your source could have just been another writeup of the same Microsoft investigation, of course.
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.