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

Dynamic

  • Full Member
  • ***
  • Posts: 202
Re: Look through portfolio - Google Sheets with live prices
« Reply #40 on: February 15, 2018, 09:45:50 AM »
The latest 13-F as of 31st Dec 2017 is now included in all three spreadsheets. There are some doubts about figures assumed but not reportable in the 13-F (see below), but these might be resolved on 23rd Feb when the Annual Report comes out.

If you select a Google Sheets workbook you can edit (or copy it to a private Google Sheet workbook of your own using the instructions on my non-editable version) you can enter your currency and number of shares in the blue cells near the top-left of the first Worksheet tab and you can also set the currency of your choice.

By default, however, I've set the number of shares to represent one-millionth of all those outstanding, amounting to 2467.18 BRK.B equivalent shares at present and the currency to USD.

When cell C5 contains that number, 2467.18, you can read off the equivalent number of shares in each holding as millions of shares held by Berkshire Hathaway as a whole, and the values of those holdings as if they were in millions of US dollars (or whatever currency is set in cell B2). Cell I6 therefore would also represent Berkshire's Market Cap in millions.

If you prefer a simpler layout, click on the tab with the red stripe below its name, "Look-Through Summary". Columns A-J have been distilled down to basic info you might want (less useful columns such as my very rough Low End Valuation have been 'hidden from view' to simplify matters a little). However, if you want to change currency or number of shares you must enter these on the first sheet, entitled "Look-Through BRK earnings and holdings" (as the red text in cell A1 warns you)

I have removed WABCO as it was sold entirely in 2017Q3. Teva is now included.

This version is the one nobody but I can edit. It contains instructions for copying them to your own private spreadsheet so you can edit it to your heart's content in full privacy:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=292668837

This version is editable by anyone, but be aware that anyone can see your edit history, so if you enter private information there, it's potentially disclosed to the whole internet! It could also get corrupted by anyone!
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

There's another version that you may request permission from me to edit, here:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

If you'd like a simplified overview of the BRK portfolio, I can recommend http://minesafetydisclosures.com/individual-investor-portfolio and for CNBC's Look Through, https://www.cnbc.com/berkshire-hathaway-portfolio/

Discussion of discrepancies
The 13-F numbers and the totals agree with one posted on Seeking Alpha, so I'm pretty happy we both entered the information just fine. https://seekingalpha.com/article/4147016-tracking-warren-buffetts-berkshire-hathaway-portfolio-q4-2017-update

The rocketfinancial version of BRK's portfolio seems to have additional stock in US Bancorp, which they seem to have taken from the 13-G filing. I think something odd is going on there as the 13-F is made up to the same date 31st Dec 2017. I love the way they present it (and the speed it was updated yesterday), and I'm sending them a support email to query it. http://www.rocketfinancial.com/Holdings.aspx?fID=1058
They might be correct to rely on the 13-G and the rest of us wrong. I'm not sure. Could the difference be amounts held in pension funds etc, not attributable to shareholders?

Phillips 66 (PSX) shows at 45,689,892 on CNBC. The rest of us have 80,689,892, so I think they're wrong there, https://www.cnbc.com/berkshire-hathaway-portfolio/ meaning they're about $3bn out. The rest matches just fine. I've just contacted them to let them know.

I only update cell AH3 when the annual report tells us the dividends received. This avoids double-counting that portion of look-through earnings that was already reported in GAAP earnings. At the moment this is a year out of date while I wait for the Annual Report.

The Annual Report is usually the only hint to non-US holdings such as BYD, Sanofi (SNW) on Euronext (which I enter as an equivalent number of Sanofi ADRs (SNY)), Societŗ Cattolica di Assicurazione - Societŗ Cooperativa and Home Capital Group (CANADA) - you'll note that these are highlighted differently on the sheets including 13-F LATEST.


globalfinancepartners

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1073
Re: Look through portfolio - Google Sheets with live prices
« Reply #41 on: February 15, 2018, 09:51:21 AM »
The difference in USB, AAPL, etc, is probably in New England Asset management, under general re on Edgar- but iím Away from the office and canít check if thatís your issue at the moment

globalfinancepartners

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1073
Re: Look through portfolio - Google Sheets with live prices
« Reply #42 on: February 15, 2018, 01:51:27 PM »
Yeah - I had a chance to look at your sheet and it looks like you are short the following share amounts, which are also owned by Berkshire but reported separately through New England Asset Management:

