General Category > Berkshire Hathaway

Look through portfolio - Google Sheets with live prices

(1/26) > >>

Dynamic:
View-only example look through portfolio
(set to GBP currency - only I can edit this but I think you can select and copy the cells into your own Google Drive spreadsheet):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

Publicly editable version of look-through portfolio:
(might get corrupted by someone else's edits, but you can have a play with it and try changing currency and number of shares held):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

At time of posting this is updated to 30th June 2016 holdings as declared in 15th August 2016 13-F and the shares oustanding at the same date from the 10Q.

Commentary:

I track our total personal portfolio and Watchlist using Google Sheets in Google Drive (the GOOGLEFINANCE function is very useful for currency conversion and delayed prices and basic financials such as EPS). I also use various tabs to track our investment ledgers and our monthly and annual deposits into our tax-advantaged investment accounts, and to keep an eye on our pension schemes.

I wanted to make a look-through total portfolio, partly to gauge our total exposure to companies we hold that are also held by Berkshire Hathaway, such as Wells Fargo, where our look-through stake is about half of our direct stake because we're fairly heavily into BRK.

As a by-product, I've created a Google Sheet which you might wish to look at, borrow from or select-All and Copy into your own Google Sheet on Google Drive. If you paste into Excel or LibreOffice Calc you'll probably lose the GoogleFinance functionality.

The link below is my personal version (only I can edit it, but anyone can view it and I believe could copy and paste the cells to another sheet they can edit):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

The link below this is publicly editable by anyone (so you can play with it, collaborate on it or paste the contents of the other sheet into it):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

If you enter in the blue cells your number of BRK class A shares and class B shares and your native currency (e.g. GBP, EUR, USD, CAD etc.) it converts it into equivalent B shares and it estimates look-through holding values from row 8 down, and in columns way over to the right, it includes the EPS reported by GoogleFinance. Some shares have an Error (N/A) so I set these to zero in totalling EPS per BRK.B, but these are mostly minor holdings, so the overall look-through EPS at the bottom right is about right.

As a crude indication, as for my full portfolio, I also include Low, Typical and High estimates of trading range based on some kind of fundamental and multiples of it. This also sets a colour scale to crudely indicate if the current market price is Low (DARK GREEN), Typical (BRIGHT YELLOW) or High (DARK ORANGE/BROWN). In my full portfolio I sometime use this colour scale to easily whittle down my list of candidates to sell at toppy prices in order to buy something else I think is cheap, and I use the Low/Typ/High idea to project long term retirement portfolio value without so much influence of current market-wide depression/euphoria.

For BRK this fundamental is Book Value Per Share. For most others it's EPS reported by GoogleFinance. Where EPS is negative or N/A I've estimated some other value such as normalised EPS - using very little effort of analysis!!!

Do your own research etc. and don't simply rely on the spreadsheet to make investment decisions!

Soon I expect to summarise the key figures near the top and the left of the sheet and perhaps make the look-through list filtered/sortable.

Dynamic:
I've made a couple of changes to both spreadsheets.

I've inserted rows 7, 8 and 9 on the spreadsheet.

Row 7 splits out the equity holdings per BRK.B share. It tells you how much one share of BRK.B contains in current Market Price of equity holdings.
(Market Price is about $53.66 as I type this, with effective EPS of $3.05 per B share, P/E = 17.57 or E/P yield of 5.69% from column W). It also values your total holding in your native currency.

Row 8 splits out the operating side of Berkshire Hathaway only (it does not back out cash equivalent holdings, however). It shows the operating earnings excluding dividends (as per the annual report for now, not per the 10Q).
(Market Price is about $94.41 as I type this, with effective EPS of $8.86 per B share, P/E = 10.66 or E/P yield of 9.38% from column W). It also values your total holding in your native currency.

Row 9 is the sum of the two rows above - the total Look-Through BRK.B share, with total look-through EPS in column S.
(Market Price is the same as BRK.B = $148.07 as I type, Look-Through EPS = $11.91, P/E = 12.43 or E/P yield of 8.04%)

You can use this in a few ways to approach valuation, according to your tastes, such as:

Apply an earnings yield or P/E multiplier to Total Look-Through EPS - see cell S9 then multiply by a fair P/E or divide by a fair earnings yield.
Back out cash and equivalents per B share then do the above to value the non-cash portion.
Apply a sum-of-the-parts valuation to both the portfolio's look-through earnings per B share (so you free yourself from it's current market price) and to the operating company's look-through earnings per B share.
Make a valuation of each significant holding individually and to each division of Berkshire's operating business.
Value various business units within Berkshire Hathaway separately and combine them. E.G. Insurance, Utilities, Railways, Retail and Consumer, Financial Products, etc. to value the operating portion, then add in the shares.
Use whatever crude metrics you normally employ to roughly gauge the relative attractiveness of different stocks. For example, my crude Low, Typical, High measure adjusted using columns Y, Z and AA.

Dynamic:
For those who want to play or to enter private information, like your actual BRK holdings, you can copy the "View Only" version of the Google Sheet to a Google Sheet of your own.


* Open Google Drive or http://drive.google.com in the usual way.
* Go to or create the folder you want then press NEW and select Google Sheet.
* Your Untitled Spreadsheet will open. You can click in the title to change its name.
* Open my view only Sheet at https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing
* Once it has fully loaded, go to the tab marker at the bottom called "Look-Through BRK earnings and holdings" and click on the light downward arrow on the right of its name
* You will have only one option, namely "Copy". Click it.
* Select your newly created Google Sheet from step 3 above and hit OK.
* It will then say worksheet copied and offer a link "Do you want to open the target Google Sheet?". Say Yes and it will open your Google Sheet.
* It will probably be open on the blank tab you created it with, but at the bottom of your window you can click the arrow to delete that tab and leave the new tab called "Copy of Look-Through BRK earnings and holdings" which you can now edit to your heart's content.
I've tested this on someone else's Google Chrome and Google Account and it works fine. Feel free to share your own versions on this thread or to copy them to a new sheet you don't mind sharing publicly.

BTW, if you ask me for permission to edit my "View Only" version of the sheet, I'll allow you Comment access, but not Edit access, so it won't get corrupted.

If you want Edit access in the usual collaborative way, there's now a third version of my sheet to which you can request full access. Find it at: https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing
To request Edit Access, make sure you're signed into your Google account then click the blue View Only button to request edit access from me.

I've granted edit access for that to the two people who requested it on my View Only version.

Perhaps this version can be one that members of CBF forums can work on together when new financials come out. Whoever wants to can input, say, the Book Value per share, the shares outstanding (find these over to the right on the top rows) and the dividend income received from Berkshire's portfolio so that the right figures flow through. Post here or comment on the Google Sheet to say what you've updated.

Also, various minor holdings have no valid EPS figure so I just took Wild guesses at normalised EPS for the purposes only of the Low, Typical, High crude valuation of each stock. In some cases, a multiple of Book Value of other figure might be a better valuation yardstick for a specific stock, so feel free to edit columns Q to AA on any rows you like.

Do bear in mind that whatever you type there can be seen by anyone on the internet as can the edit history of the spreadsheet!!! Don't put anything proprietary or personally identifiable.

physdude:
Thanks for the useful spreadsheet! Really appreciate your effort.

arcube:
Well done. Thank you for sharing. Much appreciated.

--- Quote from: physdude on August 17, 2016, 09:32:52 AM ---Thanks for the useful spreadsheet! Really appreciate your effort.

--- End quote ---

Navigation

[0] Message Index

[#] Next page

Go to full version