Why does the Quicken 2020 R23.17/27.1.23.17 report function calculate wrong sums?

Dave Dickerson

I run a report monthly that adds up a long list of dollar values. Since I upgraded to the latest version of Quicken the report does not total the values correctly (it's off by about a minus six percent). When I copy the report into Excel the totals are correct. Is this a known issue and when will it be fixed?

Rocket J Squirrel

Not enough info. What version of Quicken? What exact specific report? Is it a report you've customized & saved, or a standard report? Can you provide a screen shot or 2 showing the discrepancy?

Dave Dickerson

Version and release numbers were stated in the heading which is repeated here:

Why does the Quicken 2020 R23.17/27.1.23.17 report function calculate wrong sums?

I see this problem in both standard and customized reports. I'm totaling dollar values and the report has about 3725 line items going back to 2004. All the items are listed. There are no line breaks or empty line items in the report. The Quicken totals are wrong and Excel totals are correct.

Rocket J Squirrel

Sorry, by the time I scrolled down to your question, the title with the version number scrolled off the screen.

Please specify a particular report. No one here is going to go through every report trying to reproduce your issue.

Dave Dickerson

Investment Income report both the standard one and any one of the several I've made custom that essentially do the same thing but with different time frames. I'm suspecting the summing error is related to the number of line items. Shorter reports add correctly. Longer ones (several thousand line items) don't.

Dave Dickerson

I'll add that I first noticed the sum errors with Quicken 2017 on a Windows 7 PC but only in one report I used at that time. I'm now using Quicken 2020 on a Window 10 PC so some of the custom reports have gone thru the upgrade process and others I created new today. Both have the same sum problem. I verified the issue is also in the standard report so it's starting to smell a bit like a programming glitch.

q_lurker

a)  The Investment Income report presents info as category sums, not as individual transactions.  If you drill down on one of those categories (like _DivInc), then you get a second report that is really an Investment Transaction report for that one category.  That report could have 3,000+ line items (transactions) to add together.  Please clarify.

If it is that drill-down report, I suggest you try the equivalent Investment Transactions report to confirm the same behavior (summation).  You could also go to a Banking Transactions report customized to include the applicable investment accounts to see what sum that report comes to.

I am skeptical of the number of lines conjecture.  I have seen cases where the drill-down report summation is different than the master report summation, because different customization came into play.  That doesn't seem to be the case you are seeing.    

Dave Dickerson

I just tried your suggestion using the Investment Transactions standard report and had high hopes but using the same date range/account/category/security selection criteria I get a slightly higher total than previously but still short by about 5%. When I copied the transaction detail from that report over to Excel and sum it, I get the correct total.

Dave Dickerson

Oh, and to clarify, yes, I did drill down to the category details in the investment Income report and summed each category separately in Excel and got the correct sum while the category sums in the detail section of the report were wrong.

Rocket J Squirrel

I have been trying to reproduce this, but cannot. My report of every _DivInc transaction since the beginning of time has 2655 lines. When I select the amounts of the line items in Excel, the total displayed in the Excel footer matches the total from the Quicken report to the penny.

Dave Dickerson

Excel screen shot attached. Note I'm using six different categories, some canned some custom.

Jim_Harman

I suspect the errors are related to specific transactions in your file. Have you validated the file without errors?
You might also try limiting the date range until there are no errors then expanding until you see an error to find a problem transaction, then see what is unusual about it, a split perhaps.

Jim_Harman

Also what are those non underscore categories? Are you recording income in multiple ways?

Tom Young

I did the same as @Rocket J Squirrel for all Accounts for all time and in my case - almost 4,200 transactions - there was a difference between the Quicken detail and the Excel summation amounting to .316511....%, with the Quicken amount overstated.

I found that the discrepancy came down to 2 transaction where the Quicken report writer had, for whatever reason, slipped the dollar amount one column to the right in the Excel export, omitting them from the Excel summation total.

You might see if that's the case for you too.

Dave Dickerson

Over the years, for various reasons, I created those non-underscore categories for recording income in multiple ways. I do have a few old securities that split but those did not total anywhere near the amounts in error. I may try some of your suggestions but I don't have time right now with EOM and EOY events looming. After the new year I'll have investigate longer and deeper.

I appreciate everyone's willingness to help. Thank you. It's frustrating to not be able to reply of sums from within Quicken reports. I'm now finding errors in a simple portfolio balance report with just a couple of dozen securities in the list, probably from the way Quicken sums categories that q_lurker mentioned. In the meantime I'm running all summed reports thru Excel to get correct totals.

Dave Dickerson

Tom Young - I'll inspect my transaction detail after year end and I'll keep an eye out for slipped decimal points. Hopefully, an accumulated $11,708, 6% error will be a bit easier to find.

Dave Dickerson

Oops. Slipped column not decimal point. I don't think that'll be my issue, though, since I'm only summing one column are there are no blank fields.

q_lurker

Tom Young said:

I did the same as @Rocket J Squirrel for all Accounts for all time and in my case - almost 4,200 transactions - there was a difference between the Quicken detail and the Excel summation amounting to .316511....%, with the Quicken amount overstated.

I found that the discrepancy came down to 2 transaction where the Quicken report writer had, for whatever reason, slipped the dollar amount one column to the right in the Excel export, omitting them from the Excel summation total.

You might see if that's the case for you too.

How had you (both @Tom Young and @Dave Dickerson)  transferred the report to Excel - via clipboard paste or print to file?  I can imagine the print to file / import getting one line off by one column.  I have not seen that happen (as best I can recall) with the pasting from the clipboard pathway. 

@Dave Dickerson -- just to be clear, the type of discrepancy I have seen has been between the higher level Investment Income report and the drilled-down transaction detail.  The transaction detail info might have more or less than the summary data because, for some reason, that drill-down report came in with different customization settings than the higher level report had used.  It is something to look at, but may just as well be a red herring.

I would be breaking your 6 selected categories down addressing them one by one looking for the discrepancy.    

Rocket J Squirrel

q_lurker said:

How had you (both @Tom Young and @Dave Dickerson)  transferred the report to Excel - via clipboard paste or print to file?  I can imagine the print to file / import getting one line off by one column.  I have not seen that happen (as best I can recall) with the pasting from the clipboard pathway.

I'm not those guys, but I use the Export to Excel Workbook command in the report's tool bar.

Dave Dickerson

Excel transfer via clipboard/paste. I understand what you're saying about the higher-level category totals and the drill-down detail totals but even so, those totals should match within a couple of cents allowing for some typical rounding-type errors. I'll do the per-category sub-totaling when I have the time, probably January. Thanks for your help.

Tom Young

I used the copy to clipboard process.  The highlighted transactions are how they showed up in Excel, with the transactions immediately before and after those two oddballs:

How the two transactions showed up in the transaction list:

@q_lurker

"just to be clear, the type of discrepancy I have seen has been between the higher level Investment Income report and the drilled-down transaction detail.  The transaction detail info might have more or less than the summary data because, for some reason, that drill-down report came in with different customization settings than the higher level report had used."

I'm not sure I understand this.  I'd expect that a new "out of the box" higher level Investment Income report and the drilled-down transaction detail would agree.  Is it possible to take an Investment Income report, drill down, modify the drilled-down report, pop back up to the the higher level report and save it, with a "saved" disconnect between the summary and the detail?

q_lurker

@Tom Young "How the two transactions showed up in the transaction list: ... "

How did the two transactions appear in the Report that was being copied?

I get that (for the first case), your memo said "$0.97" dollars/share as the dividend value leading to the $7301.67 total dividend reinvested to buy 172.169 shares @ $42.4099/share.  The 42.4099, 172.169 and 7301.67 are all one column farther right than they should be.  I am wondering what is was in the report that pushed them over.  Something in the security name?  In the memo field?  

I can't document a specific on the drill-down discrepancy -- at least not off the top.  It has been brought up in the forum questions from time to time.  The times I have seen it have been without edits to either report customization.  I may have associated it at one point to how the "No security (includes cash" security was handled in some reports.  

Tom Young

Both appeared in an entirely regular fashion.  Here's the first one: