trouble with date when importing a just exported csv file.

Sat Jan 31 13:55:57 EST 2015

I agree with others that price is not necessarily the best thing to focus
on here. Imagine you have a transaction in which you purchase 100 shares of
AAPL for $1180.00. Your cash account goes down by $1180.00, and your AAPL
account balance increases by 100; that is all. The price data is used in
some intermediate calculations but is not present in the transaction. The
price data can be stored in another location but it's not in the accounts
themselves so probably shouldn't be part of the import.

The issue that is important and merits focus is the issue of connecting the
two legs of the transaction properly. (Once that is done, the price per
share can be calculated.) There is a decrease in cash and an increase in
AAPL, but those two parts of the transaction must be connected in order for
double entry accounting to work. In the earliest CSV file you sent, there
were two lines, one for each part of the transaction, but it's not clear on
first glance that those are parts of the same transaction. How would the
import process be able to tell? If each transaction has two legs, fine; but
some transactions have multiple splits (e.g., purchase of stock while
paying for the stock, paying a commission, and paying fees to a government
agency).

That is why QIF is a better format for import than CSV. Others have made
suggestions for file conversion approaches (e.g., Wm has suggested
http://csvconverter.gginternational.net/ and Michael Novack has suggested a
framework for the conversion.

Personally, my approach would be not to bother (I enter transaction data by
hand in almost all cases) but if I had a large number of transactions to
import I would write my own converter. It is easy to find QIF documentation
and the CSV format should be apparent from the file you download (although
there may be twists that aren't apparent in a single download so it would
be better (though likely not possible) to get a formal specification). With
my own converter, I would retain control and security over the data and
fine control over the import process. An experienced programmer could slap
something together in Perl or the like in a short time; testing would be
more time-consuming.

As for figuring out a more generic approach that could be rolled into
GnuCash as an importer, I think think it's practically impossible for
organizations that supply data in CSV format. There is not enough structure
available. Brokerages should use QIF or something more structured. Users
who find themselves fussing with file conversion, etc., might consider the
possibility of switching brokerages instead.

On 31 January 2015 at 10:57, Larry Evans <cppljevans at suddenlink.net> wrote:

> On 01/31/2015 08:43 AM, Wm wrote:
> > Fri, 30 Jan 2015 20:46:41 <mahfmj$tao$1 at ger.gmane.org>  Larry Evans
> > <cppljevans at suddenlink.net>
> >
> >> On 01/30/2015 12:26 PM, Tommy Trussell wrote:
> >>> On Fri, Jan 30, 2015 at 10:21 AM, Larry Evans
> >>> <cppljevans at suddenlink.net>
> >>> wrote:
> >>>
> >>>> On 01/29/2015 04:12 PM, Wm wrote:
> >>>>> Thu, 29 Jan 2015 14:34:44
> >>>>> <CA+E35_7AN=0Zc11Y8dWvbeaXsjkRZSwba4g3Efa+1purnO6y8w at mail.gmail.com>
> >>>>> Edward Doolittle <edward.doolittle at gmail.com>
> >>>>>
> >>>>>> Probably the fastest way to get something working would be to write
> a
> >>>>>> program to translate from the CSV format that your brokerage
> provides
> >>>>>> into
> >>>>>> QIF or something else for easier importing into GnuCash.
> >>>>>
> >>>>> That is available for free, see my
> >>>>> ===
> >>>>> Subject: csv to qif / ofx
> >>>>> Date:    08 January 2015 21:27:00
> >>>>> ===
> >>>>> and similar
> >>>>>
> >>>>> is no one (not you, E) able to solve a problem using the tools
> >>>>> available
> >>>>> these days?
> >>>>>
> >>>> Thanks for the suggestion, Wm.
> >>>> Here's what I tried:
> >>>>
> >>>> * Went to the webpage:
> >>>>   http://csvconverter.gginternational.net/
> >>>> * uploaded file:
> >>>>     SHA1_transactions.csv
> >>>>   which contained only 1 line:
> >>>> 01-06-2015,buy more,Checking Account,1.5,100.0
> >>>>   where fields were:
> >>>> Date,Description,Transfer,Shares,Price
> >>>> * Then tried the mapping step on the web page, but
> >>>>   got stuck at the place shown in the attached .png.
> >>>>   As you can see, there's no obvious place to put
> >>>>   the Price field :(
> >
> >>> I haven't worked with this on this level, but I seem to recall some
> >>> discussion saying the security price gets derived from the sum of the
> >>> buy
> >>> (or sell) transaction splits. In other words, you cannot just set the
> >>> price
> >>> of a security for a transaction;
> >>
> >> I also gave the number of Shares bought.  Please review the lines:
> >>
> >>>>     SHA1_transactions.csv
> >>>>   which contained only 1 line:
> >>>> 01-06-2015,buy more,Checking Account,1.5,100.0
> >>>>   where fields were:
> >>>> Date,Description,Transfer,Shares,Price
> >>
> >> In my previous post.  There, 1.5 Shares were bought at a price of 100.0
> >> per share.  That's all that's required in the register, which calculates
> >> the value of the Buy (if Shares is positive) or the value of the Sell
> >> (if Shares is negative).  The import interface should behave similarly.
> >>
> >>> it is derived from the results of the
> >>> splits in a fully-formed transaction.
> >>>
> >>> If my memory is wrong, my apologies.
> >>
> >> No problem.
> >
> > It might help to think of it this way.
> >
> > In a test book use your lottery winnings to make a transfer from
> > equity:lottery to assets:share1 [1]
> >
> > you can also buy some assets:share1 using expense:share_purchases or
> > income:share_reinvestment and so on
> >
> > in gnc the expense, income and equity account types deal in money; lots
> > of different kinds of money called currencies but only money [2] not
> > units of something that isn't money.
> >
> > So, the obvious way of getting your transactions in and out is through
> > amounts of money.
> >
> > Presuming you don't have thousands of these them it becomes a simple
> > matter of adjusting the not-pure-currency (i.e. there was a number of
> > frog-legs or bags-of-recycled-paper) involved via an amount of money
> > which could, theoretically be an arbitrary 1.  But I'd suggest a best
> > guess at a realisable value is better than 1.
> >
> > Have I made things more or less confusing? :)
>
> Thank you for the explanation, Wm.
>
> However, my aim was to make the import process mirror the interactive
> entry of a transaction as done in the "Checkbook-Style Register":
>
> http://www.gnucash.org/features.phtml#main-feat
>
> And the actual register used would depend on which account was being
> used as the target of the import.  IOW, for a stock, it would be
> as shown in the 8.7.1 Example here:
>
>
> http://www.gnucash.org/docs/v2.6/C/gnucash-guide/invest-sell1.html#idm242253328896
>
> Or, in the particular case in this thread, like that shown here:
>
>
> http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20150129/9f0f0f99/attachment-0003.png
>
> IOW, if, instead of the column types choices being as shown in:
>
>
> http://lists.gnucash.org/pipermail/gnucash-devel/attachments/20150129/9f0f0f99/attachment-0002.png
>
> they were:
>
>   None
>   Date
>   Num
>   Description
>   Transfer
>   R
>   Shares
>   Price
>   Buy
>   Sell
>   Balance
>
> Then the import would behave similar to the register, except it wouldn't
> be interactive.  For example, if all fields except:
>
>   Num, R, Buy, Sell, Balance
>
> were specified, then the import would mirror the interactive input to
> the register.
>
> This is what was proposed on the devel list here:
>
> http://lists.gnucash.org/pipermail/gnucash-devel/2015-January/038489.html
>
> IOW, if it can be done in the register, it can be done in a similar way
> in the import.
>
> >
> >
> >
> > [1] mention of SHA1 consistently distracts my thought processes to
> > something other than stocks and shares
>
> I got that name from some Gnucash example.  I can't remember where.
> I guess it's short for SHAre from stock_1.
>
> [snip]
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> 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.
>

-- 
Edward Doolittle
Associate Professor of Mathematics
First Nations University of Canada
1 First Nations Way, Regina SK S4S 7K2

« Toutes les fois que je donne une place vacante, je fais cent mécontents
et un ingrat. »
-- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI