Importing Investment Portfolio qif

[Charles Day]

On Mon, Mar 17, 2008 at 10:17 PM, The Wassermans <dwass at optusnet.com.au>
wrote:

>  Er, Charles,  The qif Importer is complaining again
>
> I found a couple more "N"'s not on the list;-
> CGLongX (= Long term capital Gain Distribution . . .)
> DivUF     (= Unfranked Dividend)
>
> Please.
>


CGLongX is correct as is.

I have never seen Quicken-generated QIF file including unfranked dividends,
so I don't know what the correct 'N' line would be. And even if I knew, the
importer doesn't know anything about franked vs. unfranked dividends anyway.
Certainly "DivUF" is not going to work, even if it is correct, because the
GnuCash importer doesn't support it. Worst case, you can import a dividend
by using the more generic "Div". But the "unfranked" distinction would be
lost, and after import you would have to separate franked dividends from
unfranked dividends manually. Or, if you want try being tricky, change every
"DivUF" to some obscure 'N'-line value that you probably won't be using,
like "cgmid" (mid-term capital gains). This will import all the unfranked
dividends into a "Cap. gain (mid)" account, which you can rename "Unfranked"
(or whatever pleases) after import.

If you know anyone that uses Quicken and can generate a QIF with franked and
unfranked dividends, I'd be happy to take a look and try to enhance the
GnuCash QIF importer to support them. I have only used the US version of
Quicken, which knows nothing about franked vs. unfranked.

-Charles

Charles Day wrote:
>
> On Mon, Mar 17, 2008 at 4:21 PM, The Wassermans <dwass at optusnet.com.au>
> wrote:
>
> >
> >
> > Charles Day wrote:
> >
> > On Mon, Mar 17, 2008 at 1:10 PM, The Wassermans <dwass at optusnet.com.au>
> > wrote:
> >
> > > I have run a trial import of a qif file I created from Quicken data.
> > >  I
> > > get error messages re 'Action' (i.e. qif 'N') in my creation.  And
> > > then
> > > the whole thing fails.
> > >
> > > A few questions please:
> > > 1. Can you please point me to where I can find a listing of the
> > > correct/optimum qif data GnuCash can use.
> >
> >
> > I'm afraid that data only seems to exist in the Scheme code itself and
> > in the heads of a few developers and users. And even that is incomplete. I
> > have seen a few bits of documentation on the QIF file format, including the GnuCash
> > writeup<http://svn.gnucash.org/trac/browser/gnucash/trunk/src/import-export/qif-import/file-format.txt>(last updated seven years ago!!), but none adequately addresses investments.
> >
> >  2. My qif (below) is obviously wrong.  Does it need major surgery?
> >
> >
> > I think the answer is "no". I think that with a little explanation, all
> > you may need to change is the 'N' lines on the transactions below. What
> > software produced this "BoughtX" transaction, by the way? Am I recalling
> > correctly that you are producing these transactions on your own, from
> > QuickBooks reports?
> >
> >  "BoughtX" comes from Quicken(2008).  Yes, that's me.  I am running
> > Quicken reports into Excel.  From there I use a utility called "xl2qif"
> > (available on line).It works well.  From there I manually edit the qif.  I
> > am happy to talk any Quicken exporters through the process I use.
> >
> >
> > For example, GnuCash expects "BuyX" rather than "BoughtX". So just
> > search/replace them. It's weird that there is no grand total for these
> > transactions (a "T" line) but they should still import OK.
> >
> >  "BuyX" makes sense.  I'll investigate the apparent "T" line anomaly.
> > Might just be an oversight on my part.
> >
> >
> > What do the transactions with "Added" mean? I suspect that GnuCash
> > expects "ShrsIn". (I notice that in Quicken 2006, the GUI says "Added" but
> > Quicken exports it as "ShrsIn".) Likewise, "Removed" would be "ShrsOut".
> >
> >  "Added" and "Removed" are a result of shares I transferred out of
> > another portfolio.  The counter entries will be shown there.  But, again,
> > "shrsIn" and "ShrsOut" probably will work.
> >
> >
> > If you make a list of each rejected 'N' line, I'll try to tell you the
> > correct search/replace to use.
> >
> >  Good! Here's the Quicken list: See attached .doc file.
> >
>
> OK, here's what I can make out from your attachment. This is a partial
> list, as are several others not covered (for example, "WriteChk" converts to
> "Cash", and there are also foreign language variants).
>
> Cheers,
> Charles
>
>
> Quicken GUI  QIF 'N' Line  Notes
> ===========  ============  =====
> Added        ShrsIn
> Bought       Buy           Implicitly uses the brokerage account
> BoughtX      BuyX          For use with an 'L' line
> Cover Short  CvrShrt
> Dividend     Div           Implicitly uses the brokerage account
> Dividend     DivX          For use with an 'L' line
> Entry        ???           Not currently supported by GnuCash QIF importer
> Exer. Optns. Exercise      Not currently supported by GnuCash QIF importer
> Expire       Expire        Not currently supported by GnuCash QIF importer
> Grant Optns  Grant         Not currently supported by GnuCash QIF importer
> IntInc       IntInc        Implicitly uses the brokerage account
> IntIncX      IntIncX       For use with an 'L' line
> MargInt      MargInt       Implicitly uses the brokerage account
> MargIntX     MargIntX      For use with an 'L' line
> MiscExp      MiscExp       Implicitly uses the brokerage account
> MiscExpX     MiscExpX      For use with an 'L' line
> MiscInc      MiscInc       Implicitly uses the brokerage account
> MiscIncX     MiscIncX      For use with an 'L' line
> ReinvDiv     ReinvDiv
> ReinvInt     ReinvInt
> Reminder     Reminder
> Removed      ShrsOut
> Reprice Opt  ???           Not currently supported by GnuCash QIF importer
> RtrnCap      RtrnCap       Implicitly uses the brokerage account
> RtrnCapX     RtrnCapX      For use with an 'L' line
> Sell         Sell          Implicitly uses the brokerage account
> SellX        SellX         For use with an 'L' line
> Short Sale   ShtSell
> ShrsIn       ShrsIn
> ShrsOut      ShrsOut
> StkSplit     StkSplit
> XIn          XIn
> XOut         XOut
> Vest         Vest          Not currently supported by GnuCash QIF importer
>
>
>
> >
> >  3. Will it be okay to import Investment data along will other data in
> > > the import function or should I first run my accounts and then
> > > investments separately?
> > >
> >
> > I would run it all at once to avoid inadvertently creating duplicate
> > transfers between accounts.
> >
> > At least you have managed to convert a few Aussie bucks into shares. I
> > seem to spend all mine on Tim Tams and Bindi wine. :)
> >
> > Cheers,
> > Charles
> >
> >
> >  Thank you so much for your help Charles.  This has been a long haul for
> > me but I am determined to make it work.  And to escape the clutches of
> > Quicken and indeed Windows.  Soon as I am happy with GnuCash I am going to
> > Ubuntu.  Enjoy your Tim Tams.  Not sure I have ever come across Bindi wine.
> > But if it's red I drink it happily!
> >
> >
>