[GNC] Example of importing Stock transactions from CSV
David Carlson
david.carlson.417 at gmail.com
Sun Aug 21 23:49:22 EDT 2022
Jon,
The exported CSV security purchase transaction did have more than two split
lines because it had a commission and fees, so to simplify things I deleted
the commission and fee lines. If there had been a dividend or if it was a
sale rather than a purchase there would have been other distractions as
well. For the export I started from the security account register and
exported one transaction by selecting a single date to export. I opened
the exported CSV file in LibreOffice which, after simplifying, was a two
line by many column file with a title line to act as a header naming all
the fields which I then saved as a text CSV file. That way the critical
columns were already blessed with header names very similar to the names
that were needed during the import.
Here is a list of every column that was exported and where I selected a
field to import to, the name of that field.
Export Column CSV Import field
Date > Date
Transaction ID > None
Number > None
Description > Description
Notes > Notes
Commodity/Currency > Transaction Commodity
Void Reason > None
Action > None
Memo > Memo
Full Account Name > None
Account Name > None
Amount with Sym > Deposit
Amount Num. > None
Reconcile > None
Reconcile Date > None
Rate /Price > Price
There are several places where alternate choices would work. One, I think,
would have been Account Name, which might have eliminated the need to
assign a transfer account to the transaction in the last step before
accepting the import. As it was, I needed to assign the brokerage account
name as the transfer account in that step.
In the Import Preview step I needed to assign the security account name in
the Account name box on the upper right, select the date format because
Americans use M-D-Year format, and 1 leading line to skip the header line.
I saved these settings under a name that would make it easy to preselect
the same account names next time.
Finally, because the transaction that I had exported was actually still
there, in the next step I needed to click the A(dd) box and select the
brokerage account as the transfer account.
This test worked nicely, but I did not try extending the test to multiple
transactions.
On Sun, Aug 21, 2022 at 3:19 PM David Carlson <david.carlson.417 at gmail.com>
wrote:
> 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
>
--
David Carlson
More information about the gnucash-user
mailing list