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”

Aligning Stocks to Sectors

For stock analysis, it is important to understand how stocks roll up to sectors or industries (in this context, both words are attempting to say the same thing).  Per this excellent article from Fidelity, there are three main classifications of individual stocks into sectors or industries:

There are three main classification schemas. They are the Global Industry Classification Standard (GICS), the Industrial Classification Benchmark (ICB), and the Thomson Reuters Business Classification (TRBC).  These classification schemas are designed to provide an acceptable and meaningful method for standardizing industry definitions so that comparison and analysis can be conducted between companies, industries, and sectors worldwide, and for creating benchmarks.

I am going to use the same schema used by Vanguard, since it is easy to find reference benchmarks for them (I will use the ETF performance to compare against the performance of our individual stocks against that sector).  Vanguard uses the GICS model, and as such has the following 11 sector ETF’s (and Ticker Symbols).  The GICS indexes are run by McGraw Hill (MCSI).

  1. Consumer Discretionary (VCR)
  2. Consumer Staples (VDC)
  3. Energy (VDE)
  4. Financials (VFH)
  5. Health Care (VHT)
  6. Industrials (VIS)
  7. Information Technology (VGT)
  8. Materials (VAW)
  9. Real Estate (VNQ)
  10. Telecommunications (VOX)
  11. Utilities (VPU)

There are 4 levels in the Global Industry Classification Standard.  You can see a breakdown of them here at Wikipedia.

Often I track the stocks in Google.  In fact, I am migrating everything to Google Sheets.  Google uses the ICB model (Industry Classification Benchmark), which means as you look up individual stocks in Google Finance, you see them categorized according to ICB.  This article describes some of the differences between GIC and ICB.

I am going to continue researching the differences between the two methods.  If I can find simple and representative ICB ETF’s by main sector perhaps I will use them instead of the GIC model because any time you pull up a stock in Google it brings in the ICB information automatically.  Here is a link to a summary of the ICB model as maintained by FTSE (the UK exchange).

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.

What a Stock Market Rally Looks Like

Rally_View

The stock market has been on a tear since the election.  Initially, the stock rally was concentrated in a few industries where investors believed the new administration would assist their financial position (airlines, banks, etc…).  Recently, the rally has taken on a life of its own and is just going UP.  There are 21 stocks contained in Portfolio One, and today 20 of the 21 stocks went up (one foreign stock ADR went down slightly).  This sort of correlation amongst all the stocks is a sign of euphoria in the market.  I don’t have any particular insight into where stocks are moving next but this is an interesting sign for the market right now.

On Investing

