Netting off Transactions

All,

Please can you assist me with the query below.

I am trying to create an indicator to allow me to net off some positive and negative transactions. The rule that needs to be applied is if transaction ref is the same as another transaction ref and the FCY of both those transactions and the LCY of both those transactions net off to zero then create an indicator saying "net" so i can then exclude them. In my example rows 2 and 3 net off and 15 and 16 and 29 and 30 as they have the same reference and the fcy amount and lcy amounts added together equal 0.

thanks in advance

netting off transactions.yxmd

Reporting

Accepted answers

Here is how you can do it.

Workflow:

Hope this helps : )

netting off transactions (1).yxmd

All comments

Here is how you can do it.

Workflow:

1. Using formula tool to remove comma from [FCY] and [LCY]

2. Using select tool to convert [FCY] and [LCY] number.

3. Using multi-row formula tool to check if Ref is same and [FCY] and [LCY] nets and flag it

Hope this helps : )

netting off transactions.yxmd

Thank you this is great although I had hoped "net" would go across the two lines that netted off not just the one. Is it possible to do that.

Many Thanks

It is possible.

Here is how you can do it.

Workflow:

Hope this helps : )

netting off transactions (1).yxmd

Thank you very much that has worked brilliantly

Vijay_Vittal_11

This does not completely remove the netting off transactions.

My Scenario.

30,000 lines of policy numbers with +ve and -ve amounts

EG:

ABCDEFGH123 $ 1.50

ABCDEFGH123 $ -1.50

ABCDEFGH123 $ 2.50

ABCDEFGH123 $ -2.50

ABCDEFGH123 $ 4.00

ABCDEFGH123 $ 5.55

I want the first two policy numbers to be shown as net off and the last two policy numbers should be separated.

Vijay_Vittal_11

Please help

Vijay_Vittal_11

Once a pair is found can this stop and not include the non-paired one for example -10, +10, +10. The formulas should leave the last +10

Quick Links

Tags

This months top contributors

erich_shiino 280