Dynamic bucketing when input data can change

Hello Community

I need to bucket financial instruments that have maturity dates (called Tenor's) into bucket periods. Because the instruments and tenors can change every time I want to report, these inputs need to be dynamic. (mostly tenors will change, say for a bond from 21/06/33 to 21/05/33 if I was reporting the bond mth on mth)

In my workflow I've created a table of dates based off my reporting date (in this case 28/06/2024) which correspond to bucket periods.

I have dates (in format dd/mm/yyyy) assigned to each bucket. For each instrument, the tenor dates could be in multiple formats (i.e. 104W, 10M, 01/11/32, 153W etc.) My problem is trying to bucket instruments with multiple format type tenors (which change every mth) into my bucket dates (dd/mm/yyyy) to give the correct bucket periods (example below in yellow).

Instrument example.png

Attached file has sample instrument data with its 'system generated' tenor and the bucketing table. Blue column (D) is my expected output.

After several days trying to crack this, any help would now be greatly appreciated!

Thank you Alteryx Community!

Sures

instrument bucketing problem.xlsx

Datasets

Date Time

Dynamic Processing

Accepted answers

binu_acs

@sureslala let me know the attached workflow works for you

Bucket_13102024_updated.yxmd

binu_acs

@sureslala In the above formula the Regex_Match() function looking for the different pattern on the field [Tenor] and based on that it is converting to Date field

For eg: if the Tenor = 25/10/24 , then the regex_match checks the pattern which is \d{2}\/\d{2}\/{\d{2}} and then converting it into date field using the DateTimeParse function assuming that the given date is in the format of dd/mm/yy, if that pattern is not satisfying the given data it checks the next pattern whether the given field is in 2024-10-25 then converting to date and so on. let me know you need more explanation

All comments

binu_acs

@sureslala how the 104W falls in the 1Y bucket?

sureslala

Hi Binuacs
You are right it's 104 week is 2Y, however my spreadsheet calculates the date as 46199, which when formatted to a date is 26/06/26, 2y bucket starts at 28/06/26 so I believe my spreadsheet is reading it as the date hasn't passed the start of 2Y tenor yet, so bucket to 1Y. Not sure if it's an excel formatting issue however i don't think it would be material issue if we bucket 104 weeks to 2 yrs, considering overall value if +35m I'm calculating.

Happy if your potential answer buckets to 2Y, as you can logically justify this.

Hope this helps,

Thanks

Sures

binu_acs

@sureslala let me know the attached workflow works for you

Bucket_13102024.yxmd

sureslala

Thanks bincaus!, wouldn't have figured out those formulas in a million years! thanks very much. I can logically follow through your workflow, I think my workflow has format issues I need to resolve to get it to work.

I get the following error message with my string date

string error.png

If I use 'V-String' my workflow won't run. What's the easiest way to change my date column to format dd/mm/yyyy pls?

change string date format.png

Thanks

Sures

binu_acs

@sureslala Are you trying to extract from the text "Report Date 28 Jun 2024?"

sureslala

Hi
Trying to change my string date '28 Jun 2024' to string '28/06/2024'

Aiming to get my workflow data in same format as your bucket solution, as mu workflow isn't working, think it's to do with format and data types.

Cheers

Sures

Hi Binuacs

Found one issue with your workflow sorry, after reviewing the final join tool, I noticed that I have instruments that have not been bucketed. These show 'Dates' [Null] because either 'Tenor Date' or 'Tenor' is [Null].

I need to bucket all these to the 0M_6M bucket. In the Formula tool,

instruments not bucketted.png

Can you modify the 'Date' formula so that if Days=0 and 'Tenor_Date' is [Null] the 'Date' column shows the 'Source Date', i.e. 2024-06-28. I ran into issues trying to write this formula as 'Source Date' is string. All these instruments have to be bucketed in 0M-6M bucket, so we should not have any [Null]'s showing in 'Date' column.

short end_null tenors.png

Hope this is clear.

Thanks!

Sures

binu_acs

@sureslala

Bucket_13102024_updated.yxmd

sureslala

Hi Binuacs
I forgot to thank you for your assistance on my queries, it was very much appreciated. There's no way I would ever have got to your formula solutions, so again thank you for this!

Just for my own benefit, your formula below transform's a 'string type' tenor date to a 'date type'

If you wouldn't mind explaining, logically what does each line say it's doing?

Change tenor format from string to date.png

Thanks

Sures

binu_acs

@sureslala In the above formula the Regex_Match() function looking for the different pattern on the field [Tenor] and based on that it is converting to Date field

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703