Bank imports and transfers

I proposed to put this on the ideas but I think that it was rejected directly by @lubos.

I think I have an idea on how to improve reconciliation / import procedures without compromising Manager’s simplicity.

For those who completely rely on bank imports they will have 1 additional click of a button since upload and import will be split into two but it’s nothing compared to the advantages they will also receive in bank reconciliation/import improvements.

Quick summary for those who can’t be bothered to read the entire post :wink:

  • Bank Reconciliation tab will change to Bank Statement tab
  • Bank rules will be unified again and will include Transfers as well
  • Cleared on / Cleared date will be dropped. Instead, each transaction will be referenced in a line of the new Bank Statement edit form
  • Uploads of bank statements will be exclusively from Bank Statements tab
  • Users will be allowed to manually match transactions before importing or just skip the matching and import the entire thing
  • Users will be able to chose not to see the detailed matching process if their statements and books are matching, or in case they’re having trouble they can examine the new transparent matching inner workings and fix their errors completely within manager itself
  • Automatic matching of uploading bank statements will continue as-is with old import process
  • Automatic matching of manual transactions can wait if and when the developer decides it’s feasible

Details for those who are interested

The new bank rules are as follows:

These will shared by receipts, payments and transfers.

The new Bank Statements tab that will replace Bank Reconciliations:

The first option is to upload a bank statement which will:

  • Ask the user to upload file
  • Create a new bank statement in the “Bank Statements” tab with the lines imported and take you the View screen, which shouldn’t be any different from what we currently have in “Bank Reconciliations”.
  • No receipts, payments or transfers are created so far.

The top action buttons for the View screen of bank statement should include the following buttons/menus:

  • Edit
  • Clone (just to accommodate manual bank statement entries with no lines, so that users who relied on the previous method can still do it the old way)
  • Import Unmatched Entries. This will continue the old import, application of bank rules and categorization process only this time, the import will exclude whatever has been matched by the user and the import will take into consideration the transaction types that the user selected in the Edit form which is as follows:

Note the following:

  • The lines and all their fields will not show on printed documents, so that we still get the old neat view mode similar to the old Bank Reconciliations.
  • the bank statement (previously bank reco’) entry form includes two dates: a Start date and an End date. This is because it contains the bank statement and not just the ending balance. This will be prefilled if the bank statements has been uploaded.
  • the bank statement now includes two balances: a Starting balance and an Ending balance to ensure that: (1) users can reconcile the old way without uploading anything – this is especially important in case the user has only scanned statements; and (2) to allow the users to make manual changes like splitting a bank line in case the bank grouped some entries – in my country some banks would group their own cheques under one entry for the day (e.g. Deposit Home CHQs) so the user cannot match this to multiple transaction so the user can manually split the entry himself by adding a new line and subtracting that from the original line.
  • There’s a balance check at the end similar to what we have in Journal Entries in order to accommodate for users’ manual adjustments.
  • The user can select the type of transaction of each line. For positive lines the user can choose either (receipt or transfer) and for negative lines the user can choose either (payment or transfer). These choices will be taken into consideration in importing of unmatched transactions.
  • Upon choosing the transaction type, a dropdown list will appear that include the pending bank transactions of that type for that particular bank account.
  • Once a specific transaction has been referenced, the Import Unmatched Entries will ignore that line.

This should keep track of everything except unequal matches. For that there should be additional status column in the Receipts, Payments and Transfer tabs to show the matched/cleared status. like so

For receipts and payments the statuses will be:

  • Cleared for perfectly matched transactions
  • Partial match for transactions matched with bank entries of different amounts
  • Pending
  • Blank for cash transactions.

For transfers an additional status of “partially cleared” status for transfers cleared in a single account but not both. Alternatively, there could be two statuses for Transfers.

If possible, the matching discrepancies can be shown in the status as well similar to how overdue invoices are displayed.

And the benefits are …

I stand to be corrected but although I don’t think this is exactly what @peterb had in mind but it checks many of the boxes for him without:

  • Compromising the simplicity of Manager
  • Bending backwards to have manager do relational database stuff (you know manager is an object or document database and trying to make it mimic what it isn’t might hinder its performance)
  • Affecting the workflows of users who will not benefit from the additions. In case of those who only rely on imports, the will upload as usual and that would take them to bank statement view screen and only then they can click on Import Unmatched Entries which is only one click away. For users who don’t import but still reconcile, you don’t need to import, just post the closing balance and you’re good to go if the figures are matching.
  • This method will ensure backwards compatibility since all the fields of the previous Bank Reconciliation document map to Bank Statements. In fact, they’re still there.

Also, this will enable the user to do both manual and imports and also improve the transparency and audit trail over bank reconciliations and import as myself, @Patch, @Davide among other users who would appreciate such improvements.

Finally, an additional benefit that would come as a freebie is that if you try to delete a matched transaction, you will be prompted with this little lovely message:

And then you can press Edit and remove the reference first before deleting it. In case the user has no business in bank reconciliation (and no access as a result) then this will prevent an idiot from ruining a perfectly reconciled bank account. :clap: :clap: :clap:

I too have had similar thoughts previously but now I’m leaning more towards what we currently have because:

  1. Allowing a single contact to be multiple things at once involves abandoning the mapping of his transaction to a single accounts. In Odoo I realized that it takes 1 untrained or careless user to create a mess of a single contact transactions that way. Dynamics you can map the contact to only a select few accounts and limit invoices to a specific receivable account but in order to do so, the mighty Microsoft couldn’t find a better solution than dimensional account codes which are another nightmare in and of themselves.
  2. Restricting contacts to a single control account better complies with the no offsetting rules by IFRS and other local GAAPs and regulations.
  3. Manager just doesn’t work that way. One of the reasons why manager is so performant is that it isn’t a relational database. This means that it doesn’t support enforcing complex dimensional relationships. It’s a trade-off.

Having said that, you reminded me to take down my request for unified contacts :grin:

That’s not true. In relational database you can have a many to many relationship. It implies that you will have an intermediate table.

But makes you breaking many other rules. For example that you should not net credit vs a client with debits vs a client but you should show both of them under assets and liabilities.

From what Lubos once wrote it is not a reason of performance but only that the structure is still WIP and so that he doesn’t want to normalize it to fixed tables. But it uses SQLite to store data so it uses a relational database.

But manager is not a relational database, it’s an object-oriented or document database. And this is why many request aren’t really feasible to implement like many-to-many relations, multi-dimensions, SQL queries, etc. It’s not a bad thing though, it is a trade-off after all.

Here here :point_down:

That must be why it’s impossible to store a single object inside two other objects without a strict hierarchy in place: one has to be the parent of another. This is equivalent to having the Accounts receivable be the parent of Accounts payable or the other way round which doesn’t make any sense.

I tried to research this and the more I learn about OODB and it’s advantages, the more I appreciate the trade-offs that @lubos made. For example you will not easily find another software with a magical Undo button like manager and that is because while relational databases keep logs, manager has document versioning which is way better. Anyway … I could be speaking nonsense for all I know. @lubos needs to confirm this.

That’s exactly what I meant by “no offsetting rules”. So actually @Davide, you and I are in agreement. :grimacing:

Other accounting systems have matching workflow not because they think it’s better - they literally do not have any other option.

In order to implement accounting system without matching workflow, you need to make sure that payment and receipt transactions can debit or credit any account in the system including all sub-accounts.

Typically, in accounting system, when receiving payment for invoice, you have to find that invoice and mark the invoice as paid on invoice itself.

So you record all these transactions involving sub-accounts in so many different places and then you need some mechanism to somehow match them to actual receipts or payments.

In Manager, matching is not necessary because payment and receipt can debit or credit any account including sub-accounts. This makes receipts and payments in Manager self-contained.

Example how single receipt can make entries into variety of accounts across the system.

And because receipts and payments are self-contained, this creates really nice benefits. First of all, if there is a bookkeeping error, you review each receipt and payment in isolation. In systems with matching workflows, you need to review a lot more because the transaction is recorded at multiple different places and then tied up together with some matching mechanism. It’s not unusual to throw away all the bookkeeping work, unmatch everything and re-do the entire period from scratch.

Also, by having self-contained receipts and payments, we can have really sophisticated bank rules so you can get to the point where your entire bank statement can be automatically categorized using bank rules without any workflow whatsoever.

And this is the key. Having everything automated. Why do you want to spend even one second of your time on matching? What benefits do you get in exchange? I see none.

I’m not sure why this topic even exists. My impression is that it’s because Manager doesn’t handle transfers between bank accounts. I do have solution to this and hope to implement it before end of this year.

2 Likes

I really hope so. Also, please, implement a more flexible import functionality for bank transactions.

That confuses me. I agree it would not be a good idea to change any of that functionality. I thought the suggestion was just to retain all the bank import data and show the miss matches in both directions (the matches hidden by default).

From my perspective I see these advantages

  1. Allowing users to choose to use both bank imports and manual entry based on what works best for individual transactions rather than forcing then to use full manual entry or fully bank import

  2. Allowing automated synchronisation between different automated data importing sources. Bank imports for two accounts with some inter account transfers is an example, credit cards, efpos machines, point of sale terminals could also use the same basic facility.

  3. Better account reconciliation. Currently a user can manually enter the running total at some particular time. If a subsequent entry / edit doesn’t maintain the running total at that point in time, then Manager can readily display the loss of reconciliation. Maintaining the full bank import would enable drill down to display the out of balance transaction pair (Manager transaction vs Bank import transaction).

So in my opinion Manager would be significantly enhanced by adding this feature. A much more difficult question is would Manager be enhanced more if similar work was invested in other aspects of Manager. From a selfish personal perspective I would value the ability to round interim values in country specific localisation and custom fields on COA accounts more. And adding data entry country specific localisations and multi component tax codes as a set of simple tax codes would probably increase Mangers penetration into other jurisdictions more. But I don’t know the bigger picture in Managers road map, nor can I accurately scope the work involved in these tasks so I can’t sensibly comment on priorities.

I’m not convinced this would be wise. Keeping them as separate tabs a business could activate based on individual work flow would make more sense in my opinion. They function differently both from a user work flow and programming perspective.

If the external entity interacts with a business in different ways then recording that fact would enable Manager to better manage a businesses iteration with that entity. Yes it is more complicated but that is because of what is happening in the real word.

So I think I still would like a unified contacts database. But that’s just my opinion.

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.