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

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #20 on: February 24, 2017, 09:41:01 AM »
The 13-F is here: https://www.sec.gov/Archives/edgar/data/1067983/000095012317002417/xslForm13F_X01/form13fInfoTable.xml

However, although relatively immaterial, I have a few differences compared to, say, the CNBC Portfolio tracker: http://www.cnbc.com/berkshire-hathaway-portfolio/.

Foreign holdings never shown on the 13-F:
  • BYD Company Limited (SHE:002594) is assumed to be a permanent holding at 225 million shares.
  • Sanofi (SNW on EuroNext Paris) - I understand that some shares bought in Paris are held, but only the ADRs (NYSE:SNY) are shown on the 13-F. Does anyone know how many SNW are held so I can record this as an assumed permanent holding?

I seem to be OK with US holdings that have been sold:
  • Deere & Co (DE)
  • Now Inc (DNOW)
  • Kinder Morgan Inc (KMI)
  • Lee Enterprises (LEE)

Differences to CNBC Portfolio tracker & name-changes/ticker-changes:
  • Liberty Media Corp Delaware, Class COM A SIRUSXM - this seems to have been discontinued or ceased trading on the market since 31 Dec 2016. Anyone sure how many shares of what ticker the 10,058,800 shares turned into?
  • Liberty Media Corp Delaware, Class COM C SIRUSXM - this seems to have been discontinued or ceased trading on the market since 31 Dec 2016. Anyone sure how many shares of what ticker the 22,236,109 shares turned into?
  • All the Liberty Global plc and Liberty Media things seem confusing, but only the plain Sirius XM shares reported seem OK in the 13-F

If anyone can advise on any of these points, I'd be grateful, and can probably update the look-through sheet in a short while.

One version of my sheet has a second tab where I've entered the 13-F details as best I can:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit#gid=2050477249


scorpioncapital

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1335
    • scorpion capital
Re: Look through portfolio - Google Sheets with live prices
« Reply #21 on: February 24, 2017, 09:42:47 AM »
I see both tickers traded today -
NASDAQ:LSXMA
NASDAQ:LSXMK


Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #22 on: May 17, 2017, 07:35:56 AM »
All the version of the spreadsheet are up-to-date with the latest figures and I've got my head around all the Liberties and their tickers. I've also made some substantial changes to how I handle odd cases, like Sanofi and Bank of America Warrants.

• The one only I can edit:
https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing
• The one only approved users can edit (click the Share tab to request permission from me):
https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s
• The one anyone on the internet can edit without signing in:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

Again, please remember that if you edit the public versions, your data can be seen by anyone on the internet (including the use of File/See Revision History to revive previous versions), so keep your privacy in mind.

If you wish to copy both sheets to your own private Google Sheet, start with the 13-F worksheet tab and hit Copy to... from the menu on the tab, then rename it to remove the added words "Copy of ", the copy the Look-Through worksheet which picks up data from the 13-F worksheet, hence the need to rename it to match the name used in each such formula). Here's the full procedure to copy it:

  • Open Google Drive or http://drive.google.com in the usual way.
  • Go to or create the folder you want then press NEW and select Google Sheet.
  • Your Untitled Spreadsheet will open. You can click in the title to change its name.
  • Open my view only Sheet at https://docs.google.com/spreadsheets/d/1Ok3bOO4z_2Itbta6FguKbuFA1HvcQvzisspPBN6IpZY/edit?usp=sharing
  • Once it has fully loaded, go to the tab marker at the bottom called "13-F 2017-05-15" and click on the light downward arrow on the right of its name
  • Click on the option, "Copy to..."
  • Select your newly created Google Sheet from step 3 above (e.g. you can find it in Recent) and hit OK.
  • It will then say worksheet copied and offer a link "Do you want to open the target Google Sheet?". Say Yes and it will open your Google Sheet.
  • At the foot of the new tab called "Copy of 13-F 2017-05-15" click the arrow then "Rename" and delete the words "Copy of " including the space at the end.
  • If you return to my sheet, go to the tab marker at the bottom called "Look-Through BRK earnings and holdings" and click on the light downward arrow on the right of its name and again click Copy To... and select your new Google Sheet. There's no need to rename this sheet if you don't want to.
  • Close my version and feel free to edit your own in private. If you adjust the blue shaded cells to your preferred currency and holdings, that will cover most use cases.

While going through the 13-F compared to the previous one I noticed a couple of things:
• IBM - was listed in various ownership codes on the previous 13-F but is now all under code 4,11 on a single line
• A couple of other holdings under various codes seem to have moved around including Wells Fargo (WFC).
Could these changes indicate tax-efficient selling and/or buying in certain divisions of Berkshire?

Some items are not in the 13-F but I've made my own entries on my 13-F worksheet in gray background, and I'll summarise them as I've changed how I handle them:

1. BYD (China), as before is included on the assumption it is still held at the same position size.

2. Sanofi ADR. The 2016 Annual Report p19 (Chairman's Letter) mentions the total holdings including both ADR (ticker SNW) and direct shares traded on Paris Euronext. The latter are not reportable on 13-F, but I've put the equivalent number of ADRs (2 ADRs = 1 share of SNY) on the assumption they're still held. I sanity-checked this and the holding is about 1.76% of the market cap, which is about the 1.7% shown in the AR.

3. Bank of America warrants being included in effect as if it were BAC exposure.

Berkshire holds preferred stock with 700 million warrants to purchase BAC common stock at $7.14 until mid 2021.

I'm including these in the look-through effective exposure by using ticker BAC but adjusting the number of shares as if this was a cashless exercise at the current price.

e.g. at $23.99 for BAC, the warrant is worth $23.99 - $7.14 = $16.85
700 million of these amount to $11.795 billion.

That will buy 491,663,193 shares of BAC, so that's the number of shares I put in the look-through portfolio.

In fact, I run this calculation to adjust the effective number of BAC shares ‘live’ to give some sort of indication of the effective exposure and total value of the warrant.

The formula for the effective number of BAC shares I've used is:
No of shares = 700 million x (BACprice - $7.14) / BACprice

Thus, it reflects the current value of the warrants reasonably well, and gives at least some indication of the exposure to BAC - so if you held BAC yourself, you could work out the additional effective exposure via BRK approximately, although you'll be aware that it will increase in 2021 unless the warrants expire worthless.

In reality Berkshire will exchange $5 billion of cash from the operating company to exercise the warrants before they expire, but this will reduce the cash held and reduce the value of the operating component, which I’ve called BH.Own on line 8 of the spreadsheet, by $5 billion while the portfolio BH.Portf will increase in value.

Jurgis

  • Hero Member
  • *****
  • Posts: 3546
    • Porfolio
Re: Look through portfolio - Google Sheets with live prices
« Reply #23 on: May 17, 2017, 08:52:43 AM »
• The one only approved users can edit (click the Share tab to request permission from me):
https://drive.google.com/open?id=10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s
• The one anyone on the internet can edit without signing in:
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

Hey,

Thanks for your work.
I think you messed up "anyone can edit" and "approved users can edit" URLs. Now they both point to the same sheet that requires approval. Looking at your previous posts, this was the URL for "anyone can edit", but no longer...

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #24 on: May 17, 2017, 09:47:02 AM »
This is now the one anyone can edit (with corrected editing rights):
https://docs.google.com/spreadsheets/d/10gMfyZOFCW1-KrY_P8SGRf3pTstspdAGw_DuKSQxO8s/edit?usp=sharing

And this is the one people with permission can edit:
https://docs.google.com/spreadsheets/d/1Um4ENkSz4tppynxqKAMVLrUcuNqn2JaVBBRu4CEVvGQ/edit?usp=sharing

Sorry for the mistake, and thanks, Jurgis for pointing it out.

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #25 on: May 18, 2017, 06:52:38 AM »
I've made a small adjustment to all three sheets.

I've made sure to use live EPS data from GoogleFinance for all shares, but then three showed N/A errors - LBTYK, LILAK, NASDAQ:LILA (new ticker required for LILA to play nice with Google Finance functions) because they don't have EPS data.

I've hard-coded an alternative very rough valuation metric for all three and shaded the cells in columns K:M and Q:AA pink to indicate this. These three stocks don't contribute to the look-through EPS figure, but they're very small holdings anyway (0.16% of portfolio by market value at present) so I'm not concerned.

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #26 on: June 26, 2017, 09:27:04 AM »
There are a few new things to incorporate into the Look-Through Spreadsheet. They mostly fairly immaterial, but I'd still like to get them roughly right.

1. There's the 8-K from STOR June 23rd showing NICO's investment of 18,621,674 shares of the Company’s common stock, which I'll add to the 13-F LATEST worksheet as if it were on the last 13-F.
http://ir.storecapital.com/Cache/389226355.pdf?IID=4553160&FID=389226355&O=3&OSID=9

2. There are two SEC Form 4's from BRK on May 25th regarding various Liberty stock purchases.
https://www.sec.gov/Archives/edgar/data/315090/000120919117035615/xslF345X03/doc4.xml
https://www.sec.gov/Archives/edgar/data/315090/000120919117035614/xslF345X03/doc4.xml
In the first, regarding LSXMK, I think I should use 23,293,786 and ignore the 7,153,027 in various pension plans for which Berkshire and its subsidiaries have no beneficial ownership.
In the second, regarding LSXMA, I think I should use 10,174,586 (a tiny reduction compared to the last 13-F) and ignore 4,308,117 held in pension plans with no beneficial ownership.

The full number of shares including non-beneficial pension plan shares has been recorded (possibly in error) on CNBC's Portfolio Tracker http://www.cnbc.com/berkshire-hathaway-portfolio/ . If the pensions are defined benefit types, excess returns may accrue to BRK over time, but if they're money purchase types, the returns will accrue to the benefit of the employee beneficiaries of the pension. I'll assume the latter.

3. Speaking of the CNBC Portfolio Tracker, they made an estimate of the number of IBM shares held from Buffett's interview around the time of the Annual Meeting where he said about 30% had been sold, bringing their estimated share count to 56,862,612 versus the 13-F's 64,561,955. I haven't made any such adjustment, and its effect is far more material than the matters above, due to the size of the position (0.64% difference in total portfolio value).

While I'm at it, CNBC's doesn't account for the BAC Warrants or the Sanofi or BYD foreign holdings (the latter of which might actually be from the Hong Kong listing, rather than the Shanghai listing I used, although this is probably immaterial). And speaking of foreign holdings...

4. Berkshire now owns or will soon own 40,000,000 shares in Canadian Home Capital (HCG.CA) subject to shareholder approval of the private placement of 24,000,000 shares over and above the 16,000,000 common shares purchases, so I'm including these in the same way as BYD, for example.
http://www.cnbc.com/2017/06/22/home-capital-to-get-1-point-5-billion-loan-from-berkshire-hathaway.html


Nos 1,2 and 4, I will probably include as mentioned above.
No 3 is one I will leave alone until we get the next 13-F in July/August and find out how much IBM is still being held. The Look-Through Portfolio is always going to be out of date, so user beware!

LongTermView

  • Full Member
  • ***
  • Posts: 216
Re: Look through portfolio - Google Sheets with live prices
« Reply #27 on: September 23, 2017, 12:14:50 PM »
Dynamic,

Thanks for putting this together!

Cell S7 shows $4.16 L-Thr EPS holdings per class B share through June 30th.

Cell AH4 shows $1.48 dividends per class B share last fiscal year.

Does that mean the retained earnings from stock holdings are about 2.8 times [$4.16/$1.48] more than the reported dividend earnings?

Thanks!
« Last Edit: September 23, 2017, 12:16:44 PM by LongTermView »

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #28 on: September 25, 2017, 10:30:54 PM »
I don't have computer access until tomorrow and will not look into this on my smartphone in detail, but from my memory, I believe I entered the dividends received by BRK from its shareholdings in the last full year (from the Annual Report) so that I could lower the Earnings of BRK-operating-Company to exclude equity dividends and not double-count that form of income and only count income from operating activities and non equity investments such as treasury bills.

It would then be the case that these dividends would simply be part of the look through Earnings (meaning Earnings of the investee companies, whether distributed or not) which ought over time to accrue to Berkshire in terms of Total Return, meaning dividends plus capital appreciation.

I'll check your question again and may reply in my next post.

Dynamic

  • Full Member
  • ***
  • Posts: 165
Re: Look through portfolio - Google Sheets with live prices
« Reply #29 on: September 25, 2017, 11:15:59 PM »
Dynamic,

Thanks for putting this together!

Cell S7 shows $4.16 L-Thr EPS holdings per class B share through June 30th.

Cell AH4 shows $1.48 dividends per class B share last fiscal year.

Does that mean the retained earnings from stock holdings are about 2.8 times [$4.16/$1.48] more than the reported dividend earnings?

Thanks!

Thanks for your comments, LongTermView

The Earnings of the investee companies includes anything they distribute as dividends.

The retained earnings would be = earnings - dividends - stock buybacks

If we can ignore the buybacks we have a ratio of
(4.16 - 1.48)/1.48
= 2.68 / 1.48
= 1.81

Expanding the top line you can write it as:
(4.16/1.48) - (1.48/1.48)
which simplifies to
(4.16/1.48) - 1
so my answer is always 1 less than your formula.

The original ratio you showed was Total Earnings / Dividends.

So you can say that for every $1.00 of investee dividends received at Berkshire Hathaway in 2016 there's about an additional $1.81 retained which should eventually accrue to Berkshire as a combination of capital appreciation and increased future dividends.

This means that for each $2.81 of total investee earnings, only $1.00 was received as dividends and reached Berkshire's GAAP earnings bottom line in 2016, while the remaining $1.81 was retained by the investees to fund future growth or buybacks which should increase the value of Berkshire's shareholding over the long term.

If you want to get more accurate you could look up the trailing 12 month dividends of the investees and apportion them to the look through number of shares held so that the timings are roughly within the same quarter instead of half a year out.