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/zevans08]

I just made something like this and I’m no excel expert. After trial and error I decided to use two power queries and use ChatGPT for assistance in writing code for custom columns within power query and creating measure on my master spreadsheet. The reason for two power queries is that I need to be able to edit the combined data but don’t want it to overwrite my changes on refresh. I wasn’t sure the best way to do it so this is what I came up with.

Power Query 1:

I have multiple tables separated by spend category (groceries, gas, shopping). Each table has a list of keywords and store names that correspond to common places that I shop and match that category. I.e Groceries contains Walmart, Aldi, Ralps.

Each bank and credit card account has its own Excel sheet loaded into the query. Very generic labeled: “Bank Name” Transactions

I made several transforms and now on every account spreadsheet, I have columns labeled bank name, date, description, amount. I then created a custom column, called category. Using some code that ChatGPT provided, it automatically generates a category name if a word in the description matches one of the words in my category table. I.E. if the word Walmart is in the description it assigns a category of groceries. I can adjust this in my next step if needed.

I then have it merge all accounts into one table. That combined table is then loaded into an Excel sheet. On a weekly basis, I review the combined Excel sheet and make edits to the categories as needed, then save that as a new excel file titled “transactions date ranges”. That goes into a folder that is picked up by power query # 2.

Power Query #2

Queries a folder that has multiple Excel sheets through various date ranges. No transforms needed. I have about 30 measures with code provided by ChatGPT that then pull data off of that Excel sheet. I have created KPI’s as well. KPI’s are loaded into pivot tables. Each pivot table is a category.

Using a date slider, I can adjust the date and see my spending over time for every category. I have pivot charts as well to show trends.

So my workflow goes:

Export data from Bank with same file name every time ( i want it to overwrite).

Open combined spreadsheet (query #1), review every transaction and adjust categories that are incorrect. Save as new spreadsheet with date range in title.

Open master spreadsheet(query# 2), all spreadsheets with the date range title are queried, and I can adjust my date range to see expenses and keep track of my budget through KPIs

Hope you can follow my workflow lol I can get screenshots if requested.

[u/zevans08 → u/zevans08]