Author Topic: Look through portfolio - Google Sheets with live prices  (Read 37768 times)

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #80 on: April 26, 2019, 11:21:44 AM »
The purchase of USG Corp by Knauf completed on Wednesday at $43.50 per share (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.


John Hjorth

  • Hero Member
  • *****
  • Posts: 2585
Re: Look through portfolio - Google Sheets with live prices
« Reply #81 on: April 26, 2019, 12:09:54 PM »
Thank you for the heads-up, Dynamic,

I really appreciate your tool.
”In the race of excellence … there is no finish line.”
-HH Sheikh Mohammed Bin Rashid Al Maktoum, Vice President and Prime Minister of the United Arab Emirates and Ruler of Dubai

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #82 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.

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #83 on: May 16, 2019, 07:38:52 AM »
The new 13-F showing the portfolio position at 31st March has been published.

As always the primary_doc mentions New England Asset Management (NEAM) under heading List of Other Managers Reporting for this Manager and as usual the 13-F for NEAM has a primary_doc 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

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

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.
« Last Edit: May 29, 2019, 09:03:22 AM by Dynamic »

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #84 on: May 18, 2019, 04:20:31 AM »
Thanks to a comment below a Seeking Alpha article about Berkshire's portfolio by Bower-Ranger 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.
« Last Edit: May 30, 2019, 06:09:51 AM by Dynamic »

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #85 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

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #86 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.

gfp

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1746
Re: Look through portfolio - Google Sheets with live prices
« Reply #87 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. 
« Last Edit: June 07, 2019, 11:18:46 AM by gfp »

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #88 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.

Dynamic

  • Hero Member
  • *****
  • Posts: 503
Re: Look through portfolio - Google Sheets with live prices
« Reply #89 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 I came across the SEC's 13F FAQ 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.