Placeholder Investment Transactions and their impact on Income and Expense reports problematic

ciarmer

I needed to delete one of my investment accounts (due to corruption). I did this with the help of a Quicken telephone representative. Once deleted, I then added it back to my Quicken accounts and was able to download 3 months worth of historical transactions for that account.

Placeholder transactions were added by Quicken for each of the holdings with all of the placeholder transactions dated at the beginning of this 3 month period.

I modified these placeholder transactions by adding my estimate of the average basis cost for each of the holdings in the transactions.

This is now generating a good estimate of the realized gain for any sales of those securities that have occurred since the date of the placeholder transactions.

However, whenever I look at any Income/Expense report, the Income shown is overstated as the income for each of the sales shows the total proceeds of the sale rather than the realized gain from each of those sales.

Have I done something incorrectly?

Any assistance would be greatly appreciated.

Jim_Harman

My guess would be that prior to the Placeholders with the estimated cost basis, the cost basis of the securities is zero, so that if the period for the report includes the Placeholder date, the Income/Expense report will show the whole sale price as realized gain. The placeholder makes the securities magically appear in your account on the Placeholder date, with your estimated cost basis.

You can check this by clicking on Holdings in the account and adjusting the As of date before and after the Placeholder.

To make Quicken's data more accurate, you might Add the securites to the account just before the Placeholder date, with the estimated cost basis and purchase dates. This will adjust the share count in the Placeholders. When a Placeholder is zero, you can delete it.

As always, back up your data file first in case you do not like the result. 

ciarmer

I understand your explanation and it makes sense to me. In fact, I was hoping that was the problem. But unfortunately, I don't think it is. Rather than change any placeholder transactions or add any other transactions, I took the following approach.
I have generated a capital gains report for sales of shares during a one month period well after the placeholder date. No surprises here. The Capital Gains report shows the correct gross proceeds, the correct Cost Basis and the correct Realized Gain/Loss.
I then prepared an Income/Expense report covering the same one month period (well after the placeholder date). This report shows Income on a line entitled "RlzdGain". The amount shown on that line is the Gross Proceeds amount. It does not reflect an offset for the cost basis.
So, the program understands there is an estimate of the cost basis that is related to the placeholder entry and it uses that cost basis when it calculates the realized capital gain on a sale. But it doesn't recognize it when it calculates the income in an income/expense report covering the same period as the capital gains report.
Very strange...

Jim_Harman

I tried the same in a test file using the built-in Income/Expense by Category report customized to include just the investing account. The only income item it shows is _RlzdGain for the correct amount. See these screenshots




Does your report perhaps have other customizations that are throwing off what it shows?

Also for the record please let us what version and release of Quicken you are running, from Help > About Quicken

ciarmer

Interesting!
I took the steps you suggested.
I looked at one of the placeholder entries and added the same security with the same number of shares and the purchase cost with the entry dated one day before the placeholder entry.
The placeholder entry remains in the file but the shares added by that placeholder entry was automatically reduced to zero.
I made sure (for testing purposes) that I had selected a security that had a sale a couple of months later and sure enough as you suggested, the Income and Expense report now shows an income item equal to the correct realized gain for that sale (and not the incorrect sales price).
I'm left wondering why? The placeholder entry had been marked as needing a cost basis and I did enter the cost basis for the placeholder when it was first established. All of the information in the placeholder entry and my "Add Shares" entry is the same except the date on my entry is one day before the placeholder entry.
I'd love to be able to identify why this is happening as it would be far easier to adjust a setting than go through this same process with over 300 placeholder entries (since I've already added cost basis information to over 150 placeholder entries and it looks like all of that work has generated a bad result).
I did notice one thing: The placeholder entry is an "Adjust Share Balance" transaction whereas my adjustment entry is an "Add - Shares Added" entry. The placeholder entry does not allow me to change the transaction type. The "Adjust Share Balance" transaction type in the placeholder transaction cannot be changed.
I am using Quicken 2017 Premier Version R 19.1 Build 26.1.19.1
This is Quicken for Windows.

ciarmer

.... By the way... I should have said this before my last response. I really appreciate your help with this. You've already taken a lot of time and effort to assist and I wanted to let you know that I appreciate it. Thank You.

