Bank reconcilliations and date shifting

1

As we opened the doors to our business I was reconciling on a daily basis (because I’m OCD and learning, and I just wanted to. lol). All was going well. I reconciled every night. And it was easy. Everything reconciled perfectly.

But then we got busy and I didn’t realise but I haven’t done so for a few weeks. No problem, but when I go into the bank reconciliations, they’re not all NOT reconciled.

I never did a new one until the previous was done.

1358×626 67.8 KB

What I don’t get is the transactions are still there, but their dates have shifted by a couple of days and this shifting of payments have thrown the statements out.

The problem with this is, in the reconciliation screen, I don’t have access to the payments to readily change their dates. ie, I’m using the desktop version and the workflow is complicated. What I mean by that, I have to exit out of the report, find a missing transaction, edit it and change the date, then re-search to get the same dates and find the next missing transaction, change its date, then search again… etc. Because the scrolling doesn’t return to the same place it just makes the process more complicated (IMO)

756×636 53.1 KB

As can be seen, there is only one payment on the day, and I’d like to be able to select a few payments that have been moved from where they should be (here) and put them back. But to do so, I have to exit out of the report, find some missing transactions, enter each one and change the date to the correct date (it WAS correct before).

Anyway, I’m going to walk away for the moment, we’re running late.

But if anyone knows how a whole series of dates or can be moved in a group or what it is that may have happened it would be helpful.

I am on the desktop edition and using 16.10.91

2

Your post is confusing, @d3mad. What do you mean that dates have shifted. Where? In Manager, or in your bank statement? Can you provide examples? Dates in Manager should not change unless you edit them. Sometimes you will se an original transaction date, sometimes you see clearance date, but they don’t move. So, need more info.

3

Yeah sorry about that, I was trying to get as much out (in the post) before I ran out the door.

It seems the bank reconciliation date has changed from the date that it was cleared to the date it was entered OR SOMETHING LIKE THAT (I’m not saying it specifically, as I really don’t understand what’s happened). But there is something that has changed in the way the bank reconciliation date works because all these transactions were reconciled to the end of September and even up until the 8th October. I know this because I don’t do a new one until the last one is clear.

For example, it can be seen in the first screenshot (original post) that most of them aren’t reconciled. Yet in the below screenshot from manager version 16.10.2, all except the top ones ARE reconciled. In the 16.10.2 version I am using a backup dated 30/9/16. If I load the same backup file in 16.10.91 the reconciliations are out.

I’m going through and manually editing them now but the short of it is there a lot that need updating.

This is a real PITA.

1354×553 58.3 KB

4

For completeness, same backup file (30/9/16) and latest manager (16.10.91):

1350×565 59.4 KB

5

Well, I can’t tell much without seeing the underlying transactions, as you can imagine. But the thing to remember is that these are not documents or files, but reports. Manager doesn’t save the reconciliation statement; it saves the definition of the statement and generates a new one whenever you want to view it. This is true of all reports.

So as you change the underlying data, you change all reports that use it. This could be part of your problem. You may just be kicking the can down the road from one report to another. I have had situations where a new version of Manager yields trouble with previously defined reports, probably due to changes in data structure. But recreating the report has always cleared the trouble for me. Try deleting one of the unreconciled reports and recreating it as at the date that report was for. You should be able to obtain a reconciled report.

Personally, I have no use for lots of old reconciliation statements. I keep one for each bank or credit card account and edit it to the current date if I want to reconcile. Once you’ve changed the date and click View, it asks for the balance and then produces the report.

6

Thanks @Tut, but you’re actually making my point. Without any changes to the underlying data, the report structure appears to have changed.

The data file is the same data file imported into different versions.

I have just gone through all my backups in manager 16.10.02 as far forward as I could go (up to backup 28/10/16) and everything reports correctly, but then I started using 16.10.89 and that’s when the report doesn’t reconcile. Even older backups. So it can’t be the data.

Running a new report doesn’t change the result as (I believe) the result is probably generated at the time the page is loaded since in reality it is just a query. My thinking something behind the query has changed.

Keeping the old reconciliations is nothing more than I haven’t deleted or changed them. Initially I was just updating the date, but then I just started creating a new one. No reason in particular.

I realise this is probably inconsequential to someone who knows what they’re doing, or maybe it’s not, but for me, I haven’t updated my bank account for a week or so and went to run the reconciliation to see when it was last “correct” and found this problem going back over a month.

It has to come down to the way I’m entering the data being different to everyone else if no-one else is having the problem I’m seeing. I generally don’t change the top date since I enter a cleared date, but then more often than not I enter things on the day (or at least I try to—we’ve just been busy lately). Part of why I wanted to run it in the first place.

