Search data by concept, company or variable in WRDS

Within Wharton Research Data Service (WRDS), a lot of datatypes are available: stock prices (CRSP), annual or quarterly report data (CompuStat), compensation data (ExecuComp), forecasts (I/B/E/S), governance data (RiskMetrics) and more. The “Get data” option, located at the top of your screen can help. You can search data by concept, company or variable.

 

Advertisements

Some industry classifications

Different databases use different industry classifications. Here is a (non-exhaustive) list of classifications:

SIC -Standard Industrial Classification

ICB -Industry Classification Benchmark

Classification of Dow Jones Indexes and FTSE. Used in Thomson One

NAICS           http://www.census.gov/cgi-bin/sssd/naics/naicsrch?chart=2007

The North American Industry Classification System

The standard used by Federal statistical agencies in classifying business establishments for the purpose of collecting, analyzing, and publishing statistical data related to the U.S.business economy.

GICS             http://www.standardandpoors.com/indices/gics/en/us

(choose GICS Maps)

Global Industry Classification Standard

Developed by Standard & Poor’s and MSCI Barra, GICS consists of 10 sectors, 24 industry groups, 68 industries and 154 sub-industries.

TRBC               http://thomsonreuters.com/content/financial/pdf/i_and_a/indices/trbc_quick_guide.pdf

Thomson Reuters Business Classification system.

TRBC consists of four levels of hierarchical structure. Each company is allocated an Industry, which falls under an Industry group, then Business Sector, which is then part of an overall Economic Sector. There are 10 Economic Sectors, 25 Business Sectors, 52 Industry Groups and 124 Industries.

USED IN DATASTREAM!

Orbis uses the NACE industry classifications made by the European Commission. They also have a convertor from other industry classifications to this NACE.

 

Why is the stock price in CRSP preceded by a minus sign?

Sometimes you see negative stock prices in CRSP. This means that there was no closing price available for that period. Instead, the bid/ask average was used. To distinguish the bid/ask averages from actual closing prices, CRSP puts a leading dash in front of the price when the bid/ask average was used. If neither price nor bid/ask average is available, Price or Bid/Ask Average is set to zero.

Find repurchases in ThomsonOne

This is the definition used by SDC: “Repurchases: indicates all deals in which a company buys back its shares in the open market or in privately negotiated transactions or a company’s board authorizes the repurchase of a portion of its shares.”

Repurchases can be found in the Thomson One –> Screening and Analysis –> Deals & league Tables –> M&A –> Advanced Search. Repurchases are a specific Deal Type in the M&A module. Go to Deal Info > Deal Type and select Repurchases.

You can also use the item ‘Repurchase Flag (Y/N)’ – this item can be selected by using the Item Search option (it’s not in the Item List).

FAQ: Industry codes in Datastream

When you need a list of companies or stocks in a particular industry, and your startpoint is Datastream, then what are your options?

  1. Via the internet version of Datastream Navigator
    In the Explore option you can choose Equities > a country  and then you get a list by industry. This list is based on the sector level of the ICB classification
  2. Via Find Series in the Datastream Excel Add-in
    • The Explore option there has the same as in the internet version of Datastream Navigator
    • In the Criteria Search option you can choose Equities. One of the search criteria is Sector – also based on the ICB classification.
      The advantage is that you can combine this with other search criteria – you are not limited to one particular country as in the Explore option.

When you already have a list of company codes compatible with Datastream (DS Codes, DS Mnemonics, ISINs, SEDOLs) than you can request other industry codes in a Static Request.

  • SIC – the source of this item is Worldscope. Worldscope gives up to 8 SIC codes per company, so in Datastream you can select SIC Code 1, SIC code 2 etc. In your output you will only see the SIC code, not a description.
  • ICB – you can request the industry, supersector, sector and subsector levels from the ICB classification – both the code and the name/description. In the list of datatypes select the codes that start with ICB, the ones that start with FTSL are not available to us.

Looking for companies in the Fortune 500 (US/ Global)?

The Fortune 500 is available on the website of CNN Money, but unfortunately no company identifiers are added to the company names. This makes it impossible to upload this list of companies into a financial database.

This is a workaround via Bloomberg:

How can I see a list of Fortune 500 companies?

We do not have an index tracking Fortune’s lists of the highest revenue companies. However, you can search for the list of companies that meet specific criteria on the Equity Screening (EQS) function. To create your equity screen for highest revenue companies, from EQS <GO>: 1) From the “Screening Criteria” section, click “Country of Domicile”. 2) In the “Enter Query” box, start typing a country, then select from the list of options that appears. Note: You may select more than one country or region. 3) After making your selection(s), click the “Update” button. Note: To see Fortune Global companies, skip steps 1-3. 4) In the “Add Criteria” section, in the amber field, start typing “Revenue”, then select “Revenue – Screening/Display Field” from the list that appears. 5) In the drop-down menu below “Revenue”, select “Rank”. In the field next to “Top”, enter the number of companies you want to see, then press <GO>. 6) Click “See Results | WATC” to see the list of companies.

  • To see the list as of a historical date, change the date at the top-right of the screen.
  • To add more data columns, in the “Add Column” field, enter keywords to search for the data you’d like to see. See more on adding columns in the EQS Help Page: LPHP EQS:0:1 1469311 <GO>.
  • To export the results data to Excel, from the toolbar, select “Actions > Output > Excel”.

See more on adding criteria, such as city of domicile, to your screen in the EQS Help Page: LPHP EQS:0:1 998054 <GO> Excel Integration To dynamically download your EQS search results into Excel, you must save your search. From the Results screen, from the toolbar select “Actions > Save”. In the “Name” field, enter a title for the search, then click the “Update” button. To download the results, use a BEQS formula. The syntax is =BEQS(“Screen Name”). For example, if your screen is named “Fortune” the formula is =BEQS(“Fortune”)

 

Data Update Calendar WRDS

The WRDS databases are not updated in Real Time. Most databases do have a delay from 1 year.

See for updates in WRDS the “Data Update Calendar” This calendar provides up-to-date information on the status, coverage, and frequency of updates for all datasets available on WRDS.: https://wrds-web.wharton.upenn.edu/wrds/support/update_calendar.cfm

Wharton Research Data Service (WRDS) :is the interface to datasets from Bankscope, CRSP, Compustat, ExecuComp en I/B/E/S, OptionMetrics, RiskMetrics and TAQ.

Bond ISIN to company ISIN

Suppose you created a list with bonds and the accompanying ISIN codes. You can’t use these ISIN codes to download information from the issuer of this bond. But here is way to use the ISIN codes you collected so far to find the ISIN code of the issuer

An example: the ISIN code US44044KAA97 belongs to a bond. To find the ISIN from the company you can use Excel on the Bloomberg terminal.

b1

First you copy US44044KAA97 into B1. In B2 you enter this formula:

=BDP(B1 & ” corp”,”FUNDAMENTALS_TICKER”)

b2

In B3 you use the contents of B2 and use concatenate to put “Equity” behind it. Use this formula:

=B2& ” Equity”

b3

The last step is to use this function in cell B4:

=BDP(B3,”ID_ISIN”)

b4

to find the ISIN you are looking for.

US tickers to Datastream mnemonics

If you have US tickers as output  from e.g. Compustat index constituents and you want to get stock prices from Datastream that’s possible. You can use a ticker to create a Datastream mnemonic. You have to add country code, so “U:” would do the trick. You can easily do this in Excel by concatenate U: with the column with ticker.

This solution works fine for all but the Nasdaq firms. Those will need a “@” sign in front not “U:” So just to be sure you have everything and you don’t miss any data do both so a list with “U:” and one with “@”.

Example

Screenshot_USticker_mnemonics_v2

The ones that don’t make sense will give errors but this way you won’t miss any. So with Ford U:F will work and give data, @ F won’t it will give an error. Similarly Micosoft @MSFT will work and give data, U:MSFT won’t it will give an error

Cross border deals

Searching for cross border deals in Thomson is quite easy. You’ll need to use the “Advanced Search”. Go to Screening & Analysis > Deals & League tables > M&A > Advanced Search

Screenshot 1 - Advanced Search

When you include targets from a specific nation and exclude the acquirors from that same specific nation, you’ll get cross border deals within a country.

Screenshot 2 - Exclude acquiror

Below you’ll find an example of a search list for cross border deals.

Screenshot 3 - Search list

In your report you can use “Cross Border Deal Flag ” to verify the deals.

Screenshot 4 - Cross Border Deal Flag