Blank cell cant make calculations !

TheSardOz · Dec 1, 2020

Hello Forum!
I have this data and I would like to calculate the profit loss,( entry value - exit value) or the other way around it depend if is a long or a short.
The problem I am facing is that I can't make the calculation because the data in not linear but shifted, what's the best solution ?

Thank you !

fadee2 · Dec 2, 2020

Hi TheSardOz

Welcome to the Board..

What is the criteria for your results? I mean what should happen if the data is "long" and what should happen if the data is "short"? aditionally what should happen and either of the cells in entry value and exit value are blank?

Also please use xl2bb addon and upload part of your worksheet instead of providing only the screenshot.

TheSardOz · Dec 2, 2020

fadee2 said:
Hi TheSardOz

Welcome to the Board..

What is the criteria for your results? I mean what should happen if the data is "long" and what should happen if the data is "short"? aditionally what should happen and either of the cells in entry value and exit value are blank?

Also please use xl2bb addon and upload part of your worksheet instead of providing only the screenshot.

Hi Fadee2, Thanks for the welcome....

I am trying to make a dashboard, after re arranging the original data some values are no in the same row, and I can't do simple calculation like sum or subtractions to calculate the profit loss for each individual transaction.

this one below is the starting raw data (table 1)

OrdNo	AccCode	SecCode	Exch	Dest	SubDest	B/S	Volume	Price	Value	FXRate	Date	commmission
39840013	287863	FLT	ASX	CIMB_SGX_HKE		B	100	1716.00	1716	1	30/11/2020 14:00:43	$1.37
39840107	287863	FLT	ASX	CIMB_SGX_HKE		B	100	1714.00	1714	1	30/11/2020 14:04:34	$1.37
39840351	287863	FLT	ASX	CIMB_SGX_HKE		S	100	1713.00	1713	1	30/11/2020 14:16:12	$1.37
39840429	287863	FLT	ASX	CIMB_SGX_HKE		S	100	1714.00	1714	1	30/11/2020 14:19:52	$1.37
39840970	287863	FLT	ASX	CIMB_SGX_HKE		B	100	1713.00	1713	1	30/11/2020 14:50:00	$1.37
39841137	287863	FLT	ASX	CIMB_SGX_HKE		B	100	1712.50	1712.5	1	30/11/2020 14:59:07	$1.37
39841516	287863	FLT	ASX	CIMB_SGX_HKE		S	100	1718.00	1718	1	30/11/2020 15:24:09	$1.37
39841619	287863	FLT	ASX	CIMB_SGX_HKE		S	100	1717.00	1717	1	30/11/2020 15:30:26	$1.37
39835206	287863	TWE	ASX	CIMB_SGX_HKE		S	50	893.00	446.5	1	30/11/2020 10:32:39	$0.36
39835737	287863	TWE	ASX	CIMB_SGX_HKE		S	50	900.00	450	1	30/11/2020 10:35:32	$0.36
39836181	287863	TWE	ASX	CIMB_SGX_HKE		B	25	875	218.75	1	30/11/2020 10:43:10	$0.18
39836213	287863	TWE	ASX	CIMB_SGX_HKE		B	75	882	661.5	1	30/11/2020 10:43:28	$0.53
39840669	287863	TWE	ASX	CIMB_SGX_HKE		B	100	851	851	1	30/11/2020 14:33:15	$0.68
39840804	287863	TWE	ASX	CIMB_SGX_HKE		S	100	849.5	849.5	1	30/11/2020 14:40:24	$0.68

this the data re arranged (table2)

using FLT CODE (FIRST 4 ROW FROM TOP ) as example: the trade was made in 4 transactions 2 buy and 2 sale to close the position,
to calculate the GROSS P&L I need to do (EXIT VALUE C - ENTRY VALUE C) because is a long position or for a short trade would be (ENTRY VALUE C - EXIT VALUE C)
but because the values are not in the same row I can't do the calculation.
I am learning excel so I get stuck easily unfortunately!

Thank you !

