XLOOKUP Conundrum

[LK88]

Hi All

I'm trying to implement a trailing stop loss for simulated stock trading. See the mini sheets, which is are stripped back versions of my actual file as it would be too large.

TL;DR: I need to achieve the result in column U "trailing sl time", without using any of the purple columns F:H.

Green cells are variables.

I've got column R "fixed sl time" working OK. However, I'm having to cheat be adding column G "trailing sl trigger price" to get the column U "trailing sl time" functioning correctly.

Not such a big deal, except I have around 500 different tests per entry (column G "stop loss points" in will vary in a separate matrix not shown here), so would need 500 additional columns on the data_table, which isn't feasible because there will be ~100K rows of data. The workbook would become too large and slow. I need to achieve the xlookup all in one cell, like I have with the column R "fixed sl time".

Column R "fixed sl time" is the correctly functioning FIXED stop loss. It looks up the "date / time" that the "stock price" falls below the "fixed sl trigger price" ("open price" - "stop loss points").

What I'm after is for the stop loss price to move upwards if the price rises from the trade open price, as shown in column G "trailing sl trigger price" (the cheat column I need to lose).

Column U "trailing sl time" is the correct result, but it refers to the cheat column G "trailing sl trigger price".

Columns S and T (red text) are my best attempt at doing it properly, but are incorrect. They are setting the stop loss trigger price at the maximum price for the entire daily trading session (shaded yellow), minus the "stop loss points" (column N). So it's looking forwards in time, which is wrong. What it needs to do is refer to the maximum only of any prices between the open time and the current time. So it's only looking backwards in time.

You will notice that 20th Feb 2021 is getting the correct result, but that's just a fluke because there are no higher prices after the stop loss event. The other two dates are incorrect because the price rises further after the stop loss event, which is picked up by the MAX part of the formula.

I don't mind adding a few extra columns to either table, just not hundreds.

I have the new LET feature, so am not scared of large formulas if that's what's required.

Thanks in advance.

Mini Sheet 1:

STOP LOSS LOOKUP PROBLEM - V 001.xlsx
	D	E	F	G	H
9	date / time	stock price	fixed stop loss	trailing sl trigger price	trailing stop loss
10	20/02/2021 04:00	3933.18
11	20/02/2021 05:00	3934.01		3929.01
12	20/02/2021 06:00	3933.75		3929.01
13	20/02/2021 07:00	3936.81		3931.81
14	20/02/2021 08:00	3933.7		3931.81
15	20/02/2021 09:00	3929.74		3931.81	stop loss
16	20/02/2021 10:00	3929.47		3931.81
17	20/02/2021 11:00	3930.48		3931.81
18	20/02/2021 12:00	3925.69	stop loss	3931.81
19	20/02/2021 13:00	3928.47		3931.81
20	20/02/2021 14:00	3923.73		3931.81
21	20/02/2021 15:00	3924.49		3931.81
22	20/02/2021 16:00	3923.96		3931.81
23	20/02/2021 17:00	3922.23		3931.81
24	20/02/2021 18:00	3922.74		3931.81
25	20/02/2021 19:00	3922.78		3931.81
26	20/02/2021 20:00	3920.45
27	20/02/2021 21:00	3921.71
28	20/02/2021 22:00	3924.97
29	20/02/2021 23:00	3926.7
30	21/02/2021 04:00	3915.39
31	21/02/2021 05:00	3918.72		3906.72
32	21/02/2021 06:00	3911.93		3906.72
33	21/02/2021 07:00	3913.47		3906.72
34	21/02/2021 08:00	3908.99		3906.72
35	21/02/2021 09:00	3910.64		3906.72
36	21/02/2021 10:00	3915.16		3906.72
37	21/02/2021 11:00	3913.68		3906.72
38	21/02/2021 12:00	3908.86		3906.72
39	21/02/2021 13:00	3910.71		3906.72
40	21/02/2021 14:00	3920.96		3908.96
41	21/02/2021 15:00	3916.48		3908.96
42	21/02/2021 16:00	3900.98	stop loss	3908.96	stop loss
43	21/02/2021 17:00	3903.95		3908.96
44	21/02/2021 18:00	3894.49		3908.96
45	21/02/2021 19:00	3896.73		3908.96
46	21/02/2021 20:00	3897.97
47	21/02/2021 21:00	3891.71
48	21/02/2021 22:00	3879.74
49	21/02/2021 23:00	3870.03
50	22/02/2021 04:00	3846.73
51	22/02/2021 05:00	3851.73		3843.73
52	22/02/2021 06:00	3854.23		3846.23
53	22/02/2021 07:00	3847.72		3846.23
54	22/02/2021 08:00	3860.48		3852.48
55	22/02/2021 09:00	3871.21		3863.21
56	22/02/2021 10:00	3855.74		3863.21	stop loss
57	22/02/2021 11:00	3847.71		3863.21
58	22/02/2021 12:00	3836.21	stop loss	3863.21
59	22/02/2021 13:00	3828.74		3863.21
60	22/02/2021 14:00	3831.25		3863.21
61	22/02/2021 15:00	3829.95		3863.21
62	22/02/2021 16:00	3835.29		3863.21
63	22/02/2021 17:00	3832.97		3863.21
64	22/02/2021 18:00	3825.53		3863.21
65	22/02/2021 19:00	3831.26		3863.21
66	22/02/2021 20:00	3828.95
67	22/02/2021 21:00	3833.26
68	22/02/2021 22:00	3823.81
69	22/02/2021 23:00	3818.76
Sheet1

