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.


%d bloggers like this: