################
Tax Workbook v.8
################
**Savings and C-Card codes**
See :REF:`tw8-SavingsCodes` 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 |
+------+------------------------------+
.. raw:: html
************
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.
.. raw:: html
*************************
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:
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw01Codes01.png
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* :ref:`tw8-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:
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw01Codes03.png
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':
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw08Codes04.png
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:
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw08Codes06.png
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 :REF:`tw8-ccpayment`), the Code Assignment software is invoked:
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw08Codes04.png
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:
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw08Codes06.png
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 :REF:`tw8-calcABS` for an insight as to how quarterly budget sheets are maintained.
.. raw:: html
**************
The About Form
**************
In an effort be be somewhat cross-platform friendly, I only have one form: the "About".
.. raw:: html
.. _tw8-corefcn:
*****************************
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:
.. image:: https://www.tightbytes.com/images/pubimages/appmgr/tw08Codes04.png
invokes the _Click() event for that button, which runs the following code:
.. code-block:: vbnet
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).
.. code-block:: vbnet
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.
.. _tw8-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.
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*.
.. code-block:: vbnet
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
.. _tw8-ccpayment:
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 |
+------+------------------------------+
.. raw:: html
****************
Additional Notes
****************
.. _tw8-SavingsCodes:
========================
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.
.. 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 `_
====================
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)