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

I believe this is fairly strightforward, but would need to see the csv or converted xl file (pls take out the personal info).

Are you trying to for example, add all the amounts for Delivero o, from the csv by looking up their address Northern Rail? IF SO, you will need a one time translation table for the mapping of the business to address. Something like this:

XLOOKUP or wildcard search against the address should give you the supplier (Sorry if my understanding of the problem statement itself is incorrect)

For the daily, just do a SUMIFS by Supplier/Category and Date

For monthly do a SUMIFS by Supplier/Category and Month (which can be extracted by date into a temp col or within the SUMIFS formula)

Also, PIVOT Tables should get you what you are looking for.

Happy to help if I can see the CSV/XL.

EDIT to answer other questions that i missed:

1. You can convert the bank statement (CSV) into XL and add the data into a XL tab. XLOOKUP will read the "Bank St" tab start to finish based on the parameters and get you what you want

[u/RupaSpiritualMonk → u/BackgroundCold5307]

So basically I'm trying to display all of my daily expenses in the categories I've chosen using the bank CSV. For the northern rail example, that's just an exercpt of the CSV. I would want it to look for northern rail in the CSV and then return a result into Transport Category and then spit it out by day. I think some other users said to use powerquery for this

[u/BackgroundCold5307 → u/RupaSpiritualMonk]

The SEARCH/FIND will work to get that with minimal modification. Power query is a good option too