Cell Formulas
Range	Formula
F10:F69	F10	=IF([@[date / time]]=XLOOKUP(FLOOR.MATH([@[date / time]],1),trades_table[date],trades_table[fixed sl time]),"stop loss","")
G10:G69	G10	=IF(OR(MROUND(MOD([@[date / time]],1),1/24)<$K$4,MROUND(MOD([@[date / time]],1),1/24)>$K$5),"",MAX([@[stock price]]:XLOOKUP(MROUND(FLOOR.MATH([@[date / time]],1)+$K$4,1/24),[date / time],[stock price]))-XLOOKUP(FLOOR.MATH([@[date / time]],1),trades_table[date],trades_table[stop loss points]))
H10:H69	H10	=IF([@[date / time]]=XLOOKUP(FLOOR.MATH([@[date / time]],1),trades_table[date],trades_table[trailing sl time]),"stop loss","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E10:E69	Expression	=AND(MROUND(MOD($D10,1),1/24)>=MROUND($K$4,1/24),MROUND(MOD($D10,1),1/24)<=MROUND($K$5,1/24))	text	NO

Mini Sheet 2:

STOP LOSS LOOKUP PROBLEM - V 001.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U
4	open time	05:00
5	close time	19:00
6
7
8										this is incorrect	this is incorrect	this is correct
9		date	open id	close id	stop loss points	open price	close price	fixed sl trigger price	fixed sl time	trailing sl trigger price	trailing sl time incorrect	trailing sl time
10		20/02/2021	20/02/2021 05:00	20/02/2021 19:00	5	3934.01	3922.78	3929.01	20/02/2021 12:00	3931.81	20/02/2021 09:00	20/02/2021 09:00
11		21/02/2021	21/02/2021 05:00	21/02/2021 19:00	12	3918.72	3896.73	3906.72	21/02/2021 16:00	3908.96	21/02/2021 12:00	21/02/2021 16:00
12		22/02/2021	22/02/2021 05:00	22/02/2021 19:00	8	3851.73	3831.26	3843.73	22/02/2021 12:00	3863.21	22/02/2021 05:00	22/02/2021 10:00
Sheet1

Cell Formulas
Range	Formula
L10:L12	L10	=MROUND(K10+$K$4,1/24)
M10:M12	M10	=MROUND(K10+$K$5,1/24)
O10:O12	O10	=XLOOKUP([@[open id]],data_table[date / time],data_table[stock price])
P10:P12	P10	=XLOOKUP([@[close id]],data_table[date / time],data_table[stock price])
Q10:Q12	Q10	=[@[open price]]-[@[stop loss points]]
R10:R12	R10	=XLOOKUP(TRUE,XLOOKUP([@[open id]],data_table[date / time],data_table[stock price]):XLOOKUP([@[close id]],data_table[date / time],data_table[stock price])<=[@[fixed sl trigger price]],XLOOKUP([@[open id]],data_table[date / time],data_table[date / time]):XLOOKUP([@[close id]],data_table[date / time],data_table[date / time]))
S10:S12	S10	=MAX(XLOOKUP([@[open id]],data_table[date / time],data_table[stock price]):XLOOKUP([@[close id]],data_table[date / time],data_table[stock price]))-[@[stop loss points]]
T10:T12	T10	=XLOOKUP(TRUE,XLOOKUP([@[open id]],data_table[date / time],data_table[stock price]):XLOOKUP([@[close id]],data_table[date / time],data_table[stock price]) <= [@[trailing sl trigger price]],XLOOKUP([@[open id]],data_table[date / time],data_table[date / time]):XLOOKUP([@[close id]],data_table[date / time],data_table[date / time]))
U10:U12	U10	=XLOOKUP(TRUE,XLOOKUP([@[open id]],data_table[date / time],data_table[stock price]):XLOOKUP([@[close id]],data_table[date / time],data_table[stock price])<=XLOOKUP([@[open id]],data_table[date / time],data_table[trailing sl trigger price]):XLOOKUP([@[close id]],data_table[date / time],data_table[trailing sl trigger price]),XLOOKUP([@[open id]],data_table[date / time],data_table[date / time]):XLOOKUP([@[close id]],data_table[date / time],data_table[date / time]))

 

[LK88]

link to file

STOP LOSS LOOKUP PROBLEM - V 001.xlsx

1drv.ms