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 

Convert CRSP date YYYYMMDD to Excel true date

10 May 2016 1 comment

If you have a data file from CRSP (or many other sources), the date may be recorded as a text string of the form YYYYMMDD (for example, 20151231 for the last New Year’s Eve). If you wish for Excel to interpret this as a date, for the purpose of analysis, this can be done with the four formulae below.

=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2))

This reads the following fields.

  • Year from the first 4 characters of cell A2.
  • Month from the middle 2 characters of cell A2 starting from position 5.
  • Day from the last 2 characters of cell A2.

See the image below for an example.

Excel date from CRSP YYYYMMDD

Use Excel’s DATE formula to interpret text or numbers as a date, for analysis purposes. You may need the LEFT, MID and RIGHT formulae to pull out the year, month and day values from another cell.

Now that the dates are recorded this way, Excel can interpret them correctly when doing analysis or when using features like pivot tables.

Categories: Business Databases Tags: ,

Historical Index Constituents in Bloomberg

12 April 2016 2 comments

This post follows on from Historical Index Constituents (e.g. S&P 500) written in October 2014.

It is easy to find the current constituents for an equity index such as the FTSE 100 or S&P 500 but finding the historical members for a given date or range of dates requires more work. This blog post shows briefly how to use four screens in Bloomberg.

Bloomberg screen SECF

Use Security Finder SECF to find the code for the Equity Index. Choose Category: Index/Stats and Sub-Category: Equity Index. You can type part of the name or description to further filter the list.

In the Bloomberg search bar, type ‘SECF<GO>’ (that is, SECF then the Enter key) to use the Security Finder to find the Equity Index you need. You could just type the name of the Index in the search box and see if the correct Index is suggested. In this example, click on SPX Index (S&P 500 Index). A menu will appear, choose Member Weightings MEMB.

Bloomberg screen MEMB

For an Index, the MEMB screen shows constituent members (and weightings) for a given date. You can add extra Fields such as ISIN or CUSIP identifiers to use later.

You can get straight to the Member Weightings screen if you know the code of the index. Type, for example, ‘SPX <Index> MEMB <GO>’ (that is, SPX then the F10 key then MEMB then Enter). The date can be changed in the ‘As of’ box at the top-right and extra columns can be added. From here, on the red menu, click Output > Excel to export the data to a new Excel document (requires the Excel add-in to be configured, see desktop icon “Install Bloomberg Office Add-ins” at Manchester).

Bloomberg Export MEMB to Excel

Open the output file, accept a warning message about file types.

If you requested ISIN or CUSIP identifier numbers, these can be used in other databases such as Datastream, Compustat or Capital IQ.

Getting constituents for a range of dates

Getting the membership for the start date and the end date in your range will obviously not consider any company that joined and left in the intervening time. It is common to collect the membership at month end for each month in the range, paste the results into one long list in Excel then use the Data > Remove Duplicates function to get a tidy list of each company once only.

Bloomberg output without duplicates

(1) Export each month’s constituents. (2) Copy and paste the Tickers into one long column for all months (without copying the heading ‘Ticker’ each time). (3) Select that column and choose Data > Remove Duplicates.

You can do this and keep all the columns (name, weight, ISIN etc.) but the image above just keeps Ticker. You can get all the other data again using the Bloomberg Excel add-in if you wish (with formulas such as =BDP(“AAPL US Equity”, “ID_ISIN”) where the first parameter is a reference to the cell with the Ticker).

Is there a way to do this more easily in Bloomberg? Well, yes and no. There is a screen in Bloomberg called Historical Index Member Changes CHNG.

Bloomberg screen CHNG

Historical Index Member Changes for a given Index and date range. But only works for some indices and the report cannot be exported.

For the FTSE 100, type ‘UKX <Index> CHNG <GO>’. Choose the date range and click the top ‘Enter 1<GO> to generate report’. A report will be generated and it will tell you the number of the report. Go to the Retrieve Reports screen RPT.

Bloomberg screen RPT

Retrieve reports generated in Bloomberg. View these on screen or save one page at a time to an image or printer.

You will see a line in the report for each member’s entry and exit to the index, with more detail. But you cannot export the list of member tickers to Excel or do anything else systematic like that. It will be better to use the MEMB screen and export each month.

Do you know a better way? Please let us know by commenting on this post.

See also Historical FTSE100 Index Constituents on Datastream, dated July 2012.

Categories: Business Databases Tags:

WRDS new interface for selecting output variables

6 April 2016 Leave a comment

Following on from the refresh last summer, the Wharton WRDS web portal has been updated again. It is now easier to find and select the output variables you want to use in your request.

WRDS variables 2016

Finding and selecting output variables in WRDS is now much easier

The screen above uses the CRSP monthly stock database as an example. In the ‘Query Variables’ step, there are now multiple tabs containing categories of variables (which used to be listed in one column of boxes). The first tab contains all the variables, the subsequent tabs are categories like identifiers, balance sheet items, profit & loss items, and so on. There are orange and white arrows to scroll left and right between the tabs.

There is a search field in the first tab where you can type the name of the variable or any substring of it. For example, type “code” to get any variable with “code” in its name, it does not have to begin with “code”. Tick each match that you want to use and it appears in the column on the right, labelled ‘Selected. You can browse through all the tabs and see the same master list of Selected variables appear in each. This is much easier than using the browser’s ‘CTRL+F’ find function that was required before.

New feature: Saved Codelists

WRDS saved lists

You can save lists of codes to use later

Another new feature is the Saved Codelists. Usually we recommend people upload a text file containing the codes for the companies (or other entities) that they wish to run a query on. WRDS lets you type in or select companies one-by-one as well (useful for looking up a small number of companies). There is a blue link to ‘Code Lookup’ in most of the WRDS databases which lets you type the whole or part of the name of a company to find its code.

The new feature is that you can save these short lists to use later. The screen above shows Bureau van Dijk Amadeus. Simply tick the first box highlighted in the image above (to the right of the codes) and type a name for the list. On a later visit to WRDS, you can find the list in the second box highlighted above, ‘Select Saved Codelists’.

US tickers to Datastream mnemonics

31 March 2016 Leave a comment

A handy summary for those wishing to use a list of US companies Compustat in a Datastream query. In summary, a well-chosen prefix to the ticker symbol can be systematically added in Excel before creating a static or time series Datastream request. All you need to know is if the company is listed on Nasdaq or elsewhere. Thanks to EDSC for this post.

EDSC tips & hints

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

View original post

Changing Datastream data & Stata

24 March 2016 Leave a comment

A wonderful post from Research Financial again, this time giving a thorough worked example of using Stata to reshape Datastream data. Stata and Datastream are available at The University of Manchester Library in the Finance Zone.

researchfinancial

The past few weeks I have been learning about and working with Stata. This program can handle a lot of data and uses commands to edit data or analyse it. A sequence of commands can be saved in .do files and then rerun as a script. There are many commands available and one of them is very handy when it comes to changing data from columns to rows. It is similar to the transpose option that Microsoft Excel offers for quick changes. It is the reshape command.

In this blog post I will use the reshape command to change Datastream data as an example. Similar work can be done for other downloads from databases like Amadeus or Bankscope.
For downloads it can take a bit of work to change the data and rework it before Stata can be used to merge it with other data. In the case of…

View original post 674 more words

Categories: Data Analysis Tags: ,

Getting started with SDC Platinum: videos from Thomson Reuters

21 March 2016 Leave a comment
Screenshot of SDC training video from Thomson Reuters

Screenshot of SDC getting started video from Thomson Reuters

Thomson Reuters provide a series of screencast “getting started” training sessions for their products. In the past, we have highlighted these for Datastream and Thomson ONE. Today, it is the turn of SDC Platinum.

This course covers the following topics:

  • Introduction (1:13)
  • Setting up Your Search Criteria (5:05)
  • Output Your Search Results as a League Table (2:17)
  • Output Your Results as a Report (1:47)
  • Output Your Results as an Analysis (2:27)
  • Saving and Printing Your Output (1:21)
  • Accessing the Standard Search Sessions (2:46)
  • Defining Your Search Criteria Using a Standard Search (3:21)
  • Editing an Existing League Table, Report, or Analysis (4:04)
  • Creating a Custom Report (3:08)
  • Creating a Custom Analysis (3:01)
  • Change Your Default Output to Excel (0:55)
  • Accessing SDC Customer Support (1:43)

You will need to use your University email address when you start the SDC getting started session.

Datastream – Dead Companies

With the loss of the ‘Criteria’ screen within the Navigator search function in Datastream  (5.1 and Advance for Office versions), the area of ‘Dead’ companies is relevant when searching for historical financial data.

 

Dead Companies [Equities]

Starting the Navigator feature and entering ‘Dead’ as a search term produces a total of 151,626 companies matching this status.

 

Datastream - Dead (Equities)

Datastream: Navigator search (via the ‘Find Series’ button).

 

 

When A Company Is Acquired (Taken Over)

The following is an example using ‘CNS Inc.’ (consumer health care products), which was quoted within the US NASDAQ price index between June 1987 and December 2006 . It was acquired by GlaxoSmithKline in December 2006 and didn’t continue to trade using the CNS name.

Searching within the Navigator for CNS doesn’t locate the company:

 

Datastream (CNS Search)

Datastream Navigator Search for ‘CNS Inc.’.

 

 

However, when the search is repeated and ‘dead’ is also included, it does locate the company:

 

Datastream (CNS and Dead)

Clicking on the company pins the entry and displays additional details, such as the dates it was quoted between (11/06/1987 to 19/12/2006)

 

 

When A Company Delists

That is, change from public (quoted) company status to private.

The ASDA food retailing group is such an example. It was acquired by Wal-Mart Inc. in 1999, but continues to trade using the ASDA name.

Searching for ASDA in the Navigator locates the company:

 

Datastream (Dead - Asda Search)

ASDA Group: background information.

 

Again by clicking on the company entry, additional details are displayed. Noting the dates between which Asda has data available in Datastream (30/12/1964 to 30/09/1999) allows a search to be conducted within these dates. For example, when looking for the Share Price (P: Price Adjusted Default datatype).

Time Series Data, 01/01/1995 to 31/12/1997, Daily Frequency and ‘Run Now!’.

 

Datastream (Dead - Asda Share Price)

This search successfully locates ASDA share price data.

 

 

 

A useful datatype when searching for multiple ‘Dead’ companies in relation to Share Price data, is ‘P#T’. This ensures that data doesn’t continue displaying past the end date, when a company becomes ‘Dead’.

Previous related post: Datastream – Price variables: P, P#S, P#T   which dates from 13 April 2012.

 

Follow

Get every new post delivered to your Inbox.

Join 1,162 other followers