Commission	Code	Date Calc	include	Volume Mov	Position Balance	Code Balance	Trade count	Time In	Time Out	cotract date calc	Ticker Date Ref	Long/Short	Unit Price $ Cal	Entry Price	Exit Price	Entry Value C	Exit Value C	Entry Volume	Exit volume	Balance	Gross P&L
$1.37	FLT	30/11/2020	Yes	100	100	FLT/100	1	14:00:00		30/11/2020	FLT-44165	Long	17.16	17.16		$1,716		100		-$1,716.00
$1.37	FLT	30/11/2020	Yes	100	200	FLT/200	1	14:04:00		30/11/2020	FLT-44165	Long	17.14	17.14		$1,714		100		-$1,714.00
$1.37	FLT	30/11/2020	Yes	-100	100	FLT/100	1	14:16:00		30/11/2020	FLT-44165	Long	17.13		17.13		$1,713		100	$1,713.00
$1.37	FLT	30/11/2020	Yes	-100	0	FLT/0	1		14:19:00	30/11/2020	FLT-44165	Long	17.14		17.14		$1,714		100	$1,714.00
$1.37	FLT	30/11/2020	Yes	100	100	FLT/100	1	14:50:00		30/11/2020	FLT-44165	Long	17.13	17.13		$1,713		100		-$1,713.00
$1.37	FLT	30/11/2020	Yes	100	200	FLT/200	1	14:59:00		30/11/2020	FLT-44165	Long	17.13	17.125		$1,713		100		-$1,712.50
$1.37	FLT	30/11/2020	Yes	-100	100	FLT/100	1	15:24:00		30/11/2020	FLT-44165	Long	17.18		17.18		$1,718		100	$1,718.00
$1.37	FLT	30/11/2020	Yes	-100	0	FLT/0	1		15:30:00	30/11/2020	FLT-44165	Long	17.17		17.17		$1,717		100	$1,717.00
$0.36	TWE	30/11/2020	Yes	-50	-50	TWE/-50	1	10:32:00		30/11/2020	TWE-44165	Short	8.93	8.93		$447		50		$446.50
$0.36	TWE	30/11/2020	Yes	-50	-100	TWE/-100	1	10:35:00		30/11/2020	TWE-44165	Short	9.00	9		$450		50		$450.00
$0.18	TWE	30/11/2020	Yes	25	-75	TWE/-75	1	10:43:00		30/11/2020	TWE-44165	Short	8.75		8.75		$219		25	-$218.75
$0.53	TWE	30/11/2020	Yes	75	0	TWE/0	1		10:43:00	30/11/2020	TWE-44165	Short	8.82		8.82		$662		75	-$661.50
$0.68	TWE	30/11/2020	Yes	100	100	TWE/100	1	14:33:00		30/11/2020	TWE-44165	Long	8.51	8.51		$851		100		-$851.00
$0.68	TWE	30/11/2020	Yes	-100	0	TWE/0	1		14:40:00	30/11/2020	TWE-44165	Long	8.50		8.495		$850		100	$849.50

fadee2 · Dec 3, 2020

Hi TheSardOZ,
I am completely inexperience in trading, but to understand computational part of your query, I need to ask, do you want to compute balance based on the code, e.g. balance for each row would be computed based on FLT starting from 0 and once It becomes TWE computations would again start from 0, keeping in view Long and Short part of the equation as well?

Please pardon my trading inexperience.

TheSardOz · Dec 3, 2020

fadee2 said:
Hi TheSardOZ,
I am completely inexperience in trading, but to understand computational part of your query, I need to ask, do you want to compute balance based on the code, e.g. balance for each row would be computed based on FLT starting from 0 and once It becomes TWE computations would again start from 0, keeping in view Long and Short part of the equation as well?

Please pardon my trading inexperience.

