so I am doing some backtesting for some trading strategies on the stock market on Google Bigquery and I would like to set a trailing stop loss of 1% away from the entered price. If the stock price went up by let's say 5%, the trailing stop loss will move up 5% as well. If the stock price went down, the trailing stop loss will not change. (https://www.investopedia.com/terms/t/trailingstop.asp)
I have this table which shows my signal to enter and the exit column will show a value of 1 if the price goes below the trailing stop loss price, which means the trade is exited.
This is the table I have so far:
date price entry_signal
30/06/2018 95 0
01/07/2018 100 1
02/07/2018 103 0
03/07/2018 105 0
04/07/2018 104.50 0
05/07/2018 101 0
I would like to have a column showing what is the trailing stop loss at every date. The trailing stop loss is first set as 99% of the price on 01/07/2018 when the enter_signal = 1, where trade is executed on this date.
When the price moves up by y%, the trailing stop loss will move up by y% as well. However if the price goes down, the trailing stop loss will not change from its last value.
When the price <= trailing stop loss, the trade is exited and there will be an exit_signal of 1...
I am currently stuck at not having the trailing stop loss to move down by y% if the price move down by y% as well....
Desired table outcome:
date price trailing stop loss entry_signal exit_signal
30/06/2018 95 NULL 0 0
01/07/2018 100 99 1 0
02/07/2018 103 101.97 0 0
03/07/2018 105 103.95 0 0
04/07/2018 104.50 103.95 0 0
05/07/2018 101 103.95 0 1
This was my original code:
SELECT
date, price, entry_signal,
GREATEST(trailing_stop_loss, 0.99 * price) AS trailing_stop_loss
FROM (
SELECT
date, price, entry_signal,
LAST_VALUE(trailing_stop_loss IGNORE NULLS) OVER (ORDER BY DATE) AS trailing_stop_loss
FROM (
SELECT
date, price, entry_signal,
IF(entry_signal * 0.99 * price > 0, 0.99 * price, NULL) AS trailing_stop_loss
FROM table
)
)
Table I have obtained:
date price trailing stop loss entry_signal
30/06/2018 95 NULL 0
01/07/2018 100 99 1
02/07/2018 103 101.97 0
03/07/2018 105 103.95 0
04/07/2018 104.50 103.455 0
05/07/2018 101 99.99 0
Below is for BigQuery Standard SQL
I am currently stuck at not having the trailing stop loss to move down by y% if the price move down by y% as well....
#standardSQL
WITH temp1 AS (
SELECT day, price, entry_signal,
UNIX_DATE(PARSE_DATE('%d/%m/%Y', day)) day_as_days,
COUNTIF(entry_signal = 1) OVER(ORDER BY UNIX_DATE(PARSE_DATE('%d/%m/%Y', day))) grp
FROM `project.dataset.table`
), temp2 AS (
SELECT day, price,
0.99 * price AS trailing_stop_loss,
IFNULL(price > LAG(price) OVER(PARTITION BY grp ORDER BY day_as_days), TRUE) AS up,
entry_signal, grp, day_as_days
FROM temp1
)
SELECT day, price, trailing_stop_loss, entry_signal,
IF(price > trailing_stop_loss, 0, 1) AS exit_signal
FROM (
SELECT day_as_days, day, price, entry_signal,
IF(up, trailing_stop_loss, arr[OFFSET(0)]) trailing_stop_loss
FROM (
SELECT day_as_days, day, price, up, trailing_stop_loss, entry_signal,
ARRAY_AGG(trailing_stop_loss) OVER(PARTITION BY grp ORDER BY IF(up, day_as_days, 0) DESC) arr
FROM temp2
)
)
-- ORDER BY day_as_days
with result
Row day price trailing_stop_loss entry_signal exit_signal
1 30/06/2018 95.0 94.05 0 0
2 01/07/2018 100.0 99.0 1 0
3 02/07/2018 103.0 101.97 0 0
4 03/07/2018 105.0 103.95 0 0
5 04/07/2018 104.5 103.95 0 0
6 05/07/2018 101.0 103.95 0 1
Desired table outcome: ...
As you can see, above query at least partially addresses your stuck at point - while I am not sure what the whole picture is and what else needs to be addressed - even if your overall problem is still not fully addressed - i feel like your particular question is answered.
So, I hope above will unblock you and you can complete your challenge by yourself :o)
Btw, I used below dummy data (from your question)
WITH `project.dataset.table` AS (
SELECT '30/06/2018' day, 95 price, 0 entry_signal UNION ALL
SELECT '01/07/2018', 100, 1 UNION ALL
SELECT '02/07/2018', 103, 0 UNION ALL
SELECT '03/07/2018', 105, 0 UNION ALL
SELECT '04/07/2018', 104.50, 0 UNION ALL
SELECT '05/07/2018', 101, 0
)