Quicken ending reconcile balance does not match statement

Frankx

HI again ealvin45,

So, where I think you are is that you determined that the out-of-balance problem was principally caused by a check that you had incorrectly marked as reconciled when it was still outstanding (or uncashed).  And that you have now corrected the status of the check from "cashed" or "cleared" to "uncashed".

Therefore, assuming no other errors, you should be able to reconcile the account to the most recent bank statement.  However, if you still have problems - simply come back here and we'll try to help.

Frankx

ealvin45

I printed out a new version of the last reconcile statement (June) and the ending balance was $49,224.22. When I brought up the reconcile window my prior balance was $49,224.22. I though I was finished because they matched.

Today I noticed that the new statement I printed out last night and the statement the bank provided me at the start of the month didn't match. The bank statement showed a prior balance of $50,932.13. The ending balance on the July bank statement was $35,831.06 which I believe is correct and showed up in the reconcile opening screen.

I think I'm pretty close, but my head is spinning. Hopefully you can straighten me out. Thanks again for all the support.

Frankx

Hi again @ealvin45,

A couple of questions first:

a) Above you said that the account balance per the June 30 reconciliation report was 49,224.22 - that should match the account balance you now see in your Quicken account for the same date as the date of the reconciliation report - does it?

b) What is the account balance in your Quicken account as of July 31?

c) Have you tried doing the July 31 bank reconciliation in Quicken yet?

Let me know.

Frankx

ealvin45

First let me add some new info I've just discovered. Despite unclearing the $1,418.06 check, it still shows the entry as if it was not changed. The new balance is $1,418.06 less than the transaction above it. For some reason, it doesn't look like that change took.

a) yes, it does match.

b) Per the bank statement it is $50,932.13, and that is the same number that shows up in the reconciliation window in Quicken.

c) Yes. When I got to the reconciliation page I believe I saw only two of my monthly transactions. Both were uncleared checks, with one being the $1,418.06 check from January that I uncleared (as referenced above). The other was a current check for $120.00 that hasn't been cashed yet. The remainder of my monthly transactions show up in the ledger (but not the reconciliation window), where they are marked with a "C."

d) I then went to the ledger and uncleared every June transaction, assuming I would see them in the reconciliation window, as if I was reconciling them for the very first time. Unfortunately, that wasn't my result. I got the same window with showing only the same the two checks described above.

Hopefully, this may clear up some things.

Thanks again.

Frankx

Hi again @ealvin45,

On your first comment above about "unclearing the $1,418.06 check" and the "new balance" in the account register not changing - that is normal behavior.  The act of clearing or unclearing a transaction does not affect the "running balance" of a checkbook or any accounting journal, and it should not.

In my second question - actually item b) -  I was asking about the balance of your bank account in Quicken, not on the bank statement.  What was the balance of the checking account in Quicken as of July 31?

Regarding your item d) - "unclearing" every prior month transaction is not going to help you (or help me help you).  So you need to go back and mark those as cleared - because they have cleared the bank and once cleared should not be changed.

Frankx

ealvin45

Thanks Frank,

a) Good to know.

b) The balance in Quicken on 7.31 is $32,462.03, which includes a check for $123.05 that hadn't been cashed yet, and thus didn't make the July statement.

c) I've once again marked these transactions as cleared.

ealvin45

Hi, I'm still here. I think we're pretty close to getting this resolved but I need your help to finish it off. Thanks.

Frankx

Hi @ealvin45,

Sorry, I did take a look over the weekend but I was thrown off by the balance per Quicken @ 7/31 (which you indicated was 32,462.03) while the bank statement balance on 7/31 apparently was 50,932.13, especially since I thought you indicated there was only one outstanding check in the amount of indicated is 123.05.

Is all of this correct, or did I miss something?  Perhaps here ere other outstanding checks @ 7/31?

Frankx

ealvin45

Sorry for the confusion. This stuff makes my head spin, especially dealing with two months of opening and closing balances.

Let me try again. The ending balance in the Quicken ledger on 7/31 shows $32,585.08.

Per the 7/31 bank statement, the ending balance is $50,932.13. At the opening of the reconciliation window it shows $49,224.22 as the prior balance. This It the same number shown in my reconciliation report. It seems that the discrepancy between these two numbers may indicate where to look to find the problem.

In hope that this will be useful, let me add this. Inside the reconciliation window I see $16,866.88 of debits that have been cleared. Three items aren't cleared, 2 checks not cashed totaling $243.05, and that Master Assn. check from January for $1,408.06. Adding those uncleared makes the total debits $17,109.93.

