Formula For Stock Entry/Exit Points

[kivikatz]

Hi All. I have a two-part question regarding what's the best formula to use in figuring out entry/exit points for a stock, as well as recalculating cumulative profit/loss when entering a position. I have tried multiple formulas, using INDEX/MATCH and XLOOKUP, but have had no success at all. I have struggled over this and I think is way beyond my ken of understanding. I would appreciate your help in figuring out the most effective formulas.

Attached is the Excel sheet for reference (I have removed several rows just for expediency sake and the data is hard coded, except or the entry, exit and profit-and-loss columns). The following is what I am trying to accomplish. I would like to recalculate a new ENTRY in column H after there is an EXIT signal in column I (in this case it would be cell H17). I would also like to recalculate a new EXIT in column I after the new ENTRY signal in column H (in this case it would be cell I26).

For the PROFIT & LOSS column, I would like to end the calculation and leave the cells blank after the first EXIT signal (F13:F16), recalculate PROFIT & LOSS after the subsequent ENTRY signal (F17:F26), and then end the calculation again after the subsequent EXIT (F27:F28).

I think the Excel sheet better illustrates what I am trying to accomplish. Thanks for all your help.

KKHKHK.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
5	Date	Close	Change	Cumulative	New Highs	P&L	MACD	Entry Signal	Exit Signal
6	Fri 12/13/19	71.68	0.00%	0.00%			1.94
7	Mon 12/16/19	76.30	6.45%	6.45%	TRUE	0.00%	1.94	ENTRY			ENTRY
8	Tue 12/17/19	75.80	-0.66%	5.75%		-0.66%	2.37
9	Wed 12/18/19	78.63	3.73%	9.70%	TRUE	3.05%	2.64
10	Mon 03/09/20	121.60	-13.57%	69.64%		59.37%	3.57
11	Tue 03/10/20	129.07	6.14%	80.06%		69.16%	1.07
12	Wed 03/11/20	126.85	-1.72%	76.97%		66.25%	-0.31		EXIT		EXIT
13	Thu 03/12/20	112.11	-11.62%	56.40%		46.93%	-1.56				P&L should be blank
14	Wed 06/03/20	176.59	0.16%	146.36%		131.44%	7.58				P&L should be blank
15	Thu 06/04/20	172.88	-2.10%	141.18%		126.58%	7.80				P&L should be blank
16	Fri 06/05/20	177.13	2.46%	147.11%		132.15%	7.59				P&L should be blank
17	Mon 06/08/20	189.98	7.25%	165.04%	TRUE	148.99%	7.68				P&L should be recalculated			New ENTRY
18	Tue 06/09/20	188.13	-0.97%	162.46%		146.57%	8.69				P&L should be recalculated
19	Wed 06/10/20	205.01	8.97%	186.01%	TRUE	168.69%	9.23				P&L should be recalculated
20	Thu 06/11/20	194.57	-5.09%	171.44%		155.01%	10.90				P&L should be recalculated
21	Fri 06/12/20	187.06	-3.86%	160.97%		145.16%	11.25				P&L should be recalculated
22	Mon 06/29/20	201.87	5.17%	181.63%		164.57%	8.48				P&L should be recalculated
23	Tue 06/30/20	215.96	6.98%	201.28%	TRUE	183.04%	8.47				P&L should be recalculated
24	Wed 07/01/20	223.93	3.69%	212.40%	TRUE	193.49%	9.50				P&L should be recalculated
25	Thu 07/02/20	221.98	-0.87%	209.68%		190.93%	7.80				P&L should be recalculated
26	Fri 07/03/20	212.76	-4.15%	196.82%		178.85%	-0.05				P&L should be recalculated			New EXIT
27	Mon 07/06/20	198.50	-6.70%	176.93%		160.16%	-1.56				P&L should be blank
28	Tue 07/07/20	205.00	3.27%	185.99%		168.68%	1.07				P&L should be blank
Chart Data (2)

Cell Formulas
Range	Formula
H7:H28	H7	=IFERROR(IF(MATCH("ENTRY",$H$6:H6,0),"",IF(AND(E7=TRUE,G7>0),"ENTRY","")),IF(AND(E7=TRUE,G7>0),"ENTRY",""))
I7:I28	I7	=IFERROR(IF(MATCH("EXIT",$I$6:I6,0),"",IF(AND(MATCH("ENTRY",$H$6:H7,0),G7<0),"EXIT","")),IF(AND(MATCH("ENTRY",$H$6:H7,0),G7<0),"EXIT",""))
F7:F28	F7	=IF(OR(H7="ENTRY",NOT(ISBLANK(F6))),(B7-$B$7)/$B$7,"")