Expense reports and Home / Overview tab are not footing (Q Mac)

Just Lurking

I've been trying to determine the source of several discrepancies between my expenses in different parts of the Home tab (Overview vs. "Spending over Time" screens) and some of the expense reports. This is on Quicken for Mac Version 6.2.0 (Build 602.38882.100).

I would appreciate any insights or tips to help me hunt down the reasons why I'm seeing so many different values. I've already tried looking (without success) for transactions that could account for the differences between these reports.

Let's take my April expenses as an example. The Home Overview screen has one value for spending:


If I click on "Spending by Category", Quicken opens a new window of transactions. On this window, the spending amounts in the middle match the Home/Overview tab, but the total in the top-right does not match:




Then if I go back to the Home tab and choose the "Spending over Time" view, there is a different value for April, AND the number of transactions (107) does not match the number of transactions (106) from the above screen:


If I then go to the Reports tab and choose a "Category summary by month" report for April, the amount matches the "Spending Over Time" view of the home tab:


However, if I choose a "Payee Summary by Month" report, the total there doesn't match any of the previous views/reports!

Jon

I took a look at those. I also see a difference between the "Spending By Category" and "All Transactions (Spending)" values. For me, the difference is caused by a split transaction which is a pension payment that had taxes withheld as well as an insurance premium. The "Spending By Category" value includes the tax withholding and insurance premium but not the pension income; in other words it only includes the "Spending" parts of the transaction. The "All Transactions (Spending)" value includes the entire transaction, both the income and the expenses. It makes sense to me when I think about it, since the transaction report has to include either all of the transaction or none of it.

(That same pension payment also shows up in the "All Transactions (Income)" screen. I guess since the transaction includes both income & expenses it gets flagged as both.)

I don't know what to say about the Spending Over Time value, for me it matches Spending By Category (at least for this month it does, I don't usually look at the Spending Over Time graph).

The Payee Summary By Month doesn't match the other numbers because it includes all income and expense transactions. In my case, several income transactions (mostly interest payments) were included here but not in any of the Spending totals discussed above. I don't have a problem with this number, it looks correct to me.

Jon

I typed up a reply but I guess I made too many edits & it got thrown into moderation. The short version is that I was able to account for most of the discrepancies you saw except for the Spending Over Time graph which for me matched the Spending By Category value.

Just Lurking

Hi @Jon, thank you so much for testing your Quicken file with these reports. Fortunately I did see at least one version of your lengthier reply before it was put into moderation.

In my case, I don't appear to have any split transactions for April (I looked at the All Transactions view and then sorted by Category to look for transactions with a Category of "Split"), so while that's a great suggestion, that doesn't appear to explain my discrepancies. Also, while I do have some Dividend income / reinvestments during April, that doesn't appear to explain my discrepancies either.

I'm open to any other suggestions you may have. Thank you!

Jon

The only other idea I had was that it might have something to do with the various transaction dates, that you might have transactions that straddled the beginning or ending of the month and that those were being included in some reports but not others. But I see that I have transactions like that & they aren't causing issues for me.

I assume you've also checked to make sure you don't have any uncategorized transactions, or transactions without a payee.

Jon

OK, one thing I see is that if I go into "Hide and Show Accounts" and check "Keep separate" for one of my accounts, it changes the values on the Spending Over Time graph and the Payee Summary By Month report; they no longer include transactions in the "separate" account. But the Spending By Category pie chart on the Home screen is unchanged & still includes those transactions.

Do you have any accounts flagged as "Keep Separate"?

Just Lurking

Hello again @Jon, thank you once more for your suggestions. I checked them all and unfortunately none of them were the cause. I was determined to figure this out, so I exported a few reports / register views to CSV and loaded them up into Google Sheets to do some analysis, and I think I've mostly figured out the discrepancies.

I've written up my findings below, as much for my own benefit as anyone else's who encounters the same issue and stumbles across this. I see three different expense totals depending on the view/report, and I'm calling them Total A, Total B, and Total C.

Identified Discrepancies in Expense Totals

The Home Tab: Spending by Category

Issue #1: This total (Total A) excludes an expense value that is greater than 0. In this case, this is a credit card rebate which I assign as a positive value to an "Expense" category, which I believe to be a valid accounting method as the rebate effectively reduces the cost of my credit card spend.) I consider this to be a bug.

Issue #2: Total A also does not include my vehicle's monthly depreciation. Which is not a big issue (and perhaps it's preferable this way), but I wish I understood the underlying mechanics better. I have an asset account for a vehicle, then a monthly "bill" for a Depreciation Payee and a Depreciation category that is automatically marked as paid from the asset account.

The Home Tab: Spending Over Time bar chart / "All Transactions - Spending"

This one was trickier to figure out. From the Home / Spending Over Time view, hovering on the bar chart for April displays a different expense total (Total B), and 107 transactions. Clicking on April opens an "All Transactions - Spending" window that is filtered on April.

However, this new window contains two different totals, neither of which match. Instead this window shows Total A (previously seen in the Spending by Category view) next to the pie chart, and Total C in the top-right of the register. In addition, this register view has 106 transactions, one fewer than is displayed by hovering on the April chart. After much digging, I figured out how to get all the figures to foot:

Explanation #1: Total C is equal to Total A plus the credit card rebate mentioned above. 
Explanation #2: Total B equals Total C plus the depreciation mentioned above.

I didn't dive into the Payee summary by month report but as you mentioned I believe this report includes income, so naturally that total would be off.

jacobs

@Just Lurking I tried replicating some of your testing, and was going to recommend reporting the first issue as a bug. Except that it seems to work correctly for me!

I created a $100 expense in a category for last month, and verified that it was reflected in the Spending By Category graph and total. Then I created a new transaction with a $25 positive (cash received) value for the same Payee and Category, as would occur with a return or credit. The Spending By Category graph and total reflected the reduction in expense of $25. The total was correct; the category wedge correctly shows $75 for two transactions.

Then it dawned on me: you probably used a category with no other activity, so it was an expense category with a positive value for the month. I tested that by changing my refund amount to a different category not used in the month, and indeed, the total reflects the original $100 expense but not the $25 credit.

Here's the problem I see: On a pie chart, there is no way to show a negative value. So the developers made the decision to exclude expense categories with positive values. If they decided to make the total correct, then the wedges in the piece chart wouldn't add up to the total, because there's no way to depict a negative slice in the chart.

I'm not sure what the right answer is, but I no longer think it's a bug. It's simply an inherent problem in the data representation being attempted by this screen. Perhaps they should add a footnote to say "Any expense category with a positive value is not included in this chart and total." Or perhaps they should change from a pie chart to a bar chart, as is used for the income screen, because a negative value can be expressed on a bar chart.

jacobs

As for the depreciation issue, I concur that an expense in an Asset account is not reflected in the spending graph on the Home screen, but is reflected in the Spending over Time bar chart.

Here's what's interesting. On the Spending Over Time chart, which includes the depreciation expense, click on that bar and Quicken will open an All Transactions window, set for Spending for that month — and it will not include the depreciation expense from the Asset account. If you look at the account filter, though, it shows "All Banking & Investing Accounts". You can pull down that menu to select other options, such as "All Banking Accounts" or "All Investing Accounts" -- but there is no option to include Asset accounts. This filter is apparently what's used for the Home screen Spending chart, which is why your depreciation expense is omitted.

I don't know whether not including Asset or Liability accounts here was an omission or an intentional decision. It's unusual to have spending in an asset account. (In most usage cases, someone would increase or decrease an asset account via a transfer from another account. For instance if you bought something which was an asset, it would be recorded as a transfer from your checking account.) In your case, where you recorded a non-cash expense in the Asset account, one could argue that it shouldn't show up in a Spending report because you didn't actually spend any money. It's not an "Expense" report; it's a "Spending" report, and an accounting entry for depreciation isn't spending. If you create a Report of expenses, or of income and expenses, for last month, the depreciation expense does show up as expected. So I could defend the way Quicken currently works on those grounds.

But one could also argue that you'd expect the spending totals to match across all screens, and that this should be changed to include Asset and Liability accounts. You could create an Idea post to ask for Asset and Liability income and expenses to be included int he Home screen charts for income or spending. I suspect these charts, along with most of the Home screen, are mostly carry-over code from the predecessor Quicken Essentials program, and haven't been revamped much over the years. The developers have tagged an Idea request to make the Home screen user-configurable as "Planned", so you might also want to add a comment about this in that thread.

CTMarkell

i was about to report a bug for something similar -- the Q Mac home page "overview spending by category YTD" pie chart was showing me 1 uncategorized transaction with a positive amount, but when I drilled down into it, zero transactions and zero $ were returned in the pop-up report view.

Searching for that amount in "All transactions" returned nothing as well. Was driving me a bit crazy looking for the orphan/phantom uncategorized transaction.

It turns out the transaction was in an account marked "keep separate", which was a suggestion for a place to check made by "Jon" above, so thank you Jon!

Once i applied a cateogry to that transaction the home page overview no longer shows an uncategorized transaction.

But you are on to something, the home page overview pie chart includes data from accounts kept separate... and the drill downs into that pie chart filter them out (Because they are kept separate!).

That does smell like a bug in the logic of building the dataset for the overview graphs -- it would make sense to exclude separate accounts in a consistent way.

Just Lurking

jacobs said:

Then it dawned on me: you probably used a category with no other activity, so it was an expense category with a positive value for the month. I tested that by changing my refund amount to a different category not used in the month, and indeed, the total reflects the original $100 expense but not the $25 credit.

Here's the problem I see: On a pie chart, there is no way to show a negative value. So the developers made the decision to exclude expense categories with positive values. If they decided to make the total correct, then the wedges in the piece chart wouldn't add up to the total, because there's no way to depict a negative slice in the chart.

Excellent analysis and conclusion @jacobs, thank you. You are exactly right; my "Credit Card Reward" category is infrequently used and not only did it have no other activity this month but also it is always positive.

jacobs said:

Or perhaps they should change from a pie chart to a bar chart, as is used for the income screen, because a negative value can be expressed on a bar chart.

I like this suggestion the best. I don't find Pie Charts that useful, for various reasons, and this issue that we've jointly discovered is just one more reason why perhaps a change to something other than a pie chart would make sense.

CTMarkell also identifies another issue with this part of QMac related to "Separate Accounts" that would be good to correct.

jacobs said:

As for the depreciation issue, I concur that an expense in an Asset account is not reflected in the spending graph on the Home screen, but is reflected in the Spending over Time bar chart.

<trimmed for length>

I don't know whether not including Asset or Liability accounts here was an omission or an intentional decision. It's unusual to have spending in an asset account. 

<trimmed for length>

 So I could defend the way Quicken currently works on those grounds.

But one could also argue that you'd expect the spending totals to match across all screens, and that this should be changed to include Asset and Liability accounts.

Agreed.

jacobs said:

You could create an Idea post to ask for Asset and Liability income and expenses to be included int he Home screen charts for income or spending. I suspect these charts, along with most of the Home screen, are mostly carry-over code from the predecessor Quicken Essentials program, and haven't been revamped much over the years. The developers have tagged an Idea request to make the Home screen user-configurable as "Planned", so you might also want to add a comment about this in that thread.

Thanks for the suggestion. This is not important enough to me (nor do I really think it'll be relevant to enough people) to make an idea post specifically for it, but thank you for linking to the thread about planned work in the Home tab. I will post over there with a link back to this thread. Hopefully that helps increase the visibility on some of these issues / inconsistencies so that Quicken developers keep them in mind when they revamp this part of the app.