Transaction Tracker v.9

Introduction

This Excel-based utility - formerly the Tax Workbook - is designed to manage creating and maintaining Banking Transactions Data for Tax Purposes and Budget Management in spreadsheet form. The workbook contains transaction data imported from CSV files downloaded from the user’s banking institution, which are then categorised to enable revenue and expense tracking.

This Excel-based approach replaces the paper ledger. It was originally designed to identify tax-data figures: the tax accountant requested an income and expenditures summary worksheet in Excel. Using the power of Excel and VBA, data from downloaded CSV files populated the Accounts Worksheets, which informed fields on the Summary Sheet through links: this happened dynamically. In this version, the Summary worksheet has been removed as it is no longer needed, replaced by budgeting worksheets.

This Transaction Tracker is a living document, both from a data as well as a VBA standpoint. As income-and-expenses pictures change over time, this document - version 0.9 - is a much simplified version to previous versions.

---

Transaction Tracker - Worksheets

Worksheets Overview

When Transaction Tracker is first opened in Excel, security settings - commonly set by organisations to prevent malicious code from being executed - may issue a security warning:

• Some active content has been disabled. Click for more details.

Next to this warning is a button labelled [Enable Content]. While the spreadsheet’s data can be viewed without “enabling content”, the menu and other features in this workbook will only run if the content is enabled.

This only needs to be done the first time that particular workbook is opened.

The workbook is made up of several worksheets, some of which contain links to other worksheets. The main account worksheets (data) represent a checking account, creditcard account, and a personal savings account.

The analysis worksheets are TSy and the Overview sheets. There are also sheets of analysis by Quarter (Qtr1-4 and Bud-Q1-4).

The data worksheets - Checking, CreditCard and Savings - use a naming convention of the last three digits of the account and the description. The data is categorised through codes, seen in this example:

Process Overview

This workbook assumes three accounts: Checking, Savings and CreditCard. The process is as follows:

1. The data to populate the worksheets is downloaded from the banking institution’s website via CSV file, ideally saved into a folder labelled with the download date. The files could be named using the last 3 digits of the account name. Depending on how frequently this is done, one may wish to download 60, 90 or even 120 days worth of data to ensure no transactions are missed.

2. In the ribbon, select Data, then Get Data -> From Text/CSV. Each CSV file is opened in (imported into) Excel. After sorting the data from oldest to newest, the transactions that have not yet been copied to the corresponding worksheet are copied over using Copy Special: Values. Once the data has been copied, the CSV sheet can be deleted as well as the connection that was formed by Excel, as it is no longer needed.

3. In order to preserve the original bank-generated transaction information, the transaction list is cut from column C and pasted into Column G.

4. Transaction Tracker -> Click on the menu item respresenting that account, i.e., Checking, CredCard or Savings. The transactions are identified - if they exist in the AList worksheet.

5. Any transactions not in the AList worksheet needs to be categorised by selecting the uncategorised Transaction Target fields and copying that list to the bottom of the AList column A. The approach to take is outlined here: Creating Categories for Transactions. Once the new entries have been given categories, step 4 above is repeated, until all transactions have been categorised.

---

Checking

The Checking Worksheet is a date-sorted, formatted Checking Account transaction worksheet. Typically, payroll, home-loan and investment property transactions, as well as certain utilities payments were recorded in this account. After the data is imported from a CSV file downloaded from the bank’s website and sorted oldest to newest, the most recent records are appended to the existing data. Prior to processing, the Transaction Target entries are moved to column G.

The columns are as follows:

A - Date
B - Transaction Amount
C - Processed Transaction Target (Payee or Payor)
D - Transaction Code
E - (Empty)
F - (Empty)
G - Original Transaction Target (moved from column C)

After the transactions have been copied, the Code Assignment script is invoked:

At the end of processing, the user can determine if any entries lack a transaction code at the top-right of the worksheet, in column I. If the number is greater than 0, one or more transaction still needs categorising. During processing, the transaction target - column C - is given a standard description, leaving either a ‘FROM’ or ‘TO’ prefix if appropriate, and the appropriate code is entered for the transaction in column D.

---

Credit Card

The CreditCard Worksheet is a date-sorted, formatted Credit Card transaction worksheet. The data is imported from a CSV file, sorted oldest to newest and the most recent records are appended to the existing data.

