Quicken 2018 Deluxe Portfolio Value Incorrect
AbbyKay
Member ✭✭
Quicken 2018 Deluxe R10.11, Build 27.1.10.11 on Windows 10 Pro. I run a Portfolio Value report, subtotal by account, export to excel. I recalculate Portfolio value in excel by summing each account total (as Quicken exported). Quicken is 10% high. I tried to troubleshoot by recreating the math in excel starting with shares x price from the Quicken export data. Shares x price match Quicken. Sum the in each account in excel Account totals match Quicken. But, sum of account values in excel is off the same 10%. Any ideas?
0
Comments
-
I just tried to duplicate what I think you said you did:
- Ran a Portfolio Value Report
- Exported to Excel
- In a cell a couple of columns to the right of the "Balance" column (column "I") I entered the formula +I9. That was the cell with the first "Total" for an Account as reported by Quicken.
- I then copied that same formula down the same column to each "Total" line for each Account included in the report
- I then summed all of the totals
Perhaps you've made some error in in Excel entries?
0 -
Does your investment accounts' total of Cash Balance + cash rolled over into a Money Management Mutual Fund match the brokerage statements?
Have you reconciled your investment accounts?
0 -
Thanks for helping me.
Yes, I reconciled my accounts. And, I double checked my excel calcs. I am without my computer for a couple of days, but will try running the report with no sub totals and the do it account by account to see if I can isolate the problem. BTW, for one brokerage account, Quicken showed the cash balance twice - once as the first “position” and again as the last. But, it only counted it once in the total for that account. Strange.
I will update you Thursday.0 -
> You said: " I run a Portfolio Value report" andAbbyKay said:Thanks for helping me.
Yes, I reconciled my accounts. And, I double checked my excel calcs. I am without my computer for a couple of days, but will try running the report with no sub totals and the do it account by account to see if I can isolate the problem. BTW, for one brokerage account, Quicken showed the cash balance twice - once as the first “position” and again as the last. But, it only counted it once in the total for that account. Strange.
I will update you Thursday.
"Quicken showed the cash balance twice - once as the first “position” and again as the last"
IIRC, there was a bug like that in earlier versions of Quicken that has since been fixed.
Are you reusing a report created and saved in an earlier version of Quicken?
I just ran a Portfolio Value report on my Q 2018 R 10.11 and subtotaled it by account. The Cash Balance amount is shown only once, as "- Cash -", as the last line listed before the subtotal is printed.
0 -
OK - I have some new clues based on questions you've asked. Thanks again.:
1. Reported Cash position appears to be the source of the problem. Depending on how I subtotal, I get a duplicate cash line in one of the subtotal categories (one (but not all??) of the accounts if subtotal by account, "Cash" if I subtotal by security type, "Cash" if I subtotal by Asset Class"). In subtotal by account, the duplicate cash line is shown, but not included in the reported subtotal for that account. But it IS included in the Portfolio Report Total. For the other two versions - same thing. The Cash category has a duplicate cash line. The duplicate is not included in the reported Cash total but it IS included in the Portfolio Report Total.
2. It definitely matters if I run the report for a date that is not the last true reconcile date. Example - I had reconciled my brokerage statements as of 7/31/18, but initially ran the Portfolio Value report on 8/7/18. Two issues here - same "cash" reporting issue as described above. Plus, each version of the report gives me a slightly different Total Portfolio Value. Even if I manually correct for the duplicate cash entries, I get slightly different results. Small enough that I don't care, but more than a few dollars.
3. I did not run this report from a previously saved Portfolio Report. This was "fresh" from the Quicken Report link on the toolbar.
So, I know to only believe the Portfolio Value report if I run it at the end of the month and I had reconciled and if I exclude duplicate cash lines from the reported Total Portfolio Value.
Someone reported that this duplicate cash issue was fixed a while ago. I am wondering if it crept back into the latest version of Quicken?
Next, I'm going to analyze the Net Worth reports to see if they are accurate. That is a number I track regularly and want to ensure is correct.
0 -
Good news. I just ran a 7-31-18 Net Worth Report and compared the "Investments" section to the Portfolio Value Report. The reported totals for each account in the two reports match. The reported totals of all the two reports match. I verified that the sum of the investment accounts in the Net Worth report is reported correctly in Quicken. So, it appears that the cash reporting bug (I'll call it a bug for now) in the Portfolio Value Report is not affecting the Net Worth Report.0
-
What you are seeing with the Portfolio Value report certainly seems strange. When I try what you have described, everything looks OK. The total at the bottom of the Balance column is the same as the number next to Investing in the Accounts bar +/- a few cents, regardless of how I subtotal the report.
Could you check a couple of things, just to be sure?
-- In your Security List, is there a security called Cash? - There shouldn't be, and this might lead to some confusion. The automatically generated cash entry in the reports is "-Cash-" with the dashes.
-- Click the gear at the top of the Account bar and make sure "Show current Balance" is selected. If you select "Show ending balance" the Account bar numbers will include future-dated transactions that are not in the report.
-- If you are downloading security quotes, of course the report will reflect the latest security prices and will not match an earlier statement.
-- If you have money market funds set to download quotes, look at the price history for those funds and make sure all the entries are 1.00 and there are no zeros. Since the MMF price is always 1.00, it is best to disable downloading of quotes for these funds.QWin Premier subscription0 -
Responses to your questions below:
-- In your Security List, is there a security called Cash? - There shouldn't be, and this might lead to some confusion. The automatically generated cash entry in the reports is "-Cash-" with the dashes.
No security called Cash. The Portfolio reports only show -Cash- with the dashes.
BTW, in the Portfolio Value report, customize, securities, I unchecked "No Security (includes Cash). That omits the -Cash- security, and the report total is correct.
-- Click the gear at the top of the Account bar and make sure "Show current Balance" is selected. If you select "Show ending balance" the Account bar numbers will include future-dated transactions that are not in the report.
The "Show current Balance" box was checked.
-- If you are downloading security quotes, of course the report will reflect the latest security prices and will not match an earlier statement.
I ran the report for 7-31-18 after running 1-step update and reconciling the statements. The issue isn't that the report doesn't match brokerage statements, the issue is the report includes duplicate -Cash- line items in the total Portfolio Value. (But not for the account which also has the duplicate -Cash- lines when I subtotal by account).
-- If you have money market funds set to download quotes, look at the price history for those funds and make sure all the entries are 1.00 and there are no zeros. Since the MMF price is always 1.00, it is best to disable downloading of quotes for these funds.
I am not downloading money market funds. All cash equivalents are treated as cash.
Please continue to ping me with questions. I appreciate the help. I've been using Quicken for what seems like at least 20 years and other than an issue with the Tax report being wrong several years ago (you had to select the dates of the tax year, not "Last Year" when running the report), I've never had a problem!
0 -
-- In your Security List, is there a security called Cash? - There shouldn't be, and this might lead to some confusion. The automatically generated cash entry in the reports is "-Cash-" with the dashes.
No security called Cash. Only -Cash- with the dashes.
-- Click the gear at the top of the Account bar and make sure "Show current Balance" is selected. If you select "Show ending balance" the Account bar numbers will include future-dated transactions that are not in the report.
"Show ending balance" was checked.
-- If you are downloading security quotes, of course the report will reflect the latest security prices and will not match an earlier statement.
The issue isn't that the report doesn't match brokerage statements. The issue is the duplicate -Cash- entries, once at the top of the PV report, once at the bottom, just above the Total line.
-- If you have money market funds set to download quotes, look at the price history for those funds and make sure all the entries are 1.00 and there are no zeros. Since the MMF price is always 1.00, it is best to disable downloading of quotes for these funds.
MM funds are not downloaded. All cash equivalents are called -Cash-. BTW, in the Portfolio Value report, when I go to Customize/Securities, if I uncheck "No Security (includes Cash), both Cash lines are omitted and the total is correct.
Potential additional clue. I have Quicken 2016 Deluxe, Rev 17.4, Build 25.1.12.4 on a desktop computer with the data updated through ~6/30/18 (before I bought the laptop). I ran the PV report and see the same duplicate Cash lines. Once at the top of the security list and once at the bottom if I don't subtotal and once as the first security, once as the last for ONE of the accounts if I subtotal by account. Same issue as originally reported in Q18.
After upgrading to Q18, I renamed some of the accounts so they would sort differently. As a result, when I run the PV report and subtotal by account, the accounts are listed in different order on Q16 vs. Q18. However, the SAME brokerage account has the duplicate cash entry in both versions of Quicken. Whether it is first on the list or later in the list. I don't know if there is something I should look for in that account.
0 -
More information. I ran the PV report for multiple years and found out that the duplicate cash entry goes away on 11/2/14. But, it returns on 11/3/14. I then ran the PV report excluding that account. The duplicate cash entry is gone for all dates of the report and whether or not I subtotal by account.
Looking at the transactions for that account on 11/3/14 there is a Quicken placeholder entry to reconcile the cash balance. But, the cash balance shown before that placeholder was the correct cash balance. When I tried to click on the delete button to delete that entry, I got a dialogue box that says: "This transaction was entered by Quicken to maintain your existing cash balance. This transaction cannot be modified. Search for "placeholder" at quicken.intuit.com/support for further information. I then discovered that I could right click on the transaction and delete it. I did. But, the report still shows the duplicate -Cash- entry starting on that date.
There are 3 other entries for that date for that account. They are all reinvested dividends and one corporate name change. They all appear correct.
Does this help? Any suggestions on what to do from here? I have not run a data validation yet. Frankly, I'm afraid to. My Quicken file is huge.
0 -
[Edit - our posts overlapped] It shouldn't matter, but are there Placeholders in the account that has the multiple -Cash- entries? Go to Edit > Preferences > Investment Transactions and make sure Show hidden transactions is checked. Any Placeholders will have a gray background and and Action of "Entry"
Also, to make sure there is not a problem with your data file, do a backup then go to File > File Operations > Validate and repair and check Validate file then OK to see if Quicken finds any problems.QWin Premier subscription0 -
Just got your email. I already deleted the placeholder entry. Still OK to validate?
0 -
Yes you can validate. Back up first, just in case!AbbyKay said:Just got your email. I already deleted the placeholder entry. Still OK to validate?
QWin Premier subscription0 -
Just validated. It found and corrected some errors, but none to do with this account. I ran the PV report again and got same results. Duplicate -Cash- entry goes away if I exclude that account from the report. And, the problem still starts 11/3/14. Any other suggestions? 4 years worth of transactions is too much to re-Ienter manually. But if you thought it would work, I could delete all the November transactions, look for old statements (maybe on line) and manually re-enter November 2014.0
-
No, don't delete everything. Copy the details of the 11/3/14 transactions, delete them one at a time and re-enter, checking to see if the problem goes away. I assume you have a recent backup just in case, as mentioned above.AbbyKay said:Just validated. It found and corrected some errors, but none to do with this account. I ran the PV report again and got same results. Duplicate -Cash- entry goes away if I exclude that account from the report. And, the problem still starts 11/3/14. Any other suggestions? 4 years worth of transactions is too much to re-Ienter manually. But if you thought it would work, I could delete all the November transactions, look for old statements (maybe on line) and manually re-enter November 2014.
QWin Premier subscription0
This discussion has been closed.