Google Sheets Integration

I have been using Google Sheets to update these portfolios and it has been an excellent experience.  I can share the spreadsheets with the beneficiaries and they can see a real-time view of their portfolios on any device (phone, PC, tablet) through Google Sheets.  The only minor updating item is the cash in the money market account which is a product of recent dividends.

It used to take me a long time to update each spreadsheet.  I had to do the following items:

• look at the performance of stocks that were sold.  Now I use the “Google Finance” formula to get the current price of stocks that have been sold and I have it in a text statement embedded with the purchase and sales price for that stock
• I don’t update the current prices for any stocks; that happens automatically

I try to centralize functions.  One item you can’t find via Google Finance is “dividend yield”.  I have a central sheet where I update yields once and then can copy that throughout all of the cells.  I need to copy a block of text into the spreadsheet so that a VLookup can be applied against the data, with the ticker being the primary key.  This also works for the current description of the stock’s status (commentary).

Portfolio Five Updated July, 2017

Portfolio Five is almost 5 years old.  The beneficiary contributed $4000 and the trustee $8000, for a total of $12,000.  The current value is $14,522, for a gain of $2522 or 21%, which is about 4.3% / year when adjusted for the timing of cash flows.  Go here for details or see the link on the right of the page.

We are looking at several stocks.  ConocoPhillips (COP) has been hit hard by the decline in oil prices, but has kept a modest dividend.  Spirit Airlines (SAVE) has been a great stock but has come down recently.  Tata Motors (TTM) provides some diversity in terms of currency and emerging markets but is down about 20% from highs.  Juniper Networks (JNPR) has been doing well but long term they face risks from Amazon (AMZN) and the cloud.

 

Portfolio Four Updated July, 2017

Portfolio four is almost 8 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current value is $15,082 for a gain of $3,082 or a 5% rate of return, adjusted for the timing of cash flows.  See detailed PDF here or go to the link on the right side of the page.

The portfolio has some technology stocks that are doing quite well, which include Box and Oracle (you could also call Tesla a partial technology stock, as well).  The oil stocks of Devon, Shell and Statoil have generally been hit by the continued fall in oil prices.  The stock prices of Shell and Statoil have held up better than Devon because they kept their high dividends; Devon cut their dividend and has continued to fall (there are other factors at play as well).

It is important in all these portfolios not to just look at the current share price when compared with the purchase price; you need to take into account dividends, as well.  The oil stocks look bad on stock price alone but when cumulative dividends paid are tracked as well, the situation is much better.  That does not mean that we should hold stocks just for the dividends, but it is a very important factor in long run performance.  To date this portfolio has earned $1735 in dividends, which makes up more than half of the total return earned to date.

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%

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).

Portfolio 2 Updated April 2017

Portfolio Two is 12 1/2 years old.  The beneficiary contributed $6500 and the trustee $13,000 for a total of $19,500.  The current value is $33,334 for a gain of $13,834 which is 71% or 7.4% / year when adjusted for the timing of cash flows.  You can see the portfolio detail here or go to the links on the right.

This portfolio is different than the other portfolios because it has shifted to ETF’s and CD’s.  The ETF’s are broadly tied to the US and non-US stock indexes.  There is also a CD that returns 1.55% / year for $10,000 in the portfolio.

Since markets have gone up over the last year, this portfolio has done well (it tracks the market).  All of the ETF’s are near 100% of their 52 week high, which means that they are at or near their highs and the indexes have been rising continually over this time period.

Unlike the other portfolios, which are invested in individual stocks, these ETF’s do have annual expenses.  You can’t “see” the expenses because you receive the returns “net” of expenses, but this is disclosed.  Over the 1 1/2 years that we’ve had this portfolio the low cost ETF’s cost $86, which is very low for a portfolio of over $30k.  If you go back ten or fifteen years ago mutual funds would routinely cost 2% or more each year which would be $600 / year on a portfolio of this size.  It is a testament to the efficiency of ETF’s (which drove competition in the mutual fund markets, mutual fund expenses have been driven down proportionally, as well) that these sorts of rock bottom expenses are now commonplace if you know where to look.

In a technical note, the CD does fluctuate in value (a bit), but I record it at cost ($10,000) since we intend to hold it to maturity.  The cost fluctuations thus do not matter.

Finally, in another note, when I moved this portfolio over to Google Sheets, I noticed that I had been overstating the contributions in the “cash flows” calculation since 2012.  Thus the recorded return since inception now looks higher.  The value of the fund was always correct it was just the calculation of total gains to date that was incorrect.

 

Using Google Sheets for your Portfolio

These directions are specific to the portfolios that are being shared.  I likely will build a “public” version and link to it if someone else wants to leverage it.

The Google Sheets application is easily installed on your ipad or iphone.  From there you should just click on it and select your portfolio.

Alternatively, you could open it up on the web by going into Google and signing in and then bringing up the “sheets” application from Google Docs.

When you open the spreadsheet, there is a disclaimer that the financial data is updated 20 minutes late.  You can click on the “x” on the bottom to make this disclaimer go away.

Every time you open the application it will attempt to go out to the web and update all of the stock prices.  The prices are in several places (including the “text” under the current price on sold items) so it may take a little bit of time on portfolios with lots of stocks.

The tab that tells the story and history of all the stocks, the cash, investment to date, dividends, and sales is the “summary” tab.  This is the tab to look at if you want to see and study the entire picture.

Note that for any application, if you open it up on your phone or your ipad, you can resize the screen and it automatically zooms in or out.  This is a great feature and recommended.  If you look at it on your PC or Mac you need to zoom in or out by using the zoom function or + / – depending on how your machine is set up.  Or you can just use your mouse or arrow tab to get around.

Day to day, I recommend looking at the “analytics” tab.  There are multiple tabs in the sheet and it may or may not open up to that tab first.  You can just click on the tab at the bottom for that one to come up.

The analytics tab has all of your stocks.  You can see how the portfolio has changed that day.  Increases are automatically in green and decreases are in red.  The increases and decreases are in percentage term and in dollar terms (your net gain or loss for the day).  At the bottom of the analytics column you can see the total change across all of your stocks for the day.

In the box below the green / red section of your stocks on the analytics tab, there are 3 other benchmarks.  The first one is the S&P 500 index.  The second benchmark is for the non US stocks (unhedged, so it contains both stock price performance and the impact of currency changes).  The third benchmark is the US dollar vs a basket of foreign currencies, which shows the direction of whether the US dollar is going up or down.  Generally, if the US dollar goes up, there is a decrease in the value of your foreign stocks.

To the left on the analytics sheet, there are 4 items in a different box.  They start with the % of your stocks that are US vs. foreign, the % of your portfolio that is tied to the largest sector, the % of your portfolio that is in high dividend stocks (about 3% or more in dividend yield) and the % of your total portfolio that is in cash (not invested in stocks).

You can also see the 52 week high and low and the % of 52 week high that the stock is currently at. 100% would be the highest value and stocks in the 90% means that they are near or testing a 52 week high.