Gain/Loss As Calculated in Specify Lots Sold dialog different in Investment Transaction Report
bobair
Quicken Windows Subscription Member
I have recently started to use the Specify Lots Sold dialog for sold investments (my financial planning company doesn't support Quicken download processing). The financial company uses the Minimum Gain method for tax purposes. The attached snip shows the proper accounting and amount in the Specify Lots Sold dialog (17.85). However, the Investment Transaction Report shows an entirely different (_RlzdGain) gain(/loss) amount (22.98). This happens for nearly all transactions. Why?
Using Quicken for Windows, R29.22.
Using Quicken for Windows, R29.22.
0
Best Answer
-
Thanks Mark1104 and q_lurker.
Your responses opened up a can of worms; a small can of worms for me anyway. It also exposed that my original issue may not have been clearly described.
First answers to your questions.
From Mark1104:
to be clear, the transaction in one place is June 16 and the other report its November 5..... is that part of the problem as well? that doesn't necessarily seem right either. (ANSWER from Bobair)The [image.png] depiction shows the Specify Lots Sold in This Transaction dialog in the upper left overlaying the Investment Transaction Report (ITR) in the lower right. What is missing from the Specify Lots Sold in This Transaction dialog box is any evidence of the Transaction date from the Sell - Shares Sold dialog. To answer your question, this transaction is for the 10/5/2020 line in the ITR not 11/5. Remember that the entire transaction spans two lines in the ITR. Line 1 has the date, R.., Sold, Ame..., amount, # of shares, etc. Line 2 has the calculated Realized Gain (_RlzdGain). So the red circled data in the ITR is for 10/5/20.
Do you by chance hold this same security in two different ACCOUNTS? is this transaction posting to the correct ACCOUNT? (ANSWER from Bobair) Yes and Yes.
Also, before the transaction occurred were all the cost basis for each lot correct? (ANSWER from Bobair) The can of worms has been opened. More on this later.
:) :)
suggest also running the validate process (file >> file operations ?>> validate and repair) so the lots are all recalculated..... (ANSWER from Bobair) Did this but
turned up nothing out of the ordinary or anything that could not be explained.
From q_lurker:
Shares acquired 6/16/20 via LT Cap Gain reinvestment. Shares sold 11/5/20. (ANSWER from Bobair) Not sure if there's a question here. But the shares sold for the 10/5/2020 transaction were from a 6/16/2020 lot and determined by the Minimum Gain Auto select shares sold strategy.
Validate a valid direction. Before going that way, make sure you have a good backup at hand. (ANSWER from Bobair) Not sure if there's a question here.
Another question: Were there any commissions or discounts applied on the reinvestment transaction? Was it a simple 6.936 shares received for a dividend of $233.12 ($33.609878/share)? (ANSWER from Bobair) No there was not a commission for the transaction but it shouldn't matter. I normally use the commission field to reconcile a buy/sell transaction for rounding errors (e.g. when price * quantity <> amount of transaction). In this case the price
was automatically calculated for Reinvestment transaction. The only reason 6.936 shares were available for sale is because shares of the 6/16/2020 lot were used in earlier (date) sell transactions. The entire 6/16/2020 lot was 25.228 shares.
THE WORMS
The can of worms was opened on [transactions dated] 3/7/2020 (not shown here). This portfolio/account had several mutual fund conversions (e.g. convert "B" shares to "A" shares or convert type "X" shares to type "Y" shares) on that date. In the past I would use SELL (i.e. "B" shares) and then BUY (i.e. "A" shares) transactions to account for the change. This type of conversion transaction didn't happen very often and my accounting for it was absent of any problems or issues UNTIL the shares were sold for real money to buy bread and milk. "So why didn't you use the Mutual Fund Conversion dialog?" you may ask. Answer: never had to before and I've been using this product a looooooonnnnnngggg time. Oh the traps we set up for ourselves. My financial company was kind enough to supply data for the conversion so I had a basis cost for all funds to start with. With a fresh backup in hand it's time to try the Mutual Fund Conversion dialog. Make sure to get the correct information in all of the boxes or be ready to do a lot of manual deletes if something doesn't work right or one of the numbers or accounts was entered improperly. After a lot of bell ringing, I opened my eyes and the conversion was done for one of the mutual funds with the appropriate Removed/[shares]Added entries for the fund. The corresponding errant SELL/BUY transactions were then deleted. I repeated this process for all the remaining mutual fund conversions. I then reapplied the Minimum Gain Auto select shares sold strategy for the shares and regenerated the ITR. The corresponding picture tells the rest of the story. Many differences from the previous posting. And the results shown in the report match the data (+/- 0.01) provided by my financial company.
All is good now. Thanks Mark1104 and q_lurker for poking into this as it took me into a feature of Quicken that I had never used before but will be using alot now. I'll use the collected worms for my next fishing trip.0
Answers
-
to be clear, the transaction in one place is June 16 and the other report its November 5..... is that part of the problem as well? that doesn't necessarily seem right either.
Do you by chance hold this same security in two different ACCOUNTS? is this transaction posting to the correct ACCOUNT?
Also, before the transaction occurred were all the cost basis for each lot correct?
suggest also running the validate process (file >> file operations ?>> validate and repair) so the lots are all recalculated.....0 -
Shares acquired 6/16/20 via LT Cap Gain reinvestment.Mark1104 said:to be clear, the transaction in one place is June 16 and the other report its November 5..... is that part of the problem as well? that doesn't necessarily seem right either.
Shares sold 11/5/20.
Validate a valid direction. Before going that way, make sure you have a good backup at hand.
Another question: Were there any commissions or discounts applied on the reinvestment transaction? Was it a simple 6.936 shares received for a dividend of $233.12 ($33.609878/share)?0 -
Thanks Mark1104 and q_lurker.
Your responses opened up a can of worms; a small can of worms for me anyway. It also exposed that my original issue may not have been clearly described.
First answers to your questions.
From Mark1104:
to be clear, the transaction in one place is June 16 and the other report its November 5..... is that part of the problem as well? that doesn't necessarily seem right either. (ANSWER from Bobair)The [image.png] depiction shows the Specify Lots Sold in This Transaction dialog in the upper left overlaying the Investment Transaction Report (ITR) in the lower right. What is missing from the Specify Lots Sold in This Transaction dialog box is any evidence of the Transaction date from the Sell - Shares Sold dialog. To answer your question, this transaction is for the 10/5/2020 line in the ITR not 11/5. Remember that the entire transaction spans two lines in the ITR. Line 1 has the date, R.., Sold, Ame..., amount, # of shares, etc. Line 2 has the calculated Realized Gain (_RlzdGain). So the red circled data in the ITR is for 10/5/20.
Do you by chance hold this same security in two different ACCOUNTS? is this transaction posting to the correct ACCOUNT? (ANSWER from Bobair) Yes and Yes.
Also, before the transaction occurred were all the cost basis for each lot correct? (ANSWER from Bobair) The can of worms has been opened. More on this later.
:) :)
suggest also running the validate process (file >> file operations ?>> validate and repair) so the lots are all recalculated..... (ANSWER from Bobair) Did this but
turned up nothing out of the ordinary or anything that could not be explained.
From q_lurker:
Shares acquired 6/16/20 via LT Cap Gain reinvestment. Shares sold 11/5/20. (ANSWER from Bobair) Not sure if there's a question here. But the shares sold for the 10/5/2020 transaction were from a 6/16/2020 lot and determined by the Minimum Gain Auto select shares sold strategy.
Validate a valid direction. Before going that way, make sure you have a good backup at hand. (ANSWER from Bobair) Not sure if there's a question here.
Another question: Were there any commissions or discounts applied on the reinvestment transaction? Was it a simple 6.936 shares received for a dividend of $233.12 ($33.609878/share)? (ANSWER from Bobair) No there was not a commission for the transaction but it shouldn't matter. I normally use the commission field to reconcile a buy/sell transaction for rounding errors (e.g. when price * quantity <> amount of transaction). In this case the price
was automatically calculated for Reinvestment transaction. The only reason 6.936 shares were available for sale is because shares of the 6/16/2020 lot were used in earlier (date) sell transactions. The entire 6/16/2020 lot was 25.228 shares.
THE WORMS
The can of worms was opened on [transactions dated] 3/7/2020 (not shown here). This portfolio/account had several mutual fund conversions (e.g. convert "B" shares to "A" shares or convert type "X" shares to type "Y" shares) on that date. In the past I would use SELL (i.e. "B" shares) and then BUY (i.e. "A" shares) transactions to account for the change. This type of conversion transaction didn't happen very often and my accounting for it was absent of any problems or issues UNTIL the shares were sold for real money to buy bread and milk. "So why didn't you use the Mutual Fund Conversion dialog?" you may ask. Answer: never had to before and I've been using this product a looooooonnnnnngggg time. Oh the traps we set up for ourselves. My financial company was kind enough to supply data for the conversion so I had a basis cost for all funds to start with. With a fresh backup in hand it's time to try the Mutual Fund Conversion dialog. Make sure to get the correct information in all of the boxes or be ready to do a lot of manual deletes if something doesn't work right or one of the numbers or accounts was entered improperly. After a lot of bell ringing, I opened my eyes and the conversion was done for one of the mutual funds with the appropriate Removed/[shares]Added entries for the fund. The corresponding errant SELL/BUY transactions were then deleted. I repeated this process for all the remaining mutual fund conversions. I then reapplied the Minimum Gain Auto select shares sold strategy for the shares and regenerated the ITR. The corresponding picture tells the rest of the story. Many differences from the previous posting. And the results shown in the report match the data (+/- 0.01) provided by my financial company.
All is good now. Thanks Mark1104 and q_lurker for poking into this as it took me into a feature of Quicken that I had never used before but will be using alot now. I'll use the collected worms for my next fishing trip.0 -
Glad you found a new tool and got the issue addressed.
Keep an eye on the MF Conversion process and results. There have been subtle and not-so-subtle issues. One of the drawbacks of Quicken's newer frequent update approach is that it is harder to keep track of what works almost right and what continues to need tweaks. As I recall, the Investment Performance Report pointed to past issues with the MF Conversion process. I suggest you run that report for this year and those two securities to make sure the data looks good.0 -
From what I have been able to tell, the Mutual Fund Conversion now works correctly UNLESS you have the old fund set to "Use average cost"
If Use average cost is selected, the cost basis will be wildly incorrect after the conversion.QWin Premier subscription0 -
Thanks Jim and q_lurker (again).
A few nit things I forgot to mention.
- "After a lot of bell ringing, I opened my eyes ..." should have read "I closed my eyes, held my breath, selected OK and exited the room. After a lot of bell ringing, I returned to the room, opened my eyes ...". While the MF Conversion option was enticing, I only used it knowing that I could go back to my backup version if it didn't do what I expected(?) it to do. For my situation, the account had 6 mutual funds which had only 3+ years of history with this financial company. If there were any significant erroneous transactions, damage control was in place (backup).
- Use Average Cost (in Edit Security Details dialog) - good thing Quicken doesn't automatically check the box when adding a security. None of my mutual fund securities have this box checked but I'll double check first before going through the process again. Again, this is my first venture into looking closely at this stuff.
- After the MF Conversion was complete, there was a very small error in the total number of shares when compared to my financial company share balance. The MF conversion does its best to reconcile the AFTER conversion shares to 6 decimal digits. My financial company reports shares to 1/1000 of a share (3 decimal digits) for the funds in the portfolio (all funds < $999.99/share). To keep reconciling easy, I made extremely small adjustments to one of the [shares]Added entries for each fund - somewhere on the order of +/- .000001 to +/- .000003.
- I ran the Investment Performance Report for a couple of the funds. I didn't see or recognize any troubling information or inaccurate dollar amounts.
Over and out...0 -
My personal policy is to go through ALL the generated Add Shares and manually round them to the 3-decimal precision. To get to the correct final 3-decimal precise result, there may be one I need to 'break the rules' and round the wrong way (0.xxx497 up instead of down, for example). In my mind, this policy prevents both Quicken and me from getting out of synch with the MF data.bobair said:Thanks Jim and q_lurker (again).
...
- After the MF Conversion was complete, there was a very small error in the total number of shares when compared to my financial company share balance. The MF conversion does its best to reconcile the AFTER conversion shares to 6 decimal digits. My financial company reports shares to 1/1000 of a share (3 decimal digits) for the funds in the portfolio (all funds < $999.99/share). To keep reconciling easy, I made extremely small adjustments to one of the [shares]Added entries for each fund - somewhere on the order of +/- .000001 to +/- .000003.
...
Over and out...0
