Azure Data Explorer (Kusto/KQL) Financial Asset Backtesting Trail Stop

Question

I have a data set of financial asset prices over time and I'd like to mimic a trail stop for back testing strategies against this data set.

Trail stops are a type of trade order supported by some online brokers that are used as a stop loss or profit protection when opening a position, a trail stop is placed to automatically stop loss when a price condition is met.

The trail stop order will follow an asset price as it increases, and stay at the max during the time the position is open, once the asset price falls below the trail stop max, the position will be closed by the broker.

In this case the trail stop is a percentage of asset price. i.e. asset price less 3%.

I've tried a number of approaches, including summarization and the scan operator, and can't seem to land on a working prototype.

Below is an example data table of an asset with price changes over time.

//Trail Stop Properties:
//Trail stop will follow an asset price as it increases 
//  and remain at the max of the asset price increase during an open position
//  the position will be closed when the price is less than 
//    or equal to the trail stop value.

//Usually the stop is set with a percentage of loss from the trailing price.
//i.e. in the below example the trailing stop is 0.03 or 3% of the asset price.

let trailstop = double(0.03);
let assets = datatable 
(
  Timestamp:datetime, Symbol:string, StrikePrice:double, CallPremium:double, 
  PositionId:int
)
[
    datetime(2022-03-16T13:57:55.815Z), 'SPY' ,432, 2.46, 1,
    datetime(2022-03-16T14:00:55.698Z), 'SPY' ,432, 2.48, 1,
    datetime(2022-03-16T14:01:15.876Z), 'SPY' ,432, 2.49, 1,
    datetime(2022-03-16T14:08:25.536Z), 'SPY' ,431, 2.45, 1,
    datetime(2022-03-16T14:18:25.675Z), 'SPY' ,434, 2.40, 1,
    datetime(2022-03-16T14:21:50.887Z), 'SPY' ,434, 2.40, 2,
    datetime(2022-03-16T14:35:00.835Z), 'SPY' ,434, 2.33, 2
]
;
assets
| sort by Timestamp asc
| extend TrailStop = round(CallPremium - (CallPremium * trailstop),2)
| extend rn = row_number()

Output

2022-03-16T13:57:55.815Z    SPY 432 2.46    1   2.39    1
2022-03-16T14:00:55.698Z    SPY 432 2.48    1   2.41    2
2022-03-16T14:01:15.876Z    SPY 432 2.49    1   2.42    3
2022-03-16T14:08:25.536Z    SPY 431 2.45    1   2.38    4
2022-03-16T14:18:25.675Z    SPY 434 2.4     1   2.33    5
2022-03-16T14:21:50.887Z    SPY 434 2.4     2   2.33    6
2022-03-16T14:35:00.835Z    SPY 434 2.33    2   2.26    7

If the trail stop worked properly and there were position open and close columns to indicate when the trail stop happened, resulting in a closed position, the result set would look like the output of the following data table.

let outcomes = datatable 
(
    Timestamp:datetime, Symbol:string, StrikePrice:double, CallPremium:double, 
    PositionId:int, TrailStop:double, PositionOpen:int, PositionClose:int
)
[
    datetime(2022-03-16T13:57:55.815Z), 'SPY', 432, 2.46, 1, 2.39, 1, 0,
    datetime(2022-03-16T14:00:55.698Z), 'SPY', 432, 2.48, 1, 2.41, 1, 0,
    datetime(2022-03-16T14:01:15.876Z), 'SPY', 432, 2.49, 1, 2.42, 1, 0,
    datetime(2022-03-16T14:08:25.536Z), 'SPY', 431, 2.45, 1, 2.42, 1, 0,
    datetime(2022-03-16T14:18:25.675Z), 'SPY', 434, 2.40, 1, 2.42, 0, 1,
    datetime(2022-03-16T14:21:50.887Z), 'SPY', 434, 2.40, 2, 2.33, 1, 0,
    datetime(2022-03-16T14:35:00.835Z), 'SPY', 434, 2.33, 2, 2.26, 0, 1
]
;
outcomes
| sort by Timestamp asc
| extend rn = row_number()

Output

    2022-03-16T13:57:55.815Z    SPY 432 2.46    1   2.39    1   0   1
    2022-03-16T14:00:55.698Z    SPY 432 2.48    1   2.41    1   0   2
    2022-03-16T14:01:15.876Z    SPY 432 2.49    1   2.42    1   0   3
    2022-03-16T14:08:25.536Z    SPY 431 2.45    1   2.42    1   0   4
    2022-03-16T14:18:25.675Z    SPY 434 2.4     1   2.42    0   1   5
    2022-03-16T14:21:50.887Z    SPY 434 2.4     2   2.33    1   0   6
    2022-03-16T14:35:00.835Z    SPY 434 2.33    2   2.26    0   1   7

