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.

Portfolio 1 Updated April 2017

Portfolio One is 15 1/2 years old.  The beneficiary contributed $7500 and the trustee $15,500 for a total of $23,000.  The current value is $41,421 for a gain of $18,421 or 80%, which is about 6.2% / year when adjusted for the timing of cash flows.   You can see the data here or follow the link on the right.

The portfolio is generally doing well.  This portfolio is the first to be moved from a trust fund to the beneficiaries control (although technically they were under control from 18 onward).  The trustee now has agency to track the stocks and dividends and make transactions.  Now there will be charges for buys and sells (I think it is about $20 / trade) but this shouldn’t be too bad since we don’t do a lot of transactions annually.

Portfolio 3 Updated April 2017

Portfolio 3 is 9 1/2 years old.  The trustee contributed $5000 and the trustee $10,000 for a total of $15,000.  The current value is $17,483 for a gain of $2,483 or 16%, which is about 2.7% / year when adjusted for the timing of cash flows.  You can see the detail at the links on the right or go here.

The portfolio is generally doing well.  We will consider selling ConocoPhillips (COP) which has been selling off assets to pare down debt and reduced their dividend.  Their stock has stabilized but this may not be the best oil play.  We are also sticking with ExxonMobil (XOM) which we bought near a high because that company has proven to be well run over the last few decades.

In the new “analytics” tab you can see that this portfolio has a heavy non-US component, with 62% of stocks from non-US countries.  We have 2 of the 3 major Chinese internet companies and also 2 large Canadian banks, among others.

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 Eight Updated April 2017

Portfolio eight is 2 1/2 years old.  The beneficiary contributed $1000 and the trustee $2000 for a total of $3000.  The current value is $3201 for a gain of $201 or 6%, which is 4% / year over the life of the fund.  Go here for a view of the stocks or use the links on the right.

The stocks are 1/2 US and 1/2 foreign and right now we are watching Gilead (GILD), one of the health care stocks that is potentially impacted by various health care proposals.

Portfolio Seven Updated April 2017

Portfolio 7 is 2 1/2 years old, with the beneficiary contributing $1000 and the trustee $2000 for a total of $3000.  The current value is $3670, for a gain of $670 which is 22% or 14% / year over the life of the fund.  Here is a PDF of the fund activity and you can also see this in the right column on links.

The stocks are doing well, with 3 of the 4 near their 52 week highs.  The stock is half US and half foreign and the largest sector is Techology (if you consider Mastercard, the payment processor, as Technology) at half the portfolio.