Yes, each transaction need it's own profit loss result, when the position balance is 0 it means the all the shares I bought has been sell.
If instead of FLT was Apple's or Oranges the process would be the same:
I bought 100 Apples for X amount and after sold the Apples for Y amount, the difference is my profit or loss, and I need to calculate it for each transaction
The VOLUME MOV shows that my first buy was 100 units then I bought 100 more In the POSITION BALABCE you can see the units increasing when I buy and then decreasing as I sell down to the value of 0 witch means I have sold all the shares.
The Gross profit loss of this transaction is (1,713.00+1,714.00) - ( -1,716.00+ -1,714.00)= $3 ( this are the value from the BALANCE)
PS, there is a mistake in the (time in ) formula the time in showing 14:16:00 it should be in time out.

Thank you very much for showing interest and trying to help me out with this little problem!

fadee2 · Dec 4, 2020

TheSardOz said:
The Gross profit loss of this transaction is (1,713.00+1,714.00) - ( -1,716.00+ -1,714.00)= $3 ( this are the value from the BALANCE)

I think the P & L should be -3 as the equation goes

Book1

Z

AA

AB

AC

AD

23

24

1713

1714

-1716

-1714

-3

25

Sheet1

Cell Formulas
Range	Formula
AD24	AD24	=SUM(Z24:AC24)

if this is true then try, the following and see if does what you need...

Excel Formula:

=IF(AND(N3=N2,Y3="Long",AD3=0),AH2-ABS(AG3),IF(AND(N3<>N2,Y3="Long",AD3=0),AG3,IF(AND(N3=N2,Y3="Long",AD3<>0),(ABS(AH2)-AG3)*-1,IF(AND(N3<>N2,Y3="Long",AD3<>0),AG3,IF(AND(N3=N2,Y3="Short",AC3=0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3=0),"",IF(AND(N3=N2,Y3="short",AC3<>0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3<>0),AG3))))))))

TheSardOz · Dec 4, 2020

fadee2 said:
I think the P & L should be -3 as the equation goes

Book1
Z AA AB AC AD
23
24 1713 1714 -1716 -1714 -3
25
Sheet1
Cell Formulas
Range Formula
AD24 AD24 =SUM(Z24:AC24)

if this is true then try, the following and see if does what you need...

Excel Formula:

=IF(AND(N3=N2,Y3="Long",AD3=0),AH2-ABS(AG3),IF(AND(N3<>N2,Y3="Long",AD3=0),AG3,IF(AND(N3=N2,Y3="Long",AD3<>0),(ABS(AH2)-AG3)*-1,IF(AND(N3<>N2,Y3="Long",AD3<>0),AG3,IF(AND(N3=N2,Y3="Short",AC3=0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3=0),"",IF(AND(N3=N2,Y3="short",AC3<>0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3<>0),AG3))))))))

Thank you very much for taking your time to help me out, I really appreciate. Unfortunately after the second calculation the profit loss is incorrect,
If I add a Colum there is a way to have the exit value inline with the entry value? maybe using the (time in) has reference?

Commission	Code	Date Calc	include	Volume Mov	Position Balance	Code Balance	Trade count	Time In	Time Out	Time close	cotract date calc	Ticker Date Ref	Long/Short	Unit Price $ Cal	Entry Price	Exit Price	Entry Value C	Exit Value C	Entry Volume	Exit volume	Balance	Gross P&L	correc values
$1.92	DOC	4/12/2020	Yes	2500	2500	DOC/2500	1	11:26:22			4/12/2020	DOC-44169	Long	0.96	0.96		$2,400		2500		-$2,400.00	-$2,400.00
$1.91	DOC	4/12/2020	Yes	-2500	0	DOC/0	1		11:29:32	11:29:32	4/12/2020	DOC-44169	Long	0.96		0.955		$2,388		2500	$2,387.50	-$12.50	yes
$1.48	NXL	4/12/2020	Yes	250	250	NXL/250	1	12:55:28			4/12/2020	NXL-44169	Long	7.40	7.4		$1,850		250		-$1,850.00	-$1,850.00
$1.50	NXL	4/12/2020	Yes	-250	0	NXL/0	1		12:56:17	12:56:17	4/12/2020	NXL-44169	Long	7.52		7.5186		$1,880		250	$1,879.65	$29.65	yes
$0.63	NXL	4/12/2020	Yes	100	100	NXL/100	1	13:12:15			4/12/2020	NXL-44169	Long	7.89	7.89		$789		100		-$789.00	-$818.65
$0.64	NXL	4/12/2020	Yes	-100	0	NXL/0	1		13:13:26	13:13:26	4/12/2020	NXL-44169	Long	7.95		7.95		$795		100	$795.00	-$23.65	no
$0.66	NXL	4/12/2020	Yes	100	100	NXL/100	1	14:07:42			4/12/2020	NXL-44169	Long	8.21	8.21		$821		100		-$821.00	-$844.65
$0.66	NXL	4/12/2020	Yes	-100	0	NXL/0	1		14:08:31	14:08:31	4/12/2020	NXL-44169	Long	8.25		8.25		$825		100	$825.00	-$19.65	no

