OFX Import and negative prices on stock transactions

[Dave Reiser]

Charles Gagnon wrote:
> 
> 
> David Reiser wrote:
>> On Feb 7, 2008, at 11:00 PM, Charles Gagnon wrote:
> [...]
>>>   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?

No. the importing function hasn't changed at all for a while.

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

It may be the case that there is no internal difference between ofx and 
qfx files. I suspect that Quicken can recognize an incoming .qfx file 
and glom onto it as a webconnect session. If there are any internal 
differences, they are likely to be in <INTU.SOMETHING> tags, which 
libofx just throws away anyway. But I don't think it could hurt to try 
the qfx files.

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

Negative total as in <TOTAL>-2269.00</TOTAL> in the ofx data stream.
The ofx spec says:
"In a stock buy, the total value is negative, the unit price is always 
positive, and the number of units is positive."
So I'm of the opinion that the spec requires your bank/broker to put the 
'-' sign in the total field for a buy so that libofx and gnucash can 
interpret it correctly.

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

Yes, but your clip below shows that the bank is listing positive numbers 
for all of: units, unitprice, and total. That tells me that your ending 
up with a negative unitprice is happening on the gnucash end of 
processing. That might be modifiable for what looks like a pathological 
case. However, it would take a bunch of effort to make sure the patch 
wouldn't be interfering with properly constructed ofx files.

It might be the case that the Buytocover attribute is mucking things up. 
But I've never seen such a transaction elsewhere, so I don't know.

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

http://www.ofx.net/DownloadPage/Downloads.aspx
> 
> 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