Reconciled balance not agreeing with cleared transactions

Ward Grant · January 14

I'm running a subscribed version of Quicken on Windows 11.

I've balanced my checking acct w/o issue going back to Sept of 2023 but can't get December to reconcile. The bank statement's opening balance is 7,249.17 and its ending balance is 4,839.69.
All my checks/debits agree with the bank: -4,773.57
All my deposits/credits agree with the bank: 2,364.09

But Quicken's cleared balance is 961.78, noting a difference of -3,877.91

Two years of college calculus and I figure out what's happening here. Can you help me out, please? Is it me, the bank, or the program?

Mark1104 · January 14

does the banks' opening balance of $7259.17 match the ending balance from November's reconcilition in Quicken? (look at the last Quicken transaction with a 'R' as the "CLR" status.

there have been issues over time where Quicken changes the opening balance of the account which throws off its current Quicken current balance.

Ward Grant · January 15

Yes. It does. As I mentioned; I've had no reconciliation issues for the three previous months. I've also repeatedly dbl-checked the reconciliation balances.

I do also recall that Quicken has in the past had its own issues with math.

Mark1104 · January 15

sort all your Quicken transactions by the CLR column. is there a rouge transaction that is dated well into the past that is not cleared?

JoelC · January 16

Or, unless I have missed something, a rouge transaction that should be date din teh future but has somehow been dated with the reconciliation period.

One sure fore way — given that the started balance matches is to unclear all transactions and then go through the reconciliation line item by line item per the bank statements!

Chris_QPW · January 16

Sorting by CLR shouldn't be needed. No matter how old a transaction is, if it is on or before the ending statement date and its status is uncleared or cleared it will show up in the reconcile window (uncleared don't have the green check box). On the other hand, reconciled transaction no matter what the date will be included in the cleared balance. So, future dated reconcile transactions are one way that the cleared balance amount can be wrong.

Another hopefully obvious thing is that if you have transactions in the reconcile that aren't marked cleared, they won't be included in the cleared balance. If you have these, you may have to select one or more of them to add up to the missing amount.

Note that the cleared balance should be the balance on the last reconciled transaction (provided it isn't past the statement ending date) and all of the transactions marked cleared.

So, if one was to select the Clear All button in the register, you would expect the Cleared Balance amount to be balance of the last reconciled transaction, which you said is the same as the opening balance from your financial institution.

JoelC · January 16

@Chris_QPW agreed, the approach I suggested is brute force and should always work. It is not the most elegant but it is effective.

Ward Grant · January 18

JoelC and Chris_QPW,

Thank you very much gentlemen. But I don't "hear" you taking in to consideration the fact that ALL my December transactions agree with the bank's December transactions. I'm seeing a solid list of checked-off checks/debits and deposit/credits — no weird dates. No "hanging chads."

I DO, however, show 20 transactions going back to 2021 that, despite positive reconciliations, never got cleared. It's like those transactions never happened. I've just never known what to do with them, since everything seemed to be reconciling OK. Could those be an accumulating problem?

JoelC · January 18

@Ward Grant

But I don't "hear" you taking in to consideration the fact that ALL my December transactions agree with the bank's December transactions. I'm seeing a solid list of checked-off checks/debits and deposit/credits — no weird dates. No "hanging chads."

I believe we have taken that into by our approach by:

i) confirming the starting balance; and

ii) conforming there are no transactions in Quicken that are not on the bank statement

I DO, however, show 20 transactions going back to 2021 that, despite positive reconciliations, never got cleared. It's like those transactions never happened. I've just never known what to do with them, since everything seemed to be reconciling OK. Could those be an accumulating problem?

Possibly, this would show up in the worst starting balance for the reconciliation, point i) above.

Chris_QPW · January 19

But I don't "hear" you taking in to consideration the fact that ALL my December transactions agree with the bank's December transactions.

This statement indicates to me that maybe that you don't understand what Quicken's reconcile is doing.

It isn't reconciling a given month. It is reconciling the whole account.

For instance, say I have everything reconciled and I change one reconciled transaction either the amount, or deleting it or even adding one. When you go to reconcile again, Quicken will say it now out of balance/not reconciled.