fadee2 · Dec 5, 2020

Without adding the coloumn "Time Close" the formula works fine. Formula references were readjusted.

Excel Formula:

=IF(AND(N3=N2,Y3="Long",AD3=0),AH2-ABS(AG3),IF(AND(N3<>N2,Y3="Long",AD3=0),AG3,IF(AND(N3=N2,Y3="Long",AD3<>0),(ABS(AH2)-AG3)*-1,IF(AND(N3<>N2,Y3="Long",AD3<>0),AG3,IF(AND(N3=N2,Y3="Short",AC3=0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3=0),"",IF(AND(N3=N2,Y3="short",AC3<>0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3<>0),AG3))))))))

If you breakdown the formula, it checks 5 basic criteria to match and provide the result.
N3 & N4 = Code
Y3 = Long/Short
AC3 & AD3 = Entry Value / Exit Value
Then computes the result based on True/False, using values in AG and AH coloumns. Check if the values computed adjacent to "no" are the correct ones????????

Book1

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

2

Commission

Code

Date Calc

include

Volume Mov

Position Balance

Code Balance

Trade count

Time In

Time Out

cotract date calc

Ticker Date Ref

Long/Short

Unit Price $ Cal

Entry Price

Exit Price

Entry Value C

Exit Value C

Entry Volume

Exit volume

Balance

Gross P&L

3

$1.37

FLT

30/11/20

Yes

100

FLT/100

1

14:00:00

30/11/20

FLT-44165

Long

17.16

$1,716

100

($1,716.00)

-1716

4

$1.37

FLT

30/11/20

Yes

100

200

FLT/200

1

14:04:00

30/11/20

FLT-44165

Long

17.14

$1,714

100

($1,714.00)

-3430

5

$1.37

FLT

30/11/20

Yes

-100

100

FLT/100

1

14:16:00

30/11/20

FLT-44165

Long

17.13

$1,713

100

$1,713.00

-1717

6

$1.37

FLT

30/11/20

Yes

-100

0

FLT/0

1

14:19:00

30/11/20

FLT-44165

Long

17.14

$1,714

100

$1,714.00

-3

7

$1.37

FLT

30/11/20

Yes

100

FLT/100

1

14:50:00

30/11/20

FLT-44165

Long

17.13

$1,713

100

($1,713.00)

-1716

8

$1.37

FLT

30/11/20

Yes

100

200

FLT/200

1

14:59:00

30/11/20

FLT-44165

Long

17.13

17.125

$1,713

100

($1,712.50)

-3428.5

9

$1.37

FLT

30/11/20

Yes

-100

100

FLT/100

1

15:24:00

30/11/20

FLT-44165

Long

17.18

$1,718

100

$1,718.00

-1710.5

10

$1.37

FLT

30/11/20

Yes

-100

0

FLT/0

1

15:30:00

30/11/20

FLT-44165

Long

17.17

$1,717

100

$1,717.00

6.5

11

$0.36

TWE

30/11/20

Yes

-50

TWE/-50

1

10:32:00

30/11/20

TWE-44165

Short

8.93

$447

50

$446.50

446.5

12

$0.36

TWE

30/11/20

Yes

-50

-100

TWE/-100

1

10:35:00

30/11/20

TWE-44165

