All

How to create calendars (in finance)

mgreco

22/05/2019

No Comments

In finance, it’s very important to know how to handle dates properly. Within the management of dates, the creation of calendars is something that is not always easy to solve. In this post we will find an alternative to carry out this task efficiently.

Given an event that repeats itself in a recurrent way, we call “calendar” to the set of dates in which this event takes place. Think, for example, of someone’s birthday. The “birthday” event is repeated every year, starting from that person’s date of birth.

In order to define this task programmatically, a very extended standard was created: the rfc5545 where the recurrence rules (RRULES) are defined in it. This standard is used by many applications that handle notifications or alerts, such as Google calendar.

RRULES in Python

The definition of the recurrence rules allows you to define the occurrence of almost any event. Returning to the example of the birthday, let’s think of someone who was born on April 20, 1990. The RRULE explained textually, could be defined with the following points:

  1. The “birthday” event begins on 1990/04/20.
  2. The event has an annual frequency.

Python Implementation:

In Python, there is an implementation of the RRULES within the dateutil library.

>>> # some imports
>>> import pandas as pd
>>> import numpy as np
>>> from dateutil import rrule
>>> from datetime import datetime
>>> from dateutil.rrule import YEARLY

The RRULE of the event “birthday” can be defined as follows:

>>> dtstart = datetime(1990, 4, 20)
>>> birthday_rrule = rrule.rrule(
...     YEARLY,
...     dtstart=dtstart,
...     count=10,
...     )
>>> birthday_list = list(birthday_rrule)
>>> birthday_list
[datetime.datetime(1990, 4, 20, 0, 0),
 datetime.datetime(1991, 4, 20, 0, 0),
 datetime.datetime(1992, 4, 20, 0, 0),
 datetime.datetime(1993, 4, 20, 0, 0),
 datetime.datetime(1994, 4, 20, 0, 0),
 datetime.datetime(1995, 4, 20, 0, 0),
 datetime.datetime(1996, 4, 20, 0, 0),
 datetime.datetime(1997, 4, 20, 0, 0),
 datetime.datetime(1998, 4, 20, 0, 0),
 datetime.datetime(1999, 4, 20, 0, 0)]

So we have the calendar of 10 elements of the event “birthday”. Alright! Now let’s see a more complex example of a financial calendar.

A practical example: Delivery day of Euro-Bobl Futures

We are going to create the calendar of the event “Delivery day of the euro-bobl future”. This calendar has a more complicated pattern that is quite interesting to implement in finance. In the website of the future, we can find a textual definition of the event frequency:

“The tenth calendar day of the respective quarterly month, if this day is an exchange day; otherwise, the exchange day immediately succeeding that day.”

To define a rrule in Python, we need a frequency of occurrence, a start date (an anchor). For more information see the datetuilt.rrule documentation. As in the birthday example, the above description provides us all the necessary information to start working:

“The tenth calendar day of the respective quarterly month”

  • The event has a quarterly frequency or which is equivalent, a monthly frequency but selecting specific months.
  • The event occurs every 10th day of each month.
>>> from dateutil.rrule import MONTHLY
>>> dtstart = datetime(2018, 1, 1)
>>> delivery_rule = rrule.rrule(
...     MONTHLY,
...     dtstart=dtstart,
...     count=10,
...     bymonth=(3, 6, 9, 12),  # quarters
...     bymonthday=10,          # 10th of each month
...     )
>>> dt_colection = list(delivery_rule)

At this point, we already have a calendar of 10 dates. Let’s check if these are really the dates we want. For example, we’re going to check if every date is a business day. For this, we’re going to use the DatetimeIndex from Pandas which  provides us the vectorized method day_name().

>>> vals = [delivery_rule, pd.DatetimeIndex(delivery_rule).day_name()]
>>> pd.DataFrame(vals, index=["date", "name"]).T
                  date       name
0  2018-03-10 00:00:00   Saturday
1  2018-06-10 00:00:00     Sunday
2  2018-09-10 00:00:00     Monday
3  2018-12-10 00:00:00     Monday
4  2019-03-10 00:00:00     Sunday
5  2019-06-10 00:00:00     Monday
6  2019-09-10 00:00:00    Tuesday
7  2019-12-10 00:00:00    Tuesday
8  2020-03-10 00:00:00    Tuesday
9  2020-06-10 00:00:00  Wednesday

