Python/Pandas: Find the Custom Business Quarter End of a datetime which takes holidays into account

user_11745269 0
Viewed 474

I want to find the Business Quarter End of a datetime in python which will take care of holidays as well. These holidays may be passed as list for simplicity. I know BQuarterEnd() from pandas.tseries.offsets. As far as I know, it doesn't take holidays into account.

Example: If 2020-11-20 is passed and 2020-12-31 is a business day but a holiday as well; it should return 2020-12-30.

Thanks.

pandas time-series date datetime python
2 Answers

In Pandas, there are a set of Custom business days functions where you can define your own list of holidays and then the functions calculate the correct date offsets for you, taking into account the custom holiday list.

For example, we have CustomBusinessMonthEnd (better documentation here). Unfortunately, there is no corresponding CustomBusinessQuarterEnd (Custom Business QuarterEnd) function for quarter end.

However, we can still get some workaround solution, like below:

  1. Define your custom holiday list, e.g. :
holiday_list = ['2020-12-31']
  1. Make use of a combination of QuarterEnd + CustomBusinessMonthEnd to get the required date for Custom Business QuarterEnd skipping the holidays:
import pandas as pd

base_date = pd.to_datetime('2020-11-20')   # Base date

custom_business_quarter_end = (base_date 
                                   + pd.offsets.QuarterEnd(n=0) 
                                   - pd.offsets.MonthBegin() 
                                   + pd.offsets.CustomBusinessMonthEnd(holidays=holiday_list))

Firstly, we add your base date to the QuarterEnd to get the quarter end date (without considering holidays). Then, to get the Custom Business QuarterEnd skipping the holidays, we use the CustomBusinessMonthEnd passing also the holiday list as parameter for it to adjust for the holidays.

For QuarterEnd, we pass the parameter n=0 to handle the edge case where the base date is already on the Quarter End date. We avoid QuarterEnd to rollover this quarter end date to the next quarter end date. You can refer to the official doc here to know more about how Pandas handles dates falling onto anchor dates (see the subsection starting with "For the case when n=0, ...")

We also make use of MonthBegin first before calling CustomBusinessMonthEnd. This is to avoid rolling over of a day at month-end anchor to the next month. We need this because the n=0 parameter does not work similarly for CustomBusinessMonthEnd like how it works for QuarterEnd to avoid rolling over. Hence, this extra minus MonthBegin is required. With the use of MonthBegin, we get the month begin date of the quarter-end, i.e. 2020-12-01 first, and then get the custom business month-end date. In this way, we can avoid the result of QuarterEnd e.g. 2020-12-31 being rolled over to the next month end e.g. 2021-01-31 when directly calling CustomBusinessMonthEnd.

Result:

print(custom_business_quarter_end)

2020-12-30 00:00:00
user_150706970

You probably need a custom function. Maybe something like:

def custom_quarter_end(date, holidays=[]):
    holidays = [pd.Timestamp(h) for h in holidays]
    end = pd.Timestamp(date)+pd.tseries.offsets.BQuarterEnd()
    while end in holidays:
        end = end - pd.tseries.offsets.BDay()
    return end

>>> custom_quarter_end("2020-11-20", ["2020-12-30", "2020-12-31"])
Timestamp('2020-12-29 00:00:00')
user_98576310
Related