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.