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

Dynamic

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


John Hjorth

  • Hero Member
  • *****
  • Posts: 2374
Re: Look through portfolio - Google Sheets with live prices
« Reply #71 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.
« Last Edit: February 13, 2019, 09:19:31 AM by John Hjorth »
”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

  • Sr. Member
  • ****
  • Posts: 435
Re: Look through portfolio - Google Sheets with live prices
« Reply #72 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, who would otherwise get named in divorce proceedings.   :P

Dynamic

  • Sr. Member
  • ****
  • Posts: 435
Re: Look through portfolio - Google Sheets with live prices
« Reply #73 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 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 with these figures.
« Last Edit: March 15, 2019, 02:51:03 AM by Dynamic »

Dynamic

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

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

Dynamic

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

« Last Edit: March 05, 2019, 05:04:39 PM by Dynamic »

Dynamic

  • Sr. Member
  • ****
  • Posts: 435
Re: Look through portfolio - Google Sheets with live prices
« Reply #76 on: March 11, 2019, 01:34:53 AM »
Berkshire Hathaway has filed Form 3 and Form 4 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.

Dynamic

  • Sr. Member
  • ****
  • Posts: 435
Re: Look through portfolio - Google Sheets with live prices
« Reply #77 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 (also included as a Source on COMBINED HOLDINGS column J) will show only the last 40 Ownership filings relating to Berkshire Hathaway. 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) 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%

John Hjorth

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

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