Tax Workbook v.7

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. At the end of the fiscal year, it is submitted to the tax accountant.

---

Tax Workbook - Worksheets

Worksheets Overview

This workbook contains transaction data from primarily ANZ 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))

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 the associated code:

The software 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.

When “Tax Workbook” is first opened in Excel, security settings commonly set by organisations 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 Budget. Summary holds data pertinent to the Tax Accountant, and Budget allows analysis of personal expenses.

The worksheets supporting these preceding sheets are InvestProp, 568CreditCard, 259Checking, 822HomeLoan and 972Savings, where the naming convention for the worksheets is the last three digits of the account and the description.

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.

Investment Property

The InvestProp Worksheet contains mainly manually-entered investment property financial data, such as Loan Payments, Body Corporate fees, Property Management fees, Council Rates fees and Water.

Much of this data is also available on the 259Checking Worksheet, such as renter payments and loan repayments, and the workbook manages that particular data via that worksheet. However, Property Management information is only available via PDF and thus manually-entered in.

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

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.

Home Loan

The Access 822HomeLoan Worksheet is a date-sorted, formatted Home Loan Account transaction worksheet. Seabrae - primary residence - home-loan data goes 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 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 traansactions.

This is still under refinement.

Budget

Stub - to be updated.

---

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()’).

Also avaliable…

mkdir MyDir

Worksheet Functions

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

---

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:

---

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