Balancing Calculation Process and Matching Pairs Output

Question

Hi Experts,

Need your assistance to build a little complex calculation model, there are two steps for it

List value (Amount Column) split into two side: Positive and Negative, comparing the absolute value between the sum of positive and sum of negative value first to decide which is the base value to start the calculation. (higher minus lower,)

2nd step:

Take the 1st value(Earliest date value) from either positive list or negative list (Decided by which side has higher absolute value), saying side A

a) minus the other side (side B)  first value

b) the reminder minus side B 2nd value and keep going until the first value become 0

c) take the 2nd value in side A, minus the reminder from side B value.

d) repeat step a-c

Additional condition:

1. The calculation direction (A ->B or B->A) decided by date. i.e, if A side minus B side, the value date A >= B, otherwise stop)

2. continue with condition 1, if the calculation stop, calculation direction switch.

Example:

Raw Data

CdatePastDateValueUserProduct202211012022091050AApple202211012022091320BApple202211012022091530CApple202211012022091540DApple202211012022091550EApple2022110120220915-10FApple2022110120220918-20GApple2022110120221001-60SApple2022110120221002-300GApple2022110120221003-300GApple2022110120221003-30SApple202211012022100320FApple2022110120221005-20DApple2022110120221006300AApple2022110120221007300SApple

In Manual Process: Value will be splited,

Side ARefCdatePastDateValueUserProduct 1000052022110120220915-10FApple 2220032022110120220918-20GApple 222332022110120221001-60SApple 3300002022110120221002-300GApple 3222012022110120221003-300GApple 3533302022110120221003-30SApple 3222222022110120221005-20DApple   Sum-740

Side BRefCdatePastDateValueUserProduct 100001202211012022091050AApple 10002202211012022091320BApple 100002202211012022091530CApple 200010202211012022091540DApple 300005202211012022091550EApple 130001202211012022100320FApple 322222022110120221006300AApple 3155322022110120221007300SApple   Sum810

With calculation absolute value of the sum of both side,  Side B > A, Start with B

Calculation Details: Image Attached

Sample out: Showing Calculation Pairs

PairRefCdatePastDateValueUserProduct1100001202211012022091050AApple11000052022110120220915-10FApple12220032022110120220918-20GApple1222332022110120221001-20SApple210002202211012022091320BApple2222332022110120221001-20SApple3100002202211012022091530CApple3222332022110120221001-20SApple33300002022110120221002-10GApple4200010202211012022091540DApple43300002022110120221002-40GApple5300005202211012022091550EApple53300002022110120221002-50GApple63300002022110120221002-200GApple6130001202211012022100320FApple6322222022110120221006180AApple73222012022110120221003-300GApple7322222022110120221006120AApple73155322022110120221007180SApple83533302022110120221003-30SApple8315532202211012022100730SApple93222222022110120221005-20DApple9315532202211012022100720SApple10315532202211012022100770SApple

Much Appreciated that you may help me with it. :)

Hedging Calculation.PNG

k3pineapple · Answer

@DawnDuong Thank you very much for the advise here.

Unfortunately, there is no such relative data/columns that we have in order to use here. that's the reason why we are still do the calculation manually.

Wondering whether it may work if we build the workflow into several sections.

Workflow I, comparing two sides, sorting the data

Workflow II, generate the pair one by one, by using the sorted data

Workflow III, unite the result

Thank you

DawnDuong · Answer

hi @k3pineapple

While I understand what you are trying to do, I think the logic will not work all the times (i.e. it cannot be generalised)

The current logic offsets the next number as long as it is smaller than the residual value after subtracting previous values. This logic is flawed because the next "start" value can occur before the previous "start" has been fully amortised and there is no guarantee that the "start" is of the same +/- sign than the previous start.

What you can get out of this logic is at best a guess (which will likely fail when the data gets larger).

I would suggest that you check with the IT/Data team to see whether there are additional fields that can be used to do a more reliable mapping.

Regards,
Dawn.