converting qfx files to qif

[cbbrowne@hex.net]

On Thu, 26 Apr 2001 12:33:17 CDT, the world broke into rejoicing as
linas@linas.org (Linas Vepstas)  said:
> On Wed, Apr 25, 2001 at 11:06:29PM -0400, Peter Dolloff was heard to
> remark:
> > Hello there

>> I'm sending 3 ofx/qfx files to you.  The first two are named
>> cibc.ofx and cibcvisa.ofx.  They are from the Canadian Bank of
>> Commerce.  The first one is from a standard checking account, the
>> second contains transactions from a VISA account.  It's weird that
>> there actually differences between these two files even though
>> they're from the same financial institution.

>> The third file is from the Royal Bank of Canada.  In all cases, I
>> have changed the account numbers to all 9's.

Hmmm....  Familiar-sounding institutions...

It's not too surprising that the file formats would be different: It's
entirely probable that the division at CIBC that deals with bank
accounts is _completely_ independent from the division that deals with
VISA.

Some years back, I did consulting work "at" Bank of Montreal (I use
the word "at" loosely because with the work I did for the RRIF group,
I never once visited a bank location), and seemingly similar projects
required working with organizations in different provinces.

Similarly, one of my brothers was at IBM, writing PPC system
configuration software, and when they had a theory of using this
software both for RS/6000 and AS/400 systems [that use PPC hardware],
it was spectacularly impossible because making it joint work required
getting managers to get together all the way up the management line to
Lou Gerstner.  It was rather like concluding that in order to deploy
GnuCash at both the Department of the Interior and the Department of
Energy would require getting George W. Bush involved in the
decisionmaking process.  :-(

At any rate, no surprises.

> Thanks, I checked thse in.

> I am disappointed in the USE OF CAPTIAL LETTERS; although some are
> mixed case.

Evidently, CIBC is using the SGML version of OFX.

> I am much, much more sore in the lack of a double-entry/ledger
> format; I suspect this is a screw-up on the part of the OFX
> standard.  How can Microsoft, Quicken and Checkfree (the authors of
> ofx) blow it so badly?

I think the critical point is that Microsoft and Intuit have intense
and extensive experience at "blowing things badly."

> If gnucash imports these files, it will be painful, possibly more
> painful than QIF's !! We'll know what bank, bank account number, and
> currency things come from (and this is good, an improvement over
> QIF), but we don't know where things are going to.

That's a problem.

Mind you, it shouldn't come as a _completely_ unexpected thing.

After all, the folks at the bank [or Intuit, or MSFT] have no idea how
you plan to classify the transactions.  

Someone might take the [fairly silly] approach of basically having
three accounts:

 - The Bank
 - Incomes
 - Expenses

Or things might be rather more sophisticated :-).

> Maybe we can guess that anything with the string 'ATM' in the memo
> should transfer to a cash account.  Human intervention is
> unavoidable when there is a check made out to HEB groceries: neither
> gnucash nor the bank can make a valid guess.  But I am disappointed
> that the automatic withdrawls (electronic funds transfers) don't
> indicate the account name/number/'financial institution id' where
> the money is going to.  Even in the case of hand-written checks: it
> would be nice to know what the bank & account number was where the
> check was deposited.  That way, we could automate things: whenever
> gnucash saw the account number for 'home depot' gnucash could guess
> that it goes into the 'home improvement' account.  As it is, human
> intervention will be required for any ofx import. Arghhhh!

I'll disagree for a moment...  

If I'm at Home Depot or some such place, I would be _extremely
unhappy_ if credit card companies started giving out my account number
to everyone.

Flip side: _SOME DEGREE_ of Human Intervention will doubtless be
necessary.  But that can be improved on, for subsequent transactions.

The first time a check is made out to "HEB Groceries," it will
certainly be necessary to classify it by hand.

But subsequent times, there should be some [plausible magical
pattern-matching way] for the OFX loader to guess that "Groceries" is
a best guess.  Pattern matching would require throwing in extra
dialogs, and storing an extra table of "memorized past matches," which
would add a fair bit of code.

Simpler would be a register hack where a "BLANK" account type joins
those already existant.  

OFX imports (and perhaps QIF imports, if they don't indicate
meaningful category names) would propose using a special account of
type "BLANK" (rather than Income/Expense/Bank/Credit Card/...).
Transactions would be loaded, assigned to the "Blank" account.

The user would then go into the transaction, hit "tab," and find that
a proposed account would be filled in automagically based on past
experiences with vendors.
-- 
(reverse (concatenate 'string "ac.notelrac.teneerf@" "454aa"))
http://www.ntlug.org/~cbbrowne/resume.html
Out of my mind. Back in five minutes.