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]

Sure, but what is [TenorExt] and [MiniTenorOrder] for ?

And the logic you first described doesn't work for the the other Area/Group combinations.

[DamienMinter]

TenorExt is the direction of the amount. Long is positive and short is negative. MiniTenorOrder is the order of the periods. I am trying to catch to make sure if we are missing a period it won’t net against the next one. Ie 7yr can net against 10yr but not 15yr if 10 year is missing.

Yeah trying to come up with some fail safe logic that works every time. Definitely a challenge

[Joe_Mako · Accepted solution]

Attached is my first pass at a workflow and iterative macro that may be close to what you are looking for.

An assumption that I made was that your data type for "Amount" would be integer, but that may not be accurate, because it look like there is a rounding difference in your first record for Area1-Group1-6M I believe the Netting should be -2385 (-19483 - -17098 = -2385) and not -2386.

If you need decimal numbers, let me know how many decimal places you need, and I will modify the workflow and macro (there are numerous places where this needs to be configured).

as for the calculation, from looking at your Excel file, it seems all we need is "Amount", "Next Amount", and "Previous Residual" to calculate both Netting and Residual. because they need to be calculated at the same time, and Residual passed back in as Previous Residual, I am using an iterative macro that calculates both, one record at a time.

Please try thing on more data, and compare the results, maybe my logic is not complete, and just happens to work for this small sample.

Here is what is happening in the workflow:
- deselect the provided Netting and Residual, so simulate the actual data
- we need a field we can sort, we convert the string for Period into a number with the expression:

ToNumber([_CurrentField_],1,1)/IF EndsWith([_CurrentField_], "M") THEN 12 ELSE 1 ENDIF

- to prep for the iterative macro, we add a field for "Previous Residual" with a value of zero
- run the iterative macro (see below)
- sort the results
- convert the Period number back to a Period string
- compare to provided with a join and filter

In the macro:
- Select to ensure data type
- Sort to ensure correct sort
- Record ID to add a row identifier
- Multi-Field Formula to get the "Next Amount"
- Sample to get just the last record per Area-Group
- Formula too to calculate "Netting" and "Residual"

Netting:

IF [Amount]-[Previous Residual]=0 THEN 0
ELSEIF ([Amount]-[Previous Residual]>0 and [Next Amount]>0)
OR ([Amount]-[Previous Residual]<0 and [Next Amount]<0) THEN [Amount]-[Previous Residual]
ELSEIF ABS([Amount]-[Previous Residual])>ABS([Next Amount]) THEN [Amount]-[Previous Residual]+[Next Amount]
ELSE 0 ENDIF

Residual:

IF [Amount]-[Previous Residual]=0 THEN 0
ELSEIF ([Amount]-[Previous Residual]>0 and [Next Amount]>0)
OR ([Amount]-[Previous Residual]<0 and [Next Amount]<0) THEN 0
ELSEIF ABS([Amount]-[Previous Residual])>ABS([Next Amount]) THEN [Next Amount]
ELSE -([Amount]-[Previous Residual]) ENDIF

- Select tool prior to "R output" for results, select just wanted fields and ensure field order
- other Select tool renames Residual to "Previous Residual"
- the first Join uses the Left output to return records that are not the last record per Area-Group
- Sort
- Sample to keep just last record per Area-Group
- Join to replace with new "Previous Residual"
- other Join to with Left output to return non-last records
- Union all records
- Select to keep on wanted fields and ensure field order before "Left output" for looping back in
(additionally, used the Interface Designer config to setup the looping settings)

You are welcome to message me to setup to meet for a screen share to walk through what is happening and make adjustments to fit any other constraints you may have.

[DamienMinter]

Hi @Joe_Mako, 

Thanks, I am looking over your workflow now. I just tried to extract and test, but I am using version 11.0.6.28907. Do you have the ability to save the workflow in earlier versions?

Thanks

Damien 

[Joe_Mako]

You can edit the two files in a text editor like notepad, and change the version number from 11.7 to 11.0, and it should work fine.

[DamienMinter]

Hey Joe, looks liked it worked. I have tested with the actual data and it did the job. Cheers for the solution. Thanks