OFX not working

[David Reiser]

On Jan 4, 2005, at 8:29 PM, Benoit Grégoire wrote:

> On Tuesday 04 January 2005 20:08, badgers wrote:
>> I have spent the last week reading about how to import ofx files.
>> it seems that vanguard.com give me a choice in formats. When I googled
>> the subject it seemed that gnucash liked the ofx format.
>> but when I import my transactions I end up with -25 bucks.  I have a
>> positive balance and always have.
>> It seems that some of the transactions are not comming in.
>> This is a simple money market account. the share price is always $1.00
>> and it always has been.....
>> any ideas?
>
[libofx message examples snipped]

> The first step to diagnose is to run ofxdump on your file and see if 
> the
> output makes sense and is complete.  If it is, check that your 
> commodity is
> set properly on the gnucash account.
>

!!  My recent experience is that the commodity definition requirement 
is more stringent than I thought it would be. If the commodity is set 
up manually, most people will end up leaving the CUSIP field blank. 
When libofx imports an ofx file, it uses the CUSIP as the primary match 
key. If the user tells the importer which commodity and account to use, 
but the user's commodity definition CUSIP field is blank, 
libofx/gnucash will get the transaction into the right account, but it 
will mangle the share and price handling.

For badgers case, I think the commodity definition should be:

Full Name: Vanguard Prime Money Market
Symbol/abbreviation: VMMXX
Type: NYSE
CUSIP or other code: 922906201
Fraction traded: 1/10000

I don't think the name matters. The NYSE probably doesn't matter, at 
least in the U.S. I tell gnucash to get my Vanguard quotes from 
Vanguard, but that choice only appears in the account definition editor 
under Price Quote Source.

The best place to get a CUSIP is from an ofx file you are importing. If 
you don't have any transactions in gnucash for that commodity yet, let 
libofx define a new commodity during an import, then go back and delete 
your manually defined commodity -- it's a lot faster.  ofxdump makes it 
a little easier to read the CUSIP definitions. In a pinch, you can run 
the importer on the ofx file and write down the code when the dialog 
pops up asking you to choose or create the commodity. If you do have to 
write down the code and then manually edit your commodity definition, 
you will probably have to go in to the account register and delete 
mangled transactions, too.

The reason I recommend using an ofx file to find CUSIPs is that the 
CUSIP-security mapping only has to be unique in one direction. 
Ameritrade tells me that the CUSIP for Time Warner (TWX) is DeSoto0, 
whereas the Fidelity online CUSIP query utility gives a nine digit 
integer for the TWX CUSIP. On the other hand, I think the Fidelity 
utility and Ameritrade agree on all the other CUSIPs I've needed. It's 
possible that my TWX is a transient issue, as my TWX was originally 
purchased as AOL. I don't know what the brokerage/exchange data 
managers do to handle CUSIP consolidation in the case of 
mergers/takeovers. Whatever they're doing, you want your gnucash 
definitions to agree with what the brokerage ofx file is sending.

Best bet: let libofx define new commodities during an import the first 
time your ofx file has transaction data for a new stock/mutual fund.


> If it isn't, I'll need a sample file, and a description of the expected
> behaviour.  I have no way to test investments account here, and this 
> code was
> writtent directly from the spec, but isn't a complete implementation.  
> Either
> the abcence of one of the above "WRITEME" is the source of your 
> problem, or I
> misinterpreted the meaning of one of the elements.
> -- 
> Benoit Grégoire, http://benoitg.coeus.ca/

Dave
--
David Reiser
dbreiser at earthlink.net