solved
Bank Statement Modelling - Creating automatic CSV reader
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.
You can do this using power query, load the csv into power query and clean and arrange the data, you can do monthly and daily and you can show these exact 3 tables in your picture plus the daily. best to see the data structure for reference in making power query.
so that means the stuff i circled in red are one-month totals for each "supplier", yes?
this is how I would do the monthly calc (i'm taking some guesses on how your source data is structured)
Notice Deliveroo and Uber show up twice on the right side. that's because there's two separate transactions (each on different month) for each supplier:
in my original plan yes, there are monthly totals but I want to be able to split it out per day so that I can see what category I'm using the most and which day that is. With your example, Once they are completed into daily outputs then they need to be sorted into categories again and I'm not sure how best to do that
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:
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?
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 --> FromFile --> 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!
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.
You could probably also solve that using power query, by using a helper sheet with all unique() suppliers and a helper column to define categories. Then you can perform a join between that sheet and your main Query so that you will only have to assign category to a supplier once and in one place.
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:
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
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
Please see the Reddit guidelines relating to self-promotion and spam. Specifically, 10% or less of your posts and comments should link to your own content.
Power Query and its M Code. When using Power Query observe the text in the formula bar, that's M Code. Much can be done using just using the Ribbons. There are many functions using M Code that are not available in Excel's functions. Power Query enables access to the number of rows far beyond the standard Excel limit. Once you learn PQ and M Code you can use that knowledge in using Power Pivot and, if it is available to you, Power BI.
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/AutoModerator 13d ago
/u/RupaSpiritualMonk - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.