Sales for September, 2017

We are about to re-invest for the 2017 stock season.  Would like to clear out some of the stocks we no longer want to hold.

Portfolios One, Two and Three – no sales (we made some recent sales in Portfolio One).

Portfolio Four (M):

  • Sell Devon (DVN) – hit hard by recent down turn in oil prices
  • Sell Spirit Airlines (SAVE) – in a price war with much larger competitors

Portfolio Five (D):

  • Sell Spirit Airlines (SAVE) – in a price war with much larger competitors
  • Sell Tata Motors (TTM) – value driven mostly from non-India components (Jaguar and Land Rover)

Portfolio Six:

  • Sell Tata Motors (TTM) – value driven mostly from non-India components (Jaguar and Land Rover)

Portfolio Seven (G):

  • Sell Spirit Airlines (SAVE) – in a price war with much larger competitors

Portfolio Eight (K):

  • Sell Tata Motors (TTM) – value driven mostly from non-India components (Jaguar and Land Rover)

 

Advertisements

T+3 and Stock Sales

Many elements of our stock system are completely antiquated when compared with other facets of our modern society.  One of the most “old-school” elements is the T+3 rule for being able to withdraw cash from your account after you sell stocks.

The “T” is the transaction date.  You can withdraw cash from your account THREE BUSINESS DAYS after the date of the transaction.  That’s a long time… the type of process that made sense when these sorts of events were settled manually and / or with confirmations sent by the US mail.

Here is the definition per Investopedia:

The Securities and Exchange Commission (SEC) sets securities’ settlement periods. For example, with the three-day settlement period, a stock trade occurring on Friday is settled on Wednesday, as long as no holidays occur during that time. Otherwise it takes an additional day, due to the markets being closed on weekends and holidays. The three-day settlement period for stocks was established when cash, checks and physical stock certificates were exchanged through the postal system. Adequate time was needed for traders to efficiently buy or sell the stocks and send money to their accounts or stock certificates to the purchasers.

Although money is now instantly transferred electronically, the settlement period remains in place as a convenience for traders and brokers. However, most online brokers require traders to have enough funds in their accounts before buying stock. Also, most physical stock certificates no longer exist; securities are typically traded electronically and are backed up by account statements.

The broker we use allows you to buy other stocks after the sale… you can immediately re-invest the funds while they wait for the T+3 days until the stocks “officially” clear.  But if you want to actually withdraw the cash, you need to wait all 3 business days.

This practice makes no sense for many reasons, but it is is for the benefit of the industry so I don’t see it changing anytime soon.

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.