So, one can't say I checked all of the transactions in X month, and because they are correct there is no way I shouldn't be able to be reconciled.

I DO, however, show 20 transactions going back to 2021 that, despite positive reconciliations, never got cleared. It's like those transactions never happened. I've just never known what to do with them, since everything seemed to be reconciling OK. Could those be an accumulating problem?

Yes, absolutely they can be the problem.

Let me give you my definition of "reconcile". To make Quicken's account match exactly with what the financial institution has with the only exception being transactions that the financial institution doesn't yet know about. An example of a transaction that the financial institution doesn't know about is say you wrote a check, you enter it into Quicken because you want to make sure that you don't forget it/overdraw your account. Until it is cashed and sent to the financial institution it isn't going to know about it. It should be in your Quicken register without the c or R in the CLR column (uncleared).

If you have unreconciled transactions in the past there are only a few reasons that they can be there.

But let me state something, Quicken should have marked any downloaded transaction with a c for cleared. If they have no c in it, one has to question if they were manually entered or whatnot.

They haven't cleared the financial institution yet.
They are duplicate transactions that shouldn't be there.
Instead of marking them reconciled you somehow put in a balance adjustment. This is basically #2, you have both the transactions and another "balance adjustment" that is taking their place.

One thing that I see over and over on here is a statement like this "I have to keep entering balance adjustments over and over 'to reconcile'". First off, as far as I'm concerned, they didn't reconcile. Reconciling would have been getting to the root of the problem. Hint, it is always going to be a user or Quicken problem, the financial institution's almost never make an error in this. What you have to do to fix the root problem is a whole other question, but as soon as a person puts in a balance adjustment without understanding the root cause, that tells me they didn't reconcile.

Is this an easy process? No. And in fact, if ignored long enough it might even be impossible to do right. For instance, if you can't get a hold of statements anymore for the time period where it is wrong.

If one knows that they have truly reconciled account in a given month and copy of the data file at that point, then they are pretty much assured that they can fix anything by using that backup and fill in from there. But if one has ignored a problem for years, they don't have that backstop. They can go back but will always know that they are only getting back to a non-reconciled "but maybe ok" state.

You can go back and find out why those transactions are there, but the fact that they are set to uncleared means at this point they aren't being considered in the cleared balance or the reconcile. If you deleted them Quicken's reconcile wouldn't change one bit. Something like there transactions would bother me no end, but others might just delete them and say, OK it is in the past and I don't care. That is in fact, what putting in a balance adjustment does. The reason this bites people sometimes is because a transaction(s) that the balance adjustment was correcting for, now shows up. This can certainly happen if a person is reconciling to an online balance that has pending transactions in. It will be off by those pending transactions. Then they put in a balance adjustment, and then shortly afterwards those transactions download.

DC-Dave · February 2

just had a similar issue reconciling my credit card statement, but it was off by the exact amount of one transaction on Jan 1. All the numbers reconcile between the bank statement and whats cleared in the register. I rechecked it multiple times manually checking every fn transaction in the reconcile window. I ended up just unchecking the one transaction to get the reconciled amount to be zero. then manually marked that one transaction as reconciled. Maybe you have a similar thing with a transaction or two? My suspicion is a bug in one of their many many updates they push out to us related to something between the year change (my statement period was Dec 20 '23 until Jan 20 '24. I've been using Quicken for 20 years. This is not the first time the reconcile process has been off. My wife is an accountant and I am a software engineer - you don't mess with our numbers!

Ward Grant · February 4

Thanks for the suggestions, gentlemen. But even DC-Dave's error-check checks out. I think it's time to get some support from the programmers. (That, or the bank has screwed up… Right!)

Ward Grant · February 4

BTW; I've been using Quicken for about 20 years, too, and recall at least a couple quicken math issues in that time.

JoelC · February 4

@Ward Grant , confirm there are math errors / issues in Quicken. I have posted a few them over the last 2 or 3 months myself.

Chris_QPW · February 4

On the subject of "math errors". I have certainly seen some in budgets and such (they aren't really math errors as much as database query problems that don't include the right transactions to be added together).

In general, I have never personally any in reconcile given the understanding of exactly what it does.

Others have reported such problems, but the problem there is that no one has ever actually produced any proof. It is all just statements. I have seen a couple of times where someone had a corrupted data file and the adding of a transaction in the register was shown (and wrong).

The biggest problem with all the reported reconcile problems is that people are pointing to the Cleared Balance as the problem. The reason this is such a problem is that the only way you can be sure that you are correct and Quicken is wrong is by adding up all the Reconciled transactions and cleared transactions in the register. And no one is doing that. There are ways other than adding it up manually though, as I think about it. Maybe the best would be exporting to Excel and having it add them up. Another possibility/cross check would be to select the transactions in the register and see what Quicken comes up with for a total there.

Other problems I have seen has to do with the adding up of the debits/credits in the reconcile window. This is much easier to track back. And one of the ones that I have hit a few times comes back to this statement "understanding of exactly what it does". Quicken will add in the reconciled transactions even if they are past the Ending date used for the reconcile. I personally think this is wrong. And it also means that it isn't possible to reconcile a section of transactions that end before the latest transactions. In other words, If I want to re-reconcile the transactions for a month ago, I can't just set those transactions as cleared and then reconcile just them, I have to set all transaction from a month ago to the present and reconcile both that other month and this one.

Here is an example of this. I put in the date of 1/30/2024 and balance of 1952.42.

Notice that I'm off by $184.07. That is 178.07 + 6.00, the two reconciled transactions that are past the ending date.

If all you do is look at the Reconcile window, you would be very hard pressed to see where the problem is.

Chris_QPW · February 4

Let me state something that might not be obvious to everyone.

If you have been successfully reconciling month after month (or whatever period, you use) and we could be certain that nothing in the past in your register could be changed, then for sure then you could safely say I reconciled last month, but it won't reconcile this month, the problem must be in this month's transactions.

But we can't say that. There are numerous ways that past transactions can be changed. So, the "math" that Quicken is doing with what it currently has can be absolutely correct, but you aren't reconciling because something in the past changed, and as such the problem may or may not be in this month's transactions.

JoelC · February 4

@Chris_QPW

Let me state something that might not be obvious to everyone.
If you have been successfully reconciling month after month (or whatever period, you use) and we could be certain that nothing in the past in your register could be changed, then for sure then you could safely say I reconciled last month, but it won't reconcile this month, the problem must be in this month's transactions.
But we can't say that. There are numerous ways that past transactions can be changed. So, the "math" that Quicken is doing with what it currently has can be absolutely correct, but you aren't reconciling because something in the past changed, and as such the problem may or may not be in this month's transactions.

Agreed but should it not be the case that once a transaction is reconciled that it should be "locked" this negating the very possibility / problem that you raise. Personally, I never understood why Quicken does not do this.

Ward Grant · February 16

Well, I'm totally befuddled, now.

I did get in touch with Support and he immediately focused on the several uncleared transactions I had accumulated (@Chris_QPW's concern).

Support advised the fix was to delete all those uncleared transactions and accept the balance adjustment. We went back and forth a few times because I was and still am leery about doing so (I've kept a record of them, just in case).

One thing I think I failed to mention is that the program's Cleared Balance didn't agree with the debit/credit balance (isn't THAT a red flag?!) But it didn't seem to bother the Support guy. So, I deleted the uncleared and accepted the balance adjustment. (Because, of course, things still weren't adding up.)

That was December's reconciliation. January is wanting a balance adjustment, too. And again, all entered transactions agree with the bank's statement.

I'm beginning to wonder why I don't just go back to Excel. At least I'd know any screwups were more probably my own fault, and easier to track down :(

Chris_QPW · February 16

Let me say that I never accept balance adjustments and find it poor advice to do so. What you are doing by applying a balance adjustment is admitting that you have no idea why your account hasn't reconciled. Yeah, you might get lucky and there was a onetime problem, but without understanding the underline cause of the problem you may very well have the problem again.

Proper reconciling isn't easy. And there is something to understand fundamental to Quicken. Quicken reconciles the account, not "the last month". And it is VERY possible that what was reconciled in the past might not be today.

For instance, there is an ongoing bug that changes the Opening Balance in the account. That will certainly change the "Cleared Balance" amount in the reconcile. The Cleared Balance is calculated adding up all the reconciled transactions (even if they are in the future, which I think is wrong), and all of the transactions that are marked cleared up to and including the ending date used for the reconcile.

There is also an ongoing problem that is affecting a lot of people where the download is missing transactions (usually transactions where the amount is the same as another transaction that was recently downloaded).

Throw out also if you are using Sync to Mobile/Web sometimes it messes up transactions too.

If you reconciled on a given date (actually got a balance of zero, not any balance adjustment) then the very first thing I would check is to see if that balance has changed. Either by looking at a backup or maybe even easier just look at what it was online at your financial institution.

If it is wrong, then you know that the problem is in those past transactions (all the way back to the Opening Balance transaction). If that is the case, then the first thing to check is that Opening Balance transaction because of the known bug.

If it isn't that, then you have to decide if it is easier for you to do a re-reconcile of all the transactions or go back to a copy of your data file before the problem started and redo whatever has been changed before that.

Note that if one is going to do the re-reconcile for the whole account you can do what is called a binary search. Divide whatever period of time you are going to reconcile in about 1/2 reconcile the first half. If that works you split the rest in 1/2 again and continue. If the reconcile fails, you have un-reconcile the transactions and split that section in half. This will speed up the reconciling process a lot instead of trying to do something like one month at a time.

Note that "un-reconciling" is selecting the first transaction and then hold down the shift key and select the last one. Then right click and go through the context menu to reconcile and then clear.

JoelC · February 16

@Chris_QPW

Let me say that I never accept balance adjustments and find it poor advice to do so. What you are doing by applying a balance adjustment is admitting that you have no idea why your account hasn't reconciled. Yeah, you might get lucky and there was a onetime problem, but without understanding the underline cause of the problem you may very well have the problem again.

I could not agree more.

The math guy in me is focused on finding the cause . problem not adjusting around it. To @Chris_QPW 's point, the problem is still there, you have simply put band-aid on it!

Ward Grant · February 16

@Chris_QPW

Note that if one is going to do the re-reconcile for the whole account you can do what is called a binary search. Divide whatever period of time you are going to reconcile in about 1/2 reconcile the first half. If that works you split the rest in 1/2 again and continue. If the reconcile fails, you have un-reconcile the transactions and split that section in half. This will speed up the reconciling process a lot instead of trying to do something like one month at a time.

OK, call me stupid (I'm just a writer/media producer), but doesn't whatever period I select still have to agree with the bank? Therefore, doesn't that necessitate going by their monthly ending balance?

BTW; I keep extremely close track of my receipts and never allow Quicken to access/provide my data online. I enter everything manually.

Chris_QPW · February 16

I'm sorry, I didn't explain the process clear enough.

Let's imagine you had a register with only transactions from 1/1/2023 to 12/31/2023 and you needed to reconcile all of that.

One way to do that would be to start with reconciling January, and then February, … This will take you 12 reconciles.

But the "binary way" would be to reconcile January to June all in one reconcile (split the year in 1/2 and reconcile the first half).

If that reconciles without a problem, then you know that there isn't any problem in the first half of that year, and you can proceed to reconcile the second half of the year looking for problems. But if there is a problem in first half of the year then you can cancel the reconcile and divide the first 6 months up by 1/2 and try reconciling January through March. And do the same process. Once you have a reconcile that passes you can move on to the future, dividing it up again 1/2 at a time.

Note that you always have to work from older to newer, and you can't have any transactions marked as reconciled that are later than the date that you put into as the ending date for the reconcile.

JoelC · February 17

@Ward Grant

BTW; I keep extremely close track of my receipts and never allow Quicken to access/provide my data online. I enter everything manually.

Amen. I too enter everything manually so that I can enter / track the receipts and then the memo field, payee field, tag field, etc. get completed in a consistent manner. This, at least to me, is necessary to make searching easier and more efficient.

Ward Grant · February 17

Thanks, @Chris_QPW. I'll keep you all posted.

Reconciled balance not agreeing with cleared transactions

Answers

Categories