[Kmymoney] Re: QIF IMPORT PROBLEMS

[timothy]

Hi Alan

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?

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.

Timothy


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
> > > > > > 
> > > > > 
> > > > 
> > > 
> > 
>