Short

9

$450

50

$450.00

896.5

13

$0.18

TWE

30/11/20

Yes

25

-75

TWE/-75

1

10:43:00

30/11/20

TWE-44165

Short

8.75

$219

25

($218.75)

677.75

14

$0.53

TWE

30/11/20

Yes

75

0

TWE/0

1

10:43:00

30/11/20

TWE-44165

Short

8.82

$662

75

($661.50)

16.25

15

$0.68

TWE

30/11/20

Yes

100

TWE/100

1

14:33:00

30/11/20

TWE-44165

Long

8.51

$851

100

($851.00)

-834.75

16

$0.68

TWE

30/11/20

Yes

-100

0

TWE/0

1

14:40:00

30/11/20

TWE-44165

Long

8.5

8.495

$850

100

$849.50

14.75

17

$1.92

DOC

04/12/20

Yes

2500

DOC/2500

1

11:26:22

04/12/20

DOC-44169

Long

0.96

$2,400

2500

($2,400.00)

-2400

($2,400.00)

18

$1.91

DOC

04/12/20

Yes

-2500

0

DOC/0

1

11:29:32

04/12/20

DOC-44169

Long

0.96

0.955

$2,388

2500

$2,387.50

-12.5

yes

($12.50)

19

$1.48

NXL

04/12/20

Yes

250

NXL/250

1

12:55:28

04/12/20

NXL-44169

Long

7.4

$1,850

250

($1,850.00)

-1850

($1,850.00)

20

$1.50

NXL

04/12/20

Yes

-250

0

NXL/0

1

12:56:17

04/12/20

NXL-44169

Long

7.52

7.5186

$1,880

250

$1,879.65

29.65

yes

$29.65

21

$0.63

NXL

04/12/20

Yes

100

NXL/100

1

13:12:15

04/12/20

NXL-44169

Long

7.89

$789

100

($789.00)

-759.35

($818.65)

22

$0.64

NXL

04/12/20

Yes

-100

0

NXL/0

1

13:13:26

04/12/20

NXL-44169

Long

7.95

$795

100

$795.00

35.65

no

($23.65)

23

$0.66

NXL

04/12/20

Yes

100

NXL/100

1

14:07:42

04/12/20

NXL-44169

Long

8.21

$821

100

($821.00)

-785.35

($844.65)

24

$0.66

NXL

04/12/20

Yes

-100

0

NXL/0

1

14:08:31

04/12/20

NXL-44169

Long

8.25

$825

100

$825.00

39.65

no

($19.65)

Sheet1

Cell Formulas
Range	Formula
AH3:AH24	AH3	=IF(AND(N3=N2,Y3="Long",AD3=0),AH2-ABS(AG3),IF(AND(N3<>N2,Y3="Long",AD3=0),AG3,IF(AND(N3=N2,Y3="Long",AD3<>0),(ABS(AH2)-AG3)*-1,IF(AND(N3<>N2,Y3="Long",AD3<>0),AG3,IF(AND(N3=N2,Y3="Short",AC3=0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3=0),"",IF(AND(N3=N2,Y3="short",AC3<>0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3<>0),AG3))))))))

In case if you need to add another column, all references are required to be readjusted based on changes to your data.

TheSardOz · Dec 5, 2020

fadee2 said:
Without adding the coloumn "Time Close" the formula works fine. Formula references were readjusted.

Excel Formula:

=IF(AND(N3=N2,Y3="Long",AD3=0),AH2-ABS(AG3),IF(AND(N3<>N2,Y3="Long",AD3=0),AG3,IF(AND(N3=N2,Y3="Long",AD3<>0),(ABS(AH2)-AG3)*-1,IF(AND(N3<>N2,Y3="Long",AD3<>0),AG3,IF(AND(N3=N2,Y3="Short",AC3=0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3=0),"",IF(AND(N3=N2,Y3="short",AC3<>0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3<>0),AG3))))))))

