[GNC] Need help importing Investment transactions

[davidcousens49 at gmail.com]

David,

The issues with building GC4.9 on Ubuntu 20.04 are likely relatively minor as
far as I can see, and mainly those that first time builders of GnuCash encounter
with getting the dpendencies and build environment setup and enough familiarity
with the process to be able to avoid the common mistakes with relative
addressing etc.

I had absolutely no problems on Linux Mint which is an Ubuntu 20.04
derivative. I had to reinstall a couple of dependencies because I had downgraded
some libraries while installing another piece of software a couple of weeks ago
(I was expecting that problem) but once I had them reinstalled there was no
problem and it was built and installed in a few minutes.  David Whiting's script
looks pretty good

I had started to explore the CSV import of both single line and multiline CSV
with a view to documenting it better a year or two ago and then was sidetracked
by a physics project I have been involved with and I unfortunately have not got
back to doing that. One difficulty for me with this was that I am not actively
trading shares so I am not familiar and couldn't easily create suitable test
cases.

David Cousens

On Sat, 2021-12-25 at 14:52 -0600, David Carlson wrote:
> I almost forgot that there is an issue with release 3.8 where scheduled
> reinvested dividend transactions and other stock purchase transactions do
> not correctly apply the share price when the SX runs which I know has been
> fixed in the 4.something update.  Maybe that fix also fixed this issue?
> 
> On Sat, Dec 25, 2021 at 2:31 PM David Carlson <david.carlson.417 at gmail.com>
> wrote:
> 
> > The main difference is that I am using release 3.8.  At the matching step
> > after I touch add so it won't erase the text in the existing transactions
> > it declares that the four non-zero value dividend  transactions are already
> > balanced and it won't let me assign an income account.
> > 
> > Then I touch Next and it accepts them without an imbalance transfer
> > account and I cannot manually edit them to add an income account.
> > That throws the brokerage account out of balance and the transaction
> > repair feature cannot fix it either.
> > 
> > I n other accounts the matching step will behave similarly on occasional
> > zero value transactions but I have been able to assign a special account
> > that I use exclusively for comments.
> > 
> > As you have seen in other threads here it is still an error-prone process
> > to upgrade Ubuntu LTS to Gnucash release 4.9.  I would want to try it in a
> > disposable virtual machine before committing my only working instance to
> > the upgrade.
> > 
> > On Sat, Dec 25, 2021, 12:15 PM john <jralls at ceridwen.us> wrote:
> > 
> > > Your example worked fine for me in GnuCash 4.9. I set it to ignore the
> > > first 5 rows, set the date field to Date, the description field to
> > > Description, and the Amount Field to deposit and ignored the rest. I used
> > > Assets:Investment:Brokerage Account for the target account. On import the
> > > matcher window came up with the several transactions. I clicked OK without
> > > bothering with matching and all were created with splits in Brokerage
> > > Account and Imbalance-USD.
> > > 
> > > What did you do?
> > > 
> > > Regards,
> > > John Ralls
> > > 
> > > 
> > > On Dec 25, 2021, at 8:26 AM, David Carlson <david.carlson.417 at gmail.com>
> > > wrote:
> > > 
> > > I have created a test csv import file and I can replicate the single line
> > > out-of-balance imported transactions. .  Reminder, I am still using
> > > release
> > > 3.8 in Llnux Lite [Ubuntu 20.04].
> > > 
> > > The test file is pure text, I will quote it to be sure that it gets
> > > through.  Alas, that adds spurious carriage returns.  There should only be
> > > five transaction lines beginning with the date and no cr's until just
> > > before the next date.  I will try attaching it as well.
> > > I set it to import into the brokerage account skipping the header lines,
> > > assign import columns Date to Date, Memo to Type, Description to
> > > Description, Deposit to Amount, Transaction Commodity to Symbol and Price
> > > to Price. Then I make all five transactions new in the match step as there
> > > are existing similar transactions.  The dividend transaction does get a
> > > transfer to the correct account.
> > > 
> > > [Transactions
> > > Account: DianeIRA (*****574)
> > > 
> > > Time Period: 07/01/2021 to 12/22/2021
> > > Transaction type: All transactions without Cash Sweep Activity
> > > 
> > > Date,Type,Description,Quantity,Amount,Symbol,Price,Check number
> > > 08/02/2021,Dividend,"Bristol Myers Squibb Co
> > > Cash Div  On     475 Shs
> > > Rec 07/02/21 Pay 08/02/21",,232.75,BMY,,
> > > 08/02/2021,Dividend,"Pimco Corporate & Income
> > > Opportunity Fund
> > > Cash Div  On     600 Shs",,78,PTY,,
> > > 07/26/2021,Interest,"RBC Insured Deposits
> > > Monthly Interest 06/28-07/25
> > > Apye#: 00.01 %",,0.02,,,
> > > 07/09/2021,Dividend,"American Tower Corporation
> > > Reit
> > > Cash Div  On     150 Shs",,190.5,AMT,,
> > > 07/01/2021,Dividend,"Pimco Corporate & Income
> > > Opportunity Fund
> > > Cash Div  On     600 Shs",,78,PTY,,]
> > > 
> > > On Thu, Dec 23, 2021 at 12:58 PM David Carlson <
> > > david.carlson.417 at gmail.com> wrote:
> > > 
> > > > It will be a while before I can find an adequate time window to test and
> > > > post an example csv with instructions to duplicate the single line
> > > > transactions that I saw the other day.  I still have the csv file and
> > > > the
> > > > import configuration so I should be able to do it.  In the meantime have
> > > > a
> > > > nice holiday.
> > > > 
> > > > On Thu, Dec 23, 2021 at 11:44 AM john <jralls at ceridwen.us> wrote:
> > > > 
> > > > > 
> > > > > On Dec 22, 2021, at 8:58 PM, David Carlson <
> > > > > david.carlson.417 at gmail.com>
> > > > > wrote:
> > > > > 
> > > > > I am currently running release 3.8 in Linux Lite, which is Ubuntu
> > > > > 20.04
> > > > > under a different label.  I think that the CSV transaction import has
> > > > > not
> > > > > been changed, between 3.8 and 4.9.  If that is not correct, then some
> > > > > of my
> > > > > issues may be bugs that have been fixed.
> > > > > 
> > > > > I have read the current help manual section on importing transactions
> > > > > in
> > > > > csv format <https://gnucash.org/viewdoc.phtml?rev=4&lang=C&doc=help>
> > > > > which
> > > > > gave me a start, but there are some things that I cannot make work
> > > > > correctly.
> > > > > 
> > > > > I have come to the conclusion that my brokerage house World Equity
> > > > > Group
> > > > > CSV exports probably need to be massaged, and possibly split up but
> > > > > there
> > > > > are a couple of issues that I need to understand in order to determine
> > > > > how
> > > > > to fix the import data and to assign the proper names to the various
> > > > > fields.
> > > > > 
> > > > > One issue is that in one test many transactions were imported as
> > > > > single
> > > > > line unbalanced transactions with no transfer account, not even an
> > > > > assignment to an unbalance account.  Why did GnuCash accept unbalanced
> > > > > transactions containing amounts? Applying Check and Repair to some of
> > > > > those
> > > > > transactions did not balance them to an unbalance account.
> > > > > 
> > > > > Another issue is that I have not figured out which column name should
> > > > > be
> > > > > used for the number of shares in a purchase or sale transaction.
> > > > > 
> > > > > Yet another issue would be whether I can import everything into the
> > > > > brokerage account or do I need to do something special to get stock
> > > > > transactions assigned to the proper security account when appropriate?
> > > > > 
> > > > > A fourth issue would be whether I need to use the Multi-Split feature
> > > > > and,
> > > > > if so, how to format the CSV file to make it assign the correct split
> > > > > lines
> > > > > to the correct base transaction, and save it in CSV format if I built
> > > > > the
> > > > > spreadsheet in LibreOffice Calc or equivalent
> > > > > 
> > > > > 
> > > > > Your premise might be mistaken: There have been 40 commits in
> > > > > gnucash/import-export/csv since 3.8 including several bug-fixes.
> > > > > However
> > > > > none of the summaries say anything about creating single-split
> > > > > transactions. You're right that that shouldn't be possible. Can you
> > > > > post an
> > > > > example or two of CSV rows that do that?
> > > > > 
> > > > > There isn't a column name for amount (see
> > > > > https://bugs.gnucash.org/show_bug.cgi?id=797368), just for price, but
> > > > > even that apparently doesn't work well:
> > > > > https://bugs.gnucash.org/show_bug.cgi?id=793306 and
> > > > > https://bugs.gnucash.org/show_bug.cgi?id=796955.
> > > > > 
> > > > > If you root the import on the brokerage cash account you should be
> > > > > able
> > > > > to designate the transfer accounts to the various stock's accounts in
> > > > > the
> > > > > matcher if importing stock trades worked, see previous item.
> > > > > 
> > > > > The easiest way to learn the format for multi-split CSV files is to do
> > > > > File>Export>Export Transactions to CSV and examine the resulting
> > > > > spreadsheet.
> > > > > 
> > > > > Regards,
> > > > > John Ralls
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > > 
> > > > 
> > > > --
> > > > David Carlson
> > > > 
> > > 
> > > --
> > > David Carlson
> > > <Test_Transactions_without_Cash_Sweep_Activity_2021-12-22.csv>
> > > 
> > > 
> > >