Welcome to Business Research Plus

27 July 2011 1 comment

Business Research Plus header 2015

From specialist databases to business literature, Business Research Plus provides advice and tips based on The University of Manchester Library Business Data Service resources and expertise.  See our About page for more details.

  • Library Research Plus provides expert insight from The University of Manchester Library’s Research Services for researchers in all disciplines including business.
  • My Learning Essentials, the Library’s award-winning skills progamme includes online resources with a wealth of useful tips on searching, referencing, writing to support personal and professional development.

Exploring our resources – try the Business and Management Resources page (Subject Guides),  scroll down to category or tag cloud in the right-hand column or search this blog (top right). For latest news see @UML_BDS 

Advertisements

Dissertation Research

19 October 2017 1 comment

When seeking to complete a dissertation in the area of Accounting & Finance, a key consideration is ‘Research Feasibility’. This can be summarised by the statement:

 

Can I obtain the data I require, in a timely manner, to successfully complete my research?

 

A typical one year MSc course would allocate 3 months at the end, to complete the disseration. The gathering of company financial quantitative data, (from sources such as: Thomson Reuters Datastream, S&P Capital IQ, Bloomberg Professional, Compustat via WRDS: Wharton Research Data Service) is fundamental to the success of the research.

 

What can go wrong?

 

  1. Data is not available:  It is not contained within the databases the university subscribes to. The years required are not covered. The student is off-campus and the data is only accessible on-campus (as is the case for Datastream and Bloomberg Professional).
  2. Research Proposal:  This may be too ambitious. For example, a student reads an accounting/finance journal article and decides to try to replicate all or part of the research contained within the article. This can be problematic, as the academic probably spent two or more years completing the research – greater than the time available for an MSc dissertation.
  3. Topic:  The choice of topic can be influenced by a desire to work in a particular area of finance. Unfortunately, this may lead to the key difficulty when conducting research – Data is Not Available.

Data is the foundation on which any analysis is based. Where this is difficult to obtain, time pressures may result, leading to the possibility of failure to submit the dissertation on time.

Whilst it could be argued that the difficulties experienced by students in working on their dissertation are part of the research process, as a Librarian, my approach is different: how can I be most helpful, in assisting the student to successfully complete their research?

 

Helpful Suggestions

 

  1. Pilot Project: Essentially this means establishing the best source – there could be more than one available. Also, how to search the source productively. Also, whether all the years of data required are covered.
  2. Seek Guidance:  This follows directly from point one above. It may be that the most efficient method (shortest time to collect what is required) is not known to the student. Guidance from a Librarian can demonstrate the best source and search method, drawing on years of experience in supporting student dissertation research.
  3. Explore Resources:  With so many sources available to students, the difficulty is often one of familiarity – knowing which databases are available and how they can be accessed. A Library web site is a good place to start. The example below is the subject guide for ‘Business and Management’, at the University of Manchester.

 

Database Guide

Business & Management Guide

 

One of the sections is  for ‘Specialist financial databases’. These are useful for dissertation research:

 

Financial Databases

Specialist Databases

 

Summary

 

Making the best use of resources by seeking guidance from Librarians and planning ahead (pilot study) can help to ensure a dissertation is successfully completed. The key factor being, the ability to secure data, on which to base any analysis.

 

Previous related post, in the Library Research Plus blog:

Research Feasibility [18 February 2015]

 

 

13 new ESG Scores released on Datastream

10 October 2017 Leave a comment

The following message is copied directly from Thomson Reuters content notification DN093509. It applies to Thomson Reuters Datastream, specifically Environmental, Social and Governance (ESG) data. There has been no change in the ASSET4 Excel template which can be used to access this data. The 2015 ASSET4 ESG data glossary (Excel format) offers an additional reference of the relevant datatypes. A copy of this message is also available as a PDF.

Thirteen new ESG Scores measures have been added to Datastream.

Thomson Reuters ESG Scores are an enhancement and replacement for the existing equally weighted ASSET4 ratings*. They reflect Thomson Reuters’ new strategic ESG framework.

* NB: We advise all users of the ASSET4 ratings to migrate to the new ESG Scores in the coming months, as we plan to retire the ratings in early 2018.

Key enhancements over the legacy equal-weighted ASSET4 ratings are:

  1. ESG controversies overlay
  2. Industry and Country benchmarks at the data point scoring level
  3. Automatically adjusted Category scores based on the size and impact of each category
  4. Percentile Rank scoring methodology where hidden layers of calculations are eliminated

The new scores are designed to transparently and objectively measure companies’ relative ESG performance across ten themes as shown in the chart below.

New ESG measures added to Datastream

