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%

Here is another view that I created by portfolio that shows it by sector as well as split between US and Foreign stocks.

 Portfolio  Sector US Foreign Grand Total

1

CASH $447.00 $447.00
Consumer Discretionary $4,701.12 $2,696.01 $7,397.13
Consumer Staples $6,531.07 $1,913.92 $8,444.99
Energy $3,063.60 $984.50 $4,048.10
Financials $1,679.04 $1,679.04
Health Care $1,208.70 $1,208.70
Industrials $2,753.80 $2,753.80
information Technology $6,049.50 $8,772.18 $14,821.68
Utilities $1,576.88 $1,576.88
1 Total $25,122.97 $17,254.35 $42,377.32

2

CASH $646.00 $646.00
CD $10,000.00 $10,000.00
ETF $12,967.94 $10,050.75 $23,018.69
2 Total $23,613.94 $10,050.75 $33,664.69

3

CASH $839.00 $839.00
Consumer Staples $1,678.38 $1,678.38
Energy $2,013.12 $2,013.12
Financials $1,988.80 $1,988.80
Industrials $3,521.58 $3,521.58
information Technology $1,351.71 $5,331.23 $6,682.94
Materials $1,248.00 $1,248.00
3 Total $7,130.21 $10,841.61 $17,971.82

4

CASH $918.00 $918.00
Consumer Discretionary $1,263.52 $1,263.52
Consumer Staples $2,114.06 $2,114.06
Energy $572.40 $1,341.58 $1,913.98
Financials $974.80 $974.80
Health Care $725.45 $966.96 $1,692.41
Industrials $1,220.56 $1,206.60 $2,427.16
Information Technology $2,567.32 $2,567.32
Materials $806.54 $806.54
4 Total $10,187.85 $4,489.94 $14,677.79

5

CASH $413.00 $413.00
Consumer Discretionary $1,118.70 $1,118.70
Consumer Staples $1,953.16 $1,196.20 $3,149.36
Energy $853.92 $853.92
Financials $1,765.20 $1,765.20
Health Care $725.45 $725.45
Industrials $2,500.44 $998.48 $3,498.92
information Technology $1,071.35 $1,883.17 $2,954.52
5 Total $7,517.32 $6,961.75 $14,479.07

6

CASH $616.00 $616.00
Consumer Discretionary $881.40 $881.40
Consumer Staples $863.30 $863.30
Energy $1,326.56 $577.60 $1,904.16
Health Care $725.45 $725.45
Industrials $1,001.52 $1,085.94 $2,087.46
Information Technology $744.56 $744.56
6 Total $4,532.83 $3,289.50 $7,822.33

7

CASH $116.00 $116.00
Consumer Staples $850.88 $850.88
Industrials $1,054.12 $1,054.12
Information Technology $827.47 $1,092.60 $1,920.07
7 Total $1,997.59 $1,943.48 $3,941.07

8

CASH $81.00 $81.00
Consumer Discretionary $813.60 $813.60
Consumer Staples $850.88 $850.88
Health Care $659.50 $659.50
Information Technology $827.47 $827.47
8 Total $1,567.97 $1,664.48 $3,232.45
Grand Total $81,670.68 $56,495.86 $138,166.54

Read more of this post

Portfolio Four Updated April 2017

Portfolio Four is 7 1/2 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current fund value is $14,444 for a gain of $2444 or 20%, which is 4% / year when adjusted for the timing of cash flows.  You can see the detail here or go to the links on the right.

We have a couple of stocks on watch.  Devon Energy (DVN) got waxed with the downturn of the oil industry a couple of years ago and has recovered a lot of its losses but cut its dividend significantly in the interim.  DVN seems to be plateauing and is thus on watch.  Oracle (ORCL), the technology company famous for its database software (although they own many other products, including cloud software) is in a long term price and technology war with AWS and other cloud providers (including Microsoft’s Azure).  They have been performing well but are on watch as a result.

