Categorizing Bank statements

[jklarsen]

Hi All,

I have been trying to find a previous reply to my challenge with no luck, so I am taking the chance a asking you this.

I want to categorise my bank statements and a spending overview, based upon predefined shop names and categories.

- So basically I paste my bank statement into the sheet.
- I have a prefilled sheet with shop names which I update manually
- I want to index bank statement with the predefined shop name
- Finally I want to generate an yearly overview of my spending throughout the year for each category.

Like this:

I can't figure out how to:
3. Search for the shop names in the bank statement text and return the shop name, main category and sub category
4. Search for the amount of sub categories in each month, returning the SUM

I also have the file here in my dropbox - Bank Statements.xlsx


Can anybody help guide, how to do this?

Kind regards,

Jan H.

 

[maabadi]

I upload my file Table to table and Describe.
1. Table 2

There is some cases where I need to add a " " after the name for it to work,

You can use this function to remove extra spaces at shop names and then paste as value them at source range (Yellow Part)

=TRIM(SUBSTITUTE(B21,CHAR(160),CHAR(32)))

2. Table 1
My formula works completely well. (Blue Part)
For Extract month name from Date Use Text Formula (Green Part):

=TEXT(B4,"mmmm")

Bank Statements.xlsm
	A	B	C	D	E	F	G	H
1
2		1. Bank Statements
3		Date	amount	Statement Text	Extract Shop Name	Extract Main	Extract Sub
4		1/1/2021	-195.00	Dankort-nota Louis Nielsen 25053	Louis Nielsen	Transportation	Parking	January
5		1/4/2021	-155.00	Dankort-nota The Coffee Col 12261	Coffee Col	Other living costs	TV & Streaming	January
6		1/6/2021	-100.00	Dankort-nota OLE MATHIESEN A 6868	Ole Mathiesen	Personal spending	Doctors & Treatment	January
7		2/8/2021	-200.00	Dankort-nota DSB 764 Frh RKA 015	DSB	Housing	Furniture & housing equipment	February
8		3/1/2021	-65.00	Visa køb DKK 65,00 Mc Donalds Valby V Den 21.07	Mc Donalds	Transportation	Parking	March
9		3/10/2021	-23.07	Visa køb DKK 23,07 EASYPARK 280377052 Den 21.07	Easypark	Transportation	Parking	March
10		4/14/2021	-509.00	Dankort-nota proshop.dk 2211	proshop.dk	Personal spending	Hardware	April
11		5/20/2021	-228.00	Visa køb DKK 228,00 TORVEHALLERNES FRUGT Den 22.07	Others			May
12		4/14/2021	-221.50	Visa køb DKK 221,50 Hav Torvehallerne AS Den 22.07	Others			April
13		4/14/2021	-140.00	Visa køb DKK 140,00 THE COFFEE COLLECTIV Den 22.07	Others			April
14		5/14/2021	-100.00	Visa køb DKK 100,00 STIGS OLIVEN APS K Den 22.07	Stigs	Personal spending	Groceries	May
15		5/14/2021	-100.00	Visa køb DKK 100,00 LAURAS BAKERY TORVEH Den 22.07	Lauras bakery	Personal spending	Cafes	May
16		6/14/2021	-99.00	Visa køb DKK 99,00 APPLE.COM/BILL 802 Den 22.07	Apple.com/bill	Personal spending	Online services	June
17		6/14/2021	-25.00	Visa køb DKK 25,00 APPLE.COM/BILL 802 Den 22.07	Apple.com/bill	Personal spending	Online services	June
18
19		2. Shop names
20		Name	Main category	Sub category
21		McDonalds	Personal spending	Fast food & Take away	McDonalds	Personal spending	Fast food & Take away
22		Mc Donalds	Transportation	Parking	Mc Donalds	Transportation	Parking
23		Louis Nielsen	Transportation	Parking	Louis Nielsen	Transportation	Parking
24		Coffee Col	Other living costs	TV & Streaming	Coffee Col	Other living costs	TV & Streaming
25		Ole Mathiesen	Personal spending	Doctors & Treatment	Ole Mathiesen	Personal spending	Doctors & Treatment
26		DSB	Housing	Furniture & housing equipment	DSB	Housing	Furniture & housing equipment
27		Easypark	Transportation	Parking	Easypark	Transportation	Parking
28		proshop.dk	Personal spending	Hardware	proshop.dk	Personal spending	Hardware
29		Stigs	Personal spending	Groceries	Stigs	Personal spending	Groceries
30		Lauras bakery	Personal spending	Cafes	Lauras bakery	Personal spending	Cafes
31		Apple.com/bill	Personal spending	Online services	Apple.com/bill	Personal spending	Online services
Sheet1

Cell Formulas
Range	Formula
E4:E17	E4	=ExtractShopName($D4,$B$20:$D$31,1)
F4:F17	F4	=ExtractShopName($D4,$B$20:$D$31,2)
G4:G17	G4	=ExtractShopName($D4,$B$20:$D$31,3)
H4:H17	H4	=TEXT(B4,"mmmm")
E21:G31	E21	=TRIM(SUBSTITUTE(B21,CHAR(160),CHAR(32)))

 

