How to reconcile a checking account against bank statements?

Question

My first thought on this matter, is that if the balance is off by a certain amount, one or more missing transaction should add (withdrawl or deposit) to the missing amount.
The software I am using for this uses an SQL Backend (Money Manager Ex) which makes it easy to query for missing / duplicate transactions.
The qfx file, from my bank, can be converted into a csv file and be filtered, using excel or anything else that can read that file format.
The csv file also has unique values for each transaction, and this is useful to find out if a transaction has accidently been imported twice provided it is also imported.
So how does one tell if they need to look at the csv or the MMEX transactions, the withdrawls or the transfers or the deposits, to find out what is missing?
It seems to me that after that, looking for only the withdrawl transactions that are less than or equal to the missing balance would be a good place to start, and then trying to find how a combination of this add up to the missing balance might shed some light on it; or...one could left join the unique id's to find the missing transactions in either using the same date range for both data sets MMEX and csv.
I already know how to look for duplicates.

Greg Schmidt · Accepted Answer

One of the difficulties in tracking down this kind of discrepancy is that there might not be a single problem to find.  There might be two or more missing transactions that were not imported, or there might be be two separate corrupted transactions that have incorrect values that are "off" by amounts A and B respectively.  You see that there is a difference of amount (A+B) between your MMEX balance and the bank's balance, but no matter how hard you try, you probably won't find a transaction with the amount of (A+B).  Of course, depending upon the signs of the errors, the magnitude of (A+B) may be smaller than either A or B.  Or larger than both.
The errors may be due to corruption of the data that occurred during

The preparation of your data file by the bank, or
The download of the file from the bank to your computer, or
The conversion of the data from qfx format to csv, and other processing, or
The import of that converted data into Money Manager Ex.

In the absence of better information, I would suspect step 3 the most.  The other processes are likely to be quite robust, and well-tested.
I can't offer a "magic bullet" solution to the problem - but I would suggest:

Cut your imports (or your reconciling) into smaller time chunks.  For example, try to reconcile a month's transactions instead of a year's transactions.  If you can narrow down the time window during which incorrect data was imported/entered, then you have fewer transactions to wade through to identify the culprit(s).
Don't compare your MMEX transactions against the csv transactions - both files may contain the same corrupted transactions, so you won't discover a difference.  Instead, compare your MMEX transactions against the source - which is the statements issued by your bank as their formal record of your account.

How to reconcile a checking account against bank statements?

One Answer

Add your own answers!

Ask a Question