The columns are as follows:

A - Date
B - Transaction Amount
C - Processed Transaction Target (Payee or Payor)
D - Transaction Code
E - (Empty)
F - (Empty)
G - Original Transaction Target (moved from column C)

After the transactions have been appended, the Code Assignment script is invoked:

The script compares transaction target entries to those in the ‘AList’ worksheet, leaving the Description column [C] and Code column [D] blank if it doesn’t find that tranaction. With time, ninety percent or more transactions are going to be with the same vendors, so it’s a simple matter of updating ‘AList’ and re-running the script.

For example, this is a typical credit card transaction listing:

Listing without codes
06/12/2022	-$80.35	7-ELEVEN 4203 VICTORIA POIN
06/12/2022	-$11.00	7 ELEVEN 4297 REDLAND BAY
06/12/2022	-$22.45	REDLAND BAY NEWS REDLAND BAY
07/12/2022	-$51.25	WOOLWORTHS/CNR COLBURNE A VICTORIA PNT
07/12/2022	-$21.50	SQ VILLAGE CREATIVE S Wellington Po
07/12/2022	-$45.65	LINCRAFT AUSTRALIA PTY VICTORIA POIN

After the codes are assigned:

Listing with codes
06/12/2022	-$80.35	7-ELEVEN 4203 VICTORIA POIN	DLXALI
06/12/2022	-$11.00	7 ELEVEN 4297 REDLAND BAY	DLXALI
06/12/2022	-$22.45	REDLAND BAY NEWS REDLAND BAY	HSHOTH
07/12/2022	-$51.25	WOOLWORTHS/CNR COLBURNE A VICTORIA PNT	DLXALI
07/12/2022	-$21.50	SQ VILLAGE CREATIVE S Wellington Po	OTHHOJ
07/12/2022	-$45.65	LINCRAFT AUSTRALIA PTY VICTORIA POIN	OTHHOJ

At the end of processing, the user can determine if any entries lack a transaction code at the top-right of the worksheet, in column I. If the number is greater than 0, one or more transaction still needs categorising.

---

Savings

The Savings Worksheet is a date-sorted, formatted Savings Account transaction worksheet. The data is imported from a CSV file, sorted oldest to newest and the most recent records are appended to the existing data. Prior to processing, the Transaction Target entries are moved to column G.

The columns are as follows:

A - Date
B - Transaction Amount
C - Processed Transaction Target (Payee or Payor)
D - Transaction Code
E - (Empty)
F - (Empty)
G - Original Transaction Target (moved from column C)

After the transactions have been copied, the Code Assignment script is invoked:

At the end of processing, the user can determine if any entries lack a transaction code at the top-right of the worksheet, in column I. If the number is greater than 0, one or more transaction still needs categorising.

---

AList

The AList Worksheet is sorted by Column C - the codes column - and then column A - the transactions column. Note: sorting this worksheet is not strictly necessary: I do it because I’m pedantic and sometimes search for stuff visually. Sorting makes stuff easier to find. The function that assigns transaction codes refers to this worksheet.

---

Budget Worksheets

See calcABS for an insight as to how quarterly budget sheets are maintained. This bit of the worksheets are still being tweaked, so documentation will happen closer to the final inception.

---

The Code Running The Workbook

Processing Code

For those who might be curious as to how certain features work, I offer the following as a bit of a discussion on my approach to solving these little problems. My code is quite basic and of course open-source: anyone can modify it to suit their needs. To view the code, press [Alt] [F11] and you will be presented with the Visual Basic Editor. These user-defined functions and sub-routines - including those used on the worksheets - are stored in the MdlT modules page.

Some of this code is still under development, and doesn’t quite do as I’d like it to.

Ribbon Code

The Transaction Tracker ribbon buttons code is stored under the MSO Ribbon Code and Callback Buttons Actions sections. When the button is clicked in the Transaction Tracker menu item, it first fires the corresponding code in the MSO Ribbon Code section, which calls the RunScript sub-routine with the value it receives from the CheckFilterMode function as well as the sheet number the button is associated with - 1: Checking / 2: Creditcard / 3: Savings - sent as parameters (arguments). The CheckFilterMode function checks that no filters have been set on that sheet. The code cannot run against a sheet with filters set. If no filters have been set, then the TxnCodes function is called, with the sheet number sent as parameter.

Using the menu option “CredCard” in the ribbon under Transaction Tracker as an example:

invokes the _Click() event for that button, which runs the following code:

'Callback for btnCreditCard onAction
Sub btnCC_Click(control As IRibbonControl)
  Call RunScript(CheckFilterMode(2), 2)
End Sub

In the process of calling RunScript, CheckFilterMode is called first with the sheet number as parameter. That function checks to see if a filter was set on that worksheet, and returns the filter status, which status is sent as first parameter, along with the sheet number. RunScript checks the filter status and if it is set the user is notified and processing is aborted.

Barring a set filter, RunScript calls the TxnCodes routine with the sheet number as parameter:

Public Sub RunScript(FilterSet As Boolean, sShtNo As Integer)
  If sShtNo = 100 Then Call ChecksOS:  Exit Sub

  If FilterSet Then
    MsgBox ThisWorkbook.collMsgs("msgFilter")
  Else
    Call TxnCodes(sShtNo)
  End If
End Sub

Note: you may see pre-processor directives - stuff preceded by a ‘#’ - that’s for debugging and doesn’t really affect the code itself.

The first line of the RunScript sub actually calls Help - this document you are reading - via the browser. If the number 100 is sent instead of 1, 2 or 3, the CheckOS function is called:

Public Function ChecksOS()

  '  first 3 chars will be either Win or Mac
  Dim sOS As String
  sOS = Mid(Application.OperatingSystem, 1, 3)

  Dim sLink As String
  sLink = ThisWorkbook.collMsgs("linkHelp")

  If sOS = "Win" Then
    Call bOpenInEdge(sLink)
  End If
  If sOS = "Mac" Then
    MsgBox ThisWorkbook.collMsgs("msgHelpLink")
  End If
End Function

Office behaves differently in Windows than on the Mac, so I had to provide some - rather awkward - solution for Mac users:

Public Function bOpenInEdge(sURL As String) As Boolean
Dim sFullURL As String
  sFullURL = ThisWorkbook.collMsgs("linkURL") & sURL
  CreateObject("Shell.Application").ShellExecute sFullURL
End Function

In Windows, clicking on Help will bring up the Edge browser and these pages. On the Mac, I provide the link to this help file in a messagebox. {{{sigh}}} Sometimes the magic works.

Process that Worksheet

In the PROCESS WORKSHEETS section is the main code that assigns category codes to transactions. Generally, the contents of the AList worksheet are dumped into an array, and then column G of the target worksheet gets stepped through row by row. If a code already exists for that transaction in column D, that row is ignored. If the code is missing, then the entry in column G is run through the array until it finds a match, using the magic of regular expressions. Columns C and D are then given the values of that array item, and the process is repeated for the next row.

Here’s the code:

Public Function TxnCodes(nShtN As Integer)
  Dim regEx As regExp
  Set regEx = New regExp

  Dim sREPattern As String
  With regEx
    .Global = True
    .IgnoreCase = True
    sREPattern = "a-zA-Z"
  End With

  Dim rWorksheetRng As Range
  Dim sEntry As String, sCode As String
  Dim i As Integer, j As Integer

  Set rWorksheetRng = Worksheets(nShtN).Range("A1").CurrentRegion

  Dim arREList() As Variant
  arREList = shAList.Range("A1").CurrentRegion

  For j = 1 To UBound(arREList)
    regEx.Pattern = arREList(j, 1)

    For i = 2 To rWorksheetRng.Rows.Count

      If Len(rWorksheetRng(i, "D").Value) > 0 Then
        ' Do nothing
      Else
        sEntry = UCase(rWorksheetRng(i, "G").Value)
        If regEx.Test(sEntry) = True Then

          Worksheets(nShtN).Range("C" & Trim(Str(i))).Value = arREList(j, 2)
          Worksheets(nShtN).Range("D" & Trim(Str(i))).Value = arREList(j, 3)

        End If
      End If
    Next i
  Next j

End Function

Note: the author realises that this is certainly not the fastest or most efficient mechanism ever designed, but it works, and it’s reasonably fast. The use of an array and regular expressions ensures some improvements on processing speed.

Worksheet UDFs

A UDF is a ‘user-defined function’: it can be used in place of Excel’s own functions. Transaction tallies for a given category - based on Transaction Category Codes - are usually done using Excel’s own SUMIF() function… for example:

=ABS(SUMIF('568CreditCard'!$D165:$D399,"HSHPHO",'568CreditCard'!$B165:$B399))

Note: the use of SUMIF() has been largely replaced with a User-Defined Function (UDF) called calcABS(), particularly on the Budget worksheets. See calcABS for how this works.

Using the CreditCard worksheet as an example, the codes in column D are assigned via Excel’s Visual-Basic for Applications functions (VBA), copied from the AList Worksheet which has an instance of the Transaction Target and an associated code:

The script that copies the Transaction Code from the AList sheet to the CreditCard sheet is run from Transaction Tracker menu option under the main menu, i.e., the ‘Ribbon’:

Much of the workbook’s data is managed in this fashion.

calcABS

This function is used on the TypeSummary worksheet as well as the Quarterly budget sheets (Qtr1, Qtr2, etc).

Public Function calcABS(sSht As String, tCode As String, _
                        nRangeS As Integer, nRangeE As Integer) As Double
Dim sRangeD As String, sRangeB As String

        sRangeD = "D" & stp(nRangeS) & ":D" & stp(nRangeE)
        sRangeB = "B" & stp(nRangeS) & ":B" & stp(nRangeE)

        Select Case sSht
                Case Is = "ch"
                        calcABS = Abs(WorksheetFunction.SumIf(sh259.Range(sRangeD), tCode, sh259.Range(sRangeB)))
                Case Is = "cc"
                        calcABS = Abs(WorksheetFunction.SumIf(sh568.Range(sRangeD), tCode, sh568.Range(sRangeB)))
                Case Is = "sv"
                        calcABS = Abs(WorksheetFunction.SumIf(sh972.Range(sRangeD), tCode, sh972.Range(sRangeB)))
        End Select
End Function

This is typically called thus (e.g., for ‘Phone/Internet’):

=calcABS("ch","HSHPHO",E13,E14) + calcABS("cc","HSHPHO",E15,E16) + calcABS("sv","HSHPHO",E17,E18)

calcABS() replaced the following formulae:

=ABS(SUMIF('568CreditCard'!$D165:$D399,"HSHPHO",'568CreditCard'!$B165:$B399) + SUMIF('259Checking'!$D$65:$D159,"HSHPHO",'259Checking'!$B$65:$B159))

This approach expects the following table on the Qtr1 (and subsequent quarters) worksheet in a specific range - D13:E18 - and formatted in this manner:

Field Ranges
Checking Start	2
Checking End	64
CreditCard Start	2
CCreditCard End	164
Savings Start	2
Savings End	66

The ‘Start’ and ‘End’ numbers are references to the beginning and end of that quarter’s date range on that particular worksheet, e.g., 01/07/2024 - 30/09/2024 (using the normal DD/MM/YYYY format used everywhere BBUT in the US)

Maintenance is easy, if a bit kludgy: the following quarter, a copy is made of the current worksheet, the copy is renamed using ‘Qtr’ and {number of quarter}, i.e., Qtr2, and the table is updated thus:

Field Ranges
Checking Start	65
Checking End	509
CreditCard Start	165
CCreditCard End	519
Savings Start	67
Savings End	529

The start numbers are, of course, last quarter’s end number incremented by 1, and the end numbers for the last quarter’s date range - (e.g., 509, 519, 529) - are just arbitrarily assigned. At the end of the last month of the quarter, when the actual worksheet’s last cell row number is known, that number is entered. Then, a copy is made of that worksheet and the process is repeated.

The advantage to this approach is that formula errors are avoided, or far more easily identifed and fixed. It also removes the tedium of having to go into each formula:

