XLOOKUP Conundrum

LK88

New Member
Joined
Mar 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.


TRAILING STOP LOSS PROBLEM - V 005.PNG



Mini Sheet 1:


STOP LOSS LOOKUP PROBLEM - V 001.xlsx
DEFGH
9date / timestock pricefixed stop losstrailing sl trigger pricetrailing stop loss
1020/02/2021 04:003933.18   
1120/02/2021 05:003934.01 3929.01 
1220/02/2021 06:003933.75 3929.01 
1320/02/2021 07:003936.81 3931.81 
1420/02/2021 08:003933.7 3931.81 
1520/02/2021 09:003929.74 3931.81stop loss
1620/02/2021 10:003929.47 3931.81 
1720/02/2021 11:003930.48 3931.81 
1820/02/2021 12:003925.69stop loss3931.81 
1920/02/2021 13:003928.47 3931.81 
2020/02/2021 14:003923.73 3931.81 
2120/02/2021 15:003924.49 3931.81 
2220/02/2021 16:003923.96 3931.81 
2320/02/2021 17:003922.23 3931.81 
2420/02/2021 18:003922.74 3931.81 
2520/02/2021 19:003922.78 3931.81 
2620/02/2021 20:003920.45   
2720/02/2021 21:003921.71   
2820/02/2021 22:003924.97   
2920/02/2021 23:003926.7   
3021/02/2021 04:003915.39   
3121/02/2021 05:003918.72 3906.72 
3221/02/2021 06:003911.93 3906.72 
3321/02/2021 07:003913.47 3906.72 
3421/02/2021 08:003908.99 3906.72 
3521/02/2021 09:003910.64 3906.72 
3621/02/2021 10:003915.16 3906.72 
3721/02/2021 11:003913.68 3906.72 
3821/02/2021 12:003908.86 3906.72 
3921/02/2021 13:003910.71 3906.72 
4021/02/2021 14:003920.96 3908.96 
4121/02/2021 15:003916.48 3908.96 
4221/02/2021 16:003900.98stop loss3908.96stop loss
4321/02/2021 17:003903.95 3908.96 
4421/02/2021 18:003894.49 3908.96 
4521/02/2021 19:003896.73 3908.96 
4621/02/2021 20:003897.97   
4721/02/2021 21:003891.71   
4821/02/2021 22:003879.74   
4921/02/2021 23:003870.03   
5022/02/2021 04:003846.73   
5122/02/2021 05:003851.73 3843.73 
5222/02/2021 06:003854.23 3846.23 
5322/02/2021 07:003847.72 3846.23 
5422/02/2021 08:003860.48 3852.48 
5522/02/2021 09:003871.21 3863.21 
5622/02/2021 10:003855.74 3863.21stop loss
5722/02/2021 11:003847.71 3863.21 
5822/02/2021 12:003836.21stop loss3863.21 
5922/02/2021 13:003828.74 3863.21 
6022/02/2021 14:003831.25 3863.21 
6122/02/2021 15:003829.95 3863.21 
6222/02/2021 16:003835.29 3863.21 
6322/02/2021 17:003832.97 3863.21 
6422/02/2021 18:003825.53 3863.21 
6522/02/2021 19:003831.26 3863.21 
6622/02/2021 20:003828.95   
6722/02/2021 21:003833.26   
6822/02/2021 22:003823.81   
6922/02/2021 23:003818.76   
Sheet1
Cell Formulas
RangeFormula
F10:F69F10=IF([@[date / time]]=XLOOKUP(FLOOR.MATH([@[date / time]],1),trades_table[date],trades_table[fixed sl time]),"stop loss","")
G10:G69G10=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:H69H10=IF([@[date / time]]=XLOOKUP(FLOOR.MATH([@[date / time]],1),trades_table[date],trades_table[trailing sl time]),"stop loss","")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E10:E69Expression=AND(MROUND(MOD($D10,1),1/24)>=MROUND($K$4,1/24),MROUND(MOD($D10,1),1/24)<=MROUND($K$5,1/24))textNO



Mini Sheet 2:


STOP LOSS LOOKUP PROBLEM - V 001.xlsx
JKLMNOPQRSTU
4open time05:00
5close time19:00
6
7
8this is incorrectthis is incorrectthis is correct
9dateopen idclose idstop loss pointsopen priceclose pricefixed sl trigger pricefixed sl timetrailing sl trigger pricetrailing sl time incorrecttrailing sl time
1020/02/202120/02/2021 05:0020/02/2021 19:0053934.013922.783929.0120/02/2021 12:003931.8120/02/2021 09:0020/02/2021 09:00
1121/02/202121/02/2021 05:0021/02/2021 19:00123918.723896.733906.7221/02/2021 16:003908.9621/02/2021 12:0021/02/2021 16:00
1222/02/202122/02/2021 05:0022/02/2021 19:0083851.733831.263843.7322/02/2021 12:003863.2122/02/2021 05:0022/02/2021 10:00
Sheet1
Cell Formulas
RangeFormula
L10:L12L10=MROUND(K10+$K$4,1/24)
M10:M12M10=MROUND(K10+$K$5,1/24)
O10:O12O10=XLOOKUP([@[open id]],data_table[date / time],data_table[stock price])
P10:P12P10=XLOOKUP([@[close id]],data_table[date / time],data_table[stock price])
Q10:Q12Q10=[@[open price]]-[@[stop loss points]]
R10:R12R10=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:S12S10=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:T12T10=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:U12U10=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]))
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Watch MrExcel Video

Forum statistics

Threads
1,132,977
Messages
5,656,192
Members
418,289
Latest member
Kjohno

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top