financeazure-data-explorerkqlback-testing

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


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.

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


Solution

  • Investopedia for a good explanation about trailing stop


    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