Business calender: Shifting missing dates

[Marc Pelow]

Hello,

I do not use the business calender function very often and assume that my question isn't a really hard one. Anyway, I am struggling to shift missing business calender dates to the next business/trading day. I created my business calender using a long time series of daily returns on a stock market index:

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 Deal_No int Deal_Announced
"2508865020" 19464
"2588919020" 19693
"2431881020" 19183
"2463387020" 19295
"2366354020" 19005
"2808152020" 20635
"2348789020" 18898
"2527238020" 19513
"2363242020" 18948
"2406806020" 19101
"2551522020" 19571
"2683664020" 20004
"2728048020" 20145
"2712356020" 20093
"2336968020" 18854
"2525219020" 19506
"2437828020" 19204
"3145754020" 21067
"3091683020" 20920
"3212518020" 21223
end
format %tdDD.NN.CCYY Deal_Announced

gen business_date = bofd("sp500", Deal_Announced)
format business_date %tbsp500:DD.NN.CCYY
generate date_low = string(business_date-1, "%tbsp500:DD.NN.CCYY")
generate date_high = string(business_date+1, "%tbsp500:DD.NN.CCYY")

If the Deal_Announced date is a non-trading day such as Saturday or Sunday, these will be missing in the business_date variable. However, instead of generating missing values, I would like to shift these days to the next trading day so that the second Obs. "01.12.2013" (Sunday) is shifted to "02.12.2013" (Monday). Is there a way I can achieve this without manually shifting the Deal_Announced date?

Thanks

[Kreshna Gopal (StataCorp)]

Marc Pelow, the problem is in this line of code:

generate date_high = string(business_date+1, "%tbsp500:DD.NN.CCYY")

If business_date is missing, that line will generate missing values. So, you should be incrementing Deal_Announced rather than business_date. Here I assume you want to find date_high only if business_date is missing. So, this should work:

generate date_high = string(bofd("sp500", Deal_Announced+1), "%tbsp500:DD.NN.CCYY") if missing(business_date)

However, incrementing the business date by 1 may not be enough, in general. There may be two or more consecutive holidays. Here is a more robust solution. The business calendar file I use here, sp500.stbcal, looks like this:

* Business calendar "sp500"

version 12.1
dateformat ymd

range 2011jan03 2018dec31
centerdate 2011jan03

omit dayofweek (Sa Su)
omit date 2011dec25

It is a simple one with Saturdays, Sundays, and one Christmas day marked as holidays. After you create your business calendar file, please use bcal load or bcal describe to make sure there are no inconsistencies in your calendar file.

The following code generates two business dates variables, previous and next, for the previous and next working dates. In your example, for a Sunday (day of week, dow = 0), the next working day is Monday, and the previous is Friday. In this solution, next and previous are the same as business_date unless the latter is missing. Also, it assumes there exists previous and next business days, otherwise it'll keep searching indefinitely.

input str10 Deal_No int Deal_Announced
"2508865020" 19464
"2588919020" 19693
"2431881020" 19183
"2463387020" 19295
"2366354020" 19005
"2808152020" 20635
"2348789020" 18898
"2527238020" 19513
"2363242020" 18948
"2406806020" 19101
"2551522020" 19571
"2683664020" 20004
"2728048020" 20145
"2712356020" 20093
"2336968020" 18854
"2525219020" 19506
"2437828020" 19204
"3145754020" 21067
"3091683020" 20920
"3212518020" 21223
end
format %tdDD.NN.CCYY Deal_Announced

gen dow = dow(Deal_Announced)

gen business_date = bofd("sp500", Deal_Announced)
gen previous = business_date
gen next     = business_date

format business_date previous next %tbsp500:DD.NN.CCYY

local i = 1
count if missing(previous)
while (r(N) > 0) {
  replace previous = bofd("sp500", Deal_Announced-`i') if missing(previous)
  local i = `i'+1
  count if missing(previous)
}

local i = 1
count if missing(next)
while (r(N) > 0) {
  replace next = bofd("sp500", Deal_Announced+`i') if missing(next)
  local i = `i'+1
  count if missing(next)
}