That Subtext screencast is fascinating - thanks for posting the link. Reminds me of the Bret Victor talks.
I agree with your other points - this is something I'd love to build (or see built). But do you think it could displace Excel?
I agree with your other points - this is something I'd love to build (or see built). But do you think it could displace Excel?
But what I'd like to see is a generalization beyond columns/rows/sheets for relating data of various dimensions. The underlying data type would probably be a list (so lisp) but have options to enable order, array access, key->value access, etc. Cell references would work more like in programming languages and operate over ranges that aren't just two dimensional.
So a spreadsheet would be a subset of this. So a set of columns with array access, a set of rows with array access, a notation like column-row for cell references, and all of the operators like $ for absolute references, etc.
I guess the underlying data structure could be a relational database but I'd prefer something human-readable like JSON with a thin notation over it for expressing relationships between structure members. You could use something like the Subtext editor to work on it but maybe if the syntax was easy enough you could write it manually.
I think the real power of this would be if relationships could be executables (like how Excel macros work but with stdin/stdout/stderr and black box scripts written in any language). Imagine being able to call sed/awk or really high level tools like Go/Erlang and Matlab/Octave per-cell the way we use SUM in Excel..
For example, say I want to make a wiki page to track some kind of code migration project. Currently I might grep for usages of a term, and project out the (module name, term) pairs. I then run this query a few times for different terms, and use Excel to merge and pivot the data so the first column is a module name and the remaining columns mark occurrences of each term. I then copy paste into Vim and use a regex to mangle the data into wiki markup.
There is surely a better way of doing this, using a single tool to glue the steps together so that the pipeline is repeatable and the various steps are individually reusable.
Right now companies don't send their employees on VBA courses even though they could work some much more efficiently with a basic knowledge of coding.
For example, an asset management database. If I can use this to generate different free form queries, it would be something I would totally throw money at.
I translated excel into a javascript data structure, translating the formulas into javascript functions. The data structure could be saved to json, and them reloaded back into javascript. The cool thing is that changing a value (a2 = 3) automatically updates dependent values in the rest of the data structure. The data structure can be referenced much like the spreadsheet - it is a tree with branches for pages, rows, columns. There is a simple faster csv like API that makes it easy to go page by page, row by row. Also, cells can be looked up explicitly.
The code is proprietary, which is a shame. It was written as a library to quickly reverse engineer large, complex xlsx files. The trick in making it work is just having the right data structure.
Sorry if I am rambling or any misspellings. I am very sick right now, drowsy from medication, and trying to type thus on my phone.