Proper Order Definition Backtests for Trading ETFs Based on Major Market Indexes
The Security Trading Analytics blog includes a growing series of posts featuring the Proper Order Gain Lock-in (POGL) model. This model specifies buy and sell dates for a trading instrument, such as SPY, based on a proper order relationship between closing prices, selected EMA series, and daily price action. A major driver behind this expanding series is the model's repeated success across different contexts. For those looking to get up to speed, a couple of these historical posts are accompanied by audio deep dives to help you easily learn more about the mechanics.
- A Preliminary Analysis of EMA Lengths and Price Action in a Buy-Sell Model
- What are Single-Stock ETFs and Should I Invest in Them?
- A Model to Lock-in Gains When Trading Single-stock ETFs
- Should I Trade Single-Stock Leveraged ETFs?
- Posts On Single-Stock ETFs and Modeling Buy-Sell Decisions
This analysis expands into uncharted territory by tracking three sets of tickers not previously evaluated by the POGL model.
- The first set tracks ETFs based on major market indexes.
- The second set tracks ETFs based on 3X leveraged factors relative to those same major market indexes.
- The third set tracks ETFs based on a 3X inverse leveraged factor. This marks the very first time the blog has featured coverage of inverse ETFs.
The most significant goal of the current post is to evaluate
the POGL model with several different pairs of EMA value sets. EMA value sets are critical for determining when
the POGL model issues a buy recommendation.
The current post evaluates POGL performance outcomes based on EMA series
with period lengths of 21, 50, 63, 100, and 200 days.
A secondary goal for the current post is to assess the
capability of ETFs based on a 3X inverse leveraged factor for major market
indexes to return superior performance when ETFs based on standard major market
index returns were returning relatively weak performance. The assessment strategy uses an updated
definition of proper order.
ETFs for Major Market Indexes Tracked in this Post
The following categories outline the 15 tickers and security names analyzed in this study.
The proper order definition for the first two ticker sets is: "close">"EMA_fast">"EMA_slow" .- Underlying Market Indexes (Core Price Action)
- DIA: Tracks the Dow Jones Industrial Average
- SPY: Tracks the S&P 500 Index
- QQQ: Tracks the Nasdaq-100 Index
- SOXX: Tracks the NYSE Semiconductor Index
- IWM: Tracks the Russell 2000 Index
- 3X Leveraged ETFs (3x Daily Percentage Change)
- UDOW: Tracks 3X DIA
- SPXL: Tracks 3X SPY
- TQQQ: Tracks 3X QQQ
- SOXL: Tracks 3X SOXX
- TNA: Tracks 3X IWM
Because the third set of tickers are 3X inverse ETFs, the proper order definition for the third set of tickers is flipped to: "close"<"EMA_fast"<"EMA_slow" . Applying this flipped mathematical definition aligns the signals to track the performance of these corresponding short-side instruments:
- 3X Inverse Leveraged ETFs
- SDOW: tracks 3X Inverse DIA
- SPXU: tracks 3X Inverse SPY
- SQQQ: tracks 3X Inverse QQQ
- SOXS: tracks 3X Inverse SOXX
- TZA: tracks 3X Inverse IWM
Downloading Historical Prices for Tickers Tracked in This Post
The following Excel STOCKHISTORY and Google Sheets GOOGLEFINANCE functions were used to derive valid historical prices for all fifteen tickers analyzed within this post.
Unfortunately, the STOCKHISTORY expression for the DIA ticker did not return valid historical prices. This can happen when the function's underlying data source (MSN Money / MSN Finance) does not have valid historical data for a ticker. A prior instance of the STOCKHISTORY function returning invalid historical prices was noted in the Building Thirty-four Ticker Historical Price Datasets with Excel’s STOCKHISTORY and Sheets’ GOOGLEFINANCE Functions post.
The Excel workbook excerpt below is updated with a column
denoting the source ticker for each worksheet.
Because two different worksheets exist for the DIA ticker, the tab names
for each DIA ticker are named, respectively, DIA_XL and DIA_GS. The following screenshot shows the DIA_GS tab
because it contains valid data for the DIA ticker.
The final processing step for converting the worksheet tabs to a single tall table in SQL Server is described in the How to Populate a Trading Database with Refinitiv, Excel, and SQL Server—Update 2 post. This step returns a fresh copy of the stockhistory_clean table in the default database, which is typically named SecurityTradingAnalytics in posts from this blog.
Calculating and Using Fast and Slow EMAs
The POGL model depends strongly on the interaction between fast and slow EMAs. For a buy signal to trigger, the close price for the current trading day must exceed both the designated fast and slow EMAs. EMAs with a shorter period length are termed "fast" EMAs because their values adapt more rapidly to underlying price updates, such as daily ETF close prices. Conversely, EMAs with a longer period length are considered "slow" EMAs because their values move more gradually relative to underlying price changes.
After importing historical ticker prices to SQL Server, then you will need a framework for computing EMAs for close prices in SQL Server. The “Recursive EMA Logic for SPY Across Multiple Period Lengths” section in the “Recursive EMA Logic in SQL Server Using Wide-Format Time Series Data” MSSQLTips.com article provides a code example for computing EMAs.
use SecurityTradingAnalytics
go
-- create and populate dbo.stockhistory_with_emas from
-- dbo.stockhistory_clean and
-- dbo.ticker_ema_history_from_sma_seed_v2
-- drop the table if it already exists to allow for
re-runs
if object_id('dbo.stockhistory_with_emas', 'u') is not
null
drop
table dbo.stockhistory_with_emas;
-- create the new table by joining the data
-- from dbo.stockhistory_clean
-- and dbo.ticker_ema_history_from_sma_seed_v2
select
sc.ticker,
sc.date,
sc.[open],
sc.[close],
ema21.ema as
ema_21,
ema50.ema
as ema_50,
ema63.ema
as ema_63,
ema100.ema
as ema_100,
ema200.ema
as ema_200
into dbo.stockhistory_with_emas
from [dbo].[stockhistory_clean] sc
left join (
select ticker,
date, ema
from
dbo.ticker_ema_history_from_sma_seed_v2
where period =
21
) ema21
on sc.ticker = ema21.ticker
and sc.date = ema21.date
left join (
select ticker,
date, ema
from
dbo.ticker_ema_history_from_sma_seed_v2
where period =
50
) ema50
on sc.ticker = ema50.ticker
and sc.date = ema50.date
left join (
select ticker,
date, ema
from
dbo.ticker_ema_history_from_sma_seed_v2
where period =
63
) ema63
on sc.ticker = ema63.ticker
and sc.date = ema63.date
left join (
select ticker,
date, ema
from
dbo.ticker_ema_history_from_sma_seed_v2
where period =
100
) ema100
on sc.ticker = ema100.ticker
and sc.date = ema100.date
left join (
select ticker,
date, ema
from
dbo.ticker_ema_history_from_sma_seed_v2
where period =
200
) ema200
on sc.ticker = ema200.ticker
and sc.date = ema200.date;
Visualizing EMA Warm-Up Layouts in SQL Server
When first working with database tables containing calculated EMA series, it is entirely normal to find the initial row layouts confusing. To help ground these concepts, the following five walkthrough descriptions and screenshot excerpts illustrate how row segments populate within the stockhistory_with_emas SQL Server table.
1. Initial 25 Trading Days for DIA_GS
- The first 20 trading days feature explicit NULL values in the ema_21 column. This occurs because a 21-day exponential moving average requires a complete 21-day trailing window to establish its baseline and remains mathematically undefined until that window fills.
- The 21st trading day (row 21) marks the very first non-null calculation. The value shown (92.89) is the exact arithmetic average of the first 21 sequential rows in the [close] column. This value serves as the foundational "seed" for all subsequent iterations in the 21-period EMA array.
- Subsequent rows (from row 22 onward) calculate weighted averages that favor more recent price action based on standard EMA computational rules. Refer back to the previously cited MSSQLTips.com article for a breakdown of the mathematical weighting multiplier.
2. The 50-Day EMA Threshold for DIA_GS
- Row 50 generates the initial non-null ema_50 value (94.85), which represents the basic arithmetic mean calculated across the first 50 entries of the [close] column.
- From row 51 onward, the trailing cells shift dynamically into standard EMA weighted average logic.
3. The 63-Day EMA Threshold for DIA_GS
The third screenshot centers on the launch of the 63-day calculation. Just like the prior tiers, rows 1 through 62 display empty spaces under the ema_63 header. Non-null tracking triggers explicitly on row 63 with a seed calculation of 96.18, while rows 64 through 67 begin utilizing the recursive smoothing multiplier.
4. Establishing the 100-Day EMA for UDOW
The final asset in alphabetical sequence is the ProShares UltraPro Dow30 (UDOW). The fourth screenshot captures the 100-day EMA launch window for this leveraged security, isolating the specific rows where the series goes live. Rows 72931 through 72935 remain NULL for the 100-day parameter, while row 72936 establishes the initial seed calculation (1.9235).
5. Establishing the 200-Day EMA for UDOW
The final screenshot in this instructional series displays the introduction of the longest baseline tier analyzed: the 200-day parameter. In the final five rows of this excerpt, the ema_200 column generates its first active values. Row 73036 marks the 200th day of data processing for this ticker group, printing the initial arithmetic seed value of 1.9471.
Summary Statistics for Proper Order Definition and Asset Pair Backtests
- proper order definition and
- asset pair for unleveraged versus leveraged ETFs within proper order definition.
The following table presents a summary of POGL model performance outcomes based on five different proper order definitions. Each row in the table displays total trades, average unleveraged portfolio return percentage, average leveraged portfolio return percentage, and leveraged portfolio return percentage advantage.
- the two best performing asset pairs are soxx/soxl and qqq/tqqq across all five proper order definitions, and
- additionally, the iwm/tna pair is consistently one of the two worse two performing ETFs, and
- finally, leveraged ETFs always outperform their corresponding unleveraged ETFS. This explains why the leveraged ETF portfolios in the preceding table have such a lopsided performance advantage relative to the unleveraged portfolios.
Applying an Adaptation of the POGL Model to Inverse ETFs
This section introduces initial coverage of inverse ETFs at this blog. The tickers seeking 3X inverse index performance are, respectively, SDOW, SPXU, SQQQ, SOXS, and TZA. The matching tickers for the ETFs based on corresponding standard major market indexes are, respectively, DIA, SPY, QQQ, SOXX, and IWM. Additional information on these tickers is available in the “ETFs for Major Market Indexes Tracked in this Post” section.
The exit and entry dates for ETFs based on standard major market indexes are applied to the ETF dates and prices from the ETFs based on 3X inverse factors. This process parallels the way entry and exit dates for leveraged major market ETFs were based on entry and exit dates for unleveraged major market indexes.
- Columns D and F show the entry and exit dates, respectively, for each ownership cycle.
- Columns J and K show, respectively, the return percentage for an ownership cycle. Column J shows the return percentage for the QQQ ticker, and column K shows the return percentage for the SQQQ ticker.
- Most of the time when the std_return_pct column value is positive and the inv_return_pct is negative.
- It is not easy to determine from the excerpted result set listing if positive inv_return_pct values are associated with negative std_return_pct values. This is true, in part, because only 3 of the 20 inv_return_pct values are positive. However, in the full set of ownership cycles for the QQQ_SQQQ ticker pair, there are a total 121 cycles – not just 20 cycles.
- However, it is easy to confirm that std_return_pct values are generally positive and inv_return_pct values are generally negative.
The following scattergram plots std_return_pct values along the horizontal axis and inv_return_pct values along the vertical axis. The scatter points are derived from all the ownership cycles in the QQQ_SQQQ tab in the preceding worksheet.
The scattergram also shows a regression line through the scatter points. Notice the slope of the line is -2.4178. This indicates that scatter points with a negative std_return_pct value generally have a positive inv_return_pct value. Furthermore, the R2 value .864 means that 86 percent of the variance for the inv_return_pct values can be explained by the std_return_pct values.
The next four scattergrams plot inv_return_pct values versus
std_return_pct values successively for the SOXX_SOXS, IWM_TZA, SPY_SPXY, and DIA_SDOW
ticker pairs in that order.
As you can see from the scattergrams, there is an inverse relationship between std_return_pct and inv_return_pct. The strength of the inverse relationship is reflected by the following table. When assessing the strength of a relationship across multiple pairs of variables, such as std_return_pct and inv_return_pct for all 5 ticker pairs, the standard p-value can overstate the true level of statistical significance. However, the raw p-values from the following table are very substantially beyond the 5% level of significance. Even when the Bonferroni adjusted p-value was calculated to account for the potential heightened probability of false positives with multiple tests, such as R2 values greater than 0, the strength of the relationship was still highly significant.
Concluding Comments
- For the cross of 5 ticker pairs and 5 proper order definitions (25 comparisons in total), the POGL model always uncovered a larger average return percentage for leveraged ticker pairs versus unleveraged ticker pairs.
- The 5 proper order definitions included 2 definitions with an EMA_slow period length value of 200 days and 3 definitions with an EMA_slow value of 63 or 100 days. Both of the proper order definitions with an EMA_slow period-length value of 200 days returned smaller average return percentages.
A secondary goal of this post is to assess the capability of ETFs based on a 3X inverse leveraged factor for major market indexes to return superior performance when ETFs based on standard major market index returns are returning relatively weak performance. A scattergram diagram with a linear regression and R2 analysis overwhelmingly confirmed the tendency for 3X inverse ETFs to produce better results when ETFs based on standard major market indexes are declining within an ownership cycle. Additional research is required to assess whether this clearly documented trend can result in profitable trading rules.
The Code Appendix
This appendix provides the full T-SQL source code used to execute the parametric backtests and generate the performance reporting grids featured in this post. The architecture isolates the quantitative logic into two separate structural components: a reusable database simulation engine (the Stored Procedure) and an automated execution wrapper (the Control Script). By splitting the calculation mechanics from the data extraction layer, this design ensures high performance and complete programmatic reproducibility when evaluating distinct moving average trend definitions across multiple index asset tracking pairs. This appendix also provides the T-SQL source code for backtest results for standard major market ETFs and 3X inverse ETFs.
Stored Procedure to Run the POGL Model Backtest
The dbo.sp_run_pogl_backtest stored procedure serves as the analytical core of the POGL model, designed to dynamically backtest trade lifecycle parameters across a target watchlist of index asset pairs. By accepting variable inputs for the fast and slow exponential moving averages (@fast_ema and @slow_ema), the procedure builds a dynamic string query context that automatically adjusts to the corresponding column definitions in the historical pricing repository. Structurally, it uses a local fast_forward cursor to iterate through the five mapped index configurations day-by-day, constructing local memory staging arrays to safely evaluate "proper order" trend entries, manage risk via dynamic price rungs, and execute trailing stop-loss exits. The primary design feature of the procedure is its persistent side-effect output layout: rather than printing a standard grid to the screen, it pushes the calculated, uncompounded arithmetic return percentages of each asset directly into a physical permanent repository table (dbo.pogl_parametric_results) to capture an isolated record of every trade.
create procedure [dbo].[sp_run_pogl_backtest]
@fast_ema int,
@slow_ema int
as
begin
set nocount on;
-- construct
parameter label (e.g., 'close > ema_21 > ema_63')
declare
@param_label varchar(30) = 'close > ema_' + cast(@fast_ema as varchar) + '
> ema_' + cast(@slow_ema as varchar);
-- temporary
mapping table for the 5 market pairs
if
object_id('tempdb..#ticker_map') is not null drop table #ticker_map;
create table
#ticker_map (u_ticker varchar(10), e_ticker varchar(10));
insert into
#ticker_map values
('dia_gs',
'udow'), ('spy', 'spxl'), ('qqq', 'tqqq'), ('soxx', 'soxl'), ('iwm', 'tna');
-- dynamic
cursor execution
declare @u_tkr
varchar(10), @e_tkr varchar(10);
declare
cur_pairs cursor local fast_forward for select u_ticker, e_ticker from
#ticker_map;
open cur_pairs;
fetch next from
cur_pairs into @u_tkr, @e_tkr;
while
@@fetch_status = 0
begin
if
object_id('tempdb..#u_data') is not null drop table #u_data;
-- safe
data staging: dynamically select the correct ema columns and skip leading nulls
declare
@sql nvarchar(max) = '
select
row_number() over (order by [date]) as row_id,
[date], [open], [close],
ema_' + cast(@fast_ema as varchar) + ' as ema_fast,
ema_' + cast(@slow_ema as varchar) + ' as ema_slow,
lead([open]) over (order by [date]) as nxt_o
into
#u_data
from
dbo.stockhistory_with_emas
where
ticker = @u_tkr_param
and
ema_' + cast(@slow_ema as varchar) + ' is not null;
select
* from #u_data;';
-- execute
and catch data into a local loop processing table
if
object_id('tempdb..#staged_loop_data') is not null drop table
#staged_loop_data;
create
table #staged_loop_data (
row_id
int, [date] date, [open] decimal(18,4), [close] decimal(18,4),
ema_fast decimal(18,4), ema_slow decimal(18,4), nxt_o decimal(18,4)
);
insert into
#staged_loop_data
exec
sp_executesql @sql, N'@u_tkr_param varchar(10)', @u_tkr_param = @u_tkr;
declare @i
int = 1, @max_i int = (select count(*) from #staged_loop_data);
declare
@in_pos bit = 0, @u_ent decimal(18,4), @b_dt date,
@low_b decimal(18,4), @up_b decimal(18,4), @lvls int;
while @i
<= @max_i
begin
declare
@d date, @uc decimal(18,4), @ue_fast decimal(18,4), @ue_slow decimal(18,4),
@unxto decimal(18,4);
select
@d = [date], @uc = [close], @ue_fast = ema_fast, @ue_slow = ema_slow, @unxto =
nxt_o
from
#staged_loop_data where row_id = @i;
if
@in_pos = 0
begin
if
@uc > @ue_fast and @ue_fast > @ue_slow and @unxto is not null
begin
set @in_pos = 1; set @b_dt = @d; set @u_ent = @unxto;
set @low_b = @u_ent * 0.95; set @up_b = @u_ent * 1.10; set @lvls = 0;
end
end
else
begin
if
@uc >= @up_b
begin
set @lvls = @lvls + 1; set @low_b = @up_b * 0.95; set @up_b = @up_b *
1.10;
end
if
@uc <= @low_b and @unxto is not null
begin
declare @e_ent decimal(18,4), @e_exit decimal(18,4);
set @e_ent = (select [open] from dbo.stockhistory_with_emas
where ticker
= @e_tkr and [date] =
(select [date]
from #staged_loop_data where row_id =
(select row_id
+ 1 from #staged_loop_data where [date] = @b_dt)));
set @e_exit = (select [open] from dbo.stockhistory_with_emas
where ticker
= @e_tkr and [date] =
(select
[date] from #staged_loop_data where row_id =
(select
row_id + 1 from #staged_loop_data where [date] = @d)));
if @e_ent is not null and @e_exit is not null
begin
insert into dbo.pogl_parametric_results values (
@param_label,
@u_tkr, @e_tkr, @b_dt, @d,
((cast(@unxto as
float) - @u_ent) / @u_ent) * 100,
((cast(@e_exit as
float) - @e_ent) / @e_ent) * 100,
@lvls
);
end
set @in_pos = 0;
end
end
set @i
= @i + 1;
end
fetch next
from cur_pairs into @u_tkr, @e_tkr;
end
close
cur_pairs; deallocate cur_pairs;
end;
GO
T-SQL Script to Report Performance Changes for Unleveraged ETFs Versus Leveraged ETFs
The accompanying T-SQL execution script operates as the automated master control panel and reporting suite for the parametric experiment. It manages the runtime cycle in three sequential phases: first, it sanitizes the database workspace using a truncate statement to ensure zero data cross-contamination from prior draft iterations; second, it coordinates five back-to-back executions of the POGL engine to sequentially pack the underlying repository with raw trade history for each moving average configuration; and third, it processes the resulting data into two highly optimized reporting views. The script features a strict non-compounded framework that aggregates performance exclusively via summed growth arithmetic to avoid geometric compounding distortion. By leveraging structured subqueries, it extracts view 1 to display the granular chronological return premiums on a per-ticker level, while simultaneously compiling view 2 to average out the multi-asset portfolio metrics—explicitly exposing the exact mathematical leveraged portfolio return advantage of each parametric setting.
/*
=====================================================================
project: pogl
ema fast_slow parametric backtest execution, v2
purpose: clear
repository, execute all 5 target simulations, and
output
ticker-level and macro portfolio summed reporting views.
author: rick
dobson
===================================================================== */
use securitytradinganalytics;
go
-- step 1: clear out old data from prior runs
truncate table dbo.pogl_parametric_results;
-- step 2: execute the 5 requested configurations
sequentially
exec dbo.sp_run_pogl_backtest @fast_ema = 21, @slow_ema =
63;
exec dbo.sp_run_pogl_backtest @fast_ema = 21, @slow_ema =
100;
exec dbo.sp_run_pogl_backtest @fast_ema = 21, @slow_ema =
200;
exec dbo.sp_run_pogl_backtest @fast_ema = 50, @slow_ema =
100;
exec dbo.sp_run_pogl_backtest @fast_ema = 50, @slow_ema =
200;
-------------------------------------------------------------------------
-- view 1: detailed chronological summed results per
index asset pair
-------------------------------------------------------------------------
select
parameter_set as
[definition],
u_ticker + '/' +
e_ticker as [asset_pair],
count(*) as
[total_trades],
cast(sum(u_return_pct) as decimal(18,2)) as [total summed unleveraged
return %],
cast(sum(e_return_pct) as decimal(18,2)) as [total summed leveraged
return %],
cast(sum(e_return_pct) - sum(u_return_pct) as decimal(18,2)) as
[leveraged return advantage]
from dbo.pogl_parametric_results
group by parameter_set, u_ticker, e_ticker
order by parameter_set, [leveraged return advantage]
desc;
-------------------------------------------------------------------------
-- view 2: macro aggregate portfolio summary across all
asset pairs
-------------------------------------------------------------------------
select
parameter_set as
[definition],
sum(total_trades)
as [grand total trades],
cast(avg([total
summed unleveraged return %]) as decimal(18,2)) as [avg unleveraged portfolio u
return %],
cast(avg([total
summed leveraged return %]) as decimal(18,2)) as [avg leveraged portfolio
return %],
cast(avg([total
summed leveraged return %]) - avg([total summed unleveraged return %]) as
decimal(18,2)) as [leveraged portfolio return advantage]
from (
-- subquery to
stage the view metrics for clean aggregate averaging
select
parameter_set,
u_ticker,
e_ticker,
count(*) as
total_trades,
sum(u_return_pct) as [total summed unleveraged return %],
sum(e_return_pct) as [total summed leveraged return %]
from
dbo.pogl_parametric_results
group by
parameter_set, u_ticker, e_ticker
) as stage
group by parameter_set
order by [leveraged portfolio return advantage] desc;
Comments
Post a Comment