Difference between revisions of "Merging GnuCash Files"

Latest revision as of 09:30, 14 September 2023

These are the steps I took to successfully merge two GnuCash files containing transactions for two separate years. Below I use 2000.gnucash and 2001.gnucash as examples, but obviously you can use any two years. We will take 2000.gnucash and import into it all transactions from 2001.

This process does not merge any Business data.

Prerequisites:

• I successfully used GnuCash 5.0, 5.1, and 5.3 for this process.
• VSCode with the Rainbow CSV plugin.

Exporting accounts and transactions from 2001

• Create a separate ‘merge folder’ to store the temporary files required for the merge process. See #Note 1.
• Make a copy of 2001.gnucash into the ‘merge folder’ so you don’t modify the original file.
• Open 2001.gnucash in read-only mode, select File→Export→Export Accounts and name the file 2001_01_accounts.gnucash.
• Select File→Export→Export Account Tree to CSV… Accept default settings (comma separated file) and name the file 2001_03_account_tree.csv.
• Select File→Export→Export Transactions to CSV… Accept default settings (comma separated file) and on the ‘Account Selection’ page select all accounts under Assets (where you have all your assets, like bank accounts, cash, stocks, etc.) and all accounts under Expenses. Click Next and name the file 2001_02_transactions.csv.
• Open VSCode and make sure the Rainbow CSV plugin is installed. Open 2001_02_transactions.csv in VSCode, press CTRL+SHIFT+P, search for ‘Rainbow CSV: RBQL’ and select it. In the new tab check ‘Input table has header’ at the bottom, and overwrite the Example query with SELECT DISTINCT * ORDER BY a2. Click Run, a new tab will be opened. Save As this tab with the name 2001_02_transactions_noduplicates.csv.

Test that the transactions were exported correctly

We test it by importing the transactions back into an empty 2001 file. See #Note 2.

• Make a copy of file 2001_01_accounts.gnucash with the new name 2001_02_accounts_imported_into_itself.gnucash and open it.
• Select File→Import→Import Transactions from CSV… Select the file 2001_02_transactions_noduplicates.csv. On the Import Preview page, under Load and Save Settings, select ‘GnuCash Export Format’. On the Match Transactions page, make sure all lines have ‘New, already balanced’ in the rightmost ‘Additional Comments’ column. If a line has other comments then manually fix the problem in the CSV file. Then click Apply.
• Open a second instance of GnuCash and load 2001.gnucash. Make sure that in the two GnuCash windows on the Account tab the values in the Total column are identical for accounts on the lowest level. If the Total for a higher level account is different it does not matter because it can be caused by missing commodity prices. If the totals are identical then you can be sure that 2001_02_transactions_noduplicates.csv correctly contains all your transactions from 2001.

Comparing the Account Tree between years

• Make a copy of 2000.gnucash into the ‘merge folder’ under the name 2001_03_before_import.gnucash so you don’t modify the original file.
• Open 2001_03_before_import.gnucash then select File→Export→Export Account Tree to CSV… Accept default settings (comma separated file) and name the file 2001_03_previous_account_tree.csv.
• Make a copy of 2001_03_previous_account_tree.csv with new name 2001_03_previous_account_tree_sorted.csv.
• Make a copy of 2001_03_account_tree.csv with new name 2001_03_account_tree_sorted.csv.
• Open the above two copied files in VSCode and do the following in both files: Select All then press CTRL+SHIFT+P, search for Sort Lines Ascending and execute.
• Activate the tab of 2001_03_account_tree_sorted.csv then press CTRL+SHIFT+P, search for File: Compare Active File With… and execute. Select 2001_03_previous_account_tree_sorted.csv and press Enter. A new tab should open, comparing the two files. 2001_03_previous_account_tree_sorted.csv should be on the right.
• In this tab you see the accounts you used in 2000 on the left hand side. The idea is that all accounts on the left must exist on the right. If you find missing accounts then copy their line from 2001_03_previous_account_tree_sorted.csv into a new file called 2001_03_account_tree_tobeimported.csv. If your account names contain international characters then you have to find out what encoding will work best for this new file. It seems to depend on the language you use GnuCash in.
• If some accounts exist on boths sides but their account settings are different, you have two choices:
  • Open 2001_03_before_import.gnucash in GnuCash and change the account settings manually. In the CSV the last 3 columns are Hidden, Tax Info, Placeholder, each with a value of T (true) or F (false).
  • Copy the lines of these accounts into 2001_03_account_tree_tobeimported.csv. Upon import, the account settings in the CSV file will overwrite your current settings for these accounts.

Merging securities

• Open two instances of GnuCash with the files 2001.gnucash and 2001_03_before_import.gnucash. Open Tools→Security Editor and make sure all securities in 2001.gnucash also exist in 2001_03_before_import.gnucash. Manually create the missing securities.

Importing Accounts

• You can close 2001.gnucash and keep open 2001_03_before_import.gnucash. In this file select File→Import→Import Accounts from CSV… Select 2001_03_account_tree_tobeimported.csv to import and accept default settings. Ideally, on the last screen GnuCash will say that the number of accounts imported plus the number of accounts updated equals the number of lines in your CSV file. If you’ve done the updates manually, then 0 accounts will be updated.

Importing Transactions

• Make a copy of 2001_03_before_import.gnucash with new name 2001_04_imported.gnucash.
• Open 2001_04_imported.gnucash and select File→Import→Import Transactions from CSV… Select the file 2001_02_transactions_noduplicates.csv. On the Import Preview page, under Load and Save Settings, select ‘GnuCash Export Format’. On the Match Import and GnuCash accounts page make sure that the account names in both columns are identical and there is no ‘No Linked Account’ message in the right hand side column. On the Match Transactions page, ideally, all lines will have ‘New, already balanced’ in the rightmost ‘Additional Comments’ column. For those that don’t, you have to manually decide what GnuCash should do with that particular line: Update an existing transaction and reconcile it (check the box in the U+C column), or skip the import (do not check any columns).
• Open a second instance of GnuCash and load 2001.gnucash in read-only mode. Make sure that in the two GnuCash windows
  • on the Account tab
  • the values in the Total column
  • are identical for all accounts
  • under Assets on the lowest level. If the Total for a higher level account is different it does not matter because it can be caused by missing commodity prices.
• To make the totals equal, you might need to delete the opening balance transactions which were imported from 2001.
• Open each Equity account and make sure that if they have opening balance transactions from 2001 it really makes sense to keep them in the merged file. Most of the time they should be deleted, because ending balances for 2000 will already be in the merged file.
• Open each Liability account and make sure their ending balance is equal to the ending balance in 2001.gnucash.

Notes

Note 1

At the end of this process, you will have many files in this folder corresponding to each step, so if you discover any errors at the end then you don't have to start from scratch but only go back to the problematic step.

Note 2

If you can successfully import transactions into the file it was exported from you prove that your CSV file is correct. This step is useful because it is much harder to detect errors once you import the CSV file into 2000.gnucash and transactions from both years are merged.