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.

New Google Sheets Analytics – Sector, US / Foreign, and Dividend Views

I really enjoy working with Google Sheets and the Google Finance portfolio functions.  Recently I moved tracking from excel to Google Sheets and sent links to the beneficiaries so that when they open the file, the stocks update automatically.  I made 8 of these sheets and sent them to each individual beneficiary, and learned a lot along the way.

There still is some manual and redundant work done within each spreadsheet and for me to track performance, I had to open each sheet individually.  Thus I went to work and built a summary sheet that taps into each of the 8 individual portfolios and shows performance against a 4/30/17 baseline (I just hard coded that baseline).

Recently I expanded that model to take each individual stock in any portfolio and make a consolidated view that included 1) sector information 2) US vs. Foreign 3) Yield 4) description of stock and reason for buying.  Now I can update that table in one place and re-do each of the portfolios 1-8 so that these fields are updated and consistent across each portfolio (I still have to do that, but I will in the relatively near future).  Here is a link to the data in PDF form.

 Portfolio 5/15/17 4/30/17 Change $ Change %
Portfolio One $42,377.71 $41,514.50 $863.21 2.08%
Portfolio Two $33,665.44 $33,334.33 $331.11 0.99%
Portfolio Three $17,972.62 $17,761.07 $211.55 1.19%
Portfolio Four $14,677.84 $14,625.89 $51.95 0.36%
Portfolio Five $14,479.56 $14,582.35 -$102.79 -0.70%
Portfolio Six $7,823.01 $7,834.26 -$11.25 -0.14%
Portfolio Seven $3,941.67 $3,879.91 $61.76 1.59%
Portfolio Eight $3,233.08 $3,258.89 -$25.81 -0.79%
Total $138,170.93 $136,791.20 $1,379.73 1.01%

Continue reading “New Google Sheets Analytics – Sector, US / Foreign, and Dividend Views”

Aligning Stocks to Sectors

For stock analysis, it is important to understand how stocks roll up to sectors or industries (in this context, both words are attempting to say the same thing).  Per this excellent article from Fidelity, there are three main classifications of individual stocks into sectors or industries:

There are three main classification schemas. They are the Global Industry Classification Standard (GICS), the Industrial Classification Benchmark (ICB), and the Thomson Reuters Business Classification (TRBC).  These classification schemas are designed to provide an acceptable and meaningful method for standardizing industry definitions so that comparison and analysis can be conducted between companies, industries, and sectors worldwide, and for creating benchmarks.

I am going to use the same schema used by Vanguard, since it is easy to find reference benchmarks for them (I will use the ETF performance to compare against the performance of our individual stocks against that sector).  Vanguard uses the GICS model, and as such has the following 11 sector ETF’s (and Ticker Symbols).  The GICS indexes are run by McGraw Hill (MCSI).

  1. Consumer Discretionary (VCR)
  2. Consumer Staples (VDC)
  3. Energy (VDE)
  4. Financials (VFH)
  5. Health Care (VHT)
  6. Industrials (VIS)
  7. Information Technology (VGT)
  8. Materials (VAW)
  9. Real Estate (VNQ)
  10. Telecommunications (VOX)
  11. Utilities (VPU)

There are 4 levels in the Global Industry Classification Standard.  You can see a breakdown of them here at Wikipedia.

Often I track the stocks in Google.  In fact, I am migrating everything to Google Sheets.  Google uses the ICB model (Industry Classification Benchmark), which means as you look up individual stocks in Google Finance, you see them categorized according to ICB.  This article describes some of the differences between GIC and ICB.

I am going to continue researching the differences between the two methods.  If I can find simple and representative ICB ETF’s by main sector perhaps I will use them instead of the GIC model because any time you pull up a stock in Google it brings in the ICB information automatically.  Here is a link to a summary of the ICB model as maintained by FTSE (the UK exchange).

Relative Performance

As we start to select potential stocks for the summer it is useful to see how the various US sectors have performed so far in 2015.  We can see some of the winners and losers in our own results but it is useful to view it across all the sectors broadly, even those sectors where we have less exposure.

US_Sectors_YTD

In addition to looking at US performance, it is useful to review performance of some economies where we have stock selections, notably Canada (many banks, utilities) and Australia (banks and natural resources). These stocks seem to be doing reasonably well on their own exchanges, down 3-5%, when denominated in local currency.

Australia_Canada_Local_CurrenciesHowever, when these results are converted into US dollar terms, they look much worse.  For a while the Canadian and Australian dollars were near “parity” with the greenback… since then they have fallen significantly.

Australia_Canada_US_Dollars

It is important to take into account sector performance and the US dollar vs. specific other companies currencies.  These elements are very impactful, along with the individual stocks selected.  You can always pick a stock that will go far above or below the trends of its sector and / or currency, but in general these are strong forces and most sector and country selections have a solid correlation in terms of outcomes.