####################### 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. .. raw:: html
******************************** 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: .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes01.png ================ 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: :REF:`tw9-txncategoring`. Once the new entries have been given categories, step 4 above is repeated, until all transactions have been categorised. .. raw:: html
======== 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: .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes06.png 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. .. raw:: html
=========== 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: .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes04.png 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. .. raw:: html
======= 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: .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes06.png 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. .. raw:: html
===== 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. .. raw:: html
================= Budget Worksheets ================= See :REF:`tw9-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. .. raw:: html
***************************** The Code Running The Workbook ***************************** .. _tw9-corefcn: =============== 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: .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes04.png invokes the _Click() event for that button, which runs the following code: .. code-block:: vbnet '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: .. code-block:: vbnet 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: .. code-block:: vbnet 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: .. code-block:: vbnet 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: .. code-block:: vbnet 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* :ref:`tw9-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: .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes03.png 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': .. image:: https://www.tightbytes.com/images/pubimages/appmgr/tt09Codes04.png Much of the workbook's data is managed in this fashion. .. _tw9-calcABS: ------- calcABS ------- This function is used on the **TypeSummary** worksheet as well as the Quarterly budget sheets (Qtr1, Qtr2, etc). .. code-block:: vbnet 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. .. raw:: html
**************** Additional Notes **************** .. _tw9-txncategoring: ==================================== 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. .. _tw9-SavingsCodes: ===================== 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. .. raw:: html
***************** 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 `_