Bank imports and transfers

[peterb]

@Tut

And what happens when the other half of the transfer is imported from the other account? Now you have a duplication.

In GnuCash, QuickBooks or Xero, the transaction is (usually!) recognized as a match, and no duplication is entered. I’m sure there are times when people (or the algorithm) make mistakes, and in those cases you will typically catch the error in reconciliation or on review.

Then prove it with something besides an assertion unsubstantiated by fact. When have you ever made the same deposit in two bank accounts at once? When have you ever received a check drawn on two bank accounts?

Perhaps this is the root of our misunderstanding? In my example above, the other account is the other end of the transfer; it is the source (or target) of the receipt (or payment). I agree with you that the same deposit being deposited into two bank accounts is nonsensical. But a deposit being deposited into a bank account as the result of a transfer coming from another bank account is perfectly common, can and is often reflected by a single double-sided entry, and should be able to be handled with ease as part of statement upload.

[Patch]

@Tut

And what happens when the other half of the transfer is imported from the other account? Now you have a duplication.

No
What happens during import of the other bank account is already entered transactions are matched. Each existing transactions must match one of the imported transactions, if not differces should be shown. The matching should be progressive, aligning more exact matches first.

You are correct Manager is not currently strong in that area. The major limitation with Manager’s current approach is it discards the bank import concept at the import dialogue box then throws the transactions it has selected into the suspense account.

Manager’s bank import would be much more powerful if the bank import construct was maintained until after the transactions have been assigned valid Manager accounts. Missing / extra transactions on either side (external bank or Manager records) would then be displayed.

[Ealfardan]

I too agree that bank import/reconciliation needs improvement, namely the matching part. But I don’t want the process to be overcomplicated. I don’t know about Gnu Cash or Xero but I am pretty sure I hate reconciling bank in QuickBooks. It’s too slow, too many menus, too much visual clutter, too many transaction types, none of them seem to fit the pegs and mistakes are very costly to fix.

That out of the way, there is a simple solution for the matching problem. Manager already matches previously imported transactions so this means that Manager has a built in mechanism for identifying those. What if the user can supply the same information used in that mechanism like:

• Bank description in the description field.
• Clearing status and date
• Amount

Manager can then use these information to identify matches that were manually entered as if the have been previously imported? That seems possible.

To take things a step further, I already submitted a wordy proposal for alternative bank import/reco workflow that seemed to have been ignored by most for being tldr – much like most of my posts . You can check it here. This is pretty much what @Patch suggested here:

@Patch

Manager’s bank import would be much more powerful if the bank import construct was maintained until after the transactions have been assigned valid Manager accounts

As for inter-account transfers, I don’t like them. They’re like the ugly duckling in Manager. The clearing account process seems more promising.

What if we were able to select the Receipt/Payment Payer/Payee to be Transfer so the new list becomes (Customer, Supplier, Transfer, Other) and then just copy the receipt to a payment or vice versa. This should link the two. Any unlinked transfer receipts/payments should have a status or a tag that can be used for follow up.

[eko]

@Ealfardan

As for inter-account transfers, I don’t like them.

We actually do. We have so called POS Cash (from POS system) monthly totals that we import into POS Cash Account. Some of this cash is deposited into a Bank Acount while some is used for cash transactions. The deposit show in the bank account and we then assign the bank statement import part of that to an inter account transfer. This way both our cash account and bank receipts are correct.

[Ealfardan]

So does petty cash reimbursements. But that’s not the only way to do it. A receipt/payment pair to a single clearing account achieves exactly that.

[Patch]

The core changes suggested are

1. When a bank import is done, Manager retains all the imported records as a bank import. This is similar to when doing a bank reconciliation, Manager doesn’t throw away the reconciliation value after it is found to match.

2. Manager uses progressive matching (from most exact to least exact) to dynamically link bank import data to existing Manager transactions for the bank import period.

3. Manager shows the bank import hiding all transactions which match by default. This display would mostly look the same as the existing bank import display.

