This article is the continuation of a project to construct a simple check register in REALbasic (RB). The application through Part 3 can be downloaded here:
http://www.wap.org/journal/realbasic/
At the end of the last article I had completed the bare bones check register. This article will demonstrate how to add two useful functions to make the application really practical:
But first, I need to do some housekeeping.
I'm going to add a few methods to transWindow that I will use to accommodate the features that I am adding now and will add in the future. First I'll add a method named initialize with the following code:
today = new date
getDataFile
setSystemYear
I'll change the only code in the application's Open Event Handler to read:
initialize
Two new properties, today As Date and systemYear As Integer, will be added to the GlobalFinancial module and this setSystemYear method will be added to transWindow:
// determine the year for the register
dim s As string
dim d As date
dim d1 As new Date
s = TransWindow.transList.cell(0, 1)
If parseDate(s, d) then
systemYear = d.year + 1
End
// See if New Year has passed
if d1.year > systemYear then
if testForComplete then MsgBox "All
checks for last year have cleared. You can execute the End of Year Archive."
Else
Return
End
I've introduced a couple of new things in this method that will run when the Check Register opens. Notice that I've declared d and d1 as dates and, at the same time, given d1 a value. A new date takes the value of the date and time at which it was created, so d1 is set to the date and time the application opens. Next, the function parseDate takes the string s, converts it to a date object represented by d, and returns True if s represents a valid date. Since s is set to the date in the "Brought Forward" line of transList, d.year + 1 is the year of the Check Register and is assigned to the integer systemYear. Then, since d1 is today's date, if d1.year is greater than systemYear we're now into the new year and the register would be eligible to be archived, as long as all the checks for the year have cleared.
So, I immediately test that notion with the function testForComplete. If the function returns true then a message box, shown in Figure 1, alerts me that End-of-Year archiving can proceed.
// See if all transactions cleared for the year
dim i As Integer
dim d As date
allCleared = True
for i = 0 to ntransactions - 1
if ParseDate(transList.Cell(i, 1), d) then
if d.year = systemYear and not translist.CellCheck(i,
3) then
allCleared
= False
exit
end
End
Next
Return allCleared
Figure 1
The property allCleared has been defined as a boolean (it’s either
True or False) and is initially set True. Every transaction is then examined,
and if its year equals systemYear and it hasn't cleared, allCleared
is set to False and the For...Next loop exits. Otherwise, allCleared remains
True. The function returns the value of allCleared and this value will be
used later.
You may recall that the method writeDataFile was originally written (in Part 1) to write to one file. Now that I want to create an archive, writeDataFile must be modified to allow various numbers of check records to be written to other locations. This is an easy fix; I'll merely change writeDataFile to accept an integer and a FolderItem when it is called, thereby passing the number of records to be written and the file name to be saved. The revised method follows:
// Receive the integer n & FolderItem f
dim o As TextOutputStream = f.CreateTextFile // Provide path to
file
// Count through all records on transList,
// get them formatted by sendTransRow,
// then write each record to disk
dim i As Integer
for i = 0 to n // Count through all records
o.WriteLine(sendTransRow(i)) // Write each formatted
record to disk
Next
o.Close // Close file handler
listHasChanged = False // Reset flag
I've found that doing a reconciliation at the end of each month by hand is time-consuming and fraught with errors, and for many people it is so much trouble that they just take the bank's word for how much is in their accounts. Having the ability to reconcile automatically was one of my initial reasons for writing this Check Register.
Before beginning I will add some things to the GlobalFinancial module, a Property, bbal, representing my calculation of the bank's balance and a method, findBankBalance, which will execute that calculation. I'm placing these things in the global module because I'll be using them in other parts of the Check Register later. The code for findBankBalance:
dim i As integer
bbal = StrToVal(transwindow.transList.Cell(0,6))
for i = 1 to ntransactions - 1
if transwindow.transList.CellCheck(i, 3) = true then //cleared
transaction
bbal = bbal - StrToVal(transwindow.transList.Cell(i,
4)) + StrToVal(transwindow.transList.Cell(i, 5))
end if
next
This code assigns bbal to the initial Brought Forward balance, then
steps through all transactions and, if the transaction is marked as cleared,
adjusts bbal appropriately. I will add the findBankBalance method
call to transWindow's new initialize method discussed previously.
The next step is to create a new window named “Reconcile,” shown
in Figure 2. At the top is an editBox (named statementBal) for entering
the bank statement's ending total. Its Open Event Handler is the single line:
me.setfocus
which insures that the cursor is located in this box when the window opens.
Figure 2
The number group to the right of "Calculated Bank Balance" is plain text which will be initialized upon opening to bbal defined above:
me.text=Format(bbal,"-#,###.00")
The number group to the right of "Out of Balance" is also plain text. Upon opening, it will be initialized to the difference between the value of bbal and the value of statementBal.
me.text= Format(bbal-val(statementBal.text),"-#,###.00")
The listBox (named reconList) will display all uncleared transactions. Its Open Event Handler is:
dim i As integer
me.columnWidths = "90,80,280,100,0"
me.ColumnAlignment(0) = 2 //center alignment
me.ColumnAlignment(1) = 2
me.ColumnAlignment(2) = 1 //left
me.ColumnType(2) = 2 //checkbox
me.ColumnAlignment(3) = 3 //right
me.ColumnAlignment(4) = 3 //right alignment
For i = 1 to ntransactions - 1
If TransWindow.TransList.CellCheck(i, 3) = false then
me.addrow TransWindow.TransList.Cell(i, 1)
If val(TransWindow.TransList.Cell(i, 2)) <> 0
then //check nr
me.Cell(nRecon, 1) = TransWindow.TransList.Cell(i,
2)
elseif val(TransWindow.TransList.Cell(i, 5)) > 0
then
me.Cell(nRecon, 1) = "dep" //
deposit transactions
End if
me.Cell(nRecon, 2) = TransWindow.TransList.Cell(i,
3) // Description
If val(TransWindow.TransList.Cell(i, 4)) <> 0
then
me.Cell(nRecon, 3) = "-" +
TransWindow.TransList.Cell(i, 4)
elseif val(TransWindow.TransList.Cell(i, 5)) <> 0
then
me.Cell(nRecon, 3) = TransWindow.TransList.Cell(i,
5)
me.cellbold(nRecon, 3) = true
End if
me.Cell(nRecon, 4) = str(i) // transaction
#
nRecon = nRecon +1
End
next
reconBbal = bbal
The terms nRecon and reconBbal have been defined as integers in the Reconcile window properties list. This code is similar to the code in Part 1 which loads transWindow transList. It first specifies the columns' configuration, then polls through each transaction in transList; if the transaction has been cleared (indicated by the check box), it adds the transaction to reconList and increments nRecon. Notice the line with the comment "transaction #." That command stores the counter i in column 4, which is not visible since its width is set to zero. That value, which represents the record number in transWindow transList, will be used later.
One additional graphic touch is added with the reconList CellTextPaint Handler:
dim cellData as string
cellData = me.cell(row,column)
if column = 3 and val(cellData) < 0 then
g.foreColor = rgb(255,0,0)
elseif column = 3 then
g.foreColor=RGB(27,137,8)
end
This examines each entry: if the column = 3 (the Amount column) and the value is less than zero the entry is painted red. Otherwise, if the column = 3 the value must be greater than zero so it is painted green. Obviously this doesn't show in your Journal; you'll have to download the application to see it.
All transactions in the bank statement that have cleared are checked in reconList. When each record is checked, the Out of Balance reading will change to reflect the current out-of-balance state until at the end -- we hope -- it will read zero. This is accomplished by the CellAction Handler.
dim i As integer
reconBbal = Bbal
for i=0 to nRecon - 1
if me.CellCheck(i, 2) = true then
reconBbal = reconBbal+StrToVal(me.Cell(i, 3))
end if
next
BankBal.text = Format(reconBbal,"-#,###.00")
OutOfBal.text = Format(reconBbal-val(StatementBal.text),"-#,###.00")
This code fires when a checkbox is checked in a record of reconList. When this is done all the records are examined; if the column 2 check box is True (checked), reconBal is recalculated and thus the Calculated Bank Balance and Out of Balance displays are updated.
The last two controls in the Reconcile window are the Cancel and Done buttons. The Cancel simply closes the window with no further action:
self.close
The Done button does this:
dim i,j As integer
for i = 0 to nRecon - 1
if ReconList.CellCheck(i, 2) = true then
j = val(ReconList.cell(i, 4)) // transList index
transWindow.transList.cellcheck(j, 3) = true
transWindow.transList.cell(j, 7) = "x"
end if
next
FindBankBalance
ListHasChanged = True
self.close
It polls each record in reconLis,t and if the associated check-box is true, it looks at the invisibly stored record number in column 4 and marks the associated check box in the transWindow transList record. It updates the bank balance, sets the flag showing data has been changed, and closes the window.
Now I'll add a new Menu to the Menu Bar called Special and within that, a new Menu Item (Reconcile...). These are added in just the same way a Transaction Menu and its Menu Items were added in Part 3.
The last detail is accomplished in transWindow. As I did in Part 3, I click on the Add Menu Handler button, select SpecialReconcile in the popup menu, and add the following:
Reconcile.Show
This opens the Reconcile window and this step completes the addition of a reconcile function.
In order to close out the year and carefully save the prior year's transactions after the first of the year, I will add some functions which will:
This time I'll begin by adding a new Menu Item (End of Year Archive) to the Special Menu. It will be named “SpecialEOY.” However, I want that menu to be greyed out until the new year has arrived and all transactions for the systemYear have been cleared. Remember that the property allCleared, discussed earlier, was set True by exactly these conditions, so I'll use that variable to activate the SpecialEOY Menu Item. This is done in one of the transWindow Event Handlers called EnableMenuItems. I won't bore you with all the times that this handler is polled, but one of these times is when the operator clicks on a menu. Therefore, the following will execute to enable specialEOY whenever the Special menu is clicked. I'm also adding a test for the boolean variable, listHasChanged, to control the File-Save and File-Save & Quit menus.
if allCleared then
SpecialEOY.enabled = True
Else
SpecialEOY.enabled =False
End
if listHasChanged Then
FileSave.Enabled = True
FileSaveQuit.Enabled = True
Else
FileSave.Enabled = False
FileSaveQuit.Enabled = False
End
So now I have the Menu Item, specialEOY, that will be enabled whenever the conditions are appropriate; its Menu Handler looks like this, to accomplish the steps listed above:
dim i, nLastTransaction As Integer
dim s, eoyBalance As Double
dim lastYear As new Date
dim newYear As new Date
dim dlg As saveAsDialog
lastYear.year = systemYear
lastyear.month = 12
lastyear.day = 31
s = lastyear.totalSeconds
newYear.month = 1
newYear.day = 1
for i = 1 to ntransactions - 1 //find last transaction of lastYear
if val(translist.cell(i, 0)) >= s then //its in
new year
nLastTransaction = i - 1
exit
End
Next
eoyBalance = StrtoVal(translist.cell(nLastTransaction,6))
// Save old year's records
newYearFolder // Make new folder in Archives if it doesn't exist
dlg = new saveAsDialog
dlg.promptText = "Select Folder and Name for last year's data archive"
// point to archives folder for past year
dlg.initialDirectory = DocumentsFolder.child("Archives1").child(str(systemYear))
dlg.filter = "text"
dlg.suggestedFileName = "Checkbook Data " + str(systemYear)
fileArchive = dlg.showModalWithin(self)
if fileArchive = nil then //Cancel pressed
MsgBox "End of Year archiving is cancelled"
return(false)
end
WriteDataFile(fileArchive)
//establish new year's records
TransList.cell(0, 6) = Format(eoyBalance, "-#,###.00")
TransWindow.TransList.Cell(0, 0) = Format(s, "#")
TransWindow.TransList.Cell(0, 1) = lastyear.shortdate
systemYear = newyear.year
//remove old records and write updated data
for i = nlastTransaction downto 1
TransList.removerow i
next
nTransactions = transList.listcount
FindBalance
FindBankBalance
WriteDataFile(nTransactions - 1, GetFolderItem("Checkbook Data"))
EnableMenuItems // disable EOY menu
This one's rather long, but all the steps are really quite clear. First I declare two dates, lastYear and newYear. I then define lastYear as December 31 and newYear as January 1. Now I set s to the totalSeconds of lastYear. If you recall, in Part 1, I talked about totalSeconds (the number of seconds since January 1.1904). You'll recall, when the program loads the data file, each transaction's totalSeconds is stored in column 0 of transWindow transList. The only way RB can compare dates is by using totalSeconds, so I step through all the transactions to find the first one where totalSeconds is greater than s. This isthe first one of newYear. Subtracting one from the counter defines the index of the last transaction of lastYear. Using that index, I set the end-of-year balance -- eoyBalance.
The method newYearFolder makes a new folder to archive last year's records. I'll examine newYearFolder in detail later.
The next series of commands customizes a new Save As dialog box with the suggested name for the archived file. The variable fileArchive is defined as a FolderItem so the command: fileArchive = dlg.showModalWithin(self)makes fileArchive equal to the result of the dialog box. ModalWithin(self) presents a dialog within transWindow and prevents clicking in another Check Register window until the dialog is cleared by clicking in Cancel or OK. Once that is done, either the archive is cancelled or all of last year's records are written to the new archive file.
The term eoyBalance is written into the Brought Forward cell, and the new year totalSeconds and date are written to that record.
Next, each of last year's records is deleted and the counter nTransactions is reset to the actual number of records remaining. Finally, balances are recomputed, the remaining records are written to the Checkbook file, and the call to EnableMenuItems disables the specialEOY menu item.
The method newYearFolder is written as follows:
// create an Archives folder and a Year folder within
dim f As folderItem
dim year As string
year = str(systemYear)
f = DocumentsFolder.child("Archives1")
if not f.exists then f.createAsFolder
f = DocumentsFolder.child("Archives1").child(year)
if not f.exists then f.createAsFolder
This first tests whether there is a folder within Documents named Archives,1 and if not, it creates one. It then tests for a folder with the name of the systemYear within Archives1, and again it creates one if necessary. When the Save As dialog is called by SpecialEOY above, it points to this folder. NOTE: If you download the application and run SpecialEOY it will create these folders in your Documents folder.
This completes the addition of statement reconciliation and end-of-year archiving to the Check Register. Future articles will show you how to add functionality that will let you:
The compiled application to-date along with the data files and the source
code can be downloaded here:
http://www.wap.org/journal/realbasic/