Datastream is useful to be able to search for multiple companies (Series) and datatypes, in terms of quantitative data.
For example, Tesco PLC, J Sainsbury PLC and Marks & Spencer Group PLC are UK supermaket groups, each within the FTSE100 Price Index. One measure of company performance is Share Price. This quantitative data can be represented in chart form.
Data Category is the starting point. For company related data, this would be ‘Equities’.
For Analysis, this would be ‘Time Series Data’.
The Series can be identified by using the Navigator function, activated by clicking on the ‘Find Series’ button. The code displayed in the Navigator search, when selected, gets copied into the search screen. For example, TSCO for Tesco PLC.
The Datatype, for Share Price, searchable via the Navigator (Datatypes button) is ‘Price (Adjusted – Default)’, represented by the code: ‘P’.
Time Period can be selected from set date ranges, such as ‘-10Y’, which represents the last 10 years. Alternatively, a specific date range can be entered by clicking on the ‘Time Period’ button, giving the option to enter a Start and End date using the format: DD/MM/YYYY.
The frequency of data can be selected by clicking on the Settings button. This allows choices of ‘Daily, Weekly, Monthly, Quarterly and Yearly’, with a default of ‘Daily’. Daily is normally used for Share Price data.
Finally, click on the Run Now! button to execute the search.
The results of this search for Tesco PLC Share Price data, in Datastream 5.1, gives the following output:
It is then possible to add this data into a chart view, by selecting Multiple Series/Flexible Chart, then Comparison Line Chart then Rebased.
By clicking on the plus [ + ] symbol, the Series is copied into the chart summary box on the left of the screen.
Go back to ‘Time Series Data’ Analysis option, run the search for the next company’s Share Price and repeat the process, to include all those required in the chart summary box:
Execute the search by clicking on the ‘Run Now!‘ button at the top right of the screen.
By virtue of specifying ‘Rebased’, this represents the different series as an index, beginning at 100. This allows a relative view of performance. Hence, a movement from 100 to 95 represents a 5 per cent decrease in the share price of a company. All three companies are showing a value of about 40 (as at 24/3/2017), indicating they have lost approximately 60 per cent of their value, as expressed by Share Price, over the last 10 years.
Price Index Comparison
By comparing a number of companies within a particular Price Index, it is possible to get an appreciation of how the companies have performed relative to the whole index. Tesco PLC, J Sainsbury PLC and Marks & Spencer Group PLC are all part of the FTSE100 Price Index.
Using the Analysis option ‘Comparison Line Chart – Rebased to First’ gives a scale relating to the first series (FTSE100). To do this, run a search for the FTSE100 data first and subsequently the companies also part of the index. Changes to the search above include:
Data Category: ‘Equity Indices’, Datatype: ‘Price Index’ and run the search for data.
Next, change back to the settings for Companies (Data Category: Equities, Datatype: Price (Adjusted Default), run the searches for data and add to the chart summary box [ + ].
Next, click on the ‘Run Now’ button to execute the search.
This chart gives a clear graphical representation of the relative performance of company Share Prices and the FTSE100 Price Index. The competition from low cost retailers such as Aldi and Lidl is a factor in the poor recent performance of Tesco PLC and J Sainsbury PLC in particular, reflected in the above chart.
Datastream is available to current students and staff of The University of Manchester.
A detailed source of environmental, social and governance (ESG) data can be found in Thomson Reuters Datastream.
Instead of choosing the appropriate datatypes in a regular Datastream time series request, you can download the ASSET4 template. This is hard to find in the InfoBase support platform (replacement for Extranet) so I suggest you get it from the Datastream Excel add-in toolbar, although this method is not quite as easy as you would like.
Open Datastream in Excel the usual way (remember the desktop icon “DSSetup – Shortcut”). Click on the Datastream ribbon tab, go to the Request Tables group and click Sample Sheets. Tick the button ‘Equities : ESG ASSET4 – Sector Industry analysis’ then click the Download button.
The Download & Open button does the same as the Download button, it doesn’t open the file.
The template takes the form of a macro-enabled Excel workbook and is downloaded to a hidden folder. Click on Start > Computer, click in the address bar and type the following:
Note that ‘ProgramData’ is not the same as ‘Program Files’ or ‘Program Files (x86)’, it is a hidden folder. As you type the rest of the path, Windows should suggest auto-completions, which you can accept.
From here, open the file and accept any warnings about enabling content.
There are many sheets (tabs), starting with Home. Click on ESG Filters to select your search criteria. Choose (1) a sector or industry such as Telecommunications Services and (2) criteria across the environmental, social and corporate governance categories such as Water Use Total, Women Managers and Size of Board. When ready, click the Go button.
Wait a few minutes while the data is downloaded to the Data Table sheet.
The tables and charts of the many other sheets are automatically updated to reflect your search (except the Data Fields definitions sheet). For example, the Bottom 5’s sheet.
If you find a datatype you would like to use in a regular time series request, make a note of its code.
See also earlier post Environmental, Social and Governance (ESG) Data from July 2013.
One of the most useful Excel functions I help students with is VLOOKUP – extremely useful to automatically bring in data from one table to another. If you have company accounts data from Compustat Global (via WRDS) for multiple companies in multiple currencies, you will need to bring in the change rate for each currency to show every field in US Dollars.
|1||Company||Currency||Total Assets Local||Exchange rate||Total Assets USD|
|2||Co. X||AUD||100||=VLOOKUP(B2, currencies, 2, FALSE)||=C2*D2|
|3||Co. Y||PLN||400||=VLOOKUP(B3, currencies, 2, FALSE)||=C3*D3|
Where ‘currencies’ is a named range elsewhere in the workbook:
The VLOOKUP command looks for the currency code in the first column and returns the rate from the second column (use ‘FALSE’ to ensure an exact match). The conversion is simply a multiplication. (Note that you can convert from USD to USD at a rate of 1.0.) But what about making sure that the exchange rate is correct for the date of the data, and what if you have data from multiple years?
Performing VLOOKUP when matching a pair of variables
Thanks to Professor Marie Dutordoir for suggesting this kind of technique!
Usually you will want to look at accounts data over several years and for several companies. If you are working with several currencies then you will need a more complicated solution to this VLOOKUP exchange rate table, considering a day/month/year for each rate-currency pair. You do not need a second lookup table or other Excel functions, you just need to have another variable of date and a variable of currency-date pairs.
The new column F (mapping) is formed from currency and year, so F2 contains “=G2&H2” (see image above). The ‘currencies’ named range now begins with a different first column produced in the same way (see image below). A VLOOKUP always matches on the first column of its range, and now this one pulls the rate from the fourth column, so rate is “=VLOOKUP(F2, currencies, 4, FALSE)” for row 2.
In the ‘currencies’ named range, I have combined the exchange rate calculation and lookup into one formula (column K). Repeat this for each data type that you wish to convert the currency for. Remember to have an entry for USD at rate 1 for each year.
This example only has the average exchange rate for each year, you may wish to extend this approach for monthly or even daily rates. If you do this, you will need to take extra care with the format of the mapping cells, but this exceeds the scope of the post.
Where to find historical exchange rates?
- Datastream (as shown in the screen above).
- The documentation for Compustat suggests using WRDS Cloud, but this requires knowledge of Unix and SAS, similar to the S&P 500 constituents example.
- Other sources of historical exchange rates (last updated July 2015).
Identifiers can take many forms (e.g. Company Name, Ticker, CUSIP, SEDOL, Datastream Code, ISIN) and be national, such as CUSIP for US / Canadian companies or international, such as ISIN (International Security Identification Number). The ISIN code incorporates the national identifier. For example, for Tesco PLC: GB0008847096, which includes the SEDOL (UK company identifier) code: 0884709.
Company Lists and Datastream
In seeking to further their dissertation research, MSc students often make use of more than one research database. Consequently, a typical scenario involves a request to import a list of company identifiers into Datastream, then conducting further searches to secure data, prior to analysis. The ‘Create List (From Range)’ function is useful in this regard.
Create List (From Range)
This function within the Excel Add-In version of Datastream is extremely effective when working with lists (up to 5,000 Series [companies] per list). This enables Company Identifiers to be copied and pasted into Excel and saved as a List on the specific computer being used (Store List Locally) or saved to the Datastream mainframe computer (Upload List) – accessible from computers with Datastream installed.
The code generated to represent the list of companies can then be entered directly into a ‘Static‘ or ‘Time Series‘ request search screen in Excel. Hence, one or more Datatypes can be obtained for an entire list of companies and the results would be displayed in a single Excel sheet.
Company Identifiers which work with the ‘Create List (From Range)’ function include: ISIN, Ticker, Datastream Code. Both Company Name and CUSIP don’t work, even though CUSIP is meant to.
Within Excel, with the Datastream tab selected, enter (paste in) the company identifiers. Next, Select the identifiers (ISINs below) and then click on ‘Create List (From Range)’. To illustrate the process, a short list of two US companies will be used (Apple, Microsoft) – this could of course be a list of many hundreds of companies.
From the ‘List Creation for Excel’ dialog box, the default option is ‘Store List Locally’ (on the computer being used at the time), with ‘Upload List’ as an option.
This generates a confirmation message – click ‘OK’.
The ‘List File Name’ [ New_0032.LLT ] is entered in the Series/Lists field in this Static Request (as at 03/06/2016) to locate: Company Name [NAME], CUSIP [WC06004], Ticker [WC05601], Datastream Code [DSCD] and Market Value [MV].
Results: widen columns as necessary, to display data.
It would be possible to repeat the above process, to create lists with different identifiers (e.g. Datastream Code, Ticker) and also select ‘Time Series’ request for historical data. For Ticker identifiers, it is advisable to format the cells in the Excel column to ‘Text’ (before identifiers are pasted in) where US companies can have an ‘@’ symbol as part of the identifier (e.g. @AAPL, for Apple). If the cells are not formatted as Text, the ‘@’ symbol will be interpreted by Excel as the start of a function.
If a list contains both US and UK Ticker identifiers, this can cause a problem where an incompatible datatype is specified in a search request. For example, if CUSIP (datatype: WC06004) is included, this means the search will fail (i.e. it gives no results), as UK companies do not have CUSIP codes.
Converting from Ticker to Datastream Code identifiers is a way around this difficulty. The Datastream Code identifiers could then be used to create a new list: for Apple (992816), Microsoft (719643), Tesco (900803) and J Sainsbury (926002).
A Static Request (03/06/2016) search for datatypes: Company Name, CUSIP and Market Value is successful (i.e. it gives results) and merely has a blank cell under CUSIP for Tesco and J Sainsbury.
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
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.