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 |
Trust Funds For Kids 
Leave a Reply