[GNC] Example of importing Stock transactions from CSV

[David Carlson]

Jon,

I just tried exporting one transaction from a security account and
re-importing it with the CSV importer.  It worked like a charm!  I don't
have time right now to detail what I did here, but I did not need to select
the Multi split box after all.  Have fun

On Sun, Aug 21, 2022 at 12:16 PM David T. <sunfish62 at yahoo.com> wrote:

> 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.
>>
>>

-- 
David Carlson