Jim_Harman

Adjust Share Balance and Placeholder are the same thing. It is a "dynamic" transaction in that it forces the share balance to match the specified number on the date of the Placeholder.

When you enter the estimated cost basis info in the Placeholder, be sure to use the Placeholder date and not the other option, which is Today. 

Do you have 300 securities in the account? That is a lot of placeholders to deal with.

You have not said exactly what Income/Expense report you are using, but if you have the cost basis entered correctly in the Placeholder and the report set up correctly, it should match the capital gains report as it did for me.

ciarmer

When the placeholder appears at the bottom of the account register, there is a note on it to "Enter Cost" or "Enter History".
When you click on that you have two options. One option is to "Enter Missing Transaction" and the other is to "Estimate Average Cost". The approach that I had taken was to "Estimate Average Cost". I just tried selecting the other option ("Enter Missing Transaction"). When I do that, the transaction entry screen pops up with a "Buy - Shares Bought" transaction where it asks you to fill in the number of shares and the price paid. If I do that, the correct results show up in the Income/Expense report. If, however, I click the "Estimate Average Cost" option and enter the "Cost" or the "Price per share", the Income/Expense report generates an income item equal to the total sales proceeds rather than the Realized Gain. I wonder if you adjusted your sample placeholder by entering the average cost or by entering a missing transaction. I can tell you that when I take the "Enter Missing Transaction" approach, I get the same result you get in the Income/Expense report.

To respond to your questions/comments:

Yes. I have a lot of transactions. About 10 years worth of history and this particular account was corrupted in some way so it needed to be restored from a backup and then deleted and rebuilt. So I ended up with over 300 placeholders all dated in early March, 2019. The rebuilding process was done with the assistance of Quicken Support technician. Not sure if it was the proper way to do it but that's what I'm left with.

I am using Reports/Spending/Income and Expense by Category.
When I did the work of entering the estimated cost basis information in the placeholders, I used the Placeholder date (which comes up automatically); not "Today".

I can't spot anything that I'm doing wrong here. Nor do I see any settings in the Income/Expense report that seem to make a difference in this result.

Jim_Harman

You can see from my top screenshot that I used the "Estimate average cost" method to adjust the Placeholder. So it appears that you and I are using the same steps but for some reason you are getting different results.

I wonder if perhaps there are one or more additional placeholders that are affecting this security. Have you made the Placeholders visible in your account's transaction list by going to Edit > Preferences > Investing Transactions and checking the Show hidden transactions box? Then you can sort the transaction list by security (click on the Security column header) to see all the transactions that affect that security.

The settings in the latest placeholder for the security will be the ones that affect the share quantity and cost basis going forward.

ciarmer

Placeholders are visible.

As mentioned, there are numerous placeholders since the account had been corrupted (could not be accessed without crashing Quicken). When the account was re-established only 3 months of history was downloaded from Fidelity and placeholder entries were included for all holdings as of 3/6/2019. There are over 300 of them. I've sorted the account by security and confirmed that for each of these securities the placeholder entry on 3/6/2019 for each security is unique.

One other item that might be impacting the results. I did not delete the corrupted account. I did stop downloads to that account. I also set that account display settings so that it would be separate, hidden in the transaction lists and hidden in the account bar (in other words I checked the first three boxes in the Display Settings for that account). I did not "Close" the account. I set these settings with the assistance of the Quicken representative on the help line. He made no recommendation about closing the account and I didn't ask about it.

What this means is in addition to all of my other accounts, I have another account (Investment Account A) which includes transactions from years ago all the way through early June 2019 (the date the crashing started). This account no longer gets updated with downloads. This account was replaced by "Investment Account B" which has transactions from 3/6/2019 to the current date (3 months was the most I could download from Fidelity). I'm wondering if it is possible that a security's history in Investment Account A is somehow impacting this process as I try to work on Investment Account B. There is also an overlap period here where some transactions in one account are duplicated in another account.
Should I have "closed" the old corrupted investment account? Would closing that account eliminate the possible duplication of transactions in the establishment of a basis cost?

ciarmer

An interesting observation.

I tried entering the average cost for one of the placeholder entries. The result is that the Capital Gains report (under investment reports) shows the correct capital gain for this security (as it shows the gain and not the proceeds). The expense report incorrectly shows the proceeds rather than the gain.

I then entered a "shares added" transaction for this security (with the correct number of shares and the cost per share). This action reduced the placeholder transaction to zero shares. In addition, the capital gains report continues to show the correct gain but the expense report now also shows the correct gain.

I am having a lot of trouble trying to understand what is going on here.

Jim_Harman

If you do not intend to eventually go back and enter the transaction history that led to your current hoildings, I think the Shares Added approach is better than messing with the estimated cost in the placeholders.

That said, it is still not clear to me why the estimated cost approach behaves differently for you than it does in my test file, where it gives the expected result in the income/expense report.

ciarmer

Thanks so much Jim.
You've been very helpful.

I simply cannot go back and enter the transaction history. Too many years and too many securities to deal with. I'm plowing through the placeholders and while it is a lot of work it is manageable.

I'll never know what led to the data corruption on this one account. I offered the Quicken phone representative my crash reports but he said those would need to be reviewed by the developers and who knows how long that would take. So, I've resigned myself to just dealing with it.

I'm not sure if you had the time to read my previous post in this thread (6/11). If you have, does it make any sense for me to "Close" the old account in Quicken?

Jim_Harman

I would not close the account, I don't think that would affect what you are seeing.

I don't know what state your old account is in or what sort of corruption you had, but if you haven't already, you should probably Remove any holdings that are left in it and zero out any cash balance. You should also disable downloads for that account if you have not already.

-or-

If the holdings (share balances and tax lots) in the old account were accurate, you might try - on an experimental copy of the data file from before you created the new account - creating a new, empty account and using Shares Transferred between Accounts to move the holdings from the old account to the new one. If you set the date for this to just before the transaction downloads started in the current new account, it should empty out the old account on that date.

Before downloading into the new account, go to Holdings and check to see if the share balances and tax lots are correct. There is a chance that everything will move over intact and you won't need any placehoders or manual Adds at all. It's worth a try.  

ciarmer

Wow - I would love to try that on a test file. My file and all of my accounts were fine until a recent download from Fidelity. At that point, the file was still readable and I was able to run the file through a validate and repair with no issues (save for some missing stock splits). But whenever I clicked on the account it would crash quicken.

Once I restore a file from backup (for a date prior to the download that created the issue), how do I go about making this happen. IE - How do I go about

"creating a new, empty account and using Shares Transferred between Accounts to move the holdings from the old account to the new one. If you set the date for this to just before the transaction downloads started in the current new account, it should empty out the old account on that date. "

It sounds simple but I probably just need a little more step by step detail on how to do this. I have loads of backup files (weekly going back quite way). But I don't know how to "move the holdings from the old account to the new one".

Jim_Harman

If you have a recent backup from before the corruption, why not just restore that, make sure it validates OK, and download again from Fidelity?

Make sure it is set to not automatically accept downloaded transactions so you can review each one and not accept anything that looks suspicious. Edit > Preferences > Downloaded transactions > Automatically add to investment transaction lists should be un-checked.

Maybe you won't get the corruption in the first place and you can just keep using that version of your data file.

ciarmer

Thanks, Jim. That is one of the first things I tried. I restored the entire file from a day or two before the dreaded download that somehow led to the problem.

