How do I automatically assign percent split categorizations to previous transactions?

JohnB4Dawn

I have been using Quicken Canada for Windows for about 20 years. Now I am about to retire I am trying to get a better handle on our expenses. We have been very much a cash transaction household, but over the years I estimate that 70% of our cash transactions are for groceries. I would like to go back to all the previous ATM transactions with the category "Cash", and now split them as 70% Groceries and 30% Miscellaneous. I am able to do this for new transactions that are downloaded with a Memorized Payee ("ATM Withdrawal"), but have exhausted myself trying to find an automated way to reclassify all my previous "Cash" ATM transactions - I have over 1400 so a manual update is not an option. While this is far from completely accurate it will give me a better estimate for what we're spending on groceries.

I usually can solve my issues with Quicken by working hard to find creative solutions but this one has me completely stumped — I've tried everything. (BTW This is my first post to the Community Forum in at least 20 years!).

Many thanks!

Chris_QPW

I don't think this can be done in Quicken.

On option might be to run a report and either manually do the math on that one category or export it to Excel and do it there.

Chris_QPW

Does the Canadian version of Quicken have the Lifetime planner?

JohnB4Dawn

Thank you so much Chris, for your quick response. I previously did what you suggested and this gives me a snapshot of my grocery spending over 10 years (or whatever period used in the report), but I was hoping to have it adjusted in particular for all the cash entries over the last year so they are included in the real time spending run rate estimates.

In a worst case I could update these 68 entries manually but I was REALLY hoping there would be an easier way. There are enough entries to make this extremely tedious.

And unfortunately the Canadian edition does not seem to have the Lifetime planner. There is a "Retirement Calculator" but it's really just a variant of an amortization table.

Thanks again!

Arctic Hare

Export one or more reports to MS Excel and build your projection model in Excel.

JohnB4Dawn

Thanks, I'm doing that now, but it's not a satisfying solution. I thought I'd probe the brains of the Quicken geniuses in the community to see if there might be another way, but it's becoming clear that I can't do this in Quicken unfortunately. Many thanks for your response.

Chris_QPW

Just so you know, after I asked about if the Canadian version had the Lifetime planner, I realized it really doesn't apply to this situation exactly. It only shows from today onward (it would be greater of tool if it could do the past, but it doesn't"). What it does for expenses is help you figure out what your current expenses would be "one number", and then use that in the future prediction of how long your savings/investments and such will last.

JohnB4Dawn

I wanted to thank Chris and Arctic Hare for responding to my note, some of the suggestions for approach, have helped me resolve the issue. It's inelegant, and somewhat primitive but it does give me what I originally wanted.

I generated a "Cash" transaction report and converted it to Excel. I then duplicated all the transactions. I changed the category for half the transactions to "Groceries" calculating 70% of the total amount I wanted to split, and using 'Misc. Cash" as a new category for the other half with 30% of the original amount. I then convert the Excel CSV to a .QIF file and imported it back into Quicken. This approach unfortunately doubles the number of transactions but it does give me a more accurate estimate of our grocery spending over 10 years for my budgeting estimates. Thanks again for your help trying to solve my problem.

Arctic Hare

You might find that the model you have built is too strongly anchored to the underlying assumption that the data from the last 10 years will accurately predict you spending going forward. There is inflation, which is difficult to predict, your eating preference may evolve as you age, and the quantity you eat will also evolve over time. I'll venture that you have an overstated sense of accuracy of the model - as far as predicting the future goes.

JohnB4Dawn

You are correct. I wanted to see if there were any trends annually, and of course see how inflation (and other factors) has affected our food spending over the years. The hope was that by collecting annual data, plotting it to see if a trend that can be projected forward and then using for it for high and low estimates into the future. I didn't want to get into the detail when I posted my problem because I thought it might distract from what I was trying to do in Quicken but I was hoping to get some rough idea about our food spending in the past which might provide some guesstimates for the category in the future. Obviously the data for the last 12 months will be waited the heaviest, but I think estimating what we've spent on food over the years may be helpful forecasting the expense in the future (it may not!). I completely get this is may be inaccurate but it is better than what I have now which is nothing!

Thank you for your cautions, a second opinion is always valuable. I don't have any data now and it will be interesting if I can get anything useful from it. I do have an ace up my sleeve though, and that is a daughter who is a data scientist who may be helpful normalizing it once I have data to show her. Ten plus years may provide enough information to at least to peke her interest :-).

Chris_QPW

One of the things I have toyed with over the years is what is our personal inflation. One of the main numbers that is used in the Lifetime planner is what the person puts in for inflation since it is what everything is multiplied by to find future dollar values.

So, what to put in there? A lot of people think that you should put in something like the CPI number. But that number is almost guaranteed to be wrong for any given person (and probably way low).

So, I have tried to do basically what you have and tried to find my personal inflation number, but not just for food, for all my expenses. I have failed every time I have attempted that. Maybe I might have succeeded if I just used Groceries and Dining (I combine them because we might eat out more often in one year than another, which will influence how much Groceries we buy.), but that is just one part of our overall expenses. And frankly, even in this I have found that as @Arctic Hare said, it changes enough that trying to tease out inflation is "questionable", especially since recently inflation has been so high in comparison to years farther back. And you are starting with an assumption of 70% of a number. I would predict that your assumption to what really happened will be so different that any inflation number you calculate will be quite inaccurate.

And groceries are easier than say fuel to predict. But the major one is a lifestyle change, and retirement tends to be one of the biggest ones.

So, like I try to tell people using the Lifetime Planner, at most all you can consider it is a "ballpark figure". It isn't going to predict the future for you, and ensure that everything is nice and predictable, no matter how we would like it to be.

As such, my suggestion would be to simply use your expenses over the last 12 months and take a guess at the inflation number if you are calculating it for future expenses.

I would note also one of the things I think is a big shortcoming in the Lifetime Planner that someone using a spreadsheet should avoid repeating. It only shows from today till when you say you are going to die. It has no history. One should keep the history in some way. The reason is that without the history people think they are great at predicting because they don't have anything to check against. In other words, say I setup saying that inflation is 3%. And then go a year. When I bring it up, it will now show what I currently have. Unless I save a copy of the Quicken data file from a year ago, I will have nothing to compare the new "current" to the "expected current" for this date. All Quicken's Lifetime Planner is going to tell you is that if your assumptions are right, then here is how much you will have at any given year in the future, it won't tell you that your assumptions are wrong. Note the other major number that is like this is return on investments.

JohnB4Dawn

Sigh …. it is so frustrating hearing about this terrific tool, (the Lifetime Planner), that as a Canadian we don't have access to, and I'm not sure why. It would be wonderful to hear if there are any plans to enable this capability in Quicken Canada in the future.

Chris_QPW

Well for what it is worth, the Quicken Mac people have been asking to get it for years too.

But if I didn't make it clear, I personally find its shortcomings large enough that I don't use it. I think the major reason people do tend to like it is because Quicken has all their data. In my opinion the reason that is the stumbling block for the online tools. Whereas Quicken has the data, it has programmers/a company that aren't experts in this area. And when you get on sites that have the experts, they have to deal with the fact that they have to work with very limited data because there is no way they can keep anyone's attention long enough to entry their detailed data. So, they work with very limited data.

Quicken has one inflation number, and one investment return number for before retirement, and one for after. Notice I didn't say anything about different numbers for things that might happen in retirement, like on the US side when a person has to start taking out money out of their retirement (known as RMD). And there isn't any way to tell it what accounts to take out when. It makes a guess at which is the best. Which in my case the assumption is wrong. With the different tax ramification for Canadian that kind of thing might be easier, or not, I don't know.

At any rate I don't think anyone has posted an Idea for this for Canadian users, at least not in a long time. That is how Quicken Inc takes suggestions on implementing features. You can do that by going to the Home webpage and selecting New Post → New Idea.