How should I edit categories in investment accounts?

Flotzam

I am using QC for Windows, starting with a lot of data downloaded from my broker.

The only way I have found to edit or even observe category assignments in investment accounts is with the Find/Replace function.

This is horribly cumbersome as compared to looking at the assigned category in a transaction register and changing it there, as one does with a Banking account. Quicken support confirmed for me there is no way to show the category column in an investment account transaction register, but I did not get an answer for why that is, whether its working as intended or just missing functionality that for some reason is difficult to get into the software.

So, for example -

What workflow do you use to do something like find all unbalanced investment transactions with category "_RlzdGain" , locate a matching "buy" transaction for a "sold" transaction and make sure both sides of the transaction have category "_RlzdGain" so that the Taxable Income YTD window on the tax planning screen will show a useful number? I seem to have many unbalanced assignments in this category where only the "Sold" transaction has this category assignment and the sum in this category is a large +ve number that is meaningless, because the "buy" transactions don't have this category.

I'd like to make this category work correctly as opposed to just marking it not tax relevant, but it seems really onerous to get there.

Tom Young

"but I did not get an answer for why that is, whether its working as intended or just missing functionality that for some reason is difficult to get into the software."

It has always worked this way, presumably because you typically don't get a lot of transactions in Investment Accounts that explicitly use Categories.

"What workflow do you use to do something like find all unbalanced investment transactions with category '"_RlzdGain"', locate a matching '"buy"' transaction for a '"sold"' transaction and make sure both sides of the transaction have category "_RlzdGain"

I think what you're referring to here isn't an "unbalanced" entry, but instead an entry that has no basis for a correct calculation of the realized gain? I'm lost on that "both sides" comment. A Buy is a balanced entry of a cash reduction and a basis increase. A Sell is a balanced entry of an increase in cash, a reduction of basis, and a gain or loss as the number that "balances" debits and credits. There's no realized gain associated with a Buy transaction, there's only the assignment of a specified lot, or lots, of a security to a particular sale.

If the download of information from the the broker doesn't contain every single transaction from the time the account was created until "today", then it's next to impossible to actually get "today's holdings and cumulative gain or loss correctly stated. And, as a caution, you simply cannot rely on downloads alone to actually get an Investment Account properly stated, the brokers and the software aren't, collectively, that sophisticated to get the correct accounting every single time.

Maybe you can provide some some "background" detail of what you've been able to download and maybe some screenshots of some transactions, to illustrate the problem you're facing?

Flotzam

Tom -

Thanks for the response, much appreciated.

In the Tax Planning window I see this -

The _RlzdGain category sum being reported is meangingless because at least some of the "Buy" transactions that are in my register do not have this category assigned to them by Quicken, leaving the "Sold" transaction with no basis for correct calculation of the realized gain, as you suggested in your repsonse. I have not done an exhaustive check to see if my issues are coming from simply not having enough "Buy" transactions in my history to cover everything, that may be it or part of it, but even if that ends up being the case, I would still like to understand the easiest way to modify categories for investment transactions.

My first thought to start auditing this category was to find any transaction in my investment account registers that are a Buy and see the category Quicken assigned to it, assess if I think the category is appropriate, and either change the category to _RlzdGain or find some other approach to resolve the meaningless sum that is now calculated by Quicken adding all transactions with this category assignment into one number and presenting it to me in the above report.

I don't see any way to do this without flipping around between the actual transaction register and the "Find/Replace" window, and that seems klunky, so before I head down that path I am asking if there is a better way. Find/Replace won't find based on "Action" so I can't use it directly to pull up all the "Buy" transactions. I need to find a "Buy" in the investment account transaction register and then choose some other field in it to find it in the "Find/Replace" window so that from there I can see the category. I'm hoping someone can suggest a better way.

"you typically don't get a lot of transactions in Investment Accounts that explicitly use Categories."

If I understand the Quicken tax planning implementation properly, it seems to me that categories are how Quicken collects tax related items into the appropriate buckets, and from there it seems that changing categories is the only way to move a transaction from one tax relevant line item to another. Am I wrong about that? If I'm right, it seems to me that changing categories for investment transactions would be a common thing to do when trying to make sure one is comfortable with ones tax reporting in Quicken.

_RlzdGain is being reported as an fyi in this report, as far as I can tell, it does not get associated with any tax line item, so I could just change that category to not be tax related and I think it would disappear from this report and not affect the calculations of total tax I owe. I am hesitant to do that right now because I am not confident I understand fully how it is meant to be used. I think its supposed to be a sanity check on the other gain numbers that are used to calculate taxes owed, which idea I like, so I want to take a stab at making this number match what my broker says my capital gains based on my sales activity is for this year.