If you breakdown the formula, it checks 5 basic criteria to match and provide the result.
N3 & N4 = Code
Y3 = Long/Short
AC3 & AD3 = Entry Value / Exit Value
Then computes the result based on True/False, using values in AG and AH coloumns. Check if the values computed adjacent to "no" are the correct ones????????

Book1
M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ
2 Commission Code Date Calc include Volume Mov Position Balance Code Balance Trade count Time In Time Out cotract date calc Ticker Date Ref Long/Short Unit Price $ Cal Entry Price Exit Price Entry Value C Exit Value C Entry Volume Exit volume Balance Gross P&L
3 $1.37 FLT 30/11/20 Yes 100 100 FLT/100 1 14:00:00 30/11/20 FLT-44165 Long 17.16 17.16 $1,716 100 ($1,716.00) -1716
4 $1.37 FLT 30/11/20 Yes 100 200 FLT/200 1 14:04:00 30/11/20 FLT-44165 Long 17.14 17.14 $1,714 100 ($1,714.00) -3430
5 $1.37 FLT 30/11/20 Yes -100 100 FLT/100 1 14:16:00 30/11/20 FLT-44165 Long 17.13 17.13 $1,713 100 $1,713.00 -1717
6 $1.37 FLT 30/11/20 Yes -100 0 FLT/0 1 14:19:00 30/11/20 FLT-44165 Long 17.14 17.14 $1,714 100 $1,714.00 -3
7 $1.37 FLT 30/11/20 Yes 100 100 FLT/100 1 14:50:00 30/11/20 FLT-44165 Long 17.13 17.13 $1,713 100 ($1,713.00) -1716
8 $1.37 FLT 30/11/20 Yes 100 200 FLT/200 1 14:59:00 30/11/20 FLT-44165 Long 17.13 17.125 $1,713 100 ($1,712.50) -3428.5
9 $1.37 FLT 30/11/20 Yes -100 100 FLT/100 1 15:24:00 30/11/20 FLT-44165 Long 17.18 17.18 $1,718 100 $1,718.00 -1710.5
10 $1.37 FLT 30/11/20 Yes -100 0 FLT/0 1 15:30:00 30/11/20 FLT-44165 Long 17.17 17.17 $1,717 100 $1,717.00 6.5
11 $0.36 TWE 30/11/20 Yes -50 -50 TWE/-50 1 10:32:00 30/11/20 TWE-44165 Short 8.93 8.93 $447 50 $446.50 446.5
12 $0.36 TWE 30/11/20 Yes -50 -100 TWE/-100 1 10:35:00 30/11/20 TWE-44165 Short 9 9 $450 50 $450.00 896.5
13 $0.18 TWE 30/11/20 Yes 25 -75 TWE/-75 1 10:43:00 30/11/20 TWE-44165 Short 8.75 8.75 $219 25 ($218.75) 677.75
14 $0.53 TWE 30/11/20 Yes 75 0 TWE/0 1 10:43:00 30/11/20 TWE-44165 Short 8.82 8.82 $662 75 ($661.50) 16.25
15 $0.68 TWE 30/11/20 Yes 100 100 TWE/100 1 14:33:00 30/11/20 TWE-44165 Long 8.51 8.51 $851 100 ($851.00) -834.75
16 $0.68 TWE 30/11/20 Yes -100 0 TWE/0 1 14:40:00 30/11/20 TWE-44165 Long 8.5 8.495 $850 100 $849.50 14.75
17 $1.92 DOC 04/12/20 Yes 2500 2500 DOC/2500 1 11:26:22 04/12/20 DOC-44169 Long 0.96 0.96 $2,400 2500 ($2,400.00) -2400 ($2,400.00)
18 $1.91 DOC 04/12/20 Yes -2500 0 DOC/0 1 11:29:32 04/12/20 DOC-44169 Long 0.96 0.955 $2,388 2500 $2,387.50 -12.5 yes ($12.50)
19 $1.48 NXL 04/12/20 Yes 250 250 NXL/250 1 12:55:28 04/12/20 NXL-44169 Long 7.4 7.4 $1,850 250 ($1,850.00) -1850 ($1,850.00)
20 $1.50 NXL 04/12/20 Yes -250 0 NXL/0 1 12:56:17 04/12/20 NXL-44169 Long 7.52 7.5186 $1,880 250 $1,879.65 29.65 yes $29.65
21 $0.63 NXL 04/12/20 Yes 100 100 NXL/100 1 13:12:15 04/12/20 NXL-44169 Long 7.89 7.89 $789 100 ($789.00) -759.35 ($818.65)
22 $0.64 NXL 04/12/20 Yes -100 0 NXL/0 1 13:13:26 04/12/20 NXL-44169 Long 7.95 7.95 $795 100 $795.00 35.65 no ($23.65)
23 $0.66 NXL 04/12/20 Yes 100 100 NXL/100 1 14:07:42 04/12/20 NXL-44169 Long 8.21 8.21 $821 100 ($821.00) -785.35 ($844.65)
24 $0.66 NXL 04/12/20 Yes -100 0 NXL/0 1 14:08:31 04/12/20 NXL-44169 Long 8.25 8.25 $825 100 $825.00 39.65 no ($19.65)
Sheet1
Cell Formulas
Range Formula
AH3:AH24 AH3 =IF(AND(N3=N2,Y3="Long",AD3=0),AH2-ABS(AG3),IF(AND(N3<>N2,Y3="Long",AD3=0),AG3,IF(AND(N3=N2,Y3="Long",AD3<>0),(ABS(AH2)-AG3)*-1,IF(AND(N3<>N2,Y3="Long",AD3<>0),AG3,IF(AND(N3=N2,Y3="Short",AC3=0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3=0),"",IF(AND(N3=N2,Y3="short",AC3<>0),AH2+AG3,IF(AND(N3<>N2,Y3="Short",AC3<>0),AG3))))))))

