Putting Together Simple Historic Valuation Database (Avoiding Quagmires)

OK, so before ranting on too much about all the pitfalls and shortcuts of building databases, let's talk through a simple example.

I'm going to talk through a database that I built one summer for the universe of Spanish stocks back in 1996.

I've chosen this as an example, because you mentioned about 'Historic Valuation' Data as being something, which might be useful for you to discuss with analysts.

By way of a preamble, the reason for building this was that ABN Amro, who I was working for back in 1996, had acquired Alfred Berg. It then appointed management from Alfred Berg, on the basis of it being a 'best of breed'  country broker to head up the whole European stockbroking operation. The news ex-Alfred Berg management then went on to appoint other ex-Berg people to manage things and I ended up with an ex-Berg boss from the US sales-side, who wanted a Berg-style database 'pronto'.

One of the features of Alfred Berg research was that it always featured a P&L, Balance Sheet, Cashflow statement AND VALUATION HISTORY, which went back longer than provided by most brokers. Whereas the standard for most brokers was 5 years of history, Alfred Berg always put ten years, which they claimed was particularly appreciated by their clients, especially they claimed US clients.

This is a 'Valuation Perspective' from one of the 60-odd companies that I knocked into the Alfred Berg format (it happens to have 9 not 10 years history because it has 4 forecast years, with 14 columns being about the limit we could squeeze on a page).

As you, I hope can see, it basically  gives you are simple snapshot of 'range' data, based on High, Low and Year End data for share prices. The only investment ratio which is put against all three price points is PE.

Other investment ratios are only shown against Year-End prices, but adding full ranges is just a matter of adding the lines and a formula to the spreadsheet. Space considerations were the only thing that stopped them being included. The raw data is there should another want to calculate them.  

All of the High/Low/Year End data was downloaded from datastream in a relatively simple macro, which downloaded the data across all 60 stocks in the database.THE REAL TRICK WAS TO DOWNLOAD BOTH ADJUSTED AND UNADJUSTED PRICE DATA (ie. for Corporate Actions).

In fact nearly all of this table could have been generated using 'unadjusted' price data and figures as reported at least in terms of ratios. The only line which really necessitated adjusting everything was the seemingly innocuous line '% change' in the year end shares price (ie. share price performance in the 12 month period).

Adjustments were derived from comparing the datastream adjusted and unadjusted prices, with a couple of extra lines in the spreadsheet (which are all hidden in this published form) to allow future adjustment factors to be applied within the on-going database.

The core fundamental data was taken from the following summary P&L, Balance and Cashflow which was prepared for each company:

When I say a that this was a 'relatively' easy database to set up, I should point out that this was around 8-10 weeks of around 90 hour weeks for 60 odd companies, already having a lot of the data, but I was working on my own and in an overseas office without datastream etc..

What happened to all this work?

Well like a lot of databases the work in it was not really valued. A new boss arrived, I left, he I believed deleted it all!

I console myself with the great Jimi Hendrix lyric 'Castles Made of Sand...sink into the sea...eventually'.