Import QIF Transactions from Investment Brokers CSV file

smith.seab's Avatar

smith.seab

28 Jun, 2021 10:22 PM

I am in the process of creating a QIf file in Excel VBA from a CSV file to add transactions to existing Investment Moneydance account.
Does someone know what the syntax if for the QIF file. For example, do I use Moneydance Action Codes or Quicken Action Codes.
If some has developed such a QIF file, can I get the QIF code they used?

  1. 1 Posted by dwg on 28 Jun, 2021 10:53 PM

    dwg's Avatar

    I'm a fellow user.

    Most CSV files that I have seen investment houses provide are unsuitable for conversion to QIF, the only ones I have found that are close to being good enough are from other financial programs.

    That being said Quicken actions should work, provided Moneydance has the same functionality. The QIF import is often used to import QIF files produced by Quicken.

    The syntax of QIF files is very straight forward. Intuit have produced some specification documents that can be found on the web, plus there is a wiki article that spells it out pretty well.

    Many years ago someone produced an Excel macro that produces QIF files from properly structured CSV files.

    http://xl2qif.chez-alice.fr/xl2qif_en.php

  2. 2 Posted by tgilbert666 on 01 Jul, 2021 02:37 AM

    tgilbert666's Avatar

    You might find this page useful too...

    https://en.wikipedia.org/wiki/Quicken_Interchange_Format

    (I've been happily using the xl2qif extension mentioned by dwg for years, but not for investment transactions, sorry, no direct experience there) Cheers,
    Tony
    (a fellow user)

  3. 3 Posted by dwg on 01 Jul, 2021 03:24 AM

    dwg's Avatar

    I've used xl2qif for investment transactions with CSV data from Reckon, I did have to use VBA first to "fix" the data.

  4. 4 Posted by Stuart Beesley ... on 01 Jul, 2021 12:15 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar

    Just create a new blank account. Delete all categories, accounts, secuties , currencies. Create a dummy investment account and security(s). File export backup this to save a copy. Now, Create dummy txns. Do file export QIF to create example qif format you can copy.

    Test the import on the blank account.

  5. 5 Posted by Stuart Beesley ... on 01 Jul, 2021 12:30 PM

    Stuart Beesley - JUST A FELLOW USER and Toolbox ‘guy’'s Avatar
  6. 6 Posted by smith.seab on 02 Jul, 2021 04:16 PM

    smith.seab's Avatar

    Thanks to everyone who responded to my discussion.
    I got most of the suggestions to work.
    The only things that I am having a problem with, is that I can't supply the category for the Div and ReinvDiv.

  7. 7 Posted by dwg on 02 Jul, 2021 09:06 PM

    dwg's Avatar

    In a Dividend type transaction an L line in the transaction should handle a category -

    Lcategoryname

    If it is a subcategory the entry could look something like:

    LInvestments:Dividends

  8. 8 Posted by smith.seab on 02 Jul, 2021 09:31 PM

    smith.seab's Avatar

    Thanks,

    But, the L line didn't work.

    The category "Dividend Income" was created and the dollar amount was put
    into that category.

    Seab

    From: dwg [mailto:[email blocked]]
    Sent: Friday, July 2, 2021 4:07 PM
    To: [email blocked]
    Subject: Re: Import QIF Transactions from Investment Brokers CSV file
    [Investments #6006]

  9. 9 Posted by dwg on 02 Jul, 2021 10:07 PM

    dwg's Avatar

    The developer told me he fixed that years ago, not impressed!.

    I found in Moneydance 2012 that a Dividend type transaction would go to a category called Dividend or perhaps it was Dividend Income no matter what.

    In my case it was totally inappropriate or to put it bluntly wrong in Australia dividends must be categorised into Franked or Unfranked - the Australian Tax Office doesn't care what software does or does not do, you do it their way.

    The only way I found around it back then was to treat all dividends as MisInc which did allow the L line and for Dividend reinvestment I had to break that into a MiscInc and a Buy, i.e. two transactions.

  10. 10 Posted by smith.seab on 03 Jul, 2021 12:09 AM

    smith.seab's Avatar

    Thank you a lot.

    I will play around with your suggestions.

    Actually, the CSV from Schwab almost does just as you suggest.

    The 2 transactions are ReinvDiv and ReivnShrs.

    Thanks Again

    Seab

    From: dwg [mailto:[email blocked]]
    Sent: Friday, July 2, 2021 5:08 PM
    To: [email blocked]
    Subject: Re: Import QIF Transactions from Investment Brokers CSV file
    [Investments #6006]

  11. 11 Posted by dwg on 03 Jul, 2021 12:51 AM

    dwg's Avatar

    If you already have two transactions it makes it a lot easier I would think, one becomes your MiscInc and the other the Buy, in my case I only had one so I had to create the two rather than modifying what was there.

  12. 12 Posted by dwg on 03 Jul, 2021 12:54 AM

    dwg's Avatar

    While on the subject a ReinvDiv is really two transactions rolled into one in any case, it is a convenience in the software.

    With the way Reinvested dividends often work here. I have since ended up doing them as two transactions in any case.

  13. System closed this discussion on 02 Oct, 2021 01:00 AM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac