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 Follow @UML_BDS
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.
Now that the dates are recorded this way, Excel can interpret them correctly when doing analysis or when using features like pivot tables.
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.
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.
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).
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.
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.
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.
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.
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.
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
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’.
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.
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 “@”.
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
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.
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
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.
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.
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:
However, when the search is repeated and ‘dead’ is also included, it does locate the company:
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:
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!’.
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.