In case if you need to add another column, all references are required to be readjusted based on changes to your data.

The results are inconsistent, some are correct some other not

FORSE!!!!! - Copy.xlsx

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

1

2

3

4

5

Commission

Code

Date Calc

include

Volume Mov

Position Balance

Code Balance

Trade count

Time In

Time Out

Time close

cotract date calc

Ticker Date Ref

Long/Short

Unit Price $ Cal

Entry Price

Exit Price

Entry Value C

Exit Value C

Entry Volume

Exit volume

Balance

Gross P&L

Proffit/Loss

6

7

this is correct

8

9

this is correct

10

11

this should be $6 dollars

12

13

this should be $4 dollars

Data Entry

Cell Formulas
Range	Formula
P6:P13	P6	=IF(LEN(Data_entry[@[commmission ]])=0,"",Data_entry[@[commmission ]])
Q6:Q13	Q6	=IF(LEN(Data_entry[@SecCode])=0,"",Data_entry[@SecCode])
R6:R13	R6	=IF(LEN(M6)>0,IFERROR(DATE(YEAR(M6),MONTH(M6),DAY(M6)),"xxx"),"")
S6:S13	S6	=IF(COUNTIF(S7:S10000,"Yes")=0,"Yes",IFERROR(IF(AND(AE6="",AF6=""),"No",IF(OR(LEN(R6)=0,R6="xxx",LEN(D6)=0,LEN(H6)=0,LEN(I6)=0,LEN(J6)=0),"No",IF(AA6>90000,"No","Yes"))),"No"))
T6:T13	T6	=IF(LEN(I6)=0,"",IF(H6="B",I6,I6*-1))
U6:U13	U6	=IF(LEN(D6)<1,0,SUMIF($D$5:D6,D6,$T$5:T6))
V6:V13	V6	=D6&"/"&U6
W6:W7	W6	=IF(LEN(D6)<1,1,COUNTIFS($AA5:AA$6,Z6,$D5:D$6,D6,$AC5:AC$6,AC6,$U5:U$6,0)+1)
X6:X13	X6	=IF(LEN(M6)=0,"",IF(AI6="","",IFERROR(TIME(HOUR(M6),MINUTE(M6),SECOND(M6)),"")))
Y6:Y13	Y6	=IF(LEN(M6)=0,"",IF(AJ6="","",IFERROR(TIME(HOUR(M6),MINUTE(M6),SECOND(M6)),"")))
Z6:Z13	Z6	=IF(LEN(M6)=0,"",IF(U6<>0,"",IFERROR(TIME(HOUR(M6),MINUTE(M6),SECOND(M6)),"")))
AA6:AA13	AA6	=IF(LEN(I6)=0,"",IF(U6=0,R6,IFERROR(INDEX($AA7:$AA$10000,MATCH(D6&"/0",$V7:$V$10000,0)),99999)))
AB6:AB13	AB6	=D6&"-"&AA6
AC6:AC13	AC6	=IF(LEN(I6)=0,"",IF(U6-T6=0,IF(H6="B","Long","Short"),LOOKUP(2,1/($AB$5:AB5=AB6),$AC$5:AC5)))
AD6:AD13	AD6	=IFERROR(IF($J$5="Price",J6/100,J6),J6)
AE6:AE13	AE6	=IF(LEN(I6)=0,"",IF(U6-T6=0,AD6,IF(AND(LOOKUP(2,1/($AB$5:AB5=AB6),$H$5:H5)=H6,LEN(LOOKUP(2,1/($AB$5:AB5=AB6),$AE$5:AE5))>0),AD6,"")))
AF6:AF13	AF6	=IF(LEN(I6)=0,"",IF(LEN(AE6)>0,"",AD6))
AG6:AG13	AG6	=IF(LEN(I6)=0,"",IF(AE6="","",AE6*I6))
AH6:AH13	AH6	=IF(LEN(I6)=0,"",IF(AF6="","",AF6*I6))
AI6:AI13	AI6	=IF(LEN(I6)=0,"",IF(AE6="","",I6))
AJ6:AJ13	AJ6	=IF(LEN(I6)=0,"",IF(AF6="","",I6))
AK6:AK13	AK6	=IF(AND(AE6="",AF6=""),"",IF(H6="S",AD6I6,IF(H6="B",I6AD6*-1,"")))
AL6:AL13	AL6	=IF(AND(Q6=Q5,AC6="Long",AH6=0),AL5-ABS(AK6),IF(AND(Q6<>Q5,AC6="Long",AH6=0),AK6,IF(AND(Q6=Q5,AC6="Long",AH6<>0),(ABS(AL5)-AK6)*-1,IF(AND(Q6<>Q5,AC6="Long",AH6<>0),AK6,IF(AND(Q6=Q5,AC6="Short",AG6=0),AL5+AK6,IF(AND(Q6<>Q5,AC6="Short",AG6=0),"",IF(AND(Q6=Q5,AC6="short",AG6<>0),AL5+AK6,IF(AND(Q6<>Q5,AC6="Short",AG6<>0),AK6))))))))
W8:W13	W8	=IF(LEN(D8)<1,1,COUNTIFS($AA$6:AA7,Z8,$D$6:D7,D8,$AC$6:AC7,AC8,$U$6:U7,0)+1)

fadee2 · Dec 7, 2020

hi ThesardOz,
From your last results, it seems the conditional part of the formula needs some amendments. I'd suggest evaluating formula steps with matching conditions using the [F9] key and then changing the matching criteria, if needed.
Initially the formula was designed to compute results based on a single group, CODE, and then matches the criteria of Long/Short etc., e.g. if you had 4 rows of code DOC then the computations were made starting from 1st occurrence of DOC till the last one, keeping in view Long/Short. But now, maybe, I think you are computing the results for 2 rows, I m sorry but I have no experience in trading therefore, it might be getting way over my head.

I hope my explanation makes sense.

Blank cell cant make calculations !

TheSardOz

New Member

Excel Facts

fadee2

Active Member

TheSardOz

New Member

fadee2

Active Member

TheSardOz

New Member

fadee2

Active Member

TheSardOz

New Member

fadee2

Active Member

TheSardOz

New Member

fadee2

Active Member

Similar threads

Forum statistics

Share this page

Blank cell cant make calculations !

New Member

Excel Facts

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock