After looking at all currently listed UK companies (part 1) we can now look at producing a list that includes delisted/dead companies. (Note that delisted/dead companies may still exist but their shares are no longer actively traded.)
Thomson Reuters Datastream has worldwide coverage and includes a number of pre-defined constituent lists that can be used as a starting point.
However, as with the list of current UK companies from the London Stock Exchange (LSE), there are a number of filters that we might want to apply.
205 securities are not listed on the LSE – most of these are on ICAP Sec & Der.E (formerly UK Plus) exchange, some have no value for EXNAME. 169 securities are not the major security of the company. 86 securities are not the primary listing of the security.
- All securities on WSCOPEUK on 28 May 2013 – No of securities 5015
- Securities on LSE, that are major and primary (EXDSCD = ‘LN’ and MAJOR = ‘Y’ and ISINID = ‘P’ – No of securities/companies 4670
- Securities/companies that are not Investment Trusts or Closed-End Funds (TYPE = ‘EQ’) - No of securities/companies 4055
- Of these 4055 securities/companies, 2732 are dead/suspended and 1323 are active securities/companies - 517 on LSE Main market and 806 on LSE AIM market (Datastream variable REMK identifies AIM listed companies but only if they are active)
One potential limitation of WSCOPEUK is that it only includes companies that delisted/died in 1985 or later. Indeed the data for early years looks sparse – it has 7 companies that died in 1985 and 11 companies that died in 1986.
Datastream also has the constituent lists FBRIT (Research UK active) and DEADUK. Using the same filters as above – first FBRIT
- All securities on FBRIT (active securities) on 28 May 2013 - No of securities 1640
- Securities on LSE, that are major and primary (EXDSCD = ‘LN’ and MAJOR = ‘Y’ and ISINID = ‘P’ - No of securities/companies 1595
- Securities/companies that are not Investment Trusts or Closed-End Funds (TYPE = ‘EQ’) - No of securities/companies 1549
- Of these 1549 securities/companies, 39 suspended, 563 LSE Main Market, 947 LSE AIM market
- All securities on DEADUK (dead securities) on 28 May 2013 - No of securities 8568
- Securities on LSE, that are major and primary (EXDSCD = ‘LN’ and MAJOR = ‘Y’ and ISINID = ‘P’ - No of securities/companies 5353
- Securities/companies that are not Investment Trusts or Closed-End Funds (TYPE = ‘EQ’) - No of securities/companies 5082
- Of these 5082 securities/companies, 4872 died after 31 Dec 1984, 678 died in 1985 and 150 died in 1986.
As in part 1 the number of companies depends on how you choose to define “UK listed companies” and the time period that you are considering.
One major advantage of using Thomson Reuters Datastream in this investigation is that the companies can be identified using codes (in this case the Datastream dscode) rather than names so getting further data is easier.
Looking at the Datastream Navigator summary data for WSCOPEUK it is clear that this constituent list includes unquoted companies for which there is no share price data, and there are many UK listed companies where Datastream has share price data but no accounting data in Worldscope. Having discovered while writing this post that there are more issues here than expected further analysis is postponed to part3.
The analysis of Datastream’s coverage of UK companies was done using the Datastream Navigator advanced search, and a DS request table to get static data about all members of constituent lists WSCOPEUK, FBRIT and DEADUK – see screenshot for example results.
Worldscope is the global company accounts “database” from Thomson Reuters and thus a key research resource for company information. It is accessed using either Datastream (active and inactive companies) or Thomson One Banker (active companies only).
Worldscope is designed to allow comparison of companies that report under different accounting rules worldwide. In addition, it records company information using four templates: banks, industrial companies, insurance companies and other financial companies. This means that many of Worldscope’s datatypes only have values for a subset of the companies covered (e.g. US companies).
For full details of the Worldscope methodology and the definitions of the datatypes you can consult the Worldscope Data Definitions Guide (Issue 14) available from the Datastream Extranet.
The Worldscope Coverage Report, also available from the Datastream Extranet, for 28th Feb 2013 includes the following coverage information:
- Argentina – total companies 144, active 105, inactive 39
- Australia – total companies 2903, active 1957, inactive 946
- United Kingdom – total companies 5093, active 1890, inactive 3203
- United States – total companies 21130, active 9175, inactive 11955
- All countries – total companies 74757, active 46704, inactive 28053
The Worldscope Data Definitions Guide (Issue 14) and Worldscope Coverage Report are also available in the folder Database Manuals\Worldscope when using the database PCs in the Eddie Davies Library
Related Worldscope posts:
- Worldscope – company accounts data definitions (April 2011)
- Datastream, Worldscope and Units (August 2011)
- Worldscope accounting data – finding data tips (July 2011)
Worldscope is not the only database for company accounts (financials) – for example Compustat, available through WRDS, is very popular with researchers studying US companies and Bloomberg’s Financial Analysis (FA) function gives company account information.
The term “company financials” is often used rather than “company accounts”.
The library has a number of useful resources for the financial analysis of public companies (companies quoted on a stock exchange).
Thomson Reuters’ Worldscope database provides historical accounting data – balance sheet, income statement and cash flow statement. It is available through Thomson One Banker and Datastream. Bloomberg also has accounting data – command FA (Financial Analysis).
Company Filings (including Annual Reports)
Factiva from Dow Jones is a extensive online news database covering a wide range of sources. (See How Business News can enhance you research for more details.) Bloomberg also provides a wide range of company, financial market and economic news.
Related FAQ questions:
- Where can I find company financials, e.g., balance sheets etc.?
- Where can I find current and historical company annual reports?
- How do I find analyst reports (investment bank reports)?
- Where do I find current news about a company?
- How do I compare a company to its industry?
For further tips: use tags below to find other posts on these databases, and look at the How to research … guides,
What are the units in Datastream for the accounting variables, e.g. total assets, net debt, EBITDA?
The accounting variables in Datastream are from Worldscope and despite a long detailed Worldscope Data Definitions Guide there is no detail about units. The answer from the Thomson Reuters helpdesk is as follows:
- Most of the accounting data from Worldscope to Datastream is in thousands.
- Per share data & number of employees are available in actual.
Note that this is specific to the Worldscope in Datastream:
- The variable/datatype Market Value (MV) is in millions, while the similar Worldscope variable Market Capitalization (WC08001) is in thousands (and an annual rather than a daily figure).
- Using Thomson One Banker to get Worldscope data the default unit (scaling factor) is millions, though this can be changed or you can explicitly request the details by asking for WS.TotalAssets.ScalingFactor or WS.TotalAssets.ScalingFactorText (see Excel examples below).
Adding #U to a datatype will often give the units:
- MV#U gives 1000000 as Market Value (MV) is in millions
- P#U gives 0.01 for UK shares as these are quoted in pence, 1 for US shares/stocks as these are quoted in dollars
Thanks to Aberconway Library blog (Cardiff Business School) for this – Finding the scale of datatypes within Datastream (posted July 2012)
For more information see:
The large number of data items (datatypes) available in Worldscope can make it difficult to find the ones that you want. The following tips might help.
Using the Datastream Navigator:
- Make sure you use reset to clear previous search criteria
- Use menu on left to restrict scope to Worldscope
- Try alternatives when searching by name
- Try restricting search to just key items – (Key WS Item option)
You can also try making a static request for say all Worldscope balance sheet items for industrial companies. See screencast
Remember – just because a data item exists within Worldscope does not mean that data will be available for all companies – much depends on what the company reports in its annual accounts (and when a company was included in Worldscope – WC11516 Date Added to Product)
You can also use Thomson One Banker:
- Hover over a value in a Worldscope report to get the definition
- Use item lookup on Worldscope in the Report Writer (Web interface) or Report Wizard (TOB Excel add-in) – right-click to access definition
- Access pre-defined Worldscope report in the TOB Excel add-in – select a value and the TOB worldscope name will appear in the description of the cell contents e.g.
=PFDL(“ws.OperatingIncomeAfterDepr“, <date ref>, <comp ref>, <currency ref>)
The Worldscope data definitions guide provides the full detail of the templates for companies (Industrial, Banks, Insurance and Other Financial) and the definitions for all data items. See also:
- Are the Worldscope codes available in financial statement order? (from FAQ)
(from Databaser blog)
- Worldscope meets Compustat: A Comparison of Financial Databases by Niels Ulbricht and Christian Weiner may be useful if you are looking to find comparable Worldscope and Compustat data items.
The Thomson One Banker (TOB) Excel add-in report wizard is an excellent tool for retrieving data (from the TOB company analysis module). However, it does not offer the flexibility to get your results directly as panel data.
This can be done using the TOB PFDL language. For example, a spreadsheet showing panel data from Worldscope –
(Excel 2007 with TOB add-in required)
The core formula for a cell is =PFDL(I$2,$B4,$A4,I$3) where :
- I2 cell is ws.Sales (the data item)
- B4 cell is Y2010 (the period)
- A4 cell is “company key/identifier” (ISIN in this example)
- I3 call is USD (the target currency)
The formula =PFDL(I$2,$B4,$A4) is enough if no currency conversion is required, and you can use =PFDL(I$2,$B4,$A4,”USD”) if you are always going to convert into US Dollars.
The formula =PFDL(I$2,$B4,$A4,I$3) means that rows and columns can be copied and edited and will refer to: the data item in row 2 of current column, the period in column B of the current row, and the company key/identifier in column A of the current row.
To find the possible codes for the data items you can use: the report wizard, the Lookup Item tool, or examples from the reports library.
For valid company identifiers,
- see this example
(Excel 2007 with TOB add-in required) or
- select help for more PFDL information.
Previous posts on TOB Excel add-in:
Thomson One Banker Excel add-in tips – some notes on resources to help you get the most out of the TOB Excel add-in.
- Financial market data on active companies and some indices
- Worldscope company accounts data
- IBES summary analysts forecasts data
This ReturnDailyFTSE100.xlsm (Excel 2007 via Dropbox) spreadsheet shows getting daily return data for the FTSE 100 using both the Datastream and Thomson One Banker Excel add-ins. The Datastream tab will only work on a PC with Datastream and its Excel add-in installed. The Thomson One Banker tab requires its Excel add-in to be installed.
[05 May 2011 problems reported with Googledocs version ReturnDailyFTSE100.xlsm ]
- How do I access Thomson One Banker? includes brief notes on installing the Thomson One Banker Excel add-in as well as using the web interface.
- Thomson One Banker Excel add-in post July 2010 - all TOB tagged posts
- [Datastream] Share Price Return post Dec 2009 – all Datastream tagged posts
- FAQ answers for Thomson One Banker
- FAQ answers for Datastream
So if you are unable to access Datastream because the library is closed you might find Thomson One Banker useful.
Worldscope describes itself as “the financial industry’s premier source of detailed financial statement data and profile data on public companies domiciled outside the United States of America. It also contains complete coverage of US companies filing with the Securities Exchange Commission, with the exception of closed end investment companies.” (Worldscope Data Definitions Guide (Issue 9) 2010, p 24)
Worldscope takes company accounts and related data to complete global templates designed to facilitate comparisons between companies both within and across national boundaries. Details of the Worldscope methodology and the defintions of the data items (datatypes) is given in the definitions document.
Worldscope Data Definitions Guide (Issue 9) November 2010 is available:
- via Datastream (Demo-
- via Datastream Extranet (similar to via Datastream)
- in the folder Database Manuals\Worldscope when using the database PCs in the Eddie Davies Library.
Perhaps the most important thing to remember when using Wordscope is to check whether real data is available for the companies and years being studied. Worldscope defines a large number of data items but some may only be available for companies in specific countries, or collection of the data item may only have started recently.
Manchester Business Answers 24/7:
[27 Jul 2011 - post Worldscope - finding data tips ]
The Thomson One Banker Excel add-in is a powerful resource for getting company analysis data directly into Excel, especially if you are getting data for many companies. The main restriction is that Thomson One Banker does not cover inactive/dead companies (only the few that became inactive recently).
FAQ – For Thomson One Banker, is web or Excel interface best? has more detail
Screenshot (click to enlarge)
Getting started with Thomson One Banker Excel add-in
- Login – select padlock at top left
- Price Wizard – select entity (e.g. Equity) and dates
- Company Report – select company by TOB ticker or key (e.g GSK-LN)
- Reports Library – browse other pre-defined reports to see range of data available
- Report Wizard – select entities and items (data types) as shown above
There is a manual “Excel Add-in Manual 2007_banker_v2″ in the Database Manuals -> ThomsonOneBanker folder on the database PCs in the Edddie Davies Library, and the online help.
Related tips on Manchester Business Answers 24/7 -
How do I obtain data for multiple companies in Thomson One Banker?
How do I access Worldscope?
Where can I find company financials, e.g., balance sheets etc.?
Where can I find historical stock prices?
More advanced features:
- Using “saved entity sets” from Thomson One Banker in wizards to get data on many companies – see Thomson One Banker – Dissertation Research (Oct 2012)
- Using PFDL commands to create your own custom reports (see documentation and Panel data from Thomson One Banker (Jul 2011) )
There are some sample Excel spreadsheets available on the database PCs ( Database Manuals -> ThomsonOneBanker -> ExcelAddin )