3

I use excel. I use google sheets. I've used Opencalc. And to really date me, I've used Visicalc.

I've come to the conclusion that spreadsheets are in essence the 'Basic' of tabular data.

I am looking for an alternative:

  • Documentable. You can sprinkle notes here and there, but if you are presented with a spreadsheet you've never seen before (or one you worked on a year ago) trying to figure out what you did is daunting.

  • Resuseable. When you do something clever, I want to be able to drag that somewhere else like I can a Perl module.

  • Clearer syntax for formulas. I don't like writing mini programs as one long line of nested functions. Nor do I like to have N columns of hidden intermediate results.

  • A better set of functions.

Features I like about excel:

  • Lookups to other files.
  • Conditional formatting
  • Quick formulas at either end of a block to summarize the column or row

Features I don't like:

  • Managing large numbers of named ranges is clunky.
  • Formula editing sucks golf balls through a soda straw.
  • Formula structure with multiply nested formulas is difficult to read.

I do like a spreadsheet for a quick and dirty lists. But I keep feeling like I'm trying use a screwdriver when I need a crescent wrench.

I've also used databases. dBase II, MS access (sort of a database), Filemaker. These are clunky to work with: You have to define each table. Doing a sum or a standard deviation of a column requires some extra hoops to jump through.

Apple's Numbers has some good touches: Multiple tables on a sheet. It also has some real weaknesses -- Inability to do lookups in separate files, limited conditional formatting.

I think I'm looking for somethat between a spreadsheet and a database. I want to do lookups. I want different views. I want a usable set of default views.

Excel with Matlab syntax maybe?

Small grids of cells connected to other grids of cells with formulas?

Dijkstra wrote "GoTo statement Considered Harmful" and nudged us away from Fortran and Basic.

I'm convinced that a using a spreadsheet for doing anything that takes over a day to write is much like trying to use MS Word to create a long technical document. Word is fine for a 3 page memo. More than that, I want something like Framemaker.

I'm looking for something that does to spreadsheets what Pascal is to Basic

iPython, mentioned below, looks possible.

pySpread is another one to check out. And it got me thinking:

In web design we go to some length to pull apart the content from the markup. In my own site I use a combination of perl scripts, templates, and markdown so that the markup doesn't get in the way of writing content.

In programing we have data structures: Arrays, hashes, lists, structs etc.

The latest versions of excel have the start of structured data with their idea of table. This at least allows you edit a formula once in a column, and have it apply to the column.

I think in spreadsheet-like app, I want a way to separate formulas from data, and a way to have structured data.

4
  • How about LibreOffice calc?
    – user3169
    Mar 14, 2015 at 5:02
  • 1
    @user3169 OP stated already having used that: "I've used Opencalc" (OpenCalc and LibreCalc are basically the same application, as LO is a fork of OO).
    – Izzy
    Mar 14, 2015 at 12:27
  • @Izzy True, but I think LO has been doing a better job of program improvements in the last couple of years.
    – user3169
    Mar 14, 2015 at 17:02
  • @user3169 That might be true – but does it meet the requirements specified?
    – Izzy
    Mar 14, 2015 at 17:09

2 Answers 2

3

I would suggest taking a look at the python language and some of its user interfaces such as ipython & Jupyter specifically with notebooks & Pandas

  • Full power and flexibility of python available.
  • Allows a mix of data, calculations, formatted notes in Markdown & HTML, pictures, much more. So definitely Documentable
  • You can construct and reuse building blocks and python modules so reuse is high.
  • Very version control friendly
  • Functions have to be written as clear multi-line, indented, code - you'd have to work hard to do the everything on one line.
  • You had better believe that you have a better set of functions, including being able to use all of the numpy, scipy, matplotlib, pandas libraries, etc.
  • Free both gratis and Open Source.
  • You can share your final results as HTML, PDF or via the Notebook Viewer.
  • There are a lot of Machine Learning tools available for &/or written in Python.
  • Tools for handling big data.
  • Can harvest data from the web
  • Toolbox is huge - more like swapping your screwdriver for a completely fitted out toolman station with a hidden door into a complete warehouse of tools.
  • With pandas or with several other libraries you can store your data &/or output your results in a number of formats including Excel spreadsheets if you really have to.
  • You can even embed videos in the page.
  • Write your code once then run it anywhere - Python is available for Windows, Mac, Linux, Solaris, etc. and will run on hardware from RaspberryPi right through to Super Computing Clusters and AWS or other cloud platforms.
  • Arbitrary Precision Calculations with mpmath and also the fractions library.
  • The library/tool installer pip is like magic - it automatically downloads & installs the dependencies for you.
  • Lots of online help

Python is also quick to download and install - the core python 3.10 or 3.11 Windows installer is under 30 MB and a download of most of the tools mentioned above tested on a Windows 11 64 bit machine using pip download pandas ipython notebook jupyter jupyterlab note that normally you would use pip install to actually download and install the packages and dependencies actually downloaded a total of 85 packages (including numpy) so as to include the dependencies of the 5 requested. This download totalled about 64 MB.

Page from website

1
  • 1
    You get a +1 just for such a great answer. I'm downloading python so I can then get.... There goes the evening. Mar 15, 2015 at 4:28
2

A second python based option if you like the spreadsheet look is PySpread - it looks like a traditional spreadsheet but each "cell" can contain anything up to a complete python program, it is Free & GPL3 but you do have to meet the dependencies and it will run under Windows, OS-X & Linux at least.

Main Features

  • CSV import and export
  • Chart creation dialog based on matplotlib
  • Python objects as cell results
  • Python macros
  • GPG based save file signatures for preventing foreign code execution
  • Access to all Python modules from within cells. These modules allow for example matrix operations via numpy or fixed point decimal numbers for business calculations via decimal.

Sinus Basemap example

1
  • Another one to check out. And it sparks another idea for refining the question. +1 for you, good Sir. Mar 16, 2015 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.