OFX Import and negative prices on stock transactions

[Charles Day]

On Feb 8, 2008 11:03 AM, Charles Gagnon <charlesg at unixrealm.com> wrote:

>
>
> David Reiser wrote:
> > On Feb 7, 2008, at 11:00 PM, Charles Gagnon wrote:
> [...]
> >> Instead, I get one transaction only (so the commission is rolled into
> >> the base price which) and it looks like:
> >
> > Yes, libofx rolls the commission into the base price. Recently, fields
> > have been added to libofx so that commissions and fees could be
> > handled separately, but I don't think the code has been updated to use
> > those changes.
>
> That's okay I think. My focus with gnucash is accounting and taxes and I
> think rolling the commission into the basis prices fits that model.
>
> >>
> >>   Buy 60 units of ABAX at -37.80 = 2269 (in the sell column)
> >>   (the quantity shows up positive as it should it a BUY but the price
> >> is negative)
> > That's odd. The buy should have a negative total -- cash flow
> > convention is that it is negative when it leaves your account, as it
> > (the cash) is leaving your account to buy the stock the total should
> > be negative.
>
> This one is killing me. I would have to manually reverse all my
> transactions. I am running: GnuCash 2.2.1, Built 2007-11-23 from r16462.
> Would a newer version help me?
>
> Anything I could try to see if it works better?
>
> My insitution can also produce QFX files. Does that have a chance at
> being handled better? As I said, I've been using gnucash forever but I'm
> new to the ofx/qfx thing.
>
> > All of my stock purchases from ameritrade show a positive unitprice,
> > but a negative total (as I think it should to follow the common
> > convention). I'm pretty sure the ofx spec also proclaims that stock
> > purchases should have a negative total.
>
> A negative total? What do you mean? The purchase will show up in the buy
> column but the total will still be positive no? Or should the
> institution put a negative total for it to show in the right column?
>

The OFX 2.1.1 standard says that for buys, the quantity of shares (<UNITS>),
price per share (<UNITPRICE>), and commission (<COMMISSION>) should all be
positive. The total price (<TOTAL>) should be negative and reflect the grand
total of the transaction (i.e. including commission). So Interactive Brokers
would seem to have the sign wrong on the their (otherwise correct) total.
GnuCash's behavior makes sense given the bad data.

But why should we even need signed numbers if we already know that we are
doing a buy, as evidenced by the <BUYSTOCK> tag? It seems unnecessary, but
that's just the way OFX works, I guess.

Maybe my total is positive (see OFX extract below), the number shows up
> as a sell. And the only to 'sell' a positive quantity (+60) is to make
> the price negative so Gnucash adjusts.
>
> In other words, it should be:
>
> buy     +60             +10/ea          -600
> sell    -60             +10/ea          +600
>
> But I get:
>
> buy     +60             -10/ea          +600 (shows up as a sell)
> sell    -60             -10/ea          -600 (shows up as a buy)
>
> Do I make sense?
>
> Anybody has link to the OFX standard I could refer to if I want to
> contact IB and ask why the total amount for the transactions do not
> follow standards.
>

OFX specs can be found at: http://www.ofx.net/DownloadPage/Downloads.aspx

If you download the zipped 2.2.1 specs, open the PDF and take a look at
section 13.3 and the example in 13.12.

-Charles


> Thanks.
>
> [...]
> >> ### extract from OFX file ###
> >>            <BUYSTOCK>
> >>                <INVBUY>
> >>                 <INVTRAN>
> >>
> >> <FITID>20080115202000.000[-5:EST].xxxxxxxxxx.USD.002567105</FITID>
> >>                    <DTTRADE>20080115202000.000[-5:EST]</DTTRADE>
> >>                 </INVTRAN>
> >>                <SECID>
> >>                    <UNIQUEID>002567105</UNIQUEID>
> >>                    <UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
> >>                </SECID>
> >>                <UNITS>60.0</UNITS>
> >>                <UNITPRICE>37.8000</UNITPRICE>
> >>                <COMMISSION>1.00</COMMISSION>
> >>                <TOTAL>2269.00</TOTAL>
> >>                <CURRENCY>
> >>                    <CURRATE>1.0</CURRATE>
> >>                    <CURSYM>USD</CURSYM>
> >>                </CURRENCY>
> >>                <SUBACCTSEC>CASH</SUBACCTSEC>
> >>                <SUBACCTFUND>CASH</SUBACCTFUND>
> >>                </INVBUY>
> >>                <BUYTYPE>BUYTOCOVER</BUYTYPE>
> >>              </BUYSTOCK>
> >> ### end ###
> >>
> >> --
> >> Charles Gagnon                   | My views are my views and they
> >> http://unixrealm.com             | do not represent those of anybody
> >> charlesg at unixrealm.com        | but me.
> >
> >
> > Dave
> > --
> > David Reiser
> > dbreiser at earthlink.net
> >
> --
> Charles Gagnon                   | My views are my views and they
> http://unixrealm.com             | do not represent those of anybody
> charlesg at unixrealm.com        | but me.
> _______________________________________________
>