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.
Showing page 3 out of 4. View the first page
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
61 Posted by dwg on 04 Feb, 2020 01:35 AM
My view is the more information that is around the better. Moving from Quicken, or really any other program, is a migration exercise.There are going to be issues, not everything will be clean, but there are various techniques that can be used to minimize problems, the more that is around on these the better. To put some more skin in the game I recently looked at migrating Foreign currency accounts, wrote up the results and posted them in the forum.
62 Posted by davidvernonlong on 04 Feb, 2020 05:30 AM
Yes, that would be good
63 Posted by jkosa on 08 Feb, 2020 08:39 PM
A detailed "migration from quicken" KB would be great. I've appreciated quick replies over the past week from dantdavis, dwg, and others who have been thru it already. Having that in one place would be great.
Basic migration of checking/savings/credit cards is one thing. Migrating investment accounts is another whole can of worms. I can't imagine migrating foreign currency accounts!
Jaimi
64 Posted by dwg on 08 Feb, 2020 09:18 PM
Quicken never did make it easy, and if they can they like to make it impossible, locally Reckon have made it even harder.
My current notes on Migrating from Quicken take up about 9 pages, that does include Foreign Currency Accounts, which I think can be done provided Quicken plays ball a bit in the rates they hold. Tellingly I have very little noted for Investments though.
Investments are a real rabbit warren. There are at least three places where you can make changes that impact your migration; in Quicken, in the QIF file and in Moneydance (usually post import and using various tools) You can also add foreign currency where you need to do separate exports and imports of data.
Handling all the various actions types can take you down various black holes.
I have worked out some but nowhere near all, and for some there is just not a good answer.
Migrating from Quicken is not so much on the scale of a KB article but rather a small but growing book. :)
Personally I'd rather see TiK grow the number of supported Action types and enhance the QIF importer to deal with some types that Moneydance can handle in a different way.
65 Posted by dtd on 08 Feb, 2020 09:29 PM
Migrating from Quicken is not so much on the scale of a KB article but rather a small but growing book. :)
Personally I'd rather see TiK grow the number of supported Action types and enhance the QIF importer to deal with some types that Moneydance can handle in a different way.
---------------
Even though I'm past this issue and now have 35 years of clean data (yeah!), I agree wholeheartedly with this. Even if they just changed how BUYXFR doubles the transaction (vs BUY), and other entries end up being zero due to offseting transactions - that would go a long way.
But that's part of the detail in the small but growing book you mention... ;)
66 Posted by dtd on 08 Feb, 2020 09:39 PM
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.
67 Posted by davidvernonlong on 11 Feb, 2020 05:06 AM
Hi,
A few things I would mention:
If you change the base currency you are asking for trouble and reports get
screwed up.
In my case price history of securities was not transferred. There may be a
way to import it, but I have not discovered it yet.
In securities where there had been bonus, rights, scrip issues there were
problems with balances, so I had to put in some manual purchase at zero
cost transaction. There may be a better way, but that corrected the
balances.
68 Posted by dwg on 11 Feb, 2020 06:07 AM
I'm a fellow user.
When you export your transactions no security price data is included in the QIF file by Quicken., hence why any pricing in Moneydance would only come from buy or sell transactions.
I do not recall if there is any way of exporting the price data in a QIF file from Quicken, there is provision in the QIF specification for such data and I have been able to import QIF files containing price data into earlier versions of Moneydance.
I'm pretty sure that you can export the price data into CSV type files on a security by security basis similarly to how Exchange rates can be exported.
I have located some brief instructions for this:
"Through the security details, you can access and edit the price history information. From that listing, there is a Printer icon option. Through that icon, you can then switch to exporting the data to a text file, a tab-delimited file, or a PRN file."
I have found something on Exporting the data in the QIF format but as to whether it works I do not know:
"File / Export / QIF option, Security selection will include the price history info for all (all applicable to the account?) securities."
If someone wants to provide an output file from Quicken I'm happy to take a look at what sort of gymnastics would be required to import it.
69 Posted by jon on 11 Feb, 2020 07:54 AM
Yes, I suffered most of these issues, but have corrected them the same as you.
------------------------------------------
Jon Diamond
07885 372605
http://www.tunbridgewellscroquet.org.uk
http://www.britgo.org
70 Posted by jkosa on 11 Feb, 2020 10:43 PM
I'm a new user, and have fallen into the rabbit hole of investment transactions.
I found a post from 2013 that said investment registers do not currently support matching & confirmation of downloaded transactions. Another post from May 2019 seems to imply the same. Is this still the case? I really can't match 2 transactions, even manually?
My QIF import worked ok (all Vicissitudes aside!), but the 1st online connect downloaded 2yrs of xans. Is my only recourse to delete the QIF imported xans and confirm the ofx downloaded ones (so future direct connect downloads won't keep trying to import the same ones again)?
Jaimi
71 Posted by dtd on 11 Feb, 2020 10:55 PM
My impression is you cannot match (merge) transactions in investment accounts.
I created a thread recently (can't find it at the moment) asking, and dwg said you can't do this.
I also had two years of downloaded transactions from Schwab and two years from Vanguard. It's one reason I made sure all my QIF imports had cleared, which allowed me some leeway in telling the difference.
But yes, I had to delete each old one by hand, and fix the new ones as well (ie.. copy over memos and fix some bad action items in the new items.) It was not fun.
But at least it is a one-off. It just took an entire evening.
72 Posted by dtd on 11 Feb, 2020 11:00 PM
Found the thread on the subject you are asking about...
https://infinitekind.tenderapp.com/discussions/general-questions/94344-merging-accepted-items
73 Posted by dtd on 12 Feb, 2020 09:37 PM
David Long wrote:
In my case price history of securities was not transferred. There may be a
way to import it, but I have not discovered it yet.
---------------------
I did get my security price histories transferred, a couple of lines from the QIF file for one security:
!Type:Security
N1 800 FLOWERS.COM INC
SFLWS
TStock
^
Then later in the QIF file:
!Type:Prices
"FLWS",6.30," 2/28' 3"
^
!Type:Prices
"FLWS",6.72," 3/31' 3"
^
!Type:Prices
"FLWS",7.611," 4/30' 3"
^
(and more)
The only thing I made sure of was to click all the boxes in the QIF export - which includes a box called Security List.
74 Posted by dwg on 12 Feb, 2020 09:51 PM
I'm a fellow user.
That is the QIF price format I was able to import in older versions, no reason to think it would still not work.
I tried a file like:
!Type:Prices
"AGK",14.84,"10/17/2012"
"AGK",14.86,"10/18/2012"
"AGK",15.02,"10/19/2012"
"AGK",14.96,"10/22/2012"
^
75 Posted by dwg on 12 Feb, 2020 09:58 PM
I'd be curious to know which of the Quicken export option, I know some versions have a few, actual results in the security prices being exported. I do not think there is any way of exporting volume data but hat I think is less significant in any case.
76 Posted by dtd on 12 Feb, 2020 10:17 PM
Here's the screen I've become very familiar with recently, in Quicken Windows 2017.
I cannot say which one actually results in security prices being exported, as I always have checked all boxes.
77 Posted by dwg on 12 Feb, 2020 10:40 PM
So in all probability it is most likely part of the Security List.
78 Posted by dwg on 13 Feb, 2020 12:46 AM
OK I have found an Intuit published QIF specification that has a structure that seems to correlate with the QIF Export Option screen.
From that spec I would derive the following:
Transactions: Moneydance definitely uses these
Account Lists: I'm not sure if the Moneydance importer would use them or not
Category Lists: Again don't known If Moneydance uses these
Memorized Payees: Moneydance does not use these. Moneydance does not keep such a list.
Security List: It would seem that Moneydance may at least partially use this list
Business List: Items are of no use to Moneydance
Moneydance typically builds the accounts/categories from the transaction lists, historically this was all that was provided, hence the approach taken.
The memorized payees list does not correspond with the data needed for reminders.
The security prices are in the Security list definition that Intuit has provided, even to the level of including a sample of the output. So from the experience reported in this thread it would suggest that Moneydance imports the prices from this, whether it imports any other data from this section again I do not know.
79 Posted by dtd on 13 Feb, 2020 01:51 AM
dwg, ok, now you made me go look.
Yes, it appears security prices come from the security list. But I don't think MD uses the security list to create it's OWN security list.
Three instances I explored:
1) I own the securities.
2) I used to own the securities, but haven't in years.
3) I used quicken to download prices (DISNEY), but have never owned the security.
MD does this:
1) security available, price history available, easy to access via graph from where the security resides. easy to access with quote loader.
2) security available, price history available, can be accessed via graph by picking it specifically from a different security being shown (go to the security and pick a different one), accessible with quote loader by clicking show zeroes.
3) this is where the fun begins. DISNEY (DIS) of course is not available by looking at accounts, but it is also not available by looking at the security list within an account - I do not see ANY way to show it by graph. However, if you go to view securities, it IS in the security list, and you can bring up a history stock price table. Quote Loader does not see it anywhere.
Found that interesting.
80 Posted by dwg on 13 Feb, 2020 02:04 AM
I'm a fellow user.
I have create three lists by hand an Accounts list, a Security list and a Category list.
Moneydance imported all three and created the appropriate structures, the importer did ignore some QIF fields that Moneydance does not use. For this test I used Moneydance 2019.4 (1903).
81 Posted by dwg on 13 Feb, 2020 02:21 AM
You can only see a Security within an account if you add the security to that account, otherwise it has no association to that account.
The definition of the security is held separately, and the prices are held as part of this definition so you can see them when you go into securities, however there are no graphing functions within the security area.
82 Posted by dtd on 13 Feb, 2020 03:20 AM
So DISNEY would belong to no account, thus no way to show a graph of the price history.
83 Posted by dwg on 13 Feb, 2020 03:30 AM
That would be correct.
If you had some shares that you wanted to monitor the price over time graphically you could create a separate investment account and add them to this account.
System closed this discussion on 14 May, 2020 03:40 AM.
dtd re-opened this discussion on 03 Mar, 2021 06:02 AM
84 Posted by dtd on 03 Mar, 2021 06:02 AM
reopening this in case a possibly interested poster cannot do so.
85 Posted by harold on 03 Mar, 2021 03:49 PM
Hello, I am in the process of cleaning up my Quicken 2016 H&B QIF import with decades of data. The bank accounts are all reconciled I am now working on the investment accounts. I spent the last 2 days trying to find a way of downloading investment transactions from TD Direct Investing in Canada. From the Holdings page I can download a QFX file, but the file contains only a sample security with a 0 value, maybe because there is no new transaction in the date range which cannot be specified.
From the activity page I can download transactions dating back to mid-January but they are only available in CSV format. The MD native importer was unable to extract much useful information, so I used the Text File Importer extension.
With it, I could extract date, description, action, quantity and prices of shares and the payment and deposit amounts including interests but not the commissions. I could not import it into the investment account but was able to select the “investment accountX” (that the QIF import created as a bank account). The action fields are properly calculating the balances, but the quantities are in the memo fields and the prices are in the account fields.
I wonder how I should proceed: cut and paste the data into the investment account or clean up the Inv accX and use it as an investment linked bank account. The original Quicken investment account was reconciled until 2017 but only with a number of cash balance adjustments. My intention is to use the Initial Cash Balance feature dated to 2017 and then reconcile the account from there.
I have also tried to use Stuart’s Extract Data 1004 but had some unexpected results: when I ran it on the TD CSV file I left the default mapping, saved the file and then opened it with MD in the investment account. It inserted many transactions back to dates before mid-January. What am I doing wrong?
Is the QIF converter QIF2CALC worth a try?
Overall I would like to say that I am really glad that I have finally found a Quicken replacement in MD, that actually fits my needs better, is supported by an amazing community and is produced by a company that values ethics more than annual extraction fees from its captive customers.
I am not sure if anybody is working on updating a manual; the knowledge base is good but incomplete and there is a lot of useful information in the discussion groups that needs to be integrated into the KB/manual. The software is excellent and if the documentation can be improved you would attract a lot more Quicken customers. There is a lot to know before you should even attempt to import a large QIF file from Quicken, so detailed documentation is crucial particularly right at the beginning.
I am planing to post my notes and observations when I am done and would be happy to contribute to an effort to improve the documentation.
Thank you!
86 Posted by dwg on 03 Mar, 2021 08:35 PM
I'm a fellow user.
CSV is a simple table format, Moneydance can import this data into accounts that have a structure that the table format of CSV files can be mapped to, for most users this means bank type accounts.
Investment accounts do not have a simple table like structure. The data that can be in an investment transaction is driven by the action field which has a discrete number of valid entries. The action determines what fields are available and what fields are mandatory thus it is not a simple matter of mapping a field in a CSV file to a field in an investment record. OFX files are not used in migration they are generally only used for transactions obtained from financial institution via manual or automatic downloads.
OFX is not a suitable format to use to migrate from one program to another as it has no provision for category information. It really is for downloaded data in this instance.
QIF is the most complete format for migrating of financial data and especially of Investment accounts so you should stick to that and you should be looking at the Investment register in the Investment account and not the Bank register, which is nothing more than a sub-register that only shows bank type transactions.
An InvestmentX account is usually a sign of Quicken's bad behaviour of doing things like drawing on the account itself to create an initial balance, yes Quicken allows you to both create and dispose of money in thin air. Moneydance being accounting based will not tolerate this and creates an account for any transfers. For initial balances put the balance in the initial balance field on the account info screen and delete the transaction.
For specific problems with QIF imports, and with investment accounts there will be some, you will need to give us the details of what they are so that solutions can be suggested.
QIF is not a perfect format, even though it is the best available, and Moneydance does not have complete handling of every Investment action type, so unless the investments are pretty simple there will be issues.
87 Posted by dtd on 04 Mar, 2021 03:15 AM
Welcome.
If I may try to interpret - you have Quicken 2016 H&B, but you haven't really used it since 2017. And of course now you can't download new data as it has expired.
I'm not quite sure what you want to do as you refer to 'decades of data', but at the same time write as if you are going to start fresh from 2017.
As dwg said, bank accounts are easy, investment accounts are problematic - and you can probably forget csv as a viable import format for investment accounts.
The reason I referred you to this thread is I did a qif import for 35 years of data - and it took a terribly long time to do - even with QIF.
So, let's start again - what will work for you? Do your banks support direct connect? Can you manually enter a security set into an account from a specific date? Enough. Knowing specifically what you want to occur (and I know the answer - as of today's date, have an investment account with all the correct values in it) - but still what do you want the account to have from the past?
Oh and yes - accountX accounts are a bit due to Quicken being loosy goosy - I doubt you want to read the entire thread here - but yes, that part is covered.
Hope that helps, just a user.
88 Posted by harold on 04 Mar, 2021 04:34 AM
I thought that I had posted a reply with a screenshot a few hours ago, but it seems it was not posted. I had ISP interruptions today, so maybe it did not go through. Here it is again slightly amended to respond to your last post:
My last Quicken download was end of May 2019. I have manually entered the missing transactions between then and the earliest transactions that I could download from my bank which was August 2019. I decided to reconcile my bank accounts and loans starting in 2017 for now.
TD does not support Direct Connect in Canada, so I am manually downloading. As mentioned, from the Holdings page I can download a QFX/OFX file, but it only fetches a “sample security” with a 0 value. I believe in Quicken this was a placeholder that needed to be resolved in a wizard. It is possible that if I had an actual transaction that is in the proper date range I could download it, but at this point I do not know. Now I am trying to download transactions from the activity page of the bank site which comes in CSV format.
Let me rephrase my question:
I have extracted all essential transactions from the CSV download (except the commissions) with the Text File Importer into the investment accountX. However, because of the mapping some ended up in the wrong fields. See the attached screenshot.
If that accountX is merely the result of Quicken transactions that do not have an “other side” do I still have to hunt down the other sides and correct all transfers between investment accounts? My bank accounts are reconciled, so the transfers involving any bank accounts are all correct. To repeat that process with the investment accounts would be a lot of work, so I would go back only to 2017 offsetting the incorrect balance with the Initial Balance option in Accounts|Edit on the start date of that range.
If the Quicken phantom transactions do not affect my ability to reconcile the investment accounts I would just hide and inactivate that accountX. In that case I would create a new bank account and extract the data from the CSV download into that account. Is there a way to extract the data into the linked bank account of the investment account directly? If so can MD make use of the data there in reports etc?
If not I guess I will have to cut and paste the extracted CSV data into the investment account manually.
Thanks
89 Posted by dtd on 04 Mar, 2021 04:53 AM
Very late here - but yes to this statement.
If the Quicken phantom transactions do not affect my ability to reconcile the investment accounts I would just hide and inactivate that accountX.
Let the loosy goose stay loosy goosy.
More tomorrow.
90 Posted by dwg on 04 Mar, 2021 05:38 AM
Your screen dump looks like a bank account, it is certainly not an investment register.
It is just not possible to import the transaction data from a CSV or any general form of a text file into an Investment account register. If it was a pristine CSV file with just the right data and format yes it can be converted into a QIF file but I have yet to see an investment house get anywhere close, even CSV files produced by Quicken are not right and require programming effort and quite some lines of code to make them so.
There is no such thing in Moneydance as a bank account linked to an Investment account. You have Investment accounts that contain all the transactions but you can view it through an Investment register which does show all transactions just some are not in a totally useful way or you can view it through a Bank register which only shows those transaction that make sens in a bank account i.e. it does not show Investment centric transactions in the listing.
The idea of a bank register in Investment accounts is to support those institutions that allow you to use your Investment account like a bank account e.g. writing cheques.
It is no use importing CSV of investment transactions into any form of bank register it is just of no use.
If you have QIF files from Quicken these should mostly work, yes the Investments will still require some work but the core data should be there and in a usable format. It is not clear to me if you have imported these and are just filling in the blanks or exactly what stage you are at.
If you are filling in the blanks you need either OFX/QFX or QIF files to be able to import. If you do not have access to either and just have the data in CSV then sorry to say that manual entry looks like the only alternative. I would not be hopeful of getting a general CSV file into any sort of useful format.
You do not have to touch the created AccountX accounts if you do not wish to, yes you can make them hidden, it really depends on the person, for both dtd and myself I know we wanted to make our data as clean as possible, and leaving these accounts on the system was therefore not a consideration.