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.

3
  • I think I see where I went astray. To implement the iterative cycle I want to use here, I need to create the empty file once and then use the Tools>Security Editor to set up the set of securities I would like to have available for my Chart of Account stock accounts, save that file as a master copy and open a working copy of that master copy with Gnucash and then import my Chart of Accounts CSV file....unless there is a way to directly import into the security editor table that I have missed. Aug 26 at 1:06
  • It's not clear to me why you are setting up your Chart of Accounts then going through an export process before re-importing the C of A into a new GnuCash file. GnuCash allows you to make changes to your C of A after the initial account setup if you want to tweak it or need to add new accounts. You can "Save As" if you want to have point-in-time snapshots of different setups. I don't understand what advantage you are trying to achieve with the export/import process - and you already know at least one disadvantage. Aug 29 at 4:02
  • Thank you for your comment. Aug 30 at 15:12
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.

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.

6
  • Thank you very much for your detailed response, flywire. I believe I successfully completed the procedure you described, but I did not see the individual securities listed as subaccounts under the Asset:Shares account. In your answer, you correctly stated that I want accounts for each security (ie Asset:Shares:REA). Ultimately, I am looking to see aggregated share holdings by security across different brokerage accounts and reporting that would enable to me to calculate any holdings changes needed in order to rebalance the holdings to bring them in line with my target allocation. Sep 2 at 20:07
  • Hmm, I decided to use GnuCash for tax accounting and Performance-Portfolio for share analysis/management. In your case you would need subaccounts for each holding for shares, dividends etc. Create one then export COA, make similar changes for others then import again. Try these unofficial docs for an old version: gnucash-docs-rst.readthedocs.io/en/latest/guide/C/… It gets unwieldy quickly and the official docs only use one security (AMZN) to demonstrate it. Do you want me to change the answer?
    – flywire
    Sep 3 at 1:04
  • I have a clearer understanding of what you want now. Can you add a Broker_Securities_split.csv for your example using Asset:Shares:REA type COA and adding a number of shares column then I'll provide a new answer following the official docs. btw, have a look at portfolio-performance.info but you'd still need a cashbook for tax.
    – flywire
    Sep 3 at 1:37
  • Thank you again for your help with my dilemma. Allow me to point out that my illustration in the beginning of this discussion was a test scenario I put together in order to reproduce the error message that I needed clarity on, and does not reflect the COA that I was developing for my purposes. I think we have come full circle in that you have confirmed to me that my original (and admittedly somewhat tortured) approach had some validity, as I am working towards a COA that is more or less what you described in your suggestion to me. I am working on a simpler test example and will report back. Sep 3 at 14:07
  • I will have to span comments as I cannot fit the entire note on one comment block. OK here goes: This is a procedure that results in the error message I started this thread off with but eliminates the OFX import. In Gnucash, start by clicking File>New file;click next, next, next, uncheck common accts, scroll down and check investment accts, click finish, apply. Supply a save as file name and click save as button. Now, goto section 9.4.2 in the documentation and follow the procedure there to create the security AMZN. Sep 5 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.