0

This is a follow-up question from Stack Overflow question 71501098

Original 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.

There is a modification to the original data set used for example and mimics real world market data along with the over all goal more accurately.

The original data I posted was contrived in that, 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. PositionId values would be an additional grouping column added by the process of starting (opening) a position. i.e. When a position is opened value at the same PositionId for all rows until stopped out. The next open position found is PositionId + 1

See Investopedia for trail stop definition:

Investopedia

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

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

let trailstop = double(0.03);
let assets = datatable 
(
  Timestamp:datetime, Symbol:string, StrikePrice:double, CallPremium:double 
)
[
    datetime(2022-03-16T13:57:55.815Z), 'SPY' ,432, 2.46,
    datetime(2022-03-16T14:00:55.698Z), 'SPY' ,432, 2.48,
    datetime(2022-03-16T14:01:15.876Z), 'SPY' ,432, 2.49,
    datetime(2022-03-16T14:08:25.536Z), 'SPY' ,431, 2.45,
    datetime(2022-03-16T14:18:25.675Z), 'SPY' ,434, 2.40,
    datetime(2022-03-16T14:21:50.887Z), 'SPY' ,434, 2.40,
    datetime(2022-03-16T14:35:00.835Z), 'SPY' ,434, 2.33
]
;
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    2.39    1
2022-03-16T14:00:55.698Z    SPY 432 2.48    2.41    2
2022-03-16T14:01:15.876Z    SPY 432 2.49    2.42    3
2022-03-16T14:08:25.536Z    SPY 431 2.45    2.38    4
2022-03-16T14:18:25.675Z    SPY 434 2.4     2.33    5
2022-03-16T14:21:50.887Z    SPY 434 2.4     2.33    6
2022-03-16T14:35:00.835Z    SPY 434 2.33    2.26    7

If the trail stop worked properly there would be an output with PositionOpen, PositionClose and PositionId columns.

These columns would show when a position opened, closed, and group all rows in a position. Meaning an indicator triggered a position open and the trail stop happened at some later point, resulting in a closed position, the result set would look something 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, with a position grouping column.

  • 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.

Damien Johnston
  • 23
  • 2

1 Answers1

0

We'll be using scan operator to calculate the current TrailStop value and afterwards set the TradeOpen and TradeClose values accordingly. Finally we'll use row_cumsum() on TradeClose to calculate the TradeId.

let trailstop = double(0.03);
let assets = datatable 
(
  Timestamp:datetime, Symbol:string, StrikePrice:double, CallPremium:double 
)
[
    datetime(2022-03-16T13:57:55.815Z), 'SPY' ,432, 2.46,
    datetime(2022-03-16T14:00:55.698Z), 'SPY' ,432, 2.48,
    datetime(2022-03-16T14:01:15.876Z), 'SPY' ,432, 2.49,
    datetime(2022-03-16T14:08:25.536Z), 'SPY' ,431, 2.45,
    datetime(2022-03-16T14:18:25.675Z), 'SPY' ,434, 2.40,
    datetime(2022-03-16T14:21:50.887Z), 'SPY' ,434, 2.40,
    datetime(2022-03-16T14:35:00.835Z), 'SPY' ,434, 2.33
];
assets
| sort by Timestamp asc
| extend TrailStop = round(CallPremium - (CallPremium * trailstop),2)
| scan with 
(
    step s: true => TrailStop = iff(s.TrailStop > TrailStop and s.CallPremium > s.TrailStop, s.TrailStop, TrailStop);
)
| extend TradeOpen = CallPremium > TrailStop, TradeClose = CallPremium <= TrailStop
| extend TradeId = row_cumsum(tolong(prev(TradeClose))) + 1, rn = row_number()
Timestamp Symbol StrikePrice CallPremium TrailStop TradeOpen TradeClose TradeId rn
2022-03-16 13:57:55.8150000 SPY 432 2.46 2.39 1 0 1 1
2022-03-16 14:00:55.6980000 SPY 432 2.48 2.41 1 0 1 2
2022-03-16 14:01:15.8760000 SPY 432 2.49 2.42 1 0 1 3
2022-03-16 14:08:25.5360000 SPY 431 2.45 2.42 1 0 1 4
2022-03-16 14:18:25.6750000 SPY 434 2.4 2.42 0 1 1 5
2022-03-16 14:21:50.8870000 SPY 434 2.4 2.33 1 0 2 6
2022-03-16 14:35:00.8350000 SPY 434 2.33 2.33 0 1 2 7
RoyO
  • 261
  • 1
  • 4