QIF file export and import question from old to new data file - QWin
Options
BK
Member ✭✭✭✭
Dear Experts & Superusers,
I am experiencing some issues with my data file where it is in a limbo state between the former Quicken connection channel and the new one and all started since the Schwab fiasco. Support team is looking into it but I am not hopeful [none of the backups or file copy help either!] . Appears that I need to start with a new clean file and import my data - which I have been experimenting using QIF export/import. Surprisingly, the imported data is almost accurate and a few hours of work makes it 100%.
Observation:
The single gross "paycheck" transaction gets imported as three separate transactions:
- Gross pay minus taxes in one split detailed transaction- Employee 401K contribution Transfer
- Employer match Transfer
I wish the QIF export/import could handle the "paycheck" transaction as a single one that it was - to keep it cosmetically clean. But I can understand why this is happening. The good news is that the two Transfers are correctly matched between the bank and 401k accounts and the end results are correct.
Question1: Is there a way/hack to convert these three back into a single "paycheck" transaction, just to keep them cleaner? My guess is that there isn't.
Question2: Any hack/script to also export/import the following: Attachments, Flags, Renaming Rules, Reports? Again, my guess is that manual re-entry is the only option.
And if you have done this before, any tip is appreciated. TIA
QWin Deluxe R37.37
- QWin Deluxe user since 2010, US subscription on Win11
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr
0
Best Answers
-
All I can say is you have been much luckier than I have ever been, usually with my data file using the standard export/import would yield anything usable. Here is my take on how I have done it:
http://www.quicknperlwiz.com/changetransfers.html
On the paycheck or any split for that matter with a transfer.
The problem stems from Quicken really lousy matching of transfers.
Given that they can't be trusted to be matched correctly there was two approaches to link them back up while importing the "Special handling for transfers", which removes transfer transactions on the "from side" or leaving them in and make the user try to verify each match.
By removing one side of the transfer when the other side is imported it will automatically create a linked transfer to the other account. Why they choose the remove the "from side" instead of the "to side" is a mystery to me. It seems much better to have saved the ones in the split (which is always the "from side"), but that isn't what they did.
Of course, the other alternative is basically impossible for a full data file. No one can make sure/correct all the transfers in a data file. And for Quicken Subscription it isn't an option anyways. Once they changed it so that all the transactions go directly into the register there was no longer a way to even attempt the match. From my testing "Special handling for transfers" is always on in Quicken Subscription.
As for the second question, the QIF format doesn't have any syntax for that other data and as such it will not transfer. Note this is also true for the QXF format that is used to transfer data to the Quicken Mac.Signature:
This is my website: http://www.quicknperlwiz.com/1 -
I haven't done a lot of study on the tax lines. The QIF format does have the tax line numbers in them, so in theory it should work correctly, but note my procedure was always delete all the categories I could before doing the import. My categories don't line up with "new categories", so it much easier to get rid of them up front.
Since the tax line files that Quicken uses haven't changed it ages, I would expect not to have any "differences".
As for what order to do the downloading from the financial institutions, I would most likely do the download after I had QIF import done and data file 100% (or as close it as possible), at least from a transactions standpoint, done. The main reason for that is the fact in Quicken Subscription the QIF import puts transactions with no indication whether they are new are not. When importing from the financial institution you can quite easily see if they are new, matched or not.
Signature:
This is my website: http://www.quicknperlwiz.com/1
Answers
-
All I can say is you have been much luckier than I have ever been, usually with my data file using the standard export/import would yield anything usable. Here is my take on how I have done it:
http://www.quicknperlwiz.com/changetransfers.html
On the paycheck or any split for that matter with a transfer.
The problem stems from Quicken really lousy matching of transfers.
Given that they can't be trusted to be matched correctly there was two approaches to link them back up while importing the "Special handling for transfers", which removes transfer transactions on the "from side" or leaving them in and make the user try to verify each match.
By removing one side of the transfer when the other side is imported it will automatically create a linked transfer to the other account. Why they choose the remove the "from side" instead of the "to side" is a mystery to me. It seems much better to have saved the ones in the split (which is always the "from side"), but that isn't what they did.
Of course, the other alternative is basically impossible for a full data file. No one can make sure/correct all the transfers in a data file. And for Quicken Subscription it isn't an option anyways. Once they changed it so that all the transactions go directly into the register there was no longer a way to even attempt the match. From my testing "Special handling for transfers" is always on in Quicken Subscription.
As for the second question, the QIF format doesn't have any syntax for that other data and as such it will not transfer. Note this is also true for the QXF format that is used to transfer data to the Quicken Mac.Signature:
This is my website: http://www.quicknperlwiz.com/1 -
If you haven'talready, you may want to review: http://www.quicknperlwiz.com/changetransfers.html0
-
Thank you both @Chris_QPW and @SherlockAs always excellent suggestions including the writeup on your site. Given that I am testing right now, I decided to try both approaches: vanilla QIF import and ChangeTransfers.exe. Since my export/import is 99.99% accurate, the result without using the ChangeTransfers.exe was a better one. Which is in line with what you wrote on your site as well: "I would advise anyone that is trying to create a new data file from a QIF file, to first try it with the transfers in the QIF file..."First thing I did was to prepare my existing file before generating the QIF exports by ensuring that everything was reconciled. Skipping many details, I finally imported the transactions into the new file. So what were the small issues I found:* About 20 duplicates in the investment accounts which were easily detectable (they were not reconciled) - sort by column and delete.* About 30+ transactions again in the investment accounts where all cash Deposits had switched to Misc Income! Again easy to detect since they were not reconciled either but I had to manually correct them.* And the paycheck observation in my OP, which is not an issue, just not as clean. And I'll just let it be as it will take too long to delete and recreate with no real benefit.Very lucky since it took about 1 hour to get my transactions corrected. Still testing, I had to delete and recreate a few accounts and I also need to recreate a few reports to compare old and new data files. Once I have a good confidence as well as the proper order of steps, I will repeat everything in a final new data file from scratch. FYI my test file is third of the size which I am sure lack of Attachments, Flags, Renaming Rules, Reports, Planner, etc. and a clean database are the reasons. Cheers.
- QWin Deluxe user since 2010, US subscription on Win11
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr0 -
Certainly if you can get away without changing the transfers to regular categories that great.
BTW Quicken's Subscription putting transaction directly into the register can be a plus in the sense that it Quicken 2017 and below you had to go into every non-investment accounts and accept the transactions. I have 138 accounts so that takes a while. I say non-investment accounts because if I remember correctly in the investment accounts they went right into the register too even in the older versions, which is one reason the "verify transferer matching" wasn't really even possible.
Note that the link back to the posing in this forum has a long discussion about what things will not transfer. And I forget all of what was mentioned, but thought I would mention that the price histories are only exported for securities that have symbols. So if you have securities that don't have symbols you have to give them some fake symbol to get them to export.
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
To Chris _QPW:
Your memory is failing you (LOL!). Investment transactions in older Quicken versions (pre-subscription) NEVER were automatically added to the register. They were always put into the Downloaded Transactions pane below the register awaiting them to be accepted into the register, either individually or ACCEPT ALL.
Even now, with Quicken Windows subscription and automatic entry into the register turned OFF...you still have to accept transactions from the Downloaded Transactions pane into the register.
I run both Quicken Windows and Quicken Mac. While I like the "automatically entered into the register" workflow in Quicken Mac (there is no option to turn that off), I have played with the same option in Quicken Windows...and for me I'm always going back to Preferences and wind up unchecking "automatic entry into the register" as the workflow in Quicken Windows just seems unnatural for me.
My brain is programmed to do it the same way I've done it since Quicken started downloading transactions. And I just can't seem to get past that. And believe me...I've tried multiple times to re-train my brain.0 -
@Chris_QPW , I just read the other 2-page older post which you contributed to (I know your writing style by now!). Learned a few new things and order of steps but now have two new questions for you:* Imported categories seem to have a problem with tax lines, right? So, would you suggest not importing the categories at all and instead recreate the custom ones manually?* Which one is a better order to download the transactions? (a) Import transactions from the QIF first, then download them from the FI and delete them. or (b) reverse: Download the transactions from the FIs first and delete them, then QIF import mine?[adding] I think (b) is easier since allows me to easily delete everything prior to the last download date of the old fie.Thanks.- QWin Deluxe user since 2010, US subscription on Win11
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr0 -
I haven't done a lot of study on the tax lines. The QIF format does have the tax line numbers in them, so in theory it should work correctly, but note my procedure was always delete all the categories I could before doing the import. My categories don't line up with "new categories", so it much easier to get rid of them up front.
Since the tax line files that Quicken uses haven't changed it ages, I would expect not to have any "differences".
As for what order to do the downloading from the financial institutions, I would most likely do the download after I had QIF import done and data file 100% (or as close it as possible), at least from a transactions standpoint, done. The main reason for that is the fact in Quicken Subscription the QIF import puts transactions with no indication whether they are new are not. When importing from the financial institution you can quite easily see if they are new, matched or not.
Signature:
This is my website: http://www.quicknperlwiz.com/1 -
I'd like to share with you that I found the solution to my Question1 in my original post. Hopefully it will help someone in the future.
* I created two QIF export files: ALL_Transactions.QIF and Bank_A_Transactions.QIF. Bank_A being the account that my paychecks get deposited to.
* In the new data file, first I took care of setting up everything else before transactions - such as Categories, memorized payees, renaming rules, etc.
* Looking at my old data file, I counted that Bank_A had 200 transactions total: 150 regular and 50 split paychecks (example)
* First I imported ALL_Transactions.QIF into the new data file with "Special handling for transfers" selected.Focusing only on the two accounts: Bank_A and 401K
* Now Bank_A had 300 transactions: 150 regular, 50 split paychecks, 50 employee contributions, 50 employer match contributions
* 401K account also had the matched transactions
* Although all correct, it would be nice to bring Bank_A back to 200
How did I do this?* Now I deleted ALL transactions in Bank_A - everything. Naturally that deleted the matching transactions in the 401K as well, as it should.
* Then I imported the Bank_A_Transactions.QIF file with "Special handling for transfers" unselected. Yes, unselected.
* This time my Bank_A had 200 transactions, 150 regular and 50 split paychecks.
Those split paycheck transactions this time ALSO contained the two contributions including the referencing 401K account as the category.
* The 401K account had all the matching deposits.
Everything was properly balanced and accurate. Mission accomplished.- QWin Deluxe user since 2010, US subscription on Win11
- I don't use Cloud Sync, Mobile & Web, Bill Pay/Mgr1 -
@BK good job. And interesting since the last time I tested Quicken Subscription it was ignoring the "Special handling for transfers" setting.Signature:
This is my website: http://www.quicknperlwiz.com/0
This discussion has been closed.