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

CassiusKing1

  • Full Member
  • ***
  • Posts: 131
Re: Look through portfolio - Google Sheets with live prices
« Reply #110 on: October 30, 2019, 05:55:25 AM »
Great detail and explanation.  Thanks for the continued effort and sharing!!


gfp

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1835
Re: Look through portfolio - Google Sheets with live prices
« Reply #111 on: November 02, 2019, 09:10:12 AM »
Certainly seems they have continued to sell down the WFC stake to stay below 10%. 

Dynamic

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

John Hjorth

  • Hero Member
  • *****
  • Posts: 2853
Re: Look through portfolio - Google Sheets with live prices
« Reply #113 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 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.
”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: 560
Re: Look through portfolio - Google Sheets with live prices
« Reply #114 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 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.

Dynamic

  • Hero Member
  • *****
  • Posts: 560
Re: Look through portfolio - Google Sheets with live prices
« Reply #115 on: November 12, 2019, 04:02:24 PM »
Berkshire has made two 'Ownership' filings on 12th November:

1. Berkshire has filed Form 3 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 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.

Dynamic

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

John Hjorth

  • Hero Member
  • *****
  • Posts: 2853
Re: Look through portfolio - Google Sheets with live prices
« Reply #117 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!
”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: 560
Re: Look through portfolio - Google Sheets with live prices
« Reply #118 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

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

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.

Dynamic

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