Unlock the Editor’s Digest for free
Roula Khalaf, Editor of the FT, selects her favourite stories in this weekly newsletter.
With apologies to Stanley Kubrick.
OK we really, really wanted to make a pretty racing bar chart that showed the changing market cap of the FTSE 100’s largest companies, so went to LSEG with this — we think — not unreasonable request.
We encountered the following problems (suggested musical accompaniment):
— LSEG do not have this data going back before 1996.
— LSEG could not pull data that reflected the entrance of new members to the FTSE 100, or stop pulling market cap data once those stocks were delisted.
— Something was definitely wrong with the data because at no point did Shell become the most valuable company on the FTSE 100, even though it definitely was. We asked why this was the case, and LSEG couldn’t explain it. It looks like it was a case of not combining the share classes, something we couldn’t easily fix while WFH at nearly 7pm on a Wednesday.
— LSEG provided data that reset every five years to include new entrants, but this meant the chart we made reset every five years, which was visually horrifying.
— Stocks that were no longer listed said “DEAD” in their titles.
— These DEAD, delisted members lingered, corpse-like, until they either fell out of the top 10 or a five-year reset occurred.
— Each bar was individually coloured but would change colour after each five-year reset.
— The data, to repeat, seemed to be inaccurate.
— Time was rapidly running out to get this article out on the FTSE 100’s 40th birthday.Reader, we gave up.
Well — three days, five Bloomberg help chats, and several emotional breakdowns later — we kinda did it (important caveats below):
The important caveats
This is basically two data sets stuck together: from 1996-2005 it’s LSEG’s data, augmented in some key areas by us, and from July 2005 onwards it’s pure, uncut Bloomberg.
First of all, we want to thank the LSEG and Bloomberg help staff who assisted us with varying degrees of success.
Then, we would like to say. with great feeling:
It’s almost unbearable to think how much easier this might all have been if Shell has just been structured like a normal bloody company.
Apparently, back in ancient times (pre-2005), ancient people used to calculate Shell’s market cap by taking the market caps of Royal Dutch Petroleum and Shell Transport & Trading, and adding them together. Wild, right? Anyway nowadays Bloomberg still has historic market cap data for ST&T, but zilch pre-2000 for the Royal Dutch part.
So what did we do? A massive fudge, obviously. Historically, the Royal Dutch/Shell company was 40 per cent Shell, 60 per cent Royal Dutch, so we . . . just took the ST&T number, multiplied it by 1.5, and then added the two numbers together. Deal with it.
BP is also . . . weird. Looking at the long-run market cap and share price data for the group on Bloomberg reveals a very weird drop in the late-1990s. We assume the data is borked.
With no clean data available for Shell or BP, one might begin to have a nagging thought: is any of this data right? And our answer is: we have no idea. Still, we hope you had fun, because we didn’t.
Further reading
— Signs of a nervous breakdown (WebMD)