I have other consistency issues in the above report that I haven't started looking into yet, eg the delta between "Div Income" and "_DivInc", but I'm starting with most glaring oddity, which is the _RlzdGain number.

Chris_QPW

I'm not an expert in this area, but I would like to point out a few things. The first is placeholders. Unless your data goes back when your account was first created, you are guaranteed to get placeholders. You should have this preference on so that you can see them:

Edit → Preferences → Investments → Show hidden transactions.

A placeholder is like a balance adjustment would be for when you didn't properly reconcile a bank account, except for shares. It just magically adds the shares to the register, but without any cost basis. As such the cost basis of any of those shares can't be determined, and as such neither can the realized gain when sold. Placeholders should be fixed to at least be an Add Shares with the cost basis entered.

The second thing I will mention is how Quicken uses its "built-in" categories in investment transactions. There are a few "Action" types (Buy/Sell/Dividend) that have no category, and it is impossible to set one. But when there is a category most of the times the category is set based on what Action you selected. For instance, I select "Inc - Income (Div, Int, etc) and enter an amount in the Dividend field. This part of the transaction will put that amount in the category _DivInc (note that this will not show up in the tax reporting by default if this is in a tax deferred account).

The point is that for the most part it is getting the right "action" set not explicitly setting a category that counts. And unfortunately, the information sent to Quicken by some financial institutions isn't always correct. For instance, for my wife's 401K account at Merril Lynch they send the monthly reinvested dividends as "dividend" instead of "reinvest dividend", and I have to go in and change it every month. It is pretty easy to detect because it throws of the cash balance.

Tom Young

"because at least some of the "Buy" transactions that are in my register do not have this category assigned to them by Quicken," and "find any transaction in my investment account registers that are a Buy and see the category Quicken assigned"

Maybe you're using either the Mac product or Canadian product? Because you seem to be seeing something that I can't see.

A Buy transaction simply doesn't, can't, have a _RlzdGain "assigned to it. Here's the full and complete accounting for the Buy, assuming you bought a stock for $1,000:

Debit (increase) basis $1,000 «« This entry goes on the balance sheet as an asset
Credit (decrease) cash $1,000 «« This entry goes on the balance sheet as a reduction of an asset

Probably somewhere along the line you may have "the books have got to balance" or "debits have to equal credits" as that's the essence of Double Entry Accounting.

The subsequent sale of this security for $1,100 is:

Debit (increase) cash $1,100
Credit (decrease) basis $1,000
Credit (increase) _RlzdGain $100

Debits equal credits.

If you were to look at the Holdings view in the Account right after the purchase (let's say you that's your only lot of this particular security) you'd see that security with a basis of $1,000. Look at the Holdings view right after the sale and that security simply isn't there any more, it was sold. The original Buy entry will look exactly the same as it did on the day of the purchase.

