Corner of Berkshire & Fairfax Message Board

General Category => Berkshire Hathaway => Topic started by: Dynamic on August 16, 2016, 01:09:38 AM

Title: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 16, 2016, 01:09:38 AM
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 (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 (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 (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 (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.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 17, 2016, 08:14:31 AM
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.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 17, 2016, 08:43:52 AM
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.


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 (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.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: physdude on August 17, 2016, 09:32:52 AM
Thanks for the useful spreadsheet! Really appreciate your effort.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: arcube on August 17, 2016, 11:18:26 AM
Well done. Thank you for sharing. Much appreciated.
Thanks for the useful spreadsheet! Really appreciate your effort.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on September 01, 2016, 06:11:30 AM
All versions of the look-through spreadsheet are now updated to include the holding in BYD Company Limited which had been omitted from the original version. This holding was purchased by MidAmerican Energy which was since renamed to Berkshire Hathaway Energy.

The version only I can edit (but you can copy it into your own Google Sheet - see above):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing)

The version anyone on the internet can see and edit (so it could get corrupted, but you can enter your own currency and number of shares):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

Restricted editing - for this version you can request Edit Access and I'll grant it. It sends me an email from your Google account:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing)

In all case, anyone on the internet can view them so be wary of entering private information. If you want a private copy, copy the sheet to your own private Google Sheet using the instructions in post 3 of this thread at http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg272206/#msg272206 (http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg272206/#msg272206)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on September 16, 2016, 05:40:53 AM
All three versions now updated to include increased holding in Phillips 66 (PSX) as at 15 Sep 2016
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 10, 2016, 04:03:30 AM
I noticed an error in converting the Market Values and EPS of foreign holdings like BYD into USD, so the Total Market Value of the Look-Through Holdings was about $4 per B-share too high, and thus the implied market valuation of the operating part of BRK.B was about $4 too low.

I've now corrected this on all versions.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 19, 2016, 06:58:02 AM
Hi,

Slightly delayed by a short vacation in the sun, I've updated the Spreadsheets above based on the latest 13-F and updated the Book Value based on the 30th Sep 2016 10-Q (I'd calculated a slightly different value of BVPS myself than Yahoo Finance shows, so I switched to theirs, which differs in about the 4th significant figure).

I'm glad to see from the revision history and changes that a few people have played around with the numbers of shares and native currency in the Publicly editable version at:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing
...so I hope it remains useful to some of you. By copying over my new version and deleting the old one, I've undone the most recent change by a member of the public.

And don't forget there are instructions earlier in this thread to copy the latest sheet to your own Google Drive spreadsheets. If you copy it, use the version only I can edit:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing

Assumptions made include that the BYD holding remains unchanged since its original report. I assume that as a foreign holding it doesn't get included in the 13-F but is still held.

I note that three airlines have been added to the portfolio - AAL, DAL, UAL
and that two companies seem to have been removed - MEG, SU

I usually make a note of my latest update in the green cell Q6, which now reads:
Latest major change: 2016-11-19. 13-F on 15 Sep 16. Add AAL, DAL, UAL. Remove MEG, SU. BV as at 30 Sep

Various other positions have changed size, such as a reduction in Walmart and various Liberty Media adjustments.

You can compare the previous version of the spreadsheet with the current one as I retained the old version as a separate tab in the Resticted Editing version:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: scorpioncapital on November 19, 2016, 08:31:49 AM
Just wan't to understand something, is the look through earning per share of 38,58,96/share embodied in the investments per share part of the 2 column method so that the difference between this calculation and that one is the implied under or overvaluation of the market value of the investment portfolio?

Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 19, 2016, 02:30:18 PM
I don't think I can understand what you're asking as I cannot find 38,58,96/share or a number that looks like it in my spreadsheet or on the class A equivalent values. 38,58,96 per share isn't formatted like a typical number of dollars so I'm not sure what it ought to be.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: scorpioncapital on November 20, 2016, 12:21:10 AM
Sorry, I meant 38, 58, 97 as look through earnings low, base, high cases.

Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 20, 2016, 03:14:35 AM
These Low, Typ, High valuations are just a crude estimate that I've baked into my stock-tracking spreadsheets.
In the columns Y, Z, AA I define the multiple used for the Low, Typ and High respectively.
These are multiples of the figure in column V, which is mostly EPS, but occasionally I use an alternative metric such as Book Value Per Share (as I do with BRK in rows 1 and 2).

For the full BRK.B valuation, I use BVPS in column V and set Low to 1.2 (the Buy-Back threshold), Typ to 1.5 and High to 2.0, which I estimate as a typical trading range that might be seen at various times during the course of a few years.

For other companies, I figure that a quality company with stable earnings that are similar to Free Cash Flow might be good value at an earnings yield of 8.5% (P/E = 11.76) which may represent a soft floor to the usual trading range, might typically trade at a P/E of 18 and might be richly valued at the High P/E of 30.

Thus, applying the multiples of 11.76, 18 and 30 to the look-through EPS of the investments per share, we obtain $38, $58 and $97 respectively in cells N7, O7 and P7.

These values are then used to color-code the actual look-through market price of the investments per BRK.B-share which as I type this is shown as $54.6592 in cell D7. This is just below the $58 corresponding to bright yellow, so is very slightly darker and greener than bright yellow.

Incidentally, W7 is the inverse of P/E so it's the Earnings Yield at the current Market Price expressed as a ratio rather than a percentage, currently 0.0591 which could be expressed as 5.91%.

Similarly, the same multiples are applied to the Operating Company earnings of BRK.B (net of dividends) to provide the Low, Typ, High values in cells N8, O8, P8 and these are used to color-code the cell D7 'effective market value of the operation company' which is currently $103.0908 (calculated as the current market price of BRK.B $157.75 minus the look-through market price of the investments per share in cell D7 $54.6592).

Row 9 adds up row 7 (look-through) and row 8 (operating). D9 shows that the market-values of these two add up to the market value of BRK.B. The effective EPS per BRK.B share (operating + look-through) is in cell S9 (and cell V9) at $11.1829. This is multiplied by 11.76, 18 and 30 for the Low, Typ and High estimated valuations/trading range. The effective Earnings Yield as a ratio is in cell W9, currently 0.0709 or 7.09%.

You will note that these valuations differ from the Book Value Per Share valuations on row 4 (or row 5) which come out lower, but these do allow a certain like-for-like valuation method so the degree of over-valuation of under-valuation can crudely be compared to other shares that can be valued on a multiple of earnings basis. This might help to some extent in deciding whether to sell some BRK.B to buy something else that's much more undervalued, for example.

By laying everything out on a per-row basis, I seem to have turned the two-column method into a two-row method.

By changing the valuation metrics for the operating part of BRK.B or the look-through shares, you could come up with a custom valuation of your own choosing.

If you wish to share your own estimate of placing a Low, Typical or High valuation on the operating part of BRK.B, I could implement that in another tab of the spreadsheet. Also, you could add a third element by choosing to value cash per share at a certain multiple (e.g. 0.9, 1.0, 1.1 corresponding to a cash drag discount at 0.9, valuing it as risk-free at 1.0 and applying an optionality premium at 1.1) then apply a different valuation of your choice to the operating company net of cash.

For example, if you have an estimate of IV calculated based on a rate you'd like to plug in, perhaps use that IV with a multiple of 1.0 as the High, use 0.75 for Typical and 0.5 as Low multiple. Typical is a 25% discount to IV and Low is a 50% discount to IV.

Variations of the two-column method seem to be:

1. Use the market value of the look-through shares and add a sensible valuation of the operating company
2. Use a non-market valuation of the look-through shares and add a sensible valuation of the operating company

I hope that the above clarifies how these figures came about and how crude they are - albeit that they're probably more 'consistent and objective' than the fluctuating market price of the portfolio constituents.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: scorpioncapital on November 20, 2016, 10:12:58 AM
Do you compare look through earnings per B-share to Investments per B-share to see what the market is implying for the yields in the 3 cases?
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 20, 2016, 02:58:20 PM
Do you compare look through earnings per B-share to Investments per B-share to see what the market is implying for the yields in the 3 cases?

Sure do. See cells W7 W8 W9 for the earnings Yields in question. You just need to shift the decimal point two places to the right to express it as a percentage.

The implied Earnings Yield of the look through portfolio is 5.91% at current prices and reported Earnings, the Earnings being obtained via Google where available including negative contributions from loss makers and zero where an error code is returned.

The implied price if the operating company is $103.0908 with Earnings/Share of $7.9516, and the implied earnings yield in cell W8 is 7.71%.

The total of BRK.B has combined earnings of $11.1829 and current price of $157.75 so Earnings Yield in cell W9 is 7.09%  based on total operating and look through earnings per share.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on January 25, 2017, 09:08:19 AM
Just to mention that LMCK - Liberty Media Group Class C - is no longer showing a price on Google Finance or any other platform, which threw up an error on the look-through market price totals. I think this is a matter of restructuring various classes of share, probably relating to the Formula 1 deal. I'm not inclined to delve into the minutiae now as we'll be getting the 2016Q4 list of investments in a few weeks which will account for such changes, and quite possibly many more significant changes and save a lot of effort.

To get round this I've simply set the LMCK price to a fixed $3.42 and set that line of the speadsheet to RED. This makes of the order of 0.1%, so isn't materially wrong.

After 27 Feb 2017, the new portfolio should soon be available and I'll update it properly.

QUICK LINKS:
The version only I can edit (but you can copy it into your own Google Sheet - see above):
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing)

The version anyone on the internet can see and edit (so it could get corrupted, but you can enter your own currency and number of shares):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

Restricted editing - for this version you can request Edit Access and I'll grant it. It sends me an email from your Google account:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing)

In all case, anyone on the internet can view them so be wary of entering private information. If you want a private copy, copy the sheet to your own private Google Sheet using the instructions in post 3 of this thread at http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg272206/#msg272206 (http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg272206/#msg272206)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Jurgis on January 25, 2017, 12:29:52 PM
LMCK is FWONK now. And it was never priced anywhere near $3.42.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on January 25, 2017, 02:19:15 PM
Thanks, Jurgis. I've now included FWONK so the sheets work again.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 15, 2017, 07:32:12 AM
I hope to update the look-through tomorrow, if I have time, but work intervenes. For now, I've put an extra tab on this version where I've entered up the 13-F data in crude fashion: https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=1273825621 (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=1273825621)

As yet, I haven't fully matched up with ticker symbols and name changes (especially around Liberty Sirius XM and similar names) to determine which holdings appear to have been sold, renamed or reduced.

Some shares show 0, but among these is BYD (China), which we assume is still held, but not reportable to the SEC.

For a summary, various sources are available including this on SeekingAlpha:
http://seekingalpha.com/article/4046111-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2016-update (http://seekingalpha.com/article/4046111-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2016-update)
and this indicates a couple of exits such as Deere and Kinder Morgan, reductions like Walmart and increases such as Apple.

The CNBC portfolio tracker hasn't yet been updated, which I find a handy sortable table to compare & check my own work. http://www.cnbc.com/berkshire-hathaway-portfolio/ (http://www.cnbc.com/berkshire-hathaway-portfolio/)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: scorpioncapital on February 16, 2017, 09:04:33 AM
Just looking at the share prices of some banks and IBM (although BAC are held as warrants), as well as profits in industrials, I have a feeling Q4 could be an incredibly big quarter. If mark to market gains flow through the income statement it could even be a $10 billion quarter.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 24, 2017, 09:41:01 AM
The 13-F is here: https://www.sec.gov/Archives/edgar/data/1067983/000095012317002417/xslForm13F_X01/form13fInfoTable.xml (https://www.sec.gov/Archives/edgar/data/1067983/000095012317002417/xslForm13F_X01/form13fInfoTable.xml)

However, although relatively immaterial, I have a few differences compared to, say, the CNBC Portfolio tracker: http://www.cnbc.com/berkshire-hathaway-portfolio/ (http://www.cnbc.com/berkshire-hathaway-portfolio/).

Foreign holdings never shown on the 13-F:

I seem to be OK with US holdings that have been sold:

Differences to CNBC Portfolio tracker & name-changes/ticker-changes:

If anyone can advise on any of these points, I'd be grateful, and can probably update the look-through sheet in a short while.

One version of my sheet has a second tab where I've entered the 13-F details as best I can:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=2050477249 (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=2050477249)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: scorpioncapital on February 24, 2017, 09:42:47 AM
I see both tickers traded today -
NASDAQ:LSXMA
NASDAQ:LSXMK

Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 17, 2017, 07:35:56 AM
All the version of the spreadsheet are up-to-date with the latest figures and I've got my head around all the Liberties and their tickers. I've also made some substantial changes to how I handle odd cases, like Sanofi and Bank of America Warrants.

• The one only I can edit:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing)
• The one only approved users can edit (click the Share tab to request permission from me):
https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s (https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s)
• The one anyone on the internet can edit without signing in:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

Again, please remember that if you edit the public versions, your data can be seen by anyone on the internet (including the use of File/See Revision History to revive previous versions), so keep your privacy in mind.

If you wish to copy both sheets to your own private Google Sheet, start with the 13-F worksheet tab and hit Copy to... from the menu on the tab, then rename it to remove the added words "Copy of ", the copy the Look-Through worksheet which picks up data from the 13-F worksheet, hence the need to rename it to match the name used in each such formula). Here's the full procedure to copy it:


While going through the 13-F compared to the previous one I noticed a couple of things:
• IBM - was listed in various ownership codes on the previous 13-F but is now all under code 4,11 on a single line
• A couple of other holdings under various codes seem to have moved around including Wells Fargo (WFC).
Could these changes indicate tax-efficient selling and/or buying in certain divisions of Berkshire?

Some items are not in the 13-F but I've made my own entries on my 13-F worksheet in gray background, and I'll summarise them as I've changed how I handle them:

1. BYD (China), as before is included on the assumption it is still held at the same position size.

2. Sanofi ADR. The 2016 Annual Report p19 (Chairman's Letter) mentions the total holdings including both ADR (ticker SNW) and direct shares traded on Paris Euronext. The latter are not reportable on 13-F, but I've put the equivalent number of ADRs (2 ADRs = 1 share of SNY) on the assumption they're still held. I sanity-checked this and the holding is about 1.76% of the market cap, which is about the 1.7% shown in the AR.

3. Bank of America warrants being included in effect as if it were BAC exposure.

Berkshire holds preferred stock with 700 million warrants to purchase BAC common stock at $7.14 until mid 2021.

I'm including these in the look-through effective exposure by using ticker BAC but adjusting the number of shares as if this was a cashless exercise at the current price.

e.g. at $23.99 for BAC, the warrant is worth $23.99 - $7.14 = $16.85
700 million of these amount to $11.795 billion.

That will buy 491,663,193 shares of BAC, so that's the number of shares I put in the look-through portfolio.

In fact, I run this calculation to adjust the effective number of BAC shares ‘live’ to give some sort of indication of the effective exposure and total value of the warrant.

The formula for the effective number of BAC shares I've used is:
No of shares = 700 million x (BACprice - $7.14) / BACprice

Thus, it reflects the current value of the warrants reasonably well, and gives at least some indication of the exposure to BAC - so if you held BAC yourself, you could work out the additional effective exposure via BRK approximately, although you'll be aware that it will increase in 2021 unless the warrants expire worthless.

In reality Berkshire will exchange $5 billion of cash from the operating company to exercise the warrants before they expire, but this will reduce the cash held and reduce the value of the operating component, which I’ve called BH.Own on line 8 of the spreadsheet, by $5 billion while the portfolio BH.Portf will increase in value.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Jurgis on May 17, 2017, 08:52:43 AM
• The one only approved users can edit (click the Share tab to request permission from me):
https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s (https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s)
• The one anyone on the internet can edit without signing in:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

Hey,

Thanks for your work.
I think you messed up "anyone can edit" and "approved users can edit" URLs. Now they both point to the same sheet that requires approval. Looking at your previous posts, this was the URL for "anyone can edit", but no longer...
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 17, 2017, 09:47:02 AM
This is now the one anyone can edit (with corrected editing rights):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

And this is the one people with permission can edit:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing)

Sorry for the mistake, and thanks, Jurgis for pointing it out.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 18, 2017, 06:52:38 AM
I've made a small adjustment to all three sheets.

I've made sure to use live EPS data from GoogleFinance for all shares, but then three showed N/A errors - LBTYK, LILAK, NASDAQ:LILA (new ticker required for LILA to play nice with Google Finance functions) because they don't have EPS data.

I've hard-coded an alternative very rough valuation metric for all three and shaded the cells in columns K:M and Q:AA pink to indicate this. These three stocks don't contribute to the look-through EPS figure, but they're very small holdings anyway (0.16% of portfolio by market value at present) so I'm not concerned.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on June 26, 2017, 09:27:04 AM
There are a few new things to incorporate into the Look-Through Spreadsheet. They mostly fairly immaterial, but I'd still like to get them roughly right.

1. There's the 8-K from STOR June 23rd showing NICO's investment of 18,621,674 shares of the Company’s common stock, which I'll add to the 13-F LATEST worksheet as if it were on the last 13-F.
http://ir.storecapital.com/Cache/389226355.pdf?IID=4553160&FID=389226355&O=3&OSID=9 (http://ir.storecapital.com/Cache/389226355.pdf?IID=4553160&FID=389226355&O=3&OSID=9)

2. There are two SEC Form 4's from BRK on May 25th regarding various Liberty stock purchases.
https://www.sec.gov/Archives/edgar/data/315090/000120919117035615/xslF345X03/doc4.xml
https://www.sec.gov/Archives/edgar/data/315090/000120919117035614/xslF345X03/doc4.xml
In the first, regarding LSXMK, I think I should use 23,293,786 and ignore the 7,153,027 in various pension plans for which Berkshire and its subsidiaries have no beneficial ownership.
In the second, regarding LSXMA, I think I should use 10,174,586 (a tiny reduction compared to the last 13-F) and ignore 4,308,117 held in pension plans with no beneficial ownership.

The full number of shares including non-beneficial pension plan shares has been recorded (possibly in error) on CNBC's Portfolio Tracker http://www.cnbc.com/berkshire-hathaway-portfolio/ . If the pensions are defined benefit types, excess returns may accrue to BRK over time, but if they're money purchase types, the returns will accrue to the benefit of the employee beneficiaries of the pension. I'll assume the latter.

3. Speaking of the CNBC Portfolio Tracker, they made an estimate of the number of IBM shares held from Buffett's interview around the time of the Annual Meeting where he said about 30% had been sold, bringing their estimated share count to 56,862,612 versus the 13-F's 64,561,955. I haven't made any such adjustment, and its effect is far more material than the matters above, due to the size of the position (0.64% difference in total portfolio value).

While I'm at it, CNBC's doesn't account for the BAC Warrants or the Sanofi or BYD foreign holdings (the latter of which might actually be from the Hong Kong listing, rather than the Shanghai listing I used, although this is probably immaterial). And speaking of foreign holdings...

4. Berkshire now owns or will soon own 40,000,000 shares in Canadian Home Capital (HCG.CA) subject to shareholder approval of the private placement of 24,000,000 shares over and above the 16,000,000 common shares purchases, so I'm including these in the same way as BYD, for example.
http://www.cnbc.com/2017/06/22/home-capital-to-get-1-point-5-billion-loan-from-berkshire-hathaway.html


Nos 1,2 and 4, I will probably include as mentioned above.
No 3 is one I will leave alone until we get the next 13-F in July/August and find out how much IBM is still being held. The Look-Through Portfolio is always going to be out of date, so user beware!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: LongTermView on September 23, 2017, 12:14:50 PM
Dynamic,

Thanks for putting this together!

Cell S7 shows $4.16 L-Thr EPS holdings per class B share through June 30th.

Cell AH4 shows $1.48 dividends per class B share last fiscal year.

Does that mean the retained earnings from stock holdings are about 2.8 times [$4.16/$1.48] more than the reported dividend earnings?

Thanks!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on September 25, 2017, 10:30:54 PM
I don't have computer access until tomorrow and will not look into this on my smartphone in detail, but from my memory, I believe I entered the dividends received by BRK from its shareholdings in the last full year (from the Annual Report) so that I could lower the Earnings of BRK-operating-Company to exclude equity dividends and not double-count that form of income and only count income from operating activities and non equity investments such as treasury bills.

It would then be the case that these dividends would simply be part of the look through Earnings (meaning Earnings of the investee companies, whether distributed or not) which ought over time to accrue to Berkshire in terms of Total Return, meaning dividends plus capital appreciation.

I'll check your question again and may reply in my next post.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on September 25, 2017, 11:15:59 PM
Dynamic,

Thanks for putting this together!

Cell S7 shows $4.16 L-Thr EPS holdings per class B share through June 30th.

Cell AH4 shows $1.48 dividends per class B share last fiscal year.

Does that mean the retained earnings from stock holdings are about 2.8 times [$4.16/$1.48] more than the reported dividend earnings?

Thanks!

Thanks for your comments, LongTermView

The Earnings of the investee companies includes anything they distribute as dividends.

The retained earnings would be = earnings - dividends - stock buybacks

If we can ignore the buybacks we have a ratio of
(4.16 - 1.48)/1.48
= 2.68 / 1.48
= 1.81

Expanding the top line you can write it as:
(4.16/1.48) - (1.48/1.48)
which simplifies to
(4.16/1.48) - 1
so my answer is always 1 less than your formula.

The original ratio you showed was Total Earnings / Dividends.

So you can say that for every $1.00 of investee dividends received at Berkshire Hathaway in 2016 there's about an additional $1.81 retained which should eventually accrue to Berkshire as a combination of capital appreciation and increased future dividends.

This means that for each $2.81 of total investee earnings, only $1.00 was received as dividends and reached Berkshire's GAAP earnings bottom line in 2016, while the remaining $1.81 was retained by the investees to fund future growth or buybacks which should increase the value of Berkshire's shareholding over the long term.

If you want to get more accurate you could look up the trailing 12 month dividends of the investees and apportion them to the look through number of shares held so that the timings are roughly within the same quarter instead of half a year out.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: LongTermView on September 26, 2017, 08:47:03 AM
Dynamic,

I appreciate you replying with these details.

This part is key:
Quote
So you can say that for every $1.00 of investee dividends received at Berkshire Hathaway in 2016 there's about an additional $1.81 retained which should eventually accrue to Berkshire as a combination of capital appreciation and increased future dividends.

Of course we're talking about dividends from stocks like KO (as opposed to subsidiaries like BNSF) and we're not including things like interest on treasury bills. Where did you get the total of these KO type dividends on the 2016 annual report?

Thanks Again.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on September 27, 2017, 02:57:35 PM
I looked at the Annual Report p84 and section 'Insurance—Investment Income' then used the figure for Dividends received which I sanity checked by calculating a yield against the equity portfolio valuation that seemed reasonable. That number goes in the first part of the formula in cell AH3 where the formula divides it by equivalent number of shares of BRK.A in circulation which is in cell AC6.

Might be worth me looking up the dividends paid by the investees representing 85-90% of the weighted portfolio value to verify it's about right or even the whole lot. It's a shame GoogleFinance functions don't include dividends.

I realise I had the 2015 figure originally and still needed to lower it just a fraction to match 2016's and should do so every February when going through all the blue cells. The 2016 figure is 3,552 in millions of dollars and had been 3,662 in 2015 so I was able to search the 2016ar.pdf for exactly that string of characters to find the number for the next year.

I'm crazy busy at work after a week off but hope to update the look through sheets soon with this figure.

There will be discrepancies if I include the BAC holding in the EPS but not the dividends as it had been warrants and preference shares previously. I haven't worked out how significant they would be, though these calculations aren't the main purpose of the spreadsheet.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: LongTermView on September 27, 2017, 08:33:32 PM
Got it, thanks again.

So if we make some rough estimates/assumptions for 2016 then we can say the GAAP income of $3,552 million on page 84 is around $9,946 million in economic terms for a difference of about $6,394 million, right?
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: SlowAppreciation on November 08, 2017, 07:22:24 AM
Here's v1 of the Berkshire look-through earnings portfolio: http://minesafetydisclosures.com/individual-investor-portfolio

I will be doing a lot more to improve this over the coming months, but just wanted to share the progress so far for initial feedback.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: CassiusKing1 on November 08, 2017, 08:46:50 AM
Thanks for your continued work and sharing of this!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 08, 2017, 08:54:30 AM
Very impressive work, SlowAppreciation!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: DW on November 08, 2017, 12:01:36 PM
Completely agree; very impressive!

Very impressive work, SlowAppreciation!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: SlowAppreciation on November 08, 2017, 01:33:22 PM
Thanks everyone. I'm taking suggestions/feedback too, so if there's anything additional you'd like to see, please let me know and I can see if it can be included.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 21, 2017, 03:48:21 AM
Got it, thanks again.

So if we make some rough estimates/assumptions for 2016 then we can say the GAAP income of $3,552 million on page 84 is around $9,946 million in economic terms for a difference of about $6,394 million, right?

Just had a chance to look your figures over, LongTermView. I make it roughly $9,520 million of economic income from the investments -  a difference of roughly $5,970 million over the GAAP income. It would probably be better to judge in February when the Annual Report comes out, then the ratio (currently 2.82) will be nearer to matching that based on the look-through earnings of the portfolio held for the year included in the Annual Report, also taking account of adjusted holdings. It would also be a bit out if massive new positions were purchased for cash, say, half way through the year, as only half a year's dividends would be received, while a full year's look-through earnings would be counted.

Maybe one day I'll get round to using the YahooFinance import functions so I can also obtain dividend info that's missing from the GoogleFinance functions. This thread will probably help me do so: http://www.cornerofberkshireandfairfax.ca/forum/general-discussion/google-spreadsheet-tips-and-tricks/

Before I do that I hope to get time to include the latest 13-F in my spreadsheets.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 28, 2017, 09:46:30 AM
The latest 13-F is now included in all three spreadsheets.

You can enter your currency and number of shares in the blue cells near the top-left of the first Worksheet tab.

I've also added a new tab to each spreadsheet called Look-Through Summary - which has a red stripe below the tab name.

This is a somewhat simplified view of the Look-Through portfolio. Rather than re-calculate, it just grabs most of the info from the main sheet, so don't go entering your holdings on this sheet and expect it to work. It adds on the right hand side a couple of columns showing the Look Through earnings of your holding of BRK and showing each constituent stock's percentage of these earnings (just as column J show's its percentage of the BRK portfolio at current prices)

I still include WABCO for now even though the position has now been sold and thus contains 0 shares.

This version is the one nobody but I can edit. It contains instructions for copying them to your own private spreadsheet so you can edit to your heart's content:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837 (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837)


This version is editable by anyone, but be aware that anyone can see your edit history, so if you enter private information there, it's potentially disclosed to the whole internet! It could also get corrupted by anyone!
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)


There's another version that you may request permission to edit, here:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing)


If you'd like a simplified overview of the BRK portfolio, I can recommend http://minesafetydisclosures.com/individual-investor-portfolio (http://minesafetydisclosures.com/individual-investor-portfolio) and for CNBC's Look Through, https://www.cnbc.com/berkshire-hathaway-portfolio/ (https://www.cnbc.com/berkshire-hathaway-portfolio/)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 15, 2018, 09:45:50 AM
The latest 13-F as of 31st Dec 2017 is now included in all three spreadsheets. There are some doubts about figures assumed but not reportable in the 13-F (see below), but these might be resolved on 23rd Feb when the Annual Report comes out.

If you select a Google Sheets workbook you can edit (or copy it to a private Google Sheet workbook of your own using the instructions on my non-editable version) you can enter your currency and number of shares in the blue cells near the top-left of the first Worksheet tab and you can also set the currency of your choice.

By default, however, I've set the number of shares to represent one-millionth of all those outstanding, amounting to 2467.18 BRK.B equivalent shares at present and the currency to USD.

When cell C5 contains that number, 2467.18, you can read off the equivalent number of shares in each holding as millions of shares held by Berkshire Hathaway as a whole, and the values of those holdings as if they were in millions of US dollars (or whatever currency is set in cell B2). Cell I6 therefore would also represent Berkshire's Market Cap in millions.

If you prefer a simpler layout, click on the tab with the red stripe below its name, "Look-Through Summary". Columns A-J have been distilled down to basic info you might want (less useful columns such as my very rough Low End Valuation have been 'hidden from view' to simplify matters a little). However, if you want to change currency or number of shares you must enter these on the first sheet, entitled "Look-Through BRK earnings and holdings" (as the red text in cell A1 warns you)

I have removed WABCO as it was sold entirely in 2017Q3. Teva is now included.

This version is the one nobody but I can edit. It contains instructions for copying them to your own private spreadsheet so you can edit it to your heart's content in full privacy:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837 (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837)

This version is editable by anyone, but be aware that anyone can see your edit history, so if you enter private information there, it's potentially disclosed to the whole internet! It could also get corrupted by anyone!
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

There's another version that you may request permission from me to edit, here:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing (https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing)

If you'd like a simplified overview of the BRK portfolio, I can recommend http://minesafetydisclosures.com/individual-investor-portfolio (http://minesafetydisclosures.com/individual-investor-portfolio) and for CNBC's Look Through, https://www.cnbc.com/berkshire-hathaway-portfolio/ (https://www.cnbc.com/berkshire-hathaway-portfolio/)

Discussion of discrepancies
The 13-F numbers and the totals agree with one posted on Seeking Alpha, so I'm pretty happy we both entered the information just fine. https://seekingalpha.com/article/4147016-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2017-update (https://seekingalpha.com/article/4147016-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2017-update)

The rocketfinancial version of BRK's portfolio seems to have additional stock in US Bancorp, which they seem to have taken from the 13-G filing. I think something odd is going on there as the 13-F is made up to the same date 31st Dec 2017. I love the way they present it (and the speed it was updated yesterday), and I'm sending them a support email to query it. http://www.rocketfinancial.com/Holdings.aspx?fID=1058 (http://www.rocketfinancial.com/Holdings.aspx?fID=1058)
They might be correct to rely on the 13-G and the rest of us wrong. I'm not sure. Could the difference be amounts held in pension funds etc, not attributable to shareholders?

Phillips 66 (PSX) shows at 45,689,892 on CNBC. The rest of us have 80,689,892, so I think they're wrong there, https://www.cnbc.com/berkshire-hathaway-portfolio/ (https://www.cnbc.com/berkshire-hathaway-portfolio/) meaning they're about $3bn out. The rest matches just fine. I've just contacted them to let them know.

I only update cell AH3 when the annual report tells us the dividends received. This avoids double-counting that portion of look-through earnings that was already reported in GAAP earnings. At the moment this is a year out of date while I wait for the Annual Report.

The Annual Report is usually the only hint to non-US holdings such as BYD, Sanofi (SNW) on Euronext (which I enter as an equivalent number of Sanofi ADRs (SNY)), Società Cattolica di Assicurazione - Società Cooperativa and Home Capital Group (CANADA) - you'll note that these are highlighted differently on the sheets including 13-F LATEST.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on February 15, 2018, 09:51:21 AM
The difference in USB, AAPL, etc, is probably in New England Asset management, under general re on Edgar- but i’m Away from the office and can’t check if that’s your issue at the moment
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on February 15, 2018, 01:51:27 PM
Yeah - I had a chance to look at your sheet and it looks like you are short the following share amounts, which are also owned by Berkshire but reported separately through New England Asset Management:

ticker, number of additional shares not included in BRK's 13F:
AAPL, 4,217,000
BAC, 21,000,000
DEO, 227,750
GS, 431,063
USG, 4,385,964
USB, 17,386,443
VRSK, 2,954,050
WFC, 24,312,200


It makes sense if you remember that BRK got 700 million BAC shares and hasn't sold any.  You are showing 679 million in your sheet.

Here is the most recent New England Asset Management filing - all the stocks with "Other Manager 01 02" are Berkshire's

https://www.sec.gov/Archives/edgar/data/1004244/000108514618000878/xslForm13F_X01/form13fInfoTable.xml

Quote
Phillips 66 (PSX) shows at 45,689,892 on CNBC. The rest of us have 80,689,892, so I think they're wrong there, https://www.cnbc.com/berkshire-hathaway-portfolio/ meaning they're about $3bn out. The rest matches just fine. I've just contacted them to let them know.

They aren't wrong.  They are reflecting the big sale that Berkshire just announced:
http://www.berkshirehathaway.com/news/feb1418.pdf




The difference in USB, AAPL, etc, is probably in New England Asset management, under general re on Edgar- but i’m Away from the office and can’t check if that’s your issue at the moment
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 15, 2018, 03:04:35 PM
Thanks, I've just seen the SEC filing come through on the PSX. I'll update my sheets when I'm on the computer to include the extra stocks via New England that you mention. Probably tomorrow.

If these are beneficially owned by BRK they belong in a Look Through analysis I guess. Does anyone disagree with this plan or the belief that they are to shareholders' benefit?

I'll probably include a separate New England 13F sheet and Berkshire 13F then combine those into aggregate holdings.

Thanks again for the clarifications.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 16, 2018, 05:37:48 AM
OK, I've updated all three versions of the Spreadsheet.

These now include more stock holdings than most BRK portfolio summaries you'll find online:

1. Actual holdings reported in the Berkshire Hathaway Inc 13-F filing at the end of the most recent quarter.
2. Actual holdings belonging to Berkshire in the New England Asset Management Inc 13-F filing (Owner Code "01 02" in Column 7 only) - thank you to globalfinancepartners for steering me here.
3. Assumed holdings of non US securities that are not reportable to SEC but are mentioned in the Annual Report and presumed still to be held unless we hear otherwise.
4. Plus or (minus) acquisitions or (disposals) of securities announced since the last 13-F quarter. This quarter that's the 35,000,000 PSX shares bought back by PSX on 13th Feb 2018 to keep share count below 10% for regulatory reasons.

I may in future cease to maintain the editable version where people can request access soon. It seems sensible just to have two in future:
I believe I cannot just 'Make a Copy' and retain the existing URL I've published, so my copying process is more long-winded, hence the reduction to 2 versions.

The privately editable one (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837) is as I edited it. It's now very simple to create a Copy for your own private use by pulling down the File... menu and selecting Make A Copy... which will create a Copy of the whole workbook in your own Google Drive, where you will have Read/Write access.

The publicly editable version (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing) is definitely not private - so don't post private or confidential information on it even for a moment as even the Revision History is public. It may also get corrupted by anyone else on the internet, so it's less likely to be reliable than a copy of mine unless someone only fixes errors I've made!

As I said above, I will only update cell AH3 when the annual report tells us the dividends received. This avoids double-counting that portion of look-through earnings that was already reported in GAAP earnings. At the moment this is a year out of date while I wait for the Annual Report. I'm not 100% sure whether dividends received on the New England Asset Management holdings show up in Berkshire's GAAP Earnings, but I suspect they would - it makes accounting sense. If not, it may create a minor error in the operating company earnings adjusted to exclude dividends received to avoid this double-counting.

The Annual Report is usually the only hint to non-US holdings such as BYD, Sanofi (SNW) on Euronext (which I enter as an equivalent number of Sanofi ADRs (SNY)), Società Cattolica di Assicurazione - Società Cooperativa and Home Capital Group (CANADA) - you'll note that these are now included in a separate column in the tab 'COMBINED HOLDINGS' where I also included as a negative figure the sale of 35 million PSX shares announced since 31st Dec 2017, an adjustment that will need to be removed once the 2018q1 13-F filing is published in mid May 2018.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on February 27, 2018, 05:40:55 AM
Just to update on this - the primary Berkshire 13F contains some shares held by pension funds.  The New England Asset Management listings are not the holdings of the pension funds, but some number of shares on BRK's 13F linked below are held by pension funds.

