GnuCash Transaction Import Assistant - Loading Bank Statements

Question

Confused about setting up GnuCash file using the Transaction Import Assistant to load Bank Statements. Is there a GnuCash quickstart tutorial to cover this? Expecting to load the statements and the transactions would show in the source bank account and the target income/expenditure account. Any guidance appreciated.

Issues:

1. Haven't managed to configure the Transaction Import Assistant date format as dd/mm/yy like the Win10 Regional format data settings so currently restricted to d-m-y for csv import.
2. Destination accounts are processed as expected but all source transactions end up in the Imbalance account rather than the intended source Asset/liability account.

---

Test Data:

Cheque Account

Date,Amount,No,Ledger:Account-Category,Description,Memo
01-03-06,"1,000.00",,Equity:Opening Balance,Opening Balance,Starting Balance
05-03-06,-45.21,101,Expenses:Groceries,Grocery Shop,Grocery Store
05-03-06,-75.00,105,Expenses:Groceries,HomeTown Grocery,HomeTown Grocery
06-03-06,100.00,,[Savings],Transfere Money,Transfer
06-03-06,100.00,106,Expenses:Split,ABC Hardware,ABC Hardware
14-03-06,1000.00,,Expenses:Split,Employers R Us,Salary
24-03-06,500.00,,[Savings],Transfer Money,Transfer
25-03-06,-100.00,,[Cash],ATM Withdrawal,ATM
28-03-06,-20.00,,Expenses:Internet,Internet Subscription,Best Internet
28-03-06,-78.00,102,Expenses:Electricity,Light Company,Light Company
28-03-06,-45.00,103,Expenses:Phone,Phone Company,Phone Company
28-03-06,-350.00,104,Expenses:Rent,April Rent,Apartment 4 Rent
31-03-06,-5.00,,Expenses:Service Charge,Service Charge,Service Charge
28-04-06,-350.00,,Expenses:Rent,May Rent,Apartment 4 Rent
05-05-06,-300.00,,[Visa],Partial Payment of Visa Bill,To Account 123833255

Visa Account

Date,Amount,No,Ledger:Account-Category,Description,Memo
01-03-06,500.00,,Equity:Opening Balance,Opening Balance,
03-04-06,74.99,,Expenses:Clothes,Jeans R Us,JRU Holdings 301
05-04-06,-74.99,,Expenses:Clothes,Jeans R Us,JRU Holdings 301
11-04-06,100.00,,Expenses:Clothes,Faux Pas Fashion,Faux Pas Fashion
15-04-06,-100.00,,Expenses:Clothes,Faux Pas Fashion – Refund,Faux Pas Fashion – Refund
10-04-06,25.00,,Expenses:Food,Greasy Spoon Cafe,PJs Cafe
13-04-06,85.00,,Expenses:Groceries,Groceries R Us,R Us Franchies 8973
13-04-06,40.00,,Expenses:Household,Groceries R Us,R Us Franchise 8673
13-04-06,60.00,,Expenses:Household,CheapMart,Eftpos 13-04 12:29
30-04-06,20.00,,Income:Interest,Interest,Interest

Answer 1

The Import Transactions process can be a bit fiddly to set up, but once you've got it set up correctly GnuCash does a pretty good job of importing well-formatted data.

Unfortunately, your Test Data files in your example are NOT well-formatted, but I'll come back to that. Let's start with the GnuCash process. Select File / Import / Import transactions from CSV....

Immediately after you've selected the text file to import, GnuCash will bring up the Import Preview window. The first thing you should do is tell the importer which Account all the transactions belong to, by selecting the account in the pull-down field in the top-right of the Import Preview window. For your test data files, you will need to select Asset:Cheque Account for your first import, and Liabilities:Visa Card for the second one. (I suspect that you have inadvertently skipped this task and that is the reason that your transactions ended up in the Imbalance account rather than the Cheque account.)

Now go ahead and set all the other File Format and Miscellaneous settings including selecting the correct Date Format for your data file.

In the bottom half of the Import Preview window, you tell GnuCash what all of the various fields of your data file represent. As your Test Data files include column header labels that should not be imported, set Leading Lines To Skip to the value of "1".

Immediately above the preview of your data is a row of pull-down field descriptors that correspond to field descriptors in the GnuCash Transaction Register. Give each column an appropriate descriptor (or tell GnuCash to ignore one or more columns). For your Cheque Account Test Data file, I would suggest that the column descriptors would be Date - Deposit - Num - Transfer Account - Description - Transfer Memo.

If you will be repeating this import process, now is a good time to go back up to the Load and Save Settings field and save your settings with an appropriate name. You can quickly load the saved settings next time.

Cross fingers and press Next.

You should now be on a window where GnuCash lists each Account ID imported from your data file, and alongside it is the GnuCash account that will be debited or credited when the import is finalised. On your first time running the import process, it's likely that NONE of the Account ID's are correctly mapped to the relevant GnuCash Account Name. One at a time, select an Account ID, click on Change GnuCash Account, and select the correct account from the Chart of Accounts. Once you have all of the Account IDs from your data file correctly mapped to GnuCash accounts, press Next. (GnuCash will remember those mappings for the next import process, and you will only have to map any fresh ID's.)

In the Match Transactions window, GnuCash will attempt to identify if a transaction that you're about to import already exists in GnuCash. You are given a chance to exclude individual lines from the import process. Press Apply when ready.

That's it, although it's worthwhile to have a quick look at the relevant account register to confirm that all has worked as you hoped.

The next time that you repeat the import process it will be easier because GnuCash remembers the things that you already trained it on.

---

The supplied Test Data files have some inconsistencies:

• A negative amount represents a Debit in one file and a Credit in the other. There's a workaround (import the Visa Card amounts as a Withdrawal not a Deposit) but it would be better if the data files could be consistent.
• Most of the Ledger:Account-Category fields contain data in the form Expenses:Split but some look more like [Savings]. This is not a huge problem as you get a chance during the import to map these items to GnuCash accounts.
• Expenses:Split has been used twice. Neither line looks right.
• Interest on a Visa Card would normally be an Expense item, not Income.

Answer 2

1. Does not support first line as field names - https://bugs.gnucash.org/show_bug.cgi?id=639115#c5
2. At match transactions step select multiple transactions and change destination account.