by Dr. Peter Wayne
| Reconciling one’s checkbook with the bank’s statement can easily be the most tedious part of maintaining a checking account. I’ve been envious of the ease with which commercial money management programs such as Quicken and Money help one to balance an account, and I’ve wondered whether I could devise as simple a method for my Alpha Five chart of accounts. I decided to put my mind to it and discovered that Alpha Five has all the tools I needed. |
First, we’ll create a straightforward table to store our checkbook transactions. Here is the structure of ledger.dbf:
Figure 1. Structure of ledger.dbf
The required fields in the table include Date, Type, and Account. The Type can be either Withdrawal, Check, Fee, Interest or Deposit. New transactions will have the cleared and reconciled fields set to .F.. Event scripts in ledger.dbf keep the Balance field up-to-date as transactions are entered, changed, or deleted. We won’t concern ourselves with those scripts now because they do not pertain to the subject of this article.
We’ll make 10 entries into this table:
Figure 2. Entries in ledger.dbf
Now, let’s imagine that we have received a bank statement dated May 10, 1998. All the transactions in the account have cleared, except for the last one from April 26th. The bank statement states that the balance is $549.00. Does this match with the entries in our checkbook?
Here is the general approach we’ll take to balancing the checkbook:
Designing the form
On a blank form, I placed an embedded browse with columns for Date, Type, Reference Number, Payee, Amount, and Cleared:
Figure 3. An embedded browse is placed on a blank form.
I then right-clicked on the Browse to restrict the user from making new entries, deleting existing entries, or resizing my columns:
Figure 4. Choosing browse restrictions from the browse’s right-click card.
When I reconcile my checkbook manually, I first look at the opening balance, the closing balance, and the date of the bank statement. Then I look at all the transactions up to the date of the bank statement and check off those that have cleared through the bank. I know that the following should be true:
Closing balance=Opening balance + Cleared Deposits + Interest Credited – Cleared Checks – Cleared Withdrawals – Bank Fees Charged
Equation 1. The Checkbook Balancing Equation.
If I only include the cleared transactions in the statement, I can rewrite Equation 1 as
Closing_Bal – Open_Bal=Deposits + Interest – Checks – Withdrawals – Fees
Equation 2. Rewriting the Checkbook Balancing Equation.
To reconcile my checkbook, therefore, I need to verify that
Equation 2 holds once I mark off all the cleared transactions in my account.
What I will do with Alpha Five is simply to mark off each cleared transaction,
one by one. When the last transaction is cleared, then the sum of
(Deposits + Interest – Checks – Withdrawals – Fees) from my checkbook
should be equal to
(Closing Balance – Opening Balance) from the bank statement
or finally
Cbal-Obal-Deps-Ints+Chks+Withdwl+Fees=0
Equation 3.
I need to obtain 3 variables from the user: Closing balance, Opening balance, and Statement Date. Then I add another variable, Diff, which is equal to Cbal-Obal-Deps-Ints+Chks+Withdwl+Fees. As I mark transactions as cleared, Diff will dynamically change. When all the transactions are cleared, Diff should equal 0, as in Equation 3.
Let’s add the variables to the form:
Figure 5. Add the variables to the form.
These 4 variables are all layout level variables. They will be visible to each script in the form without having to use a dim shared statement.
I initially attached this script to the OnDepart event of the Cbal field:
Diff=Cbal-Obal Diff.refresh()
Script 1. Initial Diff calculation.
This will show the value of Diff before any transactions are marked as cleared. But I need to include all the cleared transactions that have not already been reconciled in a previous month and that occurred on or before the bank’s statement date. This is a task suitable for a query. Let’s change Script 1 to include all the unreconciled transactions that have been marked as cleared and that antedate the statement date:
Diff=Cbal-OBal
dim t as p<
t=table.current()
query.filter="reconciled<>.t. .and. date<={"+dtoc(Statement_Date)+"}"
query.order="date"
query.options=""
t.query_create()
t.fetch_first()
while .not. t.fetch_eof()
if t.type="Withdrawal" .or. t.type="Check" .or. t.type="Fee"
amt=-t.amount
else
amt=t.amount
end if
if t.cleared=.t. then
amt=-amt
diff=diff+amt
end if
t.fetch_next()
end while
diff.refresh()
t.fetch_first()
Browse1.refresh()
Script 2. A new calculation of Diff.
Script 2 accomplishes 2 tasks: it shows all the unreconciled transactions up to Statement_Date in Browse1, and it recalculates Diff. The query.filter statement
query.filter="reconciled<>.t. .and. date<={"+dtoc(statement_date)+"}"
will cause all the unreconciled transactions up to the statement date to be included in the query and subsequent browse.
Let’s try an example. I’ll show you how the form initializes with sample data in the checkbook:
Figure 6. Balance form before statement information is entered.
And now we’ll enter a statement date, opening and closing balance, and tab out of the Cbal field. The program in Script 2 will run, with the result that
The screen now looks like this:
Figure 7. Balance form after entering the statement date and statement balances.
We’re moving along. Now, we have to figure out an easy way to change the value of the Cleared field for each transaction that appears on the bank statement. Although we could create an OnPush script for a button, we can make a more intuitive interface by coding for the browse’s OnRowDblClick event. Then all the user has to do to change the Cleared status of a check is to double-click on it:
dim t as p
dim amt as n
t=table.current()
if t.reconciled=.t. then
ui_msg_box("Can't change status","of reconciled
transaction")
end
end if
t.change_begin()
t.cleared=.not. (t.cleared)
t.change_end(.t.)
if t.type="Withdrawal" .or. t.type="Check" .or.
t.type="Fee"
amt=-t.amount
else
amt=t.amount
end if
if t.cleared=.t. then
amt=-amt
end if
diff=diff+amt
parent.resynch()
Script 3. The OnRowDblClick script for the browse.
The line,
t.cleared=.not. (t.cleared)
is just a fast way of toggling the value of a logical variable back and forth.
This is an example of the actual clearing process in action. In our hypothetical bank account I have so far cleared all but the last 2 transactions from before the statement date:
Figure 8. Clearing transactions.
After I double click on the next to last transaction, the statement is fully reconciled:
Figure 9. All the transactions have cleared the bank except the last one, and the account is fully reconciled.
Now we are almost, but not quite, done. We have indicated which checks have cleared and have balanced our checkbook, but we have not set the reconciled field of the transactions to .t. Until those transactions are marked as reconciled, they will keep appearing in future check balancing efforts. We can create a simple button script to reconcile the transactions:
''XBasic
if abs(diff)>.01 then
ui_msg_box("Can't finish","Not fully reconciled")
end
end if
dim t as p
t=table.current()
t.fetch_first()
while .not. t.fetch_eof()
if t.cleared=.t. then
t.change_begin()
t.reconciled=.t.
t.change_end(.t.)
end if
t.fetch_next()
end while
Script 4. OnPush script for Reconcile button.
You may be wondering the purpose of the lines:
if abs(diff)>.01 then
ui_msg_box("Can't finish","Not fully reconciled")
end
end if
These lines prevent us from performing a reconciliation if the statement and our checkbook do not balance. In an ideal world we could write
if diff<>0 then
ui_msg_box("Can't finish","Not fully reconciled")
end
end if
But Alpha Five uses binary math, which is subject to round-off errors in the decimal points. Therefore you should avoid making comparisons to an actual "0" and make your comparisons instead to within a short range around 0. Here I am willing to accept a round-off error of up to 1 cent. It is unlikely that the round-off error will ever be this large.
In addition to a Reconcile button, I added a Postpone button to let me close down this form and return to my main checkbook entry form if I need to add or correct a transaction. The code for the Postpone button’s OnPush event is
dim t as p
t=table.current()
t.index_primary_put("date")
parent.close()
Script 5. OnPush script for Postpone button.
This code sets the index of the checkbook to its normal "date" order, and then closes the form. If I did not reset the index, then when I opened my usual checkbook entry/edit form, the query in the OnDepart event for the Cbal field would be the active index, and that is not what I want! For the same reason, I also put this code in the OnExit event of the form, in case the user closes the form without pressing the Postpone button:
dim t as p
t=table.current(
t.index_primary_put("date")
Script 6. OnExit script for form.
There’s almost no limit to the number of cosmetic enhancements you can make to the form. For example, I wanted to make sure that the user types in the statement date and opening and closing balances before clicking on any transactions in the browse. I accomplished this by placing a conditional object around the browse, and making the browse invisible until the user has filled out all the required variables. I’m sure that you can think of further enhancements.
My final form looks like this:
Figure 10. Final form for reconciling the checkbook.
last revised 1/9/99 - pkw
Don't forget, we need your feedback to make this site better!