0

I am a corporate IT veteran from the financial industry with some QA experience and a Gnucash newbie. I am working on my chart of accounts setup in an iterative manner: I make adjustments to my account tree CSV file, create a new Gnucash file with one dummy account (rent expense), import my adjusted account tree CSV into the new file, reimport my QFX file, evaluate the results, rinse and repeat.

I am working with a fresh install of Ubuntu desktop (minimal install) and a fresh install of Gnucash, both as of mid-August, 2021.

The Vanguard QFX file I used for my trials contains seven Vanguard accounts with a half dozen securities, some of which are held in multiple of the seven accounts.

If I export my account tree after a trial QFX import, I see my securities where I expect them to be, subaccounts under their respective brokerage accounts and stock accounts under the Investments:Stocks placeholder account. However, if I wipe my Gnucash file and then reimport this exported CSV file, I receive an error for each security and the above subaccounts are not imported, but the respective dividend, short term and long term income accounts are imported.

As an exercise to assist in recreating this condition, I attempted an import using the same QFX file going into a completely flat Gnucash account tree (start with empty Gnucash file, with the one dummy account Expenses:Rent) and I received the same error messages. A narrative of the experience follows:

I start with new Gnucash file with one rent expense account

I perform an import of Vanguard QFX file, taking all default answers and assigning the top account tree level for each account.

An illustration of the chart of accounts after the 'flat QFX import':

dummy Expenses
   dummy insurance
      dummy rent insurance
   dummy rent account
income account VMRXX
income account VMFXX
income account VSGDX
investment account 1
investment account 2
investment account 3
investment account 4
investment account 5
investment account 6
investment account 7
VMFXX
VMRXX
VSGDX
VSMGX
VTSAX

Next, I do a File>Export>Export Account Tree to CSV and save to 'flat account tree.CSV'

Next, I do a File>New File and create a new Gnucash file with the one dummy Rent account

Next, I do a File>Import>Import Accounts from CSV and select file 'flat account tree.CSV'

I see the first 10 expected accounts in the preview, I click the 'apply' button in the import window

I see the following messages in the import window:

Import completed but with errors! 
The number of Accounts added was 10 and 4 were updated. 
See below for errors...
Row 2,commodity VMFXX/FUND not found
Row 3,commodity VMRXX/FUND not found
Row 4,commodity VSGDX/FUND not found
Row 5,commodity VSMGX/FUND not found
Row 6,commodity VTSAX/FUND not found

I clear the dialog and see the new chart of accounts after the import of the CSV file:

dummy Expenses
   dummy insurance
      dummy rent insurance
   dummy rent account
income account VMRXX
income account VMFXX
income account VSGDX
investment account 1
investment account 2
investment account 3
investment account 4
investment account 5
investment account 6
investment account 7

As a result of this behavior, I find it necessary to re-enter my set of securities information after each reload of my account tree. Naturally, I would prefer that the stock information be imported with the rest of the account tree from the CSV file and I would very much appreciate any suggestions that I could implement to make this happen.

Sorry for the verbose post - I have other questions but this is my top priority so I will save the others for another day.

geoff cahill
  • 21
  • 4

2 Answers2

2

My mistake that lead me to ask this question was the invalid assumption that if a stock was treated by Gnucash an account, then I would be able to set up stocks via the chart of accounts csv import. I now know that before doing a chart of accounts import that contains stock sub-accounts, I need to use 'Tools>Security Editor' to set up a commodity record for any stocks referenced in my chart of account CSV import file that do not already exist. Failure to do so will result in the 'commodity xxxx/xxxx not found' error message. I also now know that the Vanguard OFX import example only served to distract me from the actual cause of the error message I was experiencing. My thanks to Flywire and Greg S. for helping me learn a lot about Gnucash.

geoff cahill
  • 21
  • 4
0

Your question is very confusing but I understand you are having problems importing your coa then your transactions. GnuCash csv is very confusing.

Try this in GnuCash:

Account setup

  • File, NewFile, cancel
  • File, Import, Import Accounts from CSV

ShareInvestment_AccountTree.csv

Type,Full Account Name,Account Name,Account Code,Description,Account Color,Notes,Symbol,Namespace,Hidden,Tax Info,Placeholder
ASSET,Asset,Asset,,,,,AUD,CURRENCY,F,F,F
ASSET,Asset:Shares,Shares,,,,,AUD,CURRENCY,F,F,F
LIABILITY,Liability,Liability,,,,,AUD,CURRENCY,F,F,F
LIABILITY,Liability:Contra,Contra,,,,,AUD,CURRENCY,F,F,F
INCOME,Income,Income,,,,,AUD,CURRENCY,F,F,F
INCOME,Income:Distribution,Distribution,,,,,AUD,CURRENCY,F,F,F
INCOME,Income:Dividend,Dividend,,,,,AUD,CURRENCY,F,F,F
INCOME,Income:Dividend:Franked,Franked,,,,,AUD,CURRENCY,F,F,F
INCOME,Income:Dividend:Franking Credit,Franking Credit,,,,,AUD,CURRENCY,F,F,F
INCOME,Income:Dividend:Unfranked,Unfranked,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense,Expense,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Brokerage,Brokerage,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Fee,Fee,,,,,AUD,CURRENCY,F,F,F
EXPENSE,Expense:Fee:Bank,Bank,,,,,AUD,CURRENCY,F,F,F
EQUITY,Equity,Equity,,,,,AUD,CURRENCY,F,F,F
EQUITY,Equity:Opening Balance,Opening Balance,,,,,AUD,CURRENCY,F,F,F
BANK,Bank,Bank,,,,,AUD,CURRENCY,F,F,F
BANK,Bank:MyBank,MyBank,,,,,AUD,CURRENCY,F,F,F
BANK,Bank:Broker Funds,Broker Funds,,,,,AUD,CURRENCY,F,F,F

Here's a file from https://forum.portfolio-performance.info/t/security-account-export-csv-with-itemised-transaction-costs/17595/2

In GnuCash:

  • Import Broker_Securities_split.csv
  • Date: d-m-y, Lines to Skip: 1, Fields: Date, Description, Notes, Account, Deposit
  • Import as Multi-split
  • Match Accounts
  • Save File

I think you want accounts for each security (ie Asset:Shares:REA).

Broker_Securities_split.csv

Date,Description,Note,Account,Deposit
2021-01-04,REA,T20210104689951-1,Bank:Broker Funds,-9880.86
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9871.36
2021-02-01,RIO,T20210201523636-1,Bank:Broker Funds,-9912.53
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9903.03
2021-03-01,WPL,T20210301378143-1,Bank:Broker Funds,-9998.02
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9988.52
2021-03-29,WOW,T20210329345452-1,Bank:Broker Funds,-9998.18
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9988.68
2021-04-26,CBA,T20210426460841-1,Bank:Broker Funds,-9983.1
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9973.6
2021-05-24,WPL,T20210524608678-1,Bank:Broker Funds,8695.86
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,-8705.36
2021-05-24,ALL,T20210524356856-1,Bank:Broker Funds,-18690.8
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,18681.3
2021-06-21,NCM,T20210621600557-1,Bank:Broker Funds,-9986.65
,,,Expense:Brokerage,9.5
,,,Expense:Tax,0.0
,,,Asset:Shares,9977.15

This is adjusted on the fly so leave a comment for any problems.

flywire
  • 194
  • 12