OFX Import and negative prices on stock transactions

[David Reiser]

On Feb 7, 2008, at 11:00 PM, Charles Gagnon wrote:

> I know this has been discussed before. I found traces of the topic  
> here
> and there but never really an answer. This was the latest:
> http://www.nabble.com/Bank-Interest-in-Wrong-Column-on-Reconcile-td13501251.html
>
> I import OFX files from Interactive Brokers. I will have many stock
> transactions over time and I'm trying to make sure the process is  
> fairly
> easy and mechanical. One thing I notice is the monthly  
> reconciliation is
> that amounts appear to be reversed (showing as negative) making the
> whole transaction opposite what it should be. I included a seperate
> transaction at the end of the file. It should have shown as:
>
>   Buy 60 units of ABAX at 37.80 = 2268 (in the buy column)
>   Commission of 1.00
>   TOTAL for transaction 2269
>
> 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.
>
>
>   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.

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.

None of my unitprice values is negative. But I've also never done any  
shortselling or covering.

>
>
> I am not sure what to do. Any ideas? If I need to parse my OFX in perl
> and pre-process it's worth the trouble of writing the perl but I  
> need to
> know a bit more about how gnucash processes the OFX.
>
> The one thing I noticed about the OFX is the
> "<BUYTYPE>BUYTOCOVER</BUYTYPE>". Not sure why IB marks them as buy to
> cover but it shouldn't really make a difference. It was a regular  
> long,
> not a buy to cover.
>
> Another quick general OFX question, does gnucash read the  
> transaction ID
> to prevent duplicates? If I try to re-import the same file, will it
> detect already imported transactions or no? I am just trying to figure
> out at what frequency to do my imports and my reconciliation.

Yes. Gnucash stores the ofx transaction id for any transaction you  
keep. If you delete a transaction after importing, and then import  
another file that contains that transaction, gnucash will reimport it  
rather than being able to remember that you deleted it. Otherwise,  
duplicates of earlier imports aren't even shown in the transaction  
matcher.

>
>
> Thanks  in advance.
>
> ### 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