Washington Apple Pi

A Community of Apple iPad, iPhone and Mac Users

Building a Check Register in REALbasic, Part 4

By Brent Malcolm

Washington Apple Pi Journal, reprint information

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.

Testing for the End-of-Year

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

Test for complete

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.

WriteDataFile Method

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

Bank Statement Reconciliation

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

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.

What do I do at the End of the Year?

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/