A combination of the ten categories makes up the ESG Score, which is discounted when there were ESG controversies to calculate the ESG Combined Score. Where there were no controversies for a particular period, the ESG Combined Score and ESG Score have the same value. The ESG Controversies score is calculated per fiscal period, with the latest controversies being reflected in the latest complete period.
Thomson Reuters ESG Scores are calculated and available for all companies and historical fiscal periods in the ESG Global Coverage, consisting of 6,000+ public companies globally.

Below are the corresponding Datastream datatypes, titles and definitions.

DS Datatype DS Title DS Definition
TRESGS ESG Score ESG Score is an overall company score based on the self-reported information in the environmental, social and corporate governance pillars.
TRESGCS ESG Combined Score ESG Combined Score is an overall company score based on the reported information in the environmental, social and corporate governance pillars (ESG Score) with an ESG Controversies overlay.
TRESGCCS ESG Controversies Score ESG Controversies Score measures a company’s exposure to environmental, social and governance controversies and negative events reflected in global media.
TRESGENRRS Resource Use Score Resource Use Score reflects a company’s performance and capacity to reduce the use of materials, energy or water, and to find more eco-efficient solutions by improving supply chain management.
TRESGENERS Emissions Score Emissions Score measures a company’s commitment to and effectiveness in reducing environmental emission in the production and operational processes.
TRESGENPIS Environmental Innovation Score Environmental Innovation Score reflects a company’s capacity to reduce the environmental costs and burdens for its customers, thereby creating new market opportunities through new environmental technologies and processes or eco-designed products.
TRESGCGBDS Management Score Management Score measures a company’s commitment to and effectiveness in following best practice corporate governance principles.
TRESGCGSRS Shareholders Score Shareholders Score measures a company’s effectiveness in the equal treatment of shareholders and the use of anti-takeover devices.
TRESGCGVSS CSR Strategy Score CSR Strategy Score reflects a company’s practices to communicate that it incorporates the economic (financial), social and environmental dimensions in its day-to-day decision-making processes.
TRESGSOWOS Workforce Score Workforce Score measures a company’s effectiveness towards job satisfaction, healthy and safe workplace, maintaining diversity and equal opportunities, and development opportunities for its workforce.
TRESGSOHRS Human Rights Score Human Rights Score measures a company’s effectiveness in respecting the fundamental human rights conventions.
TRESGSOCOS Community Score Community Score measures the company’s commitment to being a good citizen, protecting public health and respecting business ethics.
TRESGSOPRS Product Responsibility Score Product Responsibility Score reflects a company’s capacity to produce quality goods and services, incorporating the customer’s health and safety, integrity and data privacy.
ESG chart automobiles

The ESG Controversies Score is calculated based on 23 ESG controversy topics and measures a company’s exposure to environmental, social and governance controversies and negative events reflected in global media

For more details, you can click here to refer to the full methodology paper. In particular, you can consult page 12 for a high-level comparison between the two scoring methods.

Categories: Business Databases Tags: ,

Updated conditional statements feature in WRDS

4 October 2017 Leave a comment
WRDS conditional statements

An example of nested conditional statements, taken from WRDS help pages.

Wharton Research Data Service (WRDS) is praised for offering a consistent user interface across the many databases that it houses, so, when a new search feature is released, it will be available for all users. They have updated the conditional statements builder and made it more powerful and easy to use. We will look at Compustat Global in this example.

What is the conditional statement builder?

To quote WRDS support, you can “build unlimited conditional statements using the list of variables, comparison operators, desired values, and logical operators”. The conditional statement builder still forms part of Step 2: Apply your company codes, but you will need to come back after completing Step 3 Query variables before it will work.

How do I use the conditional statement builder?

Search the entire database

As before, you can choose to select companies individually, upload a list, or “Search the entire database”. Choose this last option if you wish to use the conditional statement builder to reduce the scope of your search using any query variable. For example, you may want to search for companies located in one country, or those with total assets over a threshold, or those in a particular sector. Note that you will need to include the relevant query variable in Step 3: Query variables before applying the conditional statement builder.

Select query variables

Scroll back up to Step 2, click “Activate Conditional Statement Builder”.

Activate conditional statement builder

Add rules one at a time, selecting one of the chosen query variables and a condition (equals, not equals, greater than, begins with, and so on). You can choose to join with AND or OR logical operators. You can add groups of statements and join them.

Build your statements

What help is available?

You must take care to use the right units, scale and codes. To check the acceptable values to use, check the Variable Descriptions tab at the top of the page, or perform a small search and look at the kind of results it gives.

There is a short help page by WRDS on conditional statements, click the link labelled “How does this work?” above the builder.

Categories: Business Databases Tags:

Getting a linking table from Compustat via WRDS Cloud using SSH and SAS

27 June 2017 Leave a comment

Researchers commonly need to combine data from multiple sources. Even when the sources are offered through the same platform such as Wharton Research Data Service (WRDS), this can still be a difficult process with many choices. Essentially, you need to find a common entity identifier in both sources; if none exists, find a reference table or other third source.

The help provided by WRDS is often sparse in detail and tailored for their more advanced users. I have previously written in detail about using WRDS Cloud and SAS to gain access to resources not available on the WRDS website, in the blog post Getting S&P 500 constituents from CRSP via WRDS Cloud using SSH and SAS.

In this post, I will suggest a way to link I/B/E/S data with Compustat North America data. I will summarise the points that are addressed in the aforementioned blog post.

Get a linking table from Compustat North America

You will need the following to begin.

  1. AUTHENTICATION. Your WRDS username and password, specifically an individual PGR or Academic login.
  2. SENDING COMMANDS. Windows users: the Putty program. Mac/Linux users: the Terminal app or command line.
  3. FILE TRANSFER. Windows users: WinSCP or FileZilla. Mac/Linux users: CyberDuck or FileZilla or command line.
  4. FILE EDITING: Notepad, Notepad++ or another text editor. Excel or other spreadsheet application.

The method is very similar to getting the S&P 500 constituents. Essentially, Compustat has a dataset called securities which lists all the identifiers for all the securities. It is in SAS format. It cannot be accessed directly by us, but we can write a SAS script to read its entire contents to a plain text file, then copy the file to our own PCs. This file is kept at location /wrds/comp/sasdata/d_na/security/security.sas7bdat for North American data and is updated often.

Your script to read this file should look like this:

options nosource nodate nocenter nonumber ps=max ls = max;
title ' ';
options source;
proc print data=comp.security noobs width=UNIFORM;

This is different from last time in a few ways.

  1. The ‘ls’ or ‘line size’ option has been changed from ’72’ to ‘max’. This is because there are more columns for Compustat securities than for S&P 500 constituents.
  2. The data table we are reading from is ‘data=comp.security’.
  3. The ‘labels’ option has been removed so we get the clean variable names as the header.
  4. The ‘width’ option is set to ‘UNIFORM’ so the columns are the same number of characters wide on each ‘page’.

The method is as follows; remember to refer to the aforementioned blog post.

  1. Save the SAS script to a new text file on your computer, compsecurity.sas or similar.
  2. Use WinSCP or equivalent to copy this file to your own space on WRDS Cloud.
  3. Use Putty or equivalent to run the script, log in and type qsas compsecurity.sas and press Enter.
  4. Wait 10 seconds and use WinSCP to download the generated files compsecurity.log and compsecurity.lst to your computer. The ‘lst’ file contains the data, if the program was successful (read the ‘log’ file to check).
  5. Use Notepad++ or equivalent to remove the blank lines from the top of compsecurity.lst and save it as compsecurity.txt – then check every 32,764 lines for a few more blank lines and repeat of the headers to be removed (these are ‘page breaks’).
  6. In Excel, open compsecurity.txt (via Data > Get External Data from Text) and use fixed column width option, carefully setting the locations for the column breaks, remembering to set most columns as Text format instead of General (particularly GVKEY and CUSIP). Save as CSV or Excel format.

Compustat securities via WRDS Cloud

Using the linking table

You will need the columns GVKEY and IBTIC (IBES Ticker).

  • If your Compustat and I/B/E/S data are in Stata format, import this table and consider joining the tables.
  • If your Compustat and I/B/E/S data are in Excel format, use VLOOKUP formulas (some more processing may be required).

Still to do: there is not necessarily a one-to-one match between these two identifiers. The same GVKEY will appear on more than one row, but will those rows contain more than one unique ticker? And is the data frequency the same in each source? You will need to find the answers to these questions out for yourself.

Company Screening in ‘Capital IQ’

A request for information from an academic (or student) can appear straightforward, but may be challenging, when all the resources available are not known, or fully understood.

Consequently, on receiving a request for a list of Chinese companies, in particular industries/sectors, with an employee count of between 100 and 500, a number of potential options present themselves. Namely, Thomson ONE.com, Bloomberg Professional and Capital IQ. With the primary emphasis on publicly quoted companies for the first two databases, that leaves Capital IQ, which also covers private companies.

 

Clear Navigation

 

The drop down menus across the top of the search screen reveal options quickly and simply. The option for searching – or screening – companies, to create a list according to the criteria specified, can be accessed by hovering over ‘Screening’, towards the top left.

 

Company Searching

Capital IQ – Company Screening

 

Criteria Selections

 

Once ‘Screening – Companies’ is selected, a number of standard options are presented, within ‘Most Used’ and ‘Company Details’. Certain options would appear to cover broadly similar areas. For example, ‘Geographical Locations’ and ‘Country of Incorporation’. Also ‘Industry Classifications’ and ‘Business Descriptions’. These options can be inserted into separate searches to obtain different results, to gauge which most closely matches the requirements of a research project.

Geographical Locations.

 

Location Searching

Screening: Geographical Locations

 

Searching for the country required: in this case ‘China’, then select from the options presented and click on ‘Add Criteria’, to begin the search process. This produces 375,994 results.

 

Country Search and Select (China)

 

Next, Business Descriptions:  search for ‘Engineering’, then click on ‘Add Criteria’.

 

Engineering

Business Description (Engineering)

 

This greatly reduces the number of results as both criteria have to be true (the ADD operator in Boolean searching), giving 5,889 companies.

 

Country and Industry

Results (China and Engineering)

 

Where a required data item does not appear within the standard list, it is possible to search for possible alternatives. In this case, employees. Type ‘Employees’ in the search box, hover over the required selection (‘Total Employees’) and then move along the line to select ‘Open Criterion’.

 

 

Search for Employees

Employee Search

 

The specific details can then be entered. That is, a range of between 100 and 500 employees working for companies, in the Last Quarter. Click on ‘Add Criteria’ to search.

 

Number of Employees

Employee Search

 

This shows 42 results.

 

42 Companies

Results (42)

 

Results

 

The culmination of Geographic Locations (China), Business Descriptions (Engineering) and Total Employees (100 to 500) combine to give 42 results – companies.

 

List of Companies

Results List

 

Click on the ‘View Results’ button to list the companies. Clicking on the company name provides additional details. For export, click on the ‘Excel’ button and ‘Go’.

 

List to Excel

Export to Excel

 

The Excel export option is straightforward, providing tabs for search results (Screening).

 

Results screen in Excel

Excel – Screening tab

 

The criteria for the search are set out in the ‘Screen Criteria’ tab.

 

Search Criteria (Excel)

Excel – Screen Criteria

 

Summary

 

The Capital IQ Screening feature is easy to use and flexible. In terms of results, these are quick to display on screen or alternatively export to Excel.

 

Related posts for Company Screening include:

Advanced Company Screening in Bloomberg Professional   [31 March 2017]

Top 10 Companies   [16 December 2016]

 

S&P Capital IQ, Bloomberg Professional and Thomson ONE.com databases are available for use by current students and staff of The University of Manchester.

Converting Company Identifiers for Quoted Companies

24 April 2017 Leave a comment

Recently, we helped a PhD student use a company ID converter tool to find the GVKEY codes (one of the company IDs assigned by S&P) for a list of US companies which had been downloaded from Thomson Reuters DealScan that doesn’t provide GVKEYs. GVKEYs are required in order for her to download the necessary data variables from Compustat North America …

This isn’t a one off case. Researchers in business and finance normally require a range of company data variables which need to extracted from more than one databases. For different reasons, companies can be assigned more than one company IDs: IDs assigned by stock exchanges, such as tickers, SEDOLs; IDs assigned by databases, such as DS Code (Datastream), BVD Code (Bureau van Dijk), PERMNO (CRSP) , CUSIP (Standard & Poors), or ISIN.

Unfortunately, there isn’t one universal company ID system which is adopted by all databases. The most commonly used quoted company identifiers are ISINs (International Security Identification Numbers) and CUSIPs. Some databases may provide more than one IDs. For example, Datastream provides ISIN, CUSIP, Ticker etc. apart from its own proprietary Datastream Codes. If using Datastream, one can download a list of companies with DS Codes, ISIN or CUSIP as required. Other databases, for example, CRSP provides PERMCO, Ticker, and CUSIP.

To help users overcome this problem, some database suppliers may provide tools for users to convert one set of IDs to another. In CRSP, for example, one of the CRSP tools can help convert CUSIP to PERMNO or vice versa.

The best tool that I have come across so far is S&P Capital IQ’s company ID conversion tool: SPCIQ Identifier Converter. Embedded in Capital IQ’s Excel Plugin tools, Capital IQ’s Identifier Converter tool can provide ISIN, CUSIP, Ticker etc. if a company name is provided. In other words, you only need a list of company names and it could provide the required IDs for you if the companies are within Capital IQ’s coverage.

Below are the steps of how to use Capital IQ Identifier Converter after the Capital IQ Excel Plugins is installed.

In Microsoft Excel, with the CIQ plugin installed, from Templates, select and download SPCIQ Identifier Converter.xls:

picture1

Then run it from Templates – SPCIQ Plugin Tools – SPCIQ Identifier Converter:

picture 2

 

The caption below shows a list of sample companies with default company ID – Ticker with the embedded formula. Please note in the command line ticker field name is `IQ_Company_Ticker’.

If a different type of ID is needed, for example, GVKEY, then slight editing of the formula should do the job.

Capture3

If you do not know whether Capital IQ provides the required company ID, run Formula Builder to confirm: eg: Click on Formula Builder, Search for GVKEY, GVKEY’s field name is `IQ_GVKEY’:

picture4

Then input the names (or paste in, from cell A12) – a list of companies for which you require GVKEY and click on `Search’ at the right top. Replace ‘IQ_Company_Ticker’ with ‘IQ_GVKEY’ in the command line and we get the list of companies with GVKEYs below:

Capture new 4

A related post: Company Identifiers in Datastream

 

 

 

Categories: Uncategorized

Advanced Company Screening in Bloomberg Professional

Students often enquire about creating a list of companies in a particular area…sector of the economy, or industry.

 

Equity Screening

 

The Equity Screening function [EQS <GO>] in Bloomberg is an obvious starting point. This allows a number of criteria to be specified, to build a search, step by step.

 

When ‘Country of Domicile’ is selected, the expanding menus on the left hand side of the screen give a breakdown from geographical regions to individual countries. However, it is actually faster to type in the country name in the search field, in this case, ‘United States’.

Click ‘Update’. This gives 12,455 companies.

 

Repeat for ‘Sectors’. Here, the expanding menus can come in useful, as you may be uncertain of the category names to enable a keyword search. A recent student enquiry requested US ‘Medical Equipment’ companies. This was located by expanding the options, starting with ‘Health Care’ – and then selected. Click ‘Update’ to incorporate ‘Medical Equipment’ into the search. This gives 202 companies.

 

Results

EQS Results Summary

 

Click on ‘See Results’ to list the companies:

 

List

EQS Company List

 

Whilst there is an icon at the top right of the screen to Export the results to another application, it can be more effective to save the list of companies through the ‘Actions – Save’  drop down menu. Enter a file name (‘US Medical Device Companies’ specified here), press the Enter/GO key to activate the ‘1) Save’ button, and then click on ‘1) Save’.

 

Save

EQS: Actions – Save

 

Excel Add-In

 

To gain access to the results from the EQS search, for additional research, the Excel Add-In interface for Bloomberg provides a number of dedicated search options. The ‘Import Data’ feature is particularly useful. This gives a number of Wizard-type options, with standard screens allowing search selections to be entered. For example:

Import Data  –  Real-Time/Historical  >  Historical End of Day

 

Step 1: Create a List of Securities.

 

Import Data

Import Data search screen.

 

The ‘Select securities’ section allows the source to be specified from a drop menu of options. This includes Indices and EQS – Equity Screening. Once EQS is selected, the field below (Saved Screens) gives access to file names saved – in this case ‘US Medical Device Companies’ – normally at the bottom of the list. This loads the full list of companies to the ‘Available securities’ window. From here, the complete list can be selected (‘Add all’) and copied into the ‘Selected securities’ window, or a subset, then ‘Add’ – as is the case in the screen shot below.

 

List

Step 1: Security List

 

Click on the ‘Next’ button to proceed:

 

Step 2: Create List of Fields.

Whilst there are a series of expanding menus [New Fields, Analysis, Corporate Actions, Descriptive, Earnings Estimates, Fundamentals, Market Activity, Ratings] to reveal the data items available, it is actually more effective to search by keyword(s). To illustrate, ’employees’ has been used as a search term, to locate the ‘Number of Employees’ field. Once selected, a definition is provided below. Click ‘Add’ to copy into the ‘Selected fields’ window.

 

Field

Step 2: Field Selection

 

Click ‘Next’, for Date and Frequency options: Yearly, back to 2010 specified.

 

Step 3: Select Perodicity and Time Frame.

 

Dates

Step 3: Dates / Frequency

 

The next two steps (Currency, Pricing Defaults) are not relevant to this search, so click ‘Next’ to proceed to

Step 6: Layout Options.

 

Layout

Layout Options

 

Click on ‘Finish’ to display results – it may be necessary to widen columns.

 

Results

Results in Excel

 

Although a simple example, this could include many additional data items for each company, for which Excel is well suited.

 

Bloomberg Professional is available to current students and staff of The University of Manchester.