Login Page - Create Account

Support Board


Date/Time: Mon, 17 Mar 2025 12:12:50 +0000



[Programming Help] - Spreadsheet trading & Backtests

View Count: 1549

[2022-08-11 00:35:06]
tuanluu20902 - Posts: 24
I have several question regarding spreadsheet trading & backtesting process:

1. Is it possible split exits (profit taking) into multiple targets? Each target is calculated as a function of multiples of ATRs from the entry level. Setting a target via the Trade Window is not an option because it is static.

2. How to backtest more accurately, it seems that when backtesting, entry is taken only at the candle's high/low/close and not a realistic entry that would be taken live as the data is forming
a. For example, during live trading - let's say the entry is to buy when the candle hits the 20EMA, that would be where the live entry is. However, during backtest, the entry would be at the candle's high/low/close (depending on the setting) which would make the backtest result incorrect.
So far the only solution I've found to this is to backtest via Live Replay but that takes a ton of computing power & time to run the live replay.
[2022-08-11 03:08:51]
Sawtooth - Posts: 4185
1. You can use the Trade Window Attached Orders' multiple targets, then use the Trade Management by Study study to move the Nearest target to a calculated value in a Formula Column.
Attached Orders: Study Controlled Targets and Stops

You can also use a complicated formula in L3/N3, then set the exit quantity with a formula in J82/J83.


2. Since the Bar Based BackTest has those limitation, IMO, the most accurate backtest for intrabar entries is a Replay Chart using 'Accurate Trading System Back Test Mode'.
[2022-08-16 00:24:49]
tuanluu20902 - Posts: 24
Hi Tom,

Thank you for your guidance. Now regarding your point 1: can you give me an example of what a complicated formula entails? If J82 = TRUE, what formula would reference a T1 exit with quantity J83 and a T2 full exit?
Date Time Of Last Edit: 2022-08-16 00:31:03
[2022-08-16 02:33:30]
Sawtooth - Posts: 4185
can you give me an example of what a complicated formula entails?
L3:
=OR(AND(E3>=ATR+1price,E3<ATR+2price),E3>=ATR+2price)
N3:
=OR(AND(E3<=ATR-1price,E3>ATR-2price),E3<=ATR-2price)
J83:
=IF(AND(J8>0,E3>=ATR+1price,E3<ATR+2price),2,IF(AND(J8>0,E3>=ATR+2price,1,IF(AND(J8<0,E3<=ATR-1price,E3>ATR-2price),2,IF(AND(J8<0,E3<=ATR-2price,1,0))))

This will exit quantity of 2 for T1, and quantity of 1 for T2.

This has no stoploss exits. If you include a stoploss in the formulas, they are longer.
If you include the formula for each of the ATR prices, the formulas become unmanageable.
(You could use separate Formula Columns for each of the ATR prices, then reference them directly.)

You have a naked position when using L3/N3 without at least an OCO Attached Order disaster stop.

IMO, it's easier to use formulas that the Trade Management by Study study references, plus it has the advantage of having protective working orders.
[2022-08-19 23:17:33]
tuanluu20902 - Posts: 24
I've been running into a problem while doing the Live backtest replay with time charts, an issue that I don't run into with tick charts. After an entry, it seems that the algo would auto flatten. I can't find the source to where it would do something like that, that would only happens on time charts and not tick charts.
Do you happen to have heard of something similar or encounter something similar?
imageScreenshot 2022-08-19 191712.png / V - Attached On 2022-08-19 23:17:25 UTC - Size: 3.95 KB - 125 views
[2022-08-20 00:39:58]
Sawtooth - Posts: 4185
Do you happen to have heard of something similar or encounter something similar?
Not specifically.

Check the Trade Service Log for why the spurious exit occurred.
[2022-09-07 00:08:17]
OctoPi - Posts: 38
Software we made on top of SC does exactly what you are asking and then some.
You can use SC backtest replay and anchor target values to different multiples of ATR. If you'd like, you can anchor Targets to multiples ( or fractions ) of previous targets as well. Same with stops, sprinkle in some break-even after hitting target 1... any combination of what you may want basically.

We are launching for public in a few weeks, but currently taking a limited number of testers so you can sample the software.

EASY Automated Back-testing. We are licensing the software for $0 for limited time.
Reach out if interested.

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account