Author Archive

Why are there so many business databases?

21 November 2014 Leave a comment

Library Research Plus

Browse The University of Manchester Library website and you will find a large number of business databases. Researchers have to chose which of these is best for their research and this can be influenced by various factors. To guide this decision it is important to remember the factors that lead to academic libraries having many business databases.

Commercial products

Business databases roundabout There are many specialist financial and business databases available to researchers at The University of Manchester

The best known business databases are commercial products: for example, Bloomberg Professional, Datastream (Thomson Reuters), and Capital IQ (Standard & Poor’s) .  They are available to universities for non-commercial use but their main market is finance professionals. These systems are similar but not equivalent. The companies developing these systems are constantly trying to improve them to maintain or increase their market share, and often this includes providing data that is not available from competitors.


View original post 668 more words


LSPD on WRDS (three tips)

London Share Price Database (LSPD) is a unique, comprehensive database of UK stock returns covering over 9,000 UK shares available to University of Manchester researchers through our WRDS (Wharton Research Data Services) subscription.

LSPD is firmly aimed at researchers: it only covers UK shares and does not have the variety of data available in Thomson Reuters Datastream or Bloomberg. It concentrates on its historic coverage (from 1955 to date) and the quality of its returns data.  The following three items illustrate LSPD strengths.

1) Historic company names

Finding companies by their previous names can be a headache for researchers, especially if a company is dead or inactive. LSPD uses its own unique company number (G1 in the excellent manual) so that it can trace historic names. You can search by name or company number using the WRDS-LSPD company lookup facility. The screenshot below shows the names for Granada (LSPD_STOCK_ID (G1) is 2222) from 1959 onwards.

WRDS-LSPD lookup for Granada

WRDS-LSPD lookup for Granada

It can be useful to first lookup by name, e.g. “Granada”, and then by the LSPD_STOCK_ID (G1) number where there has been a major name change, e.g. “Granada plc” to “ITV plc”.

Previous posts that demonstrate the problems name changes can cause

An alternative approach to find historical company names is to download the “st_names” dataset and use the find/selection options in your chosen analysis software (Excel/SPSS/Stata/SAS).

2) Main or AIM market

UK companies listed on the London Stock Exchange can be on either the Main Market or the AIM (Alternative Investment Market). LSPD can be used to identify AIM listed companies, including dead/inactive ones.

You can used the LSPD “Stock Annual query” – search the entire database with condition G16 (SEDOL Group) is 95 (Alternative Investment Market).

A recent test gave 2667 AIM listed companies, of which 1092 were active. Of the inactive/dead AIM companies the most popular reasons (from G10 Type of Death) were:

  • G10 = 5 – Acquisition/takeover/merger (approx 600 companies)
  • G10 = 14 – Quotation cancelled for reason unknown. No dealings under rule 163(2) or (3) (approx 400)
  • G10 = 20 – In Administration/Administrative receivership (approx 200)

3) UK monthly risk free rate

Finally LSPD Index 3 is Treasury Bill Rate (90 Day) so you can also get a UK risk free rate from LSPD.

One advantage of the LSPD Index Monthly query is that you get both the annual yield (I10) and the monthly log return (R22) (Screenshot below).


As previously, it is best to download the LSPD manual for the definitions of the these variables.

Categories: company information Tags: ,

Oil Price – historical data (update)

16 October 2014 1 comment

The oil price is often in the financial news as a key economic indicator so it is not surprising that many researchers are interested in historical oil price data.

Crude oil is traded worldwide but there is not a single oil price. The Financial Times world markets summary includes two crude oil benchmarks: WTI (West Texas Intermediate) and Brent. Historic data is available for these from several sources. One of the most convenient is Quandl (see Quandl – a search engine for time-series datasets for more information)

The screenshot below shows a oil price chart for “Brent Crude Oil Spot Price, Sullomn Voe, Scotland” from Quandl’s crude oil prices.

Brent crude oil price from  Quandl

Brent crude oil price from Quandl

Historical oil prices are also available from several databases:

Bloomberg Professional has the greatest detail of commodity trading and related news. Most of the trading of oil on financial markets deals with oil future (paying now for oil to be delivered at a specific date in the future) rather than the spot (current) price. Benchmarks include Brent Crude (EUCRBRDT) and WTI (USCRWTIC)

Datastream from Thomson Reuters includes numerous historical time series of oil prices among its commodity category. Benchmarks from Thomson Reuters include Brent (OILBRDT) and WTI (OILWTIN) crude oil.

Global Financial Database (GFD) also have these benchmark oil prices. (GFD) has historical prices for Brent and WTI crude oil (select GFDatabase and series type Commodity Prices). GFD has data for Brent going back to 1957 and WTI going back to 1860.

passport-oil-pricePassport (formerly GMID) from Euromonitor also has crude oil prices for Brent and WTI Cushing. However, these are only annual prices – if you want monthly or daily prices then the databases above are better.

