Opening Balance Changed

Before I used electronic downloads (no more 'reconciliation' that I need to do manually), this happened to me a few times over the 10 or so years I've been using Quicken for manual reconciliations. In every case I can remember, it was due to me messing up some previously reconciled transaction - USUALLY I deleted an entry in the register via a cut/paste instead of a copy/paste when doing that type of operation.

Quicken doesn't willy-nilly change the balances! I am 99% sure you probably inadvertently modified a previously reconciled transaction. Just my guess.

Allowing a balancing entry under these conditions would be defeating the purpose of reconciling.

The opening balance of a reconcile is the net amount of reconciled transactions in the account. All you have to do is make sure that the net amount of all transactions with an "R" in their Clr field is the same as the correct net amount for the reconcile. ;)

There is no way to determine what specific event(s) caused the opening balance to be different than the previous opening balance; so you must, in effect, re-reconcile.

In your case - as the simplest example: if you found a single $1800 transaction that had been previously reconciled but was now not reconciled, you should be able to just toggle its "Clr" field to "R". That should correct the opening reconcile balance.

But it's rarely that easy.

What you probably need to do is unreconcile transactions* back to a point where you have a known good opening balance for a printed statement. Then initiate a reconcile ... you can reconcile all the transactions you unreconciled right along with the group of transactions you were planning to reconcile when you discovered the incorrect opening balance.

[*To unreconcile: you can use Find/Replace to replace the "Cleared Status" of "R" with "c"; or you can select the transactions to be unreconciled in the register (as you would select multiple files in Windows Explorer), then click Edit > Transaction > Reconcile > Cleared.] [You didn't specify what version of Quicken you're using: the above is based on you not having a version of Quicken that uses "Smart Reconcile" ... which I think disappeared by at least Q2005 and probably earlier.]

Hi, lanman.

One of the first words that a junior auditor learns is "transposition". ;^}

That often occurs when the correct digits are entered, but in the wrong order; they are transposed. Like, "46" instead of "64". And it doesn't have to be the whole number; any part of the number can produce this result: $1,234.56 instead of "$1,243.56.

Transposition is the first thing that springs to an auditor's mind when the error amount is evenly divisible by 9. The answer when you divide the difference by 9 tells us how far apart the two digits are, and which column (units, tens, hundreds...) the error is in.

So your $1,800 error is most likely transposition of digits with a difference of 2 and in the hundreds column. For example: 6498

-4698 00

Other examples: 7999-9799 = -1800 (yes, it works in both positive and negative directions); 2000 - 200 = 1800; 1234.56 - 1243.56 = -9.00, which indicates a difference of 1 in the units and 10s columns. It even works when the digits are not in adjacent columns: 1234 - 1432 = -198; that's still divisible by 9 ("), but "which column" is not so obvious, except that there's obviously no point in searching the 10,000 column.

Of course, other errors, or a combination of errors, can also produce an $1800 difference, so this is not an iron-clad cinch, and it may not work in your case, but it usually makes a very sensible starting point.

RC

Wow! You need to unretire, RC, move up to these here parts in NY City, and help out the banks! What an interesting post. I knew about the 9s somewhat, but not to the degree you explained. Most interesting!

Great post, RC. I understand your analysis perfectly. Thanks for doing such a great job of helping out the newsgroup.

David Parker

No kidding - the guy certainly knows his stuff, and always does a great job explaining it in somewhat laymen terms where you can understand it.

Yes, a great exposition, but come on guys, the OP said it reconciled last month, he made no changes, and now the opening balance is off. How do we explain that?

Renny

Since 198 = 200 - 2 = 2 x (100 - 1), it's obvious that it's a transposition in the units and hundreds positions of digits that differ by 2. Subtraction, as well as division by 9, can be a useful tool.

This is the neatest math trick I've seen since a method of mentally calculating the day of the week for a given date that I learned in a math course from The Teaching Company. Thanks RC.

