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


  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1979
Re: Look through portfolio - Google Sheets with live prices
« Reply #120 on: December 31, 2019, 08:52:08 AM »
Man, it looks like it will be one hell of a year-end mark on the apple position.  254.55 million shares at around 292.  Yowza.

John Hjorth

  • Hero Member
  • *****
  • Posts: 3142
Re: Look through portfolio - Google Sheets with live prices
« Reply #121 on: December 31, 2019, 10:06:04 AM »
Man, it looks like it will be one hell of a year-end mark on the apple position.  254.55 million shares at around 292.  Yowza.


Yes, I actually took the time yesterday to open the spreadsheet provided and shared by Dynamic to look at it. Holy Molly! [0_0] - And people on Twitter have started to whine about why Mr. Buffett isn't reducing the position! - Let's see how it plays out.
”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


  • Hero Member
  • *****
  • Posts: 617
Re: Look through portfolio - Google Sheets with live prices
« Reply #122 on: January 29, 2020, 08:09:13 AM »
Just to make people who use the spreadsheet aware, when the deal to sell off the newspapers to Lee Enterprises closes - possibly in March 2020, various items in the Adjustments column relating to pension fund holdings are probably going to need modification as of the future 13F-HR filings.

The 13F after this may be the one that comes out on 15th May 2020.

I suspect a number of 13D or 13G filings will be made, which at that point might no-longer include the pension schemes for the likes of Buffalo News employees, though it may be possible that for a period, some of these will continue to be managed by Berkshire and show up in their 13F-HR.

It is not clear to me yet whether any of the assets of the Berkshire Hathaway consolidated pension scheme will also be transferred out, if any of the employees of other newspapers are members of that scheme, but I imagine a number of SEC filings will begin to clear things up at the time, just as they did when Applied Underwriters was spun off, and some BAC stock was transferred out with it.

- edit - I would imagine that the holdings are relatively minor. For example, the American Airlines holdings by pension plans are almost half of the 13F holding, but those in the Buffalo News plans are dwarfed by other pension plans like GEICO's. That's the only one I've really looked into. I don't imagine it will have a material effect on Berkshire's ability to increase holdings without crossing 10%.
« Last Edit: January 29, 2020, 08:33:57 AM by Dynamic »


  • Full Member
  • ***
  • Posts: 210
Re: Look through portfolio - Google Sheets with live prices
« Reply #123 on: February 10, 2020, 04:20:34 PM »
Did Berkshire sell holding its in Bank of New York Mellon? It seems like its holding is now below 10%.


  • Hero Member
  • *****
  • Posts: 617
