Splasm Software Discussions

I'm using Checkbook for my personal use and it's doing fine, and I've
had great support. Thank you Keith.

I'm invesigating using Checkbook instead of MYOB for my business
checking and Paypal accounts.

I want to be able to import QIF files from the bank and Paypal, and do
automatic reconciliation.

I've searched this forum and seen some past comments on doing this
from last September, but apparently no progress has been made.

When Checkbook imports my bank QIF file, it knows that an entry
cleared and it finds the check number in the QIF file. I think it
should find the matching checking number from when the check was
written, and flip the Resolved flag. Instead it imports a duplicate
check and marks the duplicate as resolved.

Now this is 2006 and automatic reconcillation of a checking account is
not exactly rocket science, and yet my research finds no software that
can do this on a Macintosh.

As far as I can tell, Checkbook may be the closest to implement this.

Keith, would you care to comment if this is on the horizon for
Checkbook. Or even possible. I mean it looks pretty easy to me
but I've been wrong before. Plus I don't have to code it

Thank you.

mark

The next version of CheckBook will at least prevent duplicate Checks from entering CheckBook by comparing check numbers in incoming transactions with those of existing Entries. If Date, Amount and Check Number match then CheckBook 1.6.2 will disable the Import checkbox. The decision on whether to import is still up to the user but CheckBook puts the brakes on a potential match so the user has some clue that it could be a duplicate.

It sounds like what you're suggesting is that CheckBook might automatically pick out incoming duplicates based on Check Number and simply mark the corresponding existing Entries as Resolved. That's an interesting idea because it works around the basic issue with synchronizing the Real World with a personal finance manager: unique transaction identification information. It would only work for Entries with a Check Number, and if it messed up it would be because the user did not enter the correct Check Number to begin with. That's probably an acceptable trade-off. It'll only affect QIF, since OFX (when we make that jump) will provide a real unique identifier for each transaction regardless of whether the Check Number matches.

What needs to be thought out is how to present this to the user, since CheckBook's Import function is really designed to Import, not Reconcile/Resolve. We will ponder this along with some other points that came up while we read your message. Give us some time and bump here if you don't hear from us in a few days.

Now, I've also written a very long response on some of the general issues surrounding accurate synchronization, which I present for your enjoyment:

There's not much that's difficult to code, but automatically synchronizing Entries created within CheckBook (or any personal finance manager) with transactions that exist at a Real World institution is complicated and made impractical by the order in which events occur, keeping track of one's finances in a personal finance manager while the final word occurs in the Real World, and the file formats in between. Most of these concepts are also true of synchronizing Entries that were imported from a Real World institution from the start with their current versions as they exist at the Real World institution.

Let me sum up:

When an Entry is created in CheckBook it may correspond to a transaction at a Real World institution. The distinction could be drawn at Checking vs. Cash, but CheckBook doesn't see things that finely. An Account is an Account, and the Entries within are treated more or less the same whether they're Cash, Check or Debit Card expenses.

