Finance P/L and Day P/L Formula

[avtrade]

3. Day Trade Tracker.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	TRACKING SHEET
3	# of Trades		3		Winners		3.0000
4	Batting Avg		100.00%		Losers		0.0000
5	Sharpe Ratio		#DIV/0!		Breakeven		0.0000
6	Avg ROI		84.95%		Avg Risk		1.1707
7	Commission		#REF!		Avg Winner		45.4133
8	Overall P/L		$136.24		Avg Loser		#DIV/0!
9
10	Date	Symbol	Position	Shares	Fill	Exit	Exit Half	Exit Qtr	Entry	Stop	Risk Unit	Risk	Setup	P/L	Day P/L	Time Open	Notes
11	2/23/24	MRVI	L	200	7.7100	7.7500	8.0000		7.6500	7.3900	0.26	64.00	BO	33.00
12	2/23/24	SQNS	L	500	0.7499	0.7999	0.8293		0.7500	0.6600	0.09	44.95		32.35
13	2/23/24	WBD	S	278	8.2650	8.5200			8.2900	8.4500	0.16	51.43		70.89
14											0.00	0.00
15											0.00	0.00
16											0.00	0.00
17											0.00	0.00
18											0.00	0.00
19											0.00	0.00
20											0.00	0.00
21											0.00	0.00
22											0.00	0.00
23											0.00	0.00
24											0.00	0.00
25											0.00	0.00
26											0.00	0.00
27											0.00	0.00
28											0.00	0.00
29											0.00	0.00
30											0.00	0.00
31											0.00	0.00
32											0.00	0.00
33											0.00	0.00
34											0.00	0.00
35											0.00	0.00
36											0.00	0.00
37											0.00	0.00
Tracking

Cell Formulas
Range	Formula
D3	D3	=SUM(H3:I5)
D4	D4	=(H3/D3)
D5	D5	=ABS(H7/H8)
D6	D6	=D8/(SUM(M10:M10228))
D7	D7	=SUM(#REF!)*-1
D8	D8	=SUM(O10:O10228)
H3	H3	=COUNTIF(O10:O10227,">0")
H4	H4	=COUNTIF(O10:O10227,"<0")
H5	H5	=COUNTIF(O10:O10227,"=0")
H6	H6	=AVERAGE(M10:M10229)
H7	H7	=AVERAGEIF(O10:O10228,">0")
H8	H8	=AVERAGEIF(O10:O10228,"<0")
L11:L37	L11	=ABS(J11-K11)
M11:M37	M11	=ABS((K11-F11)*E11)
O11:O37	O11	=IF(I11>0,E11/4*(I11-F11)+E11/4*(H11-F11)+E11/2*(G11-F11),IF(H11>0,E11/2*(H11-F11)+E11/2*(G11-F11),IF(G11>0,E11*(G11-F11), "")))

Hi everyone!

I am struggling with Excel formulas to define the 2 columns. The P/L and the Day P/L. I got this formula from my friend, but it seems to not be working properly. He hasn't used this sheet for a long time, so he doesn't know how to fix it unfortunately.

To clarify, the position L or S, is Long or Short. That means the fill (where I got filled), and exit (where I exited), will be either a loss or a profit depending on the Position. In these 3 examples, WBD is Short, therefore it should be a negative P/L in the P/L column for the day. The Day P/L column should add each amount in the P/L column as long as it is the same date..

Please let me know if there are any other questions. If someone could help me with this, it would be greatly appreciated!

 

[awoohaw]

Not all the excel forum are financial experts, particulary me. So, much of your terminology is greek to me.

I do not see a column called WBD.
So, please inform which sign column F (Fill) should have when column D (Position) is "L" or "S"?
(Should Fill be negative when Position is L or S?).
Are you asking for aggregate amounts in the P/L column by date?

Also, some descriptions of the accurate formula would be helpful, as well as expected values for the example, so the forum can see what values they need to work toward.

Are you aware that you have uneven cell references in the tracking data in B3:G8? While I understand the use of long column ranges, the ranges are not the same size. This leads me to believe you have deleted cells (not rows in data clean up) and the data is no longer column aligned. This probably makes no difference in the sample data you sent. But your original data may have been corrupted.