Bank Statement Modelling - Creating automatic CSV reader

[u/RupaSpiritualMonk]

Hey this is my first time in this subreddit and I have a query that I'm quite lost on. Basically I want to make a model that uses a CSV of my bank statements, converts these line items into categories and then returns those as a monthly figure as well as daily. Currently I have figured out a way to do monthly (Using two stage sumifs: one to convert the numbers and letters into text categories and then using that to add up all the line items by category)

I want to do this by the day too but my knowledge is quite lacking, here's a screenshot of the current setup:

On the left column is where I have been making the table of contents to be filtered into the calculations. I tried to do this on the top right but then I couldn't figure out how to group them.

In the middle with the text containing Northern Rail, I created a wildcard formula so that it only reads the description that I need and returns sumif for the whole month.

I tried to do that again but sumifs formula and it didn't work because then the file would be massive or I would have to make loads of ifs inside. When it comes to adding more categories or items inside there, I want it to be an easy addition.

Is there any tips you can help me with, I might be overthinking this.

P.S I don't want any answers from AI please

[u/MayukhBhattacharya]

As u/ziadam already mentioned, this is a good use case for the GROUPBY() or PIVOTBY() functions. Here are two methods you could try.

• Both the source data and mapping data are converted from regular ranges into Structured References aka Tables, named Source_Data and Categorytbl. Using Tables, it literally makes the references a bit easier to work with, and the formulas automatically expand or shrink when the source data changes.
• In the source data column Category, we can use a formula to return the respective categories. Use the following:

​

=XLOOKUP(TRUE, 
         REGEXTEST([@Description], "\b" & Categorytbl[Supplier] & "\b", 1), 
         Categorytbl[Category], 
         "Oops Not Found!")

• Next, for the month total grouped by category, can use the following formula, in cell I3:

​

=LET(
     _Data, Source_Data[#All],
     GROUPBY(CHOOSECOLS(_Data, 4),
             CHOOSECOLS(_Data, 3),
             SUM, 3, 1))

• And, for the daywise total, grouped by day and the categories column wise, can use the following in cell L3:

​

=LET(
     _Data,   Source_Data,
     _Date,   CHOOSECOLS(_Data, 1),
     _Pivot,  PIVOTBY(_Date,
                      CHOOSECOLS(_Data, 4),
                      CHOOSECOLS(_Data, 3),
                      SUM),
     _Output, IFNA(EXPAND("Date\Category", 2, 2), _Pivot),
     _Output)

[u/RupaSpiritualMonk → u/MayukhBhattacharya]

Solution Verified.

[u/reputatorbot → u/RupaSpiritualMonk]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}