I am working on Mesh Spreadsheet. It gives you a spreadsheet UI that writes code as text. It will likely use k9 as the formula language once it matures, but:
- you can try the JavaScript prototype at this link: http://mesh-spreadsheet.com
- you can see a proof of concept video using k here: https://www.youtube.com/watch?v=CEG9pFNYBCI
One reason I prefer Apple Numbers to Excel is that in Numbers you arrange tables on a canvas. The tables can refer to each other. I think it makes it easier to work with, for example, an input table and an output table because they are separate entities and not just different ranges on the same grid. It’s similar to how some websites enable you to configure a dashboard view of multiple tables and charts.
Your presentation reminded me of that, specifically the view of how you implemented the tool in itself. But you’re using one large table rather than a collection of tables on a canvas.
I’m wondering if the Numbers approach might work better. In particular it might be more natural for dynamic arrays because they would not “overlay” a range of cells. They would be their own dynamically resizing table on the canvas. This might lead to something like Spreadsheet 2000: https://en.m.wikipedia.org/wiki/Spreadsheet_2000
It might impact the 'flow' of modelling eg using automatic cell names like 'A1'. And it might complicate storing the sheet as text (might need to store more complex layout info than 'table is at coordinates x,y').
But it could also make layout easier, eg tables underneath each other could have different column widths. Could use CSS flow layout.
I also hadn't heard of Spreadsheet 2000 - great reference, thanks.
BTW - I think of the Mesh spreadsheet as a canvas, just a 'discrete' one as opposed to continuous. In theory, if the grain is fine enough (eg 1 character width of a monospace font), then you might be able to get the best of both discrete and continuous approaches.
=‘Item Price’ * ‘Num Sold’
Instead of =B1C1, =B2C2, etc. You’re doing something similar with the name cell, but with separate tables you can designate column headers and row headers, and use them to reference cells. For example, in another table you might say: =SUM(‘Sales :: Total’) to sum up column “Total” in table “Sales”.
(I might be getting the syntax wrong here, you don’t type this in, you “option-click” on the cell you want to reference and it inserts the reference.)
This is optional, you can still do A1, B4, etc and sometimes that’s what you get when you use the same name for two columns in order to disambiguate. But in general it makes it easier validate your spreadsheet, I think.
I believe the approach to naming goes back to Lotus Improv: https://en.m.wikipedia.org/wiki/Lotus_Improv
I think Quantrix Modeler is also based on the ideas of Improv.
Come to think of it, there was Javelin in the 1980, that you might find interesting. It’s not a spreadsheet but it was used for financial modeling and time series: https://en.m.wikipedia.org/wiki/Javelin_Software
But those are quite far departures and might not be where you want to go. I just wanted to point them out in case you hadn’t heard of them. (I’ve only heard of them but I have never used them, so I cannot say how good or bad they worked in daily life.)
You can do that in Excel too: whether it's the whole column of a sheet, or a column of a table - syntax for latter is `someTable[someColumnName]`. Mesh will have the latter, albeit with different syntax.
Thank you for the links! I will have a look. There's a deep history.
Your talk was interesting, thanks.