Consolidated Portfolio Vs. Detailed Portfolio

There is a single “Consolidated Portfolio” in Google Sheets and an “Individual Portfolio” for each of the 8 beneficiary Portfolios, for a total of 9 Google Sheets. In this post I will explain 1) why you need to look at stocks this way and can’t just glance at a list of stocks in a standard tracker on your phone 2) what the consolidated sheet does vs. the 8 individual sheets.

Why Not Use a Simple Stock Tracking App?

Any standard stock tracking application let’s you put in the total number of shares and track them in real time. You can also put in your purchase price to show your unrealized gain in real time. That’s all you need, right? No, definitely not.

This sort of simple model ignores dividends and the accumulated impact of dividends on the life of your portfolio, which can be very significant over time. In a non-zero interest rate environment (where short term interest rates aren’t 0.1% and medium term rates under 0.5%) you can also model the impact of cash / interest held in your portfolio.

These simple applications also don’t show “snapshots” of where your portfolio was 3 or even 6 months ago. It is a “current snapshot” and with markets, it is easy to miss that you had a stock hit a high and retreat or vice versa.

Finally, these applications may show you total return (what you’ve invested vs. current market value) but they won’t calculate annualized returns that take into account the timing of cash flows. The returns over time are what you are interested in unless you are solely in the market for the short term.

Consolidated Portfolio Tracker

The consolidated portfolio tracker shows the current performance (real time) of all 8 portfolios. I also froze “snapshots” at various points on the main page and graphed all the total performance.

Consolidated Portfolio Main Page

This table is updated real-time using Google Sheets (OK, it is 20 minutes delayed) by this sheet at the back which has the ticker and the number of shares for each of the 8 portfolios. A pivot table then runs against this information to get the real time tracker in the “price” column. Some of the items need to be added manually (such as individual cash balances) but the rest all run “live”. If I notice that a portfolio looks odd, I will match it against a brokerage statement online and I can see something that has changed which could be a stock split or perhaps a ticker change or even a spin out of a stock which adds stock to the portfolio and reduces value of existing shares.

Consolidated Portfolio Share Tracker

I also use this “stock data” sheet to calculate the other metrics, like momentum (percent of 52 week high), dividend yield classification, and other elements. I also include a description about the stock when I periodically evaluate it (we do remove stocks from the portfolio through sales) and then this data appears automatically in the 8 individual sheets (so it makes it much easier to update the detailed sheets and keep them consistent). I also go through and update “yield” which is the anticipated dividend percent which changes with the price (as the stock price goes up the yield goes down and vice versa) and categorize stocks in the various ways including US / Foreign and sector (based on standard classifications). Note that it is surprisingly complicated to do this because different benchmarks classify stocks very differently (a more detailed topic).

Consolidated Portfolio All Stocks

This consolidated sheet I check pretty regularly and if there are anomalies like stock splits I see them here and fix them easily on the central sheet. I also periodically update the cash balances (which reflect dividends received). When I believe it is time to update the individual sheets, I will start here with the stock descriptions and then go through the individual portfolios one at a time (the next section).

Portfolio Analytics Example

I also calculate an analytic page for each portfolio in the consolidated portfolio and then this “copies” automatically into the detailed sheet (they are linked, like the main stock page, above). I just need to check it from time to time for percentage calculations when the portfolio changes to make sure it makes sense.

Individual Portfolios

I try to do as much as possible in the consolidated portfolio so that I can update the individual portfolios as quickly as possible. It typically takes at least an hour to update each portfolio and check all the results even with the changes I have made to make it efficient.

Detailed Portfolio Example Main Page

This is an example of an individual portfolio sheet for a beneficiary. There is more detail here than in the consolidated report, above, although many of the sheets and details come from the main sheet. You can see some insights:

– Total return for each stock (including dividends) since inception and by year. This is very important and let’s you see how each stock is performing since it was purchased
– Previous performance on stocks that were sold and their current price
– Total return for the portfolio, including an annual rate (which we show separately below)

For sales, it takes a while to align the sale to the original price and calculate the gain / loss that is realized and then align the dividends to calculate total return. I adjust the sheet and move it to the bottom and check the formulas.

Detailed Portfolio Dividends Example

Calculation of dividends takes a while. I go through the brokerage statement and pull out each individual dividend payment, and I code them by date and by ticker. For foreign dividends with witholding (i.e. the dividend was $30 but $10 was withheld for taxes) I put the “net” amount (in this case $20). The beneficiary should receive a tax credit for these savings on the tax return so theoretically I could put the full amount but this keeps it closer to the cash balance. I put “zero” in for all new stocks that either never pay dividends (lots of tech stocks) or haven’t yet because this makes the pivot table and lookup function work correctly (they need a value of zero).

Cash Flow Return Example

I also calculate the return adjusted for the timing of cash flows. It uses the formula listed above. For instance, your total portfolio could have a 40% gain over 4 years which would seem like 10% / year but actually it is higher because you didn’t put all that money in on day one 4 years ago, you put it in even payments, so it could be 15% / year (this is just a fictitious example). This gets kind of complicated but it is an important concept in investing.

Conclusion

In summary, I try to do as much as possible in the consolidated work sheet to minimize the amount of work I need to do in each individual sheet. The sheets are linked and the consolidated sheet drives most of the formulas in the detailed sheets. I do need to update individual dividends by portfolio and also calculate the impact of buys / sales and their own unique timing of cash flows to calculate their annual return.

Each of the beneficiaries should have a link to the main consolidated google sheet tab and then their own sheet. I also make a copy of them to keep locally in case the formulas get messed up for whatever reason (you can adjust them).

Google sheets works on phones and computers and everywhere and updates real time. Periodically the formulas don’t work, but it usually corrects itself quickly.

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

Consolidated_Portfolio_Shares

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.

Stock Selections Completed, SNAP and the Summer Bull Market

We recently completed our stock buying for the fall of 2017.  We do the stock buying and matching in the fall so that beneficiaries can have the summer to make some money in order to do the match.

It is interesting that of the 6 stocks (and one ETF, IAU or gold) on the list, no one took Snapchat (SNAP).  This is interesting because while it is popular with many of the beneficiaries (they use it), they can segregate whether something is useful or whether it may be a good investment. I had Snapchat on the list because I felt that it had been beaten down by bad sentiment and poor results and because it was burning cash BUT that this also created the opportunity for a turn upward (may be at the bottom).  In the past I’ve been hesitant to put up stocks that are tied to products that the beneficiaries may use day to day because I didn’t want that to bias the selection process but it turns out I was wrong.

With Google Sheets it is much easier to track the portfolio real time.  I have a summary sheet set up like the picture in this post and I can just glance at it on my phone from the google sheets app.  I take snapshots of the values in each portfolio every month or so in order to see simple trends over time.

You can see our summer bull market in the results, although you need to mentally factor out the impact of $11,700 in contributions and $6000 in withdrawals across the period (net inflows of $5700).  Thus based on some simple math above, across the portfolio we saw an increase of $154,073 – $136,791 = $17,282 and then you take out the net inflows of $5700 to get a net increase of $11,582 divided by our base of $136,791 from about 6 months ago which is 8.4% and if you roughly double it (to get annual performance) you see annualized performance of roughly 17% in the portfolio during essentially the summer and most of the fall of 2017.

Google Sheets Integration

I have been using Google Sheets to update these portfolios and it has been an excellent experience.  I can share the spreadsheets with the beneficiaries and they can see a real-time view of their portfolios on any device (phone, PC, tablet) through Google Sheets.  The only minor updating item is the cash in the money market account which is a product of recent dividends.

It used to take me a long time to update each spreadsheet.  I had to do the following items:

  • look at the performance of stocks that were sold.  Now I use the “Google Finance” formula to get the current price of stocks that have been sold and I have it in a text statement embedded with the purchase and sales price for that stock
  • I don’t update the current prices for any stocks; that happens automatically

I try to centralize functions.  One item you can’t find via Google Finance is “dividend yield”.  I have a central sheet where I update yields once and then can copy that throughout all of the cells.  I need to copy a block of text into the spreadsheet so that a VLookup can be applied against the data, with the ticker being the primary key.  This also works for the current description of the stock’s status (commentary).

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%

Continue reading “New Google Sheets Analytics – Sector, US / Foreign, and Dividend Views”

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 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.

Google Sheets

I have been keeping track of the portfolios in excel for over a decade now.  Although I am pretty good at updating the data, it does take a little while and is error prone.

I recent started moving the portfolios over from Microsoft Excel to Google Sheets.  Google Sheets have formulas that automatically update the stock prices via a formula call like =googlefinance(PG, “price”) and there are a host of other values you can invoke from 52 week high / low to PE to EPS.  It is very cool when you build something that can auto-update just upon opening the spreadsheet.

It has been a while since I’ve built detailed spreadsheets with functions and formulas and it is a lot of fun (for me, at least).  Google Sheets have many of the same features as Excel such as pivot tables and conditional formatting and Lookups and they mostly function the same.  I was surprised that they could name a lot of features using the same names as Excel but I guess I’ve just been away from the game for a while, perhaps that is the norm.

The concept that the spreadsheet just opens up and auto updates via formulas or API’s is very powerful.  It is interesting what data is readily available and for what exchange, while other information (notably the dividend yield) has to be obtained by looking it up manually (which is also not even correct sometimes).  This is something I will be writing about more in the future as I do additional research.

The ability to share documents is also very powerful.  I can create a spreadsheet and share it with the beneficiary via Google Docs and they can go in any time and see how everything is going.  It will be about 98% right (the cash balance won’t auto update and the last few dividends won’t be recorded) and some of the new analytics I’ve created (just a start) will also auto update.

This is going to be something that I’ll be working on a lot and it opens a whole universe of possibilities.  If you think about it, most of the data is out there somewhere in the public cloud, available in a database or by API.  The data that you have on your particular situation is a mere tiny portion of the grand total.  The fact that most of this can be available FOR FREE is also astonishing.

After I’ve beaten up Google Sheets for a while I will have to ask myself what incremental value, if anything, that Excel provides.  I’m sure there are some advanced formulas they have that Google does not but sharing Excel files has always been a nightmare, and this is easy with Google Sheets (I realize that O365 is supposed to help this, but still….).  I am using Excel 2011 for my Mac and haven’t seen the urgent need to upgrade and this won’t really help the need to upgrade, either.  Microsoft still sends me security patches, which is great, when and if that stops I’ll have to consider my options.  I guess the patches will stop in October 2017 for Mac 2011.  Maybe at that time I will also trade in my Macbook from 2011 which has given me great service but likely will be reaching the end of its useful life.