How to move memo field to category field in many transactions
Comments
-
Given my strong desire for an explanation, perhaps there is one for what I'm seeing in my backups and saved desktop files. And I never purposely enabled online Sync. In fact, I am always trying to keep it off.
- I started using Q in 2000. Added my investment info going back to 1987.
- 2008 is the first complete desktop file 'set' I have. At that time, the desktop file was a set of 5 files, one of which was the QDF file. This appears to be a copy of my data from the Q07 version.
- Everything seems fine until end of 2017. In the QDF-backup file I have, taken on 1/10/2018, the categories for my main checking account are fine.
- In my backup for 2018, taken on 12/31/2018, the restored file looks like someone ran an EYC (End of Year Copy) with the data starting on 1/1/2010. What is left of the data prior to 2010 are the transactions that include a transfer to another account. I certainly don't remember doing that. In all subsequent years this is the case.
- Then in my 2021 backup, taken on 1/1/2022, suddenly the removed transactions are back but the categories are mostly missing, many have been moved to the Memo field. I have no idea in the world how I could have done anything like this. This has persisted to the present.
Since I want to keep my whole history in one file, I never run an EYC. Certainly, I don't think I would backup such a file. But I have no idea, how that can occur. That looks like what I'm attempting to do is to merge pre-2018 data with my current file. In this case this 'merge' occurred prior to the 2021 backup. but this time get the Categories correct.
That's my interpretation. I welcome any alternative explanation. If I did do this, I'd love to know how. Everything I've tried so far hasn't worked. It made a bigger mess of the file.
0 -
@Chris_QPW excellent explanation of a Quicken File vs Quicken Dataset!
I suppose that the moral of the story is i) Copy is preferred over Restore when one needs an older data file of the same name (to avoid creating hidden datasets) and ii) once Copied, resync and you should be good to go.
The Quicken process, at least to me, seems confusing and unnecessary. For example, why not simply sync the current Quicken File to the Quicken Dataset and delete everything else. This approach would be simple to understand, the Quicken Dataset always reflects the Quicken Date File.
0 -
Given my strong desire for an explanation, perhaps there is one for what I'm seeing in my backups and saved desktop files.
I hear 'ya! I too need explanations!
0 -
I suppose that the moral of the story is i) Copy is preferred over Restore when one needs an older data file of the same name
And for me, that the rub. Since 2017, all I have kept is a yearly BACKUP. Until now, I've almost never needed to go to a backup since all my data was (supposedly) in my main file. The backups were just protection. Boy, now am I glad I did!! This is one of the few times I really really needed a very old backup.
I would have really been p***ed, if I had lost the data before 2010.
0 -
@DoctorBrown I don't have any magic for your original problem, or I would have suggested it long ago. Whereas you could get deleted transactions back by exporting transactions in QIF format (which will have the category in it if it is there) and import that back in, it isn't going to "merge" with the existing data, it will be put in as duplicate transactions. And most likely the transfers will be mucked up too.
A bit of history on this. First off on the multiple files that made up the "data file" in Quicken 2009 and below.
Believe it or not they are still there. Starting in Quicken 2010 what they did is take those files an put them in a compressed file. 7-Zip (which is free) and open) that compressed file (the QDF file), put don't try to write anything to it.
The .QDF above is the original .QDF file that you would have seen in Quicken 2009 and below they just would have been Current.QDF, Current.QEL, …
On the subject of "matching/merging" existing transactions with QIF imports. In Quicken Subscription they removed all processing of QIF imports, with the exception of automatic transfer detection for some reason. The transactions just go straight into the register. And this isn't even "automatic transaction entry mode". With "automatic transaction entry mode" there would be matching to existing transactions, and they would have the status of New Match or if no match New. The transactions imported by QIF will have no status (This the "Status/blue dot column" that I'm talking about not the Clr field). What's more since the QIF format does have the cleared status in format, so if you exported transactions that were reconciled, they will be put in the register as reconciled.
In Quicken 2017 and below they did try to match the QIF importing of transaction to existing transactions but did a pretty bad job of it. Note that while the QFX format has unique Ids that Quicken uses to identify transactions with, the QIF format doesn't. I have used other personal finance programs, and they did a much better job of this, but they also depended on this format more to get customers from other companies like Quicken. Whereas Quicken has been trying to get rid of this format since 2004.
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
Believe it or not they are still there. Starting in Quicken 2010 what they did is take those files an put them in a compressed file. 7-Zip
I'm not surprised. All the MS Office formats are also a zip. If you want to customize the ribbon you need to open and manipulate it. I've done that, well with the Excel file at least.
0 -
The Quicken process, at least to me, seems confusing and unnecessary. For example, why not simply sync the current Quicken File to the Quicken Dataset and delete everything else.
Because Quicken Windows inherited the "genes" of Intuit?
The one constant between how Intuit did things and hold the Windows developers/marketing works is that they feel they shouldn't tell you what they are doing. They give the absolute least amount of information possible. Quicken Mac at least in the past had a manager that was more open, but it looks like that the new manager isn't following that lead and is doing what the company wants and not given explanations.
So, why did they retain the old dataset which is totally useless because it is now not connected to any data file?
Like I said in the past some people were told "legal reasons", and now we have "catch people trying to abuse license". Frankly I'm more inclined to believe the later, it is the way Quicken Inc (and Intuit before them) "thinks".
In the security industry, there is open-source security where you purposely show how it is being secured so that people can see if they can see any way to break in and get that fixed. But before that, there was and still is "security by obscurity" and that is definitely the way Quicken Inc works. They aren't about to tell you anything about how they do things because they are too afraid that it will be used against them.
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
….. it isn't going to "merge" with the existing data, it will be put in as duplicate transactions. And most likely the transfers will be mucked up too.
Those are exactly the issues I'm running into. And I have to think about what these imported transactions are going to do to the Manually Match list. That is another holy mess. I'm trying both the QIF and QFX routes and stripping all the transactions and other data from the file that aren't related to the stuff I want. Of course, each attempt will likely create a new dataset….
Re Syncing: Trying to sync a very complex dataset across multiple devices must be a total nightmare. And Quicken has issues, as I've seen, just keeping the desktop file intact. Hence my reluctance to do Web sync, let alone the security picture. If I wasn't forced to have a Quicken online account, I certainly wouldn't let them have any access to my local files, especially with what I've just encountered.
0 -
Note that QIF imports will not affect the Manual Match list, but QFX will.
And yeah, all this "syncing" is a nightmare!
Quicken Mac users seem to be a lot better off in this though.
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
Note that QIF imports will not affect the Manual Match list, but QFX will.
Thanks! Good to know. Are there any 'rules' that would help me or that I should avoid?
0 -
@Chris_QPW appreciated and will add that one of the (likely) reasons that Quicken can do this is that they have little real competition. That is, for all of their warts, this being one of them, they dominate teh space.
0 -
There was a post in other thread that reminded me of something that might be tried, but how much trouble it will be or if it will work at all I'm not sure.
I do believe that the QXF (Quicken Transfer format) does have "merging". I usually forget about it because it doesn't work for investment accounts, but some people have stated that for non-investment accounts it is pretty good.
The BIG problem with it though for this use case is that it all or none. So, to merge only some accounts one would have to delete all the other accounts in the file before doing the QXF export. In the case of my data file that has 134 account in it, I wouldn't even try it.
BTW I'm not sure if this is new or I just missed it before but I just brought the Quicken About (Shift+Ctrl) information and what to I see?
And here is a different data file:
QCS by the way stands for Quicken Connection Services, but on might also just call it Quicken Cloud Services (the program on the server handling the Quicken Cloud dataset). You will notice that the QCS Id didn't change. I take that to mean my "Quicken Id" is really that number as far as the server is concerned. There has to be a "backend" number like this because in fact you can change the "Quicken Id" which is your email address anytime you like.
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
So, to merge only some accounts one would have to delete all the other accounts in the file before doing the QXF export.
I have about 20 or 30 accounts (some hidden) and that's the approach I'm taking. In my 2010 file (which has fewer accounts), I'm deleting all Investment info, deleting all accounts that are not transferring to or from the banking accounts and stripping all the transactions that occurred after 1/1/2010. That should strip out most everything that can get in the way. Oh, and I have to recategorize the transfers so they don't look like transfers [Checking One] change to ToCheckingOne. That way it will be relatively easy to reconnect things. And every time I try, I find something else gets in the way. So more copying, editing and more saving and deleting files that don't work. So no wonder I had so many datasets.
Then the latest thing I hit just before I got clobbered was QFX return errors "unable to add category xxxx.' got at ton of those errors. And some other error related to categories (haven't got that file open now).
0 -
I'm baaaack…… After much trial and error (mostly error) I have discovered some very interesting behavior (to put it mildly). And what I think is serious problem with importing Quicken Transfer Format QFX files. I'm hoping that more knowledgeable members here can help me decipher what I'm seeing so that I can more accurately report what I've discovered in manipulating my files.
My investigation on my backups leads to an inescapably conclusion that I must have created my issues. Sometime in 2018 I must have decided to create an archive copy with the starting date of 1/1/2010. This, of course, removes all non-transfer transactions dated prior to 1/1/2010 from all my Banking accounts. In those accounts that had transfers and splits before 1/1/2010, these transactions remained and the starting balance was adjusted to make the data going forward after 1/1/2010 be correct.
Then sometime in 2021 I must have decided I didn't like that result and attempted to restore the data prior to 1/1/2010 in my main checking account (where the majority of the transfers were occurring. Most of the other accounts from that time where closed or hidden. It appears I tried to use the QFX format to import the data. All the transaction were imported but that's where the categories got messed up.
I have just run a test and here's what I found. I went to my QDATA1.QDF from 2010 that had the correct data into 2010. I then export the data as a Quicken Transfer Format (QFX) file. Then I created a new empty TEST file and imported the QFX data. The following three screenshots show what I saw. The first one is the opening data from the main checking account in the 2010 file migrated to the current Q version. the second is the same data in the TEST file. Notice that the Memo and Category fields reversed in the Test file. The third is from the same account (renamed) in a copy of my current file with the misplaced categories.
Notice that in my current file I've carried forward since 2000, the columns are Payee, Category, Memo. (tag is turned off). The 2010 QDATA1 file has been converted from the Q09 version to the current format. In a newly created file the relevant column order is Payee, Memo, Category the data is in the same columns, but the headers are changed. but It appears to me that the QFX file has imported the data into my file incorrectly.
I would really, really welcome someone to explain to me what's happening and if I have misinterpreted something. I have had some success with the QIF format but it takes a great deal of mucking with the data to get QIF to somewhat correctly import the data I want.
Original data in Checking account from 2010 QDATA1 file:
Checking account in NEW TEST.QDF file after the import of the QFX file. Notice the column order. I don't seem to be able to reorder the columns.
Same checking account in my current QDF file. The column order is the same as the original file from 2010. Oh, and in the Check # column the non-check entries (ATM, Bill Pay, EFT, etc) are also gone.
(this data is old enough that I don't care if the amounts are visible.)
0 -
Deselect this preference in the second data file:
Signature:
This is my website: http://www.quicknperlwiz.com/0 -
I also checked the setting in my current file and 2010 file. They both have that setting deselected.
I guess I'm really wasting my time trying to figure this out. I just want a clean way to merge the data from one file to another. Is that too much to ask?
0
