[Kmymoney] Re: QIF IMPORT PROBLEMS

[aga]

Hi Timothy

On Sat, 21 May 2011 15:56:47 +0200
timothy <timboyle at afrihost.co.za> wrote:
> > 
> > Good Grief! I thought I had tried every possible combination.
> > It begs the question of what happens if you have a second chq under
> > a different parent, but I do not want to go down that road.

Yes, that thought had occurred to me, also.  With the limited testing
I've done so far, the import has finished up where wanted, but what
decision was made, is open.

> > Many thanks. After literally weeks of research and testing we
> > finally have a solution. Well you have the solution. I feel like
> > celebrating!

I'm not yet sure that that is what should happen, but at the very
least, it gives you an easy work-around.

> > Instead I will have a look at your second QIF file and see if I/we
> > can sort out the Lcategory:sub-category problem. Its great having
> > your csv input as an independent check.
<snip>
> On doing some further testing I see that, as a side effect, another
> checking account 'chq' is created at top level under Asset. Nothing is
> transferred to it. Just an empty account. Did you notice that also?

Yes, and no!  Yes, when importing via QIF, and opening the new account
for editing shows a note 'Autogenerated by QIF importer'.  This is a
by-product of changes made by Thomas many moons ago, and for which I 
raised a bug.

No, that doesn't happen on CSV importing, I'm glad to say.

> I left the newly created chq account, deleted the transactions, and
> did the import again. Surprisingly it transferred to the correct
> Bank-Accounts:chq and not the top level chq. Weird! Could you please
> confirm or otherwise.
> 
Yes, that happens here also, to which I was referring at the top.

Allan

> 
> On Sat, 2011-05-21 at 15:23 +0200, timothy wrote:
> > On Sat, 2011-05-21 at 13:35 +0100, aga wrote:
> > > On Sat, 21 May 2011 13:16:20 +0200
> > > timothy <timboyle at afrihost.co.za> wrote:
> > > 
> > > > Hi Alan
> > > > 
> > > > Your QIF is an import into securityA for buying and selling of
> > > > shares with transfers of monies to and from the chq account. We
> > > > are at cross purposes in the hierarchy naming. I also created a
> > > > chq under Asset [chq] which I call top level. And I also
> > > > created a chq under Bank-Accounts under Asset
> > > > [Bank-Accounts:chq]. The Asset does not seem to be necessary in
> > > > the naming. The securityA is an investment so I created an
> > > > investment account under Asset with securityA as an investment.
> > > > When I imported your file into the investment account. kmm put
> > > > the Buys and Sells into securityA and got/sent monies to and
> > > > from the chq OK. But not with the second hierarchy of
> > > > [Bank-Accounts:chq]. This I could only do manually.
> > > > 
> > > > Unless I am misunderstanding something, this is how I read the
> > > > QIF file.
> > > > 
> > > > Regards
> > > > 
> > > 
> > > Hi Timothy
> > > 
> > > Erm, yes.  Importing a qif which requires a transfer to an
> > > account such as Bank-Accounts:chq does not do what you/I
> > > expected.  I tried something similar with a csv file and the same
> > > occurred.
> > > 
> > > Then, I tried something a bit different with the csv and was quite
> > > surprised by the result.  So then I did the same with the QIF
> > > file and got the same result.
> > > 
> > > If you already have your account set up as per Bank-Accounts:chq,
> > > in the qif file, alter the L[....] to just [chq], and, hopefully,
> > > you'll find that the value does in fact end up in
> > > Bank-Accounts:chq.
> > > 
> > > With the csv importer, on importing such items, it asks for the
> > > checking/brokerage account to use.  Entering Bank-Accounts:chq
> > > does not work correctly, but entering just chq works like a
> > > charm.  It's not something I've tried previously, so I was a bit
> > > taken aback.
> > > 
> > > Allan
> > 
> > 
> > Good Grief! I thought I had tried every possible combination.
> > It begs the question of what happens if you have a second chq under
> > a different parent, but I do not want to go down that road.
> > 
> > Many thanks. After literally weeks of research and testing we
> > finally have a solution. Well you have the solution. I feel like
> > celebrating!
> > 
> > Instead I will have a look at your second QIF file and see if I/we
> > can sort out the Lcategory:sub-category problem. Its great having
> > your csv input as an independent check.
> > 
> > Regards
> > 
> > > 
> > > 
> > > > 
> > > > On Sat, 2011-05-21 at 11:30 +0100, aga wrote:
> > > > > On Sat, 21 May 2011 11:48:45 +0200
> > > > > timothy <timboyle at afrihost.co.za> wrote:
> > > > > 
> > > > > > Hi Alan
> > > > > > 
> > > > > > Here is the conclusion on testing your QIF file invAa.QIF.
> > > > > > This file has BuyX and DivX transactions on a security with
> > > > > > the monies/proceeds transferred in (Buy) and transferred to
> > > > > > (Div) a cheque account at top level ie not a sub-account
> > > > > > [chq].
> > > > > > 
> > > > > > 1) The QIF file you sent works perfectly in both import and
> > > > > > transfers.
> > > > > > 
> > > > > > 2) I then edited the same QIF file to transfer to a cheque
> > > > > > account with a parent account ie [Bank Accounts:chq], the
> > > > > > transactions are imported OK, but no transfer takes place
> > > > > > and the transaction in the ledger shows a yellow
> > > > > > triangle/exclamation with the account field blank. 
> > > > > > 3) To check whether this was caused by my account
> > > > > > hierarchy, I then manually edited both 1) and 2) in the
> > > > > > ledger to a "Bank Accounts:chq" in the account field and
> > > > > > both transfers worked perfectly and the yellow triangle
> > > > > > disappeared.
> > > > > > 
> > > > > > So it looks like kmm cannot handle [parent:sub-account] in
> > > > > > the QIF import, but can do so as a manual transaction.
> > > > > > 
> > > > > > I would be interested to know whether your csv plugin works
> > > > > > with a [parent:sub a/c] transfer?
> > > > > > 
> > > > > > Regards
> > > > > > Timothy
> > > > > >    
> > > > > 
> > > > > Hi Timothy
> > > > > So far as the last para. is concerned, I've not built any
> > > > > special capability for this in the plugin, but I don't think
> > > > > it's necessary.
> > > > > 
> > > > > I set up an asset account with a checking sub-account.  I
> > > > > then did a csv import  and when presented by KMM with the
> > > > > account selector wizard, I chose the new sub-account and the
> > > > > import completed successfully.
> > > > > 
> > > > > There appears to be a slight anomaly/bug though.  After the
> > > > > import, which shows in Ledger view, the amount does not show
> > > > > in Accounts view, either against the asset account, which I
> > > > > think is correct, or against the checking account, which I
> > > > > think is an error.  This is very similar to a bug I reported
> > > > > very recently.  (Having checked, I posted about it
> > > > > - Institution View Behaviour - on Monday, but I'd better
> > > > > report it as a bug now.
> > > > > 
> > > > > So, now going back to your QIF import issue, into which
> > > > > account did you choose to import?  When you say 'no transfer
> > > > > took place', that suggests you didn't import into the chq
> > > > > account, as no transfer would then be necessary.  Perhaps I'm
> > > > > not understanding what you are doing/trying?
> > > > > 
> > > > > Allan
> > > > > > 
> > > > > > On Fri, 2011-05-20 at 16:39 +0200, timothy wrote:
> > > > > > > Hi Alan
> > > > > > > 
> > > > > > > Sorry the delay, but I was out and about. Its great to
> > > > > > > have something new to work on as I had run out of ideas.
> > > > > > > 
> > > > > > > My account hierarchy is as follows; All the top-level
> > > > > > > dummy accounts are 'asset'. The banking sub-accounts are
> > > > > > > then all "cheque" or "saving". The investment
> > > > > > > sub-accounts are all "investment" with "stock" or "mutual
> > > > > > > fund" actual investments. So there is actually not a mix
> > > > > > > of cheque and investments in one account.
> > > > > > > 
> > > > > > > I have am busy testing out your QIFs and getting
> > > > > > > interesting results. I will report back as soon as I can
> > > > > > > see a pattern. One thing I have noticed is that I am
> > > > > > > using only 'T' and in both your files you have both U and
> > > > > > > T with the same amounts. The U is supposedly for higher
> > > > > > > transaction amounts than the T. I do not think this is my
> > > > > > > problem but I am making sure it is not.
> > > > > > > 
> > > > > > > It is taking longer than I would expect because none of
> > > > > > > your accounts or categories exist in my kmm file and I am
> > > > > > > trying to standardise on the creation and/or allow kmm to
> > > > > > > do it automatically and record the results.
> > > > > > > 
> > > > > > > As far as the plugin goes I would welcome giving it a go.
> > > > > > > My expertise is average, but enthusiasm is boundless. The
> > > > > > > CSV I will send to you. I am pretty flexible on my side
> > > > > > > since I am using sed and awk on all the downloaded files
> > > > > > > to cut the rubbish and semi-standardise the format into a
> > > > > > > csv which is then input into a C program for generating
> > > > > > > the QIFs. What I mean is if there is something you would
> > > > > > > expect or anything you do not like I could fix it from my
> > > > > > > side.
> > > > > > > 
> > > > > > > Thanks for your interest and response.
> > > > > > > 
> > > > > > > Regards
> > > > > > > Timothy
> > > > > > > 
> > > > > > > 
> > > > > > > 
> > > > > > > On Fri, 2011-05-20 at 12:15 +0100, aga wrote:
> > > > > > > > 
> > > > > > > > Begin forwarded message:
> > > > > > > > 
> > > > > > > > Date: Fri, 20 May 2011 11:50:50 +0100
> > > > > > > > From: aga <agander93 at gmail.com>
> > > > > > > > To: kmymoney at kde.org
> > > > > > > > Subject: [Kmymoney] Re: QIF IMPORT PROBLEMS
> > > > > > > > 
> > > > > > > > 
> > > > > > > > On Fri, 20 May 2011 09:18:49 +0200
> > > > > > > > timothy <timboyle at afrihost.co.za> wrote:
> > > > > > > > 
> > > > > > > > > Hi
> > > > > > > > > 
> > > > > > > > > I wondered if you could confirm that the problems I
> > > > > > > > > have been experiencing are due to the way KMM handles
> > > > > > > > > QIF or that there is something wrong with my files.
> > > > > > > > > 
> > > > > > > > > I have tried importing old QIFs from when I migrated
> > > > > > > > > from MsMoney and I get similar results. At that time
> > > > > > > > > so many things were new and unknown that I can not
> > > > > > > > > remember these being specific problems. I do remember
> > > > > > > > > that I had to do considerable manual reconfiguring of
> > > > > > > > > accounts and categories.
> > > > > > > > > 
> > > > > > > > > Be that as it may, I really want to know whether I
> > > > > > > > > should give up on the QIF import and start looking
> > > > > > > > > into OFX import of investment transactions.
> > > > > > > > > 
> > > > > > > > > If anyone has examples of investment files (both QIF
> > > > > > > > > and OFX) that have successfully imported into KMM. I
> > > > > > > > > would very much like to use them in my further
> > > > > > > > > endeavours. My investments sites merely give web page
> > > > > > > > > output of transactions which I can download in
> > > > > > > > > various formats from PDF, XLS, CSV, HTML and even
> > > > > > > > > text. These I have been converting to QIF for import.
> > > > > > > > > My bank and credit card go directly with OFX into KMM.
> > > > > > > > > 
> > > > > > > > > Ar first glance OFX investing appears not to cater for
> > > > > > > > > categories and sub-categories and does not appear to
> > > > > > > > > be able to transfer the proceeds of Sells or
> > > > > > > > > Dividends to another (cheque) account?
> > > > > > > > > 
> > > > > > > > > Sample files that actually worked would be of
> > > > > > > > > tremendous help.
> > > > > > > > > 
> > > > > > > > > PREVIOUS MESSAGE TO KMM MAILING LIST
> > > > > > > > > ------------------------------------
> > > > > > > > > 
> > > > > > > > > Hi
> > > > > > > > > 
> > > > > > > > > I am running KMM 1.0.5 on KDE 3.5.10 on Ubuntu
> > > > > > > > > Maverick.
> > > > > > > > > 
> > > > > > > > > I have been testing out QIF investment files imported
> > > > > > > > > into KMM and have run into a few problems.
> > > > > > > > > 
> > > > > > > > > The QIF spec gives the L option as
> > > > > > > > > 1)category:subcategory or 2)[transfer to account]
> > > > > > > > > depending on the N option having an X or not i.e.
> > > > > > > > > NSellX or NDivX.
> > > > > > > > > 
> > > > > > > > > However in my testing:
> > > > > > > > > 1) KMM fails to recognise the colon: and creates a new
> > > > > > > > > category called "category:subcategory" as if the whole
> > > > > > > > > string was a new category name. 
> > > > > > > > > 
> > > > > > > > > 2) the transfer account given as
> > > > > > > > > [parent-account:sub-account] is not recognised and
> > > > > > > > > although imported OK shows a yellow
> > > > > > > > > triangle /exclamation with the account field being
> > > > > > > > > blank in the ledger. If I reduce the string to just
> > > > > > > > > [sub-account], a new investment account is created
> > > > > > > > > with that name at top level even though the same-name
> > > > > > > > > account exists as a sub-account.
> > > > > > > > > 
> > > > > > > > > Perhaps I should explain that I have organised my
> > > > > > > > > account structure with generic top level asset
> > > > > > > > > "dummy"accounts such as Banks Accounts; Money
> > > > > > > > > Markets; Retirement etc. Each one of these may
> > > > > > > > > contain several relevant investment or checking
> > > > > > > > > accounts with their corresponding investments.
> > > > > > > > > 
> > > > > > > > > Any light on this matter would be appreciated.
> > > > > > > > > 
> > > > > > > > > Timothy 
> > > > > > > > 
> > > > > > > > Hi Timothy
> > > > > > > > 
> > > > > > > > As Cristian mentions in his reply, I've produced a
> > > > > > > > plugin for KMM, to handle import directly into KMM, of
> > > > > > > > CSV files, both investment and bank statements.  As
> > > > > > > > it's fairly recent, it is not yet supplied as a part of
> > > > > > > > KMM, but hopefully will be part of the next release.
> > > > > > > > However, for me, and a couple of others who've tried
> > > > > > > > it, it works pretty well.  I'm not sure yet of when
> > > > > > > > that release will be avaiable.  It is presently
> > > > > > > > included in the SVN, development, version.
> > > > > > > > 
> > > > > > > > So far as bank statements are concerned, I gather you
> > > > > > > > are able to use OFX, which is good.
> > > > > > > > 
> > > > > > > > For investments, using the csv plugin, and in fact
> > > > > > > > using QIF, it is most certainly possible to have
> > > > > > > > dividends/sells transfer automatically into a cheque
> > > > > > > > type account, and I previously used QIF for this and
> > > > > > > > now use CSV successfully.
> > > > > > > > 
> > > > > > > > In your first posting, you mention problems associated
> > > > > > > > with subaccounts and categories, which surprises me,
> > > > > > > > and I wonder if your difficulty might be associated
> > > > > > > > with the way in which you say you've set up your
> > > > > > > > accounts.  I'm not clear if you have a mix of
> > > > > > > > investment and checking sub-accounts below your dummy
> > > > > > > > top-level? If so, what type is that top-level account?
> > > > > > > > I must confess I've no idea whether that is catered for
> > > > > > > > in KMM.  You may though be able to mix-n-match below an
> > > > > > > > Institution, which may give you what you require.
> > > > > > > > 
> > > > > > > > I've just dug out and modified an old QIF file and
> > > > > > > > confirmed that it imports correctly transactions
> > > > > > > > containing dividends and transferred the proceeds
> > > > > > > > correctly to a cheque account. Attached invAa.QIF.
> > > > > > > > 
> > > > > > > > Similarly, an old large Quicken file which I've
> > > > > > > > stripped, and which confirms that a payment can tranfer
> > > > > > > > to a sub-account- see last transaction of
> > > > > > > > everythingtrans.QIF
> > > > > > > > 
> > > > > > > > If so far as your web investment imports are concerned,
> > > > > > > > then the csv plugin may help.  This may be a bit of
> > > > > > > > bother to set up initially, as its only in the
> > > > > > > > development release, but much depends on your expertise
> > > > > > > > and enthusiasm!  Let me know.
> > > > > > > > 
> > > > > > > > Allan
> > > > > > > > _________________________________________________________________________
> > > > > > > > 
> > > > > > > > Hi Timothy
> > > > > > > > 
> > > > > > > > It might be an idea, if you can, to let me have a sample
> > > > > > > > investment csv file, to see if there might be any
> > > > > > > > problems with its format, that I might need to look
> > > > > > > > into.  CSV files are very variable, and I only have
> > > > > > > > seen a few types.  If so, send it direct to me.  I'll
> > > > > > > > treat it as private anyway.
> > > > > > > > 
> > > > > > > > Allan
> > > > > > > > 
> > > > > > > > Be sure to edit anything sensitive/incriminating!
> > > > > > > > _______________________________________________ KMyMoney
> > > > > > > > mailing list KMyMoney at kde.org
> > > > > > > > https://mail.kde.org/mailman/listinfo/kmymoney
> > > > > > > 
> > > > > > 
> > > > > 
> > > > 
> > > 
> > 
>