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

Here is a view of stocks by portfolio classified by dividend yield and with the projected annual income for each stock dividend type.

 Portfolio  Stock Type Total Stock Value Projected Annual Income

1

High $21,850.32 $683.15
Low $14,449.49 $138.42
Medium $6,077.51 $107.08
1 Total $42,377.32 $928.65

2

High $5,016.00 $138.94
Low $2,998.96 $4.71
Medium $25,649.73 $534.62
2 Total $33,664.69 $678.27

3

High $6,626.86 $210.82
Low $5,923.55 $41.73
Medium $5,421.41 $94.76
3 Total $17,971.82 $347.31

4

High $6,122.85 $252.74
Low $5,363.48 $2.86
Medium $3,191.46 $56.74
4 Total $14,677.79 $312.35

5

High $6,638.49 $241.31
Low $4,512.69 $19.47
Medium $3,327.89 $64.30
5 Total $14,479.07 $325.08

6

High $2,166.35 $87.66
Low $2,904.36 $27.37
Medium $2,751.62 $53.94
6 Total $7,822.33 $168.97

7

High $850.88 $28.08
Low $3,090.19 $4.96
7 Total $3,941.07 $33.04

8

High $1,510.38 $48.52
Low $1,722.07 $8.22
8 Total $3,232.45 $56.74
Grand Total $138,166.54 $2,850.42
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s