( https://www.sec.gov/Archives/edgar/data/1067983/000095012318002390/xslForm13F_X01/form13fInfoTable.xml )

Combining all Apple shares together form both 13Fs (as of 12/31/2017) gives a total of 169.55 million AAPL shares, where Berkshire's annual report shows 166.713209 million AAPL shares (which excludes pension fund holdings).  We know that Todd and Ted manage assets for the pension funds of the various subsidiaries, so the difference could be the shares 'owned' by Todd and/or Ted's accounts.

So it makes it difficult to get a 100% accurate spreadsheet going for Berkshire's look through holdings / real time book value.  But roughly right is good enough for analyzing BRK.  Thanks Dynamic, for sharing your work
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 27, 2018, 05:59:56 AM
Hi @globalfinancepartners. This is what is so great about CoBF - everyone noticing little improvements in accuracy.

A friend and I noticed the differences between the holdings attributable to shareholders and the 13-F at the weekend and I have already adjusted the top holdings (which are the most material) to the exact number of shares shown in the AR on a private Google Sheet that uses the Look Through earnings. There may be other holdings below the level of the top 15 or so that are over-reported. It might be possible to dig through the 13-G forms to ascertain which belong to pension funds (and sanity-check against known examples such as Apple)

I'm snowed under with work today but just realised I can change it very quickly as I put in an adjustment for Pension Funds in my Combined Holdings sheet (the one that combines the BRK 13-F, the NEAM 13-F and the non-US holdings), so I have done so on all three versions of the sheet.

Contrary to the Title, the column for 'Assumed non-US holdings' also now includes these adjustments for the pension fund holdings, which can hopefully be updated every February when the 10-K is released. I imagine we cannot expect more because the 13-F's purpose is in disclosing how much voting power is controlled by a person or entity, not for portfolio tracking.

I'm assuming that no mention of Sanofi in the AR means no change in their Euronext holdings. BYD was in the top holdings, confirming the number I've been using.

Incidentally the Combined Holdings sheet does include links, where applicable, to the news release or other source of information for the 'Assumed non-US holdings' in the rightmost column.

The PSX sale on 13th February 2018 to get below 10% is also included as an adjustment, but I'll need to remove that for next quarter's 13-F.

Here are the links to the two main versions of the Look-Through Google Sheet:
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 16, 2018, 05:05:33 PM
I'm just finishing up analysing the 13-F filings as of 31st March 2018 (filed 15th May 2018)for Berkshire Hathaway and will soon modify the Look Through Portfolio Google Sheets to include this.

As I've said before, 13-F is meant to disclose control/voting interest to the SEC, not to report economic interest in the companies. SC 13G and 13D filings are similarly about voting and dispositive power, not economic interest, but help reveal pension fund holdings.

My version differs deliberately from most of those you'll see on the web, as I am interested in approximately understanding the look-through economic interest we as Berkshire Hathaway shareholders have in the companies whose shares it owns.

Thanks to the valuable help of other forum members since about the end of 2017 my analysis includes holdings reported on the New England Asset Management 13-F filing where the OTHER OWNER is shown as "01 02" (meaning Berkshire).

This makes a significant adjustment in a few of the positions.

I have also used information I have gleaned from the Berkshire 10-K Annual Report and SC 13D or 13D/A or SC 13G or 13G/A filings (e.g. Berkshire's holding in Davita) to adjust for holdings whose economic benefit is attributable to pension funds for employees of Berkshire subsidiaries (in Davita's case, this was over half the holding controlled by Berkshire, for American Airlines, close to 40%). I usually link to these next to the adjustments so you can verify and make any queries on the forum.

R Ted Weschler has some apparent personal and shared holdings listed among the Liberty Sirius XM stocks which I think I didn't need to adjust for, but I might review this with some care shortly.

It will not be completely accurate as Berkshire only has to file stocks traded in the USA to the SEC in this way. I have made some adjustments for declared foreign holdings to the best of my knowledge, usually based on declarations of significant holdings in the Annual Report. Some of these are based on news reports of Berkshire investments and may be only approximately correct regarding holding sizes. Also some of the holdings of pension funds within Berkshire may not be adjusted for.

I have sanity-checked the assumed Apple holding against the 10Q, and it seems to match:
Used -2,837,753 adjustment at year-end to match page 9 of 2017 Annual Report figures (to exclude pension scheme assets). At 31/03/18 AAPL closing price was $167.78. Calculated market value using my adjusted figure was $40,676,730,846, which matches the $40.7 billion (rounded to $0.1bn) mentioned on p11 of 2018Q1 10Q, so I will not assume any change in pension scheme holdings unless I find them disclosed in SC 13G or G/A filings - I gone back to about 31 Dec 2015 with those filings.

A few comments about the new interpretation or quarterly changes to holdings (in ticker symbol order):

The holdings reported are those attributable to shareholders, not the total holdings controlled by Berkshire which may include pension fund holdings. The adjustment mentioned may be larger than the shareholders' holding (e.g. DVA)

AAL - holding unchanged at 25,258,000 but newly adjusted by -20,742,000 for pension fund holdings
AAPL - 242,440,880 for shareholders, 75,727,671 added (+46.2%) already adjusted for pension holdings
AXTA - unchanged 20,000,000 shares but newly adj for pension fund holdings
BK - 54,680,199 for shareholders, 1,372,665 added (+2.3%) assuming unchanged pension holdings
CHTR - 6,522,536 for shareholders, -266,518 reduce (-3.1%)
DAL - 53,599,357 add 488,962 (+0.9%)
DVA - 18,513,482 unchanged but -20,052,088 pension adjustment newly noted
IBM - eliminated -2,048,045 (-100.0%)
LBTYA - 17,906,408 reduce -2,274,489 (-11.3%) possibly to stay <10% of class
LILA - 1,625,185 unchanged but -1,089,669 pension adjustment is newly noted
LSXMA - 10,552,243 unchanged but -4,308,117 pension adjustment newly noted - R Ted Weschler's private holdings noted in form SC 13G but not 13-F, I think, so no adjustment needed
LSXMK - 23,942,958 unchanged but -7,148,027 pension adjustment newly noted - R Ted Weschler's private holdings noted in form SC 13G but not 13-F, I think, so no adjustment needed
MON - 18,970,134 add 7,261,387 (+62.0%)
PSX - 39,587,892 reduce -35,000,000 (-43.4%), assuming still -6,102,000 pension holdings adjustment
SNY - 44,134,997 assuming no sale of Euronext stock (non-reportable)   reduce -177,512 (-4.6%)
UAL - 26,114,584 reduce -505,600 (-1.8%), assuming -1,591,379 adjustment for pension holdings unchanged
VRSK - 3,238,828 reduce -1,278,656 (-81.8%) - N.B, majority held by New England Asset Management
VRSN - unchanged at 7,905,481 but noted -5,047,264 pension holdings
WFC - 480,825,444 reduce -1,719,024 (-0.4%) probably to stay <10% avoiding bank holding co status
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on June 11, 2018, 09:49:28 AM
I've just updated the sheets with all the changes mentioned above.

I also note that it's now very easy to make your own copy of my spreadsheet - just use the File/Make a copy... command in Google Sheet, so I changed the instructions sheet - now much simpler!

There was a $128.00 per share cash takeover of Monsanto (MON) by Bayer on 7th June, closing out this presumed merger arbitrage position, worth almost $1 per BRK.B share in total. I've assumed close to $0.09 per BRK.B share after tax profit over the 6-9 months the position has been held.

I have hard-coded MON (Monsanto) to $125.56 market price. This reflects the estimated cash left after an estimated average purchase price of $116.38, and $11.72 USD pre-tax gain per share. Tax at 21% ~= $2.44/sh. Approx 7.9% after tax gain estimated in 6-9 months. In practice Berkshire may well have bought more MON stock this quarter too and the gain could have been a little more or a little less, but better to be roughly right etc.

I've also hard-coded the EPS for MON to essentially zero (I actually set it to 1e-18 USD to avoid #DIV/0 errors, which is essentially zero) as this position is mainly cash now, and will add to the cash balance at the end of this quarter, when the 13-F will be updated (in mid August).

Within 6 months, the USG deal for $44 per share acquisition by Knauf will close (https://www.bloomberg.com/news/articles/2018-06-11/buffett-to-exit-usg-investment-as-7-billion-knauf-deal-accepted), too, but for now I'll leave it on the spreadsheet. That deal will be $0.50 per share as a special dividend plus $43.50 per share in cash. For now, I expect the stock price to track an estimated time-value discount to the takeover price plus dividend, remaining close to $43-44 per share.

The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on July 03, 2018, 06:47:23 AM
I believe (please correct me if I'm wrong) that the Berkshire interest in Kraft Heinz Co. (KHC) is consolidated in Berkshire's earnings statement, so it would be double-counting to include its Look-Through earnings of almost $0.44 per year per Berkshire Class B share, in the Look-Through Portfolio EPS calculations (in column AH) as they're already included in Berkshire's GAAP EPS and Operating earnings (those being earnings before the mark-to-market adjustments that now must be carried into the income statement, but which Berkshire presents alongside the new GAAP figure for consistency and comparison with past performance).

I also presume that the mark-to-market 'earnings' from changes in the stock portfolio market value would specifically exclude the market-value change of KHC, this being considered a consolidated subsidiary entity.

You can find and copy the Google Sheets as before:
The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

Caution: Note that EPS figures for portfolio components taken from Google Finance may include historic figures over the most recently filed 4 quarters with a mixture of US tax-rates assumed (e.g. old 35% federal rate and new 21% federal rate), so EPS shown may be materially below EPS by end of 2018.

The results include, at about 09:00 EDT on 3rd July:
Market Value of Look-Through Portfolio except KHC = $72.31 per BRK.B share.
EPS of Look-Through Portfolio = $3.7860 per BRK.B share (excluding KHC, which earned $0.4379 per BRK.B)
P/E ratio of portfolio = 72.31/3.7860 = 19.1. Earnings yield = 5.2%

Market Value of remainder of BRK.B (including cash) = $115.52 per BRK.B share (at 2nd July 2018 closing price of $187.83)
EPS less portfolio dividends for Operating part of Berkshire = $3.2229 per BRK.B share
Look-Through P/E ratio of Operating part = 115.52/3.2229 = 35.8. Earnings yield = 2.8%

From 2018Q1 10-Q p6:
 Cash and cash equivalents and restricted cash at end of first quarter = $58,374mn
Therefore Cash per class B share = $58,374mn/2,467.6mn = $23.66 per BRK.B

Market Value of Operating part of BRK.B less cash per share = $91.86
P/E ratio of same = 28.5. Earnings yield of same = 3.5%

Total Look Through Earnings Per Share of BRK.B = $7.0089 per BRK.B share
Look-Through P/E ratio = $187.83 / $7.0089 = 26.8. Earnings yield = 3.7%
Look-Through P/E net of cash* = $164.17 / $7.0089 = 23.4. Earnings yield = 4.3%.

*This assumes cash is taken at face value. If cash is used to fund undervalued acquisitions or purchase undervalued stock, it may have higher ultimate value (optionality premium). If you are comparing Berkshire to alternative investments without 'cash drag' you might wish to discount its value.

There's also some additional cash included in the look-through sheet, such as the estimated after-tax proceeds of the Monsanto acquisition by Bayer, which will show up in the Q2 financials released in early August 2018. The USG acquisition will eventually add to the cash position also. Some of this, however, might have been invested in the mean time, for which we'll await the 13-F filings later in August.

I'd anticipate the Look-Through EPS and Book Value Per Share are both likely to compound at around 10% cagr (8-12% range) in the long term (6-9% above inflation) regardless of the cash build up at this point in the cycle, but your mileage may vary.


Miscellaneous Adjustments to the Spreadsheet - feel free to ignore these
I've altered a few of the columns around columns AE and AF.

Column AE is now for EPS including KHC, and AF is now the EPS excluding KHC column.

The green shaded columns AH and AI are those I consider 'correct' for calculating look-through earnings as they exclude KHC. The sheet carries through the totals of these columns at the foot of the page into the Look Through Market Value (copied to cell D7) and the Look Through EPS of the portfolio in cell S7. The Total EPS in S9 is the sum of this figure plus the operating earnings of Berkshire (including its share of KHC), less the dividends received from the stock portfolio (these dividends, shown in the financial statements should not include dividends from KHC as these are considered distributions from a consolidated subsidiary, I believe, so I don't need to adjust further)

The blue text on a pink (light red) background includes KHC. You might want to know these figures for some specific purposes of your own, but as KHC is consolidated, they don't now find their way into cells D7 or S7.

I also made a few improvements to the robustness of the sheet possibly to do with the update going on with Google Finance, which sometimes throws up error messages that it used not to, and which may also speed up the calculations by reducing unnecessary GOOGLEFINANCE() queries:

I fixed the currency for Monsanto (no longer trading since Bayer takeover) to USD as their debt in Canada is now showing under symbol MON and reporting the currency as CAD instead. After the 13-F and 10-Q in August, this will become irrelevant as the after-tax cash proceeds of the takeover will be reflected in the financial statements.

I've also made the sheet a bit more resilient to changing functionality of the GOOGLEFINANCE() function which I have reported but which hasn't been permanently fixed - I guess it depends whether their data provider at any moment returns a value or not in some edge cases.

Where both currencies were the same, e.g. CURRENCY:USDUSD, it used to return 1.0, but now returns #N/A error sometimes, meaning any dependent cells also show #N/A error. I now check if they match and return 1.0 if they do.

On my personal sheets, I sometimes track UK shares priced in British Pennies GBX but want to convert to British Pounds GBP, my home currency, so on those sheet I also check for that and return 0.01 to fix a similar sporadic bug that covers my sheet in error messages.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on July 03, 2018, 06:51:36 AM
You have the gist right on KHC.  It is not consolidated technically, it is accounted for under the equity method.  The effect is the same for your purposes though.  Berkshire's share of the earnings are already included without the need to 'look-through.'
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on July 03, 2018, 06:54:44 AM
You have the gist right on KHC.  It is not consolidated technically, it is accounted for under the equity method.  The effect is the same for your purposes though.  Berkshire's share of the earnings are already included without the need to 'look-through.'

Thanks GFP, I was hoping you'd tell me if I had the essence correct or not.

Would I also be right to imagine that changes in KHC's market price would not carry over to Book Value except perhaps in the annual report where Berkshire might reassess its carrying value, as I think it did in the 2017 Annual Report?
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on July 03, 2018, 07:00:39 AM
Correct, wiggles in KHC share price will not effect our carrying value unless they do another deal that involves BRK's ownership percentage changing.  BRK had to mark up their carrying value for KHC in connection with the Kraft merger because for GAAP purposes when your ownership goes from 52% to 26% or whatever the net result was, they treat it as if you sold a proportional piece of your position at the merger price (not for tax purposes obviously).

Wiggles in other stocks in the portfolio will now pass through the income statement, and always were reflected in carrying value (net of their deferred tax liability).
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on July 03, 2018, 08:02:15 AM
Thanks for your response, GFP. I'm reassured that the look-through sheet is a better tool now than before these changes and the various others (such as accounting for the New England Asset Management and pension holdings) that you inspired earlier in this thread.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 09, 2018, 12:52:20 AM
A few minor changes just made updating Berkshire BV/share and share count. Nothing significant unless you want to monitor the current Price-to-Book Value ratio in cells X3 and X4.

The spreadsheet does NOT include the latest estimated Apple holding (up about 2% in share count) as the 13-F is soon due for release and it can be updated then to a more exact figure rather than mess with the established adjustment for pension scheme holdings. If you wish to account for changes to Apple and Wells Fargo, you can make a copy on your own Google Drive and easily add or subtract from the formula in the Adjustment column in tab "Combined Holdings".

This sheet also includes MON (Monsanto) with a estimate of the after-tax proceeds from its takeover. This cash and tax should be in Berkshire's quarterly financials so I'll remove the Monsanto proceeds when updating after the 13-F is released in a week or two.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 10, 2018, 08:10:38 AM
Berkshire appears to have increased its stake in AXALTA COATINGS SYSTEMS LTD. - SEC Filing Schedule 13G/A (https://www.sec.gov/Archives/edgar/data/1067983/000119312518244995/d590416dsc13ga.htm).

The beneficial ownership we count on as shareholders had been 20,940,000 shares and has increased by 940,000 to 21,880,000 shares.

There are also 2,384,000 shares for the benefit of various pensions which are controlled by Berkshire but not for their economic benefit. These pension holdings are unchanged except that Berkshire has consolidated some of its pension plans (three that held 700,000 shares between them before are now in the Berkshire Hathaway Consolidated Pension Plan, which now holds those 700,000)

Including these too, on the last 13-F it controlled 23,324,000 shares, now increased by 940,000 to 24,264,000, which is shown as 10.1% of the outstanding shares known on the filing date.

The event that resulted in this filing was 14 days ago (10 working days) - July 26th. I guess they paid around $30 per share judging by recent price action.

I will not adjust the Look-Through spreadsheet to include this until after the 13-F filing is released, and then I will link to that 13G/A filing in the worksheet 'COMBINED HOLDINGS'.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 14, 2018, 06:28:19 PM
Both sheets now updated for the latest 13-F filings for Berkshire Hathaway (https://www.sec.gov/Archives/edgar/data/1067983/000095012318008866/xslForm13F_X01/form13fInfoTable.xml) and Owner 01 02 in New England Asset Management (https://www.sec.gov/Archives/edgar/data/1004244/000108514618002153/xslForm13F_X01/form13fInfoTable.xml) (NEAM).

As always I net out holdings by pension funds to show only beneficially owned shares and I also make adjustments for known or previously declared foreign holdings believed to be held, but not required to be reported to SEC in 13-F. My holdings are thus a little different from most Berkshire portfolio updates you'll find online.

The Axalta holdings in the 13-F seem no different to the 13G/A referred to above, and the pension fund holdings in that 13G/A were unchanged in total number compared to the previous quarter.

As Berkshire's press release warned, some financial and airline stocks were trimmed to remain below 10% while other financials and airlines were added to during the same quarter.

The estimated Apple holding (based on the 10Q's holding value divided by the closing price) was correct to 3 significant figures and does represent an increase above the 5% threshold of voting power.

You can find and copy the Google Sheets as before:
The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

I removed the estimated after tax Monsanto buyout proceeds that I put in during June now that the share count is reduced to zero. The PSG holding is still there. I still include various eliminated positions on the Look-Through sheet, which come to zero.

There's a summary of the changes from quarter to quarter on the tab COMBINED HOLDINGS, columns D, E and F.

AAL: -5.1% of beneficially owned shares (-1,300,000 shares) presumably to stay below 10% voting power including large non-beneficial pension fund holding.

AAPL: +5.1% (+12,388,244 shares) taking ownership over 5%

AXTA: +4.5% (+940,000 shares) as disclosed in 13G/A (prev post)

BK: +4.8% (+2,608,928 shares) % assuming none of new stake owned by pension funds.

CHTR: -11.0% (-718,688 shares) % assuming none sold were pension holdings.

DAL: +18.8% (+10,066,483 shares)

GM: +3.1% (+1,393,611 shares)

GS: +20.1% (+2,294,971 shares)

JNJ: +8.7% (+28,832 shares)

LBTYA: +10.5% (+1,884,592 shares)

LUV: +18.6% (+8,887,943 shares)

MON (Monsanto): -100% (-18,970,134 shares) due to Bayer acquisition.

PSX: -27.7% (-10,960,378 shares) % assuming none sold by pension funds.

TEVA: +6.7% (+2,709,585 shares)

UAL: -3.9% (-1,021,421 shares) presumably to stay below 10% voting power including pension funds.

USB: +10.5% (11,273,496 shares)

VRSK: -14.2% (-458,692 shares)

WFC: -0.5% (-2,295,019 shares) to stay below 10% to avoid bank holding co status

No new positions. Only Monsanto eliminated this quarter. VRSK, while eliminated from BRK's own 13-F, was still present in NEAM 13-F [Edit: VRSK not AXTA as I previously stated in error].
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on September 27, 2018, 12:37:01 PM
Just edited both versions to account for the sale of Philips 66 (PSX) announced on Sep 10th 13G/A filing.

I've added a line for the pre-tax proceeds of the sale, as this cash is available for investment but the actual post-tax proceeds will be reflected in the balance sheet for Q3 when release in November, and I'll remove it once the 2018-Q3 13-F is filed.

I assumed it raised about $117 per share on selling 12.5 million shares, raising around $1.467 billion pre-tax ($1.467 bn). Assuming the cost basis is still around $78.31 as it was at 2017 Q4, this gives about $484 million estimated capital gains that might have about a $97 million tax liability at 20% (about $7.74 capital gains tax per share sold). The $117 is essentially hard-coded in the price. The remaining stock is still shown at current market price. If you want to account for the estimated tax, you could hard-code the price as $109.26.

I'm including the gross proceeds in the portfolio until the end of the quarter just to aid in tracking its total value. The cash proceeds don't contribute to look-through earnings any more. In the quarterly financials, the cash balance and the tax liability will be updated to include the effects of the sale and I'll then remove the cash. This is much the same as happened during Q2 when Monsanto was acquired by Bayer for $128 per share.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 15, 2018, 09:46:55 AM
I have now updated the Look-Through Spreadsheet with the latest 13-F filings plus the purchase of STNE at its IPO which was disclosed last week.

The Book Value per share in cell S5 is as at 30th Sep, but the Berkshire shares outstanding are as of 25th Oct per page 1 of the report, a reduction of 0.1% or so.

As always I net out holdings by pension funds to show only beneficially owned shares and I also make adjustments for known or previously declared foreign holdings believed to be held, but not required to be reported to SEC in 13-F. My holdings are thus a little different from most Berkshire portfolio updates you'll find online.

Doubtless a few of the holdings were trimmed to remain below regulatory thresholds such as 10% or 5% of the shares outstanding when the companies were expected to be buying back and retiring shares.

You can find and copy the Google Sheets as before:

The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

The key unknown is the Euronext Paris shares of Sanofi which never gets reported to the SEC in 13-F filings and were only known from the 2016 (I think) Annual Report 10-K, released in Feb 2017. I've left the equivalent number of SNY ADR shares (I think from memory that the ratio is 2:1, but it's easier to report the value directly in USD currency instead of EUR). All the ADRs have been sold. If the Euronext Paris shares were sold also, it might change the portfolio value by about $1.8 billion.

The total valuation of my Look Through portfolio when set to 28/09/2018 closing prices (excluding KHC, which is accounted for using the Equity Method) was about $1.34 bn below the market valuation of equity holdings shown in the 10-Q filing, so I may well be missing something or subtracting pension holdings that are no longer held from the totals shown in 13-F. These might be pension holdings that I once deduced from comparing an annual report to the year-end 13-F but not a 13D or SC 13G filing but have not found an update on since. I might take a look for some likely culprits (including currency effects I may not have got corect for foreign holdings) and report back. It's probably good to make some best guess assumptions to be a bit more roughly-right and a bit less precisely wrong.

There's a summary of the changes from quarter to quarter on the tab COMBINED HOLDINGS, columns D, E and F. This is only by share-count not market price, but below is a commentary on the changes which I posted in the General news topic:
I'll update my public Look-Through portfolio sheet soon, but for now here's a summary of the moves.

The following figures are for the shares beneficially owned by shareholders, so they exclude known Berkshire pension scheme holdings. They also include stocks held via New England Asset Management for Berkshire but filed in NEAM's 13-F with owner code '01 02'.

AAL -4.2% -1,000,000 shares American Airlines falling to 22,958,000. The other 20,742,000 are in pensions. (buyback related reduction?)
AAPL +0.2% +522,902 shares Apple up to 252,810,459. Another 2,837,753 in pensions.
BAC +28.6% +200,000,000 shares Bank of America Corp up to 900,000,000
BK +42.1% +24,111,850 shares Bank of New York Mellon up to 81,400,977 (plus 7,511,249 assumed in pensions)
CHTR -2.8% -163,200 shares Charter Communications down to 5,640,648. (1,700,337 more in pensions)
DAL +2.9% +1,869,160 shares Delta Airlines up to 65,535,000
DVA - unchanged but own a higher percentage due to DaVita buybacks
GHC - eliminated Graham Holdings position (was 107,575 shares)
GM +2.3% +1,067,800 shares General Motors up to 46,988,558 (5,472,853 more in pensions, assumed)
GS +37.3% +5,099,145 shares Goldman Sachs up to 18,784,698
JNJ -8.0% -28,382 shares Johnson & Johnson down to 327,100
JPM New Position 36,209,767 shares JPMorgan Chase & Co
LUV -0.9% -500,000 Southwest Airlines down to 56,047,399 (buyback related)
ORCL New Position 42,691,791 shares Oracle Corp
PNC New Position 7,187,819 shares PNC Financial Services Group Inc
PSX -55.6% -19,296,490 shares Phillips 66 down to 15,433,024
SNY - Reduced/Eliminated uncertain. -3,701,012 shares in US ADRs eliminated. Euronext Paris stock is not reported on 13-F. May still hold approx 40,433,985 equivalent SNY ADRs based on 2016 10-K via Paris
TRV New Position 3,586,688 shares Travelers Companies Inc
UAL -2.8% -700,000 shares United Continental Holdings Inc down to 24,393,163 (1,591,379 more in pensions) probably buyback related
USB +15.3% +18,203,375 shares U.S. Bancorp up to 137,120,760 (590,275 more in pensions)
VRSK -64.0% -1,779,811 shares Verisk Analytics Inc down to 1,000,325 (all remaining shares in NEAM now)
WFC -2.8% -13,374,425 shares Wells Fargo & Co down to 465,156,000 (buyback related trim)
WMT Eliminated Wal-Mart Stores Inc position (was 1,393,513 shares)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: AdjustedEarnings on January 11, 2019, 01:07:11 PM
Dynamic (and others), where in this spreadsheet can one see the drop in the BV attributable to the equity portfolio since the last reporting date? I'm calculating about a $9.3 (net of tax) per share reduction from the portfolio and then a $2.45 (everything is per B-share) increase from operations, for a total of $6.85 reduction. But mine is a rough calculation and this sheet seems a lot more detailed. Would love to know where I can see this info. Thank you!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on January 12, 2019, 04:45:16 AM
It isn't on the spreadsheet.  You'd need to calculate the portfolio value with the closing prices on the last trading day of that quarter which is not on this sheet - it's just the best estimate of the current live portfolio not the one at previous times
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on February 11, 2019, 07:18:33 AM
Dynamic,

The last couple of days I've been tinkering around - on and off - with the bank positions [EOP 2018Q3] Berkshire has in its tummy, with the aim to gain an overview of all those positions.

I use your file as "reconciliation anchor" [because I know you have put a lot of work into that file] - thank you very much for sharing your work!

In the "View only" file, tab "Look-Trough Summary", row 19, you have data [i.e. company name, shares owned by Berkshire] for KO, but the ticker "JPM". The actual outcome is that KO shares are double recorded [row 14 & 19], while JPM shares are missing. [It's a bit of a mystery to me how this has happened, because you're correctly using relative cell references [to the contrary of partially or fully absolute cell references].] -At least it's fixable quick! [ : - ) ]

-Again, thank you very much for sharing your work!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 11, 2019, 08:38:30 AM
Thanks for spotting that, John.

All fixed now.

I probably didn't notice that I'd omitted to copy the formulae from the cell above or below when I inserted it on the Look Through summary sheet as I usually look at the front sheet instead and not the Summary.

Something else happened the other day with Coca Cola (KO) (on my private copy at least), where GoogleFinance was throwing an error and reporting its currency as ARS (Argentine Sol) but that just got sorted out.

So, as always, be careful before using this sheet to inform investment decisions. It's possible that errors will be present from me or from GoogleFinance.

I'm glad people are finding the sheet useful and please feel free to point out any problems you spot.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on February 11, 2019, 03:54:19 PM
Dynamic,

Attached is my work in progress file, related to the US banks that Berkshire has positions in. Please note, that the filename contains "Draft" [ : - ) ]

On the "Overview" tab I have added two temporary columns, containing the reconciliations to your file. Pink cells in variance column are those of interest.

The two other tabs contains data from SEC, that have been processed this way:
- - - o 0 o - - -

Here are my conclusions on comparison with your file :

For WFC, AXP, JPM, PNC & TRV the difference consist a separate line in the NEAM 13-F/HR, that are not included in your file. Those variances are immaterial, hvorever I ask if there is a specific reason to that those rows [marked pink in NEAM 13-F/HR tab] are omitted in your file?

For USB it's about two rows - a row in the BRK 13-F/HR tab and a row in the NEAM 13-F/HR tab [also marked pink].

I haven't been able to find an explanation about the variance for BK.

- - - o 0 o - - -

I hope you will take the time to give those particular data in pink cells in my file a critical review, for the purpose of getting mutual better precision by collaboration - thank you in advance - and no hurry! [ : - ) ]
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 12, 2019, 04:23:46 AM
Hi John,

If you want to compare figures to mine, I do include all lines I've extracted from each 13-F in columns in my tabs (usually 6th and 7th tab) called BRK ONLY 13-F LATEST and NEAM 13-F 01 02 items.

The first one I found out where the discrepancy arose is Wells Fargo, and I've stopped there for now. I imagine the others could be similar.

There appears to be an error on the cover page primary_doc.html (https://www.sec.gov/Archives/edgar/data/1004244/000108514618002756/xslForm13F_X01/primary_doc.xml) of just the November 13-F filing for New England Asset Management, which I hadn't noticed until now. I believe it usually lists under List of Other Included Managers: 1 and 2, but in this case appears to have put:
1 BERKSHIRE HATHAWAY INC and
1 GENERAL RE CORP  which should be number 2 as per previous 13-F filings I've seen from them, I would think, and as user gfp ('globalfinancepartners' at the time) has explained to me in previous posts long ago on this thread.

On the information table (https://www.sec.gov/Archives/edgar/data/1004244/000108514618002756/xslForm13F_X01/form13fInfoTable.xml) of the 13-F it is ONLY where the MANAGER column states 01 02 that you should include this as a Berkshire Hathaway holding.

The 39,467 shares of WFC discrepancy in the Nov 2018 13-F do not show 01 02 in that column, so you should not have included them in your total held at New England Asset Management if my understanding is correct.

As you're pasting the data from the 13-F, there are a couple of ways to process the data. You could Conditional Format cells in that column if they contain the text "01 02" to be a different colour or background colour. Fortunately, there's only ever one line of "01 02" Berkshire holdings for any stock on the NEAM sheet (unlike the Berkshire 13-F which has a variety of different managers and combinations of managers within subsidiaries)

Or you could add a column of your own for BRK holdings or do a conditional SUM such as SUMIF or SUMIFS.

For example, in a new column, say column M, you could put a formula in cell M4, then autofill down the rows of this form:

=IF($I4="01 02",$E4,"")

When I just did that, none of the rows you highlighted pink gave a number, so I think that's the source of the discrepancies you have.

You could also filter the table to show only "01 02" in the Manager column, and it should be fine and agree with mine. Certainly none of the pink rows show up when I filter that way.

When I input the 13-F holdings onto my sheet, so that you can easily compare mine and yours to check for errors in either, I put them in two worksheets (6th and 7th tabs) called BRK 13-F LATEST and NEAM 01 02 items.
In there I put the various rows applicable to Berkshire into columns and add up the numbers to get the total holding.

For historical data and to assist in showing quarterly changes I also keep old copies of the 13-F filing sheets.

The 5th tab is COMBINED HOLDINGS and includes both BRK and NEAM figures from their respective sheets and any adjustments for other holdings or deductions for Pension Fund holdings, along with a link or descriptive text to explain the adjustments or the source of that information. With Pension Fund holdings I usually keep the sum of all the pension fund holdings as a formula so that I can easily change such holdings in the formula bar if certain funds change their holdings and compare one 13-G filing to another to spot any adjustments.

BTW, I agree that pasting from the HTML page into Excel or other spreadsheets can be terrible. It's made worse for many non-English speaking countries who use . as a thousands separator and , as a decimal separator, so your locale defaults mess with it even more! Fortunately, there are no US format dates to worry about!

Maybe I'll one day implement something to parse the XML format (which I think is JSON data) and automate as much as possible, though I'm sure there may still be problems such as a mis-spelling of MONSANTO as MOSANTO a few quarters ago that will need manual intervention at times, though the CUSIP number might allow automation to work better.

Perhaps USB and BK will still have discrepancies after you've removed non "01 02" figures from NEAM, and if so, let me know and I'll see if I can spot the reasons.

Thanks for your analysis and problem spotting.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on February 12, 2019, 04:44:53 AM
Hi Dynamic,

Thank you very much for taking the time to explain this in depth and detail to an ignorant like me. Christ, this is complicated! Suddenly I really & finally realize how complicated it must be for you to maintain your file.

I'll give my own file yet another spin, based on your explanations & guidance, and then post again. I'm pretty sure it'll weed out the vast majority of the variances, if not all.

-Thank you!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 12, 2019, 05:12:02 AM
Yeah, it took a bit of learning and guesswork and comparison to other sources to reverse engineer what I'd done wrong before I internalised how these filings are laid out. Good luck with your adjustments, John.

And I cannot thank gfp enough for the guidance, particularly early on in the process.

Eventually, after noticing some 13-D or D/A and 13-G or G/A filings mentioned the pension funds, I decided to trawl back through those sorts of filings for a few years and it made the beneficial ownership to Berkshire shareholders pretty much match up with the figures shown in the annual reports, so I was fairly sure those adjustments were correct.

I think there may still be small discrepancies. For example, Berkshire Hathaway Energy holds the BYD stake and is (from my memory) 90% owned by Berkshire. It may be that we should account for the 10% minority stake in BHE when working out our true look-through ownership. I'd be interested in hearing other people's views on that, but I think the spreadsheet is already better than most sources that ignore NEAM and pensions for estimating our Look Through ownership.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on February 12, 2019, 09:31:25 AM
... , but I think the spreadsheet is already better than most sources that ignore NEAM and pensions for estimating our Look Through ownership.

It's not only the best one, but also the only one [to my knowledge], and thereby also both moaty and anti-goaty. [Among Danes, if you're tinkering with something, and you end up far from what you aimed to achieve [called failure, in short], it's in Danish language - among other things - called "There has gone goat into it."].
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 13, 2019, 01:00:05 AM
Thank you, and by extension, thanks go to all those who've contributed in this thread. I'm glad it's useful to people who want to look at it truly from a look-through ownership perspective. It actually has a few advantages when you get a 10-Q before the 13-F too, as Berkshire reports the value of its largest positions at quarter end

Still, if it can be improved a little, by taking account of a few small factors like BHE's minority ownership's look-through proportion of BYD, I'd be glad to improve it. On my COMBINED HOLDINGS sheet that would simply require a formula in cell I40 such as =0.9*225000000 (or just the value 202500000), though I prefer the first option as it explains why it's less than shown in the 2016 Annual Report even if the comment gets deleted.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on February 13, 2019, 07:44:01 AM
Thanks for the spreadsheet dynamic.  Looking forward to seeing Berkshire's 13F at the end of the week - I guess Friday evening? 
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 13, 2019, 08:31:32 AM
Not sure of the date. I read that rule 13 specifies within 45 days from the end of the quarter for 13-F disclosure, so I think it might even be tomorrow, Thursday 14th Feb. I imagine they'll leave it as late as possible to delay releasing information that may compromise their ability to keep purchasing new positions at cheap prices.

Looking backward over the last year we have the following four quarterly 13-F filing dates:

2018/11/14 = 31inOct + 14inNov = 45 days after 2018/09/30 quarter end
2018/08/14 = 31inJul + 14inAug = 45 days after 2018/06/30 quarter end
2018/05/15 = 30inApr + 15inMay = 45 days after 2018/03/31 quarter end
2018/02/14 = 31inOct + 14inNov = 45 days after 2017/12/31 quarter end

Looking at that, in the past year it's always exactly 45 days, so unless it's a non-working day or non-trading day and it gets brought forward to the working/trading day before, I imagine it will always be Valentine's day for the first of the year, 15th May for the second and 14th Aug and 14th Nov for the others. Any later without good reason or special dispensation and presumably Berkshire would fall foul of the SEC.

However, I've seen people posting elsewhere (e.g. comments about Apple on a Charlie Munger fans' Facebook Group) that they think it will be Friday and I think I've seen it elsewhere too in comments. Mind you, I wouldn't expect most people care enough to be aware of the 45-day rule and they're probably more used to the idea of publishing the 10-Q and 10-K filings on a Friday or Saturday after the markets have closed for the week so that everyone has time to digest them.

I'm strongly guessing late on 22nd February for the 10-K, though I'm not sure what the rules are and whether it could even extend to 1st March.

The other one I've read a little about in the filings seems to be 13-D and 13-G type filings. These apply where more than 5% of a class of stock is owned and I believe they have a much shorter time period (might have been 5 or 15 days or trading days) before they must be filed after the transition across the 5% barrier either way is known, when certain material events happen (e.g. Knauf's offer for USG), or when the percentage changes by about 1%. Sometimes, assuming Berkshire has not received an exemption from public disclosure for a specific position so long as they tell the SEC, we might be able to infer that a certain amount of buying has NOT happened within a certain period. For example, that they have not bought another 1% of Apple's outstanding common stock in the first part of January, which would not have been disclosed on tomorrow's 13-F.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on February 13, 2019, 09:16:54 AM
Great analysis, Dynamic,

Thank you. I just looked at the filing of the EOP 2017Q4 13-F/HR at the SEC website. That was filed on February 14th 2018, and if you look carefully at it at the SEC website, it's attributed a time stamp for acceptance : "2018-02-14 16:03:31".

February 14th 2018 was a Wednesday. January in every year has the same number of days [31]. So, based on those observations, combined with the content of your last post, I will be surprised, if we don't see the 13-F/HR filing for EOP 2018Q4 after closing tomorrow.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 13, 2019, 09:26:06 AM
Good spot, John. After tomorrow's close seems highly likely. For me that's on the evening of Valentine's Day so my attention will be focused on my wonderful wife, rather than on EDGAR (https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0001067983&owner=exclude&count=40&hidefilings=0), who would otherwise get named in divorce proceedings.   :P
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 15, 2019, 06:15:59 AM
There were a load of 13G filings as well as the 13-F filed on 14th Feb 2019. I looked at them all. These verified the total figures from adding NEAM and BRK filings and revealed whether pension trusts were holding some of them (which I then subtract to obtain shareholder ownership).

I made a mistake earlier by forgetting to remove adjustments I'd made to the Apple totals. The Apple total now matches on 13G compared to my Combined 13-F total (BRK + NEAM), so I've removed my erroneous figures.

* 15 March - I also corrected an error on SU (Suncor) holding spotted by John Hjorth in the sheets and the table below. The table below shows only the DAL (Delta Air Lines) holding at 31 Dec 2018, ignoring the additional purchases taking it over 10% in March 2019.

[edit after close on 5th March 2019]
I found a further three mistakes (http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg364011/#msg364011) and have now corrected these figures to reflect those. The LUV error was so small it doesn't change the figures shown below.

Summary of quarterly changes (see COMBINED HOLDINGS tab on spreadsheet when it is updated):
BK__________ Bank of New Yor…llon Corp ___3.79% _______3,087,774
CHTR________ Charter Communi…tions Inc __-4.33% ________-307,486
COST________ Costco Wholesale Corp____ ____unch _unchanged count
DAL_________ Delta Air Lines, Inc_____ ____unch _unchanged count
DEO_________ Diageo P L C Spon ADR New ____unch _unchanged count
DVA_________ DaVita HealthCa…tners Inc ____unch _unchanged count
GHC_________ Graham Holdings Co_______ ________ ________________
GM__________ General Motors Co________ __77.78% ______19,808,285
GS__________ Goldman Sachs Group Inc__ ____unch _unchanged count
HCG_________ Home Capital Gr… (CANADA) ____unch _unchanged count
IBM_________ International B…ines Corp ________ ________________
JNJ_________ Johnson & Johnson________ ____unch _unchanged count
JPM_________ JPMorgan Chase & Co______ __39.91% ______14,451,627
KHC_________ Kraft Heinz Co___________ ____unch _unchanged count
KO__________ Coca-Cola Co_____________ ____unch _unchanged count
LBTYA_______ Liberty Global …c Class A ____unch _unchanged count
LBTYK_______ Liberty Global …c Class C ____unch _unchanged count
LILAK_______ Liberty LiLAC Group C____ ____unch _unchanged count
LSXMA_______ Liberty Sirius … Series A ____unch _unchanged count
LSXMK_______ Liberty Sirius … Series C ____unch _unchanged count
LUV_________ Southwest Airls Co_______ __-2.44% ______-1,200,000
MA__________ MasterCard Inc___________ ____unch _unchanged count
MCO_________ Moody's Corporation______ ____unch _unchanged count
MDLZ________ Mondelez Intern…ional Inc ____unch _unchanged count
MON_________ Monsanto Co New__________ ________ ________________
MTB_________ M&T Bank Corp____________ ____unch _unchanged count
NASDAQ:LILA_ Liberty LiLAC Group A____ ____unch _unchanged count
ORCL________ Oracle Corp______________ -100.00% _____-42,691,791
PG__________ Proctor and Gamble_______ ____unch _unchanged count
PNC_________ PNC Financial S…Group Inc __30.27% _______2,175,743
PSX_________ Phillips 66______________ _-22.92% ______-3,537,182
QSR_________ Restaurant Bran…ional Inc ____unch _unchanged count
RHT_________ Red Hat Inc______________ _**NEW** _______4,175,792
SHE:002594__ BYD Company Limited______ ____unch _unchanged count
SIRI________ Sirius XM Hldgs Inc______ ____unch _unchanged count
SNY_________ Sanofi (incl Eu…v shares) ____unch _unchanged count
STNE________ StoneCo Ltd._____________ _**NEW** ______14,166,748
STOR________ Store Capital Corp_______ ____unch _unchanged count
SU__________ Suncor Energy Inc New____ _**NEW** ______10,758,000
SYF_________ Synchrony Financial _____ ____unch _unchanged count
TEVA________ Teva Pharmaceut…td (ADR)  ____unch _unchanged count
TMK_________ Torchmark Corp___________ ____unch _unchanged count
TRV_________ Travelers Companies Inc__ __67.32% _______2,414,703
UAL_________ United Continen…dings Inc _-15.57% ______-4,045,900
UPS_________ United Parcel S…Inc (UPS) ____unch _unchanged count
USB_________ U.S. Bancorp_____________ ___6.73% _______9,226,239
USG_________ USG Corp_________________ ____unch _unchanged count
V___________ Visa Inc_________________ ____unch _unchanged count
VRSK________ Verisk Analytics Inc_____ _-75.84% ________-758,630
VRSN________ VeriSign Inc_____________ ____unch _unchanged count
VZ__________ Verizon Communi…tions Inc ____unch _unchanged count
WFC_________ Wells Fargo & Co_________ __-3.40% _____-15,806,898

I'm going to make the changes to the spreadsheet soon, but for now, these are the movements in the portfolio held for the benefit of Berkshire shareholders as I have them on my private version when I'm making the edits before putting them live.

The spreadsheets have now been updated (http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg361948/#msg361948) with these figures.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on February 15, 2019, 08:02:18 AM
I believe I've now updated both sheets to reflect the current known portfolio. The links in this old post will still work.

The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on March 05, 2019, 04:49:22 PM
I compared the top 15 holdings shown on page 12 of the Annual Report to my assumed holdings and found that there were three errors, while everything else matched, so I have modified my post showing the changes from 2018Q3's portfolio (http://www.cornerofberkshireandfairfax.ca/forum/berkshire-hathaway/look-through-portfolio-google-sheets-with-live-prices/msg361931/#msg361931). Not only did the shareholdings match, but when hard-coding the closing prices at 31st Dec 2018 the value of each holding rounded to exactly the same figure as shown on p12.

The errors were:
1. US Bancorp (USB) I completely missed out 4,840,500 shares with manager code 2,4,11. These are now included and the shareholding is correct. The only pension fund holdings are those with manager code 4 and I correctly exclude these.

2. Southwest Airlines (LUV) I had slightly fewer shares because I incorrectly attributed shares held by Medical Protective Company on p9 of 15 in the SC 13G/A filing to Pension Funds. These 8,722 shares are now correctly attributed to shareholders of Berkshire, while all remaining pension fund holdings remain deducted.

3. Charter Communications (CHTR) was showing far too few shares attributable to BRK shareholders. Without a SC 13G filing, I have simply put in the adjustment of -244,445 shares as the assumed Pension Fund holding, with a note beside it referring to p12 of the 2018 Annual Report, noting that it has changed since my assumptions based on the 2017 Annual Report.

A further item to note, having compared the portfolio value based on closing prices at 31 Dec 2018 to Berkshire's reported value of shareholdings attributable to Berkshire shareholders, is that my assumed portfolio value was approx $172,296 million excluding KHC (without perfect exchange rates for assumed foreign shares), while the annual report indicated $172,757 million. This implies that it's very likely that the Sanofi holding on Euronext remains in the portfolio, though by my calculation it is outside the top 15 positions. I made it the 16th largest position at year end, equating to about $1,755 million with a currency error I didn't adjust exactly, while BYD was 17th at $1,680 million (with currency error), GM was 18th at $1,515 million, V (Visa) was 19th at $1,394 million and VRSN was 20th at $1,172 million.

I also updated the sheet to include the correct Book Value attributable to Berkshire Shareholders and the year-end shares outstanding (exactly matching the reported Book Value per A share equivalent). I also entered the $3,652 million in Dividends received divided by the share count into cell $AH3.

This year EPS is of course crazy, only slightly exceeding the dividends thanks to GAAP forcing the inclusion of unrealized portfolio losses in EPS, thus EPS excluding dividends is tiny. Depending on your purpose, you may wish to use Operating Earnings instead to analyse the profitability of the operating part of Berkshire and ignore the effects of the portfolio.

The EPS for the investees is likely to be less distorted than for Berkshire, so the Look-Through Portfolio EPS of $5.42 per BRK.B share  equivalent (in cell AH70, after excluding KHC's negative contribution this year thanks to their recent writedowns, which should not be included as it is accounted under the Equity Method) is probably pretty reasonable.

Summary of quarterly changes (errors have been struck through, but are to be edited in the original post):
AAL_________ American Airlin…Group Inc ____unch _unchanged count
AAPL________ Apple Inc._______________ __-0.02% _________-51,697
AXP_________ American Express Co______ ____unch _unchanged count
AXTA________ Axalta Coating …stems Ltd ____unch _unchanged count
BAC_________ Bank of America…rporation ___2.10% ______18,919,000
BIT:CASS____ Società Cattoli…operativa ____unch _unchanged count
BK__________ Bank of New Yor…llon Corp ___3.79% _______3,087,774
CHTR________ Charter Communi…tions Inc __-5.45% ________-307,486
COST________ Costco Wholesale Corp____ ____unch _unchanged count
DAL_________ Delta Air Lines, Inc_____ ____unch _unchanged count
DEO_________ Diageo P L C Spon ADR New ____unch _unchanged count
DVA_________ DaVita HealthCa…tners Inc ____unch _unchanged count
GM__________ General Motors Co________ __77.78% ______19,808,285
GS__________ Goldman Sachs Group Inc__ ____unch _unchanged count
HCG_________ Home Capital Gr… (CANADA) ____unch _unchanged count
JNJ_________ Johnson & Johnson________ ____unch _unchanged count
JPM_________ JPMorgan Chase & Co______ __39.91% ______14,451,627
KHC_________ Kraft Heinz Co___________ ____unch _unchanged count
KO__________ Coca-Cola Co_____________ ____unch _unchanged count
LBTYA_______ Liberty Global …c Class A ____unch _unchanged count
LBTYK_______ Liberty Global …c Class C ____unch _unchanged count
LILAK_______ Liberty LiLAC Group C____ ____unch _unchanged count
LSXMA_______ Liberty Sirius … Series A ____unch _unchanged count
LSXMK_______ Liberty Sirius … Series C ____unch _unchanged count
LUV_________ Southwest Airls Co_______ __-2.44% ______-1,200,000
MA__________ MasterCard Inc___________ ____unch _unchanged count
MCO_________ Moody's Corporation______ ____unch _unchanged count
MDLZ________ Mondelez Intern…ional Inc ____unch _unchanged count
MTB_________ M&T Bank Corp____________ ____unch _unchanged count
NASDAQ:LILA_ Liberty LiLAC Group A____ ____unch _unchanged count
ORCL________ Oracle Corp______________ -100.00% _____-42,691,791
PG__________ Proctor and Gamble_______ ____unch _unchanged count
PNC_________ PNC Financial S…Group Inc __30.27% _______2,175,743
PSX_________ Phillips 66______________ _-22.92% ______-3,537,182
QSR_________ Restaurant Bran…ional Inc ____unch _unchanged count
RHT_________ Red Hat Inc______________ _**NEW** _______4,175,792
SHE:002594__ BYD Company Limited______ ____unch _unchanged count
SIRI________ Sirius XM Hldgs Inc______ ____unch _unchanged count
SNY_________ Sanofi (incl Eu…v shares) ____unch _unchanged count
STNE________ StoneCo Ltd._____________ _**NEW** ______14,166,748
STOR________ Store Capital Corp_______ ____unch _unchanged count
SU__________ Suncor Energy Inc New____ _**NEW** _________300,901
SYF_________ Synchrony Financial _____ ____unch _unchanged count
TEVA________ Teva Pharmaceut…td (ADR)  ____unch _unchanged count
TMK_________ Torchmark Corp___________ ____unch _unchanged count
TRV_________ Travelers Companies Inc__ __67.32% _______2,414,703
UAL_________ United Continen…dings Inc _-15.57% ______-4,045,900
UPS_________ United Parcel S…Inc (UPS) ____unch _unchanged count
USB_________ U.S. Bancorp_____________ ___3.20% _______4,385,739
USG_________ USG Corp_________________ ____unch _unchanged count
V___________ Visa Inc_________________ ____unch _unchanged count
VRSK________ Verisk Analytics Inc_____ _-75.84% ________-758,630
VRSN________ VeriSign Inc_____________ ____unch _unchanged count
VZ__________ Verizon Communi…tions Inc ____unch _unchanged count
WFC_________ Wells Fargo & Co_________ __-3.40% _____-15,806,898

Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on March 11, 2019, 01:34:53 AM
Berkshire Hathaway has filed Form 3 (https://www.sec.gov/Archives/edgar/data/27904/000120919119018300/xslF345X02/doc3.xml) and Form 4 (https://www.sec.gov/Archives/edgar/data/27904/000120919119018303/xslF345X03/doc4.xml) with the SEC in respect of Delta Air Lines, Inc (DAL) common stock, first disclosing that they had become a 10% owner then giving details of their recent purchases.

This 10%+ ownership changes their reporting requirements, I believe, and would, I believe require them to report buy and sell transactions within a fairly short period of such transactions taking place. I know they have in the past applied for special dispensation to delay publication of their reporting, but I suspect the SEC is not inclined to grant this while they build their position.

Between 6th and 8th March they purchased an additional 5,368,956 shares of DAL and spent about $265,234,000 on these shares, giving a weighted average price of $49.4014 per share for the new purchases.

Between 13-F filings, I've now added the 5,368,956 shares to my sheets as an adjustment on COMBINED HOLDINGS under "Assumed non-US holdings & adjustments"

At 31 Jan 2019, DAL had 678,950,098 shares outstanding, so BRK's new holding of 70,903,956 represents a 10.44% stake in the company.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on March 14, 2019, 01:58:12 AM
There is another Form 4 filing showing a little further buying of DAL on Monday 11th March 2019 at <$50 per share.

On the assumption this keeps happening I'll try to keep the sheets updated by changing the Adjustments column on COMBINED HOLDINGS worksheet, but I won't post here about it every time. At most I might Edit this post to note new filings.

If you want to see these Form 4 filings, this link (https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0001067983&type=&dateb=&owner=only&count=40) (also included as a Source on COMBINED HOLDINGS column J) will show only the last 40 Ownership filings relating to Berkshire Hathaway (https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0001067983&type=&dateb=&owner=only&count=40). The Form 4 filings include the recent DAL purchases among other disclosures announced previously.

Fortunately, there are no Berkshire Pension Fund holdings of DAL (see form SC13 G/A (https://www.sec.gov/Archives/edgar/data/1067983/000119312519040921/0001193125-19-040921-index.htm)) so the only adjustment needed in column J of my COMBINED HOLDINGS worksheet is the total of purchases since the last 13-F date of record.

     DAL   Delta Air Lines          Calculated                   
Date(US)   ShrsAdded Avg Cost      Cost of lot     Holding % held

12/31/18                                        65,535,000  9.65%
03/06/19   1,202,961 $49.6514   $59,728,697.80  66,737,961  9.83%
03/07/19   1,817,318 $49.2722   $89,543,255.96  68,555,279 10.10%
03/07/19       3,600 $49.7716      $179,177.76  68,558,879 10.10%
03/08/19   2,345,077 $49.3728  $115,783,017.71  70,903,956 10.44%
03/11/19       6,500 $49.9382      $324,598.30  70,910,456 10.44%

  TOTALS   5,375,456 $49.4021  $265,558,747.52  70,910,456 10.44%
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on March 14, 2019, 02:37:33 PM
Dynamic,

Thank you for sharing your hard & cumbersome work with all the CoBF members, to the benefit of us all.

- - - o 0 o - - -

For SU [Suncore Energy] you have the number of shares held by Berkshire at 300,901, while the Berkshire 13-F/HR EOP 2018Q4 signals number of shares at 10,758,000. [You have typed in your file the value of the position instead of the number of shares.]

NEAM SU position EOP 2018Q4 I read to zero shares in the NEAM 13-F/HR.

- - - o 0 o - - -

It does not matter much for the whole picture, however I've chosen to report it to you here, now that I've got aware of it.

- - - o 0 o - - -

Again, thank you very much for sharing your work, - for my part, it's really, really  appreciated.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on March 15, 2019, 02:43:12 AM
Hi John, thank you so much for checking my work and finding an error I'd missed! That's the beauty of sharing these things.

I've now made the correction to SU and I'll edit the posts above about the changes in holdings accordingly.

I may decide to implement a method of pasting the 13-Fs and parsing them to extract the info without error. I just did it for the DAL Form 4's, so I think it shouldn't be too hard to do and should reduce the likelihood of human error on my part.

With that correction, my estimate of the total market value of holdings (excluding KHC) at 31 Dec 2018 is $173,342 million.
This is about $585 million more than the $172,757 reported on page 12 of the Chairman's Letter in the 2018 Annual Report (and all 15 stocks there rounded to exactly my numbers after I made a couple of corrections to mine).

I think it's possible that I have some more errors or that I'm unaware of or there are some pension fund holdings for companies where a SC 13/G or similar form has not been filed. It's also possible that one of the non US holdings not reported to the SEC is not at the last know size - for example Sanofi's ADRs were sold a while ago, but the last known equivalent amount of ADR is still on my sheet to reflect the majority of those that were purchased on Euronext Paris, and the year-end market value would have put it in 16th place, just outside the top 15 shown. The other potential source of error is again for foreign holdings where I haven't gone back and taken year-end currency exchange rates.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on April 26, 2019, 11:21:44 AM
The purchase of USG Corp by Knauf completed on Wednesday at $43.50 per share (https://www.businesswire.com/news/home/20190424005587/en/Knauf-Completes-Acquisition-USG-Corporation) (in addition to the special $0.50 dividend in Q4 2018) so I've now modified the sheets hard coded to $43.50 to reflect the cash proceeds before tax and I've zeroed the EPS figure. I'll eliminate the position when the 13-F comes out on 15th May.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on April 26, 2019, 12:09:54 PM
Thank you for the heads-up, Dynamic,

I really appreciate your tool.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 07, 2019, 01:29:59 AM
Note 4 in the 2019Q1 10-Q results indicates that about 16.8 million shares of WFC were sold in the quarter up to 31st March 2019, as usual to ensure that ownership remains below 10%. I've modified the WFC holding accordingly so that the value of the holding at the 31st March closing price matches the reported stake. That is reported to the nearest 0.1 billion USD so I can only estimate to 3 significant figures right now, but better to roughly right than precisely wrong.

When the 13-F comes out on 15th May we'll have the accurate shareholding and I'll remove that adjustment. The other major holdings shown in Note 4 of the 10-Q (AXP, AAPL, BAC, KO etc) appear to be unchanged as the quarter-close valuations match my figures when rounded to the nearest $0.1 bn.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 16, 2019, 07:38:52 AM
The new 13-F (https://www.sec.gov/Archives/edgar/data/1067983/000095012319005436/0000950123-19-005436-index.htm) showing the portfolio position at 31st March has been published.

As always the primary_doc (https://www.sec.gov/Archives/edgar/data/1067983/000095012319005436/xslForm13F_X01/primary_doc.xml) mentions New England Asset Management (NEAM) under heading List of Other Managers Reporting for this Manager and as usual the 13-F for NEAM (https://www.sec.gov/Archives/edgar/data/1004244/000108514619001666/0001085146-19-001666-index.htm) has a primary_doc (https://www.sec.gov/Archives/edgar/data/1004244/000108514619001666/xslForm13F_X01/primary_doc.xml) which includes:

List of Other Included Managers:
No.   Form 13F File Number   Name
1   028-04545   BERKSHIRE HATHAWAY INC
2   028-04922   GENERAL RE CORP

But this time, instead of "01 02" in the OTHER MANAGER column of the 13-F, they use "1 2", which is simply a change of formatting.

The Berkshire holdings in NEAM's 13-F are not widely reported on.

Only one change to their Berkshire holdings - the Berkshire position in VRSK Verisk Analytics is no longer present.

Next quarter, we will see that USG is absent after the takeover by Knauf was completed. For now I'm leaving USG in there and have hard-coded the takeover price of $43.50 as the cash proceeds after tax will be invested by Berkshire or will add to their cash balance.

Turning to Berkshire's own 13-F:

AMZN Amazon is new 483,300 shares - currently worth around $0.9bn having risen about 5.1% since the end of Q1. I'd guess it's Ted Weschler's position, but it could also be Todd Combs, and each manages around $13bn. Warren Buffett says it's not his own. I have assumed it's not invested for the benefit of pension funds until we find out otherwise.

CHTR Charter Comms has eliminated the position held for pension funds (was 244,445 shares) and also reduced the position for the benefit of Berkshire shareholders by about a million shares.

DAL Delta Air Lines - as we knew when they accidentally went over 10%, they now have about a 10.4% stake. The exact amount was confirmed by this 13-F so I have zeroed-out the adjustment column I used in the mean time.

JPM JPMORGAN CHASE & CO - a fairly substantial increase in stake.

PNC PNC FINL SVCS GROUP INC - another increase of about 5%

PSX PHILLIPS 66 - dropped by more than half. Possibly we'll find out it has all gone by 30th June.

RHT Red Hat Inc increased about 20%. My guess is it's Ted, perhaps Todd. I imagine it's a merger arbitrage play and now trades close to the expected closing price to be paid by IBM.

LUV Southwest Airls Co reduced slightly, probably to stay below 10%

WFC Wells Fargo reduced as usual to stay below 10%.

VRZN Verizon - this tiny 928 share position has now vanished.

Here's the summary table:
AAL_________ American Airlin…Group Inc ____unch _unchanged count
AAPL________ Apple Inc._______________ ____unch _unchanged count
AMZN________ Amazon Com Inc___________ _**NEW** _________483,300
AXP_________ American Express Co______ ____unch _unchanged count
AXTA________ Axalta Coating …stems Ltd ____unch _unchanged count
BAC_________ Bank of America…rporation ____unch _unchanged count
BIT:CASS____ Società Cattoli…operativa ____unch _unchanged count
BK__________ Bank of New Yor…llon Corp ____unch _unchanged count
CHTR________ Charter Communi…tions Inc _-18.81% ______-1,322,788
COST________ Costco Wholesale Corp____ ____unch _unchanged count
DAL_________ Delta Air Lines, Inc_____ ___8.20% _______5,375,456
DEO_________ Diageo P L C Spon ADR New ____unch _unchanged count
DVA_________ DaVita HealthCa…tners Inc ____unch _unchanged count
GHC_________ Graham Holdings Co_______ ________ ________________
GM__________ General Motors Co________ ____unch _unchanged count
GS__________ Goldman Sachs Group Inc__ ____unch _unchanged count
HCG_________ Home Capital Gr… (CANADA) ____unch _unchanged count
IBM_________ International B…ines Corp ________ ________________
JNJ_________ Johnson & Johnson________ ____unch _unchanged count
JPM_________ JPMorgan Chase & Co______ __18.55% _______9,398,538
KHC_________ Kraft Heinz Co___________ ____unch _unchanged count
KO__________ Coca-Cola Co_____________ ____unch _unchanged count
LBTYA_______ Liberty Global …c Class A ____unch _unchanged count
LBTYK_______ Liberty Global …c Class C ____unch _unchanged count
LILAK_______ Liberty LiLAC Group C____ ____unch _unchanged count
LSXMA_______ Liberty Sirius … Series A ____unch _unchanged count
LSXMK_______ Liberty Sirius … Series C ____unch _unchanged count
LUV_________ Southwest Airls Co_______ __-2.50% ______-1,198,186
MA__________ MasterCard Inc___________ ____unch _unchanged count
MCO_________ Moody's Corporation______ ____unch _unchanged count
MDLZ________ Mondelez Intern…ional Inc ____unch _unchanged count
MON_________ Monsanto Co New__________ ________ ________________
MTB_________ M&T Bank Corp____________ ____unch _unchanged count
NASDAQ:LILA_ Liberty LiLAC Group A____ ____unch _unchanged count
ORCL________ Oracle Corp______________ ________ ________________
PG__________ Proctor and Gamble_______ ____unch _unchanged count
PNC_________ PNC Financial S…Group Inc ___4.36% _________407,992
PSX_________ Phillips 66______________ _-53.32% ______-6,343,127
QSR_________ Restaurant Bran…ional Inc ____unch _unchanged count
RHT_________ Red Hat Inc______________ __22.38% _________934,679
SHE:002594__ BYD Company Limited______ ____unch _unchanged count
SIRI________ Sirius XM Hldgs Inc______ ____unch _unchanged count
SNY_________ Sanofi (incl Eu…v shares) ____unch _unchanged count
STNE________ StoneCo Ltd._____________ ____unch _unchanged count
STOR________ Store Capital Corp_______ ____unch _unchanged count
SU__________ Suncor Energy Inc New____ ____unch _unchanged count
SYF_________ Synchrony Financial _____ ____unch _unchanged count
TEVA________ Teva Pharmaceut…td (ADR)  ____unch _unchanged count
TMK_________ Torchmark Corp___________ ____unch _unchanged count
TRV_________ Travelers Companies Inc__ ____unch _unchanged count
UAL_________ United Continen…dings Inc ____unch _unchanged count
UPS_________ United Parcel S…Inc (UPS) ____unch _unchanged count
USB_________ U.S. Bancorp_____________ ____unch _unchanged count (correction)
USG_________ USG Corp_________________ ____unch _unchanged count
V___________ Visa Inc_________________ ____unch _unchanged count
VRSK________ Verisk Analytics Inc_____ -100.00% ________-241,695
VRSN________ VeriSign Inc_____________ ____unch _unchanged count
VZ__________ Verizon Communi…tions Inc -100.00% ____________-928
WFC_________ Wells Fargo & Co_________ __-3.78% _____-16,965,129
WMT_________ Wal-Mart Stores Inc______ ________ ________________


I believe I've now updated both sheets to reflect the current known portfolio. The links in this old post will still work.

The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

As always, the holdings entered on the sheets are one-millionth of the total shares in issue as at 25th April, so the valuations and holdings of the company as a whole are expressed in $millions and millions of shares.

If you copy the sheet for private use using File/Make A Copy... you can enter your own holdings of Class A and Class B shares and see the look-through exposure of your own Berkshire position. You can also convert it to your preferred currency.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 18, 2019, 04:20:31 AM
Thanks to a comment below a Seeking Alpha article about Berkshire's portfolio (https://seekingalpha.com/article/4264843-tracking-warren-buffetts-berkshire-hathaway-portfolio-q1-2019-update) by Bower-Ranger (https://seekingalpha.com/user/38779606/comments) I have now added Lanxess AG a German Chemical producer held via General Re AG at about a 5% stake since December 2017. As a non-US holding it won't ever be on the 13-F filings, but it's valued just over $250 million USD at recent prices, so it's worth including.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 18, 2019, 05:24:01 AM
It turns out that I had missed a line of USB (US Bancorp) last quarter, so in fact the position is unchanged this time
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on May 29, 2019, 08:46:44 AM
I've discovered a few errors over the last 5 sets of 13F-HR filings which, for completeness, I'll list below. I think I should be able to automatically read in the 13F data into a spreadsheet in future and reduce or avoid the human error involved in doing so manually, and in testing that these human errors were found. I'll then copy and paste it into these Google Sheets. It should save me from reading the wrong column or mis-typing numbers most of the time!

4,840,500 missing USB shares in 14th Feb 2019 and 14th Nov 2018 Berkshire filings (didn't include that new row in the filing). This increase did not happen in Q1 2019, so I'll modify my post of 16th May 2019.
      600 missing JNJ shares in 14th Aug 2018 New England Asset Management filing (mis-typed figure)
   44,110 missing USB shares in 14th Aug 2018 New England Asset Management filing (figure from wrong column)
  107,575 extra GHC shares in 14th Aug 2018 and 15th May 2018 Berkshire filings, which had already been sold (didn't adequately check for absent items)
  300,000 missing LBTYA shares in 15th May 2018 filing (mis-typed figure)

I believe I made some other errors that were picked up by other forum members and corrected before now.

As always, these Google Sheets are provided on an as-is, best-effort basis and should not be relied upon to make important decisions, and the publicly editable version is potentially open to corruption by any person on the internet.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on June 07, 2019, 06:59:59 AM
I was thinking this morning about how Charlie didn't hesitate when asked what large company Warren would be thrilled to purchase (or something to that effect).  He spit out Coke so quickly that it seemed well established in his mind.  I checked in on KO this morning and was surprised to see it breaking out to all time highs at $52 / share. 

edit: I see that American Express also hit an all-time high share price intraday today. 
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on June 10, 2019, 06:05:24 AM
I haven't looked at KO for a while. With a P/E in the 30s and over 3% yield, it pays out almost all of its earnings but is priced as a virtually indestructible consumer brand with pricing power to keep pace with general price inflation and possibly a bit higher, such as wage inflation. My guess is that it's seen by Mr Market as a defensive stock in case of economic declines, having worldwide earnings, little need for additional capital and products priced low enough that most people wouldn't switch to the cheaper alternatives that don't quite have that taste or those mental associations. Perhaps it has been seen as a 'safe' place for investors nervous of tariff wars or potential recessions. Some of the bottlers have occasionally been available at potentially attractive prices over the years.

Short of seeing some qualitative growth drivers, though, that are hard to envisage now it already has worldwide distribution, it seems that is pretty much fully priced to me.

I'm fairly sure Berkshire wouldn't be interested at these prices unless there's some hidden value I haven't noticed, but it looks like a perfect fit for Berkshire at the right price at some point - lots of cash being thrown off which could be rationally allocated to other things and would provide consistent cash inflows to rapidly replenish any large insurance losses.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on June 18, 2019, 09:40:37 AM
In doing a little detective work in a thread about Li Lu's Himalaya Capital Management portfolio, finding out why they no longer need to file 13F (http://www.cornerofberkshireandfairfax.ca/forum/general-discussion/li-lu-himalaya-capital-management-llc/msg373477/#msg373477) I came across the SEC's 13F FAQ (https://www.sec.gov/divisions/investment/13ffaq.htm) which clarifies a lot of the rules and provides links to a list of all the securities that are covered by rule 13F each quarter (including archives, such as 2018Q2 when Monsanto, spelled correctly in the SEC's list, was deleted having been taken over).

For those interested in the minutiae, there are a few nuggets extracted from the FAQ quoted on that post that may be of interest, including the clarification of the 13F filing dates, which are 45 days after the quarter ends but if that falls on a non-business day the deadline is delayed until the next business day (e.g. not a Saturday, Sunday, Presidents' Day Public Holiday etc), so while this happens to match the expected dates of 14th Feb, 15th May, 14th Aug, 14th Nov in 2019, the next filing that will be delayed is 2020Q3, where 14th Nov is a Saturday so the filing will be on Mon 16th Nov (unless that's a Public Holiday for some reason) and we won't get back to the normal 45 days after quarter-end until 14th Feb 2022 as they'll fall on weekends until then!

There are also descriptions of the confidential treatment provisions towards the end of the FAQ. Apparently when the confidentiality period ends, an amendment would be filed.

For some filers, but probably not Berkshire, they mention rules about not having to disclose or net-out short positions (including short options positions such as put-writing or call-writing) but the need to disclose all long positions. Interestingly, about 186 securities containing the word INDEX are on the list of reportable 13F securities, some of which seem to be ETFs and some CALL and PUT options too.

In other news, alongside automatically reading in the 13F filing data for Berkshire and the relevant entries from New England Asset Management so that I avoid so much human error in future, I believe I'm homing in on a good approach to simplifying the appearance and sort-ability of my Look-Through Portfolio. I'm trying that out in my private version, so I may well update it by about the time the next 13F drops on 14th August.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on June 18, 2019, 12:32:02 PM
Your analysis of what's been going on with the Himalaya Capital Management portfolio is just straight out awesome, Dynamic.

I also hold your work in this topic with the Berkshire portfolio very high, now during almost three years. To me, it's a gem - nothing less.

Thank you for sharing.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: shalab on June 18, 2019, 08:23:25 PM
+1, excellent work!

Your analysis of what's been going on with the Himalaya Capital Management portfolio is just straight out awesome, Dynamic.

I also hold your work in this topic with the Berkshire portfolio very high, now during almost three years. To me, it's a gem - nothing less.

Thank you for sharing.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on June 19, 2019, 12:32:45 AM
Thanks for the kind words. It has continued to be useful to me too, and I greatly appreciate all the help from everyone on this thread who have helped point me to new information or to spot errors, thus helping me to improve its usefulness to me and to everyone else. Nice to be part of a virtuous circle!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on July 27, 2019, 08:17:03 AM
Hey Dynamic - do you have the American Airlines position size correct in the spreadsheet?  Thanks and good work as always
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on July 27, 2019, 02:55:19 PM
It's late here but I'll try to check tomorrow.

I think the 13F total agrees with my automated parsing to avoid human error, and that's 43.7 million shares.

In the adjustments column I subtract all the pension scheme assets and I've left the calculation which I entered manually from reading the 13G linked in the next column, so I'll try to check the figures tomorrow.

That subtracts 20.742 million shares almost halving the total on the 13F to leave only those for the benefit of Berkshire shareholders.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on July 27, 2019, 03:58:37 PM
That makes sense - it was probably the original airline investment for Berkshire by Ted Weschler - he was the Doug Parker fan who got Berkshire into airlines first.  And I believe a good bit of what Ted and Todd manage is in subsidiary pension plans.

It's late here but I'll try to check tomorrow.

I think the 13F total agrees with my automated parsing to avoid human error, and that's 43.7 million shares.

In the adjustments column I subtract all the pension scheme assets and I've left the calculation which I entered manually from reading the 13G linked in the next column, so I'll try to check the figures tomorrow.

That subtracts 20.742 million shares almost halving the total on the 13F to leave only those for the benefit of Berkshire shareholders.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on July 29, 2019, 03:38:52 PM
Incidentally, gfp, I have checked the values held in the pension plans against the 13ga filing (https://www.sec.gov/Archives/edgar/data/6201/000119312519040974/d667619dsc13ga.htm) and indeed they check out, totalling 20,742,000 shares owned by pension funds for employees and not owned for the benefit of Berkshire shareholders.

That's 47.46% of the total AAL shares reported in form 13F-HR that must be deducted.

Incidentally, I have made an Adjustment to the BAC total to reflect the Form 3 filing (https://www.sec.gov/Archives/edgar/data/70858/000120919119043250/xslF345X02/doc3.xml) showing an increase of 31,080,000 share of BAC to reach a grand total of 950,000,000 shares (all of which are attributable to shareholders not pension funds).

All the major 'reliable' press I have seen has been getting this wrong by reporting that Berkshire added over 50 million shares since 31st March counting only the 896,167,600 on Berkshire's main 13F-HR and ignoring the 22,751,400 held via New England Asset Management (they don't read the cover page of the 13F-HR submission). The 10-K reports the previous total of 918,919,000 shares and the last 10-Q reports the correct closing market value using BAC's quarter end closing price, and the 13F filings published 14th Feb 2019 and 15th May 2019, combined, also gave 918,919,000 in total.

We don't know yet whether these purchases were made before 30th June or between 1st July and 17th July, though I suspect the former, meaning that all 950,000,000 will be split across the two 13F-HR filings (Berkshire and NEAM).

As BAC closed the quarter on $29.00 per share, this would be confirmed by a reported Q2 holding valued at $27.6bn ($27.550 billion rounded to nearest $0.1bn). Even one share less and the rounding would make it $27.5bn ($27,549,999,971 rounded down), surprisingly enough. Just days until we see the 10-Q now!

edit on 5th August: We now know that the increase to 950,000,000 shares did take place by 30th June as $27.6bn market value was reported in the Notes to the 10-Q.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 19, 2019, 03:32:58 PM
AAL_________ American Airlin…Group Inc ____unch _unchanged count
AAPL________ Apple Inc._______________ ____unch _unchanged count
AMZN________ Amazon Com Inc___________ __11.20% _________537,300
AXP_________ American Express Co______ ____unch _unchanged count
AXTA________ Axalta Coating …stems Ltd ____unch _unchanged count
BAC_________ Bank of America…rporation ___3.40% _____950,000,000
BK__________ Bank of New Yor…llon Corp ____unch _unchanged count
CASS________ Società Cattoli…operativa ____unch _unchanged count
CHTR________ Charter Communi…tions Inc __-5.00% _______5,426,609
COST________ Costco Wholesale Corp____ ____unch _unchanged count
DAL_________ Delta Air Lines, Inc_____ ____unch _unchanged count
DEO_________ Diageo P L C Spon ADR New ____unch _unchanged count
DVA_________ DaVita HealthCa…tners Inc ____unch _unchanged count
GL__________ Globe Life Inc …ark Corp) ____unch _unchanged count
GM__________ General Motors Co________ ____unch _unchanged count
GS__________ Goldman Sachs Group Inc__ ____unch _unchanged count
HCG_________ Home Capital Gr… (CANADA) ____unch _unchanged count
HKG:1211____ BYD Company Lim… Listing) ____unch _unchanged count
JNJ_________ Johnson & Johnson________ ____unch _unchanged count
JPM_________ JPMorgan Chase & Co______ ____unch _unchanged count
KHC_________ Kraft Heinz Co___________ ____unch _unchanged count
KO__________ Coca-Cola Co_____________ ____unch _unchanged count
LBTYA_______ Liberty Global …c Class A ____unch _unchanged count
LBTYK_______ Liberty Global …c Class C ____unch _unchanged count
LILA________ Liberty LiLAC Group A____ ____unch _unchanged count
LILAK_______ Liberty LiLAC Group C____ ____unch _unchanged count
LSXMA_______ Liberty Sirius … Series A ____unch _unchanged count
LSXMK_______ Liberty Sirius … Series C ____unch _unchanged count
LUV_________ Southwest Airls Co_______ ____unch _unchanged count
LXS_________ Lanxess AG_______________ ____unch _unchanged count
MA__________ MasterCard Inc___________ ____unch _unchanged count
MCO_________ Moody's Corporation______ ____unch _unchanged count
MDLZ________ Mondelez Intern…ional Inc ____unch _unchanged count
MTB_________ M&T Bank Corp____________ ____unch _unchanged count
PG__________ Proctor and Gamble_______ ____unch _unchanged count
PNC_________ PNC Financial S…Group Inc ____unch _unchanged count
PSX_________ Phillips 66______________ ____unch _unchanged count
QSR_________ Restaurant Bran…ional Inc ____unch _unchanged count
RHT_________ Red Hat Inc______________ ___1.20% _______5,171,890
SAN_________ Sanofi Euronext Paris____ ____unch _unchanged count
SIRI________ Sirius XM Hldgs Inc______ ____unch _unchanged count
STNE________ StoneCo Ltd._____________ ____unch _unchanged count
STOR________ Store Capital Corp_______ ____unch _unchanged count
SU__________ Suncor Energy Inc New____ ____unch _unchanged count
SYF_________ Synchrony Financial______ ____unch _unchanged count
TEVA________ Teva Pharmaceut…Ltd (ADR) ____unch _unchanged count
TRV_________ Travelers Companies Inc__ ____unch _unchanged count
UAL_________ United Continen…dings Inc ____unch _unchanged count
UPS_________ United Parcel S…Inc (UPS) ____unch _unchanged count
USB_________ U.S. Bancorp_____________ ___2.20% _____149,497,786
USG_________ USG Corp_________________ -100.00% ________________
V___________ Visa Inc_________________ ____unch _unchanged count
VRSN________ VeriSign Inc_____________ ____unch _unchanged count
WFC_________ Wells Fargo & Co New_____ ____unch _unchanged count
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on August 19, 2019, 05:10:41 PM
Thanks Dynamic.  Good job with the formatting!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on August 20, 2019, 01:53:20 AM
Thanks gfp. For now, it's fixed-width text using Tt and it works OK, though it ran over to two rows on mobile view at least on my phone. I have an idea to encode the formatting as a table using forum code that I can copy and paste from the spreadsheet to the forum posts.

A few notes on the latest update. I've been very busy with work, so I'm a few days late in posting here.

I had already changed the non-publicly-editable version on 15th Aug but have now got both done. A bit of a format change is planned so that I'll have an alphabetical list then a sortable table based on that with a simpler, clearer layout, but it's not ready yet.

I might also make some kind of graphical representation - any suggestions welcome. I'm thinking perhaps a pie chart, area chart or bar graph showing different holdings. Could potentially also group together certain holdings that share characteristics - (e.g. airlines, banks, insurance & other financial, etc...) or in another plot the categories might be (near permanent holdings, merger arbitrage, other mergers agreed, etc.). The question then is also, do we include KHC or exclude it as it's carried under the Equity Method, as a Control Position. In some way it would be go to show it alongside the 100% at an equivalent scale. I could also show the look-through earnings from various holdings. A bar chart would be good here, as negative earnings are usually going to feature.

Torchmark TMK has changed ticker to GL and changed name to Globe Life Inc since the end of Q2, so I've already reflected that name change even though the 13F-HR has to report it by its official name at the time.

USG was taken over by Knauf during the quarter, so is no longer in the portfolio. As the cash is shown elsewhere on the balance sheet, the proceeds are no longer shown in my portfolio.

RHT was presumably a merger arbitrage (purchased by IBM) and the merger closed, I believe at $190 per share, after the end of the quarter. So I've put in $190 as the market price now so that the cash proceeds (pre-tax) are reflected in the portfolio value, although it closed the quarter a little lower. I guess its EPS should really be stripped out of the look-through EPS, which I haven't done yet.

It's worth remembering that Anadarko voted to be taken over by OXY, so Berkshire's $10bn 8% preferred stock investment in OXY was probably the most significant capital allocation of the quarter, although the capital hadn't actually left the building until about 8th August.

AMZN increased +11.20% to 537,300 shares worth around $1 billion at market value, so still seems to be a Ted or Todd position.

BAC increased +3.40% to 950,000,000 shares market value $27.55 bn on 30th June. Since then they found that with the share count reduction they held more than 10% and there have been no filings since indicating whether the holding had to be reduced or was allowed to remain while new bank holding company rules are being considered by the authorities.

CHTR decreased -5.00% to 5,426,609 shares (approx $2.1bn) continuing the gradual disposal.

RHT increased +1.20% to 5,171,890 shares (approx $1.0 bn) which was taken over by IBM since 30th June.

USB increased +2.20% to 149,497,786 shares (approx $7.8 bn to $7.9 bn)

The current and old versions of 13F-HR worksheets (right hand group of tabs on the sheet) on the non-publicly-editable version (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY) of the sheet now combine Berkshire's own 13F (figures in black) with any of the rightmost figures in bold and blue being those holdings that belong to Berkshire but are reported within the New England Asset Management (NEAM) 13F-HR (Other Manager 01 02 denotes these holdings). This will allow you to check my work, but I'm pretty sure it's accurate. These 13F tabs are now labelled not by the date the 13F was published but by the quarter-end date that the snapshot represents, as in 13F 2019-06-30 meaning the snapshot at 30th June 2019.

The links to Google Finance page and the code to grab prices now explicitly includes the exchange, such as NYSE:USG. For cases such as USG, or RHT where a takeover has taken place, this means you'll usually get the correct security not some random obscure security that uses a similar ticker on a difference exchange. I've also put in a Takeover Price override, so for example, Red Hat and USG have $190 and $43.50 entered there, respectively. This is part of some general improvements I'm including.

The COMBINED HOLDINGS Q2 tab is changed slightly, in that it no longer splits out the Berkshire and NEAM holdings plus or minus the adjustments but reports the combined total only in column D. If you want to see the holdings on Berkshire's 13F and NEAM's 13F, you can go to the 13F tabs over to the right. I still show the Adjustments that have been applied and the URLs to the SEC filings or news articles that justify each. I'm putting these on the Ticker List tab in their original form as part of my improvement to the sheet, and a lot of the negative adjustments show their working if you look at the formula. I even sometimes show values I have removed as new filings come out by putting 0*234567 within the formula to zero-out former pension fund holdings that are no longer reported. The idea is to make it easier to check my working.

These Adjustments are typically one of two kinds:

1. Negative Adjustments to account for holdings that belong to Pension Funds of Berkshire or its Subsidiaries, that are not beneficially owned by Berkshire Shareholders. Berkshire has to report the positions it or its managers have control to buy or sell or voting rights upon in form 13F-HR, not only positions owned for the benefit of shareholders. I usually link to the most SEC filing (often on form 13D or 13G) from which I've derived this information by adding up all the positions held by the various pensions funds. In one of two cases where perhaps there is no 13D or 13G filing, Berkshire has indicated in its 10-K exactly how many shares are beneficially owned by Berkshire Shareholders, and the difference between that and the 13F to 31st December that year is then used as an Adjustment. The details are mostly on the Ticker List, which I'll be using more in future as a helper sheet.
AAL and GM are examples where the pension fund holdings are a truly significant proportion of the total holding.

2. Positive Adjustments are usually there to account for foreign positions or any other positions that are not subject to rule 13F that are known or strongly suspected to be held by Berkshire. Here I usually link to the relevant source of this information, but I might as well re-cap here:

a. Sanofi. Back in 2016's 10-K this was one of the top 15 positions listed, with a note explaining that the majority of the holding was on Euronext Paris, and a minority was held via ADRs. Only the ADRs are 13F securities. The ADRs were eventually sold, but the Euronext holdings would have fallen just outside the top-15 holdings by year-end market value since then and appear to be necessary to match the portfolio value in Holdings in equity securities reported in the annual and quarterly filings (which excludes KHC and pension fund holdings).

b. BYD Company Ltd. 225 million shares held by Berkshire Hathaway Energy. I have switched this to the Hong Kong listing (not the Shenzhen listing) as I've seen tables showing Berkshire's holding being on that exchange, and I understand that Shenzhen listed shares cannot be held by non-Chinese entities. The prices, converted to USD, don't seem to be especially close very often, but it seems that the HKG:1211 listing more closely matches Berkshire's reported valuation. This holding is still reported in Berkshire Hathaway Energy's filing (https://www.sec.gov/Archives/edgar/data/71180/000108131619000014/bhe63019form10-q.htm) at $1,358 million which matches my quarter end valuation when converted from HKD to USD currency to the nearest million. I also see a small holding in Rabbi trusts is mentioned there, which I am ignoring. There is also some "Other" which I'm also ignoring.

c. Società Cattolica di Assicurazione - Società Cooperativa - this was an estimated holding based on news reports at the time. The exact share count is unknown, so it's probably correct to about 3 significant figures at best, assuming it's still held.

d. Home Capital Group (CANADA) - Not a 13F holding, and reports indicated that Berkshire had planned to 'substantially exit' its position, so I've assumed the position is now zero.

e. Lanxess AG (Germany) - I had seen a record of a stake disclosure from a few years ago and briefly included it, but my quarter end valuation was within 0.01% of Berkshire's 10-Q valuation if I excluded it, so I have excluded it and removed it from the Look-Through portfolio.

Checking my total market value against Berkshire's 10-Q
At Quarter-end prices with currency conversion to USD at quarter-end rates, my portfolio market value (excluding KHC, as is correct) came to $200,490,678,602, which is -$25,321,398 less than the 10-Q's Investments in Equity Securities at $200,516,000,000 (a -0.01% shortfall), so I'm pretty happy I'm roughly right with these foreign company adjustments and the pension fund adjustments.

It could be luck that my omissions and errors in part cancel others on this occasion, but the difference is probably immaterial given that we have most things accurate and many stocks will move up and down with the general market by similar proportions.

It's probable that the look-through earnings (always trailing twelve months data as provided by Google) and the look-through holdings are pretty close to correct. Some stocks such as certain Liberty tracker stocks don't return EPS figures, but I think their impact is fairly immaterial. And as always, reported EPS can be somewhat misleading. Semper Augustus was saying that about 15% of reported EPS over time tends to be wiped out by write-offs etc. and perhaps pension shortfalls too according to their analysis.

Edit: Just corrected an error with RHT and sorted by holding size. I now report the takeover price and date if there is one and zero the EPS if there has been a takeover. So RHT shows as $190 per share = 982.66 million USD with zero earnings (rather than negative earnings contribution).
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on October 15, 2019, 06:46:33 AM
Looks like Berkshire went over 10% on Wells as of this morning's updated WFC share count.  I'm seeing period end (9/30/19) Wells share count at 4.2691 Billion shares and Berkshire with 432.383 million shares.  So a few days after Wells files their 10Q we will see if Berkshire was granted or is in the process of being granted permission to let the stake drift above 10% due to repurchase activity.  Seemed to be allowed for BAC so I would expect the same for Wells.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 15, 2019, 08:41:57 AM
Thanks for posting this, gfp. I hadn't got round to checking.
The filing deadline for Form 3 or similar would be 3 days, wouldn't it?
If so we should know around Friday evening after the closing bell, I imagine, and we'll find out if they happened to add to their WFC position this quarter (which is unlikely but possible). I suspect various news outlets will report it incorrectly as an add, because they never include the 22,580,200 shares held for Berkshire via New England Asset Management's 13F-HR.

Assuming it has to remain passive once it exceeds 10% through buybacks, we can probably assume that they won't add to the position once they know it has exceeded 10% (as they did with Delta Air Lines DAL during the three days before filing, causing 2 filings within days).
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on October 15, 2019, 02:58:38 PM
sorry to post this in your thread, but it relates to what we were just talking about -
https://www.americanbanker.com/articles/warren-buffett-seeks-fed-leeway-to-boost-stake-in-bank-of-america-past-10

Also - an update for your BAC position size now that Applied Underwriters is sold
https://www.sec.gov/Archives/edgar/data/70858/000120919119053219/xslF345X03/doc4.xml
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 16, 2019, 07:48:54 AM
It's everyone's thread, gfp, and probably the best place to put news like this - I just happen to own and edit the spreadsheet.

You've made enormously valuable input to it and to my understanding of the filing rules and things like New England Asset Management's 13F, and encouraged me to look deeper into these arcane rules.

I've now modified the BAC look-through holding based on this Form 4 by way of the adjustment column (as the change won't be shown on a form 13F-HR until 14 Feb 2020, reflecting the portfolio at 31 Dec 2019).

Now the new BAC share count of 9,079.3 million shares at Sep 30th is known, Berkshire, with 947,760,000 shares, owns a fraction under 10.44% of BAC.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 23, 2019, 03:26:11 AM
Looks like Berkshire went over 10% on Wells as of this morning's updated WFC share count.  I'm seeing period end (9/30/19) Wells share count at 4.2691 Billion shares and Berkshire with 432.383 million shares.  So a few days after Wells files their 10Q we will see if Berkshire was granted or is in the process of being granted permission to let the stake drift above 10% due to repurchase activity.  Seemed to be allowed for BAC so I would expect the same for Wells.

It seems that I was wrong about the filing deadlines, which appear to be 10 days from the event, so with WFC's new share count becoming known on 15th October, then Berkshire, by the close of Friday 25th October would presumably need to file Form 3 (or possibly 13D or 13G) if the ownership has crept over 10% of WFC.

Form 3, 4, 5 (https://www.sec.gov/fast-answers/answersform345htm.html): If the issuer is already registered under Section 12, the insider must file a Form 3 within ten days of becoming an officer, director, or beneficial owner. (beneficial owner meaning 10%+)
Changes in ownership are reported on Form 4 and must be reported to the SEC within two business days. You can find the limited categories of transactions not subject to the two-day reporting requirement in the new rule. (I think this is only after you've filed the initial Form 3 and any subsequent changes in ownership occur - e.g. acquiring or disposing of shares. I don't think Form 4 is necessary if you keep the same number of shares and the issuer repurchases shares or issues new shares.)

Schedule 13 (https://www.sec.gov/fast-answers/answerssched13htm.html): Schedule 13D reports the acquisition and other information within ten days after the purchase.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on October 23, 2019, 05:34:39 AM
Could be from the date of the WFC 10-Q being filed, which would be even later since WFC hasn't actually filed the 10-Q with the SEC yet.  The big banks tend to have quite a delay between their 'earnings release' and the filing of the actual 10-Q.  Berkshire themselves always puts them out simultaneously, which like so many of their practices probably has at its root a pet peeve of Mr. Buffett.  Another pet peeve of Mr. Buffett is how deep into a filing you have to go to find actual financial statements like a balance sheet.  Sometimes its hundreds of pages in.  Berkshire always puts them up front.  Balance sheet on page 2...
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 23, 2019, 07:37:43 AM
I believe the 25th July filing in relation to BAC was in response to an event on 17th July, which appears to be on the last page of the Press Release included in BAC's 8K filing (https://www.sec.gov/Archives/edgar/data/70858/000007085819000037/bac6302019ex991.htm) where it states "Ending common shares issued and outstanding" to be 9,342.6 million, taking Berkshire's 950 million already held prior at 30th June (though not known to us at that time) over 10%.

Also in the 8K supplemental information (https://www.sec.gov/Archives/edgar/data/70858/000007085819000037/bac-6302019ex993.htm) it states with full precision:
Common stock and additional paid-in capital, $0.01 par value; authorized – 12,800,000,000 shares; issued and outstanding – 9,342,601,750, 9,568,389,268 and 10,012,719,225 shares
in addition to re-stating the 9,342.6 million rounded figure.

WFC's 8K on 15th Oct, first Exhibit (https://www.sec.gov/Archives/edgar/data/72971/000007297119000383/wfc3qer10-15x19ex991.htm) shows:
Common shares outstanding 4,269.1 million at 30th Sep 2019.

I'd imagine that, being an 8-K filed with SEC, it's probably considered a sufficiently official regulatory disclosure, even if it's packaged with press releases, but I guess we'll find out if Berkshire file within the next couple of days.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 29, 2019, 10:44:35 AM
There has been no filing from Berkshire about its WFC holding exceeding 10% so far.
The most probable two reasons are:
1. WFC isn't deemed to have disclosed its latest share count within the meaning of form 3 - it only gave a count rounded to the nearest 0.1 million shares as of 30th Sept in press release preliminary financials, not an exact statement of shares outstanding as they would produce in their 10-Q.
2. Berkshire has reduced its holding in WFC during Q3 or even in October, prior to the WFC announcement to below 426.91 million shares.

It may be that WFC is different to BAC and Berkshire has reasons to stay below 10% - closer banking relationships or keeping options open regarding reporting of share sales.

I guess the Berkshire 10-Q is most likely to come out on Sat 2nd Nov, though possibly as late at Sat 9th Nov.

The WFC position will be among the top five holdings, so the 'fair value' of the position at 30th Sept's closing price of $50.44 per share would normally be disclosed in Note 4. Investments in equity securities, rounded to the nearest $0.1 billion. From that we can estimate the number of shares held in each of those top 5.

255.3 million AAPL at $223.97 would round to $57.2 billion ($57,179,614,686 rounded up). It's possible but unlikely that this position has changed in Q3.
950.0 million BAC at $29.17 would round to $27.7 billion ($27,711,500,000 rounded down). This is known. We now know that a few of these shares worth about $65 million were divested with the sale of Applied Underwriters, but only after the 30th September quarter-end date. As it's over 10%, any movements must be reported rapidly to the SEC.
400.0 million KO (Coca Cola) shares at $54.44 will have Fair Value of $21.8 billion ($21,776,000,000 rounded up). This is known, as Berkshire has to file any trades as it owns >10% of KO.
151.6 million AXP (American Express) at $118.28 will have Fair Value of $17.9 billion ($17,932,513,596 rounded down). Again, known as they hold >10%.
WFC could be maybe $21.0 billion to $21.8 billion, the top end being if they haven't sold any shares in Q3. This is the main unknown at present.

A WFC holding remaining at 432,383,973 shares would be reported at $21.8 billion 'fair value' ($21,809,447,598 rounded down), matching KO, and unless Berkshire has sold before WFC reveals their exact share count, we'd then get a Form 3 or Form 4 filing to reveal the 10% holding within about 10 days of Wells Fargo confirming their exact common stock outstanding, presumably in their 10-Q, also likely to be published this week or possibly next.
A reduced WFC holding of, say, 426,900,000 shares would be reported at $21.5 billion 'fair value', which would actually cover a possible range from 425,257,732 to 427,240,285 shares, either of which extreme will only just round to $21.5 billion using conventional rounding rules. This range spans the 10% mark, so we'd have to wait to see if there's a further filing or not.

If Berkshire has sold to stay below 10% during Q3, I imagine they'd have chosen to err on the safe side and have reduced to perhaps 420 million shares or fewer ($21.2 bn).

Berkshire's Fair Value seems to use the widely reported closing price for the last trading day of the quarter every time I've checked (and I've checked the top five positions, like AAPL, BAC, KO, WFC, AXP every quarter for the last 7 or 8 quarters and top 15 every 10-K), and occasional coincidences of rounding break points almost prove they had used the exact closing price I obtained from GoogleFinance and Yahoo Finance.

I'd estimate that the total Fair Value of Investments in equity securities, including Others outside the top 5 (and not including KHC of course), would be no less than $210.7 bn, assuming something new was bought for about $1.0bn to replace RHT after its takeover completed, probably a Todd or Ted merger arbitrage trade. It could well be up to $6bn higher if new after-tax earnings during the quarter were also reinvested into equity securities, so probably a portfolio total in the range $210-$217 billion is possible.

I think they beat the S&P500 by almost 4% for the quarter, although that period is too short to be very meaningful, and I think they have extended that lead to about 7.5% so far in October, after gains for Apple and the banks, with the portfolio on 29th October being valued at about $221-$228 bn I'd imagine, depending how much of the earnings were invested in stocks.

With TTM earnings of about $14.0bn ($5.73 per BRK.B share), I make the trailing P/E ratio of the portfolio about 15.7, and its look through market price around $90.18 per BRK.B share, meaning that at $212.53, you get the operating part of Berkshire for $122.35. If that operating portion brings in a little over $10 per BRK.B share of after tax operating earnings normally, that could be an earnings yield over 8%, or over the whole of BRK.B, fairly conservative look-through earnings (including Berkshire's share of EPS) of say $15.75 per B share (7.4% earnings yield on $212.53). This year, the share of KHC write-downs has depressed reported earnings, but I think over $15.75 per B share is probably a fair normalized figure for look-through earnings.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on October 29, 2019, 11:42:24 AM
thanks Dynamic.  Yes, earnings release is this Saturday morning.  I'll be in the country for the weekend at an old 1820's plantation with no printer.  Hoping to sneak a laptop in...

Curious to see if he's still selling down Wells, adding to JPM or any other new surprises.  That apple position sure perked up.  255 million shares is no joke - sooner or later you're talkin' real money
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on October 29, 2019, 12:18:29 PM
Enjoy your weekend gfp. I think I could wait til Monday to digest the results if we're out having fun, though my timezone gives me a few extra hours before the market opens. A few quarters lately I've had my first look on my smartphone while out visiting friends, but it's hard to resist taking an early look and calculating a couple of numbers I've been looking for.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: CassiusKing1 on October 30, 2019, 05:55:25 AM
Great detail and explanation.  Thanks for the continued effort and sharing!!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: gfp on November 02, 2019, 09:10:12 AM
Certainly seems they have continued to sell down the WFC stake to stay below 10%. 
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 04, 2019, 02:00:03 AM
I've looked at Note 4 in the Q3 report. This summarises Investments in Equity Securities (excluding KHC and other partial control stakes accounted under the Equity Method). I also compared this to the same note on Q2's report.

This category does include the new $10bn investment info Occidental Petroleum preferred on 8th August.

The overall cost basis in the portfolio increased from $102,620 million to $112,207 million, implying that $9,587 million of cost basis was added net.

The cost basis of Banks, insurance and finance and of Consumer products declined slightly, presumably reflecting sales of WFC shares and perhaps some other, maybe including a slight trimming of the AAPL stakes held by Ted Weschler (or Todd Combs) possibly to invest elsewhere.

The cost basis in Commercial, industrial and other increased by $10,769 million, which is $0.769 billion more than the Occidental should account for.

I would not think there has been very much net buying of new securities other than the OXY deal and some reinvestment of closed positions like the cash proceeds of the Red Hat merger.

Unrealized gains increased from $97,896 to $107,844 million, an increase of $9,948 million. I estimated that an unchanged portfolio since 30th June would have gained 5.1% or $10,181 million (beating the S&P500 by about 3.9%), including realised gains such as those on Red Hat proceeds, so this seems pretty much in line.

The total portfolio value increased from $200,516 million to $220,051 million, an increase of $19,535 million. This is $9,354 million more than my estimate of gains on an unchanged portfolio, and less than the $10,000 million of the OXY investment.

Now looking at the top 5 positions:

AXP and KO positions are essentially permanent and would be disclosed early to SEC if changed as they exceed 10%. They also match my closing-price estimates when rounded to nearest $0.1bn

WFC, 432,383,973 shares held at 30th June. $50.44 closing price 30th September per Yahoo Finance and Google Finance.
$21,809,447,598 holding at 30th Sep unless shares were sold or purchased. Rounds to $21.8 bn
Note 4 reported fair value = $20.2 bn.
Clearly some were sold to stay below 10% despite WFC's strong buybacks, hence the reduction in cost basis in the Banks, insurance and finance category.

Min holding at 30 Sep $50.44/sh = 399,484,536 shares
Max holding at 30 Sep $50.44/sh = 401,467,089 shares
Central estimate at $50.44/sh = 400,475,813 shares ± 991,276

It's possible that the closing price of $50.44 is not exactly the one used by Berkshire Hathaway, but my past experience is that Yahoo Finance and Google Finance figures for quarter end closing prices match very well to the nearest $0.1 bn.

Verdict: Wells Fargo trimmed fairly substantially to remain below 10%.

AAPL, 255,300,329 shares held at 30th June. $223.97 closing price 30th September per Yahoo Finance and Google Finance.
$57,179,614,686.13
Note 4 reported fair value = $57.0 bn.
Closing price implied if holding unchanged = $223.08 - $223.46 - both would round to $57.0 bn to nearest $0.1bn

Min holding at 30 Sep $233.97/sh = 254,275,127 shares
Max holding at 30 Sep $233.97/sh = 254,721,614 shares
Central estimate at $233.97/sh = 254,498,370 shares ± 223,244

A small amount of selling could account for the cost basis reduction in Consumer products.

Verdict: Apple is likely to have seen light trimming possibly for Ted or Todd to reinvest into a more undervalued position.

BAC, 950,000,000 shares held at 30th June. $29.17 closing price 30th September per Yahoo Finance and Google Finance. Also known to have held 950,000,000 shares at 9th Oct prior to disposal of Applied Underwriters, per Form 4 (https://www.sec.gov/Archives/edgar/data/70858/000120919119053219/xslF345X03/doc4.xml) filed 15th Oct.
$27,711,500,000.00 (rounds to $27.7bn)
Note 4 reported fair value = $27.8 bn.
Closing price implied if holding unchanged = $29.22 - $29.26 - both would round to $27.8 bn to nearest $0.1bn

Berkshire also holds 354 shares of Series T 6% Non-Cumulative Perpetual Preferred Stock in Bank of America Corporation.
I believe these are probably worth $37,170,000 as they're redeemable by BAC at $105,000 per share.

Even adding in this, I get a figure of $27,748,670,000 for the total investment in BAC. That is very close to the rounding threshold, but not quite there.

My guess is that either the closing price of the BAC common stock is different to the Yahoo / Google figures (possibly they use the same data feed) or that the Fair Value of the Preferred has been calculated a little higher based on some other calculation of Fair Value (e.g. based on lower Fed rates) or by comparison to observable prices of comparable BAC securities that are traded in the market.

Verdict: Bank of America Corporation holding unchanged at 30th Sep, but possibly adjustment to fair value calculation on their preferred stock.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on November 04, 2019, 03:33:07 AM
Thank you for the very elaborate walk-through of the situation as you see it, Dynamic,

Just for information, BAC has released its 2019Q3 10-Q on October 28th 2019, stating a total share count of 8,995,807,401 (http://investor.bankofamerica.com/static-files/2793fb20-84e2-4e26-8119-a69ab5500a39) on October 25th 2019. So I suppose Berkshire is holding on to its BAC position until there is a decision if it can go above 10 percent position, or Berkshire also for BAC will become a forced seller as for WFC, until new practice for bank holding companies [likely] is applied.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 04, 2019, 06:35:02 AM
Yes, Berkshire owned 950,000,000 BAC common stock on 30th June and hasn't bought or sold any since. They only divested 2,240,000 shares on selling Applied Underwriters (https://www.sec.gov/Archives/edgar/data/70858/000120919119053219/xslF345X03/doc4.xml) on 10th October and their known percentage of shares outstanding has gone up when BAC revealed their share share count was under 9 billion shares as you mention. They have to file within 5 days of any activity as they did on 15th October, so I'm sure the 13F-HR filings will show 950,000,000 shares at 30th September overall (927,248,600 on BRK's 13F plus 22,751,400 more on NEAM's 13F).

As for WFC, it appears that either their banking relationship is closer, so they're either not permitted to own over 10% by the Fed and keep their banking relationships with Wells Fargo, or they want to retain the flexibility to sell shares without disclosing it within 5 days. I suspect the former is the main reason, though I cannot remember why I formed the impression they work closely with them.

Another interesting thing to note is that the market value of the Apple holding now represents almost one eighth of Berkshire's stock price at prices around Friday's closing prices, even if they sold a little AAPL during Q3.

The holdings in banks and financials collectively account for about 1/6th of Berkshire's stock price by my reckoning.

I've applied my central estimates for the number of shares of AAPL and WFC held in the Look Through portfolio spreadsheet (only the one that the public cannot edit for now) by way of the adjustment column. I can remove those once the 13F-HR is out. I already posted the BAC adjustment announced 15th October.

Also, I cancelled out the RHT holding as this was taken over during Q3. The proceeds are probably reinvested somewhere already, as I think this was Ted or Todd's position, but we won't know until the 13F.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 12, 2019, 04:02:24 PM
Berkshire has made two 'Ownership' filings on 12th November:

1. Berkshire has filed Form 3 (https://www.sec.gov/Archives/edgar/data/92380/000120919119056146/xslF345X02/doc3.xml) as it has become a 10% controller of LUV Southwest Airlines stock since 8th November when the 10-Q was filed by LUV indicating
Number of shares of Common Stock outstanding as of the close of business on November 6, 2019: 526,276,126
The 53,649,213 shares controlled by Berkshire and various pension funds it controls at 30th June are unchanged, but LUV buybacks have caused it to exceed 10% (approx. 10.19%). The adjustment for the pension funds remains at -6,956,500, leaving the share attributable to Berkshire shareholders at 46,962,713 shares as before, which is now 8.92% economic interest in LUV. I do not need to modify the Look-Through Portfolio sheets in light of this, as I had previously obtained the correct figures and adjustments from a SC 13G/A filing.

2. Berkshire also filed a SC 13D/A (https://www.sec.gov/Archives/edgar/data/927066/000119312519290150/d792120dsc13da.htm) report relating to DAVITA INC. (DVA). Over half of Berkshire's position is beneficially owned by subsidiary pension funds and the total contolled shares is unchanged at 38,565,570. The percentage is now 29.7% based on the 10-Q filed on 7th November:
As of November 1, 2019, the number of shares of the Registrant’s common stock outstanding was approximately 129.7 million shares.. All of the shares for the benefit of Berkshire shareholders are held via National Indemnity Company, with 18,513,482 shares (14.3%). The rest are held in various pension trusts. R Ted Weschler has some shares too, but they're not included in the Berkshire 13F-HR or the total shares controlled by Berkshire. Again, it's probably just a percentage ownership change that is being notified and I don't need to adjust the look-through spreadsheet.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 14, 2019, 02:19:27 PM
Latest 13F-HR summary: (not yet adjusted on my public Google Sheets)

Two new positions on the 13F-HR filings are OXY (Occidental Petroleum common stock) and RH (Restoration Holdings common stock)
AAPL (Apple) and WFC (Wells Fargo) reduced as previewed by 10-Q filing (see above). PSX (Phillips 66) also reduced.
RHT (Red Hat) eliminated upon take-over.
BAC was unchanged as at 30th September (the 13F date), but was reduced marginally on 15th October on the sale of Applied Underwriters.

AAL_________ American Airlin…Group Inc ____unch _unchanged count
AAPL________ Apple Inc._______________ __-0.29% _____254,549,679
AMZN________ Amazon Com Inc___________ ____unch _unchanged count
AXP_________ American Express Co______ ____unch _unchanged count
AXTA________ Axalta Coating …stems Ltd ____unch _unchanged count
BAC_________ Bank of America…rporation ____unch _unchanged count
BK__________ Bank of New Yor…llon Corp ____unch _unchanged count
CASS________ Società Cattoli…operativa ____unch _unchanged count
CHTR________ Charter Communi…tions Inc ____unch _unchanged count
COST________ Costco Wholesale Corp____ ____unch _unchanged count
DAL_________ Delta Air Lines, Inc_____ ____unch _unchanged count
DEO_________ Diageo P L C Spon ADR New ____unch _unchanged count
DVA_________ DaVita HealthCa…tners Inc ____unch _unchanged count
GL__________ Globe Life Inc …ark Corp) ____unch _unchanged count
GM__________ General Motors Co________ ____unch _unchanged count
GS__________ Goldman Sachs Group Inc__ ____unch _unchanged count
HCG_________ Home Capital Gr… (CANADA) ________ ________________
HKG:1211____ BYD Company Lim… Listing) ____unch _unchanged count
JNJ_________ Johnson & Johnson________ ____unch _unchanged count
JPM_________ JPMorgan Chase & Co______ ____unch _unchanged count
KHC_________ Kraft Heinz Co___________ ____unch _unchanged count
KO__________ Coca-Cola Co_____________ ____unch _unchanged count
LBTYA_______ Liberty Global …c Class A ____unch _unchanged count
LBTYK_______ Liberty Global …c Class C ____unch _unchanged count
LILA________ Liberty Global …C Class A ____unch _unchanged count
LILAK_______ Liberty Global …C Class C ____unch _unchanged count
LSXMA_______ Liberty Sirius … Series A ____unch _unchanged count
LSXMK_______ Liberty Sirius … Series C ____unch _unchanged count
LUV_________ Southwest Airls Co_______ ____unch _unchanged count
LXS_________ Lanxess AG_______________ ________ ________________
MA__________ MasterCard Inc___________ ____unch _unchanged count
MCO_________ Moody's Corporation______ ____unch _unchanged count
MDLZ________ Mondelez Intern…ional Inc ____unch _unchanged count
MTB_________ M&T Bank Corp____________ ____unch _unchanged count
OXY_________ Occidental Petr…eum Corp. _**NEW** _______7,467,508
OXY.preferre Occidental 8% p…red stock ____unch _unchanged count
PG__________ Proctor and Gamble_______ ____unch _unchanged count
PNC_________ PNC Financial S…Group Inc ____unch _unchanged count
PSX_________ Phillips 66______________ __-6.66% _______5,182,637
QSR_________ Restaurant Bran…ional Inc ____unch _unchanged count
RH__________ Restoration Har…ings, Inc _**NEW** _______1,207,844
RHT_________ Red Hat Inc______________ -100.00% ________________
SAN_________ Sanofi Euronext Paris____ ____unch _unchanged count
SIRI________ Sirius XM Hldgs Inc______ __-1.19% _____136,275,729
STNE________ StoneCo Ltd._____________ ____unch _unchanged count
STOR________ Store Capital Corp_______ ____unch _unchanged count
SU__________ Suncor Energy Inc New____ ____unch _unchanged count
SYF_________ Synchrony Financial______ ____unch _unchanged count
TEVA________ Teva Pharmaceut…Ltd (ADR) ____unch _unchanged count
TRV_________ Travelers Companies Inc__ ____unch _unchanged count
UAL_________ United Continen…dings Inc ____unch _unchanged count
UPS_________ United Parcel S…Inc (UPS) ____unch _unchanged count
USB_________ U.S. Bancorp_____________ ____unch _unchanged count
V___________ Visa Inc_________________ ____unch _unchanged count
VRSN________ VeriSign Inc_____________ ____unch _unchanged count
WFC_________ Wells Fargo & Co New_____ __-7.27% _____400,949,218
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: John Hjorth on November 14, 2019, 02:59:35 PM
Thank you, Dynamic,

Pretty much an uneventful quarter. - You get closer and closer a priori [before actual reporting] quarter by quarter! [ : - ) ]

Again, thank you for sharing this awesome work!
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 20, 2019, 03:17:54 AM
I believe I've now updated both sheets to reflect the current known portfolio. The links in this old post will still work.

The spreadsheet I'd advise you to Make a copy of for your own use is:
• Berkshire Hathaway Look Through Earnings & Holdings (https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/)

The spreadsheet that is publicly editable by anyone (but anyone can see any edits you make, the edit history, or corrupt the spreadsheet) is:
• Berkshire Look through earnings - Public editing allowed (https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing)

The sheets are now updated. I made an attempt to account for OXY preferred stock assuming its fair value is 5% above the initial investment of $10bn, i.e. $10.5bn, which would represent the cost for OXY to buy out the preferred. As the 8% yield is counted in Berkshire's bottom line as cash received or owed (an asset) or paid as OXY stock received, this preferred stock has no look through EPS to add to Berkshire's look through earnings.

It's possible that the OXY warrants have a value in Berkshire's portfolio that is presumably marked to model, with something along the lines of a Black Scholes approximation which it might be possible to estimate. That might explain why my end of quarter portfolio valuation (excluding KHC) is now about $1.6 billion below Berkshire's equity portfolio, still <1% difference, but is a few times larger than it was at 30th June.
Title: Re: Look through portfolio - Google Sheets with live prices
Post by: Dynamic on November 27, 2019, 01:38:03 PM
A new SEC filing reveals a 10.2% stake is now held in LUV, unchanged share count and still exactly the same pension fund holdings as before to deduct for three shareholders stake, so it's due to buybacks. But it would require any future change to be notified within 5 days as I understand it. Probably a sign that this is a potentially near permanent holding in Berkshire's eyes.

Here's the share count trend over several years  in a chart. (https://www.macrotrends.net/stocks/charts/LUV/southwest-airlines/shares-outstanding)