Online Banking Transaction Matching Rules

[DanCooperstock]

We are the authors of a bookkeeping and fund accounting program for churches and charities (ACCOUNTS from Software4Nonprofits.com - not an ad, just telling you who's asking this!). Currently ACCOUNTS does not include importing of transactions from online banking, but we would like to include that at some point.

Our problem is that it's really hard to see what the right rules might be for matching downloaded transactions against manually entered ones. None of the other venders (like Quicken or Intuit) seem to publish comprehensive rules for how they do that, and it's clearly complex.

It would make sense to me to match on cheque numbers first, but what if other details (like the amount) don't match?

And if there's no cheque number, you could try to match on date and amount, but (a) there might be duplicates, and (b) there might be inaccuracies - like transactions that the bank doesn't think happened on the same date you thought it happened when you entered it manually into the accounting program. (That's probably even worse for credit card downloads.)

I would be very hesitant to include any matching on the Payee field because it's entirely likely that the spelling would differ between the manually-entered transaction and how the bank sees it.

Does anyone have either any exact knowledge of algorithms (detailed rules) used by other software for such matching, or well thought-out suggestions for how to do so?

Many thanks.

 

[Steve-LevelUp]

I don't know the rules specifically of what you are looking for, however, given the complexities of bank reconciliations, I recommend developing a manual method first, one that takes the import and puts it into a user friendly view, allowing them to see and reconcile.

Then, you can apply the rules you discuss above (eg, match check number and amount) and see how many automated matches you can make leaving the unmatched items as unreconciled. if you focus on the user interface, then reconciling the remaining items should be easy, while also giving the user a clear view of what was reconciled automatically.

 

[DanCooperstock]

I don't know the rules specifically of what you are looking for, however, given the complexities of bank reconciliations, I recommend developing a manual method first, one that takes the import and puts it into a user friendly view, allowing them to see and reconcile.

Then, you can apply the rules you discuss above (eg, match check number and amount) and see how many automated matches you can make leaving the unmatched items as unreconciled. if you focus on the user interface, then reconciling the remaining items should be easy, while also giving the user a clear view of what was reconciled automatically.

Thanks. Yes, we are certainly aware that there has to be a user interface for manual matching, correcting matches, etc. But we also need to start with some reasonable rules for attempting automatic matching.

 

[Steve-LevelUp]

I think you have a good start. Best is to think of a very complex bank rec and then run it through its paces. look at all the potential scenarios that could come up.

eg, multiple entries with one deposit
deposits not lining up
currency variances
bank fees
missing deposit
chargebacks