So, a user creates an Entry in CheckBook for a specific Real World Debit Card transaction, say $25.00 to the neighborhood pizza parlor. That would be Carpinelli's for me, about 22 minutes due east in Moody, Alabama. I'm not affiliated except by heritage, but I do not want another great pizza place on this side of town to bite it (RIP Dante's) - so drive by when you get the chance. Neighborhood, indeed. The Debit Card Entry represents a transaction that won't necessarily hit the user's Real World Account for anywhere from four hours to four days (or more). When the transaction does show up in the Real World, the user downloads a QIF statement and imports into CheckBook. CheckBook probably won't have enough information to match the original Entry created in CheckBook against the incoming transaction in the QIF file because the QIF format doesn't contain unique transaction identification data and even if it did, as in the newer OFX transaction format, CheckBook wouldn't know that unique transaction identification information because it was created in the Real World, after the Entry was created in CheckBook. That means that CheckBook can't accurately match the original Entry with the Real World transaction every time, so it takes a stab based on a few key criteria such as Date and Amount and only decides whether to enable/disable a particular potential duplicate rather than removing it from the import session altogether.

When Entries begin life outside of CheckBook and are later imported, they're still missing unique identification information when they come from QIF files, so the issue with matching potential duplicates, or synchronizing changes made outside of CheckBook at a later date, is the same. Some of this changes a bit when OFX is used instead of QIF, because unique identification information is part of the OFX transaction format, but that only mitigates identifying duplicates and synchronizing information, such as reconciliation/resolved status. Because...

The main issue with synchronizing transactions from the Real World and a personal finance manager is that if a transaction didn't begin life in the personal finance manager, and it didn't receive any unique identification data from the Real World as the Entry was created, then there's no reasonable way for the personal finance manager to know the unique identification data assigned to each transaction in the Real World. If an Entry already exists in the personal finance manager, created by a user without foreknowledge of the unique identification data, then the personal finance manager can't use the unique identification information when comparing the existing Entry and the incoming transaction - removing the key to 100% accurate synchronization.

For end-to-end syncrhronization accuracy, the only practical way to create an Entry in a personal finance manager and have the unique transaction information from the start would be to communicate directly with the Real World as the Entry is created. That can't really happen in the pizza parlor example, or any other case where money is spent on the spot.

The best-case for end-to-end accuracy that comes to mind is bill payment, and that's where bill payment aggregators/gateways come in. Typical bill payment isn't done between a personal finance manager and the actual payee, it's usually between a personal finance manager and a gateway bill payment service. The personal finance manager has a single protocol for discussing transactions with the gateway, and the gateway implements whatever protocols, electronic or otherwise, are necessary to forward payment to the actual payee. As long as bill payment begins in the personal finance manager, it will receive the unique identification information from the get-go, giving it everything necessary to track and update a specific transaction throughout its lifespan.

Deep breaths...

_________________
Keith Gugliotto
Primordial Sea Captain
Splasm Software
https://www.splasm.com

> It sounds like what you're suggesting is that CheckBook might
> automatically pick out incoming duplicates based on Check Number and
> simply mark the corresponding existing Entries as Resolved. That's an
> interesting idea because it works around the basic issue with
> synchronizing the Real World with a personal finance manager: unique
> transaction identification information. It would only work for Entries
> with a Check Number, and if it messed up it would be because the user
> did not enter the correct Check Number to begin with. That's probably
> an acceptable trade-off. It'll only affect QIF, since OFX (when we
> make that jump) will provide a real unique identifier for each
> transaction regardless of whether the Check Number matches.
>


Keith

Thanks for the prompt and thorough response.

For me, not knowing anything about check reconcillation may be a plus


I only want to import those QIF transactions that are not clearing
things that Checkbook already knows about.

And if a transaction matches up with dollar amount and check number,
just flip the Resolved flag on my existing Checkbook entry.


> What needs to be thought out is how to present this to the user, since
> CheckBook's Import function is really designed to Import, not
> Reconcile/Resolve. We will ponder this along with some other points
> that came up while we read your message. Give us some time and bump
> here if you don't hear from us in a few days.


I've been looking at this for a few days now, and have also found QIF
Master. You probably know more than me about this package, but I'll
explain anyway.

It cycles thru each QIF data record one at a time. The user can see
the data and decide to skip or save that transaction. A few other
gizmos, like flipping data from one QIF type to another.
7
So what I was expecting to see is Checkbook giving me the same one
record at a time view.

The choices being

Import Data (for monthly account fees or bounced check charges, that I do not know about)

Match to Existing Transaction - change the resolve flag based on matching amount and check number

Skip

QIF Master allows you to put in your accounting package account
number, and re-exports the QIF file.

Since Checkbook already knows my account numbers (I think you call
them Types), then at the same time I'm importing or resolving a QIF
transaction, allow me to update the Type.

Thank you for your consideration and I hope you can come up with a workable solution.

mark