4. This display would however also show any existing Manger transaction for which a matching bank import could not be found (ie failed matches in the other direction).

5. There should also be an option to show transactions Manager has matched so users can check the automated matching if they believe there maybe an inappropriate match.

With this in place, a bank transfer becomes just another pre existing Manager transaction. Showing in bank transfers would be nice and still possible in my opinion.

[Tut]

@Patch

What happens during import of the other bank account is already entered transactions are matched.

You overlook one critical factor. Because the earlier import will be, say, a receipt and the later import will be the corresponding payment, there will be no match. That has always been the root of the problem with converting imported transactions into transfers.

All the examples given require manual intervention to furnish the necessary information to complete entry of a transfer. Squeezing it all onto one screen like QB does not inherently make it easier than Manager’s approach of having a dedicated screen.

[Patch]

@Tut

one critical factor. Because the earlier import will be, say, a receipt and the later import will be the corresponding payment

you are correct a bank transfer results in a payment in one account and a receipt in the other. That is what Manager would expect for a bank transfer so it would match.

@Tut

All the examples given require manual intervention to furnish the necessary information

correct, virtually no change was proposed to that screen. However after it has been finished, then for a particular bank import, Manager will have enough hints to repeat the match for the same bank import, hence not further user input required when reviewing a past bank import (unless the user changed the transactions elsewhere, which Manager would then show).

[eko]

This can not be treated as a petty cash account. Petty Cash Accounts get replenished while the POS is an income and expense affair similar to a bank account. However, a mentioned we deposit part of the cash in the bank account as many suppliers and customers here only accept cash, not card nor transfer nor even have bank accounts.

[Tut]

@Patch

That is what Manager would expect for a bank transfer so it would match.

To my my way of thinking, @Patch, what you describe asks too much of any program and offers too many opportunities for failure. In effect, you suggest that, because you previously imported a receipt in one bank account for $100, Manager should recognize a later payment from another account for $100 as the matching leg of an inter account transfer. I have trouble with that for two reasons:

• When the first leg of the transfer is imported, there is no match available. It has not been entered. That is why you need a clearing account for temporary lodgment.
• Receipts and payments could have identical values for lots of reasons besides being two legs of the same transfer. What happens when you import a statement from a third bank and have another $100 payment? And what would the program do if that were the actual matching leg, but the other, from the second bank, has already been erroneously identified as part of the transfer?

@Patch

However after it has been finished, then for a particular bank import, Manager will have enough hints to repeat the match for the same bank import, hence not further user input required when reviewing a past bank import

Here, you seem to hypothesize about irrelevant scenarios. You say, "…after it has been finished, then… Manager will have enough hints to repeat the match for the same import. [Emphasis added in all three cases.] But my point is, Manager does not have enough information during the import. And, in general, you are not going to import the same statement again, so there is no benefit to being able identify a match when the need has already passed. Further, the lack of a requirement for user input when reviewing past imports is not helpful. The impediment to identifying transfers is encountered when importing in the first instance. In other words, it is all well and good to say that after the fact, after you manually intervened in what is supposed to be an automatic process, after you furnished all the missing information, you can review the successful result. But so what? What have you gained?

[peterb]

Since there seems to be some appetite (and I guess need) for me to “prove” that this is possible, and since the example from QuickBooks wasn’t enough, let me give an example of this workflow in GnuCash.

I want to be crystal clear: I think that focusing on the details of the UI in these other apps is completely beside the point. Manager’s developers should follow their own UI principles and make the UI how they want it to work; they’re under no obligation to slavishly copy the UI of any other program. What I think IS at issue is this idea that categorizing transfers during import is somehow black magic, or impossible in accounting, or even particularly difficult. It’s not. It’s boring, standard, and should not be surprising to anyone. Pick whatever UI you want around this feature, but please just support the workflow.

Here’s a (trivial) statement imported into a savings account. I choose the file to import and am presented with the two transactions in that file:

The transaction in green happened to have been entered, manually, and thus has already been matched (worth pointing out: the description of the transaction in the bank statement and the transaction entered by hand differ! The Bayesian classifier, though, had no trouble correctly identifying this as a match; in Manager this would have been incorrectly imported as a new transaction). The transfer in yellow needs to be categorized. If I were to click “OK” at this point, that other side of that deposit would be put into GnuCash’s equivalent of the Suspense account. But instead, I double-click on it…

…and I’m asked to choose what account should be used for the other side of the entry. I do so and click OK, and…

…the transaction is actually put on the ledger (and it is not on the ledger UNTIL I click ok.)

And of course a matching entry has appeared on the other ledger (I realize the screen shot looks pretty much the same - that’s the point - but believe me, I navigated to the register for the counter-account to take it.)

If I were to import a statement for the brokerage account next, GnuCash would trivially recognize the matching transaction for the transfer as a match. GnuCash uses a Bayesian algorithm to determine when transactions match; it is certainly not perfect, but in practice is pretty good; I generally only have to touch about 5% of my imported transactions.

I don’t have access to MoneyWorks from the desk I’m at at the moment, but within a day or so I’ll post a walkthrough of how that app enables a similar workflow (although of course it’s different in the details.)

[Patch]

@Tut

When the first leg of the transfer is imported, there is no match available

Correct.
A bank rule is set up which detects such (say payment) during the first bank import as a bank transfer which results when accepted in

1. A payment in the first bank account
2. A Receipt in the second bank account

Then when a bank import is later done in the second bank account, the already entered receipt is detected so not re-entered.

@Tut

Manager does not have enough information during the import.

No user change during import is suggested other than Manger matching already entered transactions and showing already entered transactions which don’t match in the context of the bank import.
We agree user input will continue to be needed to achieve this phase.

@Tut

after you furnished all the missing information, you can review the successful result. But so what? What have you gained?

The same function as retaining old Bank reconciliation.

• Should later changes result in reconciliation going out of balance then there an error has occurred and is highlighted.

• With retained bank imports, the bank transaction in error would be listed by Manager. So would be very useful in my opinion.

[Tut]

Thank you for the illustration, @peterb. In my opinion, what you showed is that GnuCash requires more work than entering an inter account transfer in Manager. It seems simple to you because you have been using it. But it is nowhere close to automatic.

I’ve made my points repeatedly. So I am going to bow out of this discussion.

[Tut]

All fair enough, @Patch. We have different preferences. As my final comment, I think it’s worth noting you and @peterb are advocating for different things. But, as stated above, I’m now out of this conversation.

[peterb]

Fair enough. I’ll continue to give examples from other apps just to provide context. As I said, the issue isn’t the amount of work; the issue is whether the workflow is supported at all. In Manager, one can only classify an imported transaction as a transfer by either (a) using an unnecessary clearing account(†) or (b) deleting the imported transaction and creating a new one. Each of those paths substantially complicates the audit trail, which is why I view it as a structural weakness. I’m fine if we disagree about which is easier, I just don’t want people to be left with the impression that doing better is impossible or forbidden by accounting principles.

†: For bookkeepers that want to use a clearing account, (a) is fine. For bookkeepers that don’t, requiring it just to make the software happy is a substantial complication.

[peterb]

Here’s the MoneyWorks flow to categorize an imported transaction as a transfer.

Load the file:

Review:

and just type the account the transfer is coming from into the Account text box:

And then accept the import.

There’s just no comparison here. Doing the equivalent workflow in Manager (without clearing accounts) involves many, many, many more steps.

I hope this helps explain the workflow you weren’t understanding: nearly every accounting program that supports importing statements allows statement transactions to be easily categorized as transfers, without the use of clearing accounts.

It’s fine if Manager doesn’t support this yet - development cycles are limited - but please don’t kid yourself that the workflow doesn’t exist.

[AJD]

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

[peterb]

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:

[peterb]

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

[Ealfardan]

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.