Portfolio Overview And Recent Market Activity

Since we moved the portfolios to Google Sheets and integrated them to a single data source, it has been much easier to review overall performance (in aggregate) and performance within a single one of the 8 portfolios.  Like the rest of the overall market, we hit a peak in September at around $180k and have since declined to $168k (a loss of $12k or about 7%).  Traditionally, October has been a month where difficult events occurred, so for whatever reason it happened again in 2018.  Generally the Tech giants have been hit hard, and although we are not invested in many of the most prominent names (Amazon, Google), we do have some investment in Facebook as well as in the Chinese names like Alibaba, who also were impacted.  When this settles down a bit (likely after the elections, for good or ill) we will look through the winners and losers and see if there are any significant actions to be taken among portfolio stocks.  Note that even with recent losses, we are still up from about $137k in April 2017 to $168k in November 2018, which is approximately 20% (it is a bit less than the math would first appear because of the net effect of incremental investments and withdrawals during the period).

Using My Consolidated Formula Model in Google Sheets

I moved the portfolios to Google Sheets to take advantage of Google’s ability to use financial functions like “price” by ticker so that my stock quotes could be updated with live data when you click on the spreadsheet. These sheets can also be shared with anyone (and it is free unlike Office 365).

I have been working with these sheets for a while to 1) simplify how they work 2) build them in such a way that I can “copy and paste” into other google spreadsheets 3) link the sheets together so that I only have to do selected work one time.

We are going to start with the “All Stocks” view because this page is crucial to understanding the entire model, including what can be linked to Google Sheets and what needs to be created and / or input from another source. Once this page is updated with key information, however, it can be used by all the other sheets and I only have to input data or do analysis one time and it automatically updates everything across all the portfolios.

The “ticker” symbol is used to derive values in google sheets using the “GoogleFinance” formula.  I manually added (one time since this page is essentially a central database for all the other portfolios).

For each stock I use the GICS classification system (used by Vanguard) which I describe here.  I map each of the stocks using this model as follows:

  • sector
  • industry group
  • sub industry
  • type (US or foreign)

“Yield” is something that the GoogleFinance formula doesn’t give you.  This is the dividend % for the stock and it is important information.  I look this up, manually, stock by stock, using google or yahoo finance or marketwatch.  Some of the ADR’s are harder to find.

I also classify stocks by “dividend level” based on yield which I made up and categorized stocks as follow:

  • >3.9% – very high
  • >2.7% – high
  • > 1.4% – medium
  • Less than 1.4% – low

I have a “description” field which is my all in summary of the stock.  This summary takes into account our original plan for the stock, its price against its 52 week high, potential as a takeover candidate, potential for a dividend cut, or any other information I feel is relevant.  I write this once for each stock in any of the 8 portfolios and propagate it through all the portfolios.

The price is derived by the GoogleFinance function and the 52 week high.  Then the stocks current price is compared to the 52 week high and shown as a percent.  The categories are:

  • If > 94% of 52 week high then “near high”
  • If > 90% of 52 week high then “doing OK”
  • If > 80% of 52 week high then “at risk” (and colored yellow by conditional formatting)
  • If less than 80% of 52 week high then “on watch” (and colored red by conditional formatting)

This is the single most important page and contains most of the logic used for analytics as well as the summary description of how that stock is doing “all in”.  In future posts we will review the other pages.