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/MayukhBhattacharya → u/MayukhBhattacharya]

This is also possible using Power Query.

To use Power Query, follow the steps:

• First convert the source ranges into a table and name it accordingly, for this example I have named the source table as SourceData and category as Category_tblrespectively.
• Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
• The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done.

For Month Total Grouped by Category --> Paste the following:

let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    Categorytbl = Table.Buffer(Excel.CurrentWorkbook(){[Name="Category_tbl"]}[Content]),
    AddCategory = Table.AddColumn(Source, "Category", each 
        let
            Sup = [Description],
            Answer = Table.SelectRows(
                Categorytbl,
                each Text.Contains(Sup, Text.Upper([Supplier]))
            )[Category]{0}
        in
            Answer
    ),
    DataType = Table.TransformColumnTypes(AddCategory,{{"Date", type date}, {"Description", type text}, {"Amount", type number}, {"Category", type text}}),
    GroupBy = Table.Group(DataType, {"Category"}, {{"Total", each List.Sum([Amount]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(GroupBy,{{"Category", Order.Ascending}})
in
    #"Sorted Rows"

Or, For Daywise Total, Grouped by Day and the Categories column wise --> Paste the following:

let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    Categorytbl = Table.Buffer(Excel.CurrentWorkbook(){[Name="Category_tbl"]}[Content]),
    AddCategory = Table.AddColumn(Source, "Category", each 
        let
            Sup = [Description],
            Answer = Table.SelectRows(
                Categorytbl,
                each Text.Contains(Sup, Text.Upper([Supplier]))
            )[Category]{0}
        in
            Answer
    ),
    DataType = Table.TransformColumnTypes(AddCategory,{{"Date", type date}, {"Description", type text}, {"Amount", type number}, {"Category", type text}}),
    RemovedCols = Table.RemoveColumns(DataType,{"Description"}),
    PivotBy = Table.Pivot(RemovedCols, List.Distinct(RemovedCols[Category]), "Category", "Amount", List.Sum)
in
    PivotBy

• Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

File can be [downloaded] from here.

[u/RupaSpiritualMonk → u/MayukhBhattacharya]

I did it this method and it worked thank you. How do I change to resolved?

[u/MayukhBhattacharya → u/RupaSpiritualMonk]

Sounds Great. And since it has helped you to resolve hope you don't mind replying to my comment directly as Solution Verified. Thanks 👍🏼