QIF Import and accounts
Ian Lewis
ianmlewis at gmail.com
Mon Dec 17 09:58:06 EST 2007
Like Derek said, the QIF importer needs to be rewritten. It becomes obvious
when you look at the number and strangeness/severity of the bugs.
http://bugzilla.gnome.org/buglist.cgi?query_format=advanced&short_desc_type=allwordssubstr&short_desc=&classification=Other&product=GnuCash&component=QIF+Import&long_desc_type=substring&long_desc=&status_whiteboard_type=allwordssubstr&status_whiteboard=&keywords_type=allwords&keywords=&bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=NEEDINFO&emailassigned_to1=1&emailtype1=substring&email1=&emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&emailcc2=1&emailtype2=substring&email2=&bugidtype=include&bug_id=&chfieldfrom=&chfieldto=Now&chfieldvalue=&cmdtype=doit&order=Reuse+same+sort+as+last+time&field0-0-0=noop&type0-0-0=noop&value0-0-0=
2007/12/17, Georg Stapper <georgstapper at yahoo.com>:
>
> Thanks Ian,
>
> it seems that I am not too stupid. The system just behaves unfortunately
> like this for the moment.
> There is still the possibility of total separation. That might not be
> perfect for everybody.
>
> Georg
> ----- Original Message ----
> From: Ian Lewis <ianmlewis at gmail.com>
> To: Georg Stapper <georgstapper at yahoo.com>
> Sent: Monday, 17 December, 2007 4:23:52 PM
> Subject: Re: QIF Import and accounts
>
> Your solution may very well be more valid. I'm making a suggestion to try
> your account structure that way and see if the import works better. I have
> my accounts structured that way because I am not a business and it is not
> particularly useful to me to know balances in USD for accounts I have in
> JPY. If you want your accounts and balance sheets to look a certain way then
> I would say don't take my previous advice.
>
> However, I tried it out myself using a new file with default common
> accounts and trying to import your first QIF specified as JPY into an Asset
> tree that was specified as being in Dollars.
>
> I had the following:
>
> Assets (USD)
> Current Assets (USD)
> Checking (JPY)
> Barclays (JPY)
> ...
> ...
> Expenses (USD)
> ...
> ...
>
> I imported the QIF as JPY into the Barclays account. The accounts that the
> transactions were made against were created as new. It gave me the
> following:
>
> Assets (USD)
> Current Assets (USD)
> Checking (JPY)
> Barclays (JPY)
> ...
> ...
> Expenses (USD)
> flat (JPY)
> renovation (JPY)
> ...
> Assets 2 (JPY)
> Current Assets 2 (JPY)
> Checking (JPY)
> Barclays (JPY)
> reimbursed expenses (JPY)
> ...
>
> which is totally weird and wrong. This is a bug if ever I saw one. I think
> it will go away if structure your accounts as I suggested, but it's
> certainly up to you how you structure your accounts. Gnucash certainly lets
> you have account structures where sub-accounts have a different currency
> than the parent.
>
> I filed a report for this issue. See:
> http://bugzilla.gnome.org/show_bug.cgi?id=504046
>
> Ian
>
> 2007/12/17, Georg Stapper < georgstapper at yahoo.com>:
> >
> > Yes, I understand, but in business it is pretty common to have one
> > currency for total assets and total liabilites on a balance sheet. So, I
> > thought my setup was pretty straight forward.
> >
> > If there is no other solution then I have to do it your way anyway. It
> > just surprises me a bit.
> >
> > G.
> >
> > ----- Original Message ----
> > From: Ian Lewis < ianmlewis at gmail.com>
> > To: Georg Stapper <georgstapper at yahoo.com>
> > Cc: gnucash-user at gnucash.org
> > Sent: Monday, 17 December, 2007 1:32:10 PM
> > Subject: Re: QIF Import and accounts
> >
> >
> > 2007/12/17, Georg Stapper <georgstapper at yahoo.com>:
> > >
> > > Where do I see in GNC what the locale is?
> > > Or is it just set by MS Windows in my case? It's GBP for the moment.
> > >
> >
> > Hmm. Maybe it's trying to create the new accounts for another reason
> > besides currency.
> >
> > Why is it better to set up different account trees for different
> > > currencies?
> > > I would want to know my total assets in EUR, but also in GBP or
> > > whatever.
> > > I would also want to have an overview of the different currencies as
> > > well.
> > > This should all be possible with different reports, or not?
> > >
> >
> > Yah, but accounts kind of have levels so you can set how deep your
> > report goes in the account tree. i.e. Choose an level of 1 and the asset
> > account and it will conglomerate all your accounts under one account in the
> > report. This kind of does wierd stuff when the sub-accounts are different
> > currencies. I'm pretty sure it doesn't work properly.
> >
> > What I was suggesting to be clear is something like the following. It
> > might be cleaner inside of gnucash. When reporting, if you want to include
> > all your assets then you can select the accounts from each of the Asset
> > trees.
> >
> > Assets (EUR)
> > Current Assets (GBP)
> > Cash (GBP)
> > Checking/Barclays (GBP)
> > reimbursed expenses (GBP)
> > Savings/Barclays (GBP)
> > Assets (ZAR)
> > Current Assets (ZAR)
> > Cash (ZAR)
> > Checking/Absa (ZAR)
> > Savings/Absa (ZAR)
> > Assets (EUR)
> > Current Assets (EUR)
> > Cash (EUR)
> > Checking/Diba (EUR)
> > Savings/Diba (EUR)
> >
> > Ian
> >
> > Cheers,
> > > G.
> > > ----- Original Message ----
> > > From: Ian Lewis < ianmlewis at gmail.com>
> > > To: Georg Stapper <georgstapper at yahoo.com>
> > > Cc: gnucash-user at gnucash.org
> > > Sent: Monday, 17 December, 2007 11:56:47 AM
> > > Subject: Re: QIF Import and accounts
> > >
> > > The currency selection comes up for you when you do QIF imports? Lucky
> > > you!
> > >
> > > Ok, I saw that your reimbursed expenses account is in GBP and the QIF
> > > is GBP but the locale currency (or reporting currency (not totally sure what
> > > it uses)) is EUR right? That should have worked if you selected the right
> > > currency. The accounts in the QIF you provided seem strait.
> > >
> > > It's purely speculation on my part and I would have to check myself
> > > but the reason might also be that gnucash is getting confused because your
> > > account structure has accounts with different currencies than their parent
> > > account. I'm not sure why it would get confused necessarily but usually you
> > > have separate account trees for different currencies since balances for
> > > sub-accounts are usually summed up as part of the balance for parent
> > > accounts ( e.g. so you can see the total balance of your assets in a
> > > particular currency).
> > >
> > > Ian
> > >
> > > 2007/12/17, Georg Stapper <georgstapper at yahoo.com >:
> > > >
> > > > Ian,
> > > >
> > > > Thanks for your reply.
> > > > Just to make sure, at the top level I set EUR (Asset, Equity etc.).
> > > > Each QIF file is has got only one currency. I chose the respective currency
> > > > for the account during the import process.
> > > >
> > > > Cheers,
> > > > Georg
> > > >
> > > > ----- Original Message ----
> > > > From: Ian Lewis < ianmlewis at gmail.com>
> > > > To: Georg Stapper <georgstapper at yahoo.com>
> > > > Cc: gnucash-user at gnucash.org
> > > > Sent: Monday, 17 December, 2007 10:41:28 AM
> > > > Subject: Re: QIF Import and accounts
> > > >
> > > > Georg,
> > > >
> > > > Thanks for posting this in user. This is really where an issue like
> > > > this should be posted.
> > > >
> > > > I picked up on what your problem might be the second time around. I
> > > > had a similar problem recently and since you are working with multiple
> > > > currencies I'm guessing this is your problem. If you are importing QIF files
> > > > that have a different currency than the currency for your locale it will
> > > > often try to import the QIF as your locale currency rather than the account
> > > > currency. I submitted a bug for this. The QIF import should allow you to
> > > > select a currency.
> > > >
> > > > See this mailing list thread: https://lists.gnucash.org/pipermail/gnucash-user/2007-December/thread.html#22727
> > > >
> > > > and the bug report: http://bugzilla.gnome.org/show_bug.cgi?id=504007
> > > >
> > > > Feel free to add yourself to the notify list on the bug report.
> > > >
> > > > I believe that I was able to import properly by changing the default
> > > > reporting currency in the preferences->reporting to the currency I was
> > > > importing then doing the import and then changing it back to what it was
> > > > before. I would have to try that again when I get home to confirm but give
> > > > it a try.
> > > >
> > > > Ian
> > > >
> > > > 2007/12/17, Georg Stapper <georgstapper at yahoo.com >:
> > > > >
> > > > > Hi there.
> > > > >
> > > > > my checking and savings accounts are with ABSA (ZAR), Barclays
> > > > > (GBP), and ING DIBA (EUR). So far I kept track of my accounts with Excel,
> > > > > but that's far from perfect and I thought it would take too much time to put
> > > > > it all together. I have spent so much time now trying to import my Excel
> > > > > data history into a software package that I could have continued with Excel
> > > > > for the rest of my life. My wife is freaking out already.
> > > > >
> > > > > So I am looking for a better option. Strangely enough only the
> > > > > South African bank allows to download the transaction data in different
> > > > > formats. The other two banks provide only CSV downloands for each account
> > > > > separately. My first attempt was MS Money 2007 with QIF files and a category
> > > > > and a sub-category, but MS Money does not even recognise the categories of
> > > > > its own QIF files. GNU Cash seems to be the better choice then.
> > > > >
> > > > > Instead of using just two categories I tried to set up the whole
> > > > > account structure using the category field and a colon as the account
> > > > > separator. This ended up in a mess. So, I decided to set up the account
> > > > > structure by hand. A simplified version looks like this:
> > > > >
> > > > > Assets (EUR)
> > > > > Current Assets (EUR)
> > > > > Cash (EUR)
> > > > > EUR
> > > > > GBP
> > > > > ZAR
> > > > > Checking (EUR)
> > > > > Absa (ZAR)
> > > > > Barclays (GBP)
> > > > > reimbursed expenses (GBP)
> > > > > Diba (EUR)
> > > > > Savings (EUR)
> > > > > Absa (ZAR)
> > > > > Barclays (GBP)
> > > > > Diba (EUR)
> > > > >
> > > > > Equity (EUR)
> > > > > opening balance EUR
> > > > > opening balance GBP
> > > > > opening balance ZAR
> > > > >
> > > > > Expenses (EUR)
> > > > > flat (GBP)
> > > > > renovation (GBP)
> > > > >
> > > > > Income (EUR)
> > > > > Bonus (EUR)
> > > > > Barclays (GBP)
> > > > >
> > > > > If I try to import the following QIF files for my Barclays
> > > > > accounts GNC comes up with an additional asset account structure due to "QIF
> > > > > import: Name conflict with another account" only for the reimbursed
> > > > > expenses.
> > > > >
> > > > > During the import process the QIF account name and the GNC account
> > > > > name for both physical accounts are recognised to be the same. I am happy
> > > > > with that.
> > > > >
> > > > > The problem starts when GNC tries to match the QIF categories its
> > > > > own accounts even though they should be exactly the same. It wants to set up
> > > > > another account for the reimbursed expenses even though it exsits already:
> > > > > Assets:Current Assets:Checking:Barclays:reimbursed
> > > > > expenses
> > > > > By double clicking on the entry the "Select Account" window pops
> > > > > up, but I cannot change there anything except setting up another new
> > > > > account. That does not help. The same applies to
> > > > > Assets:Current Assets:Savings:Barclays
> > > > > and
> > > > > Expenses:flat:renovation.
> > > > >
> > > > > The result is a mixture of new accounts in the existing account
> > > > > structure and a whole new "Asset 2" branch.
> > > > >
> > > > > I did not go any further to import my EUR and ZAR accounts,
> > > > > because it's a mess already at this stage.
> > > > >
> > > > > First file:
> > > > > ------------
> > > > > !Account
> > > > > NAssets:Current Assets:Checking:Barclays
> > > > > DBarclays Checking
> > > > > TBank
> > > > > /01/08/2007
> > > > > $0.00
> > > > > ^
> > > > > !Type:Bank
> > > > > N1
> > > > > D03/08/2007
> > > > > MEmployer EXPENSES
> > > > > T100.00
> > > > > PEmployer
> > > > > LAssets:Current Assets:Checking:Barclays:reimbursed expenses
> > > > > ^
> > > > > N2
> > > > > D08/08/2007
> > > > > MK A C BUILDERS MER
> > > > > T-16.00
> > > > > PK A C BUILDERS MER
> > > > > LExpenses:flat:renovation
> > > > > ^
> > > > > N3
> > > > > D28/11/2007
> > > > > MMe
> > > > > T-100.00
> > > > > PMe
> > > > > LAssets:Current Assets:Savings:Barclays
> > > > >
> > > > > Second file:
> > > > > ----------------
> > > > > !Account
> > > > > NAssets:Current Assets:Savings:Barclays
> > > > > DBarclays Tracker Account
> > > > > TBank
> > > > > /01/8/2007
> > > > > $0.00
> > > > > ^
> > > > > !Type:Bank
> > > > > N1
> > > > > D29/10/2007
> > > > > MMe
> > > > > T1000.00
> > > > > PMe
> > > > > LAssets:Current Assets:Savings:Barclays
> > > > > ^
> > > > > N2
> > > > > D29/10/2007
> > > > > MEmployer
> > > > > T1000.00
> > > > > PEmployer
> > > > > LIncome:Bonus:Barclays
> > > > > ^
> > > > > N3
> > > > > D28/11/2007
> > > > > MMe
> > > > > T100.00
> > > > > PMe
> > > > > LAssets:Current Assets:Checking:Barclays
> > > > > ^
> > > > >
> > > > >
> > > > > I have some questions now:
> > > > > 1) Is there anything wrong with my QIF files?
> > > > > 2) How do I make GNC import my accounts properly?
> > > > > 3) Should I maybe use an older GNC version?
> > > > > 4) Does it make sense to enter reimbursed expenses under assets?
> > > > >
> > > > > I deleted always qif-accounts-map to start all over again.
> > > > > I am using GNC 2.2.1, built from r16462 on 2007-08-20 on Windows
> > > > > XP.
> > > > >
> > > > > Thanx for your help
> > > > > Georg
> > > > >
> > > > >
> > > > > __________________________________________________________
> > > > > Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
> > > > >
> > > > >
> > > > > __________________________________________________________
> > > > > Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com
> > > > >
> > > > > _______________________________________________
> > > > > gnucash-user mailing list
> > > > > gnucash-user at gnucash.org
> > > > > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > > > > -----
> > > > > Please remember to CC this list on all your replies.
> > > > > You can do this by using Reply-To-List or Reply-All.
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Ian Lewis
> > > > ianlewis at member.fsf.org
> > > > http://www.ianlewis.org/
> > > >
> > > >
> > > > ___________________________________________________________
> > > > ------------------------------
> > > > Yahoo! Answers - Get better answers from someone who knows. Try it
> > > > now<http://uk.answers.yahoo.com/;_ylc=X3oDMTEydmViNG02BF9TAzIxMTQ3MTcxOTAEc2VjA21haWwEc2xrA3RhZ2xpbmU>
> > > > .
> > > >
> > >
> > >
> > >
> > > --
> > > Ian Lewis
> > > ianlewis at member.fsf.org
> > > http://www.ianlewis.org/
> > >
> > >
> > > ------------------------------
> > > Sent from Yahoo!<http://us.rd.yahoo.com/mailuk/taglines/isp/control/*http://us.rd.yahoo.com/evt=51949/*http://uk.docs.yahoo.com/mail/winter07.html>- a smarter inbox.
> > >
> >
> >
> >
> > --
> > Ian Lewis
> > ianlewis at member.fsf.org
> > http://www.ianlewis.org/
> >
> >
> > ___________________________________________________________
> > ------------------------------
> > Yahoo! Answers - Get better answers from someone who knows. Try it now<http://uk.answers.yahoo.com/;_ylc=X3oDMTEydmViNG02BF9TAzIxMTQ3MTcxOTAEc2VjA21haWwEc2xrA3RhZ2xpbmU>
> > .
> >
>
>
>
> --
> Ian Lewis
> ianlewis at member.fsf.org
> http://www.ianlewis.org/
>
>
> ___________________________________________________________
> ------------------------------
> Yahoo! Answers - Get better answers from someone who knows. Try it now<http://uk.answers.yahoo.com/;_ylc=X3oDMTEydmViNG02BF9TAzIxMTQ3MTcxOTAEc2VjA21haWwEc2xrA3RhZ2xpbmU>
> .
>
--
Ian Lewis
ianlewis at member.fsf.org
http://www.ianlewis.org/
More information about the gnucash-user
mailing list