Unlike others, do not spend too much time and efforts in tracking your expenses. Follow this article and build your own income and expense tracker. This tool accepts your bank statements in an Excel format and then reads it to let you know where you have spent more.
Let’s Prepare the GUI
The tool needs 3 sheets. Rename sheet1 as “Control Panel”, sheet2 as “Summary” and sheet3 as “Database”. On the sheet “Control Panel”, create a field to allow the user to browse and upload historical bank statement. To set tags for each transaction in your bank statement, you need to set keywords for each tab. As shown in the image, separate multiple keywords by a comma.
Let’s make it functional
Import the script into a new module. Attach the script “Import_Bank_Statement” to the button “Import” on the sheet “Control Panel” and the script “Update_Tags” to the button “Refresh”.
How does it work?
Add the full path of your bank statement and import it. All data from your bank statement will be loaded on the sheet “Database”. The script identifies the list of tags that you have mentioned on the sheet “Control Panel”. For each listed Tag, corresponding keywords are read into a variable and they are split using VBA’s SPLIT command. For each keyword separated by a comma, the script scans through the entire database and identifies the corresponding value for each keyword. The final and total value is then updated on the sheet “Summary” which populates the bar Graph.
Script:
Sub Import_Bank_Statement()
With Sheets("Database").QueryTables.Add(Connection:= _
"TEXT;" & Sheets("Control Panel").Range("B3").Value _
, Destination:=Sheets("Database").Range("$A$1"))
.Name = "Bank Statement"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Sub Update_Tags()
Dim lr As Long
Dim r As Long
Dim v_string() As String
Dim intcount As Long
Dim rindb As Long
Dim lrindb As Long
Dim v_total As Long
lr = Sheets("Control Panel").Range("K" & Rows.Count).End(xlUp).Row
For r = 3 To lr
v_total = 0
v_string = Split(Sheets("Control Panel").Range("L" & r).Value, ",")
For intcount = LBound(v_string) To UBound(v_string)
lrindb = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
For rindb = 2 To lrindb
If InStr(UCase(Sheets("Database").Range("B" & rindb).Value), UCase(Trim(v_string(intcount)))) <> 0 Then
v_total = v_total + Sheets("Database").Range("D" & rindb).Value
End If
Next rindb
Next intcount
MsgBox v_total
Sheets("Summary").Range("C" & r + 1).Value = v_total
Next r
End Sub
Modify it
The tool now imports a single bank statement into the database. You can modify the tool to allow the user to browse and select a folder, scan for all available bank statements and import all files into the database. The sheet “Summary” can also be modified to show tags and values for each month or week. Instead of reading the entire database, the macro can be modified to read values between specific dates.
Quick fix
If the sheet “Summary” is corrupted, you can try to fix Excel by deleting the corrupted sheet and then recreate it in the same workbook.
Author Introduction:
Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt Word and outlook recovery software products. For more information visit www.datanumen.com
Leave a Reply