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

Dynamic

  • Sr. Member
  • ****
  • Posts: 435
Re: Look through portfolio - Google Sheets with live prices
« Reply #60 on: January 12, 2019, 04:45:16 AM »
It isn't on the spreadsheet.  You'd need to calculate the portfolio value with the closing prices on the last trading day of that quarter which is not on this sheet - it's just the best estimate of the current live portfolio not the one at previous times


John Hjorth

  • Hero Member
  • *****
  • Posts: 2368
Re: Look through portfolio - Google Sheets with live prices
« Reply #61 on: February 11, 2019, 07:18:33 AM »
Dynamic,

The last couple of days I've been tinkering around - on and off - with the bank positions [EOP 2018Q3] Berkshire has in its tummy, with the aim to gain an overview of all those positions.

I use your file as "reconciliation anchor" [because I know you have put a lot of work into that file] - thank you very much for sharing your work!

In the "View only" file, tab "Look-Trough Summary", row 19, you have data [i.e. company name, shares owned by Berkshire] for KO, but the ticker "JPM". The actual outcome is that KO shares are double recorded [row 14 & 19], while JPM shares are missing. [It's a bit of a mystery to me how this has happened, because you're correctly using relative cell references [to the contrary of partially or fully absolute cell references].] -At least it's fixable quick! [ : - ) ]

-Again, thank you very much for sharing your work!
« Last Edit: February 11, 2019, 08:10:29 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 #62 on: February 11, 2019, 08:38:30 AM »
Thanks for spotting that, John.

All fixed now.

I probably didn't notice that I'd omitted to copy the formulae from the cell above or below when I inserted it on the Look Through summary sheet as I usually look at the front sheet instead and not the Summary.

Something else happened the other day with Coca Cola (KO) (on my private copy at least), where GoogleFinance was throwing an error and reporting its currency as ARS (Argentine Sol) but that just got sorted out.

So, as always, be careful before using this sheet to inform investment decisions. It's possible that errors will be present from me or from GoogleFinance.

I'm glad people are finding the sheet useful and please feel free to point out any problems you spot.

John Hjorth

  • Hero Member
  • *****
  • Posts: 2368
Re: Look through portfolio - Google Sheets with live prices
« Reply #63 on: February 11, 2019, 03:54:19 PM »
Dynamic,

Attached is my work in progress file, related to the US banks that Berkshire has positions in. Please note, that the filename contains "Draft" [ : - ) ]

On the "Overview" tab I have added two temporary columns, containing the reconciliations to your file. Pink cells in variance column are those of interest.

The two other tabs contains data from SEC, that have been processed this way:
  • Brutal cut from the SEC website,
  • From the clipboard dirty paste into Excel using special UNIcode paste [after which you have a frigging mess in Excel],
  • Formatting the data as a table, to create the possibility to set filters [which makes the processing much easier and creates a better view for details to focus on, all non-relevant data can be hidden],
  • Tweaking the share counts text data to numbers data, that Excel can calculate on [only share figures - I do not use the value data], &
  • Adding conditional sums below the tables, linked to from the "Overview" tab [formula "SUM.IF"].
- - - o 0 o - - -

Here are my conclusions on comparison with your file :

For WFC, AXP, JPM, PNC & TRV the difference consist a separate line in the NEAM 13-F/HR, that are not included in your file. Those variances are immaterial, hvorever I ask if there is a specific reason to that those rows [marked pink in NEAM 13-F/HR tab] are omitted in your file?

For USB it's about two rows - a row in the BRK 13-F/HR tab and a row in the NEAM 13-F/HR tab [also marked pink].

I haven't been able to find an explanation about the variance for BK.

- - - o 0 o - - -

I hope you will take the time to give those particular data in pink cells in my file a critical review, for the purpose of getting mutual better precision by collaboration - thank you in advance - and no hurry! [ : - ) ]
« Last Edit: February 12, 2019, 04:08:10 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 #64 on: February 12, 2019, 04:23:46 AM »
Hi John,

If you want to compare figures to mine, I do include all lines I've extracted from each 13-F in columns in my tabs (usually 6th and 7th tab) called BRK ONLY 13-F LATEST and NEAM 13-F 01 02 items.

The first one I found out where the discrepancy arose is Wells Fargo, and I've stopped there for now. I imagine the others could be similar.

There appears to be an error on the cover page primary_doc.html of just the November 13-F filing for New England Asset Management, which I hadn't noticed until now. I believe it usually lists under List of Other Included Managers: 1 and 2, but in this case appears to have put:
1 BERKSHIRE HATHAWAY INC and
1 GENERAL RE CORP  which should be number 2 as per previous 13-F filings I've seen from them, I would think, and as user gfp ('globalfinancepartners' at the time) has explained to me in previous posts long ago on this thread.

On the information table of the 13-F it is ONLY where the MANAGER column states 01 02 that you should include this as a Berkshire Hathaway holding.

The 39,467 shares of WFC discrepancy in the Nov 2018 13-F do not show 01 02 in that column, so you should not have included them in your total held at New England Asset Management if my understanding is correct.

As you're pasting the data from the 13-F, there are a couple of ways to process the data. You could Conditional Format cells in that column if they contain the text "01 02" to be a different colour or background colour. Fortunately, there's only ever one line of "01 02" Berkshire holdings for any stock on the NEAM sheet (unlike the Berkshire 13-F which has a variety of different managers and combinations of managers within subsidiaries)

