Replace Excel Macro with Alteryx - Challenging Logical Problem - Genius Needed

[DamienMinter]

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

[cmcclellan]

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.

[DamienMinter]

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 

[cmcclellan]

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 ?

[DamienMinter]

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 

[cmcclellan]

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 :(

[cmcclellan]

Also, I understand that Netting and Residual are the outputs, but is Amount the only input ?

[DamienMinter]

Yes, that’s correct

Sent from my iPhone

[cmcclellan]

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

[DamienMinter]

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