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

John Hjorth

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


shalab

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

Dynamic

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

gfp

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

Dynamic

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

gfp

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

Dynamic

  • Hero Member
  • *****
  • Posts: 553
Re: Look through portfolio - Google Sheets with live prices
« Reply #96 on: July 29, 2019, 03:38:52 PM »
Incidentally, gfp, I have checked the values held in the pension plans against the 13ga filing 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 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.
« Last Edit: August 05, 2019, 05:41:46 AM by Dynamic »

Dynamic

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

gfp

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1825
Re: Look through portfolio - Google Sheets with live prices
« Reply #98 on: August 19, 2019, 05:10:41 PM »
Thanks Dynamic.  Good job with the formatting!

Dynamic

  • Hero Member
  • *****
  • Posts: 553
Re: Look through portfolio - Google Sheets with live prices
« Reply #99 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 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 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).
« Last Edit: August 20, 2019, 06:28:29 AM by Dynamic »