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

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

Allocation Problem.xlsx

NettingWorkflow.yxmc

Macros

Batch Macro

Iterative Macro

Accepted answers

Joe_Mako

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.

Example use of Netting and Residual.yxmd

Netting and Residual.yxmc

All comments

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

NettingWorkflow.yxmc

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

Joe_Mako

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

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

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.

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

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

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.

Example use of Netting and Residual.yxmd

Netting and Residual.yxmc

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

DamienMinter

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335