Accountant alert - rolling account

Showcase Doc Show and Tell
1

HI All,

Often when keeping track of financial information over time, you need to build a table with the months of the year, take the closing balance to the opening balance on the new line, etc, etc.

The below doc that I created has a simple table that allows you to track multiple accounts/ loans/ investments.
Capture an annual interest rate (Rente (jaarliks)), the formula will divide by 12 when it calculates interest. Interest is caclulated on the Opening balance (saldo)

image
image1230×596 22.8 KB

2 Likes
2

This is brilliant @Piet_Strydom. I was thinking how to implement SAP-style inventory audit in Coda. This solves half of the issue. Next, is how to link inventoryLog with inventoryAudit for In and Out.

1 Like
3

I modify your doc a bit

My objective is to have next data entered (Whenever it was entered) to refer to previous relevant row, do you have any idea how to tweak it?

Thanks

1 Like
4

Hi Hendrik

Unfortunately no, I do not know. It is not a simple topic to do in Coda. If you record date and time for each adjustment, you could find the most recent row before the current one.

Regards
Piet

5

@Hendrik_TnB

you can assign a sequence number to each row upon creation.
this number can then be used to reference rows ‘before’ and ‘after’ thisRow.

‘before’ and ‘after’ refers to the sequence in which the rows were created. sorting and filtering the table may put the rows into a different sequence on the screen, but the sequence number I illustrate below will always be based on the sequence of row creation.

create a number column, lets call it SEQ

in the ‘Number Options’ menu for the column, select the ‘Value for New Rows’ field, make it a formula (click the (f) symbol) and enter the following formula…

thisTable.Count()+1

that formula takes the count of the rows in this table, adds one, and stores that in SEQ

now, when you need to refer to the column ColumnX of the ‘previous’ row, you use this…

thisTable.Filter( SEQ = thisRow.SEQ-1 ).ColumnX

which works as follows

  • thisTable.Filter(…) will select all the rows that match the criteria below
  • the criteria (SEQ = thisRow.SEQ-1) selects the one row whose SEQ is one less than this row’s SEQ
  • the .ColumnX then selects the ColumnX value from that selected row

not sure if this is what you were looking for?

max

2 Likes
6

Thanks Max, it is helpful. But it was not I wanted to achieve.

Perhaps you can take a look at this screenshot.

image
image1083×220 9.67 KB

So it will be sorted based on item (1st) and document date (2nd).

7

Hi @Agile_Dynamics ,

I finally manage to solve it.

image
image1657×443 37.1 KB

First, using visual position, I need to find the delta for between rows. Secondly, calculate the cumulative sum. And finally the ending balance column.

Edit: big thanks to @Christiaan_Huizer medium post.

1 Like