Investing has changed significantly during the 25 or so years that I have been following both the market and also the tools available for an investor to participate within the market.  The following trends are key:

  • The cost of trading and investing has declined significantly.  Trades used to cost more than $25 and now are essentially free in many cases.  Mutual funds used to have “loads” of 5% or more standard when you made an investment, meaning that $100 invested only went to work for you as $95.  These sorts of up-front costs have almost totally been eliminated
  • ETF’s have (mostly) replaced mutual funds.  ETF’s “trade like stocks”, meaning that you can buy and sell anytime (mutual funds traded once a day, after being priced with that days’ activity) and they don’t have income tax gains and losses unless you actually make a trade (mutual funds often had gains due to changes in the portfolio that you had to pay taxes on even if you were just holding the fund)
  • CD’s and Government Debt are all electronic.  You used to have to go to a bank for various governmental bond products or to buy a CD.  Now you not only can buy all of this online, you can choose from myriad banks instantly rather than settle for whatever your main bank (Chase, Wells Fargo, etc…) offers up to you
  • Interest Rates are Near Zero.  One of the key concepts in investing is “compound interest”, where interest is re-invested and even small, continuous investments held for a long time can end up amounting to large sums (in nominal terms, because inflation often eats away at “real” returns).  However, with interest rates basically near zero, you need to earn dividend income or take on more risk (i.e. “junk bonds”) in order to receive any sort of interest income.  There is no “safe” way to earn income any more
  • Currency Fluctuations Matter.  When the Euro initially came out it was $1.30 for each US dollar, and then it went to 70 cents per dollar, and now it is about $1.10 per dollar.  At one point the dollar fell 30-40% against many currencies world wide (when “commodity” currencies like the Canadian and Australian dollar were surging).   For many years currencies were relatively stable against one another but that era seems to be ending, and thus the change in relationship between the US dollar and their currency can be much greater than the return that is earned on the international investments
  • Active Trading Has Mostly Been Beaten By Passive Trading.  While there are many exceptions, initially the majority of investments were “active”, but over the years many of the “active” managers have substantially under-performed the market, wilst charging investors more in fees (it is cheaper to run a “passive” index).  As a result, there has been a massive shift away from active investors to passive investors like Vanguard
  • Correlation Among Stocks and Investment Classes Is Much Higher.  Correlation means that stocks or asset classes tend to “move up” together or “move down” together.  It is not unusual for me to look at a portfolio of 20 stocks and 19 or 20 of them have all gone up or down on a single day.  This is related to active managers being unable to “beat” the market (see above)
  • The “Risk Premium” for Lower Quality Debt is Small.  The amount of extra interest required for low quality borrowers over the US Treasury benchmark is very small.  Investors are taking on a lot of risk to just earn a few more percentage points of return.  If there is a downturn in the economy (such as what happened only recently in US oil companies), there are likely to be significant declines in junk bond values that wouldn’t justify the modest risk premium you receive for holding these types of assets
  • ETF’s Provide an Easy Way to Participate in Commodity Markets.  It was more difficult to buy and invest in commodities like gold and crude in the past, and it was often limited to relatively sophisticated investors or those willing to hold on to physical commodities like gold (which can be risky since they need to be stored and protected due to high value and inability to trace once stolen).  Today you can easily buy a liquid ETF to participate in the commodity markets for key areas like precious metals (gold and silver) and crude oil / natural gas
  • Fewer Companies are Going Public and the Market is Shrinking (in terms of issuers, not total value) – It is easy for start up companies to access private capital (venture funds) and they tend to “go IPO” at high values, making a further upside (after the initial IPO) more difficult.  The total market is shrinking in terms of listings due to M&A (companies buying other companies) faster than the new IPO’s and many companies are “buying back” shares which also reduces the total value of the public markets
  • Bonds have had a Gigantic Bull Market that is Nearing It’s End – Bond prices move inversely to yield; thus if you held on to a 5% low risk bond (which would have been available everywhere in the early 2000’s), that bond would currently be priced at much more than 100 cents on the dollar today.  Interest rates peaked around 20% near 1980 and now are not far from zero; in this sense bonds are part of an enormous “bubble market” that has not yet peaked.  But given how low rates are (they are even negative), it seems like this bull run is about to come to an end
  • Ensure That You Include Dividends and Total Return.  A common mistake is to look at performance just in terms of stock or asset prices, and avoid including the compounding impact of dividends received, especially since dividends often rise each year.  Dividend income can make up a significant portion (25% and up) of total return, so selecting assets that provide dividend income is critical.  Finally, dividends provide favorable tax rates when compared to interest income

What does all of this mean?  I would sum it up in two ways:

  1. It is easy for individual investors to set up a simple and low cost way to track the market – the “basic plan” that I set up as a simple example can be used by anyone and it does what it says.  Here is a second plan that also includes some hedging of the non-US investment
  2. You will need to save much more (or take on more risk) because interest rates are low – with near zero interest rates, you can’t make much money on low risk interest bearing products (like CD’s, savings accounts, and simple government debt).  If you are earning risk income, you likely are taking on substantial risk of default because there is no “free lunch”.  As a result, you need to put more cash into stocks in order to earn dividends or see real returns, but this also could lead to significant losses if there is a market crash like 2008-9.

I try to promote financial literacy and have helped many friends and some family members when they ask questions.  Ideally we would actually drive financial literacy through school and into the university.  Even those who have a degree in finance or accounting often lack practical advice on personal finance and don’t know how to approach these issues.

One key concept is “net worth”.  Net worth isn’t how much you earn in salary, it is what remains in savings after taxes (or through long term deferral of taxes).  The only “assets” that count are those that you can turn into cash if needed, and they are “net” of the debt (such as on your house).  Most people have a negative or near-zero net worth, which is also linked to the concept that they are essentially a couple of missed paychecks away from very bad outcomes such as having to take out a payday loan or borrow money from relatives.

Another key concept is trying to avoid excessive student debt.  Unlike all other forms of debt (loans on your house, your car, or credit card debt) your student debt cannot be discharged through bankruptcy.  You essentially have no options except to repay your loans, and if you miss payments or fall behind the fees and penalties will greatly increase your balance due.  Student financial literacy is critical because they are making decisions that will impact themselves and their families for the rest of their lives and they must be made thoughtfully and with the end in mind (if you are taking out all of this debt, you must be driven in your career to make money in order to pay it off and get on with building net worth).

Cross posted at Chicago Boyz