Settlement Calculation if there are consecutive days holiday and weekends

[ShaileshDevadiga]

Hi All.

I am facing issue in regard to settlement date calculating specially when there is consecutive holidays

I get Trade Date from source, so settlement date is calculated as Trade Date +2 days, so to get settlement date with taking weekend ( Saturday and Sunday ) into consideration, I use below formula which works fine
if datetimeformat([Trade Date],'%A')="Friday" then datetimeadd([Trade Date],4,'days') elseif datetimeformat([Trade Date],'%A')="Thursday" then datetimeadd([TradeDate],4,'day') else datetimedd([Trade Date],2,'day') endif

Then to consider holidays, i concanete holiday list and then check settlement date is there in the holiday list, if there then updated settlement date will be +1 days.
But I am struggling when there is consecutive holidays.
For example, below are list of holidays and what I am getting as settlement date.
Trade booked on 1st May, settlement date is 3rd May but since its holiday from 3rd May to 5th May and then Saturday and Sunday, it should capture settlement date as 8th May, same for trade booked on 2nd May, should capture settlement date as 9th May.

Holidays
2023-01-02
2023-01-03
2023-01-09
2023-02-23
2023-03-21
2023-05-03
2023-05-04
2023-05-05
2023-07-17
2023-08-11
2023-09-18
2023-10-09
2023-10-10

Trade Date	Settlement Date	Correct Settlement Date	What I am getting
2023-04-27	2023-05-01	2023-05-01	2023-05-01
2023-04-28	2023-05-02	2023-05-02	2023-05-02
2023-05-01	2023-05-03	2023-05-08	2023-05-04
2023-05-02	2023-05-04	2023-05-09	2023-05-05
2023-05-08	2023-05-10	2023-05-10	2023-05-10
2023-05-09	2023-05-11	2023-05-11	2023-05-11
2023-05-10	2023-05-12	2023-05-12	2023-05-12

[Yoshiro_Fujimori]

Hi @ShaileshDevadiga ,

I generated a table of business day with serial number starting from 2023-01-01.

Then calculate the serial number of [Trade Date + 2] and use it to get the Settlement Date.

Workflow

Output

I hope this meets your requirement.

[Raj]

Great logic , was trying something like this 

[Christina_H]

Here's another version, adapted from a solution to a different question.  It calculates the next working day for each holiday, taking into account the other holidays.  In this case I've then had to apply it to the dates twice, to get the second working day after the trade date, not just the first working day at least 2 days later.

[Amit_G_Limbasia]

Dear @ShaileshDevadiga 

Please find another solution with just Multi Row Formula

[J_Gonz]

Hi @Yoshiro_Fujimori ,

I used this logic and realized during testing that if Trade Date = Holiday Date then the trade will be filtered out. Any ideas how to fix this? I was very happy with the results I was getting until this use case caused trades to be filtered out. 

Thank You,

Jessica

[Yoshiro_Fujimori]

@J_Gonz ,

If you want to deal with the rows with [Trade Date] = [Holiday],

you can retrieve them from the R output anchor of the 2nd Join tool and

then you can manipulate the rows depending on whatever you need.

Good luck.

[J_Gonz]

Hi @Yoshiro_Fujimori ,

Yes but pulling from the right Join is data that did not get a BusDaySerNo assigned so even if I union it back into the workflow the settlement date calculation will not work. Any ideas on a fix for this?

Thank You,

Jessica

[KGT]

Hi @J_Gonz,

You can assign the missing BusDaySerNo with a formula/RecordID(start at 500 maybe) as that is not important.

What you want is the T + 2 and then attach the BusDaySerNo for that, then Union it back on. Then the join will work.

As the premise is that they are not holidays, then your best option is independently working out the date you do want to use for the exceptions.

The big thing is, if it is on a holiday, and looking for 2 business days after then you want that trade date moved back to the last business day and then run through.

[Yoshiro_Fujimori]

@J_Gonz ,

I would stick to standard workflow :)

The idea is to multiply the rows with Append Field tool to create 365 rows for each Trade Date,

and count the business days after each Trade Date with Running Total tool.

Workflow

Input & Output Data

For validation, two holidays (2023-01-02 and 2023-05-03) are added to the input data.

Trade Date	Date
2023-01-02	2023-01-05
2023-04-27	2023-05-01
2023-04-28	2023-05-02
2023-05-01	2023-05-08
2023-05-02	2023-05-09
2023-05-03	2023-05-09
2023-05-08	2023-05-10
2023-05-09	2023-05-11
2023-05-10	2023-05-12