Bank imports and transfers

This only shows one of the bank imports. What happens when the second bank import is done and the transaction already exists?

1 Like

I’m not sure how MoneyWorks would deal with detecting matches; I haven’t used it much, honestly - but I’ll create a new test file sometime later in the week and give it a go.

I spot checked Sage Business Accounting (which I also don’t use much) and it works similarly to QB and GnuCash, though not quite as nice. On importing the first statement, you can classify things as transfers…

On importing the counter-account’s statement, you are asked to explicitly say if you want to try to match it (so presumably human error could make this go wrong, but then, that’s always true of everything.) On choosing “Match”, it did correctly suggest the appropriate match:

Anyone here an active Xero user who wants to run the test there and see how it handles this situation?

I think I get you better now, you were asking to better integrate transfers into bank imports so that it stops being the ugly duckling. And then the other leg should be matched and ignored during the import of the other bank.

Sounds fair enough. However to do this the transfer would have to consist of 2 separate transactions: one is equivalent to a receipt and the other is equivalent to a payment both charged to the same clearing account. Not manually but automatically off course and all of the cleaning stuff is hidden. But this would allow the transfer to appear in two banks.

As for the matching, the matching criteria and fields should be exposed to the user so that manual entries can be identified in bank imports.

I have been following this topic with interest and the one thing that struck me is that the people against the idea are actually against it, because they don’t see how it would work. That’s not their job - that is the job of the developer - to see if he could make it work!

I am not aware of anyone who thinks the current way of managing bank imports and transfers between internal accounts is a great way to do it.

@peterb I have just replied to the topic regarding bank imports and manual entries. I would still recommend a direction where you don’t enter transactions manually - just use bank imports, combined with bank feeds. This automates the work and with the live feed you get an up to date status of your current cash status. So I still advise you to pursue this direction. Entering transactions manually - when I was doing it, was error prone and time consuming to reconcile. I would never recommend manual entries as the best way to go. However I do acknowledge that the lack of a bank feed is problem with just using bank imports.

With regards to this topic, I fully agree with you. Using Bank Imports to manage transfers between two bank accounts in Manager is really poor and confusing. I don’t like it and it is a poor way to audit anything - if you have several bank accounts.

The key takeaway point that I am seeing here is nobody actually likes the current way that Manager handles bank transfers and bank imports. But users objecting to your suggestions are really objecting on the basis of the fact that they don’t see how it would work in practice. I would respectfully state, that is not their business nor problem. The developer has the necessary technical and accounting skills to know if something better could be implemented and how practical it would be.

I would suggest rather that instead of going round in circles, users simply acknowledge that the current bank transfers and bank imports design is clunky and ask the developer if he could find a better solution. Then point to some examples of how other programs deal with this issue - why re-invent the wheel?

As long as bank imports remain simple to use (as they currently are), I am not opposed to any suggestion that would improve bank transfers when doing bank imports. I agree with users who are against changing that is a difficult problem to solve, however they don’t need to worry about how to improve it. Leave it to the developer. The key point everyone agrees on is that the current design of using a clearing account is not a good way to do it. Make the developer aware and he can find a solution.

It seems that everyone is facing the same issue:

I just copy the four columns and their data from the original Excell Worksheet and Paste Special Values only into another Worksheet. I then save as Comma Separated Values (csv) and import into Manager.

Actually as long as I’m making comparisons, Sage did one thing that nothing else I’ve seen did, which I thought was a nifty quality-of-life feature, related to this thread

The very first time you import a bank statement into a bank account, Sage picks a random transaction from that account, displays it in a pop-up window, and asks “Is this transaction money going out, or money going in?” (The actual text was longer than this, and it gave 3 checkboxes to choose from, which I don’t quite remember.). From this, it’s inferring whether the positive and negative numbers on the statement transactions represent inflows or outflows, thus neatly solving the issue that different banks (or credit cards) in different countries may use different conventions for representing things. Once you’ve answered this question, it never asks you again (for that account).

Finally got around to checking the bank import workflow in Xero. It’s very interesting; similar to the other major players but with even more polish.

Xero has a very opinionated workflow, but it’s pretty easy to understand. Reconciliation is done on a per-transaction basis (so this is a different meaning than used in Manager, for example). The reconciliation flow is exactly the same whether the item came from your bank feed or via importing a statement.

If you click “Transfer” above, you get a simple UI to convert the transaction into a transfer, as you’d expect. You can choose “match” manually if you want, but in practice you never have to - every time so far I’ve imported a statement where I’ve already reconciled the other side, it has preselected the match for me, and I just have to click ‘ok’. It looks to me like only reconciled transactions are eligible for matching, which makes sense.

Other cool things:

  • The source of a transaction is remembered; so for any transaction on your books (at least for a bank account), it tells you if it came from a bank feed, from an uploaded statement, or from manual entry.
  • If you import duplicate transactions and delete some, it remembers the ones you deleted and has a view which shows you which one you kept, and where the deleted one came from:

The UI principles at work here are pretty sophisticated. If I had to boil them down to a few sentences they’d be

  • Defer items really coming onto the books until a human validates them.
  • Make it easy to undo mistakes.
  • Provide a comprehensive audit trail
  • Put all of this in one place - all of this is wrapped up in their ‘reconciliation’ concept - you’re never going to different tabs to try to clean things up. It’s always just the list of transactions for that account. (EDIT: I’m overselling this one a bit - all of these things are contained in the ‘reconciliation’ flow for a bank, but there are actually 3 tabs: Things that need to be reconciled, accepted transactions, and “statements”. I’m still unclear on the difference between the last two tabs - it feels like ‘statements’ is specifically the imported items but I’m not sure yet. Will poke at it some more.)
1 Like

That’s pretty similar to Odoo’s process and it looks neat.

In order to achieve this, Manager needs to entirely drop the cleared status and cleared date in favor of a matching reference. Also, the Receipt rules and Payment rules need to be dropped in favor of unified bank rules like the good old days. The new bank rules should accommodate receipt, payment and transfers.

Now I have an idea on how such concept is to be implemented following manager and it looks pretty good to me.

I will get cracking with Paint and update this post with illustrations as soon as I have them ready.

1 Like

Oh one other thing not related to bank imports that jumps out at me about Xero: the distinction between Customer and Supplier is determined implicitly and it’s fantastic. Anything you enter in a payor or payee of a receipt or payment is added as a contact; you can of course add more details in the contacts list, and you can add contacts manually. You never (and in fact can’t!) declare a contact to be a customer or a supplier. If you send an invoice to a contact Xero automatically understands that that is a customer; likewise if you receive an invoice they’re a supplier, and a contact can be both a customer and a supplier.

1 Like

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.