The end result would be two opened and closed positions.

Position 1 opened (rn=1) at 2.46 and closed (rn=5) at 2.42
Position 2 opened (rn=6) at 2.40 and closed (rn=7) at 2.33

Any help, ideas, or guidance would be much appreciated.

score 1 · Accepted Answer · answered Mar 16 '22 at 20:24

Investopedia for a good explanation about trailing stop

Order by position & timestamp
Use prev() to identify the starting of a new position.
Use scan() to calculate running max of CallPremium (always goes up, resets for a new position).
Compare each CallPremium to the running max and check if trailing stop achieved.

let trailstop = double(0.03);
let assets = datatable 
(
  Timestamp:datetime, Symbol:string, StrikePrice:double, CallPremium:double, 
  PositionId:int
)
[
    datetime(2022-03-16T13:57:55.815Z), 'SPY' ,432, 2.46, 1,
    datetime(2022-03-16T14:00:55.698Z), 'SPY' ,432, 2.48, 1,
    datetime(2022-03-16T14:01:15.876Z), 'SPY' ,432, 2.49, 1,
    datetime(2022-03-16T14:08:25.536Z), 'SPY' ,431, 2.45, 1,
    datetime(2022-03-16T14:18:25.675Z), 'SPY' ,434, 2.40, 1,
    datetime(2022-03-16T14:21:50.887Z), 'SPY' ,434, 2.40, 2,
    datetime(2022-03-16T14:35:00.835Z), 'SPY' ,434, 2.33, 2
]
;
assets
| sort by PositionId asc, Timestamp asc
| extend PositionId_start = prev(PositionId) != PositionId
| scan declare (CallPremium_running_max:double = double(null))
with
(
    step s1 : true => CallPremium_running_max = 
                        max_of(iff(PositionId_start,double(null),s1.CallPremium_running_max),CallPremium);
) 
| extend TrailStop = round(CallPremium_running_max*(1-trailstop),2)
| extend PositionOpen = iff(CallPremium <= TrailStop,1,0)
| extend PositionClose = 1 - PositionOpen

Timestamp	Symbol	StrikePrice	CallPremium	PositionId	PositionId_start	CallPremium_running_max	TrailStop	PositionOpen	PositionClose
2022-03-16T13:57:55.815Z	SPY	432	2.46	1	true	2.46	2.39	0	1
2022-03-16T14:00:55.698Z	SPY	432	2.48	1	false	2.48	2.41	0	1
2022-03-16T14:01:15.876Z	SPY	432	2.49	1	false	2.49	2.42	0	1
2022-03-16T14:08:25.536Z	SPY	431	2.45	1	false	2.49	2.42	0	1
2022-03-16T14:18:25.675Z	SPY	434	2.4	1	false	2.49	2.42	1	0
2022-03-16T14:21:50.887Z	SPY	434	2.4	2	true	2.4	2.33	0	1
2022-03-16T14:35:00.835Z	SPY	434	2.33	2	false	2.4	2.33	1	0

Fiddle

Thank you for the great answer @David דודו Markovitz. I made one small change to align the Position Open/Close columns values. `| extend PositionOpen = iff(CallPremium > TrailStop,1,0)` — Damien Johnston, Mar 18 '22 at 13:33
Tbh, the original data I posted is a bit contrived, the real market data used for back testing doesn't have a PositionId column. I created it as part of the example data set. In reality the market data in use is; time, asset, and price based. The goal of back testing, in this case, is to identify when positions will be opened due to indicators from market conditions and closed due to trail stop order definitions. The PositionId values would be an additional grouping added by the process of starting a position. i.e. When a position is opened value at PositionId+1 for all rows until stopped out. — Damien Johnston, Mar 18 '22 at 13:53
My pleasure, Damien. If a change to the question is needed, I would suggest keep the original version, approve the answer and open a new post for the altered version — David דודו Markovitz, Mar 18 '22 at 14:43
Thanks @David דודו Markovitz, I've marked this question as answered and posted a new question as a follow-up from this question. For reference the new question id is 71529994 and can be found at [Follow-up](https://stackoverflow.com/questions/71529994/azure-data-explorer-kusto-kql-financial-asset-backtesting-trail-stop-follow-up) — Damien Johnston, Mar 18 '22 at 16:12

Azure Data Explorer (Kusto/KQL) Financial Asset Backtesting Trail Stop

1 Answers1

Linked