Re: Look through portfolio - Google Sheets with live prices
« Reply #124 on: February 11, 2020, 12:35:40 AM »
The total holding over Berkshire's 13F-HR and New England Asset Managements' 13F-HR is exactly 92 million shares of BK, which it has been since 31st Dec 2018 (when it was 9.6% of BK's outstanding shares) - a few of those shares are held via NEAM so don't appear on CNBC's portfolio tracker or any other public 13F trackers I've seen.

That share count is unchanged and I don't think it has ever exceeded 10%. To my knowledge the only bank that exceeded 10% was BAC.

BK now has only 922.2 million shares outstanding, so the proportion controlled by Berkshire is now 9.98%. It seems the Bank Holding Company Act rule change will be just in time, coming into effect on 1st April 2020 and allowing Berkshire to hold over 10% without becoming a BHC. Berkshire will probably have to file with SEC as a 10% holder within a few days of crossing the 10% threshold of known BK shares outstanding and from then on report any trades within a few days instead of just quarterly.

There is an adjustment applied by my sheet of -7,511,249 shares to account for holdings by pension funds of Berkshire and its subsidiaries.

On COMBINED HOLDINGS tab, column H (now corrected) my sheet shows details of the sources of these adjustments:
To match page 9 of 2017 Annual Report figures (to exclude pension scheme assets) and (replacing )

If you check out that 13G filling link from 14 Feb 2018 you'll find the pension fund holdings on pages 30-32 and on page 47.

Note 3 on about page 51, confirms:
Berkshire Hathaway Consolidated Pension Plan Mater Retirement Trust, BNSF Master Retirement Trust, GEICO Corporation Pension Plan Trust and Precision Castparts Corp. Master Trust are each an Employee Benefit Plan in accordance with § 240.13d-1(b)(1)(ii)(F).
and this is also confirmed in EXHIBIT A:

Berkshire Hathaway Consolidated Pension Plan Master Retirement Trust

BNSF Master Retirement Trust

GEICO Corporation Pension Plan Trust

Precision Castparts Corp. Master Trust

Note: No Common Stock of The Bank of New York Mellon Corporation is held directly by Berkshire Hathaway Inc. or by Warren E. Buffett, an individual who may be deemed to control Berkshire Hathaway Inc. 1,417,000 shares or approximately 0.1% of Common Stock of The Bank of New York Mellon Corporation are held directly by Brilliant National Services, Inc., MPP Company, Inc. and NRG America Holding Company, none of which are persons specified in Rule 13d-1 (b) (1) (ii) (A) through (J).

I include that Note also, because it mentions 1,417,000 shares held by companies not defined under SEC Rule 13d. These three companies and their holdings are still listed in the 13G filing, but it's just pointing out that they are not required to file under the SEC Rule 13d.

These filings can be tricky to parse because many of the holdings are shown at the parent company level and the subsidiary level, and subsidiaries of the subsidiaries too, so the totals of all numbers far exceed the overall holding. Fortunately, the employee benefit pension funds do not have subsidiaries, so they can be added together quite simply to provide the adjustment so that we only count the shares held for the benefit of Berkshire shareholders.


  • Full Member
  • ***
  • Posts: 210
Re: Look through portfolio - Google Sheets with live prices
« Reply #125 on: February 11, 2020, 08:33:09 AM »
As per presentation here on page 3,

Common shares outstanding (in thousands) are 900,683 on 31 Dec 2019.

I guess since part of the holding is via pension funds Berkshire might not have reported more than 10% but may be in few months things will change.

13F for Dec 2019 should be coming soon.

Thanks a lot Dynamic for maintaining these details and your detailed response.


  • Hero Member
  • *****
  • Posts: 617
Re: Look through portfolio - Google Sheets with live prices
« Reply #126 on: February 11, 2020, 09:26:28 AM »
Thanks for that @valueinvesting101. I just googled the BK share count and found two sources stating 922.2 million as at 31st Dec 2019, which is wrong.

I would imagine that either Berkshire will have sold enough to remain under 10% (which we should discover with both of Friday's 13F-HR filings) or they may be waiting for the formal SEC 10-K filing from Bank of New York Mellon with an exact count of shares outstanding before they file with SEC that they are now a 10% owner. The last relevant SEC filing was the 30 Sep 2019 10-Q, stating:
As of Sept. 30, 2019, 922,198,877 shares of the registrant’s common stock, $0.01 par value per share, were outstanding.
I guess we'll find out soon.


  • Hero Member
  • *****
  • Posts: 617
Re: Look through portfolio - Google Sheets with live prices
« Reply #127 on: February 17, 2020, 01:36:21 AM »
The new 13F-HR filings for Berkshire Hathaway and New England Asset Management came out on Friday, and Berkshire also filed a number of 13G or 13G/A filings, plus Form 5 disclosing Warren Buffett's stake in BRK.A and donations of BRK.B as at Dec 31, 2019. All these filings are available via SEC's EDGAR at this link and it's the 2020-02-14 dated filings that I've checked at time of writing.

Most of the 13G or 13G/A filings show unchanged Employee Pension holdings, but the new 13G filing in respect of RH (formerly Restoration Hardware Holdings) reveals that despite a much-increased share count this quarter taking the proportion of RH voting controlled to 9.0%, much of that holding is in Employee Pension Funds (code EP) so the amount I know think is attributable to Berkshire shareholders is lower than I thought last quarter, when I didn't know any was in EP.

New holdings - Biogen Inc, Kroger Co., SPY and VOO index funds, all small positions likely Ted and Todd.

Oddly, SPY and VOO have look-through holdings in 500 companies including BRK.B itself (an interesting recursion), but the positions are small and EPS is not provided by GoogleFinance, so for now, I'm ignoring their look-through contribution as negligible.

As usual, my figures on changes will differ from other published sources, which I consider to be incomplete for tracking Look-Through ownership, because
(1) I deduct Employee Pension fund (EP) holdings where possible, some of which are substantial
(2) I do include the proportion of Gen Re holdings that appear on the 13F for NEAM which are ignored by every other published Berkshire portfolio tracker I've seen.
(3) I make reasonable estimates of non-13F securities such as non-US securities based on limited reporting and occasional disclosure in 10-K filings and whether my end-of-quarter Equity Portfolio valuation is reasonably close.
Some notable examples are BYD Co. (HKG:1211) held by BH Energy which is reported on some Hong Kong websites and Sanofi (traded Euronext Paris), for which the ADRs were sold, but I suspect the Euronext shares are still held - and the 10-K may well confirm or refure this as it would be a top-15 holding at year-end 2019 if still held, which it wasn't in 2018.
I also make a reasonable assumption of the value of the Occidental 8% Preferred stock, which I assume is carried at a fixed $10.5 billion valuation but which doesn't contribute to Look-Through investee Earnings, as the 8% dividend would come either via OXY stock or 'Other' income reported in Berkshire's own earnings. As yet, I haven't attempted to put a value on the small remaining position in BAC 6% preferred stock.

Changes noted (full list in fixed-width font table below): - where I mention the valuation of the position 'now' or 'today', I'm basically referring to Friday 14th Feb 2020 closing prices.

AAL - American Airlines reduced shareholder-owned share count by 5.2% so that total share count remains just below 10% voting control. N.B. Employee pensions account for almost half of Berkshire's holding, but EP count did not change. Some EP holdings will disappear when the newspapers are acquired by Lee. $635mn valuation of shareholder's stake.

AAPL - Apple reduced by 1.4%, probably Ted or Todd needing to sell something fairly full-valued to invest elsewhere, I guess. Still a vast position at $81,519mn today.

BAC - Bank of America reduced by 0.2% since 2019-09-30 due to divesting Applied Underwriters, as already disclosed in Oct 2019 filing. Percentage stake now 10.7% thanks to buybacks. $33,029mn valuation today.

BIIB - NEW STAKE - Biogen Inc., worth $216m today. Almost certainly Ted or Todd.

BK - Bank of New York Mellon reduced 3.6% to stay below 10% stake, 9.9% stake thanks to buybacks. $3,740mn today.

GM - General Motors increased BRK shareholders' stake by 6.0%. EPs own about a third of total stake. $1,669mn today for shareholders.

GS - Goldman Sachs reduced 33.8%. Now 5.1% stake in GS. $2,948mn today.

KR - NEW STAKE - Kroger Co. worth $535mn today. Almost certainly Ted or Todd.

OXY - Occidental Petroleum increased 153.5%. Stake worth $788mn today. The option of paying the $10bn 8% preferred dividend in OXY stock could be responsible for some or all of this position. Might be revealed in the Berkshire or Occidental 10-K soon, or if we could guess the payment dates based on the acquisition of Anadarko, we might be able to reconcile it to the $0.2bn per quarter in preferred dividends and OXY stock issuance. Quite possibly it's a mixture of open market purchases and dividends in stock.

PSX - Phillips 66 reduced by 95.6%, and quite possibly eliminated entirely since 31st Dec 2019. $20mn today if still held.

RH - RH, formerly Restoration Hardware, increased possibly by as much as 112.6% depending on how much was already owned by the pension in the previous quarter. $225mn stake for BRK shareholders (5.0% of company), plus 4.0% of company for the Precision Castparts Master Trust Employee Pension (EP), giving 9.0% voting stake.

SPY - NEW STAKE - SPDR S&P 500 ETF Trust, just $13.3mn at today's price. Almost certainly Ted or Todd.

SU -Suncor Energy Inc increased 39.6%. Now $447mn.

TRV - Travelers Companies Inc decreased 94.8% and quite possibly eliminated entirely since 31st Dec 2019. $43mn today if still held.

VOO - NEW STAKE - Vanguard S&P 500 ETF, just $13.3mn at today's price - virtually the same as SPY. Almost certainly Ted or Todd.

WFC - Wells Fargo & Co reduced by 13.8%. This is quite a substantial reduction on a giant stake. If still holding as much as at 31st Dec 2019, Berkshire holds $16,669mn at today's price. While they may be required to stay below 10% to avoind becoming a Bank Holding Company due to banking relationships, the stake is now 8.4% so they've either bought themselves a lot of time while selling at reasonably high valuations or they're selling for fundamental reasons. Hard to guess whether they took advantage of some reasonably high prices in the 50s last quarter to lighten up prior to the new CEO potentially making some big bath changes or whether they feel the story has changed for the worse and they should either eliminate the position or they're selling based on valuation.

Here's the table of changes:
AAL_________ American Airlin…Group Inc __-5.20% ______21,758,000
AAPL________ Apple Inc._______________ __-1.40% _____250,866,566
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 __-0.20% _____947,760,000
BIIB________ Biogen Inc.______________ _**NEW** _________648,447
BK__________ Bank of New Yor…llon Corp __-3.60% ______81,488,751
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________ ___6.00% ______48,006,000
GS__________ Goldman Sachs Group Inc__ _-33.80% ______12,435,814
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
KR__________ Kroger Co._______________ _**NEW** ______18,940,079
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
OXY_________ Occidental Petr…eum Corp. _153.50% ______18,933,054
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______________ _-95.60% _________227,436
QSR_________ Restaurant Bran…ional Inc ____unch _unchanged count
RH__________ RH (formerly Re…Holdings) _112.60% _________945,079
SAN_________ Sanofi Euronext Paris____ ____unch _unchanged count
SIRI________ Sirius XM Hldgs Inc______ ____unch _unchanged count
SPY_________ SPDR S&P 500 ETF Trust___ _**NEW** __________39,400
STNE________ StoneCo Ltd._____________ ____unch _unchanged count
STOR________ Store Capital Corp_______ ____unch _unchanged count
SU__________ Suncor Energy Inc New____ __39.60% ______15,019,031
SYF_________ Synchrony Financial______ ____unch _unchanged count
TEVA________ Teva Pharmaceut…Ltd (ADR) ____unch _unchanged count
TRV_________ Travelers Companies Inc__ _-94.80% _________312,379
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
VOO_________ Vanguard S&P 500 ETF_____ _**NEW** __________43,000
VRSN________ VeriSign Inc_____________ ____unch _unchanged count
WFC_________ Wells Fargo & Co New_____ _-13.80% _____345,688,918

So far, I've updated the version that is NOT publicly editable at this link, which you may copy to your own Google Drive and enter your own holdings of BRK.A and BRK.B to estimate your own Look-Through holdings and Earnings from the investees:

I may get around to editing the public-edit version later.


  • Hero Member
  • *****
  • Posts: 617
Re: Look through portfolio - Google Sheets with live prices
« Reply #128 on: February 26, 2020, 01:13:31 AM »

The above link tells us that the SPY and VOO index ETF positions are both pension fund holdings, so I've included adjustments to cancel those holdings.

There's also a small -323,300 adjustment I've added to Visa Inc. (V) to match page 10 of the 2019 Annual Report Chairman's letter. This adjusts for shares held by pension funds of Berkshire subsidiaries. As Berkshire only owns 0.6% of Visa, there will not be 13g filings that would disclose the pension holdings specifically.

I've also removed SAN (Sanofi Euronext Paris) - see below.

Each of the lines except Others*** on page 10 now match the share count and also the closing market value on 2019-12-31 matched to the nearest million dollars using normal rounding.

Also, I note that SAN (Sanofi Euronext Paris) is absent from the list on page 10. It last showed in the 2017 Annual Report after which the SNY (Sanofi American Depositary Receipts) position disappeared from the 13F during 2018.

At that time I did not remove SAN, which isn't a 13F security, and left it in my portfolio as it would not have appeared in the 2018 Annual Report as it was below the top 15 positions by market value.

This year, the position would have been the 13th largest, but it is absent, so it may have been eliminated or it may have been reduced to below the $1.924 billion value of the Visa Inc. holding.

I will for now assume that Sanofi has been eliminated entirely.

I also note that the *** note on page 10 states:
*** Includes $10 billion investment in Occidental Petroleum Corporation consisting of preferred stock and
warrants to buy common stock.

It does not explicitly state the 'Market value' of this, but we do know that $10 billion was the cost basis of the preferred/warrant Equity investment in OXY. For now, knowing the additional $0.5 billion in redemption cost, I'm leaving the OXY position at $10.5 billion assumed Market Value.
Maybe one day I'll run something like a Black Scholes method approximation of the value of the warrants to see if this implies any appreciable additional 'fair value' at present. From disclosures of the 'mark-to-model' fair value estimation method used for untraded European-style index put contracts, this is likely to be the way Berkshire would comply with any requirement to estimate fair value. Black Scholes is likely to be very wrong on such long-term contracts, but it's the typically accepted method.

Having adjusted a private copy of the portfolio that I froze at year-end prices, the total portfolio value I track at 31 Dec 2019, excluding KHC (which is excluded from the page 10 portfolio) comes to:
$242,992,080,157 my portfolio at 2019-12-31 close
$248,027,000,000 Berkshire's actual portfolio at 2019-12-31 close

$  5,034,919,843 difference or about 2%

So be aware that 2% is not accounted for.
Some of my error could include:

  • Valuation of non-common-stock Occidental Petroleum Corporation holdings - both 8% preferred stock and attached warrants.
  • Valuation of the very small remaining number of share of 6% perpetual preferred stock in Bank of America Corporation
  • The possibility that Berkshire holds at most 19,130,338 shares of SAN on Euronext Paris - making it at most the 16th largest common stock portfolio holding at year end 2019. I now assume zero holding in SAN.
  • Other US holdings that aren't included in the SEC's list of 13F securities
  • Non US holdings we don't know about - we know about BYD Co. in Hong Kong, have surmised BIT:CASS. I think I need to look into PayTm in India - I think there's a report of similar sized investments in that and STNE. LXS (Lanxess AG in Germany) was also reported in the past.
  • In the opposite direction, other pension fund adjustments that, similar to VISA, I was unaware of.

I'd appreciate any thoughts on this, especially if you have links to support holdings that wouldn't show up in 13F filings.

Certainly being 2% out is pretty close and won't make a whole heap of difference in estimating look-through market value, look-through earnings or adjusting your effective exposure to holdings you own directly and through Berkshire too.

It may be possible to simply model the missing $5 billion as an equivalent holding in a suitable index, with perhaps a sensible estimate of look-through earnings in that index.


  • Hero Member
  • *****
  • Posts: 617
Re: Look through portfolio - Google Sheets with live prices
« Reply #129 on: March 03, 2020, 12:38:04 AM »
According to a SEC Form 4 filing as a 10% owner, and my own calculations, Berkshire (NICO and GEICO, so presumably from shareholder's cash, not pensions) bought an extra 976,507 shares of DAL on Thu 27th Feb at a weighted average price of around $46.3985 per share, spending a total of around $45,308,500 on the additional stock.

I've changed the Adjustment column by +976,507 to account for this look-through ownership. This appears to be around 11.23% of Delta's outstanding shares, up from 11.08% or so.

I still haven't updated the publicly editable version to the latest 13F or this Form 4, just the version you can copy to your own Google Sheet.