GnuCash - Loading Pre-downloaded Transactions

[Doug Laidlaw]

On Sun, 16 Sep 2007 07:48:22 pm J S wrote:
> Derek
>
> Thanks .. I will maintain my sense of humor here.  I know I maybe a pain
> but facts are facts.  I have had a read of the QIF format but will revisit
> it again, but let me state my position again.
>
> The QIF files I have are NOT direct unloads from Quicken application.  As I
> said, they are unloaded from my Bank which gives an option for QIF & other
> formats, like CSV or Microsoft Money.  All the files I have unloaded from
> the Bank has !Type:Bank, even for Credit Card.  The Bank does not know if I
> use Quicken or GnuCash or some other accounting application, it only gives
> me an option to unload my transactions in various formats.
>
> Now to your last paragraph, I have NOT been a deer, I have been to the end
> of QIF import session, many times, but just did not 'apply' BECAUSE GnuCash
> did not indicate into which account it will apply the transactions. Well in
> fact I just did 'apply' & guess what ... I do not know where GnuCash has
> applied those transactions ....  I have checked each account & there is no
> trace of them!  Explain that to me!!
>
> As I stated before I have had no issues loading the same QIF files that
> were unloaded from my BANK with July 07 transactions, why GnuCash cannot
> import transactions for August 07? Why the rule is different for August
> transactions??
>
> The 70% failure to import QIF files for August transactions is perhaps 90%,
> I have not tried the Visa QIF file as yet, all the rest disappears into
> some void when 'apply' is clicked.
>
> I will send you a screenshot of each of the steps I take, if that helps,
> but as I said I will read the QIF format BUT I hampered as I have no
> control over that format .. al I can do is discuss with my Bank that the
> QIF format is potentially wrong BUT I need evidence as I can happily load
> same QIF format files into another accounting application with no issues!!
>
> PS:  I have also gone thru a few GnuCash documentations (via Google), but
> none even touches on the issue I have.
>
> Finally, I am more than willing to cut & run from GnuCash, so if you or
> others think that this 'issue' I am bringing up is sheer waste of time let
> me know.  Time is valuable to all of us, especially to all those who have
> contributed towards GnuCash (& other free products).   I am not a
> 'critique' nor I represent any commercial interests (like Quicken, MYOB or
> any other commercially available packages).  I am just a simple 'user' &
> indeed if GnuCash can perform similar functions, I may as well contribute
> the same as I may pay & buy Quicken or MYOB etc etc.
>
> PSS: I am not from any European or North American countries.  If the Banks
> in those countries offer a more versatile QIF format files, then perhaps
> that explains a lot.
>
>
>
>
>
> -----Original Message-----
> From: Derek Atkins [mailto:warlord at MIT.EDU]
> Sent: Saturday, 15 September 2007 10:52 PM
> To: J S
> Cc: 'Josh Sled'; 'Singh, John'; gnucash-user at lists.gnucash.org
> Subject: RE: GnuCash - Loading Pre-downloaded Transactions
>
> Hi,
>
> Quoting J S <singhj at iprimus.com.au>:
> > Derek
> >
> >
> >
> > Again Thanks .. now what do you mean if there are any QIF accounts in QIF
> > Files!!!
>
> Okay, now is the time for you to tuck your tail between your legs
> and google for "QIF Format" and go do a LITTLE bit of research before
> you respond to this email (unless that response is "okay, I'm reading
> up on the QIF File Format").
>
> The fact that your file is a !Type:Bank implies that there are
> probably NOT any QIF Accounts in there, but there still could be
> in the L tags.
>
> > The local Banking Institutions only puts in a '!Type:Bank' as the first
> > record in a QIF file, when we or I unload from the Bank's web site; for
> > example:
> >
> > [snip]
> > Let us say for the above QIF file I have a Liability account called
>
> XYZ-Loan
>
> > (type=Bank) with an opening balance of $10000.00.  If I now try to load
> > it there is approx 70% failure rate. In fact I tried it just now &
> > GnuCash
>
> just
>
> > 'fails' to pick up the QIF file and I end up with a balnk druid 'Match
> > QIF account . as per attachment GnuCash-import-4 sent in last email!!
>
> First, why would "XYZ-Loan" be a type=Bank?  A loan should be a Liability.
> What do you mean by "70% failure rate"?  What do you mean by "fails
> to pick up the QIF file"?
>
> Of COURSE you're going to end up with a blank Match QIF Account druid..
> THERE AREN'T ANY QIF ACCOUNTS IN THE FILE!!!  There certainly were
> no accounts in the snippet that I cut out above.  There are only Payees,
> which you'll have to match to an account in the "Match Memos/Payees"
> page.
>
> The problem, of course, is that it's quite likely that the importer
> wont like the fact that it's trying to pay down a liability, and might
> instead try to make an Expense account.  You'll just have to try it
> and see..  Save your file before you import and then after the import
> take a look and quit gnucash if the import did something weird -- then
> you can try again.
>
> > If you suggesting the above QIF file to have XYZ-Loan somewhere in the
> > record then let me know.  I can edit the file & place it but I am not
> > sure of the QIF file record layout.  That is, should it be
> > !Type:Bank:Liability or similar??
>
> Well, that's certainly ONE way to get it to do it.  You can add the
> QIF Account information to the appropriate transactions.  (refer back
> to your google response for more information).
>
> > All the QIF files I have are formatted as above and I have been able load
>
> a
>
> > few of them but not all.  It is just weird.
>
> I think you're just confused.
>
> > I hope this explains my dilemma.
>
> Not particular, other than I think you're very very confused about what
> QIF is and what's in your QIF files.  I think you're expecting something
> that isn't there.  The QIF Account page is, honestly, USUALLY empty.  So
> I think you're just stopping there because you're dumbfuddled and instead
> of hitting NEXT to see what else might show up you're stopping like a deer
> in the headlights.  Don't be a deer.  Just keep going.
>
> Good Luck,
>
> -derek

Sounds like the time I tried to import a QIF generated by KMyMoney2. It didn't work.  As you pointed out, Derek, the dates were in some strange format.

Here in Australia, QIF is available from all the banks, and it is designed to import into Quicken.  It imports easily into Gnucash (and KMyMoney2).  But even Quicken does not have a clear explanation of what a QIF should be, only a few examples, without any explanation.

A Loan is not a Bank Account, but like the OP, I have called them Bank in Quicken.

In answer to the question about the "Type:" tag at the start, it might help to EXPORT a QIF file from the loan account and look at it.  I would have thought that if the account is a Bank account, it should be Type:Bank, but nothing is guaranteed.

Apart from that, the QIF should have transaction records ending in a ^ on its own line.

The QIF itself, in my experience, NEVER tells you what account it should go into.  You need to tell it during the import process.  Even Quicken won't do that.  If I open my checking account in Quicken, any QIF I download will go into that account by default, but I can select another.  I can however import a QIF while in the opening page, with no ledger open.  Then it defaults to the first account in the drop-down menu.

As for allocating transactions to individual accounts in Gnucash (or categories in Quicken,) the Bank doesn't know what categories to use.  There is nothing in the Bank's QIF to direct it into a particular account.  That has to be set by the user when importing, otherwise they will probably end up as "Unspecified."

Since we are both Aussies, I am happy to try to help John off list.  It doesn't sound like a Gnucash issue.

Doug.
-- 
All reformers are bachelors.          -- George Moore.
And so was any man or woman that achieved anything.