There are analytics on the “analytics” sheet.  A few worth paying attention to is the price as a % of its 52 week high, which shows its relative strength over the last year (obviously anything near 100% means that the stock is moving up and hitting highs regularly).  The portfolio is 68% US stocks and 44% of them are “high dividend” (meaning a dividend around 3% and higher).

 

Portfolio Five Updated April 2017

Portfolio 5 is 7 1/2 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current value is $14,151 for a gain of $2151 or 18%, which is about 3.6% / year adjusted for the timing of cash flows.  You can go here to see the portfolio or go to the links on the right side of the page.

The portfolio is about 50/50 with US and foreign stocks.  Almost half the stocks are considered “high dividend” with a dividend of near 3% or greater.  Gilead (GILD) is a recent drugmaker purchase and Anheuser Busch InBev (BUD) are both pretty well run companies on or near watch.

On a side note, this is one of the first portfolio to be almost totally run by formulas in Google Sheets.  I incorporated vLookups and re-arranged the sheets a bit to have more of the information on buys and sells populate automatically.  Due to these changes, it will be much easier to update this portfolio in the future and it will be the template that I will apply to the other portfolios as I migrate them to Google Sheets.

Portfolio Six Updated April, 2017

Portfolio 6 is 4 1/2 years old.  The beneficiary has contributed $2500 and the trustee $5000 for a total of $7500.  The current value of the portfolio is $7806 for a gain of $306 which is about 4% or 1% / year over the life of the fund.  You can see a PDF of the details here or on the link to the right.

The portfolio is generally doing well.  The stock mix is 55% US stocks and the largest exposure area is oil, at 27% of the total.  High dividend stocks (3% yield or greater) make up 44% of the portfolio.  We sold off Coca Cola Femsa (KOF) recently.  The oil sector has generally been hit by the decline in oil prices per barrel but they have come up significantly from their lows.

Portfolio Six Updated January 2017 – Tax Time

Portfolio six has been around for about 4 1/2 years, with the beneficiary depositing $2500 and the trustee $5000, for a total of $7500.  The current value is $7917 for a gain of $417, or about 6% or 1.8% / year when adjusted for the timing of cash flows.  You can see the detail at the link on the right or download the spreadsheet here.

During the year we had about $136 in dividends for a yield of about 1.7%.  We had one sale, of Coca Cola FEMSA with a long term capital loss of ($321).  Our sales still look relatively good in hindsight.  For the portfolio as a whole, it has mostly recovered from the fall in oil prices.

Portfolio Four Updated January 2017 – Tax Time

Portfolio Four is a bit over 7 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current value is $13,932 for a gain of $1932 or 16%, which is about 3.3% / year when adjusted for the timing of cash flows.  You can see the detailed spreadsheet at the link on the right or download it here.

The portfolio is generally doing well. We sold LinkedIn (LNKD) because they were bought by Microsoft and gave up on Coca Cola FEMSA (KOF) which was hurt by the decline in the Mexican Peso and recent election results.  We did not sell many of the energy companies which (mostly) held on to their high dividends and have risen recently with the uptick in oil prices.  It is important that you look at the “total return” which includes dividends because some stocks look like they have losses just based on price bought vs. today when in fact they’ve been positive due to dividends.

The portfolio had about $240 in dividends for an average yield of about 1.7%.  This is a good rate but down a bit from last year because we sold Seaspan (SSW) and Garmin (GRMN) which had high dividends.  Most of our sales are still OK in hindsight but Garmin has gone up a bit since we sold it.  We had a long term capital loss of $474 due to sales of LNKD and KOF, above.

Portfolio Five Updated January 2017 – Tax Time

Portfolio five is a bit over 7 years old.  The beneficiary contributed $4000 and the trustee $8000 for a total of $12,000.  The current value is $14,071 for a gain of $2071 or 17%, which is 3.5% / year when adjusted for the timing of cash flows.  The spreadsheet can be found on the link to the right or you can download it here.

During 2016 we had about $223 in dividends for an average yield of about 1.6%.  There was a capital loss of $134 on the sale of LinkedIn (which was bought by Microsoft).  The portfolio is doing well, with Spirit Airlines (SAVE) being a recent winner.  Our sales also seem OK in hindsight.