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.