The cleared balance is $34,123.14. The statement ending balance is $35,831.05 with a difference of $1707.91. I hope that info helps.

Thanks so much for your kindness and patience guiding me through this mess.

Frankx

Thanks for the new information.  This is difficult to do using this forum, but it does sound like you are getting closer.

Based on the information above I think that you may be off by the 1,707.91 minus the 1,408.06 - which equals 299.85

You should look for a transaction, or transactions, during July that equal 299.85 and since the bank's balance is higher they would be checks that you marked cleared but were not cashed until after 7/31 - or a deposit that was made - or a transfer into the account - but doesn't appear in Quicken.

Take a look and get back to me.

Frankx

ealvin45

Thanks for everything, I can certainly understand the difficulty of attempting to solve this via this forum, and I greatly appreciate your valiant effort to help me.

BTW, I just noticed something. In my July reconciliation window the totals of the cancelled debits and credits column match the bank statement. In my last post I suggested that, "Per the 7/31 bank statement, the ending balance is $50,932.13. At the opening of the reconciliation window it shows $49,224.22 as the prior balance, which is the same number shown in my reconciliation report. I believe the discrepancy between these two numbers is the likely discrepancy." (Note: this quote is modified slightly to make it easier to understand.)

If this hypothesis is correct, I think it indicates that the July numbers are correct and the problem occurred before July. Does that seem right?

I'll spare you all the details on how I got there (unless you really want them), but it seems to me the difference may actually be $46.03. I went back over every entry beginning with Jan. 1. The good news (I guess!) is that the Master Assn. check appeared on a monthly statement and my wife and I had somehow missed it. I have now reconciled that entry from the ledger. (I hope that's the correct thing to do.)

The bad news is that everything else checked out and I didn't find the problem. It seems likely the error occurred prior to 1/1/2020. I vaguely remember having one month sometime in the past where I just couldn't get the reconciliation to zero out and I had to end the month without accomplishing that, and Quicken adjusting it.

Given all of this, should I just accept that I can't find the error and have Quicken reconcile the statement with this imperfection and just move forward? And if so, how do I do that?

Thanks again for everything. You are so great!

Frankx

Hi again @ealvin45,

You know, a few minutes after I sent my last post to you last night, it dawned on me that the January check could not have been uncashed, because you managed to reconcile that month and all the following since up until July.  Not to mention that it is unlikely that you wouldn't have heard from the "association" if you had missed that month's payment.  But at that point I was a little too tired to post again, so I thought I'd wait.

On the $46.03 remaining difference, I would suggest that you accept it and move on.  It could relate to the other small out of balance situation that you had before.

So to close things out you'll need to go back into the reconciliation for July (or start it over if you simply backed out of it when it didn't balance) and accept transactions that have cleared, etc. as you usually do.  You should then end up with a "difference" of $46.03 - and click "Done".  At that point Quicken will suggest that you accept an "adjustment" to bring the account into balance.  You should accept it and date it as of the bank statement ending date (probably 7/31/20) and move on.

Happy to have helped, and it was good working with you.  Don't be a stranger here...

Frankx

ealvin45

Frank, you're not going to believe this (or maybe you will :-).

After reconciling the $1,408.06 Master Assn. check I attempted to reconcile July. On the opening screen my prior balance was changed from last time's $50,932.13 to $47,806.16 (obviously, much greater than the difference that would be make by the $1408.06 Master Assn check). The ending balance stayed the same at $35,831.05.

Inside the window, the debits and credits are the same as before, and the differential is the same as in the opening window. Any idea why this differential would be so great?

ealvin45

I just discovered something significant and need help in how to deal with it.

In looking at my ledger I discovered that all of my July entries were cleared, but not reconciled. I'm pretty sure that's what was responsible for the major discrepancy discussed in my last post.

Frankx

Yes, the difference is greater than would have been expected.  The fact that the $1,408.06 check from January - that you thought wasn't cashed - was actually cashed (likely either in January or February) means that there was some other error at that time, and even thought you thought that the account was reconciled at that time, it was, in fact, out of balance by that amount back then.

And regarding your comment immediately above, keep in mind that  when you go to reconcile the account in Quicken, those entries that are marked as "c" in the register should show up, and those without any entry in the "Clr" should also be there, only entries that are marked "R" should not appear on the reconciliation screen. 

So from what you are saying, you apparently haven't gone back to try and reconcile the Quicken account to the end of July bank statement.  I suggest that you try to do that now - keeping in mind that if it seems as though it is not going to reconcile, you can always "Cancel" the reconciliation process at any time, and start over.

Frankx

ealvin45

Thanks Frank, to be clear, the Master Assn. was marked correctly prior to June. It was only at that time that I thought it hadn't been reconciled and I changed it to a non-entry (then when I found the cleared check, I changed it back).

What's so confusing is that, to the best of my recall, every month prior to June zeroed out perfectly. Also, it's very confusing how the difference jumped up here just recently, and I have no explanation as to why that happened.

Now, related to my last post, I was wrong. The cleared entries didn't make a difference, which is what I thought might be the case after thinking about it after posting. The difference is now $3125.97. I don't believe that number is correct but I think I'm ready to let Q adjust and move forward. Do you agree with that idea?

Frankx

Hi again @ealvin45,

I know this has been a "long and winding road" (to quote one of my favorite songs) but that is a pretty big difference in my opinion to leave as is.  Still, if you will be increasing the bank account balance in Quicken by that amount (which I am pretty sure is still the case), it doesn't bother me as much as if it were going the other way. 

There is one other thing that you could do to perhaps track it down time-wise.  You could compare the balances as of certain periodic dates (i.e. compare the balance per the bank with the balance per the Quicken register as of each Sunday starting with the last reconciliation date - which I believe was June 30).  As you compare the balances - which will clearly vary - look for the time when the "difference" between the two seems to increase/decrease be a more significant amount.  And when you see such a difference then take a closer look at individual transactions in that time frame to see whether deposits or checks may have been missed in Quicken.

But ultimately the decision is yours, and as long as you don't keep the cash balance of the account to close to zero (i.e. try to keep a $3,000.00 buffer in the account), the risk of overdrawing the account and getting a check returned will be slim. 

Frankx

ealvin45

Here's where I stand. I took your suggestion for resolving the account. Since I only have around 20 or so transactions each month I broke them out by months, comparing the ending bank statement and ledger balance.

I began in Sept of 2019. At the end of March '20 they matched. April was about $600 off. There was a major discrepancy of around $3000 in May, which is where I believed the problem to be). Looking closer, I discovered a double ledger entry for AMEX for almost $3000 and a mistakenly voided check for $!20 that was actually cashed (which I changed and corrected on the ledger).

I then attempted to reconcile June and there was around a $3000 discrepancy. It occurred to me to individually try reconciling all of the July Cs to Rs on the ledger. Apparently that was the right move and now both the June and July difference is only -$154.45.

My wife and I have gone over these entries three different times and we can't find the error. Interestingly, a few weeks ago on my first run through of the statements and ledger entries I had two $154.45 payment to a utility from the end of April and the middle of May. Since the utility amount varies each month I suspected could be an issue but called the utility and found that both transactions were correctly billed -- and both are now reconciled on the ledger.

Yet, intuitively it seems like this is the likely culprit because the odds of having another transaction or transactions adding up to that amount seem pretty unlikely. So I'm at a loss as to my next move.

One of the things I love about the reconciliation window is it clearly wraps up the month by showing you that you either balanced or didn't. I was thinking that you could go back to any month and see if that month was the issue or not. If not, you could move on to the next month and eventually narrow the problem down. But I don't think that's the case. Are there any other checks and balances kind of things I could do to pinpoint the problem?

You've been so great and I really appreciate all of your help and patience. Do you have any other thought on this?

Frankx

Hi again @ealvin45,

Well, you've done very good work!  

I think that you've identified the issue - it clearly has to do with one of those two $154.45 payments because it would be very unlikely that the problem would be related to another transaction or combination of transactions.  So, my next question is -  which is higher, the balance per the bank or the balance per your Quicken account register/ledger?

Though I understand that "both of those payments were correctly billed and are both now reconciled in the ledger". Is it possible that the utility switched you to level budget payments (you pay the same amount each month based on your expected average use based on last year's history) rather than actual usage? That could - over a period of a few months - cause this type of reconciliation problem because the amounts are the same each month and can easily be mixed up as you clear them in Quicken over a few months.

The fact that BOTH June and July are off by the same amount likely means that the problem/error happened in June.  If your balance per Quicken is lower than the bank statement balance at the end of both June & July, there could be a third entry for that same amount during the two month time frame.  If the difference goes the other way (Quicken's balance is higher than the bank) it could be that you missed entering a payment in Quicken.

Those are my "top of the head" thoughts.  I am confident that you've identified the problem.  Get back to me with additional info and/or questions.

Frankx

ealvin45

Finally! I believe the mystery is solved. Way earlier in this massive novel I mentioned that I had two checks to the utility company with the same amount of $145.54. I called the utility to see if that was a mistake, their records indicated everything was fine.

Tonight as we went through the register and the statements I discovered that one of those checks was on the register and marked as reconciled didn't show up on the bank statements.

(BTW, One big lesson I learned was that the best way to check things out is to print out the register and the statements. Then go through the statements checking them off of the register. It's a much cleaner process because your not going back and forth on late month checks or those that were cashed much later.) The one utility check wasn't checked off of the register and I couldn't find it on the statements.

So, what are your recommendations on how to handle this?

As usual, thanks for all your help.

Frankx

Hi again @ealvin45,

Nice to hear from you, I was starting to think that maybe you gave up or worse.

Okay, so you've got 2 checks in the register but only one cleared the bank.  You have an "uncleared check" (we also refer to those as "outstanding check") which will likely never be cashed.  If you want - and have them handy - you could take a look at the bills and or statements from the utility for one month before and a few months after, to see if they actually billed you for that amount.  You can also look at the most recent bill and see if the carried-forward balance (or beginning balance on the bill) is zero.  My guess is that it is.

I you just made the mistake of entering to checks when you should only have entered one - just void out the check that never cleared and you are done.

If you believe that you still own the amount, you should still void the check because it is unlikely that they have it, or will cash it even if they do have it.  And if you did owe it and they didn't get that check, they probably carried it over to the next month and you probably paid it then.

That's a long way of saying - void it and go celebrate!!

Let me know if you have any followups.

Frankx

ealvin45

As usual, much thanks. You are the best!

I must be cursed :-).

First, a quick update. Everything through August has been reconciled. As previously stated, I was off $145.54. in the June and July reconciliation. With that stated, I believe I'm now zeroed out and the pure mathematics are correct. However, for some reason Quicken isn't proving my point.

Per your last post, I voided the April check to the utility. I then attempted to reconcile April. Shockingly I noticed that there was an Opening Difference Balance of $14k, which is surprising because the end of the March register and monthly statement showed zero difference. There was also a end of month difference of $22k (no idea where that came from).

Also, when I went to the June and July reconcile windows they were no longer showing a difference of $145.54. They now showed massively large discrepancies.

Consider me dazed and confused. Hopefully you have an idea of what's going on here because it's baffling to me.

ealvin45

Here is some additional information that I hope might be helpful since my last post.

I mentioned that when I attempted to reconcile April there was an end of month difference of $22k. There were no transactions showing in the reconcile window. In comparing the statement ending balance and the ledger ending balance there was only a difference of $410, which sounds much more reasonable.

I don't know if this is enough information to help you figure anything out but hopefully it is. Thanks again for all your help.

Frankx

Hi @ealvin45,

Sorry, but I am now confused.  When was the last reconciliation that actually balanced?  

The fact that the register balance and bank statement balance for April 30th were out of balance by $410. makes more sense but the lack of transactions in the reconcile window does not make sense at all.  It sounds like there may be a problem with how transactions are marked in the register's "Clr" column. For all transactions in the register (or ledger) that have cleared the bank, have you marked then with a "c" in the register? There should be none marked "R" after the date of your last successful reconciliation date.

Let me know.

Frankx

ealvin45

Help! Where do i go from here?

Frankx

Hi @ealvin45

Let's reset a bit.  Which was the last month that the account reconciled?  

Frankx

ealvin45

Hi Frank, thanks for your reply. I believe that would be March 31, and at that time there was zero difference between the register and the bank statement.

Frankx

Okay, you had indicated that when you opened the reconcile window for April, there were no transactions and there was a large difference (22k), is that still the case today?

If so, open the check register/ledger for the checking account and look only at transactions beginning with April 1st. Are there any transactions that have "R" in the Clr column?  If so, change them to "uncleared" - you can do this by clicking on the "R" and pressing the "uncleared" button for each one.  In fact, you should make sure that any "R"'s after 4/1/20 through today's date are changed to "unclear".

Once you do that, open up the reconcile window again and you should see transactions.  Let me know if that works out.

Frankx

ealvin45

Yes, when I opened the reconcile window for April there was only a voided transaction and a $22K difference.

Yes, I saw the transactions and they all zeroed out with no difference. I haven't done the months after April yet but I'm cautiously optimistic that they'll be fine.

BTW, I'm going to start keeping saved PDF files of each month's reconciliation. Can you tell me how to do that?

I'll update you on the rest of the reconciliations when I get a chance to get to it. Thanks again for all your help. You are the best!

Tom Young

"Hi Frank, thanks for your reply. I believe that would be March 31, and at that time there was zero difference between the register and the bank statement. "

If you pull out your March 31 bank statement and compare the bank's ending balance to your 3/31/2020 bank balance per the register, does that still hold true?