ticker, number of additional shares not included in BRK's 13F:
AAPL, 4,217,000
BAC, 21,000,000
DEO, 227,750
GS, 431,063
USG, 4,385,964
USB, 17,386,443
VRSK, 2,954,050
WFC, 24,312,200


It makes sense if you remember that BRK got 700 million BAC shares and hasn't sold any.  You are showing 679 million in your sheet.

Here is the most recent New England Asset Management filing - all the stocks with "Other Manager 01 02" are Berkshire's

https://www.sec.gov/Archives/edgar/data/1004244/000108514618000878/xslForm13F_X01/form13fInfoTable.xml

Quote
Phillips 66 (PSX) shows at 45,689,892 on CNBC. The rest of us have 80,689,892, so I think they're wrong there, https://www.cnbc.com/berkshire-hathaway-portfolio/ meaning they're about $3bn out. The rest matches just fine. I've just contacted them to let them know.

They aren't wrong.  They are reflecting the big sale that Berkshire just announced:
http://www.berkshirehathaway.com/news/feb1418.pdf




The difference in USB, AAPL, etc, is probably in New England Asset management, under general re on Edgar- but iím Away from the office and canít check if thatís your issue at the moment
« Last Edit: February 15, 2018, 02:06:45 PM by globalfinancepartners »

Dynamic

  • Full Member
  • ***
  • Posts: 202
Re: Look through portfolio - Google Sheets with live prices
« Reply #43 on: February 15, 2018, 03:04:35 PM »
Thanks, I've just seen the SEC filing come through on the PSX. I'll update my sheets when I'm on the computer to include the extra stocks via New England that you mention. Probably tomorrow.

If these are beneficially owned by BRK they belong in a Look Through analysis I guess. Does anyone disagree with this plan or the belief that they are to shareholders' benefit?

I'll probably include a separate New England 13F sheet and Berkshire 13F then combine those into aggregate holdings.

Thanks again for the clarifications.

Dynamic

  • Full Member
  • ***
  • Posts: 202
Re: Look through portfolio - Google Sheets with live prices
« Reply #44 on: February 16, 2018, 05:37:48 AM »
OK, I've updated all three versions of the Spreadsheet.

These now include more stock holdings than most BRK portfolio summaries you'll find online:

1. Actual holdings reported in the Berkshire Hathaway Inc 13-F filing at the end of the most recent quarter.
2. Actual holdings belonging to Berkshire in the New England Asset Management Inc 13-F filing (Owner Code "01 02" in Column 7 only) - thank you to globalfinancepartners for steering me here.
3. Assumed holdings of non US securities that are not reportable to SEC but are mentioned in the Annual Report and presumed still to be held unless we hear otherwise.
4. Plus or (minus) acquisitions or (disposals) of securities announced since the last 13-F quarter. This quarter that's the 35,000,000 PSX shares bought back by PSX on 13th Feb 2018 to keep share count below 10% for regulatory reasons.

I may in future cease to maintain the editable version where people can request access soon. It seems sensible just to have two in future:
I believe I cannot just 'Make a Copy' and retain the existing URL I've published, so my copying process is more long-winded, hence the reduction to 2 versions.

The privately editable one is as I edited it. It's now very simple to create a Copy for your own private use by pulling down the File... menu and selecting Make A Copy... which will create a Copy of the whole workbook in your own Google Drive, where you will have Read/Write access.

The publicly editable version is definitely not private - so don't post private or confidential information on it even for a moment as even the Revision History is public. It may also get corrupted by anyone else on the internet, so it's less likely to be reliable than a copy of mine unless someone only fixes errors I've made!

As I said above, I will only update cell AH3 when the annual report tells us the dividends received. This avoids double-counting that portion of look-through earnings that was already reported in GAAP earnings. At the moment this is a year out of date while I wait for the Annual Report. I'm not 100% sure whether dividends received on the New England Asset Management holdings show up in Berkshire's GAAP Earnings, but I suspect they would - it makes accounting sense. If not, it may create a minor error in the operating company earnings adjusted to exclude dividends received to avoid this double-counting.

The Annual Report is usually the only hint to non-US holdings such as BYD, Sanofi (SNW) on Euronext (which I enter as an equivalent number of Sanofi ADRs (SNY)), Societŗ Cattolica di Assicurazione - Societŗ Cooperativa and Home Capital Group (CANADA) - you'll note that these are now included in a separate column in the tab 'COMBINED HOLDINGS' where I also included as a negative figure the sale of 35 million PSX shares announced since 31st Dec 2017, an adjustment that will need to be removed once the 2018q1 13-F filing is published in mid May 2018.