[GNC] Example of importing Stock transactions from CSV

[David T.]

The thread at https://lists.gnucash.org/pipermail/gnucash-user/2019-March/083442.html goes into quite a discussion about how to import multi line csv files. 


On August 21, 2022 6:26:24 PM GMT+03:00, Jon Schewe <jpschewe at mtu.net> wrote:
>David,
>
>I'm using GnuCash 4.11 from FlatPak. I have all of my transactions on
>single lines (I've posted samples in this thread). 
>I would greatly appreciate someone explaining how to make this work and
>I'd be happy to help update the documentation on this.
>
>Jon
>
>
>On Sun, 2022-08-21 at 09:17 -0500, David Carlson wrote:
>> Jon,
>> 
>> If you are not using some recent 4.x version of GnuCash you may not
>> have the latest improvements to the CSV transaction importer.  
>> 
>> A normal stock purchase or sale transaction has two parts, one, a
>> deposit or withdrawal of cash from a brokerage account, the other an
>> exchange of the cash for a security at some exchange rate, aka
>> price.  For this discussion I will ignore commissions and fees.
>> 
>> Thus you want to have two CSV lines per transaction.  Also, when
>> importing the CSV file, you will need to check the Multi-split box to
>> tell GnuCash that is the type of file that you are importing.  Then
>> you can assign one line to the security purchase or sale and the
>> other to the cash transfer.  You can define fields in each line to
>> match the respective lines in a completed transaction as seen in the
>> security register.  You only see the price and share details in the
>> security register and the cash transfer details can be seen in both
>> the security register and the brokerage register.
>> 
>> I will admit that I have not done that myself, so I cannot provide an
>> example for you to follow.  Perhaps another user can provide an
>> example.  Good luck.
>> 
>> On Sun, Aug 21, 2022 at 8:38 AM Jon Schewe <jpschewe at mtu.net> wrote:
>> > On Sat, 2022-08-20 at 12:20 -0400, Michael or Penny Novack wrote:
>> > > On 8/19/2022 10:33 PM, Jon Schewe wrote:
>> > > > So I didn't get the CSV import tool to work.
>> > > 
>> > > Perhaps a reminder on what CSV is and is not.
>> > > 
>> > > CSV stands for "common separated variables. That means the data
>> > is in
>> > > the form of a record consisting of a number of fields delineated
>> > by 
>> > > commas. Which means two important things. The fields (individual
>> > data
>> > > elements) cannot contain commas but even more important, ORDER
>> > > MATTERS.
>> > > 
>> > > In other words, the record A,B,C,D is not the same a B,A,C,D <<
>> > A, B,
>> > > C, 
>> > > and D being elements of data, names, amounts, etc. >>
>> > > 
>> > > The CSV data you are importing must not only be in CSV format but
>> > the
>> > > data must be in the right order AND any data that is null (not
>> > > present 
>> > > still has its space in the record.
>> > > 
>> > > Thus if a record was supposed to include A, C, and D  (B is not
>> > > relevant 
>> > > to this record) it would look like A,,C,D and not A,C,D
>> > > 
>> > > 
>> > > But even the "wrong" examples are in CSV format.
>> > > 
>> > > 
>> > Yes, I know what a CSV file is and how it should be formatted. The
>> > problem is not with the format of the file, that is handled by
>> > GnuCash
>> > just fine. The problem is the mapping of columns in the CSV file to
>> > fields in GnuCash. I cannot figure out the correct mapping to get
>> > the
>> > correct transactions.
>> > 
>> > 
>> > 
>> > I have tried all of the following possible mappings between GnuCash
>> > fields and columns in my CSV file. In the end I finally wrote the
>> > python script out of frustration of the import CSV transactions not
>> > working. I'm hoping that someone on this list understands enough
>> > python
>> > and the import CSV transactions functionality enough to tell me how
>> > to
>> > get the mapping correct.
>> > 
>> > # test 2
>> > 
>> > Transfer Account - FUND
>> > Withdrawal - FUND UNITS
>> > Account - Transfer Account
>> > Transaction Commodity - Currency 
>> > 
>> > Shares are correct.
>> > Close, need to get the dollars right now
>> > 
>> > # test 3
>> > 
>> > Transfer Account - FUND
>> > Withdrawal - FUND UNITS
>> > Account - Transfer Account
>> > Transaction Commodity - Currency 
>> > Deposit - AMOUNT
>> > 
>> > None of the shares or cash lined up.
>> > 
>> > # test 4
>> > 
>> > Try swapping withdrawal and deposit
>> > 
>> > Transfer Account - FUND
>> > Deposit - FUND UNITS
>> > Account - Transfer Account
>> > Transaction Commodity - Currency 
>> > Withdrawal - AMOUNT
>> > 
>> > None of the shares or cash lined up.
>> > 
>> > # test 5
>> > 
>> > Transfer Account - FUND
>> > Withdrawal - FUND UNITS
>> > Account - Transfer Account
>> > Transaction Commodity - Currency 
>> > price - FUND UNITS
>> > 
>> > Values don't line up with the price
>> > 
>> > # test 6
>> > 
>> > Transfer Account - Transfer Account
>> > Deposit - AMOUNT
>> > Account - FUND
>> > Transaction Commodity - Currency 
>> > 
>> > Price is right, Shares are the dollars
>> > Shares times price equals dollars.
>> > 
>> > 
>> > # test 7
>> > 
>> > Transfer Account - Transfer Account
>> > Account - FUND 
>> > Transaction Commodity - Fund Commodity
>> > Deposit - FUND UNITS
>> > 
>> > Nothing is right, couldn't find the account to transfer from and
>> > price
>> > is 1.
>> > 
>> > _______________________________________________
>> > gnucash-user mailing list
>> > gnucash-user at gnucash.org
>> > To update your subscription preferences or to unsubscribe:
>> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> > -----
>> > Please remember to CC this list on all your replies.
>> > You can do this by using Reply-To-List or Reply-All.
>> 
>> 
>
>_______________________________________________
>gnucash-user mailing list
>gnucash-user at gnucash.org
>To update your subscription preferences or to unsubscribe:
>https://lists.gnucash.org/mailman/listinfo/gnucash-user
>-----
>Please remember to CC this list on all your replies.
>You can do this by using Reply-To-List or Reply-All.