Hi
I have been working on trying to replace a reasonably complicated process currently done with and Excel Macro. I have been working on this for a few weeks now,but I am really struggling with one part of this and could really do with some help both with the logic and how to approach this with Alteryx.
I have been mainly attempting to do this using the "Multi Row Formula" tool, but I had much success. I am not sure this might need a macro that can loop.
The Problem
In the table below, For each "Area" and "Group" the "Amount" for each "Period" can be netted off with the period next to this IF the Amount is the opposite direction (positive can net against negative, but not against positive) starting at the furthest point and working backwards
For example For Area 1 / Group 1 Combination (starting at the 40Y point)
1. 40Y = 0 so nothing
2. 30Y is positive and so is 20Y so nothing can be netted. +37k goes to "Netting"
3. 20Y is positive and 15Y is negative, so +74k is netted against (48)k so +25 goes to "Netting" and (48)k goes to "Residual"
4. 15Y as all of this was netted against 20Y nothing goes to "Netting" or "Residual"
5. 10Y is positive and so is 7Y so nothing can be netted. +42k goes to "Nettingl"
This is where it gets complicated
6. 7Y is positive and 5Y is negative BUT all +88k is offset against (212)k so nothing goes to "Netting" and (88)k goes to "Residual"
7. 5Y is negative and 3Y is positive so (212)k less +88k = (124)k is netted against +72k and (51)k goes to "Netting" and +72k goes to "Residual"
8. 3Y as all of this was netted against 5Y and 1Y is also positive nothing goes to "Netting" or "Residual"
9. 1Y is positive and 6M is negative, BUT all +17k is offset against (19)k so nothing goes to "Netting" and (17)k goes to "Residual"
10. 6M has $(2)k left after being netted against +17k so $(2)k goes to "Netting"
Then move to Area1 / Group 2 combination
Area | Group | Period | Amount | Netting | Residual |
Area1 | Group 1 | 6M | (19,483) | (2,386) | - |
Area1 | Group 1 | 1Y | 17,098 | - | (17,098) |
Area1 | Group 1 | 3Y | 72,639 | - | - |
Area1 | Group 1 | 5Y | (212,544) | (51,156) | 72,639 |
Area1 | Group 1 | 7Y | 88,749 | - | (88,749) |
Area1 | Group 1 | 10Y | 42,676 | 42,676 | - |
Area1 | Group 1 | 15Y | (48,957) | - | - |
Area1 | Group 1 | 20Y | 74,039 | 25,082 | (48,957) |
Area1 | Group 1 | 30Y | 37,239 | 37,239 | - |
Area1 | Group 1 | 40Y | - | - | - |
Area1 | Group 2 | 6M | 993 | 993 | - |
Area1 | Group 2 | 1Y | 9,030 | - | - |
Area1 | Group 2 | 3Y | (40,124) | (31,094) | 9,030 |
Area1 | Group 2 | 5Y | (21,273) | (21,273) | - |
Area1 | Group 2 | 7Y | 2,737 | - | - |
Area1 | Group 2 | 10Y | (11,318) | (8,581) | 2,737 |
Area1 | Group 2 | 15Y | (5,469) | - | - |
Area1 | Group 2 | 20Y | 17,990 | 8,984 | (5,469) |
Area1 | Group 2 | 30Y | (3,537) | - | 3,537 |
Area1 | Group 2 | 40Y | - | - | - |
Area2 | Group 1 | 6M | 43,076 | 43,076 | - |
Area2 | Group 1 | 1Y | 22,365 | - | - |
Area2 | Group 1 | 3Y | (72,466) | (50,101) | 22,365 |
Area2 | Group 1 | 5Y | (40,342) | (39,725) | - |
Area2 | Group 1 | 7Y | 12,117 | - | (617) |
Area2 | Group 1 | 10Y | (11,500) | - | 11,500 |
Area2 | Group 1 | 15Y | (47) | (47) | - |
Area2 | Group 1 | 20Y | - | - | - |
Area2 | Group 1 | 30Y | - | - | - |
Area2 | Group 1 | 40Y | - | - | - |
I haven't included a workflow, mainly because I didn't get anywher with this. As mentioned before I was using the mult-row formula tool, but this was of limited use as it can only generate one column. The standard formula tool appeared to be of little use also as its doesn't allow the [Row-1: ] syntax. The more I look at this problem the more I get the impression that it needs a loop, but I am not even sure what tools to use in this loop.
I know this is challenging question, and I have been stuck on it for quite some time. So I really need a genius to be abe to help me.
UPDATE: I have added a demo file with my initial attempt and some test data inluding a couple more columns of data
Many thanks
Damien
Solved! Go to Solution.
Hey, I don't have time to write the workflow now, but I'll play with it later to see what I can do.
My approach/suggestion would be to sort the period (for calculating) so that 40Y is first and 6M is last, because that follows your logic better.
Then all the calculations are done using a multi-row for current & previous record.
Then at the end, sort by period again to put 6M first and 40Y last.
Thanks @cmcclellan, I have now attached a sample workflow with my how I initially approached the problem. I had already sorted out getting the periods in correct order (included in sample workflow) which I did in an earlier stage of the workflow. The big issue arises when I have to net more than 2 rows to net against each other with the current choice of tools.
Any help would be great
Thanks
Damien
Now I can see this thread again :( .. I'll keep on replying here ...
I'm curious if the logic is best done "backwards" (ie 40Y to 6M) or "forwards" (ie 6M to 40Y) ?
Also, if the macro works is it possible to post the exact macro code ?
Hi @cmcclellan,
This is definitely not the easiest Macro to understand as it calls parts of code from all of the place. It looks like it is using a custom function to calculate (both) columns. I am not able to find this function in the VBA an neither was a colleague who has tried to look at this problem in the past.
{=SpreadsAndOutrightStratWithoutHeader($D$1282:$D$1291)}
I am told it works from the highest number back to the lowest number (but not sure if this makes much of a difference) very hard to tell with the way it has been built (and hence the reason I want to move this process to Alteryx). I did already overcome the back to front part of this in a workflow See the attached workflow as you can use the input from this to avoid having to figure out the back to front part
Thanks
Damien
Yeah, that means there's a function called SpreadsAndOutrightStratWithoutHeader somewhere in the VBA code, so it only references the 10 periods for each Area/Group. So a batch macro would be the ultimate solution, but I'm still trying to get the logic working for the periods within each group :(
Also, I understand that Netting and Residual are the outputs, but is Amount the only input ?
I've been working on this when I can - changing between doing the calc's forward and backwards because I know you said backwards but this looks like it should be done forwards.
I've finally got Area1/Group 1 working perfectly, but the rules are more complex than you describe because the same rules don't work for the other Area/Group combo's.
I'll keep working on it, it's a great challenge :)
Yeah, also having a go when I can. Have been working on the logic using nested IF functions... but this is what I have so far. Its still a LOOONNNNGG way off. Maybe you caould share what you have also?
IF
( [Area] = [Row+1:Area]
OR [Area] = [Row-1:Area]
AND [Group] = [Row+1:Group]
OR [Group] = [Row-1:Group]
)
THEN
IF [TenorExt] != [Row+1:TenorExt]
AND [TenorExt] != [Row-1:TenorExt]
AND [MiniTenorOrder] - [Row+1:MiniTenorOrder] = 1
AND [MiniTenorOrder] - [Row-1:MiniTenorOrder] = -1
THEN
IF ABS([Amount]) > ABS([Row-1:Amount])
AND ABS([Amount]) > ABS([Row+1:Amount])
THEN [Amount]+[Row+1:Amount]+[Row-1:Amount]
ELSEIF ABS([Amount]) > ABS([Row-1:Amount])
AND ABS([Amount]) < ABS([Row+1:Amount])
THEN [Amount]+[Row+1:Amount]+[Row-1:Amount] // Check logic
ELSEIF ABS([Amount]) < ABS([Row-1:Amount])
AND ABS([Amount]) > ABS([Row+1:Amount])
THEN 0 // Check logic
ELSE 100000000 // Catch Error
ENDIF
ELSEIF [TenorExt] != [Row+1:TenorExt]
//AND [TenorExt] = [Row-1:TenorExt]
AND [MiniTenorOrder] - [Row+1:MiniTenorOrder] = 1
THEN
IF ABS([Amount]) > ABS([Row+1:Amount])
THEN ABS([AMOUNT]) - ABS([Row+1:Amount])
ELSEIF ABS([Amount]) < ABS([Row+1:Amount])
THEN [Amount] // Check logic
ELSE 20000000 // Catch Error
ENDIF
// Double check as overrides exsisiting logic
ELSEIF [TenorExt] != [Row-1:TenorExt]
AND [MiniTenorOrder] - [Row-1:MiniTenorOrder] = -1
THEN
IF ABS([Amount]) > ABS([Row-1:Amount])
THEN ABS([AMOUNT]) - ABS([Row-1:Amount])
ELSEIF ABS([Amount]) < ABS([Row-1:Amount])
THEN 0 // Check logic
ELSE 30000000 // Catch Error
ENDIF