Or you could add a column of your own for BRK holdings or do a conditional SUM such as SUMIF or SUMIFS.

For example, in a new column, say column M, you could put a formula in cell M4, then autofill down the rows of this form:

=IF($I4="01 02",$E4,"")

When I just did that, none of the rows you highlighted pink gave a number, so I think that's the source of the discrepancies you have.

You could also filter the table to show only "01 02" in the Manager column, and it should be fine and agree with mine. Certainly none of the pink rows show up when I filter that way.

When I input the 13-F holdings onto my sheet, so that you can easily compare mine and yours to check for errors in either, I put them in two worksheets (6th and 7th tabs) called BRK 13-F LATEST and NEAM 01 02 items.
In there I put the various rows applicable to Berkshire into columns and add up the numbers to get the total holding.

For historical data and to assist in showing quarterly changes I also keep old copies of the 13-F filing sheets.

The 5th tab is COMBINED HOLDINGS and includes both BRK and NEAM figures from their respective sheets and any adjustments for other holdings or deductions for Pension Fund holdings, along with a link or descriptive text to explain the adjustments or the source of that information. With Pension Fund holdings I usually keep the sum of all the pension fund holdings as a formula so that I can easily change such holdings in the formula bar if certain funds change their holdings and compare one 13-G filing to another to spot any adjustments.

BTW, I agree that pasting from the HTML page into Excel or other spreadsheets can be terrible. It's made worse for many non-English speaking countries who use . as a thousands separator and , as a decimal separator, so your locale defaults mess with it even more! Fortunately, there are no US format dates to worry about!

Maybe I'll one day implement something to parse the XML format (which I think is JSON data) and automate as much as possible, though I'm sure there may still be problems such as a mis-spelling of MONSANTO as MOSANTO a few quarters ago that will need manual intervention at times, though the CUSIP number might allow automation to work better.

Perhaps USB and BK will still have discrepancies after you've removed non "01 02" figures from NEAM, and if so, let me know and I'll see if I can spot the reasons.

Thanks for your analysis and problem spotting.

John Hjorth

  • Hero Member
  • *****
  • Posts: 2368
Re: Look through portfolio - Google Sheets with live prices
« Reply #65 on: February 12, 2019, 04:44:53 AM »
Hi Dynamic,

Thank you very much for taking the time to explain this in depth and detail to an ignorant like me. Christ, this is complicated! Suddenly I really & finally realize how complicated it must be for you to maintain your file.

I'll give my own file yet another spin, based on your explanations & guidance, and then post again. I'm pretty sure it'll weed out the vast majority of the variances, if not all.

-Thank you!
« Last Edit: February 12, 2019, 05:22:10 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 #66 on: February 12, 2019, 05:12:02 AM »
Yeah, it took a bit of learning and guesswork and comparison to other sources to reverse engineer what I'd done wrong before I internalised how these filings are laid out. Good luck with your adjustments, John.

And I cannot thank gfp enough for the guidance, particularly early on in the process.

Eventually, after noticing some 13-D or D/A and 13-G or G/A filings mentioned the pension funds, I decided to trawl back through those sorts of filings for a few years and it made the beneficial ownership to Berkshire shareholders pretty much match up with the figures shown in the annual reports, so I was fairly sure those adjustments were correct.

I think there may still be small discrepancies. For example, Berkshire Hathaway Energy holds the BYD stake and is (from my memory) 90% owned by Berkshire. It may be that we should account for the 10% minority stake in BHE when working out our true look-through ownership. I'd be interested in hearing other people's views on that, but I think the spreadsheet is already better than most sources that ignore NEAM and pensions for estimating our Look Through ownership.

John Hjorth

  • Hero Member
  • *****
  • Posts: 2368
Re: Look through portfolio - Google Sheets with live prices
« Reply #67 on: February 12, 2019, 09:31:25 AM »
... , but I think the spreadsheet is already better than most sources that ignore NEAM and pensions for estimating our Look Through ownership.

It's not only the best one, but also the only one [to my knowledge], and thereby also both moaty and anti-goaty. [Among Danes, if you're tinkering with something, and you end up far from what you aimed to achieve [called failure, in short], it's in Danish language - among other things - called "There has gone goat into it."].
”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 #68 on: February 13, 2019, 01:00:05 AM »
Thank you, and by extension, thanks go to all those who've contributed in this thread. I'm glad it's useful to people who want to look at it truly from a look-through ownership perspective. It actually has a few advantages when you get a 10-Q before the 13-F too, as Berkshire reports the value of its largest positions at quarter end

Still, if it can be improved a little, by taking account of a few small factors like BHE's minority ownership's look-through proportion of BYD, I'd be glad to improve it. On my COMBINED HOLDINGS sheet that would simply require a formula in cell I40 such as =0.9*225000000 (or just the value 202500000), though I prefer the first option as it explains why it's less than shown in the 2016 Annual Report even if the comment gets deleted.

gfp

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 1653
Re: Look through portfolio - Google Sheets with live prices
« Reply #69 on: February 13, 2019, 07:44:01 AM »
Thanks for the spreadsheet dynamic.  Looking forward to seeing Berkshire's 13F at the end of the week - I guess Friday evening?