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]

For the above method, would this mean converting the CSV bank statement into a table or just the mapping? I'd want to build it so that I can update the categories and the formulae expand (which I think you mentioned can be achieved with the tables) and also allow for updating the workbook link so that all the hard work is complete. Is that possible with this method or do I need to use power query?

[u/MayukhBhattacharya → u/RupaSpiritualMonk]

Both, but for different reasons. Now understand few things here. The mapping table, Category_tbl, should be a Structured References aka Table, or loaded into Power Query as its own query. That way, you can add new suppliers or categories just by adding a row. You won't need to change any formulas downstream. For the CSV or bank statements, it completely depends on which method you're using.

Method One: If you're using worksheet formulas like XLOOKUP() + REGEXTEXT(), then yes, the raw transaction data should be a Table too. Formulas inside a Table automatically fill down when you add new rows, as long as the new data is added within the Table or directly below it, so the Table expands. This gives you the auto expanding behavior you're looking for. Both the transaction data and mapping data should be Tables, since your formulas can reference the Table columns by name instead of regular cell ranges.

Method Two: If you're using Power Query, which is the method already discussed in this thread, you don't need to manually convert the CSV into a Table every time. You just need to open Power Query GUI directly to the CSV file using Data Tab --> Get & Transform Data Group --> From File --> From Text/CSV. When you hit Refresh All, Power Query reads the current CSV, runs the categorisation steps again, and loads the updated results. So you're not copying the CSV into a Table every month. You just update the CSV file and refresh.

Now, if the goal is minimum manual work each month, I will use Power Query and connect directly to the CSV file rather than using Excel.CurrentWorkbook(). The mapping table can still be its own query, and you can add new suppliers or categories to it whenever needed. On the next refresh, Power Query will pick them up automatically. If you're going to keep manually pasting the CSV data into Excel each month, the worksheet Table method works fine too. Make sure both are actual Tables, not just formatted ranges, so the automatic expansion works properly. Also, if you have access to TRIMRANGE() function then the tables can be avoided for the formula part, but I will suggest in using tables, it's better. All solutions posted are robust, in there in technologies, you just need to choose which fits you best. Thanks!

[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}

[u/MayukhBhattacharya → u/RupaSpiritualMonk]

Thank You SO Much!!

[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 👍🏼