I worked as a software developer for the last 20 years of my career. I often heard exactly the same claim when one of my users experienced a problem. I almost always found out that they forgot about that one, little, unrelated change that couldn't possibly have caused a problem, but did.

I've also had the Quicken opening balance change in an account. I had never touched anything in the account either. I did change a transfer transaction in another account though.

If I never touched anything in an account for a month, it wouldn't need reconciling.

-- Jim

I just reread the OP's message. He never said that he made no changes. He said he had never had to force a balance correction before.

All right Jerry - I'll byte (computers here, you know). How do you do that? I always saw this as one of those things mental giants (or idiots savant) can do - you mean to say there's a trick behind it?!?

Hi, Renny.

Good point! But maybe it doesn't matter.

A "reconciliation" is not intended to "reconcile" everything. Its whole focus is on explaining the difference between two numbers. A bank account reconciliation focuses on a balance at a particular point in time; that point could be any time, but typically we reconcile as of the close of business on the last day of a month.

If we can explain why our checkbook balance does not equal the bank's closing balance then we have reconciled the two balances and our reconciliation is done.

We MAY want to perform some other task, such as another reconciliation as of another date - such as the beginning of the month. But that is a separate project. If the OP is still worried about this month's opening balance, then he should start from scratch and do a separate bank reconciliation as of the close of business of LAST month.

A bank reconciliation is like a portrait or a snapshot, not a movie. If we see a photo of the finish line at the Indy 500 after 190 laps, it tells us only who was ahead at that moment; it doesn't tell us who eventually won the race. It doesn't even tell us who had been ahead after 189 laps. And it certainly doesn't show us the whole race. A bank reconciliation explains the differences as of the end of this month (or whatever point in time we've chosen); it does not tell what happened before or after, and it doesn't explain the differences at any other time, such as the beginning of the same month.

RC

Aha, I got a byte. My uncle will tell you that that's one more than he ever got the few times he took me fishing :-)

Google "mentally compute day of week for date" and you'll find lots of methods. What I like about the one I use is its easy-to-remember MonthCode table since my memory sucks. Briefly, as it's off topic, it goes as follows:

YearCode = Years since 1900 plus 1/4 of years since 1900 MonthCode (in groups of 3) = 144 025 036 146 (= 12x12, 5x5, 6x6, 12x12 + 2) DateCode = YearCode + MonthCode + DayOfMonth DayCode = Divide DateCode by 7 and take remainder (it will be 0 to 6) Count forward by DayCode days starting at Saturday Back up a day if in Jan or Feb of leap year

Example: 9/11/2001

YearCode = (2001-1900) + (2001-1900)/4 = 101 + 25 = 126 MonthCode for Sept = 6 DateCode = 126 + 6 + 11 = 143 = 20x7 + 3 DayCode = 3

9/11/2001 = Saturday + 3 = Tuesday

It's really easy for the current year if you memorize the YearCode, which for 2009 [after dividing by 7 and taking the remainder] is 3.

For example this Christmas is 3 + 6 + 25 = 34 = 7x4 + 6 = Friday

If you're savvy at modulo 7 arithmetic you can simplify the math, e.g. by immediately subtracting 70 from the YearCode and by using 4 (= 25 - 7x3) for Christmas.

Here's how I've tracked down opening balance mismatches. As John points out, a reconcile opening balance mismatch usually means you've deleted or changed a previous reconciled transaction.

I tend to overbackup and this is where it pays off. I use Quicken to open a backup made before the problem started. I open up the register for the account with the problem and generate a register report. I export this into a text file. I then open my current Quicken fileset, generate a register report for the problematic account and export it.

If I compare the two files (using Word or another tool), I can easily find the transactions that are different.

A variation on this scheme is to export the register reports into excel, add balance columns and see where the two balances start to differ. (Excel 2003 has a compare side by side function that may help here).

Hope this helps.

Mike