12 month Investment Analysis Data issues?
When viewing the investment screen the data for basic market value, cost and return appear to calculate properly for each line item (% and $) as do the one month and three month returns ($ and %). However, in looking at the similar 12 month return data, some data appears to be correct but for some line items the 12 month $ return shows as larger than my cost (example $30,000 cost, $35,000 return) even though the core return (also now for 12 months) is correctly showing as say $2000. The % return under the 12 month column shows up as say 7,280 (Great!) which is the same as my base cost (not the 6% it should be) The Annual and YTD $ return and IRR numbers also appear to be in error (IRR% of 3500???). Lastly, even though the core data is showing and the 1 and 3 month data shows correctly, a number of line items show NA instead of results at 12 months. Note that the errors occur for both CAD and USD line items, for funds and stocks and for transactions that were only the original purchase entry and those with multiple entries during the year. It seems the software is simply grabbing the wrong numbers to calculate 12 month and YTD values so reporting garbage.
I have verified and repaired the data base (twice) but this has not resolved these issues. Can someone advise if this is a known problem that is being worked on or if its just me, how might I get my 12 month data working correctly? Am I facing having to delete and re-enter transaction data to reset the database?
Thanks for any help on this.
Comments
-
The first 'rule' seems to be for 12-month values you typically must have owned the security 12-months ago. So if you bought any of those securities new during the last year, they will likely be showing the NA for the 12-month data.
I assume at this point, you are looking at IRR values in the portfolio view. I would switch over to the Investment Performance report as a diagnostic tool. With that report, you can customize to individual securities and to specific time spans. That may help you identify something that is not right with your data. For the same 12-month time frame, the report should agree with the portfolio view presentation.
You might also try some variations for the portfolio view with the 'As of' date and the beginning date (Options / Portfolio Preferences). (Off the top, I do not recall the sensitivity of the Avg Ann Return data to those two parameters.)
Beyond that, I would ask you for some specifics, such as I had XYZ at this cost on 12/31/16, so many dividends, and other transactions, ending on 1/2/18 at this value. The 12-month IRR shows as 3500%. Then we might be better able to diagnose with you. Snipping (Windows Snip tool) screenshots can also be very helpful. I would hope that if we get one or two securities figured out, the rest will become obvious.0 -
Looking further at the NA issue I have now found that on the Investment tab/portfolio view if I drill down to the transaction level for any security 12 month data is calculated when the transaction is old enough (so like mine dated 12/31/2016). So while I can see 1 month and 3 month data at the transaction level and also rolled up at the security level, 12 month data is only showing at the transaction level and does not roll up to the security level (shows NA as noted). Curiously the 12 month data does roll up to the account and portfolio level, even if it doesn't show art the security level.
What is also puzzling is when there are multiple transactions of different dates for any security, the 1 or 3 or 12 month data is calculated for the transaction or NA is shown if the timing criteria fails. The 1 or 3 month data rolls up as noted ignoring the NA entries but the 12 month doesn't do the same.
So this issue would seem to be with rolling up and showing 12 month data at the security level.
Not sure how effective this will be but here is some hard data concerning the 12 month data calculation issue taken from the portfolio view:
Equity A and Equity B are each a single Quicken transaction (Shares added) on 12/31/2016.
Fund C and Fund D are the same security but in different accounts. Both were set up on 12/31/2016 but had monthly minor ($10 - 20) dividend reinvestment transactions.
Equity A Equity B Fund C Fund D
Value 52,211.25 11,946 55,878.12 19,118.10
Cost 38,675 10,012 51,712.99 17,691.35
Gain/Loss 13,536.25 1,933.80 4165.13 1,426.08
Gain/Loss % 35 19.31 8.05 8.06
Gain/Loss 12 Month 33,393.75 7,646.10 3,604.25 1,236.08
% 175.99 191.88 7.11 7.11
Amount Return 1 year 33,725.99 7,961.74 55,751.42 1,736.96
Avg Annual % 1 year 179.9 191.88 10.25 9.99
No complaint about actual performance but with the portfolio/securities a year old on Quicken the total return data and the 12 month data should agree. I did not have a 175% return for equity A in 2017, only 35%. This only happens with some of the securities (others do calculate properly) so a quandary. if its corrupted data it doesn't fix with the repair tool. Why would the 1 month and 3 month work but the 12 month doesn't?
Anything to be seen in this? I did read something that deleting and reentering might be needed....
Thanks0 -
Gain/Loss (overall) vs Gain/Loss (12-month). The overall value is going to be looking at current value less basis identified in the 12/31/16 Add Shares transactions. The 12-month value is going to be looking at the current value less the value 12-months ago (1/3/2017?) The big difference in those values for A and B suggests some bad price data on or just before that 12-month ago date.Guy Winchester said:Looking further at the NA issue I have now found that on the Investment tab/portfolio view if I drill down to the transaction level for any security 12 month data is calculated when the transaction is old enough (so like mine dated 12/31/2016). So while I can see 1 month and 3 month data at the transaction level and also rolled up at the security level, 12 month data is only showing at the transaction level and does not roll up to the security level (shows NA as noted). Curiously the 12 month data does roll up to the account and portfolio level, even if it doesn't show art the security level.
What is also puzzling is when there are multiple transactions of different dates for any security, the 1 or 3 or 12 month data is calculated for the transaction or NA is shown if the timing criteria fails. The 1 or 3 month data rolls up as noted ignoring the NA entries but the 12 month doesn't do the same.
So this issue would seem to be with rolling up and showing 12 month data at the security level.
Not sure how effective this will be but here is some hard data concerning the 12 month data calculation issue taken from the portfolio view:
Equity A and Equity B are each a single Quicken transaction (Shares added) on 12/31/2016.
Fund C and Fund D are the same security but in different accounts. Both were set up on 12/31/2016 but had monthly minor ($10 - 20) dividend reinvestment transactions.
Equity A Equity B Fund C Fund D
Value 52,211.25 11,946 55,878.12 19,118.10
Cost 38,675 10,012 51,712.99 17,691.35
Gain/Loss 13,536.25 1,933.80 4165.13 1,426.08
Gain/Loss % 35 19.31 8.05 8.06
Gain/Loss 12 Month 33,393.75 7,646.10 3,604.25 1,236.08
% 175.99 191.88 7.11 7.11
Amount Return 1 year 33,725.99 7,961.74 55,751.42 1,736.96
Avg Annual % 1 year 179.9 191.88 10.25 9.99
No complaint about actual performance but with the portfolio/securities a year old on Quicken the total return data and the 12 month data should agree. I did not have a 175% return for equity A in 2017, only 35%. This only happens with some of the securities (others do calculate properly) so a quandary. if its corrupted data it doesn't fix with the repair tool. Why would the 1 month and 3 month work but the 12 month doesn't?
Anything to be seen in this? I did read something that deleting and reentering might be needed....
Thanks
Is it fair to attribute the $300-500 differences between 1-year Return $ and Gain/Loss 12-month to the reinvested dividends (Funds AB, and D)? Fund C would need some other explanation.0 -
Ok thanks. I would agree there is some bad data somewhere or a bad relational link in the database but as I understand it doing the database repair thing should delete and then replace anything that was suspect and should fix links unless the source itself was bad. Funds A and B are separate and distinct so to have the same issue with both (and other securities) not sure its just a data thing. Funds C and D are the same fund and all monthly transactions were proportionally the same so the ratios (%) should be the same.
If this all works for others as it should and its only me for both my concerns then its either corrupted software or a database that will not repair. Reinstalling the software is easy and may be the fix for the NA issue but if its in the database file then the only answer may be delete and replace the transactions that don't work. Is there a way to export my current data to some editable platform (like Excel), review and edit manually, and then import back a revised set of data into a new Quicken database file?
Maybe all I need now is confirmation or an assurance that the software when installed and set up properly works as it should for my issues so my issues are unique/local to me before I take any next steps.
Thanks again.0 -
Huh?Guy Winchester said:Ok thanks. I would agree there is some bad data somewhere or a bad relational link in the database but as I understand it doing the database repair thing should delete and then replace anything that was suspect and should fix links unless the source itself was bad. Funds A and B are separate and distinct so to have the same issue with both (and other securities) not sure its just a data thing. Funds C and D are the same fund and all monthly transactions were proportionally the same so the ratios (%) should be the same.
If this all works for others as it should and its only me for both my concerns then its either corrupted software or a database that will not repair. Reinstalling the software is easy and may be the fix for the NA issue but if its in the database file then the only answer may be delete and replace the transactions that don't work. Is there a way to export my current data to some editable platform (like Excel), review and edit manually, and then import back a revised set of data into a new Quicken database file?
Maybe all I need now is confirmation or an assurance that the software when installed and set up properly works as it should for my issues so my issues are unique/local to me before I take any next steps.
Thanks again.I would agree there is some bad data somewhere or a bad relational link in the database
You are not agreeing with me because I made no such assertion with respect to relational links. Bad data in the sense that you may have prices that are incorrect for particular days is very possible but that is all that I was suggesting.the database repair thing should delete and then replace anything that was suspect ...
No. The Validate and Repair routines will "fix" what it "knows" to be in error. Suspicion is not enough. Further the V&R routines are recognized as being limited with respect to investment accounts.
I have no doubt at all that the numbers are a result of your data. I have no reason whatsoever to think that a program installation change would have any impact at all.
Go to the portfolio view (Crtl-U) which is presumably where you are seeing this data.- Rt-Click on the Fund A security
- Choose the Price History selection
- Review the values for 12/31/16 through 1/10/17 and beyond
- Correct any errors in the closing prices (Hi, lo, and volume is your choice, I would not bother)
- Generate an Investment performance report (Reports / Investing / Investment Performance.
- Customize as applicable to accounts and securities
- You can subtotal by either security or account (or others as desired)
- Dates - I would start with Earliest to date which should be per this discussion from 12/31/16.
Maybe all I need now is confirmation or an assurance that the software when installed and set up properly works as it should for my issues so my issues are unique/local to me before I take any next steps.
I don't know what I can say to assure your the issues are unique and local to you, but I firmly believe they are. I find the Average Annual Return (IRR) values to be the most valuable measures in Quicken and have found them to be reliable. Further, when there are 'quirks', they also become explainable.
Now I will point to one such quirk.
Add Shares with basis of $100 on 12/31/16
Value closes at $500 on 12/31/16
Value closes at $1000 today (1/4/18)
The low basis is for whatever reason - such as you bought them a long time ago in another account and have now recorded them in Quicken in this account
Your Gain/Loss is $900
Your Gain/Loss for the last 12 months is $500
Your Avg Ann Ret from 12/31/16 to date is +875% (uses the $100 basis)
Your Avg Ann Ret from 1/1/17 to date is 98% (uses the $500 close)
So that type of quirk may be in play for you, but until you evaluate the data, you can't be sure.0 -
Thanks. I'll follow your advice to investigate further and see what turns up.Guy Winchester said:Ok thanks. I would agree there is some bad data somewhere or a bad relational link in the database but as I understand it doing the database repair thing should delete and then replace anything that was suspect and should fix links unless the source itself was bad. Funds A and B are separate and distinct so to have the same issue with both (and other securities) not sure its just a data thing. Funds C and D are the same fund and all monthly transactions were proportionally the same so the ratios (%) should be the same.
If this all works for others as it should and its only me for both my concerns then its either corrupted software or a database that will not repair. Reinstalling the software is easy and may be the fix for the NA issue but if its in the database file then the only answer may be delete and replace the transactions that don't work. Is there a way to export my current data to some editable platform (like Excel), review and edit manually, and then import back a revised set of data into a new Quicken database file?
Maybe all I need now is confirmation or an assurance that the software when installed and set up properly works as it should for my issues so my issues are unique/local to me before I take any next steps.
Thanks again.0 -
Partial resolution of the bad data issue. For the selected set of securities with bad data Quicken did not use the calculated unit price from each securitie's first transaction but used another number derived from somewhere else unknown. All were USD denominated securities (my Quicken works in CAD but uses both CAD and USD separately at the account level) so because I used a Sunday as the transaction date maybe no access to exchange rates for that day had an impact (or some other reason!). Changing the date to the last prior market open day, deleting the bad data and manually entering the correct unit price for each for that start date fixed the issue.
Still have problems with the 12 month gain/loss and 12 month gain/loss % at the security, account and portfolio level for some but not all my securities/accounts showing on the investment tab/portfolio. While the 1 month and 3 month work flawlessly as all levels for all securities, the 12 month still shows NA for many securities (all are more than 12 months old), and it does not roll up totals correctly for the account or portfolio level. At the portfolio level the 12 month gain/loss % shows as 6,280, not the 10 - 12% it should be. I don't see this as a data integrity issue given the 1 month and 3 month work fine as now does the 12 month annual return IRR. Something else going on....0 -
Guy Winchester said:
Partial resolution of the bad data issue. For the selected set of securities with bad data Quicken did not use the calculated unit price from each securitie's first transaction but used another number derived from somewhere else unknown. All were USD denominated securities (my Quicken works in CAD but uses both CAD and USD separately at the account level) so because I used a Sunday as the transaction date maybe no access to exchange rates for that day had an impact (or some other reason!). Changing the date to the last prior market open day, deleting the bad data and manually entering the correct unit price for each for that start date fixed the issue.
Still have problems with the 12 month gain/loss and 12 month gain/loss % at the security, account and portfolio level for some but not all my securities/accounts showing on the investment tab/portfolio. While the 1 month and 3 month work flawlessly as all levels for all securities, the 12 month still shows NA for many securities (all are more than 12 months old), and it does not roll up totals correctly for the account or portfolio level. At the portfolio level the 12 month gain/loss % shows as 6,280, not the 10 - 12% it should be. I don't see this as a data integrity issue given the 1 month and 3 month work fine as now does the 12 month annual return IRR. Something else going on....All were USD denominated securities (my Quicken works in CAD but uses both CAD and USD separately at the account level) so because I used a Sunday as the transaction date maybe no access to exchange rates for that day had an impact (or some other reason!).
This raises a couple of points. I understood you were using CAD and USD denominated accounts, but I had not considered exchange rate aspects. I know little to nothing about the effects of exchange rates on data, views and reports we are talking about. I do believe the US program lacks historical exchange rate information and only uses one current-value exchange rate where and when needed. The Canadian Quicken could be using a different exchange rate day-by-day based on historical data it has stored, I suppose.
I am more inclined to believe that "deleting the bad data and manually entering the correct unit price" (in the price history records) was the pertinent repair. A plausible sequence to get bad prices into a price history is to enter a transaction with a particular price (like a buy or a sale) and then delete or redate that transaction because it was in error. The price associated with that transaction will remain in the records and if it was on a weekend, would not get overridden by a regular price update.Still have problems with the 12 month gain/loss and 12 month gain/loss % ...
You numbers cited above were generally in agreement between 12-month Gain/Loss % and Average Annual 1-yr Return % but values were too high. So now the AAR (IRR) is right (10-12%) range, and the 1-month and 3-month Gain.Loss % is OK, but the 12-month Gain/Loss (% and dollar) are to high.
Has the security in question had a stock split in the last 12 months (but more than 3 months ago)? If so, does your security detail view look something like this:
Note the major discrepancy between G/L +436% and AAR +78% and the major 'uptick' in the split adjusted prices in the time frame of the split. The not-split adjusted prices jump the other direction. If this fits your data, I'll lead you through the fix.0 -
For exchange rates Canada Home and Business updates exchange rates as part of the One Step update and maintains historical rates in a table that is similar to the price table. When securities are added you set the base currency for the security which can be any one of a full list of international currencies - so CAD and USD but pound, EUR, yen, HK$ etc. if desired. The securities are then maintained through Quicken in the designated currency. Accounts also have a designated currency so I have CAD accounts holding CAD securities and USD accounts holding USD securities. Because I have designated CAD as my portfolio currency, the portfolio sums showing on Investing/Portfolio are the sum of the current and historical CAD accounts values plus the current and historical exchange rate applied USD accounts converted to CAD. What this means then is when I visually scan my account list I see CAD and USD account totals which are apples and oranges and the I rely on Quicken to give me the total in apples.
Where this all gets muddy is my current history stored for USD only goes back 10 months so I don't know how it goes farther back. Maybe it stores hard numbers for older data. What I don't know then is what it does if a transaction is entered for a non-business day. Pricing uses the latest prior price, can't tell if exchange rates do. Ands in my case if there was an error, is that exchange rate error permanent? My fix to change the dates to a business day may or may not help but can't hurt.
Maybe I haven't been able to describe the remaining "display" issue on Investing/Portfolio as well as I could. Don't get me wrong, Quicken works great but I just can't get some of the hard coded analytical data that I find very useful in managing my investment portfolio and should be able to see.
1. I have 9 investment CAD and USD accounts that are all very simple in nature with securities (both stocks and funds) that for the most part are buy and hold with some minor monthly dividend action for a few securities and the odd sell and buy to get rid of the odd turkey. No splits.
2. On Investing/Portfolio, the current value, cost, gain/loss, gain loss%, 1 month gain/loss and gain/loss% and 3 month gain/loss and the gain loss % work and display exactly as they should for every security (including at the transaction level for the security), for every account and the full portfolio (with exchange rates applied). Ratios and totals are correct and no N/A for any data meeting the required time criterion.
3. However, the 12 month gain/loss and gain loss % is problematic. The calculations appear to be correct at the transaction level for all securities (which may be a single entry dated 12/30/2016 or multiple entries if there was activity within the security). However, while at the security level some do show the correct values, many show N/A (even though the transactions are correct, meet the time requirement and the base value data should just rollup). At the account level there may be a total which does not appear to relate to any numbers in the account or there may be an N/A. Within any account there may be securities with the correct data and some with N/A. That said there are two accounts where everything is perfect. All data is displaying correctly at all levels (transaction, security and account).
4. The total portfolio values do not appear to relate to the subordinate data. In particular the 12 month % is displayed as a large integer (6,280) instead of a % that should be about 11%. It makes sense that with N/A's at lower levels the totals may not work, something that may be further complicated due to the exchange rate application to get totals % based on CAD. However the 1 month and 3 month calculations do work and do display perfectly at all levels. In my experience this kind of display error typically indicates a formatting/coding issue and not a data issue.
5. I don't see any common thread for any security level N/A that shows up - nothing in account set up, security set up, currency, one or many transactions etc.. Some just work and some don't. Note that the Investment Performance Report works flawlessly with its more complex IRR calculations based on the transaction data so the root data itself likely is not an issue. N/A should show up when relevant data is not available to be calculated on - data is there because it does calculate at the transaction level so N/A shows up why?
6. All of the above is based on a Group By "Account" but same error issues and N/A when group by any other of the available factors. The root seems to be the inability to sum transaction data at the security level and above when doing simple 12 month returns.
7. To sum, for me the correct 12 month transaction level data may or may not sum to the security level which may or may not sum to the account level which does not sum correctly to the portfolio level. Again, works for 1 month and 3 months as it should. Might I speculate that when the new Canada Home and Business was put together, given the complexity of the specific needs of this version maybe the 1 and 3 months were done right but the 12 month less so (it happens that Beta doesn't catch everything). If my data can now be considered to be correct and there is no user setting for me to toggle or root for me to follow to fix and if this is not an observable or repeatable issue in the Quicken US version then I think it only leads to a issue with some unique part of this version my set of data has triggered.
Maybe someone else with Canada Home and Business will come across this error and discuss it. I think the fix is for Quicken to look at their code as there doesn't seem anywhere at the user level to go. Until then I will just have to live with it.
Thanks again for your help with this.
0 -
If you have time at some point, it might be if interest to start a new file, one account, one security, duplicate the transactions, and see how the values play out. That just might(?) reveal something about the exchange rate issues or other aspects.Guy Winchester said:For exchange rates Canada Home and Business updates exchange rates as part of the One Step update and maintains historical rates in a table that is similar to the price table. When securities are added you set the base currency for the security which can be any one of a full list of international currencies - so CAD and USD but pound, EUR, yen, HK$ etc. if desired. The securities are then maintained through Quicken in the designated currency. Accounts also have a designated currency so I have CAD accounts holding CAD securities and USD accounts holding USD securities. Because I have designated CAD as my portfolio currency, the portfolio sums showing on Investing/Portfolio are the sum of the current and historical CAD accounts values plus the current and historical exchange rate applied USD accounts converted to CAD. What this means then is when I visually scan my account list I see CAD and USD account totals which are apples and oranges and the I rely on Quicken to give me the total in apples.
Where this all gets muddy is my current history stored for USD only goes back 10 months so I don't know how it goes farther back. Maybe it stores hard numbers for older data. What I don't know then is what it does if a transaction is entered for a non-business day. Pricing uses the latest prior price, can't tell if exchange rates do. Ands in my case if there was an error, is that exchange rate error permanent? My fix to change the dates to a business day may or may not help but can't hurt.
Maybe I haven't been able to describe the remaining "display" issue on Investing/Portfolio as well as I could. Don't get me wrong, Quicken works great but I just can't get some of the hard coded analytical data that I find very useful in managing my investment portfolio and should be able to see.
1. I have 9 investment CAD and USD accounts that are all very simple in nature with securities (both stocks and funds) that for the most part are buy and hold with some minor monthly dividend action for a few securities and the odd sell and buy to get rid of the odd turkey. No splits.
2. On Investing/Portfolio, the current value, cost, gain/loss, gain loss%, 1 month gain/loss and gain/loss% and 3 month gain/loss and the gain loss % work and display exactly as they should for every security (including at the transaction level for the security), for every account and the full portfolio (with exchange rates applied). Ratios and totals are correct and no N/A for any data meeting the required time criterion.
3. However, the 12 month gain/loss and gain loss % is problematic. The calculations appear to be correct at the transaction level for all securities (which may be a single entry dated 12/30/2016 or multiple entries if there was activity within the security). However, while at the security level some do show the correct values, many show N/A (even though the transactions are correct, meet the time requirement and the base value data should just rollup). At the account level there may be a total which does not appear to relate to any numbers in the account or there may be an N/A. Within any account there may be securities with the correct data and some with N/A. That said there are two accounts where everything is perfect. All data is displaying correctly at all levels (transaction, security and account).
4. The total portfolio values do not appear to relate to the subordinate data. In particular the 12 month % is displayed as a large integer (6,280) instead of a % that should be about 11%. It makes sense that with N/A's at lower levels the totals may not work, something that may be further complicated due to the exchange rate application to get totals % based on CAD. However the 1 month and 3 month calculations do work and do display perfectly at all levels. In my experience this kind of display error typically indicates a formatting/coding issue and not a data issue.
5. I don't see any common thread for any security level N/A that shows up - nothing in account set up, security set up, currency, one or many transactions etc.. Some just work and some don't. Note that the Investment Performance Report works flawlessly with its more complex IRR calculations based on the transaction data so the root data itself likely is not an issue. N/A should show up when relevant data is not available to be calculated on - data is there because it does calculate at the transaction level so N/A shows up why?
6. All of the above is based on a Group By "Account" but same error issues and N/A when group by any other of the available factors. The root seems to be the inability to sum transaction data at the security level and above when doing simple 12 month returns.
7. To sum, for me the correct 12 month transaction level data may or may not sum to the security level which may or may not sum to the account level which does not sum correctly to the portfolio level. Again, works for 1 month and 3 months as it should. Might I speculate that when the new Canada Home and Business was put together, given the complexity of the specific needs of this version maybe the 1 and 3 months were done right but the 12 month less so (it happens that Beta doesn't catch everything). If my data can now be considered to be correct and there is no user setting for me to toggle or root for me to follow to fix and if this is not an observable or repeatable issue in the Quicken US version then I think it only leads to a issue with some unique part of this version my set of data has triggered.
Maybe someone else with Canada Home and Business will come across this error and discuss it. I think the fix is for Quicken to look at their code as there doesn't seem anywhere at the user level to go. Until then I will just have to live with it.
Thanks again for your help with this.
I'll also see if I can get the attention of a Canada-minded user with investment experience.0 -
I have created a clean/new test file, created a new account in that file and added 4 securities I have in my portfolio. Created a single transaction for each, adding shares (add, not bought) as of 12/30/2016, and duplicating the data in my regular file. 2 of these had worked properly and 2 had the described issues in my regular file.
The result was again the undesired NA at the security level for all 4 securities for the 1 month, 3 month and 12 month gain/loss and % numbers but as before, all 4 have the correct data showing when I expand to the transaction level. NA at the account and portfolio levels. So the bad behaviour persists in the test file and with the 2 securities that did work now are not working at the security level in the test file, its suggesting again its not a data dependent issue but something else. but now the 1 and 3 month are also misbehaving.
Maybe clutching at straws a bit but in setting up as mutual fund on the security list, both the type and exchange will be "Mutual fund" even though the fund is listed say on the TSX (another choice other than mutual fund on my drop list)?. In setting up a security its seems like this is the only user input (after doing the search based on the fund code) so good to verify this is correct. Not sure how it would matter for this issue but just in case....0 -
Do you have valid price history for these securities going back at least to the date they were added? Make sure the history goes back far enough and there are no zero entries. Be particularly suspicious of money market funds. I have had numerous occasions where their prices would sometimes download as 0.00.
Also when you added the securities, did you enter a valid price for them, to set the cost basis?
It looks like you will get N/A for the Gain/loss and gain/loss % if either the price or the share quantity was zero at the start of the period.QWin Premier subscription0 -
Would it clarify the exchange rate component if you tried that sort of test on a purely CAD and or purely USD setup? I am hard-pressed to believe the specific securities matter so 'any' US or Canadian securities or mutual funds should do for that sort of testing, shouldn't they?Guy Winchester said:I have created a clean/new test file, created a new account in that file and added 4 securities I have in my portfolio. Created a single transaction for each, adding shares (add, not bought) as of 12/30/2016, and duplicating the data in my regular file. 2 of these had worked properly and 2 had the described issues in my regular file.
The result was again the undesired NA at the security level for all 4 securities for the 1 month, 3 month and 12 month gain/loss and % numbers but as before, all 4 have the correct data showing when I expand to the transaction level. NA at the account and portfolio levels. So the bad behaviour persists in the test file and with the 2 securities that did work now are not working at the security level in the test file, its suggesting again its not a data dependent issue but something else. but now the 1 and 3 month are also misbehaving.
Maybe clutching at straws a bit but in setting up as mutual fund on the security list, both the type and exchange will be "Mutual fund" even though the fund is listed say on the TSX (another choice other than mutual fund on my drop list)?. In setting up a security its seems like this is the only user input (after doing the search based on the fund code) so good to verify this is correct. Not sure how it would matter for this issue but just in case....
With your current test, did you look at the price history data? US bias, when you pick a valid security, QW-US usually backloads in the last 5 years of prices - month-end for the oldest 4 years, weekly for the last year, daily for the last 30 days. So I expect your data has a suffucently full set of data to be working with. Just a check.0 -
Test file was set up with CAD for the account currency and all the test securities were also CAD denominated so no exchange rate impact.
Historical pricing history did download for each security, 5 years as you note should happen. As it should, Quicken uses the manual price I entered in the add transaction on 12/30/2016 as the price for the base cost calculation (not a downloaded market price). And this was all real data for the particular real securities.
I'm assuming that for the 1 month, 3 month and 12 month gain/loss Quicken goes backwards from the current date and uses the historical price data from 1 month, 3 months or 12 month prior but if that back date does not have a price (ie a non trading day), it uses the most recent valid price prior to that back date. Similarly, because typically mutual funds lag one day for current market prices or if today is a non trading day, the price used for the current day is the latest/newest valid recent price in the history. These mechanics all seem to work correctly, at least for the basic gain/loss in both the test and real files and for every security entry transactions.
I can see an N/A being thrown if these mechanics don't work but at the most basic data level of individual transactions both in the real and test file they do work perfectly. Its an assumption on my part but the security level calculation should be summing up the time relevant cost and current values from the security's transactions set by date range (could be 1 entry only or a thousand) and then would generate the 1, 3 and 12 month gain/loss by difference and then % by calculation. This is where it seems to break down. Then either the Account and portfolio level also fail because of the same problem with summing transactions (how I would have coded it) or because they sum the broken prior layer. The intrigue is it looks like it works sometimes so which bit is broken? Because transactions work, is the issue with the collective date range at the security level and above?
Again, I don't see this being a user caused issue when similar data sets work at the base entry level but work or don't work up the cascade. I would agree we can cause this error with a defective data set but if the data set is correct then why? For now, without being able to see the code, this is all speculation.
Someone at Quicken needs to look at the source code to see how this error might occur with good data. My impression was Quicken Home and Business Canada was rushed out the door last winter so as with lots of software, there will be issues identified from real world use that were not trapped in testing. This may be one of them. From my own experience building similar function databases this seems like a very common and simple code error I have seen before that only shows up with real world stresses. Can be as simple as a variable formatting error that reacts only to certain valid data conditions on my version of Win 10.0 -
I am now working with this partial view in my file, tweaking it various ways.Guy Winchester said:Test file was set up with CAD for the account currency and all the test securities were also CAD denominated so no exchange rate impact.
Historical pricing history did download for each security, 5 years as you note should happen. As it should, Quicken uses the manual price I entered in the add transaction on 12/30/2016 as the price for the base cost calculation (not a downloaded market price). And this was all real data for the particular real securities.
I'm assuming that for the 1 month, 3 month and 12 month gain/loss Quicken goes backwards from the current date and uses the historical price data from 1 month, 3 months or 12 month prior but if that back date does not have a price (ie a non trading day), it uses the most recent valid price prior to that back date. Similarly, because typically mutual funds lag one day for current market prices or if today is a non trading day, the price used for the current day is the latest/newest valid recent price in the history. These mechanics all seem to work correctly, at least for the basic gain/loss in both the test and real files and for every security entry transactions.
I can see an N/A being thrown if these mechanics don't work but at the most basic data level of individual transactions both in the real and test file they do work perfectly. Its an assumption on my part but the security level calculation should be summing up the time relevant cost and current values from the security's transactions set by date range (could be 1 entry only or a thousand) and then would generate the 1, 3 and 12 month gain/loss by difference and then % by calculation. This is where it seems to break down. Then either the Account and portfolio level also fail because of the same problem with summing transactions (how I would have coded it) or because they sum the broken prior layer. The intrigue is it looks like it works sometimes so which bit is broken? Because transactions work, is the issue with the collective date range at the security level and above?
Again, I don't see this being a user caused issue when similar data sets work at the base entry level but work or don't work up the cascade. I would agree we can cause this error with a defective data set but if the data set is correct then why? For now, without being able to see the code, this is all speculation.
Someone at Quicken needs to look at the source code to see how this error might occur with good data. My impression was Quicken Home and Business Canada was rushed out the door last winter so as with lots of software, there will be issues identified from real world use that were not trapped in testing. This may be one of them. From my own experience building similar function databases this seems like a very common and simple code error I have seen before that only shows up with real world stresses. Can be as simple as a variable formatting error that reacts only to certain valid data conditions on my version of Win 10.
G/L overall is right as seen and confirmed for the listed Quotes by lot. Likewise, presentation for the two individual securities and the account (78.21) are correct. At the bottom (not in snip), the 78.21 is repeated.
1-month G/L OK, same values per lot and for security. Combination for account OK, similar but not identical market values so the 1.52 is about right for the composite of 1.92 and 1.05. BUT, the total at the bottom (not in snip) is 2.30 instead of 1.52.
3-month G/L -- again for each lot, the two G/L values are the same, but now there is a difference for the per-security value 28.96 vs 27.32 and 16.70 vs 18.14. As it turns out, I would rate the per security values correct, that is 28.96 and 16.70 = (1/9/18 price - 10/9/17 price) / (10/9/17 price) or the change in price divided by the initial price. I am trying to determine the source of the by-lot values
Am I on same page with you now - with these sort of issues being the question? If so, it is not something restricted to Canada and probably not just to 2017/18 editions.
As I noted, I am continuing to try to understand the other values0 -
Discovery!Guy Winchester said:Test file was set up with CAD for the account currency and all the test securities were also CAD denominated so no exchange rate impact.
Historical pricing history did download for each security, 5 years as you note should happen. As it should, Quicken uses the manual price I entered in the add transaction on 12/30/2016 as the price for the base cost calculation (not a downloaded market price). And this was all real data for the particular real securities.
I'm assuming that for the 1 month, 3 month and 12 month gain/loss Quicken goes backwards from the current date and uses the historical price data from 1 month, 3 months or 12 month prior but if that back date does not have a price (ie a non trading day), it uses the most recent valid price prior to that back date. Similarly, because typically mutual funds lag one day for current market prices or if today is a non trading day, the price used for the current day is the latest/newest valid recent price in the history. These mechanics all seem to work correctly, at least for the basic gain/loss in both the test and real files and for every security entry transactions.
I can see an N/A being thrown if these mechanics don't work but at the most basic data level of individual transactions both in the real and test file they do work perfectly. Its an assumption on my part but the security level calculation should be summing up the time relevant cost and current values from the security's transactions set by date range (could be 1 entry only or a thousand) and then would generate the 1, 3 and 12 month gain/loss by difference and then % by calculation. This is where it seems to break down. Then either the Account and portfolio level also fail because of the same problem with summing transactions (how I would have coded it) or because they sum the broken prior layer. The intrigue is it looks like it works sometimes so which bit is broken? Because transactions work, is the issue with the collective date range at the security level and above?
Again, I don't see this being a user caused issue when similar data sets work at the base entry level but work or don't work up the cascade. I would agree we can cause this error with a defective data set but if the data set is correct then why? For now, without being able to see the code, this is all speculation.
Someone at Quicken needs to look at the source code to see how this error might occur with good data. My impression was Quicken Home and Business Canada was rushed out the door last winter so as with lots of software, there will be issues identified from real world use that were not trapped in testing. This may be one of them. From my own experience building similar function databases this seems like a very common and simple code error I have seen before that only shows up with real world stresses. Can be as simple as a variable formatting error that reacts only to certain valid data conditions on my version of Win 10.
For the lot (transaction) values, the 1-month GL% is actually 30-day GL%
In contrast at the security level, the 1-month GL% is a true 1 month.
Similarly for the 3-month column, the by-lot data is off of 90-days prior prices, the by security values are the true 3-month differences.
As an example, at a lot level, the program is determining 1-month GL% looking at 1/9/18 vs 12/10/17. At the security level, it is comparing 1/9/18 vs 12/9/17. At the 3-month span, the dates would be 1/9/18 vs 10/11/18 or 10/9/18.
That discovery was predicated on January prices for 10 dates comparing back to December and October prices. The results were 100% consistent. While conceivable that something different might apply apply around February, I seriously doubt it.
For the 12-month GL%, the values were always the same for by lot or by security, so I have little doubt that they are both using actual 12-month time spans.
These values appear to be solely dependent on share prices, though the securities I am using for this research at this time are both pretty simple holdings. Effects of buys, sells, rtrncaps, removes, adds, reinv___, etc. may be factors in the real calculation.
More research needed on considering those values at the Account level and the Total level (at the bottom whose values seem very messed up).0 -
Interesting. Somewhat curious that any analytical value would be defined using different criteria from transaction to security and so on. However, we should expect consistency such that any sums and subsequent ratios for any up the chain data would use the same criteria and necessarily would be exact. Up the hierarchy totals at any level should agree with the subordinate data to an accounting standard. Something wrong if they don't.
In a practical sense maybe the difference between 30 days and a true one month is negligible for transactions and it seems for you, both calculate exactly as they should. Sloppy coding then if the field names are not unique for different variables. Reminiscent of software written by committee with modules plugged together without a full continuity check..
I see your comment regarding totals not agreeing with what a hand scan/calculation would suggest. Yes this is what I have observed as well. However, my issue is a bit deeper given the program is throwing N/A's at the security level so it very well could be garbage higher up. What your work suggests is my version of Quicken can calculate a 30 day GL% at the transaction level correctly but "may or may not" be able to do a true 1 month GL% at the security level (or 3 month or 12 month). I can only speculate but given N/A's do show up when dates do not meet the necessary criteria for the factor, this "may or may not" would point to a issue with generating data for the required prior date (1, 3 or 12 months back). Does it have issues if there is no back data on the exact date 1, 3 or 12 months prior, so should use the most recent prior data to that date but perhaps doesn't always?
I can observe in my case the 1 and 3 months tends to work but often doesn't and the 12 months doesn't at all. On my test file none works. There is a major difference in the data density when downloading historical prices (and in my case exchange rates too) with age. Not only does the data need to have a historical price but the code seems to apply an exchange rate every time based on how the security, account and portfolio were defined (it sets CAD at 1.00 in the history). Is the issue then, it sometimes can't find timely, exchange rate applied price data so croaks? I tried to test this on my test file by plugging in missing data for the exact dates but no joy, so hypothesis unproven - error continues.
That said, other date range variables do calculate like 12 month and YTD IRR. Much more complex but that module is working. A better coder?
Not sure about this but I understand 2017 Home and Business Canada that published last February was a new product modeled on the similar new US version (and not an upgrade on the prior Canadian product). It was adapted where needed for the country differences. Some of these were significant like exchange rates and I assume having multi-currency portfolios but others that were minor like what we call our retirement options (RRSP instead of 401k is one example), our banks and our exchanges. Most of the base code should be the same as the US version (1+1 = 2 everywhere) and I assume the same across many versions so behaviour (mis-behaviour?) for things like totals should be the same (as we may be seeing). However, it would seem the code was touched in a big way for us to make currencies/exchange rates work.
So what we are seeing is a possible general issue with totals and what maybe a Canadian issue with some of the date range based calculations. As I suggested earlier, I don't see any user defined choices or data that will impact either of these so it comes back to some code not working as it should.
0 -
Dear Guy;
I am having the same problem. I ended up doing a major rebalance on my accounts a year ago and the Gain/Loss column correctly calculated the values while the Gain/Loss 12-month column just gave N/A.
What do you see when you add the Gain/Loss column to your portfolio view?
I have just cleared a year on my account rebalance and the *.UN (REIT) assets are now showing their Gain/Loss 12-month rather than N/A. Canadian/US Stocks and ETFs are still mostly N/A, but I'm now getting a random 0.0 in the Gain/Loss 12-month column for them versus a numerical value in the Gain/Loss column for the assets that were purchased 366 days ago. Assets I kept through the rebalancing always showed numbers in the Gain/Loss 12-month column.
I built a dummy account for a benchmark index in 2017 and the holdings in it are now 378 days old and the Gain/Loss 12-month column started reporting numerical values at 367 days. I bought the securities on a Saturday. So it looks like the 12-month period starts on the first day of trading after you enter the Stock/ETF purchase in your account.
UPDATE
More of my assets are showing up with Gain/Loss data in the 12-month column. If you go to the Investing portfolio screen and select one of your assets that shows a number in the 12-month gain column and hit the + sign on the left-hand side of your asset name you will see that any buy older than 12 months will be reported in the 12-month gain loss column. All trades less than 12 months are reported as N/A. So this column is not including all trades in the 12-month gain loss column.
If the 12-month gain loss column is reporting $0.00, the first trade was made in the last calendar year but less than 365 days from today. If the 12-month gain loss column is reporting N/A, the first trade was made in this calendar year.
Bottom line, do not trust the information in this column to give you a correct 12-month gain loss on your assets.
Ken0
