Calculation language

[burgessg]

Hi there, I've just installed TS and walked through the tutorial. It
looks pretty nice, but the 'Spreadsheet' side of it obviously needs
some work, in the sense of being able to calculate anything.

Some notes about usability. I was just using Excel last week and was
struck by how little it has improved over the years, in terms of the
basic ability to write formulas and calculate results based on values
elsewhere in the spreadsheet. Its almost like Assembly languages, in
some respects. You can name a cell to use in a formula, but the name
on that cell is hidden unless you select the cell and direct your eyes
to the right part of the window. You can also name a discrete range of
cells, but you can't name a column. So you end up with horribly
unreadable formulas like =(D12-E12)*(B12-A13)/2

What would be so much nicer is if you could name an entire column.

Your application, with its built in sense of nestedness, would be well
suited to an Object-Oriented approach, at least to reference data. For
instance, suppose I have a table of data I want to do something with
(like create another column containing the sum), we could put this
table under a parent tag, Prices, say, which we could then access
using array indices Prices[0,1], or if we used the first row as
labels, using 'dot' notation: Prices.open.

The key point I'm trying to make about usability is that it should be
easy to write a worksheet that is easy to understand (so that
variables are named, and the names are visible).

I would think hard about whether you really want to define your own
language. It might be better to adapt something that already exists.
You might want to look at a functional language such as Matlab (I
think SciLab is an open alternative), which is designed for matrix and
vector calculations.

Another thing you need to decide is whether calculations will be done
sequentially, as in a normal procedural program, or if you can/want to
allow calculations to refer to objects/data that appear 'later' in the
tree sheet (which means you need to determine an execution order
yourself, and catch any circular dependencies).

Anyway, bit of an unstructured comment. I'd be happy to hear other
perspectives.

Thanks,
Glenn.

[Clyde]

TS isn't a calculation program it's an organization program. Don't
think of it as a replacement for Excel, but for notepads, mindmaps,
and other ways to organize data, ideas, and procedures. The fact that
it does any calculations at all is a very minor benefit.

Clyde

On Mar 30, 11:06 pm, burgessg <glenn.gene...@gmail.com> wrote:
> Hi there, I've just installed TS and walked through the tutorial. It
> looks pretty nice, but the 'Spreadsheet' side of it obviously needs
> some work, in the sense of being able to calculate anything.
>

<snip>

> Thanks,
> Glenn.

[Wouter van Oortmerssen]

The way Clyde summarized it is definitely true.

That said, having a calculation language as part of the system doesn't
hurt, and warrants discussion. The reason the existing language is
half-baked is that I am not sure it is the right way to go, it was my
first attempt.

Different people have different needs, and there are various ways
treesheets could be made more programmable:

- the current visual formulas
- excel style formulas
- a macro system
- a scripting language plugin (e.g. Lua)

I certainly don't want to do all of them, i.e. I want to keep things
elegant and simple.

I do like your suggestion on using cell contents as variable lookups.
The way I understand it, it could look something like this
(indentation -> tree structure):

sales
1
2
3
sum(sales)
6

i.e. any unknown names in a formula cause the system to search for a
cell with that name, and then use the children of that cell as the
value to operate on. The result of the formula evaluation would then
be presented as the child of the formula cell. This would also have
the formula always visible, unlike excel. Some will prefer this,
others will not.

Formula cells could be styled in a different way, they could require
the '=' prefix, or require marking the cell as being a formula
manually, like the current language.

As to reusing an existing language for this, you're telling that to
someone who probably holds a world record for inventing languages, So
that may be a tall order ;)

I don't think this language should have a sequential evaluation order,
it is more natural to let the system resolve dependencies and ensure
the right order automatically. The question is how to refer to formula
cells, this could either be done by making a label the parent of a
formula, or allow label: to prefix a formula, for example.

Wouter

[JennyB]

On Thursday, March 31, 2011 5:47:54 PM UTC+1, Wouter wrote:

The way Clyde summarized it is definitely true.

That said, having a calculation language as part of the system doesn't
hurt, and warrants discussion. The reason the existing language is
half-baked is that I am not sure it is the right way to go, it was my
first attempt.

Different people have different needs, and there are various ways
treesheets could be made more programmable:

- the current visual formulas
- excel style formulas
- a macro system
- a scripting language plugin (e.g. Lua)

I certainly don't want to do all of them, i.e. I want to keep things
elegant and simple.

I still think that the most effective approach would be to have a published interface that exported the selected grid to an external program, and allowed that program to possibly return a modified version in its place. That way, if anyone does not like they way you implement your calculation language, they can substitute their own! ;-) 

That means allowing the user to mark the meaning of particular cells in ways that the third-party program understands. You could do it just with tags. To your existing XML output format, add an attribute that marks the cell as tagged. The transforming program responds to the tag names and interprets their grid accordingly, possibly also having a default action for untagged cells or unrecognised styles.

However, Treesheets do not /have/ to use text to distinguish between cell meanings. T hey can do it much better by varying the presentation style. I got this idea from the ColorForth programming language - instead of colour changes highlighting syntax that is expressed in some other way, it is the assigned colour itself that determines how the text is interpreted. Although this would entail more radical changes to Treesheets, I really would like to see something akin the WP stylesheets. Instead of changing the style of a cell directly, you would create a named style that could then be applied to cells with one click. Editing the style would alter the presentation of all the cells to which that style was applied. 

The style name would of course be chosen by the user to reflect the semantics of the cell, and would be passed to the transforming program.  I'm beginning to think that the export format might usefully be JSON, since it is less verbose than XML. Many simple unstyled cells would appear as strings, numbers or nested arrays, while styles would become the names of name:value pairs.

Such external programs could be be written by third parties to provide many more functions than a calculator. Parsing JSON is well understood, and the result could be in any format, for use by any program.

 

[Wouter van Oortmerssen]

On Fri, Apr 1, 2011 at 5:30 AM, JennyB <jenny...@googlemail.com> wrote:
> I still think that the most effective approach would be to have a published
> interface that exported the selected grid to an external program, and
> allowed that program to possibly return a modified version in its place.
> That way, if anyone does not like they way you implement your calculation
> language, they can substitute their own! ;-)

That is certainly a very universal solution, as it allows you to plug
in any external tool, written in any language. But it is of course
also more heavyweight, since the external tool has to parse and
generate XML. Compare with a builtin scripting language, that can
operate on data directly.

This has consequences as to who it is for: having to write a separate
program that involves XML parsing is a barrier to entry that most
"users" won't be able to overcome. A builtin language is a bit
friendlier, and a formula language that lives inside cells is
friendlier still.

> That means allowing the user to mark the meaning of particular cells in ways
> that the third-party program understands. You could do it just with tags. To
> your existing XML output format, add an attribute that marks the cell as
> tagged. The transforming program responds to the tag names and interprets
> their grid accordingly, possibly also having a default action for untagged
> cells or unrecognised styles.

I am not sure if that is even necessary: the external program can
interpret the cell text as it pleases.

> However, Treesheets do not /have/ to use text to distinguish between cell
> meanings. T hey can do it much better by varying the presentation style. I
> got this idea from the ColorForth programming language - instead of colour
> changes highlighting syntax that is expressed in some other way, it is the
> assigned colour itself that determines how the text is interpreted. Although
> this would entail more radical changes to Treesheets, I really would like to
> see something akin the WP stylesheets. Instead of changing the style of a
> cell directly, you would create a named style that could then be applied to
> cells with one click. Editing the style would alter the presentation of all
> the cells to which that style was applied.
> The style name would of course be chosen by the user to reflect the
> semantics of the cell, and would be passed to the transforming program.  I'm

That would certainly be the most flexible, but not simple enough for
my tastes. The power of TreeSheets is about being able to handle
unstructured data; adding "schemas" back in makes it less flexible.

> beginning to think that the export format might usefully be JSON, since it
> is less verbose than XML. Many simple unstyled cells would appear as
> strings, numbers or nested arrays, while styles would become the names of
> name:value pairs.

agreed, yes.

> Such external programs could be be written by third parties to provide many
> more functions than a calculator. Parsing JSON is well understood, and the
> result could be in any format, for use by any program.

Wouter

[Vic]

I'm all for keeping Treesheets lightweight. I like the way it loads
almost instantly so that I can be in and out very quickly when I want
to retrieve or note something, so I'm glad to see your response
Wouter.

But I would like to play with the existing built-in language sometime,
if you have time to give an outline of what it does and how to use
it. I couldn't find instructions on how to name a cell or how to
delimit code in a cell, nor on the available operations.

Vic

On Apr 4, 8:18 am, Wouter van Oortmerssen <aardap...@gmail.com> wrote:

[Wouter van Oortmerssen]

Vic,

Don't worry, I will not allow TreeSheets to become bulky! Whatever
scriptability I may add will not affect loading time, memory usage and
such.

There is a bit of text at the end of the tutorial file that explains
more about the language (in the Advanced Operations section), and I
have made it a bit clearer how to mark things. This file will be in
the new release, which I am about to upload a release-candidate for
within the hour, so check that.

It is not much of a language, per se. It can do simple arithmetic and
sums, which covers 90% of how people use excel, but not much else.

Wouter

> --
> You received this message because you are subscribed to the Google Groups "TreeSheets" group.
> To post to this group, send email to trees...@googlegroups.com.
> To unsubscribe from this group, send email to treesheets+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/treesheets?hl=en.
>
>

[Vic]

Wouter,

By 'tutorial file', do you mean the one installed here by Treesheets?
C:\Program Files\TreeSheets\docs\tutorial.html

Because that doesn't have an Advanced Operations section - the word
"advanced" does not appear. And a G-search on site:treesheets.com
"advanced operations" gives no hits.

Can you give a link, please? Sorry to be dumb.

Vic

On Apr 4, 10:39 pm, Wouter van Oortmerssen <aardap...@gmail.com>

> > For more options, visit this group athttp://groups.google.com/group/treesheets?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Wouter van Oortmerssen]

That is confusing, yes. There is an actual treesheets file called
tutorial.cts, that loads up automatically the first time you install
treesheets. But since you have been using it for so long that may
never have happened to you! It is in the folder where you installed
treesheets, under examples (see start menu).

If you've never seen it, I am curious what you think of it :)

I should maybe change Help -> Tutorial to point to that file instead
of the html?

Wouter

[Vic]

Ah! got it. That's great. I remember that tutorial now, from the early
days, but it looks a lot different in its latest form.

I think it would be good to link to it from the Help menu, but I
suggest leaving the link to tutorial.html as well. Maybe one could be
"Introduction" and the other "Detailed tutorial".

Vic


On Apr 5, 10:26 pm, Wouter van Oortmerssen <aardap...@gmail.com>

> >> > For more options, visit this group athttp://groups.google.com/group/treesheets?hl=en.-Hide quoted text -

[Wouter van Oortmerssen]

Will add that :)

[Jennifer Brien]

On Thu, Mar 31, 2011 at 5:47 PM, Wouter van Oortmerssen
<aard...@gmail.com> wrote:

> As to reusing an existing language for this, you're telling that to
> someone who probably holds a world record for inventing languages, So
> that may be a tall order ;)
>
> I don't think this language should have a sequential evaluation order,
> it is more natural to let the system resolve dependencies and ensure
> the right order automatically. The question is how to refer to formula
> cells, this could either be done by making a label the parent of a
> formula, or allow label: to prefix a formula, for example.

I've been playing around with this for some time, and come up with these rules:

1 Run operates on a selection, not the whole sheet.
2 When Run, a formula cell is replaced with its value. Other cells
in the selection are returned unchanged
3 Therefore, a common technique will be to Paste and Run a selection.
4 When a cell is Run, its grid cells (if any) are Run first,
sequentially, in column-first order.

5 All formula are in Reverse Polish Notation (like a HP calculator)
6 Formula arguments may be single values or arrays (grids).
7 As in APL, arrays are normally treated element-by-element,
though there may be operators for reduction, and matrix or all-pairs
8 If an argument is not stated for an operator, the cell grid is used.
8 If the grid is absent or already used, use a copy of the
contents of the most recent unused Run cell
9 By rule 2, if a cell formula takes its grid as an argument then
the grid is no longer available or displayed.
10 The operator -> takes one argument and assigns it to a variable.

Variables are scoped to the grid in which they are first used.
A variable is roughly the same as an absolute spreadsheet reference.

In addition, we need operators to do relative referencing, by indexing
back into the grids already run.
In the example below - a basic spreadsheet exercise - " 2 r " means
"the cell 2 rows up", and "1 c" "1 column back"

Loan Payment Chart
Rate of interest
100 / -> roi
Payment
-> pay
Debt 1 c payoff -
Interest roi * roi *
Due + pay min + pay min
Debt paid 2 r - 2 r -
-> payoff -> payoff

To use this, paste a copy of the first two columns, enter values for
Rate of Interest, Payment and Debt, say 5, 500, 2,000

Run it, and the result should look like this:

Loan Payment Chart
Rate of interest 5

Payment 500

Debt 2,000
Interest 100
Due 500
Debt paid 400

Now paste and run the third column to the right as many times as
needed to reveal the bitter truth!

Unlike a spreadsheet, any or all of the parameters could be replaced
by a grid, to display several possible scenarios simultaneously.

Note how in this case each formula cell implicitly takes an argument
from the line above. The lines with -> do not return anything, and so
are blank in the result.

[Wouter van Oortmerssen]

Jenny,

> I've been playing around with this for some time, and come up with these rules:
>
>   1  Run operates on a selection, not the whole sheet.
>   2  When Run, a formula cell is replaced with its value. Other cells
> in the selection are returned unchanged
>   3  Therefore, a common technique will be to Paste and Run a selection.

Sorry, but I do not like this one bit. It is very inconvenient to have
to copy-paste-select-run, when in most systems you can just run. I
also should be able to run it multiple times without "losing" my
formulas.

The formulas need to stay in place. Either they are hidden behind the
data like excel (which I am not a fan of), or they are always visible
and their child is the result computed, or they are folded away or
something.

>   4  When a cell is Run, its grid cells (if any) are Run first,
> sequentially, in column-first order.
>
>   5  All formula are in Reverse Polish Notation (like a HP calculator)

Why would that be... I have developed several RPN (Forth) style
languages myself, and really love their elegance, but the vast
majority of humans have trouble reading it. I'd like it to be end user
friendly.

Wouter