At first glance we can see there are days that are not business days, so we will have to apply a moving rule over dates to fix them. The second part of the rule, which we have collected before,  is relevant for this task.

“if this day is an exchange day; otherwise, the exchange day immediately succeeding that day.”

  • If the date is not weekday then we move to the next weekday.

For this we created a small function that will help us with this task:

>>> from datetime import timedelta
>>> def apply_forward(rrule):
...     """Apply Forward rule if its needed.
...
...     Parameters
...     ----------
...     rrule : rrule.rrule or list
...
...     Yield
...     -----
...     dt : datetime
...     """
...     for dt in rrule:
...         wday = dt.weekday()
...         if wday > 4:
...             dt += timedelta(days=7-wday)
...         yield dt
>>> delivery = list(apply_forward(delivery_rule))
>>> vals = [delivery, pd.DatetimeIndex(delivery).day_name()]
>>> pd.DataFrame(vals, index=["date", "name"]).T
                  date       name
0  2018-03-12 00:00:00     Monday
1  2018-06-11 00:00:00     Monday
2  2018-09-10 00:00:00     Monday
3  2018-12-10 00:00:00     Monday
4  2019-03-11 00:00:00     Monday
5  2019-06-10 00:00:00     Monday
6  2019-09-10 00:00:00    Tuesday
7  2019-12-10 00:00:00    Tuesday
8  2020-03-10 00:00:00    Tuesday
9  2020-06-10 00:00:00  Wednesday

We now have our “delivery day of Euro-bobl future” event calendar implemented. We could think that this can surely be done with Pandas, after all, there are very few things that will resist Pandas.

Alternative in Pandas

We’re going to build a calendar of the same previous event using Pandas.

>>> import pandas as pd
>>> drange = pd.date_range("2018-03-01", periods=10, freq="3M")
>>> drange += pd.offsets.DateOffset(day=10)  # move each date towards 10th
>>> drange -= pd.offsets.BDay(0)             # ensure we have a business day
>>> pd.DataFrame([drange, drange.day_name()]).T
DatetimeIndex(['2018-03-12', '2018-06-11', '2018-09-10', '2018-12-10',
               '2019-03-11', '2019-06-10', '2019-09-10', '2019-12-10'],
              dtype='datetime64[ns]', freq=None)

This version could seem more user-friendly, however, if we think about it, it has a problem: to build this Pandas calendar it has to pass three times through the same dates, while RRULES passes twice. Also, it is necessary to consider how unorthodox it is to add the offset of 0 business days.

Performance Comparison

Let’s compare both versions to see if our previous hypothesis holds.

>>> def build_delivery_rrule():
...     dtstart = datetime(2018, 1, 1)
...     delivery_rule = rrule.rrule(
...         MONTHLY,
...         dtstart=dtstart,
...         count=10,
...         bymonth=(3, 6, 9, 12),
...         bymonthday=10,
...     )
...     dt_colection = list(delivery_rule)
...     delivery = list(apply_forward(delivery_rule))
...     return pd.DataFrame([delivery, pd.DatetimeIndex(delivery).day_name()]).T
>>> def build_delivery_pd():
...     drange = pd.date_range("2018-03-01", periods=10, freq="3M")
...     drange += pd.offsets.DateOffset(day=10)  # move each date towards 10th
...     drange -= pd.offsets.BDay(0)             # ensure we have a business day
...     return pd.DataFrame([drange, drange.day_name()]).T

>>> %timeit build_delivery_pd()
6.99 ms ± 297 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

>>> %timeit build_delivery_rrule()
2.97 ms ± 324 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

You can see that the RRULE version has better performance than the Pandas version, and maybe it is more intuitive as well (this is, of course, subjective).

Summarizing, we have seen two ways to create calendars in a relatively simple way. The RRULE is a very powerful tool for this kind of task, and with a little practice, you can do more complex things. If you know an alternative to creating calendars don’t hesitate to share your implementation or libraries down below.

Resources

If you want to go more deep about RRULE, I let you below a list of interesting resources.