A Preliminary Analysis of EMA Period Lengths and Price Action in a Buy-Sell Model
This blog introduced on March 26, 2026, an earlier version of the Buy on Proper Order and Sell on Dynamic Stop Loss Orders Buy-Sell model. Proper order was defined in that post by a relationship between a close price and two EMA values, such as close > ema_21 and ema_21 > ema_100. In this post, the model dynamically updates upper and lower boundary values based on price action to lock in a portion of accumulated gains or at least constrain the size of a loss associated with a trade. This post extends the March 26 post by
updating the code for implementing the model,
diving more deeply into how price action can change model performance, and
testing the model over a broader range of tickers examined as well as over longer timeframes.
The model examined in this post uses proper order relationships for close prices and exponential moving averages with different period lengths to discover buy dates for securities. Additionally, the model uses dynamic stop orders and limit values based on daily price action to detect sell dates that have the potential to preserve accumulated gains on winning trades and constrain losses on losing trades. This post demonstrates the effectiveness of the model with different proper order definitions as well as stop and limit orders. Stop orders are simulated by the daily close price falling below the lower boundary value. Limit orders are simulated by the daily close price rising above the upper boundary value, which in turn leads to a reset of the upper and lower boundary values for subsequent trading days. You are invited to leave comments for this post with suggestions for improvements or additional backtesting that will enable you to become a better trader/analyst.
A Review of the Buy on Proper Order and Sell on Dynamic Stop Loss Orders Buy-Sell Model
The model has two core elements: one that specifies when to buy a security and a second that specifies when to sell a security.
The buy element is based on a simple expression, such as close > ema_21 > ema_100. When the close price initially exceeds ema_21 and ema_21 is greater than > ema_100, then the terms in the expression are said to be in proper order.
Close prices move more quickly up and down than EMA values with a period length of 21 trading days. Likewise, ema_21 values move more quickly up and down than ema_100 values. At this point in the model’s development, the EMA period lengths are not tied to a single pair of lengths. So, you can use a shorter period length of 10 and a longer period length of 200. In fact, this post examines results from two different model implementations
one based on this expression: close > ema_21 > ema_63, and
another based on this expression: close > ema_21 > ema_100.
When the expression terms are observed to be in proper order and a security is not currently owned, then the model specifies a buy at the open price for the next trading day.
The sell element is based on the price action after the most recent buy date as well as a pair of upper and lower boundary values. When the price during the current trading day rises above the upper boundary value, the limit and stop orders are revised upward for use on the next trading day. When the price for the current trading day falls below the currently active stop order, the security is sold at the open of the next trading day. This approach ensures a conservative backtest by assuming that while a sell signal is triggered during the current day, the execution takes place at the earliest opportunity on the next trading day. This is the only way the model exits a trade.
Both the upper and lower boundary values can have their values moved up to a higher level whenever the current price exceeds the limit value. This action is referred to as a level-up action. Across multiple days within a trade, the upper and lower boundary values can have multiple level-up actions.
The following spreadsheet excerpt walks you through an initial buy of a security (row 2), a subsequent date where the close price exceeds the current upper limit (row 3). Next, it shows the outcome of the first level-up action where the lower boundary and the upper boundary values are recalculated in row 4. Finally, row 5 shows the exit. The security is sold at the opening price of the day following the stop-loss violation (104.00), which is below the previous day's lower limit (104.50).
T-SQL code for Implementing the Proper Order and Sell on Dynamic Stop Loss Order Model
If you are a trader or a trader/analyst, you probably have an understanding of how nuanced and even flexible trading rules need to be for different assets over different timeframes. In contrast, a programmer analyst who is writing code to backtest a model will typically require a precise set of rules that are invariant across securities and timeframes. One way of accommodating the needs of different stakeholders who are responsible for managing different asset classes is to implement the backtest with different model parameter sets and securities from a variety of different asset classes.
The following T-SQL model has options for toggling across four different parameter sets. When backtesting a model with different parameter sets, you can concurrently discover overall patterns and unique responses for how different parameters impact the way a model fits different aspects of an underlying historical dataset.
The code below allows you to switch between two different proper order definitions – one with a shorter anchor (ema_63) and another with a longer anchor (ema_100). In addition, the code is designed to facilitate running with conservative or aggressive ratios for the upper and lower boundary values. A conservative set of lower and upper boundaries are, respectively 5% below and 10% above, the initial open price for a trade or the last level-up value set when the daily close price exceed the upper boundary value. In contrast, an aggressive set of lower and upper boundaries are, respectively 2% below and 4% above, the initial open price for a trade or the last level-up value.
The script commences with a use statement for setting the default database from which to run the script; your source historical data for backtesting the model must reside in this database. By scanning the script, you can see that there are five steps within it plus a trailing additional performance reporting step. Here’s a brief summary of the code within each step.
Step 1: Data Preparation The script gathers the historical prices and EMAs for your tickers. It uses a "look-ahead" function (LEAD) to grab the Next Day’s Opening Price, executed only at the open of the trading day following the signal confirmed at the close.
Step 2: Tracking Initialization A temporary results table is created to store every trade's details (Entry, Exit, level-ups, and Return). The script also sets up "tracking variables" that act like a memory bank to remember if you currently own a stock and at what price you bought it.
Step 3: The Market Day Loop This step is the model’s engine because it "walks" through market history one day at a time, ticker by ticker, evaluating whether to hold, buy, or sell based on that specific day’s historical close price.
Step 4: Entry Logic (The "Proper Order" Check) If you don't currently own a security, the script looks for "Proper Order" (e.g., Close > EMA 21 > EMA 100). If found, it triggers a buy at the next day’s open and sets the initial upper and lower price boundaries.
Step 5: Level-up and Dynamic Stop Logic If you already own a security, the script checks two things:
The Level-Up: If the price exceeds the Upper Boundary, it ratchets both boundaries higher to lock in gains.
The Stop Loss: If the price falls below the Lower Boundary, it triggers a sell for the next day's open.
Final Step: Performance Reporting Once all days are processed for all tickers, the script aggregates the data to show you the total number of trades and the cumulative percentage return for each ticker.
use securitytradinganalytics;
go
-- step 1: Data Preparation
declare @data_ordered table (
row_id int identity(1,1) primary key,
ticker varchar(10),
[date] date,
[open] decimal(18,4),
[close] decimal(18,4),
ema_21 decimal(18,4),
ema_slow decimal(18,4),
next_day_open decimal(18,4)
);
insert into @data_ordered (ticker, [date], [open], [close], ema_21,
ema_slow, next_day_open)
select
ticker,
[date],
[open],
[close],
ema_21,
-- TOGGLE TREND ANCHOR HERE:
-- ema_63, -- for the shorter trend anchor
ema_100, -- for the longer trend anchor
lead([open]) over (partition by ticker order by [date])
from dbo.stockhistory_with_emas
order by ticker, [date];
-- step 2: Tracking Initialization
if object_id('tempdb..#results_dynamic_grid')
is not null drop table #results_dynamic_grid;
create table #results_dynamic_grid (
ticker varchar(10),
buy_dt date,
entry_pr decimal(18,4),
sell_dt date,
exit_pr decimal(18,4),
rungs_climbed int,
pct_return float
);
declare @i int = 1, @max_i int = (select count(*) from @data_ordered);
declare @in_pos bit = 0, @entry_pr decimal(18,4), @buy_dt date,
@low_b decimal(18,4), @up_b decimal(18,4), @lvls int, @trade_ticker varchar(10);
-- step 3: The Market Day Loop
while @i <= @max_i
begin
declare @t varchar(10), @d date, @c decimal(18,4), @e21 decimal(18,4),
@eslow decimal(18,4), @nxt_o decimal(18,4);
select
@t = ticker, @d = [date], @c = [close],
@e21 = ema_21, @eslow = ema_slow, @nxt_o = next_day_open
from @data_ordered
where row_id = @i;
if @in_pos = 1 and @t <> @trade_ticker set @in_pos = 0;
-- step 4: Entry Logic (the "proper order" check)
if @in_pos = 0
begin
if @c > @e21 and @e21 > @eslow and @nxt_o is not null
begin
set @in_pos = 1;
set @trade_ticker = @t;
set @buy_dt = @d;
set @entry_pr = @nxt_o;
-- TOGGLE RATIOS HERE:
-- Conservative Ratios (5% Floor / 10% Ceiling)
-- set @low_b = @entry_pr * 0.95;
-- set @up_b = @entry_pr * 1.10;
-- Aggressive Ratios (2% Floor / 4% Ceiling)
set @low_b = @entry_pr * 0.98;
set @up_b = @entry_pr * 1.04;
set @lvls = 0;
end
end
-- step 5: Level-up and Dynamic Stop Logic
else
begin
-- if price hits our upper goal, we climb a "rung"
if @c >= @up_b
begin
set @lvls = @lvls + 1;
-- TOGGLE RATIOS HERE (Must match Step 4):
-- Conservative Reset
-- set @low_b = @up_b * 0.95;
-- set @up_b = @up_b * 1.10;
-- Aggressive Reset
set @low_b = @up_b * 0.98;
set @up_b = @up_b * 1.04;
end
-- if price drops below floor, we sell at the next open
if @c <= @low_b and @nxt_o is not null
begin
insert into #results_dynamic_grid
values (@t, @buy_dt, @entry_pr, @d, @nxt_o, @lvls,
((cast(@nxt_o as float) - @entry_pr)/@entry_pr)*100);
set @in_pos = 0;
end
end
set @i = @i + 1;
end
-- Performance Reporting: display results by ticker
select
ticker,
count(*) as total_trades,
cast(sum(pct_return) as decimal(18,2)) as cumulative_return_pct
from #results_dynamic_grid
group by ticker
order by ticker;
There are three TOGGLE segments in the preceding script. These segments facilitate switching between two different proper order definitions as well as two different sets of upper and lower boundary values.
The TOGGLE segment in step 1 permits you to switch between two different proper order definitions. The proper order definition in step 1 within the preceding script results in a proper order definition based on close > ema_21 > ema_100. If you moved the comment marker from ema_63 to ema_100, the proper order definition would switch to close > ema_21 > ema_63.
The TOGGLE segments in steps 4 and 5 need to be synchronized.
In step 4, the script is currently set to aggressive (2%/4%) boundary values. To switch to conservative (5%/10%) boundary values, place the comment markers in front of the 0.98 and 1.04 lines, and remove the markers from the 0.95 and 1.10 lines.
In Step 5, you must perform an analogous "move" of the comment markers. While Step 4 initializes the boundaries for a new trade, Step 5 resets them for a "level-up" action. Steps 4 and 5 must use the same ratios to ensure the model remains consistent as the trade unfolds.
The following screenshot shows the result sets for each combination of proper order definitions with each set of upper and lower boundary values. In total, four scenarios are summarized in the table. To clarify which result set is for which scenario
comments in column I indicate whether the cumulative percent total was larger for the conservative wider boundaries (5% and 10%) or the aggressive narrower boundaries (2% and 4%),
a yellow background for rows 7 and 24 highlight the impact of the conservative boundary values relative to the aggressive boundary values for the NVDA ticker, and
average trade count and cumulative percent return values appear in rows 15 and 32.
In general, the model returned much better returns when run with conservative upper and lower boundary values than with aggressive upper and lower values. For example,
For each of the ten tickers tracked with a shorter EMA anchor value (63 period length), each conservative upper and lower boundary pair returned superior results to corresponding aggressive upper and lower boundary pairs. For the bottom two result sets run with an anchor EMA period length of 100, superior cumulative return percentages resulted with conservative boundaries for nine out of ten ticker comparisons.
Across longer and shorter anchor values, the average cumulative percentage was over 100 points greater for the conservative upper and lower boundaries than the aggressive upper and lower boundaries. One possible reason for this outcome is that the aggressive boundaries lead to many more trades – some of which likely terminated trades before trade gains reached their peak value.
The impact of the conservative upper and lower boundaries seems to be particularly pronounced for NVDA. Across scenarios with an ema_63 anchor or an ema_100 anchor, the cumulative percentage return was about 400 percentage points greater for the conservative boundary values than with aggressive boundary values.
Next Steps
The next step is to pull historical prices for the ten tickers listed for each of the four scenarios in the preceding table. After pulling historical prices for each ticker you will also need to compute EMA values with period lengths of 21, 63, and 100 days. By completing these steps, you will confirm your understanding of how to run the model.
For the example in this post, historical prices for each of the ten tickers were derived with Excel’s STOCKHISTORY function. Historical prices date back to the initial trading date for each ticker. The initial trading date typically varies from one ticker to the next.
You can find an introduction to Excel’s STOCKHISTORY function in the prior post titled “An Introduction to Retrieving Historical Security Prices with GOOGLEFINANCE and STOCKHISTORY”.
An example of how to compute exponential moving averages for historical price data downloaded with Excel’s STOCKHISTORY function is available in the post titled “An Initial Evaluation of Buy on Proper Order and Sell on Dynamic Stop Loss Orders”.
After you confirm that you can duplicate the results in this post, you are ready to apply the same data ingestion and calculation techniques to another set of tickers that are of special interest to you.
Also consider modifying lower and upper boundary ratios. This post examined 2%/4% and 5%/10% boundary ratios. Recall that 5%/10% boundary ratios were the superior choice in almost every scenario—outperforming the aggressive ratios in 19 of the 20 tests—as evidenced by superior cumulative percentage returns achieved with significantly fewer trades. This suggests that you may want to investigate 10%/20% boundary ratios to see if see if you can obtain even greater cumulative percentage returns with even fewer trades.
Comments
Post a Comment