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]

1. I Use 2 helper columns. then Add formula.
2. I changed Home Depot to Depot because of two part name.

Bank Statements.xlsx
	A	B	C	D	E	F	G	H	I	J
2		1. Bank Statements
3		Date	amount	Statement Text	<--- Pasted from my online bank
4		1/1/2021	$42.00	Mastercard-nota $ 42,00 Mcdonalds	Mcdonalds	42,00	McDonalds	Personal spending	Fast food & Take away
5		1/4/2021	$38.00	Visa purchase $ 38,00 APCOA PARKING	PARKING	APCOA	APCOA	Transportation	Parking
6		1/6/2021	$42.00	Mastercard-nota $ 42,00 Mcdonalds	Mcdonalds	42,00	McDonalds	Personal spending	Fast food & Take away
7		2/8/2021	$27.00	Mastercard-nota $ 27,00 MCDonalds	MCDonalds	27,00	McDonalds	Personal spending	Fast food & Take away
8		3/1/2021	$22.00	Paypal: Netflix subscription	subscription	Netflix	Netflix	Other living costs	TV & Streaming
9		3/10/2021	$22.00	Paypal: Netflix subscription	subscription	Netflix	Netflix	Other living costs	TV & Streaming
10		4/14/2021	$12.00	VISA Pharmacy	Pharmacy	#VALUE!	Pharmacy	Personal spending	Doctors & Treatment
11		5/20/2021	$6.00	VISA Home Depot	Depot	Home	Depot	Housing	Furniture & housing equipment
12
13
14		2. Shop names
15		Name	Main category	Sub category	<--- Manually added
16		McDonalds	Personal spending	Fast food & Take away
17		APCOA	Transportation	Parking
18		Netflix	Other living costs	TV & Streaming
19		Pharmacy	Personal spending	Doctors & Treatment
20		Depot	Housing	Furniture & housing equipment
Sheet1

Cell Formulas
Range	Formula
E4:E11	E4	=RIGHT(D4,LEN(D4)-FIND(CHAR(160),SUBSTITUTE(D4," ",CHAR(160),LEN(D4)-LEN(SUBSTITUTE(D4," ","")))))
F4:F11	F4	=MID(D4,FIND(CHAR(160),SUBSTITUTE(D4," ",CHAR(160),LEN(D4)-1-LEN(SUBSTITUTE(D4," ","")))),FIND(CHAR(160),SUBSTITUTE(D4," ",CHAR(160),LEN(D4)-LEN(SUBSTITUTE(D4," ",""))))-FIND(CHAR(160),SUBSTITUTE(D4," ",CHAR(160),LEN(D4)-1-LEN(SUBSTITUTE(D4," ","")))))
G4:G11	G4	=IFNA(VLOOKUP(TRIM(E4),$B$15:$D$20,1,FALSE),VLOOKUP(TRIM(F4),$B$15:$D$20,1,FALSE))
H4:H11	H4	=IFNA(VLOOKUP(TRIM(E4),$B$15:$D$20,2,FALSE),VLOOKUP(TRIM(F4),$B$15:$D$20,2,FALSE))
I4:I11	I4	=IFNA(VLOOKUP(TRIM(E4),$B$15:$D$20,3,FALSE),VLOOKUP(TRIM(F4),$B$15:$D$20,3,FALSE))

 

[jklarsen]

Hi Thank you for your quick reply.
The formula works great, when the company name is the last part of the text, but most cases bank statements will look like this:

Visa purchase DKK 228,00 TORVEHALLERNES FRUGT Den 22.07
Visa purchase DKK 221,50 Hav Torvehallerne AS Den 22.07
Visa purchase DKK 140,00 THE COFFEE COLLECTIV Den 22.07
Visa purchase DKK 100,00 STIGS OLIVEN APS K Den 22.07
Visa purchase DKK 100,00 LAURAS BAKERY TORVEH Den 22.07

And then the formulas does not work
So the text will quite often be mixed into the text, also some times the shop name will need to be in two words, in case there are similar stores of different categories.

Thanks


Best...Jan

 

[maabadi]

Please upload file with more example at Free hosting site e.g. www.Dropbox.com , GoogleDrive or Onedrive and insert link here to work on it

 

[jklarsen]

Not sure how to paste in the excel file without it turning into a picture, so here is the dropbox link -

Dropbox - Error - Simplify your life

www.dropbox.com

Best...Jan

 

[maabadi]

Use this two User Defined Function. you should at VBA window insert Module and Paste code then save file as .xlsm (Macro-Enabled Workbook)
Then you can use it same as excel functions.
1. CrCell is your criteria Cell at example file you can select D4 to D17 and you should fix column Letter. $D4
2. ShopNameRange is your Shops name range at example file is B20:D31 and you should fix it $B$20:$D$31
3. Col is your column that you want return result form Shops name range
Example:

ExtractShopName($D4,$B$20:$D$31,1)

Function ExtractShopName(CrCell As Range, ShopRange As Range, Col As Long) As String
  Dim Cell As Range, Tr As String, a As Long, P1 As String, P2 As String, P3 As String, P4 As String
  Dim P5 As String, P6 As String, b As Integer
    Tr = Application.WorksheetFunction.Trim(Application.WorksheetFunction.Clean(Replace(CrCell, Chr(160), " ")))
    a = Len(Tr) - Len(Application.WorksheetFunction.Substitute(Tr, " ", ""))
For b = 1 To a
   P1 = FindN(" ", Tr, a - b + 1)
   P2 = FindN(" ", Tr, a - b + 2) - P1 - 1
       If FindN(" ", Tr, a - b + 2) = 0 Then
             P2 = Len(Tr) - P1
      End If
   P3 = Mid(Tr, P1 + 1, P2)
On Error Resume Next
   P4 = Application.WorksheetFunction.VLookup(P3, ShopRange, Col, False)
      If Err <> 0 Then
            GoTo Resum1
      End If
    GoTo Resum3
    Resum1:
 Next b
For b = 2 To a
    P1 = FindN(" ", Tr, a - b + 1)
    P2 = FindN(" ", Tr, a - b + 3) - P1 - 1
          If FindN(" ", Tr, a - b + 3) = 0 Then
              P2 = Len(Tr) - P1
         End If
   P3 = Mid(Tr, P1 + 1, P2)
On Error Resume Next
  P4 = Application.WorksheetFunction.VLookup(P3, ShopRange, Col, False)
        If Err <> 0 Then
          GoTo Resum2
       End If
Resum2:
Next b
    If P4 = "" Then
       If Col = 1 Then
           ExtractShopName = "Others"
       Else
          ExtractShopName = ""
     End If
       Exit Function
    Else
       Resum3:
       ExtractShopName = P4
   End If
End Function

Function FindN(sFindWhat As String, sInputString As String, N As Integer) As Integer
    Dim J As Integer
    Application.Volatile
    FindN = 0
    For J = 1 To N
        FindN = InStr(FindN + 1, sInputString, sFindWhat)
        If FindN = 0 Then Exit For
    Next
End Function

 

[jklarsen]

Brilliant - that works great.

There is some cases where I need to add a " " after the name for it to work, and then I can find the shop name, but it still does not find the Main and sub category afterwards.

I have copied/pasted the text directly from the bank statement into the shop names cells, so they are identical, and also formatted them all as TEXT.

Am I doing something wrong?


Best...Jan

 

[jklarsen]

I seem to have found a solution for finding the shopname from on of the other threads, by mixing and matching

=INDEX(Butiksnavne!$B$3:$B$5000;AGGREGATE(15;6;(ROW(Butiksnavne!$B$3:$B$5000)-ROW(Butiksnavne!$B$3)+1)/(ISNUMBER(SEARCH(Butiksnavne!$B$3:$B$5000;C3)));1))

So now I need to figure out how to search within months, the sum from each category and adding the sum to the corresponding cells, like this

Is there an easy way to do this?


Best...Jan

 

[maabadi]

what is formula you use for shopname, main and subcategory Exactly?
I think you don't enter formula correctly.

 

[jklarsen]

I use the formula you created and they work like a charm

=IFNA(VLOOKUP(TRIM(E3);Butiksnavne!$B$3:$D$5000;2;FALSE);VLOOKUP(TRIM(E3);Butiksnavne!$B$3:$D$500;2;FALSE))

And when using it together with the new formula, everything works fine
=INDEX(Butiksnavne!$B$3:$B$5000;AGGREGATE(15;6;(ROW(Butiksnavne!$B$3:$B$5000)-ROW(Butiksnavne!$B$3)+1)/(ISNUMBER(SEARCH(Butiksnavne!$B$3:$B$5000;C3)));1))



So now I need to figure out how to search within months, the sum from each category and adding the sum to the corresponding cells, like this

Is there an easy way to do this?


Best...Jan