Importing from Quicken -- start of year?
I'm using the Moneydance trial version to see if I can transfer from Quicken (for Windows) to Moneydance (on a Mac). Thanks to kind help from this group, I've been able to try it a couple of times with some different arrangements, but trying to import all of my Quicken history leads to quite a mess.
So my question is whether it's possible to just import everything for the current year (or more likely lat year plus the current year). This would mean skipping all those old, long-dead accounts and oddball transactions from years ago. I would still have the Quicken data for historical lookups; it just wouldn't be in Moneydance and I'm good with that.
The obvious issue is that account starting points won't make sense. For example, my checking account may have a balance as of January 1 of, say, $5000, but Moneydance would have no way to know that. With investment accounts, there would be equity positions that again would not be reflected in imported transactions. I assume that I could fix a lot of these issues manually, e.g., by entering statement balances for bank accounts. That could be tougher for investment accounts, but I probably could manage it.
So has anyone tried this approach? I saw a reference to "starting with a year" on a knowledge base article, but couldn't find anything else about how to do that. Is there a way to import balances in addition to transactions? How about credit card accounts?
If it's unexplored territory, I can experiment. But I suspect that I'm not the first person to try this! Any pointers are greatly appreciated.
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 04 Aug, 2024 04:13 AM
You can do this, but yes, it is complicated. First, can you describe what the "mess" is trying to import all of your history? I did this 3-4 years ago, so I think I can describe one "mess" is that all of your account balances are totally screwy (like if you sum it, it's maybe even a million dollars off).
However, your securities should be fine (versus missing) and all the transactions should be ok. It's just the balances. One quick and dirty way to fix this (still simpler than starting from scratch say on Jan 1) is to adjust the initial account balances (which you'd have to do anyway if you do the Jan 1 thing) and hide/fix any ACCOUNTX entries.
Here is some info from my process, and my conclusions - so if some of it sounds odd, it's because it's extracted posts.....
https://infinitekind.tenderapp.com/discussions/switching-from-another-personal-finance-program/15012-the-vicissitudes-of-quicken-to-moneydance/page/4#comment_57174282
2 Posted by dtd on 04 Aug, 2024 04:14 AM
If there is one "overview" that I think makes sense, it would be this:
I wrote about 3 pages today (mostly notes/outline), but here is probably the most pertinent bit for people struggling with wondering if the import will ever make sense.
Using dwg's comment: There are at least three places where you can make changes that impact your migration; in Quicken, in the QIF file and in Moneydance.
The quickest "well, I believe it will work out" is to make a few changes in MD:
1) IMPORT QIF FILE (details in many places)
2) Hide and make inactive all ACCOUNTX creations (Account/Edit Account)
3) Make the final balances for all crazy cash balances match quicken by changing the Initial Balances (Highlight each account, Use Account/Edit Account/Change Initial Balance) - yes, the values you enter will make no sense other than forcing MD to match Quicken)
4) Your cash balances from day 1 through today will be totally wonky. Look it over, report if other things are wonky other than cash balances through time. [This may expose some other things to write about in the small but growing book]
**************************
This isn't really a final solution, but it may make you look past some things that are wrong and let you see the things that are right (credit cards, security holdings, etc.)
Then you can decide if you can live with the above - given you have a life, or now at least be able to see what you need to do further.
And if more detail gets written - you can explore the rabbit holes of investment transactions.
3 Posted by dtd on 04 Aug, 2024 04:17 AM
Now, if you really want to start on Jan 1, you pretty much have the grasp of it. Download a QIF file from Jan 1 to date. Import it. It will still be crazy, for the reasons you describe, securities messed up or missing, and balances off - and you still do the "set an initial balance that makes today's balance correct".
You may still even have some ACCOUNTX accounts, if you do what Quicken does wrong - set the category to the account itself (i.e. create/destroy money).
i.e. basically you'll have to do the same thing I describe above anyway, while adding (and it is a pain) correcting your security balances.
Hope that helps.
4 Posted by dtd on 04 Aug, 2024 04:18 AM
As for having all those long dead accounts, yes, that's true, but you can just make them inactive, and now you'll be able to examine them in MD versus having to go back to Quicken. I can guarantee, with 35+ years of data, it won't bother MD at all.
5 Posted by dwg on 04 Aug, 2024 05:03 AM
You would firstly have to get QIF files that start at a particular date. Moneydance will not selectively import from a certain date.
Your normal bank, credit card etc types of account are not an issue as you can set the initial balance to suit, pretty much the same as when you initially started to use software to manage your finances and had to set the opening balance in the software.
Investments are always tougher, to get a correct cost basis really requires all of the buy and sell transactions.
6 Posted by bheck11 on 04 Aug, 2024 07:25 PM
@dwg, @dtd, wow, thanks for all the info and for the very useful links.
A few notes about what went wrong:
- @dtd, yes, the biggest issue is a plethora of weird cash balances. ((I'm pretty sure that none of my accounts have negative balances in five figures... :^)
- There are plenty of ...X accounts
- MD characterized a number of my investment accounts as bank accounts (I recharacterized them, but I'm curious as to what happened).
The good news for me is that it does sound possible to simplify things by limiting the import to less than the entire history. I'm now thinking of maybe a few years, which is likely as far back as I would normally need to review. (I could make it 7 years as that's as far back as any tax review is likely to go -- if I ever have a review, which is unlikely anyway.) Then I could keep the entire history in CSV files for "just in case" issues.
Another bit of good news is that most of our investments are in tax-sheltered accounts (IRAs), so we don't care much about history. We do care about keeping them updated, of course, and we really care about tracking bank and credit card accounts. So I think that I can concentrate on those goals and skip the ancient history.
I'll report back, if only in hopes that all. this is of use to others going through the same process.
7 Posted by dtd on 04 Aug, 2024 07:50 PM
Good luck, and yes, would love to hear about your process.
8 Posted by bheck11 on 05 Aug, 2024 04:00 PM
Well, we're closer but not quite there yet. And I have a question (see below).
First, the Quicken export: as Quicken users know, in Quicken you can hide accounts that are no longer in use. The accounts, including all transactions, are still there, but don't show up in the account list until you check the "show hidden accounts" box. Surprise, surprise: when you all accounts to a QIF file, those hidden accounts are not included! But if you go to the account list, tell it to show hidden accounts, and then to the export, the hidden accounts are included. [Editorial comment: it should be obvious that a control for what is shown on a screen should not affect an unrelated fundamental function like exporting data. Just a bad -- as in terrible -- design decision by Quicken.]
So now that an expert of all accounts actually exports all the accounts, I did try bringing in our entire history (going back to the late 1990's!). It worked, but now there's another issue and my question. (And yes, I did search the KB and the discussions for the following issue; the only issue that I saw that seemed quite relevant had to do with a similar issue when importing from Microsoft Money, but the potential fix didn't apply to Quicken.)
in MD, transfers between accounts in split deposits are duplicated. Here's an example: In Quicken, a checking deposit (salary for a job) includes a number of splits for taxes, etc. One of the splits is a transfer to a 401(k) account that is used to purchase shares of an mutual fund. Again in Quicken, the matching transaction in the 401(k) account is "BoughtX" using the incoming funds to buy the fund shares.
In MD, in the checking account, there is a deposit with the same split, i.e., transferring $500 to the 401(k). On the 401(k) side, there is a deposit for $500. Then in the checking account there is a second deposit of $500 that is attached to a "BuyX" purchase of the fund in the 401(k) account. In other words, there are two transfers to the 401(k), one of cash and the other to buy the shares. So the cahsh balance of the account increases by $500 -- which is wrong.
So far, I've found this only in salary deposits. As both of us are retired, the issue can be corrected with a kludgy but reasonably easy fix in the 401(k) by just subtracting a lump sum for the excess cash on a date after our retirements (one fix per 401(k), 457, and whatever other accounts. But on the checking side it's a disaster: sure, I could do the same thing on a date after retirement, but the point of having checking history is to be able to review it, and the checking history would never be right until that retirement date.
So the questions are: (1) does anyone know what's going on here and (2) is there any way to avoid and/or fix the issue? When I say "fix", I don't mean adjust transactions individually: there are hundreds of them, so that's a non-starter.
My alternative plan is to go back to my original plan and just bring in a time-limited set. We retired some years ago, and so far I've seen the above issue only for salary deposits. We do have other transfers among funds, so I'll need to check those, but there aren't too many so that should be doable.
The one real oddity here is that my case surely is not unique. Lots of people have automatic deductions to things like 401(k)s, 403(b)'s, and so on, with automatic fund purchases. Surely this is a know issue -- and I just haven't looked in the right place for a fix?
Thanks in advance for any insight!
9 Posted by dtd on 05 Aug, 2024 05:20 PM
1. Yes, all accounts need to be open. That and other items are in the DerekKent message I linked to:
I'd start with the derrekkent23 post that is posted about preparing the QIF file and so on....
https://infinitekind.tenderapp.com/discussions/switching-from-another-personal-finance-program/21672-how-do-i-incorporate-old-quicken-files#comment_55919792
2. I can't tell exactly what is happening, but it sounds like you are doing one of those money creation/destruction things - which MD will not allow as MD follows double entry accounting, whereas Quicken lets you do just about anything you want. I'm guessing, but it sounds like the $500 [401k/mutual fund] is somehow "crossing streams in the timeline" (Back to the Future Joke) and thus what you did in Quicken (create or destroy money - or in this case maybe both) causes the ACCOUNTX creations. ACCOUNTX existence is almost always because MD has to correct the problems from Quicken (problems which you created of course, because quicken allowed you to do, but so did I by like depreciating my car by putting in a negative entry against the car account itself) - This is one reason the balances after QIF import are sometimes (always) wonky. MD is doing its best to make the Quicken items work. (Another different reason for wonky stuff is investment account actions that MD doesn't recognize)
You can try to resolve the ACCOUNTX (BOUGHTX/BUYX) items in Moneydance, but your concern seems to be checking balance and 401k balance - over time. That's what I mentioned in the "quick fix" - your checking history IS there, your 401k balance IS there... just the balances are wrong. I've already mentioned a quick fix in that message I sent:
1) IMPORT QIF FILE (details in many places)
2) Hide and make inactive all ACCOUNTX creations (Account/Edit Account)
3) Make the final balances for all crazy cash balances match quicken by changing the Initial Balances (Highlight each account, Use Account/Edit Account/Change Initial Balance) - yes, the values you enter will make no sense other than forcing MD to match Quicken)
4) Your cash balances from day 1 through today will be totally wonky. Look it over, report if other things are wonky other than cash balances through time.
Note particularly 4) - yes, your balance history up to "today" will be ridiculous, as all the things that Quicken does wrong, or has action steps that MD does not recognize make the balances crazy (like 401k up 500 every payday), and you fix that by putting an arbitrary number at the beginning of time which makes TODAY right, and from thereon. (including eliminating all those $500 increases immediately). The point here to enter "all data" is to have all your securities correct, and today's balances correct.
It does NOT fix that your balances in the past, but all the transactions are there to review.
To go further (and you can read that in the thread I point to as well), if you want ALL balances correct (not just today forward), you do indeed have to fix these things, and sounds like you have a lot of them. One way is to do it in Quicken, then reexport, but yes, it is a lot of work. With my 35 years of data, I probably put in 100 hours of work over 10 weeks to make my data "perfect" [I kept adjusting those initial balances over that time as I fixed things so I could keep using MD] . Sounds like you want it perfect as well, but that requires work, versus the quick and dirty solution that requires maybe a hour or two.
Yes, your case is not unique - and many take the quick and dirty approach I mentioned, and live with wonky balances, which suddenly fix themselves on "today" and from then onward. They still have all their history, and just ignore the weird balances in the past.
Going back to the time limited import is possible, but then you'll have to deal with entering your security values by hand, no longer have cost basis (unless you get that from your broker), and you will probably still have wonky balances and ACCOUNTX entries to handle (unless you never broke any quicken rules in that time limited period)
Hope that helps, willing to help more, just as dwg and derekkent23 helped me 4 years ago.
10 Posted by bheck11 on 05 Aug, 2024 07:24 PM
Thanks again, @dtd! I have just looked through -- this time in detail -- the amazing stream of posts in the "Vicissitudes of...." stream, and I think that my issue is related to the one you first raised in post 23, then in post 39 with extensive discussion following.
However, I should note that there is no X account in MD for the 401(k) account. There's no "magic money" issue in this case and the transactions post on the same day. On the checking side, there is a real money deposit: the total deposited amount is simply split via a Quicken split in the register. In more detail, there is a salary amount (say $2000) which is then split several ways. The amount split to the investment account is treated as a transfer and buy, i.e., in the investment account register, there is a BUYX transaction for $500 purchasing some mutual fund shares. For simplicity, let's say that total taxes were $500, which is indeed held by the employer, then $500 is diverted to my investment account via another line in the split, leaving $1000 now added to the balance in the checking account. So far, Quicken has done it right: the bank is happy (they added $1000 to my account), the governments in question are happy (they received that total of $500 withheld), and the mutual fund company is happy (they received that other $500 and sold me some shares of the fund, which is what Quicken shows in my investment account). No magic money involved, unlike ADD transactions and such -- real accounting only here.
But when we get to MD, things go awry. I have not looked at the QIF file. (In my previous life, I did nose around in impenetrable files of this sort, but I'm out of practice! :^) But wherever it happens, the perfectly legitimate transaction turns into two transactions. That would be fine if MD had, say, a simple transfer from checking to investment account followed by a standard BUY to purchase the mutual fund shares. But for some reason, what I get is a regular transfer followed by a BUYX <-- meaning the same amount of cash is transferred again to buy the fund shares. Thus, the checking account is debited not once but twice, while the investment account is credited twice as well, with one of the credits going to cash with the other going to purchase fund shares.
After further thought, though, I am coming around to your suggestion to just adjust balances as needed. Yeah, the checking account will look wonky until whatever date I set as "balance up day", as will the investment account. But as you note, all the transaction will be there and I can ignore the balances, odd as they may seem.
BTW, another solution occurs to me, which is to insert cash transfers in the other direction, i.e., from investment to checking account. (Returning the ill-gotten gains, to use a cliche.) I wouldn't undertake to insert all of the transactions (meaning one per month for both myself and my spouse for whatever number of years and doubled in some years by the use of 457 accounts!) to keep everything on an even keel, but I could choose my and my spouse's retirement dates when contributions ended; on that date, insert a cash transfer from investment account to checking account for the total of all the bogus transfers in the other directions. If I get really ambitious, I could do one such transaction per year, which I admit supposes more ambition that I am likely to summon.
Editorial comment: I'm kind of surprised that this issue arises. Regular contributions from paychecks to investment accounts with automatic investment are super common. I would have thought that this sort of issue would be happening left and right, thus drawing attention for a fix. Oh well....
In any case, thanks again also for the link to the DerekKent post: I was indeed doing everything on that list except making sure that the hidden accounts control was on "show". Again, I am floored by the idea that a display control in Quicken also controls the meaning of "All accounts" in a function in the file menu. "Hidden" means concealed, not eliminated. Another oh well....
@dtd, thanks again to you, not mention to @dwg and others for sharing in such detail. I'll try to pay it forward -- once I do indeed get things working!
11 Posted by dtd on 05 Aug, 2024 09:00 PM
I do think messages 23/39 apply, there were two major doubling/eliminating issues back then, but still now, as the QIF import issues have not changed since 2019, so anything you read in Vicissitudes are still pertinent.
BTW, I just reopened the thread if you have any thoughts to add, every time I've done this, good thoughts and opinions have been added.
As for no ACCOUNTX in the 401k bit, complex transactions (multiple splits, multiple accounts) often yield strange results and balance issues. I cleaned up a lot of them for myself - I see you are also running into the "not all quicken action steps are supported" bit.
One reason I suggest just doing the initial balance/ACCOUNTX thing is that it gets you going, and keeps your securities right, and provides the history (screw the balances).
And you can adjust it slowly over time. and adjust those initial balances.
The one thing that delayed me was action steps - there is no batch change for action steps in MD (has been asked for) so before I went into "it's all ok" mode and slowly adjust things over time, I did do a bit of batch change in action steps and did a new QIF export (I did ONLY about 50 of those) and then just accept what I ended up with and slowly fixed the rest.
As to your reverse solution, I did that as well, buried in inactive are quite a few accounts called FLEX: Account 0xx where I did that to free bank accounts from investment accounts (i.e. their balances became fine, while the flex accounts "absorbed" the crazy)
The point is - regardless of how perfect you want it (I'm the perfection type - which is where all the Vicissitudes bit came from) the initial (and for some - FINAL) "adjust balances and hide or fix/delete ACCOUNTX items - moves the ball from the 5 yard line to the 1 yard line. Then you either declare a touchdown, or spend quite a bit of time moving 1 more yard (and yes I "had" to do that,) but as you say we are not unique, and you do see others who did the same conversion declaring that in Vicissitudes.
12 Posted by dtd on 05 Aug, 2024 09:04 PM
Oh, as to daily security values, the extension Quote Loader is definitely your friend.