Excel Industry Analysis

Excel Industry Analysis

I considered that if I could look at the UK share price trends of all UK companies over the past 10 years or so, I could then group these into industry segments and get an idea of which segments are currently moving in positive ways–and which ones are looking like they are dying off. This kind of data was not readily available in a nice .xls file for the taking; you can buy some form of historical share price closing data for about £200. But Yahoo and Google provide the data for free on a ‘one-stock-at-a-time’ basis… which is not much use here. What I ended up doing was writing a kind of scraper using VBA (if anyone can help me write in C# get in touch!) and pulling out monthly closing price data on all current FTSE100 companies for the past five years or so. I then grouped by industry and these are the results (if you want to learn how I did this, check out THIS ARTICLE):

FTSE100 Industries: Combined Adjusted Closing Share Price (GBP)

chart_mtx1 chart_mtx2*Note: Total FTSE100 chart shows the total trend and 2yr change for all companies currently in the FTSE100 (as at Dec 2014) These charts show the adjusted closing price trend and I have also included a 2yr change percentage to hopefully provide a better means of benchmarking since most are evidently in positive growth (as is the Total FTSE100 chart). Some quick observations:

  • Mining and Oil & Gas both show a 2yr negative change suggesting some kind of slump and movement of capital away from these industries
  • Transport air, and Retail (Electronics, Hospitality, Clothing) have the biggest 2yr change which could tell me these are industries on the up and that may be growth areas for the near future (next 5yrs)
  • Supermarket, Mining, Product Testing, and Oil & Gas all show negative 2yr growth suggesting less demand on these industries (see Caveats/Weaknesses below)
  • The overall trend has been steadily positive (since 2009)


  • This is FTSE100 data–so only includes the giants of industry. Therefore, although supermarkets looks bad this is probably because Aldi and Lidl are sinking Tesco so it would be better to get data for all listed stocks (which I intend to attempt in the future, by the way)
  • Since there are only 100 companies some of these industries (e.g. Generator hire) comprise one company (Aggreko, in this case) which is misleading. Perhaps I could include a small area under each chart listing the companies under the category if I wanted. But, if I can repeat for all listed companies that will wipe this concern out.
  • It may be more beneficial to consider industry segmentation by demand category–so for example if an engineering company services the oil industry, it would be classed as oil instead of engineering. This would yield a better gauge on where the demand is trending.

Thoughts that Sparked this…

The company I work for does a lot of business with Talisman Energy, and the remains of Talisman (it sold half it’s UK assets to Sinopec in 2012) were bought by Repsol on Monday for $8.3B. This prompted a quick search which lead me to a BBC article titled ‘Falling oil prices threaten to transform the industry‘). The article explores how with the steady slide in crude prices this year has led many oil companies to look for ways to save money–something they have not had to try very hard at for the past 20 years or more. Some are considering mergers or more joint-venture activities. Others are going out of business and the landscape of the upstream industry may be set to shift markedly in the coming months. This does not seem untrue in Aberdeen. Perhaps it is a chronic feature of mineral-based E&P industries due to their boom-bust nature (leaving ghost towns in their wake, etc.) but since moving up to Aberdeen I keep hearing stories about the oil running out, supermajors downsizing North Sea operations, and how it is getting prohibitively too expensive to drill and produce hydrocarbons in the North Sea. Indeed, Chevron axed about 1/3 of their 800 Aberdeen staff a couple of months ago, I hear that redundancy rounds (albeit less dramatic) are also going around Shell and BP. I wonder if the North East of Scotland is going to also become a sort of extended ghost town itself in my generation?

Post-Oil Industries

That was one prominent scare-byte parried by the Scottish referendum (Sep 2014) ‘No’ campaigners: ‘the oil is running out’. I gave no credence to that argument since Scotland has a strong history of ingenuity and, although it has helped, we do not require oil to thrive. But, you have to wonder what industries would in the wake of a depleted oil supply? On a more personal note, having moved (earlier in 2014) from a utilities company that was very much in the ‘growth’ phase of its parent industry life-cycle, and having felt a sense of the urgency that entailed (as well as internal restructuring and promotion activities), I have been ruminating on how I might better understand which industries will be thriving in the future. I am thinking it would be better to get on that bandwagon before the oil runs out than after–even if we are talking about a 20 year horizon.

One thought on “Excel Industry Analysis”

Share your thoughts below :)