[GNC] Example of importing Stock transactions from CSV

[Jon Schewe]

On Sun, 2022-08-21 at 09:01 -0700, john wrote:
> 
> 
> > On Aug 21, 2022, at 6:37 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.
> > 
> 
> The CSV importer doesn't know how to calculate prices and there's no
> way to tell it the value (in the ttransaction currency), only the
> amount (in the account's commodity). You should supply the price
> instead. Ideally that will be the fraction value/amount (multiply
> both by the power of 10 needed to make them integers, so e.g  you'd
> write $497.65 for 57 shares as 49765/5700.
> 
> The difference between a deposit column and a withdrawal column is
> that the latter is sign-reversed. A positive deposit will increase
> the balance of an asset account and reduce the value of a liability
> or equity account; a positive withdrawal will do the opposite.

Does the CSV importer handle floating point values for amount and price
or do I need to use integers?

Given the attached CSV file
The AMOUNT column is dollars. The FUND UNITS column is in shares.

Skip 1 row, use m-d-y for Date format

Column Name -> GnuCash field
POSTING DATE -> Date
ACTIVITY TYPE -> Description
FUND -> Account
FUND UNITS -> Deposit
FUND NAV/PRICE -> Price
Transfer Account -> Transfer Account
Fund Commodity -> Transaction Commodity 

When I use those mappings GnuCash has the correct amounts in the "Match
transaction" view, however it doesn't have a transfer account
specified. When I try and specify an account I'm told that the
Transaction Commodity doesn't match the commodity of the selected
account, which is Expenses.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.csv
Type: text/csv
Size: 482 bytes
Desc: not available
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20220824/63e303ad/attachment.csv>