Summarize by groups and only display the sums

[MLAB]

Hi Coda community,

New user here.

I have a bank statement transaction database, which now needs to be summarized and displayed so that I can know how much has been spent per month in each category. This is easily done in Excel by combining 2 conditions in a SumIfs formula. I understand I cannot “make” a table like this in Coda due to its relational database nature, but there must be a way to to accomplish that in Coda, but I can’t find how. I prepared an example: Example.

I was hoping to display the categories as rows and YearMonth as columns. I can do it by creating a view of the table containing the bank statements and can even calculate the sums, but I cannot make the individual transactions disappear.

I also played with formulas like thisTable.Filter(Category=ThisRow,YearMonth="2022.1").Amount.Sum() but with no success.

I appreciate your inputs as I am stuck in this for over a week!

[Pch]

Hi @MLAB and Welcome to the Community !

Is this what you’re trying to accomplish ?

The [Summary per Month & Category] table is there to link each month in your dbYearMonths to a category in your dbCategories and then, the field bankstatements retrieves all the corresponding transactions from your bankstatements table with this formula :

bankstatements.Filter(
  YearMonth = thisRow.dbYearMonths 
  AND Category = thisRow.dbCategories
)

The amounts corresponding to the rows returned by this Filter() formula are summed in the field [Amount - Sum] next to it .

I am sadly a bit too short on time to give you more explanations at the moment (sorry ) but, in the meantime, I hope this will help you a little

[MLAB]

Hi @Pch

You nailed it!

I am now dissecting what you did.

I understand that you created [Summary per Month & Category] table in order to link categories with months and then to retrieve all transactions that have the specific combination Category_i x Month_j, even if there is no transaction that fits that combination. So, there will be one row for each combination of Category and Month (in this case, 12 months and 7 categories equals to 84 rows).

That makes a lot of sense to me. Than I must ask, how did you create that Category/Month structure without tediously copying and pasting?

Thanks a lot!

[MLAB]

My point is less of boringness and more of consistency, as the Categories list will likely evolve over time and the YearMonth will certainly grow.

[Doug_Loud]

Thanks, Pch! I have been thinking about approaching this problem “with tongs” for analyzing “suspicious transactiions” for some time, but you have answered all my questions for me. Thanks!

[Pch]

Hi @MLAB !

@MLAB

So, there will be one row for each combination of Category and Month (in this case, 12 months and 7 categories equals to 84 rows).

Correct !

@MLAB

Than I must ask, how did you create that Category/Month structure without tediously copying and pasting?

I used the currently disabled canvas button just above the [View of Summary per Month & Category] .

Its action formula is this one:

dbYearMonths.ForEach(
  CurrentValue.WithName(YM,
    dbCategories.ForEach(
      [Summary per Month & Category].AddRow(
        [Summary per Month & Category].dbYearMonths,
        YM,
        [Summary per Month & Category].dbCategories,
        CurrentValue
      )
    )
  )
)

The button first takes the “list of rows” dbYearMonths is (each specific row in the list is represented by CurrentValue) and to each row I give the name YM using WithName() so it can be used later on.

Then, it takes the other “list of rows” dbCategories is (where each specific row is also represented by CurrentValue) and for each row, I ask the button to add a row to the table [Summary per Month & Category] where:

• for the field [Summary per Month & Category].dbYearMonths it should input YM (the current month in the list)
• for the field [Summary per Month & Category].dbCategories it should input CurrentValue (the current category in the list)

Sorry if everything’s not clear … loops are not the most easiest thing to explain …
But the button loop through all the months in dbYearMonths, one by one, and for each month it adds each category from dbCategories .

Just in case, here’s a topic that helped me a lot when I was struggling with ForEach() / FormulaMap()

FormulaMap() : When and where?

@Pch Good discussion. Tossing this in for anyone else having this question. List(2,3,4).FormulaMap( CurrentValue * 2 ) 4,6,8 This is the new list comprised of each value from the original list transformed by the formula invoked in FormulaMap(). FormulaMap() is a forEach() loop that returns values. For each value in a source list, FormulaMap() maps a formula to each one of those values, meaning that it runs that formula on each value in the list. The results are returned in a n…

As for this :

@MLAB

My point is less of boringness and more of consistency, as the Categories list will likely evolve over time and the YearMonth will certainly grow.

I could make some suggestions regarding the consistency, but it depends on your current setup, how you would like/need to add months & categories to their respective tables and also, how you like to interact with your bankstatements table …

There are often more than one way to accomplish anything in Coda

[Pch]

Ahahah !

My pleasure @Doug_Loud ! Glad to know this helped you !

[MLAB]

That works beautifully, @Pch !

Your explanations were very clear, thank you so much!

Regarding the set up:

• Every month I will bring different bank statements to this database (different cash accounts, investments, credit cards, etc). I am planning to (1) keep them separately because each provider has its own format (some even don’t provide the tabular data), (2) I will process them (and automate as much as possible) and (3) finally aggregate them into a summary per month per category, as we have discussed so far.
• To be able to effectively categorize the expenses is the priority but it is not the only goal.
• In addition to the expenses, I will also track investments. And here the relational database nature of CODA will shine. I will have a table for each category of investment (Stocks, REITs, bonds, crypto, etc). For investments that pay dividends and coupons, I will track such payments on the bank statements, so that I am on top of it.
• Finally, I need to have a bird eyes view on my portfolio of investments, having for example a stacked graph of the position of each investment class at the last day of each month, for instance.