helper function to create 5253WW calendar lookup

CustomCalendar.py

"""
Make a dataframe for a 52/53-workweek accounting period calendar given the last day of the previous year.
Since many companies use this 52/53 workweek for their fiscal periods, this is a useful function to quickly
build custom lookup tables so you can join between datasets!
"""

import pandas as pd
from datetime import datetime
import numpy as np


def create_5253_calendar(start='2020', periods=3, weekday=5, startingMonth=12, variation='last'):
    """
    Builds a lookup dataframe with calendar date on the index and relevant fiscal dates for a 52/53 workweek
    aka 4-4-5 fiscal calendar. 
    
    See https://pandas.pydata.org/docs/reference/api/pandas.tseries.offsets.FY5253.html for info on the 
    pandas 52/53 workweek dateoffset object.
    
    Parameters
    ----------
    start : str, datetime, or pd.Timestamp object (default : '2020')
        Indicate the calendar year (i.e. '2020') or datetime/timestamp for the final output dataframe
    periods : int (default : 3
        Number of fiscal years ahead to build the output dataframe
    weekday : int (default : 5)
        Which day of week the fiscal year ends on
        See also: https://pandas.pydata.org/docs/reference/api/pandas.tseries.offsets.FY5253.html
        * 0 : Monday
        * 1 : Tuesday
        * 2 : Wednesday
        * 3 : Thursday
        * 4 : Friday
        * 5 : Saturday
        * 6 : Sunday
    startingMonth : int (default : 12)
        Following the pandas convention, but this actually signals the 'ending month' for the fiscal year. So a fiscal year
        that ends on the last Saturday of every December would have a value of 12 here.
    variation : str (default : 'last')
        Either 'last' or 'nearest' - parameters for the pandas FY5253 offset that specifies the type of 5253 calendar this is.
        If it's 'last', then the fiscal year ends on the 'last' `weekday` of the `startingMonth`.
        If it's 'nearest', then the fiscal year ends on the 'nearest' `weekday` of the `startingMonth`. 
        
    Returns
    -------
    pd.DataFrame
        Returns a dataframe with a daily DatetimeIndex and columns that map to key fiscal timelines for
        a 52/53 workweek calendar, such as DOY (day of fiscal year), WW (workweek in fiscal year), etc
        
    Examples
    --------
    >>>create_5253_calendar(start='20', periods=5, weekday=5, startingMonth=12, variation='last')
        
    """
    # create an offset for the Intel fiscal calendar, which ends on the last Saturday of December
    yoffset = pd.tseries.offsets.FY5253(n=1, weekday=weekday, startingMonth=startingMonth, variation=variation)

    # use the offset to create a date range with intervals at each fiscal year beginning
    yoffset_range = pd.date_range(start=start, periods=periods, freq=yoffset)    
    
    # Create a '1 day' offset:
    d1 = pd.tseries.offsets.DateOffset(n = 1)

    # Use our business acumen to specify what that first fiscal year was. '2020-12-27' is the first day of fiscal year 2021 for Intel, 
    # while '2021-12-26' was the first day of fiscal year 2022
    yr = (yoffset_range[0] - pd.tseries.offsets.DateOffset(n=8)).year +1

    # iterate over each item in the date range we created earlier:
    result = pd.DataFrame()
    for i in yoffset_range:
        # recall each item is a Timestamp that represents the first day of the fiscal year,
        # so create a date_range from beginning to end of the fiscal year
        current_range = pd.date_range(i+d1, i+yoffset, freq='D')
        interim_df = pd.DataFrame(index = current_range)
        # day of year
        interim_df['DOY'] = (current_range-current_range[0]).days +1
        # fiscal year
        interim_df['FY'] = yr
        result = result.append(interim_df)
        yr += 1

    # workweek in year
    result['WW'] = ((result['DOY']-1) // 7) + 1
    # fiscal quarter
    result['FQ'] = np.minimum(((result['WW'] - 1) // 13) + 1, 4)
    # workweek in quarter
    result['WWinQ'] = result['WW'] - ((result['FQ'] - 1) * 13)
    # fiscal month
    result['FM'] = ((result['FQ'] - 1) * 3) + np.minimum(((result['WWinQ'] // 4) + 1), 3)

    return result

Hey very useful thanks a lot. However I think I found 1 error:

# fiscal quarter
result["FQ"] = np.minimum((result["WW"] // 13) + 1, 4)

should be

# fiscal quarter
result["FQ"] = np.minimum(((result["WW"] - 1) // 13) + 1, 4)

without the 13th week is left out

# wrong
(13  // 13) + 1 -> 2
# ok
((13 - 1) // 13) + 1 -> 1

Hey very useful thanks a lot. However I think I found 1 error:

# fiscal quarter
result["FQ"] = np.minimum((result["WW"] // 13) + 1, 4)

should be

# fiscal quarter
result["FQ"] = np.minimum(((result["WW"] - 1) // 13) + 1, 4)

without the 13th week is left out

# wrong
(13  // 13) + 1 -> 2
# ok
((13 - 1) // 13) + 1 -> 1

Thanks for this, fixed the gist!