Automatically combine transfers between my own accounts

Question

I am using Gnucash for personal use, as a new user, and whilst I have managed to arrange my accounts as I wish I have an issue with importing my bank transaction data.

With my bank I have a set up with three accounts (1 joint, 1 checking, 1 savings) between which I make many internal bank transfers (e.g. 1 every 2/3 days). Let's call them "Account A", "Account B", and "Account C".

When I import my bank data I thus end up having many transactions of the type:

Account A: 01/01/2022, A12345678 Bank Transfer, +$100, Unspecified

Account B: 01/01/2022, A12345678 Bank Transfer, -$100, Unspecified

I.e. Gnucash has not been able to identify that these are clearly the same transaction and thus records the same transaction twice from each account's point of view (one from each data file from each bank account - that's how the bank gives it to me). Note, they have the exact same description string including identical serial number, date and [negative] dollar value, and I import all the data files at once.

What I really need is a single transaction of type:

01/01/2022, A12345678 Bank Transfer, $100, Credit: Account A, Debit: Account B.

But I don't know of any way to automate this across all such transactions.

Currently I have to go through all such transactions manually, change the transaction in Account A to

Account A: 01/01/2022, A12345678 Bank Transfer, +$100, Account B

and then delete the corresponding transaction in account B, or vice versa.

This is ridiculously time consuming when there are literally hundreds of such transactions, for what seems like should be a very common issue.

The closest answer I can see is this, but as far as I can tell this is just suggesting doing the above (i.e. going through it manually).

Does anybody have any better ideas?

Answer 1

One of the most important points in your scenario is "... and I import all the data files at once".

I think that your data imports will run more successfully if you introduce more steps into your process. What I'd suggest is:

1. (Optionally) pre-process your bank data for Account A.
2. Run the GnuCash import process on the Account A data file.
3. Do whatever tidying-up is needed to correct the imported transactions into their final form - for example, identify "Account B" as the source of a transfer instead of "unspecified".
4. (Optionally) pre-process your bank data for Account B.
5. Run the GnuCash import process on the Account B data file.
6. Do whatever tidying-up is needed to correct these imported transactions.

This should give you a better outcome because:

• By the time you've completed Steps 2 and 3 (imported the first lot of data and tidied up), there is already a recognisable transaction in the GnuCash database showing a $100 transfer from Account B to Account A. This is great preparation for importing your transactions for Account B. The GnuCash Import process has a Transaction Matching stage where it will effectively ask you "Is this $100 transfer out of Account B the same $100 transfer that I already have recorded for that date, or is it a new and separate transaction?" With one mouse-click, you can confirm that it is indeed the same transaction - and GnuCash won't create an unwanted new transaction.

• "Tidying-up" in Steps 3 and 6 is only necessary if GnuCash isn't provided with sufficient information in the data file it's importing. For example if your Account A data file contains the descriptor "A12345678" which reliably means "this is a transfer from Account B" then you can actually modify your data file so that "Account B" is identified as the transfer account. Similarly, any transfer out of your bank account labelled "Netflix" will inevitably go against your GnuCash expense category Expenses:Utilities:Streaming Services. It's relatively easy to embed that information into the data file before importing it. Whether you modify the bank data file using a Python Script, or an Excel Lookup Table, or by some other means, is up to you. The bottom line is that the more complete information you can provide to GnuCash in the import file, the less tidying-up of transactions you'll have to do later. Good pre-processing will ideally result in no need for any tidying-up at all.