Using Google Sheets and Pivot Tables


It is helpful to structure the google sheet in a manner that can easily be turned into a pivot table.  This table uses the following fields from the “all stocks” tab:

  • Ticker is self explanatory (i.e. GE = “General Electric”) – the unique exchange identifier for the stock.
  • Portfolio is the unique portfolio – there are 8 in total – they are numbered 1 to 8
  • If we were in a traditional “database” format, then the combination of the Ticker & the Portfolio number would be the “primary key” – all the other items are either values (price and shares) or attributes calculated from those values or the stock itself
  • Shares are entered into the spreadsheet; they rarely change except if there is a stock split (I have a few of these every year)
  • The price is a formula using google sheets
  • The sector, country, yield, and dividend level come from the “all stocks” page
  • The “momentum” is calculated based on the price level compared to the 52 week high
  • The annual income is calculated by taking the “amount” (which is price times number of shares) times the “yield”.  This is a useful figure and recommended for portfolios
  • Note that there are some exceptions – these are cash which has no attributes and must just be entered; and also CD’s which have an interest rate and a static value.  These items are in orange since they are entered manually


With the values in this format, you can now create a “pivot table” (which works just like the pivot tables in excel) which we then in turn will create the analytics pages for each portfolio which come next. Note that since these analytics are “pivot tables”, when you update the underlying data the tables automatically update without manual intervention.

Using My Consolidated Formula Model in Google Sheets

I moved the portfolios to Google Sheets to take advantage of Google’s ability to use financial functions like “price” by ticker so that my stock quotes could be updated with live data when you click on the spreadsheet. These sheets can also be shared with anyone (and it is free unlike Office 365).

I have been working with these sheets for a while to 1) simplify how they work 2) build them in such a way that I can “copy and paste” into other google spreadsheets 3) link the sheets together so that I only have to do selected work one time.

We are going to start with the “All Stocks” view because this page is crucial to understanding the entire model, including what can be linked to Google Sheets and what needs to be created and / or input from another source. Once this page is updated with key information, however, it can be used by all the other sheets and I only have to input data or do analysis one time and it automatically updates everything across all the portfolios.

The “ticker” symbol is used to derive values in google sheets using the “GoogleFinance” formula.  I manually added (one time since this page is essentially a central database for all the other portfolios).

For each stock I use the GICS classification system (used by Vanguard) which I describe here.  I map each of the stocks using this model as follows:

  • sector
  • industry group
  • sub industry
  • type (US or foreign)

“Yield” is something that the GoogleFinance formula doesn’t give you.  This is the dividend % for the stock and it is important information.  I look this up, manually, stock by stock, using google or yahoo finance or marketwatch.  Some of the ADR’s are harder to find.

I also classify stocks by “dividend level” based on yield which I made up and categorized stocks as follow:

  • >3.9% – very high
  • >2.7% – high
  • > 1.4% – medium
  • Less than 1.4% – low

I have a “description” field which is my all in summary of the stock.  This summary takes into account our original plan for the stock, its price against its 52 week high, potential as a takeover candidate, potential for a dividend cut, or any other information I feel is relevant.  I write this once for each stock in any of the 8 portfolios and propagate it through all the portfolios.

The price is derived by the GoogleFinance function and the 52 week high.  Then the stocks current price is compared to the 52 week high and shown as a percent.  The categories are:

  • If > 94% of 52 week high then “near high”
  • If > 90% of 52 week high then “doing OK”
  • If > 80% of 52 week high then “at risk” (and colored yellow by conditional formatting)
  • If less than 80% of 52 week high then “on watch” (and colored red by conditional formatting)

This is the single most important page and contains most of the logic used for analytics as well as the summary description of how that stock is doing “all in”.  In future posts we will review the other pages.

Using Google Sheets to Track Portfolios

Our portfolios took a hit in early February 2018 with the rest of the market.  Since then the portfolios have mostly rebounded as you can see below.  The results are slightly skewed between 2/9 and 2/25 because I found ~ $2000 in additional funds in portfolio 2 due to an error since IBB had split and I recently updated the cash counts for each portfolio which probably added another $1000 net across all 8.  But even with those items adjusted out, we are over $6000 above where we are right after the market drop.

These are long term portfolios and we expect variability, especially after a long bull market.

The portfolios are viewable within a consolidated portfolio tracker which is updated as the market moves via the formulas in Google Sheets. We also have analytics for each portfolio that shows pricing vs. 52 week high, dividend categorization, US / foreign, and sector data.

For each individual portfolio, they have their own google sheet which I just finished updating. Much of the analytics comes from the consolidated summary – I update a point of view on each stock there once and it “cascades” through all the sheets. I am now to the point where each sheet just has to update buys / sells, dividends, and cash balances and the rest comes from the main consolidated tracker.

Portfolio Eight Updated Feb 2018, and It’s Tax Time

Portfolio 8 is 3 1/2 years old.  The beneficiary contributed $1500 and the trustee $3000 for a total of $4500.  The current value of the portfolio is $5409 for a gain of $909 or 20%, which is 9.5% / year over the life of the portfolio adjusted for the timing of cash flows.  Go here for portfolio detail or use the link on the right.

We had 2 sales this year, Tata Motors (TTM) and Gilead (GILD).  Total long term gains from sales were $55 and dividends were $50.  The portfolio has done OK recently and bounced back from February market activity.

Portfolio Seven Updated Feb 2018, and It’s Tax Time

Portfolio 7 is 3 1/2 years old.  The beneficiary contributed $1500 and the trustee $3000 for a total of $4500.  The current value is $5991 for a gain of $1491 or 33%, or about 14% / year when adjusted for the timing of cash flows.  Go here for portfolio detail or go to the link on the right.

During 2017 we sold Spirit Airlines (SAVE) for a long term capital loss of $84 and had dividends of $40.  The portfolio is doing well, with holdings in BABA (Alibaba) and MA (Mastercard) doing very well.  The portfolio has mostly recovered from early February market activity.

Portfolio Six Updated Feb 2018, and It’s Tax Time

Portfolio Six is 5 1/2 years old.  The beneficiary contributed $3000 and the trustee $6000 for a total of $9000.  The current value is $10,716 for a gain of $1716 or 19%, which is 5% / year across the life of the portfolio.  Go here to see detail, or use the link on the right.

We had $157 in dividends and a sale of TTM for a long term gain of $35.  The portfolio has bounced back from February market activity and is doing OK.

Portfolio Five Updated Feb 2018, And It’s Tax Time

Portfolio Five is 8 1/2 years old.  The beneficiary contributed $4500 and the trustee $9000, for a total of $13,500.  The current value is $16,640 for a gain of 23%, which is 4.1% / year adjusted for the timing of cash flows.  You can see portfolio five details here or go to the link on the right.

This year we sold 2 stocks, TTM and SAVE, for a short term tax loss of ($78) and a long term gain of $24.  We also had $291 in dividends.

The portfolio is generally doing pretty well and came back a bit from the early February market activity.  We are watching JNPR because it may be in play as a takeover candidate.