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:

Price – adjusted and unadjusted (Berkshire Hathaway)

The BBC business news recently posted a report about Warren Buffet’s Berkshire Hathaway that is a great example of the difference between adjusted and unadjusted prices. See Warren Buffett’s Berkshire Hathaway shares top $200,000, 15 August 2014.

The shares in Berkshire Hathaway surpassed $200,000 because Warren Buffet has never split the company’s class A shares. The class B shares are much cheaper because they have been split.

The screenshot below shows the adjusted price (P), unadjusted price (UP) and adjustment factor for Berkshire Hathaway class A (U:BRKA), class B (U:BRKB) and Google class A (@GOOGL). The Berkshire Hathaway class A shares have never been split so the price and unadjusted price are identical, while the class B shares were subject to a 50 for 1 stock split in January 2010.

(adjusted) price, adjusted price and adjustment factor (click to expand)

(adjusted) price, adjusted price and adjustment factor (click to expand)

The Berkshire Hathaway class A share/stock worth $87,900 on 31 December 2004 is the same as the one worth $188,124 on 31 July 2014. The class B share/stock worth $2,936 on 31 December 2014 is equivalent to 50 class B shares worth $125.43 on on 31 July 2014. The adjustment is always done historically so the class B share worth $125.43 on 31 July 2014 has an adjusted price of $58.72 on 31 December 2014.

  • adjusted price (P) = unadjusted price (UP) * adjustment factor (AF)
  • 58.72 = 2936 * (1 / 50)

The Google class A shares are very slightly different. The Google class A share/stock worth $192.79 on 31 December 2104 was “split” in April 2014 with shareholder getting one Google class C share for every class A share owned. The Google A share worth $579.5498 on 31 July 2014 has an adjusted price of $96.4885 on 31 December 2014. (The adjustment factor is not exactly 0.5 because of a small price difference between Google class A and Google class C stocks.)

The screenshot above is from a Thomson Reuters Datastream request table with 2 requests – the first producing annual values for the end-of-year from 2004 to 2104, and the second producing monthly, end-of-month, values for December 2013 to July 2014.

Both Berkshire Hathaway and Google are examples where the adjustment factor is less than 1 (adjusted price is less than the unadjusted price) but this is not always the case. The Price data in Datastream and Compustat post (researchfinancial blog, October 2104) has an example from American International Group where the adjusted price is much higher due to a consolidation (reverse split) in 2007.

Categories: Business Databases Tags:

Journal ranking – August 2014 update

11 August 2014 3 comments

The journal rankings based on articles published in 2013 are now available. (Update Nov 2014 – The EAJG (ABS) journal quality guide is now not expected until early 2015 – details below)

The best known journal rankings are the Journal Citation Reports (JCR) from Thomson Reuters. These are not available for free – staff and students from University of Manchester have access through our Web of Science (formerly Web of Knowledge) subscription.

Select the Journal Citation Reports link or select Web of Science and then the Journal Citation Reports tab. Once at the JCR home page select the Journals by Rank tab.

The primary variable calculated by JCR is the Journal Impact Factor (JIF). The new interface also offers a journal connection visualization.

Web of Science - JCR 2013 (click to enlarge)

Web of Science – JCR 2013 (click to enlarge)

The above screenshot is for Incites Journal Citation Reports – year 2013; category business, finance; and edition SSCI;

For more information – Journal Citation Reports Data Release 2014  (JCR 2013 data)

SJR IconThe SJR SCImago Journal and Country Rank – Journal Rankingbased on Scopus data, now have 2013 as their latest year.

The SJR indicator, developed by SCImago, is not as well know as the JIF factor from the Journal Citation ReportsHowever the metrics are freely available – they are based on Scopus rather than ISI Web of Science so more business and management journals are covered, and more business and management subject categories.

The screenshot below shows results for the subject area Business, Management and Accounting. There is  a category Marketing in this subject area (see Journal ranking – marketing posted August 2012). The subject category Finance is in the separate subject area Economics, Econometrics and Finance.

SJR journal ranking – subject area Business, Management and  Accounting (click to expand)

SJR journal ranking – subject area Business, Management and Accounting (click to expand)

The CWTS Journal Indicators are also based on Scopus data, and again 2013 is now the latest year available.

Google Scholar Metrics currently covers articles published between 2009 and 2013 (both inclusive) and are based on citations from all articles that were indexed in Google Scholar in June 2014. (details available via the learn more link)

Google Scholar does not appear to make historical metrics available.

There is an example screenshot in the Journal Ranking – August 2013 update post.

The EAJG (ABS) journal quality guide – ABS (the Association of Business Schools) Academic Journal Guide 2015  is now located at  [Updated 28 Feb 2015]

The latest 2010 version 4 of the  ABS Journal Quality Guide is available form the archive.  See also  Journal ranking – August 2013 update

Thanks to the Academic Trends & Innovation blog for the latest JCR released post – a reminder that the Journal Citation Reports (2014 edition) is now available.

Beta values from Datastream

9 August 2014 1 comment

There is a datatype BETA available in Thomson Reuters Datastream  but often this is not what researchers are looking for when they are looking for “company betas”.

The Datastream datatype BETA is static – only the current value is available – and it is also adjusts the raw beta value to make it a forecast beta.

There is a formula that you can use to get a time series of historic beta values: REGB#(LN#(X/LAG#(X,1M)),LN#(Y/LAG#(Y,1M)),60M). This uses the regression beta function with three parameters.

  1. The return of the market over one month – LN#(X/LAG#(X,1M)
  2. The return of the equity over one month – LN#(Y/LAG#(Y,1M)
  3. The time period – number of observations in the regression – 60M

The screenshot below shows the historic beta values for the June 2004 FTSE 100 constituents yearly from 31 December 2000 to 31 December 2013. The market return is the FTSE All Share – LN#(FTALLSH/LAG#(FTALLSH,1M). The equity return is LN#(X/LAG#(X,1M) – X will be each of the list constituents in turn, and the time period is 60 months.

Datastream - historical beta values for ftse 100 (click to expand)

Datastream – historical beta values for ftse 100 (click to expand)

The results in the screenshot come from two entries in a Datastream request table

  • Static request for constituent list LFTSE1000604 and datatypes NAME,ESTAT,TIME,WC09802,BETA
  • Time series request for LFTSE1000604 and the beta formula REGB#(LN#(FTALLSH/LAG#(FTALLSH,1M)),LN#(X/LAG#(X,1M)),60M)

The results show that care is required for dead companies. The formula continues to generate results long after the last valid price data (shown by TIME) so for dead companies the beta returned is always zero once the company has been dead 5 years (60 months).

The Worldscope beta datatype (WC09802) just gives the latest value (see Worldscope manual for details). If you use this in a time series request then you just get the same value repeated. In contrast the static datatype BETA gives an error if used in a time series request.

You can use the local index datatype (LI) rather than explicitly choosing the index in the beta formula.

  • REGB#(LN#(X(LI)/LAG#(X(LI),1M)),LN#(X/LAG#(X,1M)),60M)

This will give the same values as displayed above as X(LI) is FTALLSH for all these LSE listed shares.

Related Posts

Beta values on Thomson (April 2014)

Beta values for companies (March 2010)

Beta and choosing the market (researchfinancial blog,  May 2014) – includes a useful Excel sheet for constructing a Datastream beta formula

Categories: company information Tags: