The Vicissitudes of Quicken to Moneydance
Ok. I'm ready to move. I did the QIF dance (thankfully I'm still on Quicken 2017) and am majorly impressed by what I end up with in Moneydance.
There are two big issues that I currently see. (I've only been looking things over for an hour or so)
CASH BALANCES are wonky. YES - I know the "Initial balance" bit. All those are fine (now). Most credit cards, cash, and accounts that never interacted with other accounts are beautiful. But with 20 years of data, and with tons of investment accounts that came and went, transfers between accounts (like creating an account, and populating it with securities) totally mess up the cash balance.
This is a pretty big deal as it affects tons of my older accounts that should have a zero CASH balance, but scream "fix me"!
The securities, their amounts, and balances are fine. But Quicken has an "ADD-Shares Added" attribute which specifically says "add shares to this account without affecting the cash balance". I doubt the QIF file tells Moneydance this, so no blame, but sure enough a lot of negative cash balance occurs as the transaction "buys" these shares. In fact it is doubly bad, as it ends up being a BUYXFR in Moneydance, so that ends up doubling the cash change.
Another bit is an account charging an item to its own category. (i.e. a writeoff - I have a CAR category and occasionally depreciate the car by putting in a transaction that just writes off a few thousand bucks on it) - whatever the QIF sends causes Moneydance to create a CARX category that I guess "absorbs" the writeoff. Makes it difficult to figure out, again with years of data.
Thirdly - sometimes it seems the positive/negative is backwards for the Quicken QIF import - so what is a charge becomes a deposit or vice versa. This seems to be a well known issue here, but still a pain to reverse everything by hand. I tried the find/replace extension, but since values are different, it looks like I will have to do each of the "bad" accounts by hand.
As a point of order - what determines for an investment account what goes into register, bank register, and securities detail? Although these seem accurate (other than, again cash balances), I'm trying to figure out Moneydance's categorization for those three tabs.
Overall, I'm impressed, but with 20 years of data, there's a lot of cleanup (mostly cash balances) to do. I'm tempted to just have a transition day that has tons of entries of cash changes to zero and call it quits and move forward since most of the securities and credit cards and such are perfect, but I'm afraid that would ultimately give me nightmares.
Thoughts appreciated.
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
1 Posted by dtd on 10 Nov, 2019 06:17 AM
This post is no longer relevant, removed. 9/8/22
2 Posted by dtd on 10 Nov, 2019 06:51 AM
to derekkent23
wow - Moneydance should be paying you! How much time do you spend here?
re this comment on one post:
Now the real work starts to check balances and tidy thing up. This is especially true of investment accounts as not all action types are supported. But worth it in the end.
Yep, I think that's one of my points. Would be nice if a programmed computer could handle this vs. doing it by hand.
You are obviously a stellar contributor here.
3 Posted by dtd on 10 Nov, 2019 07:37 AM
Continuing to analyze - maybe I should just remove the ACCOUNTX items from the sidebar and relegate them to hidden history?
And if that seems feasible, where is the "hidden account menu"? I would be happy to leave that history alone, but wonder how to access it if necessary. Ok, found it the + at the bottom left. Never mind. Still learning.
This concept of just hiding some of the odd imports where moneydance creates ACCOUNTX might address item 2 above.
The problem it seems is they still affect "net worth". Hmmmmmmmmmmmm.
How would I move these from BANK categories to EXPENSE CATEGORIES so they would be both hidden and not affect net worth?
Because when creating these ACCOUNTX categories - they are made as BANK categories.
Still Trying to figure out the biggie - cash balances.
4 Posted by dwg on 10 Nov, 2019 08:29 AM
I'm a fellow user.
I can provide some input on transferring data
The Moneydance link for importing data is:
https://infinitekind.tenderapp.com/kb/importing-data-from-other-pro...
and is working for me.
Some of the problems seen come from the Quicken side, some from the Moneydance side and some are due to the different way the software works. Moneydance is based on Accounting principles Quicken is either not or it is very well hidden.
As you have found out Moneydance does not have all of the action types that Quicken has, for some that does not matter as they can be mapped to other actions in Moneydance due to the different way Moneydance works, for others there is no direct mapping. In Moneydance there is no comparable action to Add & Remove Shares (or ShrIn/ShrOut). The closest to Add is a Buy or a BuyXfr. Buy takes the funds from the cash balance of the account a BuyXfr from another account or a category. Add & Remove type transactions are a problem in accounting terms as they are not valid, yet there are times when rounding of shares numbers needs to be done because of Stock Splits for example.
Ah yes the AccountX creation. Quicken allows you to create money or dispose of money magically through transferring monies to or from the account/category in which you are creating the transaction. Do this in accounting and you would fail. Moneydnace handles this by creating the extra account/category with the same name and the appended X and using this for the transfer.
The wrong sign problem has generally been seeing with downloaded data from banks, some who seem to think that transactions should be considered from their perspective, I have not seen if with regard to exported data from Quicken.
Duplication of transfers seems to occur when you have individual QIF files i.e. one for each account, it seems to be less of a problem when you export all data in a single QIf file.
Security Detail is a summary type page gained from the information in the transactions. The Investment Register shows transactions that are valid in the investment account. The bank register shows those transactions that are valid in a standard bank type register. The bank type register was largely introduced to facilitation those Investment type accounts that allow you to write cheques and in general use as a bank account.
Better transaction support in Investment accounts would make the transition a lot easier as well as better supporting Investments in Moneydance, however I do not see it will ever be a simple matter moving between software, QIF was never designed for this process to start with, so I expect the process to always be one of Migration.
Personally I moved over a decades worth of data to Moneydance that was in 70 odd accounts, some investment data went back 30 years.
5 Posted by dwg on 10 Nov, 2019 08:32 AM
FWIW marking accounts as Inactive will result in the balances not being included in net worth.
You can access closed, inactive accounts via the Tools --> Account menu item
6 Posted by dtd on 12 Nov, 2019 12:53 AM
dwg - Thanks for your reply! Excellent stuff.
As I said, I'm super impressed, but it seems moneydance is on the 5 yard line, and we have to do the rest. I will probably do so, but these comments are meant to help moneydance get to the 1 yard line.
7 Posted by dtd on 12 Nov, 2019 12:57 AM
This post is no longer pertinent. (9/8/22)
8 Posted by dtd on 12 Nov, 2019 01:00 AM
This post is no longer pertinent (9/8/22)
9 Posted by dtd on 12 Nov, 2019 01:13 AM
Part Two:
Totally get the fervent accounting principles bit. But something doesn't seem right to me in the ADD/REMOVE or as you say SHRIN/SHROUT principle. For investment accounts, there isn't an INVESTMENTACCOUNTX offset like for other accounts.
My quicken investment accounts, when doing an add or remove, end up as a BUYXFR or SELLXFR transaction in moneydance. Say we have an ADD for 25000 in Quicken. Yes, it magically appears in Quicken as an asset set of shares, and the balance does not change.
In Moneydance, the assets appear (Thank God! These end up being correct), but the money balance changes (ok, got it, accounting) - but it changes by 50,000, not 25000. That does not make sense to me. BUY works properly, but BUYXFER does a 2fer, and I don't see where (accounting wise) this works.
Any thoughts?
Miy thoughts? Maybe ADD/SELL SHRIN/SHROUT should be BUY/SELL, not BUYXFER/SELLXFR, but I'm new here, so I don't know.
10 Posted by dtd on 12 Nov, 2019 01:25 AM
Part 3:
The two above are my major responses to "maybe this should change" in Moneydance, based on your (dwg) excellent responses.
As I play and massage, here are some of my "how to do" requests:
1) I can hide bank categories and indeed they no longer affect net worth. But since these are compensations for the "magical Quicken money appearence/disappearence entries, is there any way to change them from category BANK to category INCOME/EXPENSE? Yes, yes, I know. But these entries were: "I trusted quicken, so maybe these should not be bank assets, but just magical income/expenses?"
It Would make it easier... as the user will be hiding all of this anyway as historical information.
So, can I CHANGE these categories, or maybe Moneydance just imports and creates MAGICX differently? (won't help me at this point, just wondering)
Bottom line question: can you change TYPE?
2) Can you change the header types *i.e how the data appears in the tables?)? Moneydances do not match Quickens, and I'd love to switch category with memo. Is there a change the title tabs area?
3) Ok, back to two above (sorry, going over notes) - ADDED/REMOVED does both a share entry and a cash balance bit (unless we talk about the 2fer) - maybe upon initial import, Moneydance could do a "magical compensating balance transaction entry" that literally said something like "QUICKEN and QIF is not cool, but I just adjusted your cash balance" entry for ADD/REMOVE?
I ask this, because MoneyDance DOES do this for OTHERACCOUNTSX, but not for investment accounts.
Enough questions/thoughts for the moment.
I'm a fan, really!
11 Posted by dtd on 12 Nov, 2019 01:44 AM
I do get that many of my suggestions are just to pull back the curtain and expose the magic magician of quicken.
But ultimately, the GOAL is to get people (like me, and often less I/T than me) to move to this really cool replacement for quicken.
So, if we could expose the magic, with accounting principles, and create INVESTMENT#X just like there is ACCOUNTX and put them in places (like expense/income vs a BANK type) where people could just leave them there or hide them, I think we'd be close to a touchdown in conversion.
I mean - I was SUPER impressed that so many things are already correct. As I said, 5 yard line.
Bottom line: If my NAV is $200,000 in quicken, I'd love to see something close to that in MoneyDance vs -2 billion (facetious, but serious)
12 Posted by dwg on 12 Nov, 2019 10:58 AM
Some Brief comments.
When you purchase shares the funds are expected to come from an account not a category, hence the logic to create an AccountX account.
You cannot change the order of fields in the register.
Investment accounts are a totally different beast to other account types and work very differently so I would not be expecting MD to create InvestmentX accounts.
The neatest approach I believe is to alter the initial value field in the Account info screen to reflect the monies need and delete the extra entry that Moneydance creates. I do not think it is appropriate for MD to change this field value since that amount to manipulating data.
You cannot change account types, accounts to categories etc directly.. You need to create an account/category of the type required then use the batch change option to move the transactions.
Without having a 1 to 1 relationship between actions any import is going to be a compromise and likely to need some work done.
13 Posted by dtd on 13 Nov, 2019 12:50 AM
I like the "set initial balance to the value necessary to make it all work" for investment accounts. Simple, and even though the cash balances are crazy, maybe even for years - it's historical, and will be fine going forward. I like it.
So, I guess I will move forward with only one question remaining. I'll copy/paste a modified version of the thought, hope that's ok.
--------------------------------------------------------------
Something doesn't seem right to me in the ADD/REMOVE or as you say SHRIN/SHROUT quicken actions when moving to moneydance.
My quicken investment accounts, when doing an add or remove, end up as BUYXFR or SELLXFR transactions.
Say we have an ADD for $25000 in assets in Quicken. The assets appear totally correctly, and the cash balance changes - but it changes by 50,000, not 25000. That does not make sense to me. BUY works properly, but BUYXFER does a "2fer", and I don't see where (accounting wise) this works.
Any thoughts? What's the logic behind this principle?
From my POV, Maybe ADD/REMOVE should repalce BUY/SELL, not BUYXFER/SELLXFR, but I'm new here, so I don't know.
NEW ITEM 1:
In continuing to clean up, I ran into "uncleared transactions/merge" and have no idea how I found that or how to bring it up again. VERY helpful to resolve credit card imports from Quicken.
Where might that be which I accidentally activated?
NEW ITEM 2:
I think I saw this, but lost it. Is there a way to show the balances on the side bar that INCLUDES future dates vs up to present date?
14 Posted by dwg on 13 Nov, 2019 05:02 AM
If there is a problem in a register I suggest posting a screen image, then we can all see what the transactions looks like.
Next to the "New Transaction" button there is a double headed icon pressing this toggles between a single register and splitting out unconfirmed transactions, is this what you are looking for?
Further Information on confirming transactions can be found in the knowledge base article:
https://infinitekind.tenderapp.com/kb/online-banking-and-bill-pay/c...
The available balance types available in the side bar can be found in the menu item File --> Preferences on the General tab
15 Posted by dtd on 20 Nov, 2019 06:18 AM
dwg - been out of town for a week, and am now back. Thanks for your valuable feedback. Making progress.
Taking your suggestion to post a screen image. Here is the original question:
--------------------------------------------------------------------------------------
THIS IS IN REGARDS TO AN ORIGINAL QIF FILE IMPORT TO GET A 20 YEAR QUICKEN FILE INTO MONEYDANCE.
Something doesn't seem right to me in the ADD/REMOVE or as you say SHRIN/SHROUT quicken actions when moving to moneydance.
My quicken investment accounts, when doing an add or remove, end up as BUYXFR or SELLXFR transactions.
Say we have an ADD for $25000 in assets in Quicken. The assets appear totally correctly, and the cash balance changes - but it changes by 50,000, not 25000. That does not make sense to me. BUY works properly, but BUYXFER does a "2fer", and I don't see where (accounting wise) this works.
Any thoughts? What's the logic behind this principle? (And yes, I know this is a magical input of money, but it just all seems doubled...)
Oh, if it helps, i'm pretty sure these were all "ADDED" transactions back in Quicken.
16 Posted by dwg on 20 Nov, 2019 08:00 AM
Interesting.
A couple of things come to mind.
The BuyXfr transactions are not working as they should. They should have no impact at all on the cash balance in the Investment account. I notice that the name of the security is identical to the source of the funds (Security and Transfer fields are identical). From past experiences I suspect this could cause problems.
I would change one of the BuyXfr to a Buy and see if the problem goes away.
We see a lot of problems arise when there is a mismatch of currencies, while I doubt if it is a problem here it is worthwhile checking the account, security and base currency and see what each it set to.
17 Posted by dtd on 21 Nov, 2019 05:45 AM
Changing BUYXFR to BUY stops the "2fer" - i.e. the balance change becomes the negative value of the asset ADDED/BUY versus doubled. I checked that a week ago.
Assets are added perfectly. That's of course essential.
Since this is "magic money" (i.e. ADD/REMOVE, SHRIN/SHROUT), I'm not surprised that the balance changes vs. not being affected (as Quicken handles it) and I am ready to adopt your suggestion of just putting a humongous initial balance that works its way through the years, and realize cash balance is garbage until present day.
However, it would gall me to do this with a lot of doubled cash entries - then it just plain seems wrong. Unfortunately it would be horrendously time consuming to walk through every BUYXFR and change it to a BUY (unless you know of a batch change facility in Moneydance?)
As to security/transfer asset in the picture I posted being the same, in Quicken, you only designate the security as it is an "ADD asset", so either the transfer value is buried in Quicken, or in the QIF file, or added by moneydance.
Is there any methodology to start a dialogue with someone at Moneydance about the logic of doubled cash balances with an imported BUYXFR (for a Quicken ADD) (or maybe it's a bug on QIF import?)
(as I realize this is a user-to-user forum)
18 Posted by dwg on 21 Nov, 2019 06:18 AM
The transfer field is expect to contain with an Account or a Category (really a special type of account) but in these records it contains the name of the security. So the transaction is saying buy this security but get the money from the security to pay for it. That is not good logic and not a valid thing to do.
What do these transactions actually look like in Quicken? I suspect that is where the problem is coming from, hence why I ask what the transaction looks like there.
You cannot batch change action types as the action type determines what actual fields are required, you also cannot batch change the transfer field, that field is not always active.
These forums are also the way to get official support, you can always try entering the problem in the Private Issues forum.
19 Posted by dtd on 21 Nov, 2019 07:50 AM
Here is approximately the exact same list as above, but in Quicken. I also am posting the detailed field for the 25000 (ne 50000 in moneydance) item.
As we both agree, Quicken isn't doing proper accounting, but hey, it's there. I'm pointing out that moneydance, if they want quicken users to flee to moneydance (a profitable concept) should figure out a way (as best as possible given the limitations of the QIF format) to import such files so that they at least look feasible. As I said - I'm willing to live with adjusting the initial balance to fix this, but doubling the cash balance issue just is... wrong.
Here are two files to show what this looks like in Quicken. Basically, it's a set of ADDs for a new account - inventing money (or assets) out of nothing.
20 Posted by dtd on 21 Nov, 2019 08:00 AM
quote: but in these records it contains the name of the security. So the transaction is saying buy this security but get the money from the security to pay for it. That is not good logic and not a valid thing to do.
admittedly, i don't want to dig into the qif format to suss this totally out, but i'm wondering if it is moneydance that made this transaction decision vs. quicken. Which is why I'm asking this. If moneydance, maybe a different decision is in order. If quicken, maybe moneydance should handle the import differently... just a thought.
in any case, quicken doesn't care, and moneydance, whoever's "fault" this is, ends up doubling the cash balance impact.
21 Posted by dwg on 21 Nov, 2019 08:46 AM
Ah yes Quicken's amazing ability to get money out of thin air. Add shares at a given price and total cost but do not take the money from the account's cash balance or anywhere else.
I suspect that Quicken is writing the QIF file with the security name as both the Y field as well as the L field. With an L field as well as price, quantity & amount data I would expect Moneydance to interpret an Add as a BuyXfr
Trying to program that using a generic import routine (given that the MD routine must be able to import QIF files from any number of sources) could be tricky, I know I had enough trouble parsing and getting data from Reckon (localized version of Quicken) into a fit enough state to produce useful QIF files. Some sort of pre-processing specifically for Quicken files might be the best approach.
Alternatively the developers would have to interpret an Add transactions as a Buy and ignore the transfer field, I'm not sure if this would have any other implications for imported data.
Support, I think this is something to talk to Sean about.
22 Posted by dtd on 21 Nov, 2019 08:08 PM
I would be glad to kibitz or test a change, but I think I've conveyed all the pertinent information, and I would be a sample size of one, only looking at my problem. Still - I'd love to get a version that solves the problem! :)
I'm continuing to play and massage, but this one item is preventing me from taking the leap. Everything else I've mentioned has workarounds. (Well, this does too, - change each and every BUYXFR to BUY by hand, but that's too much over twenty years when I used the improper Quicken concept of just creating accounts and populating them).
Also, given your concurrence that this should at least be looked at, should I restate this particular issue in the Private Issues area?
23 Posted by dtd on 21 Nov, 2019 09:38 PM
Moving to another topic for the moment, I'm also discovering an issue with the DEPOSIT entry for investment accounts. It appears as if the QIF file causes moneydance to enter TWO entries in the bank account the investment account is depositing "from", as well as two entries for the investment account itself.
The withdrawal from the bank account and the deposit into the investment account makes sense. But there are also separate
[Cash] and check# cash offsetting entries which redeposits/withdraws the same amount. Of course, this affects the cash balance of both the bank account and investment account as the money ends up offset, with no change in the cash balance. vs a withdrawal for one, a deposit for the other.
Thoughts? Logic? QIF file issue?
The first pic should be the QUICKEN investment account DEPOSIT detail, the second, the moneydance BANK ACCOUNT ledger for the same transaction, the third pic the moneydance INVESTMENT ACCOUNT. (This is a DEPOSIT into investment account Parent Schwab Funds , taken from the bank account Parent Investment Savings)
EDIT: Third picture is missing, don't know how to edit/add it.
24 Posted by dwg on 21 Nov, 2019 09:56 PM
Adding and removing for directly owned shares was something that always amused me, locally from a capital gains perspective it is totally the wrong way to handle the example usage that Quicken cited. My sole use of it was to round off shares for things like stock splits. In mutual funds it works in a totally different way and that I was happy enough with - especially as we often have less than complete information to work with from these.
Whether you want to raise it is a private issue is totally your call. I did place a pointer in the developers slack channel but obviously I can not say if he will pick it up.
I can see a number of ways of dealing with an ADD transaction, none of them are perfect though, especially as there can be different varieties of these transactions, we have transactions like yours then if I recall correctly mine were just for a number of shares (most often less than 1) with no cost involved.
25 Posted by dtd on 21 Nov, 2019 10:13 PM
I agree with your amusement. But Quicken provided it, and it was simpler to just create a new account, and add in the securities vs. doing it correctly.
That said, I'm very aware of proper accounting, and still did it constantly, because it worked in Quicken, and was simpler than doing both sides of the ledger.
I'm sure I'm not alone, and many others don't understand that Quicken is letting them be sloppy. The issue here is moving this sloppy allowed work to Moneydance, now that Quicken is a pain, and Moneydance would love all of our business. Unfortunately, many won't understand or appreciate "you did it wrong - tough." - I'm obviously building on the work of others - as so much is right. Still - going back to an earlier statement - I'm trying to help move the import football from the 5 yard line to the 1 yard line, in this case, so that ADD/REMOVE (and maybe DEPOSIT/WITHDRAW) are handled in a way that the (former) Quicken user will applaud.
26 Posted by dwg on 22 Nov, 2019 02:23 AM
There are enough nuisances in Quicken to perhaps justify an import module to be dedicated for this import. But having someone with the time, energy, motivation and knowledge to write an extension that could do this is another story..
There are no action types in Quicken Investment accounts for Deposits and Withdrawals, I'm not overly surprised by this. the action types and even the QIF format date back to the 1980/90's well before the days when investment houses create accounts that could be used like a cheque even though they are an investment account, so there needs to be some meshing of an Investment register and a bank register in the one account, and I do not think these always comfortable sit together. Why Moneydance is handling these transactions like it is could possibly only be explained by what data is in the QIF file for these transactions, that is the only data that Moneydance has to work with.
27 Posted by dtd on 22 Nov, 2019 07:30 PM
Looks like I have to buy Moneydance since doing all this testing quickly uses up 100 entries (or in my case edits like change BUYXFR to BUY)
(Reply to myself: No problem, you have 90 days more to get a refund if it doesn't work out, but you think it will...)
Could you amplify on what you mean by "action types". I look at this as possibly three things - the list of items Quicken lets you do, the Action types Quicken describes, or maybe the actions in the QIF file itself.
The first does have DEPOSIT/WITHDRAW, the second does not, the third, I'd have to google. Regardless, since the first has DEPOSIT/WITHDRAW, they do get used, and do get exported.... it's the journey from Quicken to Moneydance where things fall apart (cash balance wise).
28 Posted by dwg on 22 Nov, 2019 09:49 PM
The action type is the value in the Action field.
This really drives Investment accounts because the value in this field determines what other fields are active and also which of these fields is mandatory. In Quicken it can often be taken a step further because it can also determine the absolute value of the category whereas in Moneydance it does not do this hence why an action in Moneydance can have multiple uses with the user chosen category determining what it is i.e. in Moneydance it determines how a transaction is to be handled not what the transaction actually is.
Some activities you do are not reflected in action types in the software. For example in Moneydance stock split is not referenced as an action type, it is not really a transaction it is more of an event.
So form the Quicken Action field we have a 1 to 1 translation to Moneydance for some, for some others there is a substitute action that can be used (so some actions in MD have multiple uses) and some actions just do not exist in MD.
It is the cash transactions that are going wrong here, you can see they are not Investment transactions at all but are there to facilitate banking type transactions in an Investment account, how they are represented in the QIF file would be the only clue as to why Moneydance does what it does.
29 Posted by dtd on 22 Nov, 2019 10:18 PM
dwg: There are no action types in Quicken Investment accounts for Deposits and Withdrawals, I'm not overly surprised by this.
So based on your latest statement "value in 'action field'", Quicken DOES have DEPOSIT/WITHDRAW as action types.
Of course, as we both agree, it's what is exported to QIF that determines what Moneydance sees. Guess I'll have to break down and start examining QIF internals.
30 Posted by dtd on 22 Nov, 2019 10:24 PM
btw, as a point of order, I created a new file in Quicken and uploaded the same QIF file that I gave to Moneydance (for kicks and grins).
The things you expect to be right were right in both. The issue in BOTH is investment accounts, and bank accounts that interact with investment accounts.
It was a close and subjective evaluation, but I think Moneydance did better than Quicken in importing the QIF file. Both require massaging, but I think overall Moneydance did better.
That said, compliments to Moneydance, but there is a short window (April 20, 2020) to grab tons of Quicken 2017 users before they disappear into the unreachable abyss (no qif export) of Quicken 2019/2020.
I'm trying to offer thoughts on what would enable people to gratefully flee to moneydance, vs complaining "it didn't work" (even if it 95% does do so) - seeing their net worth be X vs -Y would certainly help perceptions.