Tax Workbook v.8

Savings and C-Card codes

See Quick Note on 972Savings for full explanation on the following codes:

Codes and Suffixes
di	-DiningOut
do	-Donations
en	-Entertain
hh	-HouseHold
it	-ITExpense
ra	-RobAttire
rh	-RobHobbys
ri	-Reimburse
rm	-AlMedical

For Creditcard Payments:

CreditCard Codes and Suffixes
cc	Creditcard repayment
ri	Reimbursement for purchase

---

Introduction

This Excel-based utility is designed to manage creating and maintaining Banking Transactions Data for Tax Purposes and Budget Management in spreadsheet form. The workbook contains both data inported from CSV files as well as (much less frequently) data manually entered from PDFs, such as rental property repair details.

It replaces a manually-managed ledger approach to tax-data figures. The tax accountant requested an income and expenditures summary worksheet in Excel. Using the power of Excel and VBA, downloaded CSV files populate the Accounts Worksheets, which inform fields on the Summary Sheet through links: this happens dynamically.

This Tax Workbook is a living document. As our income-and-expenses picture has dramatically changed since last year, this document - version 0.8 - is a much simplified version to 0.7. Accounts have been closed, and a greater emphasis has been placed on creating a working, viable budget. Still, we intend it to continue to serve its original purpose, so at the end of the fiscal year, it will be submitted to our tax accountant.

---

Tax Workbook - Worksheets

Worksheets Overview

When “Tax Workbook” 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 once.

The workbook is made up of several worksheets which contain links to each other. The main end-point worksheets are Summary and the Budget series, separated into Quarters. Summary holds data pertinent to the Tax Accountant, and Budget allows analysis of personal expenses to help manage a budget.

The worksheets supporting these preceding sheets are 568CreditCard, 259Checking, 972Savings and 647Beyond - the latter, soon to be closed. The naming convention for the worksheets is the last three digits of the account and the description.

This workbook contains transaction data from bank accounts. The data is categorised through codes:

Transaction amounts are tallied based on these Transaction Category Codes, a copy of which is saved in the ‘Classes’ worksheet. The tallies are SUMIF()s of transaction amounts based on the codes in column D:

=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 Credit Card worksheet as an example, the codes in column D are assigned via Excel 2019 Visual-Basic for Applications functions (VBA), copied from the Classes Worksheet which has an instance of the Transaction Target and an associated code:

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

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

The Summary Worksheet

The Summary Worksheet is comprised of two main columns; Expenses and Revenue. The expenses are only those relevant to a tax accountant, such as home loan payments and professional fees and donations, while revenue is comprised of all income.

The fields on this page are linked to specific fields on relevant account pages and update dynamically.

Checking

The Access 259Checking Worksheet is a date-sorted, formatted Checking Account transaction worksheet. Payroll, home-loan and investment property data, as well as certain utilities transactions go through this account. The data is imported from a CSV file downloaded from the bank’s website. The data is 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 - Description
F - (Empty)
G - Original Transaction Target (moved from column C)

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

During processing, non-useful text is removed from the transaction target, leaving either a ‘FROM’ or ‘TO’ prefix, making transaction identification vastly easier to do visually.

Credit Card

The 568CreditCard Worksheet is a date-sorted, formatted Credit Card transaction worksheet. The data is imported from a CSV file downloaded from the bank’s website. The data is 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 - Transaction Target (Payee or Payor)
D - Transaction Code

After the transactions have been appended and the PAYMENT transaction categorised (see Creditcard Payments), the Code Assignment software is invoked:

The script compares transaction target entries to those in the ‘Classes’ worksheet, leaving the Code column [D] blank if it doesn’t find that tranaction. 90% or more transactions are going to be with the same vendors, so it’s a simple matter of updating ‘Classes’ with the transactions missing a code.

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

Savings

The 972Savings Worksheet is a date-sorted, formatted Home Loan Account transaction worksheet. This account was mainly for personal transactions, historically. The data is imported from a CSV file downloaded from the bank’s website. The data is 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 - Description
F - (Empty)
G - Original Transaction Target (moved from column C)
H - Item or service purchase (aid in identifying PayPal transaction)

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

During processing, non-useful text is removed from the transaction target, leaving either a ‘FROM’ or ‘TO’ prefix if one existed in the original transaction target entry.

Classes

The Classes Worksheet is sorted by Column B - the codes column - and then column A - the transactions column. All worksheets that do processing refer to this sheet in order to assign codes to transactions.

This sheet and the associated processes are being reviewed for refinement and streamlining.

Budget Worksheets

See calcABS for an insight as to how quarterly budget sheets are maintained.

---

The About Form

In an effort be be somewhat cross-platform friendly, I only have one form: the “About”.

---

The Code Running The Workbook

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.

To view the code, press [Alt] [F11] and you will be presented with the Visual Basic Editor. These user-defined functions and sub-routines are stored in the MdlT modules page. The Tax Workbook ribbon buttons code is stored here.

