XLOOKUP Conundrum

L

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
149
XLfriend777
XLfriend777
M
  • Question
Auto adjust row heights based on specific column
Replies
1
Views
154
XLfriend777
XLfriend777
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
367
vampsthevampyre
V
alm395
  • Question
How do you perform a calculation based on status entry?
Replies
2
Views
561
KRice
K
P
  • Question
IF and Xlookup not showing expected result
Replies
2
Views
695
PaulyK
P

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

Share this page

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
Back
Top