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?"
Comment removed (Score:5, Informative)
MATLAB? (Score:5, Informative)
Not a very elegant language, but way better than any spreadsheet that I know of.
plugins (Score:0, Informative)
There are plugins for Excel that let you use .net with it - so C#/VB.net or even C++/CLI.
with R, you don't need spreadsheets (Score:4, Informative)
www.r-project.org/
Pandas + IPython Notebook (Score:5, Informative)
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 [pydata.org]
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 [ipython.org]
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.
Re:COM Automation (Score:4, Informative)
To my knowledge, C# can be used to write plugins for Excel, which should be able to handle the more complex macros.
python embedded in excel (Score:5, Informative)
https://datanitro.com/index.html
I've used it a bit and it's pretty fantastic
Depends (Score:5, Informative)
If you want a great spreadsheet: http://www.quantrix.com/ [quantrix.com]
If you want to beef up the programming language but are fine with Excel: http://www.wolfram.com/products/applications/excel_link/ [wolfram.com]
If you are talking non commercial: Siag (suggested above) is cool: http://siag.nu/index.shtml [siag.nu]
This hasn't seen much activity in a decade but Haxcel: http://www.johanmalmstrom.se/haxcel/ [johanmalmstrom.se] is Haskell in a spreadsheet.
Re:COM Automation (Score:3, Informative)
Yes, both outside COM automation and vSTO plugins that run in Excel http://msdn.microsoft.com/en-us/office/hh128771.aspx
Python or Javascript in LibreOffice and OOo (Score:5, Informative)
Just use R (Score:2, Informative)
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
Re:MS Offfice 2013 - Javascript apps (Score:5, Informative)
Wow... quite possibly one option that would be WORSE than VBA.
Re:Just do it the other way around (Score:4, Informative)
I would temper that enthusiasm. I work with accountants, Excel, and VBA.
Spreadsheets offer a familiar and flexible front end for non-tech users. We have spreadsheets where sections are ridge to handle the VBA but exposes data to the end user. It is easy to enter paramaters and see the updates quickily - much harder to do when you have to import a new CSV each time. The accountants then can easily customize the data for their needs – and it still prints out nicely on the printer.
So, while I have issues with Excel / VBA / Spreadsheets in general – sometimes it is the best option.
Re:Version numbers... (Score:4, Informative)
"Let's get together on this, open source nerds."
There is already something of an industry-wide standard for version numbers, and it fits with your definition pretty closely. It's just that many don't follow it. Including outfits like Mozilla, in recent years.
The standard calls for major and minor version numbers, followed (optionally) by a build or release number. E.g., 2.3.456.
Version 1.0 is supposed to be the initial, stable, core release, just as you say. But some groups (like Mozilla) insist on jumping the major version for relatively minor reasons, and others seem to get stuck at version 0.5 forever. But that's not because they can't agree on a standard. It's because they just don't follow it.
Try This (Score:1, Informative)
Re:libre office (Score:4, Informative)
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.
Re:MATLAB? (Score:2, Informative)
I agree the submitter is asking the wrong question, and really wants something like matlab instead.
> (and I assume the free Octave must be)
it is. you don't get all the fancy handle graphics ui stuff, but that's no problem and regular plotting works fine. there's ipython notepad + matplotlib for that sort of thing anyway.
see also scilab.
Re:MS Offfice 2013 - Javascript apps (Score:5, Informative)
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.
Re:Python or Javascript in LibreOffice and OOo (Score:4, Informative)
And the docs for Python in Open Office are...
Oh right - there aren't any docs.