So it If I'd see a transaction in the Transaction List where the selling price was $1,1000 and the _RlzdGain was $1,1000 I'd know immediately that no security, a security with basis, was assigned to that sale, and go looking for it. (I'm reasonably certain I couldn't even make that entry manually as Quicken, knowing what security I was selling, would use that basis for the calculation of the gain or loss. I think even transactions downloaded from the broker would work that way too. Did you download the transactions by connecting the Account to the broker and downloading, or did you use some other method?

Assuming you're using Windows Quicken (even the Canadian version) you have to "edit" the sale to select the lot or lots that were sold, assuming they're there.

"If I understand the Quicken tax planning implementation properly, it seems to me that categories are how Quicken collects tax related items into the appropriate buckets"

It does indeed.

"and from there it seems that changing categories is the only way to move a transaction from one tax relevant line item to another. Am I wrong about that?

Yes, you're wrong. There's no Category (just to be clear, "Category" is a Quicken term that's referring to what real accountants call "income and expense accounts") associated with the "Buy" because there's no income or expense associated with the Buy. The bought item sits on the balance sheet. The basis disappears and is part of the MATH of calculating gain or loss, but as I said, that initial Buy sits in the Transaction List, looking exact the way it looked on the date of purchase, until the end of time.

What you need to do is to find that lot or lots that were actually sold in the real world, and then edit the Sold transaction to include those lots. That's what will work to decrease the _RlzdGain number.

I need to go right now but will get back to you.

q_lurker

The _RlzdGain category sum being reported is meangingless because at least some of the "Buy" transactions that are in my register do not have this category assigned to them by Quicken, leaving the "Sold" transaction with no basis for correct calculation of the realized gain, as you suggested in your repsonse. 

You are mis-stating the problem.

The _RlzdGain category sum being reported is meaningless because the shares being sold in Sold transactions do not have a basis.

In many (but not all) cases, Quicken will only sell shares if you have the shares to sell - a long position in the security. If you do not have the shares to sell, the program will often prompt you to enter a Short Sell. I'll assume the long position applies.

When selling shares, the user can specify which shares to sell - by lot. If the user does not specify, First In, First Out (FIFO) rules apply. So I will further assume you have not attempted t specify exact lot of shares sold, i.e. FIFO applies.

So for any given sale, how are those first shares being added to your account? Buy transactions or Add Shares transactions? Buy transactions by their very nature have a price and cost basis to them; they should not be a problem (unless somehow you are buying shares for $0.

Add Shares transactions have a field to specify the cost basis and acquisition dates of the shares being added in that lot. If using Add Shares, is that information filled in?

 

Jim_Harman

When you buy a security, the price you paid becomes the cost basis. It does not get a Category in Quicken because it is a transfer of cash to the value of the security.

When you sell the security, Quicken uses part or all of the proceeds to reduce your cost basis to zero and the remainder, positive or negative, is realized gain and is given the Category _RlzdGain. If the cost basis is zero, the entire proceeds of the sale will be categorized as _RlzdGain.

To see how Quicken calculates your capital gains, you can use the report at Reports > Tax > Capital gains. You can click on the gear at the top right of the report to customize it. By default accounts that have been marked Tax Deferred are excluded from the report because capital gains in those accounts are not taxed. To reduce clutter in the report, you can click on the Securities tab and de-select any money market funds you hold, because their share price is always $1.00. You will probably also want to de-select any tax exempt securities you hold.

If you have securities that have a zero or otherwise incorrect cost basis, you must find and correct the transactions where they were Bought or Added to the account.

Flotzam

From all of your helpful responses, I accept that the root of the issue must be missing cost basis for some transactions. I will look for that missing cost basis.

@Tom Young Regarding my starting assumption that the Buy and Sell transactions have categories, it came from …

When on chat with Quicken support discussing the problem, I was directed to the Find / Replace function where I see a very long list of transactions with this assigned category, I have pasted a sample below.

I am worried about digging myself a pointless hole if I attempt to change the category, I haven't tried. I am pretty sure I could do so using Find / Replace. Regardless, I will stop looking for mis-categorized transactions and look for missing cost bases instead, having been convinced this is where the issue is. My assumption that some existing transactions were mis-categorized very probably incorrect. I thought I had downloaded all the history I need to have full cost basis information, but there must be some holes.

"Did you download the transactions by connecting the Account to the broker and downloading" - Yes, by downloading.

I may be mis-interpreting what I'm seeing there, so rather than respond further regarding categories I'll ask your perspective on the above screen shot.

@q_lurker Yah, makes sense, I will look for missing cost bases.

@Jim_Harman Ditto.

Chris_QPW

Are any of your transactions, options/shorts/puts/call that kind of thing?

For a normal sold the amounts should have all been positive, which leads me to believe that the "YOU BOUGHT" above might be something like that. What Find/Replace will not show is the security action. The Investment Transactions report gives a clearer picture of what is really going on.

But in any case, I will tell you that those transactions might come up in Find and Replace, but you can't change the categories of investment account transactions that way. At least not for sells. It would be "illegal" to have a sell with any other category than _RlzdGain for instance.

Flotzam

@Chris_QPW

"For a normal sold the amounts should have all been positive"

I agree, its counter-intuitive to me as well. No, all normal buys and sells. Maybe its just something to do with how my broker is presenting the data when its downloaded and I can't do anything about it. I have experimented with changing some of the cost basis numbers and don't see that changing what is in the report, so I'm not sure how to proceed from here, or if its worth wrestling with this if what I'm seeing is an artifact of the broker provided data.

Tom Young

Getting back to the "Dividend Income" vs. DivInc, DivInc is the Category Quicken uses when you use the "Div" action, in an Investment Account, to record a dividend. You generally can't use the Investment Categories that have that leading "_" in their name. A Dividend Income Category, however, you could use anywhere else, and even in an Investment Account with a MiscInc entry.

Looking way, way, way back in my own file I have some early Dividend Inc entries, and I can't remember why.