Defined Field

[AssetHound]

Some purchases and redemptions in a cash account are the result of buys and sales of stocks from a linked portfolio. Is there any way to define a field that would show just the total for buys and sells only, ignoring all the other purchases and redemptions in the cash account?

To me, this would be "trading income." Thanks.

[Mark]

Hi AssetHound,

You could use the new "Exclude" report filter option in version 12 to do this. Run your report with Purchases and Redemptions, but exclude cash type investments.

[AssetHound]

Dividends are in the cash account as a purchase so they would not be excluded. I'll give this a try when the new version is released. I'm not much of a beta tester primarily because of time constraints. I'm a 1.1 guy for the most part.

Looking forward to the new version. It looks like it has a bunch of good new stuff.

Thanks.

[Mark]

Hi AssetHound,

A dividend should be recorded in both the paying investment as a dividend, and the cash account as a purchase of the proceeds from that dividend. You can include dividends in your defined field if you want. If you run a report and exclude cash type investments, none of the transactions from the cash investments will be included.

[AssetHound]

OK. I've got that noted and will give it a try when the new version is released. Thanks for the help.

[AssetHound]

I don't think this is going to work for me. If I buy, for example, JNJ and one week later sell all those shares the proceeds show up in my cash account as the buys and sells. The profit on the trade will show up as a "gain" in the Portfolio Performance report next to the investment, but I usually don't show any investments with a zero share balance.

I have a custom "Position Report" that shows all current positions along with next income dates and expected annual income. This is extremely helpful to me in planning cash flow and trading income is an important part of cash flow for me.

What I'd like is to have a column in this report that sums up all the trading results for positions that are now at zero current shares (i.e., the buys and sells have been registered in the cash account). If the JNJ trade above was my only trade and resulted in $1,000 profit, this column would simply have $1,000 in the column on the row for the cash account. If I exclude cash accounts, my entire cash account goes away -- so that didn't work.

This doesn't seem to be possible although I may have missed something. Any suggestions?

[Mark]

Hi AssetHound,

I may not have fully understood your initial request. There is no built-in field that reports for only investments with a zero share balance, but what you're describing would be reported with either the "Gain" or "Tax basis realized gain" fields.

In your JNJ example the gain and tax basis realized gain of your cash would be zero. These same fields would be $1,000 for the JNJ stock though. The totals for the portfolio would be $1,000 also.

If you really only wanted this for investments with a 0 share balance you could write a user-defined field, and use the IF function, comparing on the ending share balance to report either the gain, or whatever you want if the share balance is not zero.