7

Based on that testing it seems to indicate a change within a Manager process, but that’s a guess.

8

I can’t help any further, @d3mad. I just reconciled all my cash accounts, and things worked flawlessly. So if anything is different, @lubos will have to sort that out. I was also using v10.16.91, as you said you were.

9

yeah, I think it has come down to me not changing the date of entry when it’s entered late because I was using the cleared date, and I think the old query did the same. That seems to be what I’m finding. All my entries that aren’t dated the cleared date are the ones that are wrong.

A feature where I could query are return all entries for the month where entry doesn’t equal cleared would be useful, if we had access to those fields (I have no idea if we do or not). I’d have a play with custom reports, but in essence, it’ll be quicker for me to go through and just manually change every entry that needs it.

Even quicker if I didn’t bother asking—but knowledge is power. :smiley:

10

@lubos, I’d really appreciate some help on this.

edit: disregard re the help, although clarification on the change in report building would be good (ie does clearance date override the entry/spend date?)

11

@d3mad, I just reread our posts. I’m not sure I understand exactly what you’ve been doing, but maybe I’ve identified something different from what I do, which has worked well (unless I make mistakes). Here is my process:

  1. I enter all cash account transactions with Date for the date the transaction actually happened, be that deposit, credit card charge, bank withdrawal, etc. You seemed to imply you were entering clearance dates.

  2. I show all transactions as pending unless they are fully completed immediately. To quality as cleared, they must be fully cleared by the financial institution, not shown as processing, pending, or anything else. Basically, only cash deposits or withdrawals in a bank account or PayPal fit this definition. PayPal requests do not. Card charges do not, etc.

  3. When a transaction is fully cleared, I switch it to cleared status and enter the date it shows as cleared on the bank or card statement. Sometimes, that date is earlier than the date it really clears. For example, my credit card charges on one particular account might take 3-5 days before they show up online, yet when they do, they show as having cleared on the transaction date. For another card, the posting date for charges is usually several days after the transaction date. Regardless, I enter the cleared date as whatever the statement shows.

  4. When I want to reconcile, I edit the reconciliation statement to the date I want, usually today. Then I view it and enter the statement balance for the edited date.

If this has ever produced problems, they have been traceable to one of two things:

a. A missing or erroneous clearance date.

b. Transactions that were entered out of order that happened on the same date as other transactions or as the clearance date of one of the transactions. Manager simply didn’t have the ability to know which event came first when several things happened the same date. Waiting a day has always resolved this.

Maybe that helps, maybe not. Good luck.

12

Thanks @Tut

That’s almost exactly how I do it now. When I first started I was NOT changing the ENTRY date (I’m still working through this, but this seems to be the main issue).

What it looks like has happened:

  1. I have initially entered the wrong date on the spend money entry right at the very start. Well, I haven’t actually entered it per sae, I just haven’t edited the date that appears, which is usually today’s date because…

  2. I enter pending or cleared as appropriate and I always enter the correct date for the clearance.

I realise now I have been going against the paradigm of Manager entries in that I believed the date at the top of the spend money feature was the date I ENTERED it into the system (since it defaulted to today), and I used the clearance date as the date the money was spent. I’m confident older versions worked this way since everything reconciled until somewhere in October.

I think I was under the misguided idea the clearance date would override the entry/spend date.

I am finding with the clearance date correct and the entry date incorrect, the accounts don’t reconcile

Are you able to test this for me?

13

I have found just entering a rough TIME into the reference field (if it’s unused) fixes this side of it. The time in the reference field puts everything in chronological order. Since I don’t use the reference field for anything else it works for me :wink:

14

That would make sense, as I assume entry date must receive some priority. I think you’re wrong in your memory of how things used to work. So far as I can remember, the date that has come up has always been the day the entry was being made, on the assumption that transactions should be entered contemporaneously. But I could be wrong.

Things would also be complicated by bank imports, since the bank would frequently only have clearance date. Some card transactions might include both.

Sorry, but I can’t test this for you because I’ve got no fake bank to cooperate with the experiment. I can only tell you what my experience has been, and I’ve already done that. But I’d start by making all the dates right.

15

When I said today, that is what I meant. The day of entry. My problem was for a while there I was entering them late and forgetting to change the date.

I would have thought the actual clearance date would have the highest priority.

Notwithstanding this topic, I have been plugging through 6 weeks of entries. I’m down to fixing the last 3 days of last month (everything before then reconciles). Then hopefully everything is good to go.

Thanks anyway Tut :smiley:
cheers