=ABS(SUMIF('568CreditCard'!$D165:$D399,"HSHPHO",'568CreditCard'!$B165:$B399)

and changing range values manually; again, another frequent source of error.

The last word for this process remains to be said.

---

Additional Notes

Creating Categories for Transactions

To categorise transactions, one needs to develop a naming scheme that can work consistently throughout the worksheets. Creating too many categories will lead to tedium, so avoid being too granular. Early days, I came up with this approach:

3-char base code prefix & 3-char refinement suffix
DLX	-ALC	DLXALC	Daily Living expenses & Alcohol
DLX	-ALI	DLXALI	Daily Living expenses & Alimentation
DLX	-DIN	DLXDIN	Daily Living expenses & Dining Out
DLX	-ENT	DLXENT	Daily Living expenses & Entertainment
DLX	-HAI	DLXHAI	Daily Living expenses & Hair/Nails
HSH	-FUR	HSHFUR	Household & Furnishings
HSH	-ITX	HSHITX	Household & IT Expenses
HSH	-MAI	HSHMAI	Household & Maintenance

Some may consider this too lost-in-the-details, some may find it’s not detailed enough. The reader may wish to be more granular than this. I would caution that doing so can render the categorising process more difficult in the end. Planning this process carefully will ultimately make maintaining the workbook significantly less tedious. The above approach could be used for expenses, whilst revenue could use an “REV” prefix:

3-char base code prefix & 5-char refinement suffix
REV	-REFND	REVREFND	Revenue: Refunds
REV	-AUPEN	REVAUPEN	Revenue: Australian Pension

Having only revenue with “REV” as prefix and 5-digit suffixes make that transaction quick and easy to identify in a worksheet.These are, of course, all only suggestions. No doubt the user will come up with an infinitely cleverer scheme.

Dealing with PayPal Entries

The approach to dealing with low-information bank transaction entries involves, unfortunately, adding text to each entry so that the script has an identifier to work with. A typical statement entry would look like this:

Date Amount Transaction description
03/10/2024	$41.94	PAYMENT TO PAYPAL AUSTRALIA 1037326410727

There is very little to go on, here. One approach is to go into saved emails from PayPal and find the record by date. Logging into PayPal itself can be useful if too much time hasn’t elapsed since the date of transaction.

After establishing the vendor and, hopefully, the purpose of the transaction, it would be prudent to keep your note fairly generic. I append the following string to the statement for the script to find (and to keep me informed as well):

Date Amount Transaction description
03/10/2024	$41.94	PAYMENT TO PAYPAL AUSTRALIA 1037326410727 [Attire: Jeans-Ebay]

“ATTIRE:” is added to the AList worksheet if not already there… the “Jeans-Ebay” bit is for me to remember what it was I purchased. Notice it can be in mixed case: RegEx is really good that way finding stuff.

Too good, one may say. Being too generic can result in mis-identification. For example, the word “PAYMENT” as an entry in AList will have the script look at the entry above and the one below:

Date Amount Transaction description
11/06/2024	$6502.88	PAYMENT THANKYOU 123456789080

(which is an automated bank transfer to early-pay the credit card) and will mis-identify either one or the other. So, the entry in AList for the Credit Card payment will be: PAYMENT THANKYOU, in order to avoid any ambiguity.

Quick Note on Savings

New for version 0.8

Many transactions happen through Paypal. These are extremely difficult to keep tabs on, as the entry in the worksheet is a mere:

PAYMENT TO PAYPAL AUSTRALIA 1021851015462

To identify the transaction prior to running the script to assign categories involves opening GMail, PayPal and Ebay -> Purchase History. Once it has been established who the payment went to and what for, I prefix the entry with a cryptic identifier (e.g., SSD) followed by a ‘-’, then include a categorised entry in the AList worksheet.

Strongly recommended is to NOT let too much time pass between workbook updating. Here in Oz, some vendor’s entries on your bank statement are like: “ZQRT & Sons PTY LTD”. Doing a google search reveals nothing more than they exist - you have no idea what name they are trading under.

---

Licence Agreement

GNU General Public Licence v3

Terms of General Public Licence, version 3:

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public Licence as published by the Free Software Foundation, either version 3 of the Licence, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public Licence for more details.

You should have received a copy of the GNU General Public Licence along with this programme. If not, see:

Full GNU GPL (you will need online access to view this document).

How to Contact the Developer

Whilst software developers endeavour to write clean, robust software with consistent, predictable behaviour, bugs do happen. The TaxWorkbook developer is committed to supporting this workbook, hence this document and a web presence on github.

A number of means exist to contact the developer:

• gmail: robinseahahn@gmail.com

• github issues page

To find the most current documentation - a (probably) updated version of this document which is most likely this one - go to:

• my Github TaxWorkbook page