Menu Options

Using the menu option “CreditCard” in the ribbon under Tax Workbook as an example:

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

Sub btnCC_Click(control As IRibbonControl)
  Call RunScript(CheckFilterMode("s568"), "s568")
End Sub

If a filter was set on the worksheet to be processed, the app notifies the user and processing is aborted.

Otherwise, RunScript calls the routine refered to (“s568”, “s259” or “s972”, for example - where ‘s’ stands for ‘sheet, and the numbers represent the last three digits of the account).

Public Sub RunScript(FilterSet As Boolean, ScriptToRun As String)
  If FilterSet Then
        'Debug.Print FilterSet
        MsgBox ("Cannot run whilst a filter is set...")
  Else

  ' The name of the function (e.g., HomeloanCodes() but without the "()")
  '  placed within a Messagebox calls that function (i.e., runs the code
  '  of that function)
        Select Case ScriptToRun
          Case Is = "s568": MsgBox (CreditcardCodes)
          Case Is = "s259": MsgBox (CheckingCodes)
          Case Is = "s972": MsgBox (PersonalACodes)
          Case Is = "s647": MsgBox (BeyondBankCodes)
          Case Is = "About": MsgBox "under Development"
        End Select
  End If
End Sub

The MsgBox() function actually calls (launches) the script (e.g., ‘CreditcardCodes()’).

Worksheet Functions

Note: the first function mentioned here, calcABS(), has much simplified maintaining and updating the budget worksheets. The second - sParseAllWords({cellnumber}) - needs work.

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.

Maintenance is easy: 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.

sParseAllWords({cellnumber})

Note, as of 2203-01-06, this function is being reviewed and revised.

Public Function ParseAllWords(StringToParse As String) As String
        If Len(StringToParse & vbNullString) = 0 Then Exit Function

        If Trim(InStr(1, StringToParse, " ")) = 0 Then
                ParseAllWords = Trim(StringToParse)
        Else
                ParseAllWords = WorksheetFunction.Trim(StringToParse)
        End If
End Function

Creditcard Payments

For the credit card, the app looks at the entry for “PAYMENT” - the only fairly ambiguous entries in this worksheet - and then checks the two-character code after the word “THANKYOU”… this code is entered prior to processing:

Codes and Suffixes
cc	Creditcard repayment
ri	Reimbursement for purchase

---

Additional Notes

Quick Note on 972Savings

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 and Ebay -> Purchases. Once it has been established who the payment went to and what for, I typically copy/paste the item - or service - purchased into column H. Column F receivs a 2-character code to identify the purchase as being of the following categories: the script then trims off the transaction number suffix and assigns a suffix to the Transaction Target based on the code I enter prior to processing in Column F:

Additional Discussion

With v.8, I have endeavoured to be somewhat cross-platform. I do most of what I do on a computer on a Macbook Pro (2015), running Windows 10 in a VM. Development of anything VBA occurs in this VM environment. However, I do need to get to the data at times on the Mac, so I load these workbooks in Excel 2016 for Mac. Help is a challenge: I can’t invoke a browser within Excel Mac like I can in Windows (to my knowledge, anyway). So, I just display the link in a MsgBox and have the Mac user copy/paste that into the browser.

Yeah, a kludge, but it works.

---

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

Currently Working on

FROM

“FROM RURAL A”: TruncSuffix = Mid(Tstr, 1, 29) “FROM QLD DEP”: TruncSuffix = Mid(Tstr, 1, 28) “FROM KHAD PT”: TruncSuffix = Mid(Tstr, 1, 28) “FROM REMITTE”: TruncSuffix = Mid(Tstr, 1, 28) “FROM PAYPAL “: TruncSuffix = Mid(Tstr, 1, 22) “FROM HANNAH “: TruncSuffix = Mid(Tstr, 1, 21) “FROM ONEPATH”: TruncSuffix = Mid(Tstr, 1, 21) “FROM 5”: TruncSuffix = Mid(Tstr, 1, 20) “FROM SPP TRU”: TruncSuffix = Mid(Tstr, 1, 18) “FROM US Covi”: TruncSuffix = Mid(Tstr, 1, 16) “FROM E338163”: TruncSuffix = Mid(Tstr, 1, 15) “FROM ATO “: TruncSuffix = Mid(Tstr, 1, 8) “PYPL PAYIN4 “: TruncSuffix = Mid(Tstr, 1, 12) “PAYPAL AUSTR”: TruncSuffix = Mid(Tstr, 1, 17)

TO

“TO TELSTRA S”: TruncSuffix = Mid(Tstr, 1, 19) “TO TMR-PRODU”: TruncSuffix = Mid(Tstr, 1, 19) “TO ANZ INSUR”: TruncSuffix = Mid(Tstr, 1, 20) “TO NRMA INSU”: TruncSuffix = Mid(Tstr, 1, 21) “TO AMBERELEC”: TruncSuffix = Mid(Tstr, 1, 21)