#HYHRD: exploring my spreadsheet $CLM $CRF $MORL $PHK $PSEC $ORC



I’m Pat Rosenheim, a.k.a. the PandA Trader.

I would like to take a look at my Google spreadsheet (click to open in a separate window/tab) and some of the useful functions it provides.

Before we get into that, however, I’d just like to mention that you could have your very own copy. I think you need a Google or Gmail account, and what you would do is click on File, then click on Make a copy, and you’ll have your very own copy which you’d be free to edit and adapt to your liking!

So, first of all you’ll notice at the bottom there are many tabs for different areas of the spreadsheet.

The first things I’d like to show you is on the Holdings tab, and can be seen when you scroll down. Scroll right past the summary with gauges that show YOC, Total Cost & Value, etc. Keep scrolling down past the incredibly useful but somewhat hard to read comparison of Position Size (Current Value) / Position Cost, until you reach the next chart with the default sort set to Total Return. I have clicked on the heading Position Size Ranking and it sorts from highest to lowest. (I’m not sure if it will sort for you, because you don’t have edit capability. However, if you have made a copy for yourself you certainly can!) Just sayin’…


Clicking on the next heading, % Change Since Purchase Rank shows exactly that;


Click on Rank By Yield and you can see how my holdings compare to each other. Keep in mind that some holdings, especially the ETN (Exchange Traded Note) MORL pays widely varying dividends resulting in widely varying yields. Here’s the current Rank by Yield;


Next we’ll take a look at what I consider a very important metric and the default for this chart, Rank By Total Return;


Here you can see all holdings sorted by Total Return, and to the right the Type of Security, the payment Frequency, and the actual Total Return in my portfolio.

Further to the right of that you can see Total $ Gain/Loss, Rank By Income, and Rank by Gain/Loss. I’d like to point out here that losses displayed are unrealized losses, meaning they are somewhat imaginary if I never sell at a loss. I believe Warren Buffet holds a similar view. No matter, I’m not Warren Buffet.

I do like to sell to capture Capital Gains, especially since most of our portfolio is held in Roth IRAs so the tax consequences don’t really matter that much. Anything showing a loss can continue to show a loss for all I care, as long as they continue to provide me with an income stream. I have a saying; “If they pay, they can stay”.

If you continue to scroll down on this first tab, you’ll come to another chart that displays an Annualized Return Visualization (click to embiggenize!);


It is clear to see that PSEC is the poorest performer in my portfolio, but still pays a greatly reduced dividend. I’m not in a hurry to sell at a loss, although I will if the need arises.

Continue to scroll down on this first tab and you’ll come to another chart, this time of Total $ Gain/Loss by SYMBOL;


It’s easy to see that my biggest losers are (in order); PHK, PSEC, & ORC. It also appears that I don’t have that many real winners, but here is where appearances can be deceiving because, as I have mentioned before I like to capture Capital Gains whenever prudent. This is where I realize gains but my losses remain unrealized.

If we take a look at the tab just to the right of that first tab, we can see all kinds of info about dividends in our portfolio.

To the right of the second tab is where I record the current year’s dividends, and also generate that charts and table for my blog page; Dividends Received.

The 4th tab is where I record dates pertinent to the receipt of dividends, the amount of said dividends, and any ‘special’ or otherwise labeled dividends.

Just to the right of that is whee I track the cash in the accounts.

The eight (8) tabs to the right of that correspond to our eight (8) accounts, and show individual account holdings, performance, etc.

The 2nd to the last tab shows account balances and calculates any MRD (Minimum Required Distribution) if necessary (There are hidden tabs which continue this functionality and will be unhidden when required).

The final tab is an experiment with a Google sheets add-in called Intrinio. They charge for most data, but what is displayed is free for me to use.

This concludes the #HYHRD spreadsheet part of our posting. I hope you have found the information useful, not boring, and I wish you success in your endeavours.

If you feel inquisitive, You might want to check out the sheet I made to calculate Rights Offering prices for CLM & CRF and is currently estimating 2018 distribution rates.

You can find that spreadsheet here; https://docs.google.com/spreadsheets/d/1jX7-2DV2Xu50ngRXx5gzv19NCeubfQq2zacuM09x_cI/edit?usp=sharing

Live long, and prosper!

But, as always, “Don’t be hasty” as Treebeard would say. 😉


I’m not telling anyone to buy anything or giving anyone any advice, because that’s illegal. You see, I have no letters after my name, like RIA, CFA, etc. I SIMPLY DO NOT GIVE ADVICE. I only tell (and show!) what I do. You, like me, are all alone in this.

And remember, always do your own due diligence!

panda_wildePat Rosenheim
(PandA Trader)
High Yield, High Return Dividend



About PandA Trader

I am, I think... "Disobedience, in the eyes of anyone who has read history, is man's original virtue. It is through disobedience and rebellion that progress has been made." -- Oscar Wilde
This entry was posted in dividend portfolio. Bookmark the permalink.

Don't be shy! Leave a Reply...

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s