Dates in ofx download setup, brokerage data

David Reiser dbreiser at earthlink.net
Sat Dec 3 21:21:59 EST 2011


On Dec 2, 2011, at 3:07 PM, Martin Preuss wrote:

> Hi,
> 
> Am 02.12.2011 07:07, schrieb David Reiser:
> [...]
>> It isn't just the gnucash implementation of aqbanking, it is every version of aqbanking. 
>> Aqbanking cannot handle anything in a brokerage download except some
> dividend payments.
>> The reason you see no transactions is that aqbanking deletes
> transaction it doesn't know how to handle during importing.
> [...]
> 
> I asked a few times on the gnucash lists for some help with those
> transactions but so far nobody answered.

I thought I had sent a file a while back, but inspection of my email logs indicates I only sent some vague generalizations. Sorry about that.
> 
> I have no example files from brokerage accounts and even if I did: I
> have no idea how those transactions should be handled (e.g. how those
> transactions need to be translated to gnucash transactions and splits)...

Looking again through aqbanking, it looks like most of the information is there, mostly in src/plugins/imexporters/ofx/parser/g_*, so it looks like the largest amount of work is having someone who knows both aqbanking and gnucash well enough to extend gnucash/src/import-export/aqbanking/gnc-ab-utils.c. It looks like that file needs an expansion of the function gnc_ab_trans_to_gnc() to handle investment transactions similar to the way investments are handled in ofx_proc_transaction_cb() in gnucash/src/import-export/ofx/gnc-ofx-import.c when gnucash is importing using libofx. 

gnc-ab-utils.c already handles creation of gnucash splits and transactions from aqbanking imports, so I hope the extension to investment transactions isn't too painful. 

There are a few cases I'm not sure about in aqbanking. BUYMF and SELLMF (mutual funds) are enough like regular stocks that I would expect mutual fund buys and sells to be similarly to stock buys and sells in aqbanking/src/plugins/imexporters/ofx/ofx-libofx.c.

The one piece of mutual fund investment that is more frequent than in stocks is dividend reinvestment. While I see some signs of aqbanking handling that, here is a sample reinvestment transaction:

<REINVEST>
<INVTRAN>
<FITID>09914219496.0040.09262003.0
<DTTRADE>20030926160000.000[-5:EST]
<DTSETTLE>20030926160000.000[-5:EST]
<MEMO>INCOME DIVIDEND    .36
</INVTRAN>
<SECID>
<UNIQUEID>922908108
<UNIQUEIDTYPE>CUSIP
</SECID>
<INCOMETYPE>DIV
<TOTAL>-30.88
<SUBACCTSEC>CASH
<UNITS>0.308
<UNITPRICE>92.08
<FEES>2.5
</REINVEST>

While aqbanking does handle <SECLIST> aggregates, I can't follow it well enough to tell if the data all gets to the same place where gnucash would get it. Gnucash identifies stocks more by Ticker symbol than CUSIP. All the ofx files I've seen identify transactions by <UNIQUEID> rather than <TICKER>. The only place the two are connected in an ofx file is in the seclist:

<SECLISTMSGSRSV1>
<SECLIST>
<STOCKINFO>
<SECINFO>
<SECID>
<UNIQUEID>009128307</UNIQUEID>
<UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
</SECID>
<SECNAME>AIR METHODS CORP COM PAR $.06</SECNAME>
<TICKER>AIRM</TICKER>
</SECINFO>
</STOCKINFO>
</SECLIST>
</SECLISTMSGSRSV1>

The one thing of greatest interest to me that seems to be missing nearly entirely in aqbanking is splits (member of <INVTRANLIST>):

<SPLIT>
<INVTRAN>
<FITID>2857893972</FITID>
<DTTRADE>20070601202953</DTTRADE>
<MEMO>STOCK SPLIT</MEMO>
</INVTRAN>
<SECID>
<UNIQUEID>112585104</UNIQUEID>
<UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
</SECID>
<SUBACCTSEC>CASH</SUBACCTSEC>
<OLDUNITS>225</OLDUNITS>
<NEWUNITS>337</NEWUNITS>
<NUMERATOR>1</NUMERATOR>
<DENOMINATOR>1</DENOMINATOR>
</SPLIT>

That was a 3-for-2 split for a stock with an odd number of shares. The 'uneven' split results in what's referred to as a 'cash in lieu of' transaction that represents the partial share sold for some price representative of the stock's price on the day of the split. In my account, the cash of the 'in lieu of' showed up in 2 transactions, so I'm not sure what's normal. 

<TRANSFER>
<INVTRAN>
<FITID>2881257642</FITID>
<DTTRADE>20070612055637</DTTRADE>
<MEMO>MONEY MARKET PURCHASE</MEMO>
</INVTRAN>
<SECID>
<UNIQUEID>9ZZZFD104</UNIQUEID>
<UNIQUEIDTYPE>CUSIP</UNIQUEIDTYPE>
</SECID>
<SUBACCTSEC>CASH</SUBACCTSEC>
<UNITS>20.95</UNITS>
<TFERACTION>IN</TFERACTION>
<POSTYPE>LONG</POSTYPE>
</TRANSFER>

The uniqueid is bogus, it refers to the broker's sweep account. The uniqueid does not appear in the seclist. The other  transaction with the 'in lieu of' cash is:

<INVBANKTRAN>
<STMTTRN>
<TRNTYPE>DEBIT</TRNTYPE>
<DTPOSTED>20070612055626</DTPOSTED>
<TRNAMT>-20.95</TRNAMT>
<FITID>2881257638</FITID>
<MEMO>MONEY MARKET PURCHASE</MEMO>
</STMTTRN>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBANKTRAN>

So neither potential transaction uses the standard <TOTAL> tag for the amount for cash associated with an investment transaction.

I think I may have used gnucash's Stock Split Assistant (Action>Stock Split...) to enter the split rather than creating the split from the ofx download, so libofx probably didn't have to handle this one at that time.
> 
> 
> Regards
> Martin

I'll see if I can clean up one of my ofx investment files enough to be willing to give it to you. Thank you for all your help over several years.

Dave
--
David Reiser
dbreiser at earthlink.net




More information about the gnucash-user mailing list