Importing QIF files creates incorrect category types. How to fix?

wisemanja's Avatar

wisemanja

06 Jul, 2021 03:58 PM

After spending several days importing QIF files from YNAB4 (and discovering several idiosyncrasies), I now discover that many of the categories that Moneydance automatically set up were created with the wrong category type (i.e. Income vs. Expense). In attempting to edit those categories to change the category type, I discovered that it is not possible. How do I correct these categories that Moneydance set up wrong.

I am attaching a screenshot of some of my Categories. Everything in the red boxes are supposed to be Expenses and are listed as expense in YNAB4 (in fact it is not even possible to create unique income categories in YNAB4 which has always been a nuisance).

Also note that there is a mix of category types under a given master category, which according to the online documentation for Moneydance isn't even supposed to be possible! For example, the "Paycheck Expenses" master category is typed as an Income category. And yet it contains both Expense and Income categories under it.

Again, how to correct this? As it is it took me days to get to where I am now.

  1. 1 Posted by Stuart Beesley ... on 06 Jul, 2021 04:15 PM

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

    Hmm... You should not have mixed types under a master; have you tried a MD restart?

    The std procedure to fix is to create the new cats you need and put a marker on the front - e.g. 'new-salary', 'new-bills' etc and then use the find replace extension to select the txns in the old cat and move to the new cat.. Then when the old cat is empty, delete it..

    Do a test first to check it all works OK for you.....

    (not support, just a fellow user)

  2. 2 Posted by Stuart Beesley ... on 06 Jul, 2021 04:23 PM

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

    There is another unofficial and probably unsupported way. Install Toolbox extension. Advanced Mode, Menu: Accounts and Categories. FIX: Force Change an Account's type... In here you can select a category and change it from Exp to Inc etc... Use carefully. The tools>categories won't display properly until you restart MD and you will also have to then edit the categories to select the right / appropriate inc/exp parent category... Use carefully and at your own risk. Backup first and test first..

    (not support, just a fellow user)

  3. 3 Posted by wisemanja on 06 Jul, 2021 04:40 PM

    wisemanja's Avatar

    Thanks Stuart.

    *I* did not mix the types though. None of those categories existed prior to my QIF imports. They were all auto created by Moneydance. Moneydance created the wrong category types.

    I'm brand new to both Moneydance as well as this forum (my first post here). The suggestion that you first made was the only thing I could think of. Have you done this before? And with categories that are part of split transactions, will this work while leaving all of the other parts of the split in place?

    As far as your second suggestion, fortunately none of my parent categories that have sub categories have any transactions recorded in them (I've only distributed to the sub categories). My question is, if I force change the types as you have indicated, will I have to go and flip the polarity of the numbers on the transactions as well? As you can see on the example I gave, all of the categories incorrectly marked as Income have negative values (essentially making them expenses). Will changing the category type correct that as well?

    Is the category type only used for report type purposes? Or does it also affect how numbers are distributed to the categories?
    (Sorry, too many questions in the same posting :-)

  4. 4 Posted by Stuart Beesley ... on 06 Jul, 2021 04:49 PM

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

    Find and replace works very well. All txns are actually splits and yes, it copes with these.

    No, force change does not flip the sign... Sorry....

    I don't actually know what MD uses the Account Type for... Sorry. I don't think it affects actual postings, but you really want it to be right..

    I would start again and get the QIF import working....

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

    dwg's Avatar

    I'm a fellow user.

    While Moneydance does not allow you to mix income and expenses like that, some software program do and Moneydance will honour and set up the structure that way if it comes over via a QIF import. It knows no better. It can also happen if you have the same category as both an Income and expense, especially a top level category. There is no indication in a QIF file as to whether a category is an income or expense items, so the software has to make an educated guess based on any context it can find.

    This is one reason why I liked the old importers ability to just read QIF files and create the structure without loading any transactions. This gave you an empty structure that you could correct before going back and loading transactions.

  6. 6 Posted by Stuart Beesley ... on 06 Jul, 2021 09:13 PM

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

    @dwg. FYI - the old importer is available in toolbox. General tools menu with the create structure option.

  7. 7 Posted by wisemanja on 06 Jul, 2021 09:45 PM

    wisemanja's Avatar

    @dwg That's interesting. So it seems that you might be able to get around this by using the old importer to set up the accounts and categories, then using Stuart's second suggestion of using the Toolbox extension to force change the incorrect category types prior to importing the data portion of the QIF files. That would be more straight forward if you knew the sequence of things.

    As it turns out, I'm days past that point in that I've had to manually re-enter hundreds of split values that YNAB4 set to $0 during the creation of its QIF files (an obvious bug), and I've also already had to recombine all of my transfers due to YNAB4 being structured inappropriately in how they handle transfers of funds between accounts.

    So I've been experimenting with the Find and Replace extension. So far it seems to be working ok.

  8. 8 Posted by dtd on 06 Jul, 2021 09:48 PM

    dtd's Avatar

    just a user - I was going to suggest find and replace, but you've already found it.

  9. 9 Posted by dwg on 06 Jul, 2021 10:05 PM

    dwg's Avatar

    The old importer was in Moneydance 2007 and earlier, and the setup structure option was often used, especially when a source shown to be questionable in it structure.

    Stuart's toolbox is a relatively recent addition to Moneydance but I do recall having discussions around this feature of the old importer and its value. I have not looked at the toolbox ability to change category type, the fix in the 2017 days was to create a category of the right type with the existing name and delete the wrongly created category.

    What you have described sounds about right but I do not know if any of us have tested it to make sure it works as expected.

    Find and replace and batch change are two very useful tools in Moneydance, they do have to be used with respect and plenty of backups however since a wrong move can really screw the data.

  10. 10 Posted by dwg on 07 Jul, 2021 05:14 AM

    dwg's Avatar

    @stuart.

    I have started looking at your implementation of the classic QIF loader functionality and the ability to force change account/category type.

    So far it is looking good. I have QIF files they will result in wrong account types, so have tried changing an account type from Bank to Investment (it only has transfers at this stage of the import).

    I do have one QIF that results in some intermingled Income/expense categories so will look at that soon.

    I note there is no shortage of warning messages :)

  11. 11 Posted by wisemanja on 07 Jul, 2021 03:32 PM

    wisemanja's Avatar

    For what it's worth, it does appear to me that the category type only controls how the internal number for a give transaction's dispersement is displayed. All my negative valued categories that were incorrectly displayed as "Income". They display now as positive values that are "Expense". I'm not convinced that the internally stored values for these transaction splits actually changed. By being identified as expenses instead of income, the same internal value just displayed different and shows up in reports in the appropriate areas.

    If I am correct, being able to force change a category type may not be such a big deal. You just need to make sure that parent and sub categories all match or your reports will be meaningless.

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Already uploaded files

  • a.jpeg 508 KB

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

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