help with org-mode table formula

Question

I want to track my cash flow and growth in text files.

Here is the sample table and formula.

Networth = sum of all accounts
Growth = previous month networth - current month networth
Growth % = Growth / prev. month networth

I am planning to simply append a column for every month.

    | Accounts    | June 2019 | July 2019 |
    |-------------+-----------+-----------|
    | cash        |       100 |       110 |
    | stocks      |       200 |       205 |
    | gold        |        10 |        10 |
    | Liabilities |       -20 |        -5 |
    |-------------+-----------+-----------|
    | Networth    |       290 |       320 |
    |-------------+-----------+-----------|
    | Growth      |           |        30 |
    | Growth %    |           |      10.3 |
    #+TBLFM: <what should I put here?>

What TBLFM should I use?

Answer 1

The following #+TBLFM line works for me:

    |   | Accounts    | June 2019 | July 2019 | August 2019 |
    |---+-------------+-----------+-----------+-------------|
    |   | cash        |       100 |       110 |         120 |
    |   | stocks      |       200 |       205 |         225 |
    |   | gold        |        10 |        10 |          10 |
    |   | Liabilities |       -20 |        -5 |         -30 |
    |---+-------------+-----------+-----------+-------------|
    | # | Networth    |           |           |             |
    |---+-------------+-----------+-----------+-------------|
    | # | Growth      |           |           |             |
    | # | Growth %    |           |           |             |
 #+TBLFM: @>>>$3..@>>>$> = vsum(@I..II) :: @>>$4..@>>$> = @-1 - @-1$-1 :: @>$4..@>$> = (@-1 / @-2$-1)*100;f1

Explanation:

• @>>>$3..@>>>$> = vsum(@I..II) - The LHS says "operate on the range of cells that consist of columns 3 to last, on the third row from the bottom of the table". The syntax @<row1>$<col1>..@<row2>$<col2> specifies a range of cells in a rectangle with an upper left-hand corner at (<row1>, <col1>) and a lower-right-hand corner at (<row2>, <col2>). @> is notation for the last row, @>> is notation for the second-to-last, etc. Similarly for columns, $> is the last column, $>> is the second-to-last, etc. The RHS says (as @MarcoWahl pointed out in a comment) "sum all the rows of the current column between the first and second divider lines". So we march along all the columns in row 6 (aka @>>> - but the latter remains valid if you add more categories of assets/liabilities, whereas the former does not, unless you use the org-table-insert-row function to insert the new row, in which case, org will rewrite the formula and change the 6 to a 7; I usually insert rows by hand, so I prefer the latter notation); in each column, we sum the rows of that column between the first and second dividers and assign the sum to the given cell.

• @>>$4..@>>$> = @-1 - @-1$-1 - The LHS is almost the same, except that it affects the second row from the bottom of the table and it starts from column 4 and goes to the last column. The reason for starting with column 4 is that in order for the calculation to make sense, we need to subtract the sum of the previous column: there is no previous column for column 3, so we have to skip it. The RHS says "from the cell on the previous row of the current column (@-1) (which contains the sum of the current column), subtract the cell on the previous row and one column to the left (@-1$-1) (which contains the sum of the previous column). These are "relative references". Now march along all the columns and do that same calculation.

• @>$4..@>$> = (@-1 / @-2$-1)*100;f1 - The LHS is the same as the previous case, except that it refers to the last row. The RHS says "divide the cell on the previous row of the current column (@-1) by the cell two rows up and one column to the left (@-2$-1), multiply the result by 100 and keep one digit after the decimal point".

This is all explained in the Spreadsheet References section of the manual.

You might have noticed that I added an extra column on the left, containing # markers (this BTW is the reason that the formulas start with column 3 (or 4) instead of 2 (or 3) for a table without that column). These are explained in the Spreadsheet Advanced Features section of the manual. Briefly, this indicates to org that every time you press a TAB on one of these rows, the row is recalculated automatically. This might or might not be what you want: the problem I was trying to solve is that in my case, the rows were calculated bottom to top, which meant that the rows needed to be updated multiple times: the first time that the last row was recalculated, the previous two rows did not have everything needed, so after those two rows were calculated, the last row needed to be recalculated again. I had to recalculate the table three times in order to get everything updated properly. The markers "solve" that problem because I could visit each row in proper sequence (top to bottom in this case), press TAB and have that row updated properly, before going to the next one.

Alternatively, there is a command org-table-iterate that is supposed to iterate the calculations until there is no change in the table. It is bound to C-u C-u C-c * but you need to be in the table for that binding to be available - for some reason, if you are on the #+TBLFM line, it does not work. You can also select it from the Tbl/Calculate menu. If you prefer this method, you can get rid of the first column I added, but don't forget to adjust the column numbers in the formula:

    | Accounts    | June 2019 | July 2019 | August 2019 |
    |-------------+-----------+-----------+-------------|
    | cash        |       100 |       110 |         120 |
    | stocks      |       200 |       205 |         225 |
    | gold        |        10 |        10 |          10 |
    | Liabilities |       -20 |        -5 |         -30 |
    |-------------+-----------+-----------+-------------|
    | Networth    |       290 |       320 |         325 |
    |-------------+-----------+-----------+-------------|
    | Growth      |           |        30 |           5 |
    | Growth %    |           |      10.3 |         1.6 |

#+TBLFM: @>>>$2..@>>>$> = vsum(@I..II) :: @>>$3..@>>$> = @-1 - @-1$-1 :: @>$3..@>$> = (@-1 / @-2$-1)*100;f1