Follow Slashdot blog updates by subscribing to our blog RSS feed

 



Forgot your password?
typodupeerror
Programming Software

Ask Slashdot: Spreadsheet With Decent Programming Language? 332

Posted by timothy
from the index-cards-and-a-hole-punch dept.
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?"
This discussion has been archived. No new comments can be posted.

Ask Slashdot: Spreadsheet With Decent Programming Language?

Comments Filter:
  • SIAG (Score:5, Informative)

    by lisaparratt (752068) on Thursday February 14, 2013 @02:05PM (#42898977)

    As old as the hills: http://siag.nu/siag/ [siag.nu]

    • 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.

  • MATLAB? (Score:5, Informative)

    by Anonymous Coward on Thursday February 14, 2013 @02:09PM (#42899031)

    Not a very elegant language, but way better than any spreadsheet that I know of.

    • Re:MATLAB? (Score:4, Insightful)

      by Idbar (1034346) on Thursday February 14, 2013 @02:28PM (#42899443)
      Looks like I lost my mod points, but trying to make mathematical calculations and operations with a spreadsheet instead of a matrix oriented language seems like the failure on part of the submitter. Matlab is (and I assume the free Octave must be) great for data analysis and even plotting is a breeze.
      • Re: (Score:2, Informative)

        by Anonymous Coward

        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.

    • by arogier (1250960)
      That's what I'd suggest MATLAB or Sage. Just because spreadsheets handle (or try to handle) numbers means they are the best tools for it.
  • by Anonymous Coward on Thursday February 14, 2013 @02:11PM (#42899099)

    www.r-project.org/

    • www.r-project.org/

      With [insert programming language of your choice here] you don't need spread sheets! What kind of programmer worth a thimble full of goat piss uses Excel to do much of anything important anyway?!?! I'm not a programmer and would use Perl, Python or [gasp] PHP to do anything I want with tabular, comma separated values. It just isn't that hard. Excel was made for people who can't program. If you can write code you don't NEED Excel. [shakes head in disgust]

    • by golodh (893453) on Friday February 15, 2013 @03:54AM (#42907681)
      An enthusiastic and experienced R user myself, I use a speadsheet (like Excel or Calc) to do what R simply oesn't do as well or as easily or as quickly.

      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.

  • by n1ywb (555767) on Thursday February 14, 2013 @02:12PM (#42899107) Homepage Journal

    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.

    pandas is a Python package providing fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language. It is already well on its way toward this goal.

    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.

    • by n1ywb (555767)

      I forgot to mention that there is some degree of integration between pandas and ipython notebook so it makes sense to use them together for interactive use.

      Also before anybody digs on Python's performance, Pandas is built on NumPy which is written in C so it's relatively fast and memory efficient even on large data sets. NumPy is even working on automatic vectorization and paralelization for those really tough problems.

    • Just use R (Score:4, Interesting)

      by Hatta (162192) on Thursday February 14, 2013 @03:11PM (#42900167) Journal

      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.

  • by h4rr4r (612664) on Thursday February 14, 2013 @02:12PM (#42899113)

    Instead of a spreadsheet with good programming just program and output a spreadsheet. CPAN has plenty of packages for this.

    • by CastrTroy (595695) on Thursday February 14, 2013 @02:21PM (#42899295) Homepage
      This. Pick any programming language you want, store the data in a real database, and write some code to output the results to CSV so it can be imported into Excel or any other spreadsheet.
      • by h4rr4r (612664)

        You can even output to XLSX so that you can do multiple tabs and such.

        Still using a normal language and a real DB is much better.

      • by alexander_686 (957440) on Thursday February 14, 2013 @02:31PM (#42899503)

        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.

        • by h4rr4r (612664)

          No, they are not the best option. It might seem like it now, but I have been down the road, soon there will be much pain.

          At the very least get them using a real DB and not VBA. Trust me you will be far better served in the long run.

          • by hazem (472289) on Thursday February 14, 2013 @03:28PM (#42900445) Journal

            At the very least get them using a real DB and not VBA. Trust me you will be far better served in the long run.

            The problem is in most organizations, IT is not responsive to requests to set up these kinds of databases. They especially don't deal with requests that are not precise and require a fast turn-around. Thus you end up with analysts and accountants using Excel and sometimes VBA because they can get the job done quickly and effectively.

            And frankly, I don't want my accountants and analysts wasting their time creating databases and learning to write database applications. Their job is to be very good at accounting and analysis, not administering databases.

            • by h4rr4r (612664)

              Then fix your organization not use a stupid work around. Assuming we can get a license for it we turn these things around in my department in under 48 hours when you don't need new hardware. Since this DB could surely live on an already setup box it would likely be less. I say that because if it were more important or demanding you would not even be considering the method we are talking about.

              Your accountants will be doing far more time wasting when it stops working or they get into anything complicated. It

            • Re: (Score:3, Insightful)

              by DragonWriter (970822)

              The problem is in most organizations, IT is not responsive to requests to set up these kinds of databases.

              Using Excel for tasks to which databases are better suited because you have an unresponsive IT organization results in an additional problem on top of the one you started with, rather than dealing with the original problem.

              They especially don't deal with requests that are not precise and require a fast turn-around.

              What you probably need in that case is a small number of technical staff "embedded" wi

              • by hazem (472289)

                I agree with you completely. And if I had control or even influence to get that kind of headcount, I think it would be a fantastic "force-multiplier" in our organization. I dream of the idea of very small teams of IT implementers that could spend a little time with various departments and help streamline their data collection and reporting using standardized tools.

                Sadly, in my large company, our IT organization just isn't willing to "invest in throw-away" tools. They only want to do huge capital projects

      • by slartibartfastatp (613727) on Thursday February 14, 2013 @02:36PM (#42899601) Journal

        (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.

        • by jpate (1356395)

          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.

          Here is your UUOC [partmaps.org] award :D

  • by Anonymous Coward on Thursday February 14, 2013 @02:15PM (#42899161)

    https://datanitro.com/index.html

    I've used it a bit and it's pretty fantastic

  • by unts (754160) on Thursday February 14, 2013 @02:15PM (#42899163) Homepage Journal

    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.

    • by Myopic (18616) * on Thursday February 14, 2013 @02:26PM (#42899375)

      "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.)

      • by Jane Q. Public (1010737) on Thursday February 14, 2013 @02:55PM (#42899903)

        "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.

        • by PRMan (959735)
          Firefox is copying Chrome, because the marketers don't want them to get too far behind. It's just like "this one is better, it goes to 11, it's one louder".
        • by Myopic (18616) *

          If few people follow a standard, is it a standard? Cue the tree in the woods.

          You make a good point. I think we are on the same team. I'm just putting in a good word for our team so that maybe others will join it. I think ours is a team of reasonable people with a moderate and helpful suggestion to improve the world in a small way. I'm always surprised that even this suggestion divides people.

      • by medcalf (68293)
        Oh, very debatable. Fundamentally, the purpose of a version number is to differentiate between different versions of the software, nothing more. If you want to use version numbers to track release date, why not use a version number plus a release date? If you want to use version numbers to differentiate between stable, development and experimental versions, why not use a version number plus a stability indicator? The basic idea is that it's not particularly useful to use one piece of metadata to encode othe
      • Heck we have windows version 8.0 that's clearly an early pre-alpha build.

  • Depends (Score:5, Informative)

    by jbolden (176878) on Thursday February 14, 2013 @02:16PM (#42899181) Homepage

    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.

    • by 9jack9 (607686)

      If you want a great spreadsheet: http://www.quantrix.com/ [quantrix.com]

      Whoa, pricey. They could probably rule the world if they released an ultra-simple free version and a somewhat more featured cheap version.

  • R is very powerful try this" http://www.omegahat.org/RGnumeric/ [omegahat.org]
  • Definition (Score:5, Insightful)

    by wideBlueSkies (618979) * on Thursday February 14, 2013 @02:20PM (#42899265) Journal

    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?

    • Re: (Score:2, Interesting)

      by Anonymous Coward

      A "decent" language is one which does not use any of the following keywords:

      Then
      Dim
      Sub
      End

      • by Nutria (679911) on Thursday February 14, 2013 @04:02PM (#42901021)

        bash uses "then" and it's a great language.

      • by cbhacking (979169)

        If you're going to complain about a programming language on the basis of its 3-4 letter syntactic sugar, you can go right ahead, but don't expect to be taken seriously. Yeah, it's a BASIC dialect, so fucking what? Yes, I know the Djikstra quote; it's quite demonstrably wrong and, frankly, stupid. A lot of excellent programmers today and in the past learned on BASIC. Let's see...

        Then / End: semantically identical to { }, but a bit less overloaded than those characters are *and* easier to understand when lear

  • by SgtChaireBourne (457691) on Thursday February 14, 2013 @02:21PM (#42899287) Homepage
    Both LibreOffice and OpenOffice.org support macros in Python [openoffice.org] or Javascript [webodf.org]. Chances are you already know one of those, so you don't even need to learn a new language.
  • Just use R (Score:2, Informative)

    by Anonymous Coward

    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

    • by csirac (574795)
      +1, although as a big ruby fan and using perl at work for nearly three years (and matlab at uni for four) I much prefer (and use) python+pandas instead.
    • by belmolis (702863)
      Agreed. The only downside to using R for this purpose is that it doesn't give you the constant display of your data that a spreadsheet does. I mostly don't care, and in any case, if you have a lot of data, you can't see it much of it in any given view of spreadsheet anyhow, but some people really do want the spreadsheet display.
  • by Rob the Bold (788862) on Thursday February 14, 2013 @02:27PM (#42899415)

    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.

    • by Obfuscant (592200) on Thursday February 14, 2013 @02:41PM (#42899677)

      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.

      • 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.

        He didn't say he was an orthopedic surgeon. That does change things . . .

    • by Cassini2 (956052) on Thursday February 14, 2013 @03:12PM (#42900187)

      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 find that a decent SQL database platform is better for complex data manipulation. Install SQL Server 2012 Express Edition With Advanced Services (it's a mouthful, but it's free). It supports import/export from Excel spreadsheets, and a number of other data formats, and also includes Reporting Services for creating nice presentable reports without coupling the layout with your data storage (as with a spreadsheet).

    Some SQL knowledge can take you a lot further than Excel will on its own.

  • it might not meet your needs because its spread sheet function came as an afterthought/alternative to an interactive geometric algebra tool. It has great power for some visualizations but not much for general data sets. IT is all open source and all in Java.
    the link: http://www.geogebra.org/cms/ [geogebra.org]
  • by viperidaenz (2515578) on Thursday February 14, 2013 @02:31PM (#42899507)

    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,

    • by MouseR (3264) on Thursday February 14, 2013 @02:50PM (#42899811) Homepage

      Turning a spreadsheet into an application is not programming. It's being an accountant.

      • Hi, Accountant here.

        I use spreadsheets a bit for data manipulation but most of the big stuff I do inside our data warehouse using SQL. It is very rare that I need to venture outside of pivottables, vlookups, sumifs, countifs, or if statements inside of a spreadsheet.

        If you are having to make extensive use of programming skills in your spreadsheet I think you have a hammer and a nail problem (every problem looks like a nail cause all you have is a hammer).

  • Don't know if it would work for what you're wanting. Just throwing it out there.

  • But it's not quite a spreadsheet application.. I found it quite powerful last I tried it ~18 months ago, but I had trouble fitting the entire dataset in memory openrefine.org [openrefine.org]
  • It is free and it works better than Matlab.
  • Without knowing what you're actually doing it's kind of hard to make any recommendation.

    Where I work, we find Tableau [tableausoftware.com] to be a good middle-ground between Excel and full SQL environments. It's not really a spreadsheet perse though.

  • Of course 'it depends on the job', but I'd suggest having a look at SPSS, if you have access or are rich. Every college and university uses it.

    en.wikipedia.org/wiki/SPSS

    It has a code view for your queries (i am weird and only use SQL) and even some online 'libraries' of often used search strings (consult google). There used to be a FOSS alternative, PSPP, but I never used it.

    I'd like to hear any feedback on my suggestion. The type of data analysis I did was either large data pulls based and de-duping on a f

    • There is an open source version of SPSS called PSPP. It is not nearly robust enough to replace SPSS in most cases where buying SPSS makes sense, but for small projects it works fine.
  • by snadrus (930168)

    Be a programmer, not a Sheet enthusiast. I needed UI but simpler processing, so the ~LAMP stack worked for me (Linux, Cherokee, MariaDB, Python) . Python fits everywhere here from heavy data analysis to being web front-end. Often a one-line SQL statement did more than I needed.

  • Using a spreadsheet and not a real program. There are countless frameworks and libraries for data analysis found in any real programming language if you accept the idea of writing a real program, opposed to trying to shoehorn a real language into a spreadsheet app. Remember, spreadsheets are designed for managers, and we all know how stupid managers are right?

    Also the idea of looking for some obscure spreadsheet alternative just because you don't like the syntax is another fail. Not sure what you are doi

  • OpenSource: QTiplot http://soft.proindependent.com/qtiplot.html [proindependent.com] uses Python as its scriptiing language

    Prop: OriginPro http://www.originlab.com/ [originlab.com] - can use C , LabsTalk and has its own C-based X-functions.

    Admittedly, they are geared towards scientific data analysis, but have powerful graphing and programming capabilities.

  • I have always thought a functional language like ML or Haskell would work well in a spreadsheet.

    The compact functional code would visually fit well in a cell expression. And functional concepts like map and foldr would fit would work great for aggregating columns.

    Also, it is insane that Excel does not have regular expression functions. Regular expression search/replace would work sooo well in a spreadsheet.

  • A spreadsheet is something a user uses....

Dead? No excuse for laying off work.

Working...