[maabadi]

3. For Table 4
I transposed it and paste at Sheet 2 (Also you can copy all of Main & sub Categories to sheet 2 (Column A & B) and then Select all , Go to Data Tab, Remove Duplicate and select Both Columns . if you want sort, Then Sort it, I also add Macro to do both remove duplicates and sort)
Then Add month Name as Headers for Columns from Column C to N.
Finally Use Sumifs Formula:

=SUMIFS(Sheet1!$C$4:$C$17,Sheet1!$F$4:$F$17,$A2,Sheet1!$G$4:$G$17,$B2,Sheet1!$H$4:$H$17,C$1)

Bank Statements.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Main category	Sub category	January	February	March	April	May	June	July	August	September	October	November	December
2	Housing	Doctors & Treatment	0	0	0	0	0	0	0	0	0	0	0	0
3	Housing	Furniture & housing equipment	0	-200	0	0	0	0	0	0	0	0	0	0
4	Housing	Insurance	0	0	0	0	0	0	0	0	0	0	0	0
5	Housing	Maintenance	0	0	0	0	0	0	0	0	0	0	0	0
6	Other living costs	Online Services	0	0	0	0	0	0	0	0	0	0	0	0
7	Other living costs	Parking	0	0	0	0	0	0	0	0	0	0	0	0
8	Other living costs	Sports & Hobbies	0	0	0	0	0	0	0	0	0	0	0	0
9	Other living costs	TV & Streaming	-155	0	0	0	0	0	0	0	0	0	0	0
10	Personal spending	Cafes	0	0	0	0	-100	0	0	0	0	0	0	0
11	Personal spending	Doctors & Treatment	-100	0	0	0	0	0	0	0	0	0	0	0
12	Personal spending	Fast food & Take away	0	0	0	0	0	0	0	0	0	0	0	0
13	Personal spending	Groceries	0	0	0	0	-100	0	0	0	0	0	0	0
14	Personal spending	Hardware	0	0	0	-509	0	0	0	0	0	0	0	0
15	Personal spending	Online services	0	0	0	0	0	-124	0	0	0	0	0	0
16	Transportation	Gas	0	0	0	0	0	0	0	0	0	0	0	0
17	Transportation	Maintenance	0	0	0	0	0	0	0	0	0	0	0	0
18	Transportation	Parking	-195	0	-88.07	0	0	0	0	0	0	0	0	0
Sheet2

Cell Formulas
Range	Formula
C2:N18	C2	=SUMIFS(Sheet1!$C$4:$C$17,Sheet1!$F$4:$F$17,$A2,Sheet1!$G$4:$G$17,$B2,Sheet1!$H$4:$H$17,C$1)

 

[maabadi]

this is your File with Macros
Bank Statements

 

[jklarsen]

Thank this is awesome.

Is there anyway to transpose back to the original layout.
I use this to compare expenses between years, which is easier with the original layout

Many thanks.


Best...Jan

 

[maabadi]

Yes. Again Copy and Paste Tranposed Your Data.
Also You can use VBA & Macros.

 

[maabadi]

Thus. At Row 50 fill all cells. same as uploaded and Use formula at C52 and drag it right & down.

Bank Statements.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
50		4. Overview	Personal spending	Personal spending	Personal spending	Transportation	Transportation	Transportation	Other living costs	Other living costs	Other living costs	Housing	Housing	Housing
51		2021	Fast food & Take away	TV & Streaming	Groceries	Parking	Gas	Maintenaince	Parking	Online services	Sports & Hobbies	Doctors & Treatment	Maintenaince	Insurance
52		January	$0.00	$0.00	$0.00	-$195.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
53		February	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
54		March	$0.00	$0.00	$0.00	-$88.07	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
55		April	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
56		May	$0.00	$0.00	-$100.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
57		June	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
58		July	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
59		August	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
60		September	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
61		October	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
62		November	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
63		December	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Sheet1

Cell Formulas
Range	Formula
C51	C51	=D21
D51	D51	=D24
I51	I51	=D23
L51	L51	=D25
C52:N63	C52	=SUMIFS($C$4:$C$17,$F$4:$F$17,C$50,$G$4:$G$17,C$51,$H$4:$H$17,$B52)

 

[jklarsen]

Awesome - Thank you very much!!!!!!


Best...Jan

 

[maabadi]

You're welcome & thanks for feedback. Please Tick Best answer post as solving Post with Click on tick of right of Post.

 

[jklarsen]

Going through the document Bank statement you uploaded I noticed a different behaviour from what you uploaded.

If you notice first line with "Louis Nielsen". It works in yours but only for a few in my setup?
What can be wrong?


Best...Jan

 

[maabadi]

Check Post #11 and compare my formula with yours at Cell E4