In many circumstances it is acceptable to use any of these sources for historical crude oil prices but your text or references should make your choice clear. If you are getting other research data from Bloomberg or Datastream it makes sense to get your oil prices from the same source.

Oil Price chart from DatastreamThe previous oil price post (January 2011) included this oil price chart showing that Brent and WTI have been almost identical. On closer inspection the two series chosen are from different sources: OILBRNP (Brent) is from ICIS Pricing while OILWTIN (WTI) is from Thomson Reuters themselves.

(There are sometimes small differences in data from alternative sources – which is why Thomson Reuters make these available to their Datastream customers. For researchers the key questions are usually the time-span and frequency of historical data, and whether our Datastream subscription includes access.)

This previous post also prompted comments about getting historical data for TAPIS (Malaysian) crude. This is available on Bloomberg (APCRTAPI) from December 1986 and Datastream (OILTPMY) from January 1991. See

Historical Index Constituents (e.g. S&P 500)

6 October 2014 4 comments

Stock market indices (e.g. FTSE 100, S&P 500, Nikkei 225, DAX, Shanghai SE, BSE Sensex, Bovespa) have constituents that change over time. For example, the FTSE 100 is the largest companies (by market capitalisation) on the London Main market and is regularly reviewed. It changes as some companies grow faster than others, through merger and acquisition activity, and also when large companies list.

Current constituents are usually readily available on the web, but getting the historical constituents often requires a specialist database.

Using Datastream you need a little knowledge of the mnemonics for constituent lists. For example:

  • LS&PCOMP is the constituent list for the current S&P 500
  • LS&PCOMP0989 is the oldest historical list (Sept 1989 – 0989) and
  • LS&PCOMP0914 most recent month (Sept 2014 – 0914).

Searching with Datastream Navigator you only get the oldest and newest of these monthly constituent lists. You create the others by editing these.

Datastream Navigator - S&P 500 constituent lists

Datastream Navigator – S&P 500 constituent lists

Other historical constituent lists follow the same pattern  e.g. LFTSE100MMYY for the FTSE 100 constituents. These historical constituent lists can be tricky to find in the Datastream Navigator – the results above are from a criteria search with “DS Mnemonic starts with LS&PCOMP”.

Once you know the oldest and newest constituent list available you can edit these to get the ones you want. The screenshot below shows a Datastream request table with a static request for the end of year S&P 500 constituents (LS&PCOMP12YY).

DS request table for names and market value of S&P 500 at year end

DS request table for names and market value of S&P 500 at year end

For another detailed example see Historical FTSE100 Index Constituents on Datastream (July 2012)

Bloomberg Professional also gives easy access to key indices through functions WEI (World Equity Indices) and EMEQ (Emerging markets equity indices).

Bloomberg - World Equity Indices

Bloomberg – World Equity Indices screen

Bloomberg has a member function (MEMB) that can be used to give a list of index constituents. Historical constituents (typically from 2001) are available by using the “Edit” option to change the date. Bloomberg also has a changes (CHNG) function that will give the index changes, including changes in weightings, between chosen dates.

UPDATE 12/04/2016: for more on this, read Historical Index Constituents in Bloomberg.

WRDS-iconWRDS (Wharton Research Data Services) provides access to S&P 500  index constituents.

Compustat – North America – Index Constituents on WRDS – use GVKEYX 000003 or TIC  I0003. Historical data is available from March 1964. See Constituent list in Compustat (posted September 2010 Databaser blog) for more details.
(This is also mentioned in Linking Compustat and Mergent FISD on WRDS – posted June 2014)

A Few WRDS about the S&P 500 (September 2014 on the Lippincott Library Datapoints blog) is a useful point about the S&P 500 that includes details of getting the historical constituents.

No historical constituents

Please note that historical constituents are not always available for all indices. Following a recent comment, we have found that the Datastream total market indices, e.g. TOTMKUK for the United Kingdom, only offer the current constituents (LTOTMKUK).

Profit warnings (business news)

30 September 2014 1 comment

Tesco features heavily in the current business news following a recent profit warning. The library’s business databases are very valuable if you want to research this in more detail.

Dow Jones Factiva is a business news database that covers a wide range of newspapers and trade journals. The news articles are indexed to make it easier to locate the information you need.

The screenshot below shows results for the following search criteria:

  • Company – Tesco PLC
  • Subject – Profit Warnings
  • Date – In the last 3 months

Note that you can scroll down the left hand column to get useful summaries of the results.

Factiva - Tesco news (click to expand)

Factiva – Tesco news (click to expand)

This search does not give the Tesco profit warning. It gives all the news articles about a profit warning that also mention Tesco PLC within the article. There have been several articles about the profit warning in the Financial Times, The Guardian, The Telegraph, The Independent, …

Selecting an specific source, for example Financial Times, is a useful way of restricting your search results to a more manageable number from the original 471 (193 eliminating duplicates). In this example it is then easy to identify that Tesco PLC issued its profit warning on Friday 29 August 2014.

We now expect trading profit for 2014/15 to be in the range of £2.4bn to £2.5bn. Trading profit for the six months ending 23 August 2014 is expected to be in the region of £1.1bn. (Tesco PLC, 2014)

This “Trading Statement” – note that Tesco does not issue a statement that uses the term “profit warning” – is available on official RNS news from the London Stock Exchange (see LSE – News and Events – RNS). It is also available from the  PI Navigator database.

PI Navigator is a specialist database covering global company filings (e.g. annual reports, IPO propectuses, news announcements, mergers and acquistions)

The following screenshot shows results for the search criteria:

  • Company – Tesco PLC
  • Classification – Trading and Operating Updates
  • Issue Date – After 30 June 2104 (3 months)
PI Navigator - Tesco trading updates (click to expand)

PI Navigator – Tesco trading updates (click to expand)

Note that in PI Navigator it is not possible to search for profit warnings – you have to search for all the market sensitive trading updates that have been released by the company. On the plus side it is quick and easy to find the Tesco PLC Trading Statement of 29 August 2014, and the later trading update about profits for the six months to August 2014 being overstated by an estimates £250m.


Tesco PLC (2014) “Tesco PLC – TSCO Trading Statement Released 07:00 29-Aug-2014”, Available at, (Accessed 30 September 2014).

Interested in finance? Test drive a database

23 September 2014 Leave a comment

Your interest in finance and financial markets could start with your university course, your career aspirations, intellectual curiosity, or a combination of all three. Whatever the reason, take one of the specialist financial databases for a quick test drive and get a glimpse of the world from the perspective of a finance professional.

Current students, and staff, at the University of Manchester have access to Bloomberg Professional from Bloomberg, Thomson from Thomson Reuters, and Capital IQ from Standard & Poor’s.

Bloomberg Professional is a great database for getting the professional experience – you just have to think of a large, quoted company, find it and start to browse around. Bloomberg is only available on specific PCs so you have to go to the library.

Once you have Bloomberg running, type the company name in the command line, select the auto-matched series, and choose DES (description) for your command. The screenshot below shows the result for Samsung.

Bloomberg overview description of Samsung (click to enlarge)

Bloomberg overview description of Samsung (click to enlarge)

If you are unsure where to go on your test drive there are various Bloomberg posts that could give you inspiration.

Thomson from Thomson Reuters is not quite as eye-catching as Bloomberg but it does have a web interface, so you can use it from any PC where you have Internet Explorer as your browser and can run the VPN software to access university resources.

Use the search box to find a large, quoted company by name and you get a standard overview report. Explore the different sections of the report and then the different tabs. The following screenshot is for Amazon.


Thomson ONE company overview of Amazon

There are various Thomson posts to give you tips. In addition, you can also use one of our library guides to help get started.

Capital IQ, like Thomson, has a web interface – you need VPN and a special username and password. Getting started is the same – search for a large company by name and start with the default overview report. With Capital IQ the large list of options to explore appears as a list of headings on the left.


Capital IQ company overview of Wal-Mart (click to expand)

This screenshot is for Wal-Mart Stores (who own the Asda in the UK).

When exploring any of these databases it is best to start with a large company as more information will be available, in general large, quoted US companies as the most studied by professionals are the best.

Finding Scottish companies with FAME

15 September 2014 Leave a comment

With the referendum on Scottish independence this Thursday (19 September 2014) there is much news and debate on the potential impact on businesses of a yes vote (BBC News, 2014a, 2014b). To research further you could use the Fame database to identify Scottish companies.

FAME, from Bureau van Dijk, covers UK public and private companies and has a very good search interface for selecting companies. We can select companies by location and one of the options for giving a location is country.

We select “Scotland” as the country. The country search page clarifies that this means companies with a registered office address or a primary trading address in Scotland. (These defaults can be changed – for example if we only wanted companies with their registered address in Scotland.)

From a Fame search the largest Scottish companies by operating revenue (turnover) are given in the screenshot below.


FAME uses a standard set of variables and orders then by operating revenue as its default view of a result list. This can be changed by adding extra variables as required, and by selecting the arrows next to the variable names to reorder. In the screenshot two extra variables have been added: Listed/Unlisted/Delisted and BvD independence indicator.

The Listed/Unlisted variable shows the companies that are Listed (Public) companies – that is have shares/stocks traded on a stock exchange – and those that are Unlisted (Private) companies. The largest private Scottish company is Arnold Clark Automobiles Limited – eighth largest by operating revenue. One of the strengths of FAME is that it includes both public and private companies.

This search also used “Ownership – BvD independence indicator” to refine the search and remove subsidiaries from the results list. An ownership search criteria was added to the Scottish location criteria. This was that companies had to have a BvD independence indicator of A or B, or be listed companies.


BBC News (2014a) “Scottish independence: Price warning letter due as business row intensifies”, Available at (Accessed 15 September 2014)

BBC News (2014b) “Scottish independence: Business row marks weekend campaign”, Available at (Accessed 14 September 2014)

Categories: company information Tags: