Using Excel for event windows & Datastream

A recent question I got had to do with the event window that a student wanted to use for an event study. For the research model the student wanted price data for three days: the price of a stock on the day before the event date, the price on the day of the event date, and the price the day after the event date. Using a Request Table search in Datastream the price used (in this case) was the default padded closing price: P. On regular weekdays (Mondays to Fridays) the variable P will always provide a price: on bank holidays the price of the last trading day is repeated until a new trading day provides a new price. Using Excel the window can be calculated and the Request table search screen would look as follows:

Unfortunately, using the standard option in Excel to calculate the event window does not help when you need exactly three prices as a window for each equity. The search result would look as follows:

Using the standard option you will sometimes get 1 or 2 prices instead of 3. This is easily explained when you look at the specific original event dates: weekend days will cause missing values. The solution is, to correct for this using the Excel function IF which can be compared to the program function: If .. Then .. Else ..

The Excel formula looks as follows: =IF(Condition, Then X, Else Y)

For more conditions you can use the nested IF and this would look as follows:
=IF(Condition1, Then X, IF(Condition2, Then Y, Else Z))

In the abovementioned scenario of 3 days the formula needs to be used for the starting date as well as the ending date.

Before you want to use the formula, you first need to figure out when you need to correct dates. To do this, you can use the Excel function =Weekday() for the event days. The function will give a value of 1 – 7 for each weekday:
1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

The formula I chose for the start date was =IF(F3=1,B3-2,IF(F3=2,B3-3,C3))
It is based on the premiss: if the start weekday falls on a weekend day, I want the price of the last weekday (Friday).

The formula I chose for the end date was =IF(F3=1,B3+2,IF(F3=7,B3+3,IF(F3=6,B3+3,D3)))
It is based on the premiss: with the (recalculated!) start date in mind I need to add 1 or more days to get an event window of exactly three days.

You can download an example in Excel here.

If I now use the new recalculated/corrected dates (with the abovementioned formulas) in a Request table search in Datastream I get Price data for exactly three days. Example search result:

1) The formulas in the abovementioned example only apply to the situation where the event window is exactly three days. If your event window is larger you need different nested IF formulas based on the same principle of Weekdays!
2) When you need to use the unadjusted unpadded (historical) price in Datastream (= UP) you also need different formulas when your model requires an exact number of prices.
3) If a stock is listed on a stock exchange with different weekend days Datastream adjusts this to make prices for all exchanges comparable. See also this blog post.


Auditor tenure as a (control) variable

When doing research on companies one of the variables that could be interesting to take into account when analyzing company data is the tenure of the company auditor. Not many databases include information on the auditor of companies for each year. A few examples are: Audit Analytics, Amadeus, Compustat North America and REACH.

The database Audit Analtics covers only the companies that file reports at the SEC (many companies from the US) but it does include a few variables to easily determine the Auditor tenure when combined:

  • LAST REPORTED AUDITOR FKEY = Unique numeric identifier for each auditor
  • LAST REPORTED AUDITOR NAME = Name of the auditor to which the fees in the data row correspond
  • LAST REPORTED AUDITOR SINCE EVENT DATE = known engagement. The auditor since date comes from either the historical auditor databases (i.e. auditor changes) or from directly inquiring with the public registrants.
  • LAST REPORTED AUDITOR SINCE EVENT TYPE = Qualifier field for “Auditor Since Year”. The field can come with two values “Since” or “Since at Least”. “Since” indicates that the auditor was first engaged in the year given in “Auditor Since Year” and has been the auditor since then. “Since at Least” indicates that the auditor has been engaged since the year given in “Auditor Since Year” however we have not confirmed that this was the first year of  engagement.

The first two identify a specific Auditor. The last two identify how long the tenure was/is.
Example data a for a company:


WorldScope coverage update 3rd Quarter 2014

WorldScope company records now cover annual reports data for 76530 companies. This includes 46120 active and 30410 inactive companies. This update: 358 companies were added. Because of these additions the number of active companies in the database has gone up 22 from 46098 in March. This is the first update in 6 months’ time that the total number of active companies went up. WorldScope company records are available through Datastream and LexisNexis.

Today I have updated the WorldScope country coverage file and it now includes the latest update as it was posted in the third Thomson Reuters Infostream quarterly publication of 2014.

Major updated Countries (new records):
Australia (16)
Canada (14)
China (19)
Hong Kong (13)
Japan (18)
South Korea (16)
United Kingdom (39)
United States (133)


Bitcoin exchange rates on Datastream

The past year the digital currency Bitcoin has increasingly caught the eye of governments as well as the investment world. I have posted two items in the past on the Bitcoin phenomenom:

1) A basic post on the availability of Exchange rate data through divers exchanges.

2) A specific post on the Bitcoin price and the Bitcoin Price index

Thomson Reuters has now decided to make available exchange rate data for the Bitcoin to US Dollar through the Datastream system. The exchange rate data is delivered by the Bitcoin UK platform Bitstamp Ltd. It can be argued that the delivery of data by just one provider may give a biased view on the developments. In this case it may be a good idea to check out the Coindesk price. The data from the exchange Bitstamp Ltd is currently included in the calculation of the Bitcoin Price Index of Coindesk.

Datastream mnemonic : BTCTOU$
Start date: 18/08/2011
Frequency of update: end of day

The available Datastream time series datatypes are:

  • (ER) Exchange Rate Middle, in this case the last BTC price on the day
  • (EH) Exchange Rate Intra-day High
  • (EL) Exchange Rate Intra-day Low
  • (VO) Volume of Bitcoins traded.


Price data adjustments & Compustat

From the moment a company is listed at a stock exchange changes in these listings can occur and are announced by the companies in press releases and such. Some of these changes include stock-splits or the issue of additional stocks. There can be many possible reasons for doing this.
Regardless of the reasons, these changes have effect on the value of stocks. As I mentioned in the previous post Compustat delivers the original price data through it’s annual, monthly and daily financial databases. These databases also include adjustment factor variables that makes it possible to adjust the price data for calculations. An overview of the adjustment factor variables is available in the Compustat manual “Understanding the data” on page 91 (Chapter 6).
If you have access to Compustat through Wharton you can find the manual there at: > Support > Compustat manuals and overviews.

I will give an example here. One of the variables in de Compustat North America Fundamentals Annual database is CEQL (Common Equity Liquidation Value). This item represents the common shareholders’ interest in a company in the event of liquidation of company assets. Common equity is adjusted by the preferred stockholders’ legal claims against the company. The CEQL data is rounded.
The calculated variable CEQL can be recreated by downloading and multiplying the variables Book Value Per Share (BKVLPS) and Common Shares Outstanding (CSHO). To get correct numbers you will have to use the adjustment factor variable Adjustment Factor (Company) – Cumulative by Ex-Date (AJEX) in this case. With regard to the adjustment factor it is important to remember that, in general, per share items are divided and share items are multiplied by the adjustment factor. Example:

In column L you see the Adjustment Factor for each year of data. If nothing changes the value is 1. If, for instance, the value is 0.1 this means there was a small adjustment. Columns T, U and V list the following variables from the Compustat database BKVLPS, CSHO and CEQL. Column W lists the Calc(ulated) value of CEQL using just the variables BKVLPS and CSHO.
For some years for the example Citigroup the adjustment factor is 0.1 which means that the calculation needs to be changed. The adjusted calculated CEQL is: ((BKVLPS/AJEX)*(CSHO*AJEX)). See column X in the example.


Price data in Datastream and Compustat

There are sometimes diferences when you download similar type data from databases. For example, depending on which source you choose for regular prices of stocks, the result may be different from what you might expect. In this case I am not talking about the possibility that you might get price data in different currencies in a download. Currencies, may also be a reason for surprising result but by carefully studying the database (and including currency as an item in your download) you may easily avoid falling into this trap as price series with different currencies become identifiable.

I recently downloaded prices from datastream using the default (closing) price, which is variable P. The resulting data looked as follows:

Some of the prices appear to be very high compared to others: see highlighted series. The main reason for this is that the standard default variable Price (P) is an adjusted price in Datastream! This means that stock prices are adjusted for stock splits and many other changes to make every price comparable over time. The second main reason may be the act that the Price (P) is padded. Padded means that on workdays that are bank holidays, prices are repeated from the last trading date until a new trading day/date occurs. In essence, on a workday that is not a trading day, you will still get a price.

For some research it may be necessary to use the original, unadjusted and unpadded price from Datastream. In this case you should use the price UP = Unadjusted Price. In addition to using this price you have to add something to the variable to make sure that No Padding takes place. In Datastream you should add the Hash sign and the letter S. The full variable / data type would look like: UP#S
When you now download the original unpadded data the result will look very different:

If you are using Compustat to get your prices for companies you do not need to worry about choosing a type of Price variable in the Security Daily or Security Monthly part databases. There is only one Closing Price option: PRCCD — Price – Close – Daily.
All price data (closing price or otherwise) from Compustat is unadjusted and unpadded! This means that you do not have to use a trick to get this type of price. However, should you want to adjust the price data (for stock splits etc.) you have to also download and use the adjustment factor variable. When you have downloaded these adjustment factor data too, you can start to recalculate prices yourself: Adjusted Price = (PRCCD / AJEXDI ) where AJEXDI = Cumulative Adjustment Factor (Issue) Ex-Date.

An overview of these adjustment factor variables is available in the Compustat manual “Understanding the Data” on page 91 and on (chapter 6). If you have access to Compustat through Wharton you can find the manual at:
> Support > Compustat manuals and overviews