Once I had a pre-crash file restored, I downloaded transactions again and I went through each and every downloaded transactions one at a time (as I've always done) to satisfy myself they were correct and consistent. Turns out on that particular day they were all simple dividend payments. Got about half way through and accepted the next one. Suddenly, all of the transactions in the register above the downloaded transactions disappeared. The register was still there with all of the column headings but no transactions appeared in the register portion of the screen. I could still search for transactions in that account and Quicken would comply with a list of the transactions that met the search criteria but when I dismissed the search results I was still left with a blank register. Once I left that screen (by going to a different account) I wasn't able to get back to the problem account (even though I'd still be able to see the correct account balance for the problem account. But, each time I'd click on that account on my home screen, Quicken would crash with some message about "Sorry, Quicken needs to close. Etc.....

Over the course of the few months prior to this crash stuff happening, that particular account was very slow to respond. It was almost like it was getting too large with too many historical transactions.

I can (and will) try it again. This time I'll go back a couple of months in selecting a backup and then try the download process again. I'll try that today (after making a backup of my current file). I'll report back. Let me know if you have any comment on what I just described. I believe I described this behavior in a post about a week ago but didn't see any replies to it. I concluded that it was something that was unique so I called Quicken support. The rest is history.

Jim_Harman

How many transactions were in that account prior to the crash? You can see this from Tools > Account list in a pre-crash file.

Also please go to Help and hold down Ctrl while clicking About Quicken to see other stats about your file and let us know the numbers.

If the pre-crash account was huge, you may be able to keep things working by Archiving the closed positions. This is accessed by selecting Archive transactions form the acount's gear menu. Read the description carefully to see what it will do.

There may be an issue with archiving and the length of the account name where you need to make the account name reasonably short - 22 characters or less - for the archive to work. See this discussion
https://community.quicken.com/discussion/7744536/could-not-create-the-archive-account-error-message

ciarmer

I'm now working from a backup file from a month ago and here are the stats. Note that the problem account in this particular file responds very slowly when I ask it to display anything. My computer has a lot of memory, has an i7 processor, solid state drive, etc. Nothing else on the machine ever hesitates to respond quickly (except this one account....)

Problem account had 16, 312 transactions

Size of QDF File: 69747K

Accounts: 13 (that's what it says but I know the number of total accounts is about triple this number)

Categories: 342

Memorized Payees 970

Securities 1027

Transactions: 33, 957

System Resources:
Available Memory: 2097151K
Total Memory: 2097151K

Transactions go back 10 years in the file.
I've tried to create Year End Files but that didn't seem to reduce the number of transactions in the investment accounts.

If there were a way of archiving stuff that is over 5 years old (now it goes back about 10 years or so), I'd be very (VERY) happy to do that as long as it doesn't mess up current balances and as long as the last couple of years of investment results and income/expense reports were still fairly accurate. Lots of transfers have taken place between these accounts over the past 10 years so I've been reluctant to take actions which delete historical transactions.

I just ran a validation on this file I've restored from backup. The results are similar to the prior backed up files with these summary results:

- Quicken found an invalid transaction and removed it

- Quicken found a damaged scheduled reminder and removed it

-QEL - No read errors

-QEL - removed 26 QEL records for payments not in register

-A list of about 20 stock splits that might be missing (I always look at these and need to make adjustments to holdings to reflect the correct number of shares. Often Quicken doesn't interpret the dowloaded Fidelity transaction as a split and eventually, a placeholder transaction is suggested by Quicken (which I usually fix.

- A number of securities which contain prices for a date in the future (1/18/2038). (In one of my prior attempts to fix the crash issue, I went into the Security Detail View and updated the price history to delete these future prices. After fixing all of them, the crash still occured - so it doesn't seem like that is the problem.

ciarmer

By the way, "Archive Transactions" does not appear in my gear menu. There is a help file I've seen out there that states that the Archive Transactions option should be there for Q 2017 and Q 2019. I don't see it in my Q 2017.

ciarmer

I just posted this question on the forum.

https://community.quicken.com/discussion/7855144/archive-transactions-menu-option-missing/p1?new=1

Jim_Harman

Sorry, I missed that you are running QWin 2017. The Archive function is only in the subscription  version of Quicken. So to avoid all those placeholders either you upgrade or we are back to the earlier idea of moving securities between accounts. 

ciarmer

Jim - I'm actually inclined to upgrade. Seems a small price to pay given the time I've invested to attempt to fix this file.

May I ask - based on what you see in the results of my File Validation and the current size of my account in terms of number of transactions, etc. can you give me your input on the following:

1. Anything you see in the validation file that might be contributing to the crash?

2. Do you think the archive process will improve my situation?

In my opinion, I think the size of the file (number of transactions) is contributing to the crash.

Jim_Harman

Others may have more experience with large accounts and slow performance, but I think the number of transactions in your account is contributing to that.

Your overall file size of 69 Meg is not  a concern.

The archive process would leave you with two accounts - a zero-balance one with all the transactions for your closed positions and an active one with the transactions for your current holdings.

The Shares transferred process would also leave you with two accounts but the zero-balance account would have all the transactions and end with Removed transactions to move your holdings as of the transfer date to the new account. The new (active) account would would start with Added transactions for all the tax lots for your current holdings. 

There are pros and cons to each approach. Either should help with your performance issues. The shares transferred would give you a smaller active account but you would have to refer to the zero-balance account to see your transaction history.

I don't see anything suspicious in the validation log, but I would delete the future-dated prices. 

q_lurker

@ciarmer:  In my experience with several versions of Quicken through close to thirty years, number of transactions in an investment account is a key factor in degraded performance.  I have used a number of 10,000 transactions as a general critical value, but there is due consideration to hardware, other software, OS, and user expectations.  10,000 is also not a make-it or break-it line. 

In my experience with my own files, I saw degraded performance some 8 years ago.  I opted for the Shares transferred direction and the immediate difference was definite.  That account was at 12,424 transactions and that formed my basis for 10,000 transactions as a baseline value.  Other users have suggested number of securities,and number of lots owned (past and present) as possible factors.  

It is certainly plausible to me that slow performance at say 10,000 transactions could degrade to crashes at 16,000 transactions.

I am using QW2017 currently and have no experience with the newer "archive" feature.  I have had no significant issue with the data or use of the file since my Shares Transferred step.  

The other stat of note in your data is the 970 memorized payees.  I believe high values for that parameter have also been linked to crashing of the program and poor performance overall (not to one account).  

Jim_Harman

@q_lurker makes a good point about the memorized payees. I currently have 269.

To reduce that number without a lot of manual intervention, you can go to Edit > Preferences > Data Entry and Quickfill and check Remove Memorized payees not used in the last (I set it to 18 months). You can also un-check Automatically memorize new payees.

In  Downloaded transactions check Use my existing renaming rules and set up renaming rules so that credit card transactions for payees like Starbucks #1234 all get changed to Starbucks going forward. 

ciarmer

Thanks so much. I appreciate the time that you both have spent on this.

A basic question.... What is the "Shares Transferred" approach. Sounds like an approach to reducing the number of share transactions in the file? I'm stopping short of upgrading to Q 2019 because that might open up a new set of issues (but I recognize that at some point soon I may need to do that). I'd just like to explore other approaches if they are possible.

ciarmer

Wow - 1/2 of the memorized payees disappeared when I set it to delete those not used for 18 months.

ciarmer

Jim - I apologize - I see that you discussed the "Shared Transferred" approach in one of your messages yesterday. I'm going to play with it on a restored backup file just to get familiar with how it works.

Jim_Harman

The key is to first create a fresh, empty brokerage account.

Tools > Add Account > Brokerage.
Click on Advanced setup at the bottom.
Select I want to enter my transactions manually.
Name the account.
Statement ending date is the day before you will be transferring the shares (just after the last transaction in the old account), amounts are zero
Leave "What securities" blank
Are you sure? Yes.
Done.

Go to the old account.

Gear > Edit account details
Online Services > Deactivate. This prevents any more downloads to the old account
Click on Enter Transactions
Select Shares transferred between accounts
Date = the day you want to do the transfer
All securities
Done

The screen will start flashing as it enters a Removed for each holding in the old account and an Added for each open tax lot of of each security in the new account. Don't despair, this will take some time for your 300 postions. 
 
Hopefully it will not crash in this process.

When it is done, you should have no shares in the old account and all your holdings should appear in the new account, with their correct cost basis. If there is a cash balance in the old account, transfer it to the new one.

Now you can click the gear in the new account and set up online services. Note the dates of the downloaded transactions and do not accept any from before the transfer date. Do not let Quicken create any Placeholders.

Hopefully there will not be a gap of missing transactions between the transfer date and the earliest downloaded transaction. If there is, you will have to enter these transactions manually.

Good luck, and let us know how it goes!
 

ciarmer

OK - I've done a quick run through using a restored backup file. I've done it with one security and I see the result. I wanted to make sure I understood what was happening.

So, in summary, in the end I'll have a new investment account which I can use for future downloads with a solid set of holdings, acquisition dates and cost bases (as accurate as they were in the original file) and I'll still have access to all the history that is still in the old account (which will no longer be updated).

I'm sure this will take quite some time but in the end if it works it sounds like it will be worth it. I'm going to attempt to do this.

Thanks so much. I'll report back.