Nowadays a lot of students use a Mac (with Safari). But the ThomsonOne database requires the use of Internet Explorer. Luckily there is a way to solve this issue. You can let Safari act as Explorer! If you use Google for a solution you probably end up with several websites that explain how to do this. This is one of them:
In this example we”ll show you some of the new functions of the WRDS conditional statement builder (in Compustat). We’ll use an example that shows how to select a SIC range.
First in step 2 select “Search the entire database”
If you try to activate the conditional statement builder and you haven’t selected any variables yet, you”ll get the following erros message.
So first select variables that you want to use in the conditional statement builder. In this example we select “SIC — Standard Industry Classification Code” and “CONML — Company Legal Name”
Now you can create a conditional statement. Add a rule and specify that the company name start with an A , add a group and add a rule where you specify that the sic code starts with a 2. Add another rule to this group and specify that the sic code starts with a 3. Within this group select “OR”.The query preview should look like this
The end result of your query is a list of companies with sic >= 2000 and sic <=3999 (sic starts with 2 or 3) and the company name starts with an A.
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.
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
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.
(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.
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.
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.
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).
When you need a list of companies or stocks in a particular industry, and your startpoint is Datastream, then what are your options?
- 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
- 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.
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”)
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.
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.
First you copy US44044KAA97 into B1. In B2 you enter this formula:
=BDP(B1 & ” corp”,”FUNDAMENTALS_TICKER”)
In B3 you use the contents of B2 and use concatenate to put “Equity” behind it. Use this formula:
=B2& ” Equity”
The last step is to use this function in cell B4:
to find the ISIN you are looking for.