Dynamic bucketing when input data can change

[sureslala]

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).  

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

[binuacs]

@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 

[binuacs · Accepted solution]

@sureslala let me know the attached workflow works for you

[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

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?

Thanks

Sures

[binuacs]

@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 

[binuacs · Accepted solution]

@sureslala 

[sureslala]

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,   

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. 

 

Hope this is clear.

Thanks!

Sures

[binuacs · Accepted solution]

@sureslala