Bank imports and transfers

Hi lubos, nice to hear your thoughts on this but I have a couple of notes.

I don’t know where you got this idea but that’s not true. Most if not all other accounting software can debit and credit any other account and that’s not unique to Manager. Some can even create a simple payment or receipt against other bank account thereby skipping transfers altogether.

Why do they still use matching then?

Reading your post, I feel that you have missed the point. You are discussing import and ignoring other more important aspects to maintaining bank accounts.

As much as love manager but I have to admit that it’s banking capabilities could use some improvements to be neck and neck with competition.

To learn why, we need to discuss what is bank reconciliation. Bank reconciliation boils down to these two steps:

  1. Identifying discrepancies between book (internal set of records) and bank records (external set of records)
  2. Resolving discrepancies

Here’s the areas where Manager is currently lacking:

  1. Manager can’t identify discrepancies. Sure the current bank reconciliation shows the total discrepancies for the closing balance, but I cannot do anything with it because Manager fails to attribute them to single transactions. How would I resolve them? Externally is the only available solution right now.

  2. Manager does nothing to prevent deletions of reconciled transactions.

  3. Manager doesn’t keep a decent audit trail over bank reconciliation process, mainly because there isn’t a bank reconciliation process, the bank reconciliation tab just creates superficial reports with nothing underneath.

  4. Manager can’t accommodate both manual and imported bank transactions. It just duplicates them and never gives you a clue what went wrong or where.

Take this for example, I wrongly set a transaction to be cleared when in fact it hasn’t. Or I set a transaction recorded at 100 BHD to cleared when it appeared in the bank at 105 BHD. Can Manager point this out to me? No.

For that you suggested this:

But how? Manager will never point out the faulty entries. This this must mean that I will have to go through each and every single transaction manually all while keeping a bank statement in a spreadsheet or PDF open. Even more likely I will have to do this :point_down:

That’s what my nightmares are made of. :joy: When in other software, you’ll have the system point the discrepancies to you and you can only undo a few matches to correct the entire thing without going to a spreadsheet or a PDF.

I know that many-to-many matching might not be feasible and that’s why I thought of simple referencing to an external record on a 1-to-1 basis. Just like referencing an invoice in a receipt.

Let’s go back full circle to this point.

… while

I think you misunderstood the matching or referencing concept.

In my suggestion as well as in other software, the bank statement do not have GL entries. They are not part of the transaction, they’re just additional documents. Think of it like it’s adding an attachment but in a more dynamic context.

The receipt/payment/transfer will still be a 100% self contained with all of it’s lines intact. It will only be referenced in a line in the bank statement.

@lubos and @Patch. It was my fault that I wasn’t understood correctly the first time. :slightly_smiling_face:

Let me boil down my request to the bare essentials.

  1. There’s no need to rename Bank Reconciliations tab.

  2. Uploading statements and importing transaction need to be split to allow the user to do manual work that Manager doesn’t do automatically.

  3. Uploaded statements lines will be stored as orphan lines (with Zero effect on GL) inside the bank reconciliation object.

  4. The user will be able manually change a receipt to transfer or a payment to transfer. (Optional step)

  5. The user can also reference a receipt/payment/transfer (which are still complete and self-contained) in the lines of bank reconciliation. These lines will be ignored during import. (Optional step)

  6. The user can then import transactions that are not referenced in the bank reconciliation in the same way Manager does right now.

  7. The system will reference the newly imported transactions in the bank reconciliation.

For users who completely rely on import the process will go like this: 1 → 2 → 5 → 6, skipping the optional steps.

Advantages:

  1. No change to how transactions are stored and structured.

  2. Bank statements are stored within manager. No need to go to a spreadsheet or PDF.

  3. Manager can now attribute bank discrepancies to specific transactions.

  4. Users can manually change a transaction to a transfer before import. Despite the sufficient rules, many banks do not give sufficient descriptions.

  5. Users can now avoid duplicating manual entries during imports.

  6. Deleting a reconciled transaction will have the same prompt like when deleting an invoice that was received.

  7. Backwards compatibility

I think it’s important to decide for each bank account whether you will be importing or manually entering. No mixing. Is this something that you actually need? If not, let’s move onto the second advantage.

You are right however this puts too much trust into statement transactions being correct and complete. Right now Manager simply asks for your closing balance and that’s the truth which can be easily verified and easily updated if incorrect.

Yes, it cannot identify discrepancies down to single transaction. It can identify down to single day which I assume is good enough.

If Manager would contain actual statement transactions with some matching workflow, what if calculated statement balance is not the same as on actual bank statement? Where do you even start looking for such a discrepancy? Because accounting system would give you no assistance. It would claim everything is reconciled with a huge green tick. Sure this is not what you want.

Here are some real-world examples how this is a problem in Xero which is doing what you propose.

I had 4 bank transactions that were not imported into a clients file, however the bank account still appeared to be fully reconciled. It was only when I looked (by accident) at the imported statement balance and then at the real bank statement, that I realised there was an issue. The bank appeared fully reconciled on the Bank Reconciliation report because I had “reconciled” all transactions that WERE there - obviously I did not reconcile the transactions that were not there! There was no warning/indication there was any issue - which I must admit does concern me.

Or

Well, it was through checking my actal Bank statements with Xero statements that I understood the problem! But I don’t quite understand why I shouldn’t worry if I can’t rely on Xero statements - do I have to check every day/week? Not quite what I was expecting.

And by the way, this is not just these two people. There are many topics on their forum with this problem [1] [2] [3]. It’s a big issue where accounting system claims the bank account is reconciled even if it’s not.

1 Like

That’s pretty limiting, don’t you think?

I admit it, this is a problem that could happen, but, Xero don’t have you @lubos as a team developer. I am sure you can figure it out.

Anyway, what I am doing is I am doing the matching part manually in a spreadsheet and then I import the unmatched part only. It’s not that difficult to achieve, I will give you that.

However, the problems that I can’t solve in current setup are these:

  1. Users with access to receipts and payments can delete reconciled transactions.

  2. After importing, I have to manually delete receipts and payments and later post inter-account transfer. I can include this in my spreadsheet but this adds to the list of manual work.

I also just came across athis new post, which also can be solved by keeping the reconciling part with current statements/reco instead of within the transaction.

For now I will have to pull a tactical retreat on this one, but I still have hope that someday you’ll change your mind on this.

I agree it would be a mistake to not specify the absolute running total. I don’t want to remove ease of use features from Manager.

However that does not mean continuing to utilise all of the bank transaction data would not be of significant benefit to users.

By the way, we are discussing the easy bit here, ie would it be of significant benefit. The hard question is what priority to put on the feature.

I think requiring such a decision would be a big mistake. When a bank account is opened, a business often has no idea what the future holds and how the account might be used. Needs and circumstances change. Business environments change, making one approach or another more attractive at any point in time.

3 Likes

This could be solved by implementing a lock on reconciliation. This would prevent making changes which could make already reconciled bank account unreconciled.

I agree this is a problem and basically the reason why this topic has started. There is going to be workflow which will allow you to easily convert matching receipts and payments into inter-account transfers.

1 Like

On rereading this thread I believe I’m envisioning a slightly different function to what @Ealfardan had described. To explain.

The Bank import system

  • Retains all bank import records unchanged by Manager or the user. Probably accessed via a bank import tab where new bank imports can be created and the user can drill down on existing / old bank imports.

  • Progressively match of bank import transactions to Manger transaction occurs from most specific match to least specific match. The matching is done dynamically when the bank import is viewed (no static links between bank import transactions and Manager transactions are created).

  • By default only significant differences between Manager and the bank import are shown. As a result the screen mapping bank transactions to Manager transactions would look almost the same as the existing screen (where bank rules are applied).

  • When Manager imports a bank statement, Manager does not automatically create transaction in the suspense account. Transactions in Manager are only created when a bank rule batch update is done or a a bank entry is “edited” (editing a bank transaction would clone it’s contents to a Manager transaction so the user is actually creating a Manager transaction not editing a bank import).

By progressive transaction matching, I mean dynamically link in an order something like

  1. Same amount, same cleared date and same description
  2. Same amount, same cleared date and Bank rule account on bank import = Existing Manager transactions COA account.
  3. Same amount, same cleared date and similar description
  4. Same amount, same cleared date

The matching then repeats the above matching sequence but compares bank statement cleared date near Manager transaction. As a result importing the a bank statement should enable batch update of bank clearance date.

The functionality the change offers is

  • Allows users to use bank import, Manual transactions entry or an optimal mixture of both
  • Allows asynchronous entry of linked “bank” imports. Such as bank transfers, credit cards, epfpos, POS systems
  • Automates auditing of bank records
  • Assists user in bank account reconciliation, as transaction differences as shown and full context / bank import can optionally be shown.
  • The cumulative total shown in the existing “Bank Reconciliations” tab should be retained for those who don’t use bank imports and to ensure bank imports cover all time (none are missing).

I read this discussion carefully and agree with many that this is too restrictive. However, many solutions offered here address what is essence a simple issue, i.e. how to have different ways of entering receipts and payments without duplications and that reconcile with the bank statements and assigned accounts?

Practically there is not much wrong with importing bank statements, except complains on format of files, and the application of receipt and payment rules that makes this process easy and quite painless. However, many would like to have entered receipts and payments that allow for details of what was purchase, sold or transferred, including attachments on a daily basis to get a better feel of what is going in and out and an almost accurate financial status. The real problem is thus how to find the duplicates and ensure that the bank statement information (such date) and reference is retained while the purchase, sales or transfer details of the manual entries. This currently is time-consuming and prone to mistakes. So the question now is what kind of automation is required to make this process much easier and automated. The solution would be kind of search and merge function where one has to approve each of those.

1 Like

But this means that you can still delete transactions that has been reconciled.

Also, this doesn’t save the reconciliation work. @lubos thinks that reconciliation is something magical but I insist on that the only acceptable documentation for bank reconciliation is matching or at minimum linking. What else could you show for it?

I can name at least two benefits from such linkage:

  • The system can now calculate true discrepancies down to the single line. What lubos proposed regarding discrepancies down to a single day doesn’t help for retail businesses using contacless payments.
  • Users who don’t have access to bank imports tab will not be able to delete linked transactions.

@lubos didn’t give me a satisfactory answer as to why he is religiously opposed to linking bank transactions to bank lines. This boggles me. @Patch could you please explain why you think static links are bad?

That’s more or less what I wanted to achieve. However, I suggested one more thing, the user can also change the type to be created to identify transfers where it’s not possible for a rule to capture that. For example, some bank don’t show account details or user descriptions, they’d only give a useless serial number. In that case, the user should be able to tell manager that this is a transfer not a receipt or payment.

Those are my only two notes on your suggestion @Patch. I’d be really happy if this gets implemented.

But the icing on top of that would be to:

  • Identify descrepancies
  • After doing your work on the bank import tab, what’s the use of having a useless bank reconciliation tab? It should be kept in report at best, if at all.

@Ealfardan, indeed! Permanently linking each transaction of an imported bank statement to payments, receipts and transfers would be a way to resolve the issues. This would have the benefit of having a record of all imported statements, maintain the use of receipts, payment (and if possible newly transfer) rules and reconcile easily. If done properly the linking should allow linking to already manually entered payments and receipts.

1 Like

Correct
Because the intention is for the user not to need to do any reconciliation work manually at all.

Also when matching really matters is when there are some pre existing transactions and the bank statement is first imported. At that time there are no user links. So getting Manager to respond well then will mean it will respond well later when the bank import is reviewed also. As s result there is no need for manual link creation or maintenance.

So this will not provide any documentation of bank reconciliation procedures. Which means I will have to keep relying on spreadsheets.

I can’t see how this will help the import to ignore pre-existing transactions but I am curious how. I would really appreciate if you could you explain more?

I am astounded by this statement. The Manager reconciliation report does not (and never has) displayed enough information for a supervising officer to properly verify that a reconciliation is accurate. There is scarcely any information in the reconciliation report, and noticeably absent is the total debits and total credits of reconciled transactions to be verified against the total credits and total debits on the bank statement. Every bank puts these total debits and total credits on their bank statements for a reason. It is a very first check on the accuracy of a bank reconciliation.

Take this scenario:
The banking officer of a business is responsible for banking the daily takings, but every now and again removes the cash from the takings and only banks the remainder. To compensate for this the banking officer enters on the business books the correct daily takings amount ( as if the cash was deposited) and enters a bogus payment for the corresponding amount of the cash that has been removed. The bank account will reconcile but the total debits and the total credits will not match to the bank statement.

Manager’s bank reconciliation report does not show this discrepancy, nor any of the transactions to highlight where the error might be located.

So, I agree with comments regarding the inadequacy of the bank reconciliation in this thread.

1 Like
  1. some transactions are entered prior to bank import by any means for a period of time

  2. Bank import done for that period of time

  3. Manager used a progressive matching algorithm to match transactions entered. Transactions not entered and transactions already entered in error are show.

  4. User utilises Bank import data as template to create Manager transactions for data not already entered and corrects any data entered in error.

  5. Drill down on the bank import after user processing runs the same matching algorithm and again shows only transactions mismatches by default, however now will show no miss matched unless the user miss calculated when splitting bank transaction to a multiline Manager entry or the user intentionally created transactions without bank record support such as @AJD example or net zero value used instead of a journal entry.

  6. An extended time period expires, someone unlocks old records and inadvertency changes the net amount of an old transaction or delete a transaction or adds a transaction dated in the wrong period. The change in running balance is alerted in the reconciliation tab. Revisiting the bank import will show the miss matched transaction pair or transaction (if one was added or deleted).

In summary, because code is written to do step 3. when only step 3 data is available then the same code can be used for step 5 & 6

Ok, now I get it. It’s going to be a mixture of auto matching and user matching. However, the matching will not be saved and the process will have to be repeated on all entries whenever there’s something wrong.

I still think static linking would vastly improve upon this because:

  1. As @AJD said – and shame on me for missing this – my staff are going to do the reconciliation and I will have to review it somehow and so does every other supervisor (no to be confused with Manager :grin:). Where’s the documented work? A superficial report isn’t going to do. Matching must be saved because it’s the only adequate documentation of work. Static matching/links solves this issue.

  2. In case of lacking information (which is way more common that one might think) especially when a large number of amounts are the same; say in retail restaurants where many orders have the same amount and customers pay using a cross-bank portal without writing any descriptions. Manager’s auto matching – just like every other software’s – is guaranteed to be wrong. Having manager redo it’s thing again will only make things worse. Static matching/links solves this issue. You will only redo the matching for those with no static links and leave the rest alone.

  3. Manager will never be able to identify mismatched amounts without static matching/links.

  4. Deleted bank transactions are completely avoided when another tab (Bank Imports) is referencing it. The user must have access to that tab to delete it and even then they will be prompted to avoid deletion by mistake. Static matching/links solves this issue.

  5. Currently reconciling locked transactions is impossible without unlocking previous periods and this isn’t good. The developer suggested introducing a separate lock for reconciliation, which is unheard of before this instant and it’s just a workaround not having adequate reconciliation procedures. Static matching/links solves this issue.

These problems are all symptoms of lacking bank reconciliation capabilities, namely static matching/links.

What boggles me is that the developer would rather go out of his way to introduce individual solutions to each symptom or ignore the symptom altogether (like #3) rather than addressing the underlying cause.

I don’t see any substantial technical costs or difficulties to store bank statements separately from self-contained transactions and link them together and this boggles me even more, especially when you consider that static links are the least costly fix brought up in this post.

Just as a side note – and I am nitpicking here, I prefer the term “Bank Statements” better than “Bank Imports” because it describes the object/document rather than the action. The action button inside should be labeled"Import". This is more inline with the nomenclature of Manager but that’s no big deal.

I have done enough ranting for a month to come. :grin: So I will just have to shut up and get back to reconciling my banks in excel.

Close
It’s going to be a mixture of auto matching and user matching editing / data entry.

Go to the bank import tab, drill down on the bank import. Manager will show you any bank import transactions for which a matching Manager transaction can not be found. No records shown indicates your job is now done.

Managers Matching would show the miss matched pair on orphaned transaction. More general context could be shown by showing more of the bank import and matching.

Which identical transaction was in error and what needs to be done about it will depend on what the error was not what identical bank import template was to generate Managers records.

Overall this looks better than I expected, in fact this method would be tidier and more manageable but I am still not sure this is going to do as good of a job as matching. But I see great potential for a mixed approach.

There’s going to be lots of false matches especially with larger statements especially when there’s lots of similar amounts and lacking descriptions.

One would assume that picking a match from a filtered drop-down would be less work than editing and data entry.

This is both the best thing about your approach as well as the area where I have my biggest issues:

  1. This is negative confirmation is a dangerous method to confirm the accuracy of such a critical task as bank reconciliations. A lazy accountant could just edit the bank statements such that only the total discrepancy is imported and no one is going to be the wiser; how could they, all proof is now gone. That is, up until a customer complains of being overcharged in his statement. Then you will face the wrath of that customer as well as the other customer to whom his statement needs upwards corrections. The same thing when the owner finds out that they’ve been overpaying a supplier because their payment records had some plugs to round off the bank reco. This isn’t hypothetical by any means I’ve been in many similar situations when I was using Tally which uses negative confirmation as well. The lazy accountant would most likely need to split a few bank lines and edit a single pre-existing transaction to achieve that.

  2. For false matches, there’s no way to back track and edit. You will have to undo and redo.

That’s why I’d rather have the full matching documented because 99.9% of the time the total discrepancy wouldn’t match any single figure that hasn’t been match so it would definitely show. I’d rather have that than a blank screen saying nothing is pending.

I will give you that but then again it’s going to be a lot of work for the user to edit and enter all the data needed for manager to start suggesting matches. Selecting from a drop down sound a lot easier especially as you scale up.

There is a larger variability in the work flow different user follow when using Manager.

I have tried to suggest an addition to Manager which

  • Requires no addition user work. Only identical user tasks are required. New links do not need to be created or maintained.
  • It does not constrain valid user tasks or data entry.

The only change is adding new reporting based on data users are already importing into Manager.

Doing so involves forging the bank import prior to it being imported. The implementation I suggest has no facility to change imported bank statements in any way. They are only used as a reference for reporting, and template to generate Manager transactions

What is a false match?

The report will correlate transactions and show just what does not match. You then have very specific information on what is different between the bank records an Managers records.

The process Manager performs in the background is similar to the old paper method.

  1. Print out the bank statement
  2. Printout Managers general ledger transaction
  3. draw a line crossing out the easy entries in the bank and corresponding Manager entry
  4. Go back through the bank statements and Manager entries crossing out less specific entries.
  5. Eventually you have a small list of entries which can’t be matched. Sometimes a pair (mismatched date or amount), some times a duplicate or deleted entry (no matching pair).
  6. This is where Managers matching could get to.

Any automatic correction at this time would be dangerous. Human intervention is required to determine what is the most accurate representation of the true business financial positions, determine why the error occurred and make any corrections which are required.

No need to explain your intent @Patch. My post might seem like a negative criticism but it’s not. I really liked most of your method and I just wanted to point out that it could be improved upon by keeping the matching work. In fact I kind of like it a bit better than mine.

I just need the work to be editable because in my 15+ years of experience as an auditor as well as an accountant, I have seen a lot of fishy business in reconciliation. In fact I spent 1 whole year fully hired by a client to reconcile their receivables and ended up reworking their bank reconciliations.

If you have a matching table it’s easy, just lookup the statements from the system to a fresh bank download and then see if the matching is 1-to-1. You only need to examine those matches that aren’t 1-to-1 or those that match the amount of an unmatched transaction. Pretty limited scope of work.

You don’t even need to lookup the entire statements if the system keeps track of manual edits. You just examine those.

On the other hand if you don’t have a matching table, there’s absolutely no way to review or audit it without reperforming the entire exercise because you just have nothing to compare.

What I suggested here is linking instead on many-to-many matching because it better suits the structure of Manager.

I suggest that we use you method but allow for static linking and keep records of it. Maybe on the view screen you get the orphan lines only but the edit screen should allow to dig deeper in case you need to.