Data substra

[SCK]

Hi all, I need help in subtracting the data of P/L from NA P/L and then adding the result to equity across multiple columns. The input and desired output data has been given below: I have multiple columns so using multi-row formula would not be efficient enough I believe. Please help

Input:

BS or P/L?	A	B	C	D	E
BS	-	(57,682)	-	(20,669,458)	(298,232)
P/L	-	76,910	-	27,559,277	397,643
Total	-	19,227	-	6,889,819	99,411
NA BS	-	(57,682)	-	(20,669,458)	(298,232)
NA PL	-	57,682	-	20,669,458	298,232
Total	-	0	-	0	(0)

Difference in P/L and NA P/L:

	A	B	C	D	E
Difference In P/L and NA PL	-	19,227	-	6,889,819	99,411

Output:

BS or P/L?	A	B	C	D	E
Equity	-	(39,227)	-	(6,920,819)	(128,411)
P/L	-	76,910	-	27,559,277	397,643
Total	-	19,227	-	6,889,819	99,411
NA Equity	-	(20,000)	-	(31,000)	(29,000)
NA PL	-	57,682	-	20,669,458	298,232
Total	-	0	-	0	(0)

[pedrodrfaria]

Hi @SCK 

Could you please explain how you get to the equity rows?

Looking at column B for example, in the input you have BS and NA BS for (57,682) and (57,682) and the difference between PL and NA PL is equal to 19,227. And in the output you don't have BS nor NA BS but you have Equity and NA Equity for (39,227) and 20,000). The difference is the same 19,227 calculated from the PL and the NA PL. How is the value for equity calculated? 

I'm was able to create the workflow to do this, I'm just not sure about the final step to calculate the equity of it and if the BS data should be excluded.

An additional question that I have is the total row seems to be always showing the difference between the PL and the NA PL. I'm able to calculate the PL and the NA PL, but would it also make sense to just use the Total Row as the given difference between PL and NA PL?

Please advise.

Pedro.

[SCK]

Apologies for the confusion. Please consider all BS as equity. Please help with the solution.

[pedrodrfaria]

@SCK 

BS - Equity from the input and output is (57,682)-(39,227) = 18,455

NA BS - NA Equity from the input and output is (57,682)- (20,000) = 37682

I'm confused to how you got to these amounts knowing that the difference between the PL is 19,227.

Pedro.

[SCK]

Apologies for the confused data. Please find the updated data for your reference @pedrodrfaria . Please help.

Input:

BS or P/L?	A	B	C	D	E
Equity	-	(20,000)	-	(31,000)	(29,000)
P/L	-	76,910	-	27,559,277	397,643
Total	-	19,227	-	6,889,819	99,411
NA Equity	-	(20,000)	-	(31,000)	(29,000)
NA PL	-	57,682	-	20,669,458	298,232
Total	-	0	-	0	(0)

	A	B	C	D	E
Difference In P/L and NA PL	-	19,227	-	6,889,819	99,411

Output:

BS or P/L?	A	B	C	D	E
Equity	-	(39,227)	-	(6,920,819)	(128,411)
P/L	-	76,910	-	27,559,277	397,643
Total	-	19,227	-	6,889,819	99,411
NA Equity	-	(20,000)	-	(31,000)	(29,000)
NA PL	-	57,682	-	20,669,458	298,232
Total	-	0	-	0	(0)

[AngelosPachis · Accepted solution]

Hi @SCK ,

Not sure I understand the requirements correctly here but is this output what you are after?

Regards,

Angelos

[pedrodrfaria · Accepted solution]

Hi @SCK 

Based on the new input I created the following workflow. I was not sure to what kind of input you have, if you have an input that shows the difference or if we would need to calculate it. So to make sure, I used the bottom part of the workflow to calculate it the difference and then I joined it back in together to calculate it all.

See below:

Please let us know if we can help you any further.

Pedro.