How to reconcile, given that Quicken uses Posted date and bank statements show Transaction date

enikon

This might be more of an Excel question than Quicken, but any solution would help.

I am trying to find an easy(ish) automated way to reconcile Quicken with my downloads from the bank. (for additional info: I am trying to fix some older issue where some data got corrupted and some transactions are missing).

I downloaded Quicken data to Excel and also have my bank data in Excel.

Ideally I would sort date and amount, and see if they match; I would fine where the match gets off, and then I know which transaction is missing.

But, Quicken uses Posted date and bank downloads use transaction data. Those sometimes match and sometimes are off by up to a week or more, making the sorting not work. I am also trying some index/match, but it is hard with dates being so far off, and sometimes I have transactions with the same amount more than once over a few days. I can't match vendor name because those don't really match from Excel's standpoint.

Any good tips on how I achieve this?

Rocket J Squirrel

Quicken Banking registers have a Downloaded Posting Date column you can show. Will that help?

Chris_QPW

Even if the dates were the same transactions on the same day wouldn't be in the same order.

Personally, if I had to re-reconcile a whole account I would do it as a binary search in Quicken using the bank statements. That is provided that I can get all the needed bank statements. This actually goes a lot faster than people would think.

The idea of a binary search is continuously dividing the problem in half. And note the "half" doesn't have to be perfect.

First you unreconcile the whole account (select first transaction, hold down shift and right click and select the last one and then Reconcile status).

Next you pick an ending statement balance/date that is about in the middle of all your transactions and reconcile to that date. If you can reconcile then there isn't any problems in the transactions up to that point. If you can't reconcile you cancel the reconcile and split the first part in about the middle and continue from there. Once you can reconcile then you can proceed in the other direction, doing the same thing. And of course, if you get down to one statement and it has a problem you will have to find the problem transactions in that statement.

Given that both the date and the payee might be subject to being different, I see no way that this can be automated.

enikon

Thanks all. I have been using quicken for over 20 years, so my file is quite large at this point. Periodically Quicken goes off balance, and I don't know why. I have had more than once that the opening statement line, instead of being 0, got changed to some random number. (this one is easy to find). Often times paychecks get messed up and they won't even open let alone see any details in them. And other bad things here and there.

I do have all my bank statements dowloaded to excel since forever.

So i was looking for a way for - when this inevitably happens again - I could do a match between what i export from quicken and the bank statement, and see where it gets off.

But this gets me thinking that maybe the best thing to do is export a "good" state from Quicken, and when it breaks, match to that - any discrepancy then would point me to the error.