A Modeling Framework for Evaluating Multiple Ticker Sets with Heikin Ashi Candle Colors
Heikin Ashi candles were initially examined within this blog in an earlier post (Backtesting Heikin Ashi Candle Colors to Buy Dips and Exit When Runs End). They are sometimes featured in stock charts because they can visually convey by Heikin Ashi candle colors whether prices are in a rising or declining trend better than OHLC candles for historical prices. Two or more consecutive green candles mean prices are rising over that period. Likewise, two or more red candles mean that prices are declining during that interval. The original Heikin Ashi candle post in this blog presents, describes, and backtests a very simple buy-the-dip model for entering and exiting trades based on Heikin Ashi candle colors via T-SQL scripts.
This post presents a framework for creating multiple ticker sets, retrieving OHLC candles for those tickers, computing Heikin Ashi candles, and backtesting alternative automated trading models based on Heikin Ashi candle colors. Within this framework, you can compare and contrast alternative automated trading models based on Heikin Ashi candle colors. This post specifically shows how to build and contrast a very simple Heikin Ashi buy-the-dip model with a similarly simple follow-the-trend model. Finally, the follow-the-trend and buy-the-dip models are compared based on their overall percent change and performance for trading tickers relative to a benchmark ticker (SPY). To help you understand general market conditions during the backtest evaluation timeframe, the comparison commences with a brief review of market swoons throughout the backtest timeframe.
Based on the findings from these two automated trading models, this blog will continue to explore automated trading models that utilize Heikin Ashi candles. By reading this post, you will grow your understanding of how Heikin Ashi candle colors can drive automated trading models as well as acquire skills to build automated trading models based on your own custom assumptions.
Data Ingestion from Google Sheets to SQL Server
Historical OHLC price data for this post originates from two Google Sheets workbooks that include a separate tab for each ticker in a distinct ticker set. Eventually, the data ends up in a SQL Server table named dbo.HA_Dataset. This section gives readers who care about these ingestion details an overview of the steps along the path.
Below is an excerpt from the first workbook, First_HA_Ticker_Set, which contains nine separate tabs for tickers ranging from AAPL through SPY that are selected for their appeal to active traders. The fx bar displays the =QUERY(GOOGLEFINANCE(...)) formula used to populate the sheet. While the query starts on January 1, 2017, market data begins on the first trading day of the year, January 3, 2017, and runs through the end date of September 30, 2026.
For readers interested in reviewing how a Google Sheets workbook can be configured to download historical prices with the GOOGLEFINANCE function, this post provides two links to a read-only version of each workbook. You can examine the workbooks from your browser.
- Here’s the link for the first workbook: https://docs.google.com/spreadsheets/d/1nBxDJD4spgv3h6cvbPVcJtjI4GV9HmMwI_9hlHrsEK4/edit?usp=sharing
- Here’s the link for the second workbook: https://docs.google.com/spreadsheets/d/13ejRuySf_w0zW9ZnuJQ11x59oMQMV0wOxvBJ2Y1Q8cQ/edit?usp=sharing
After you extract historical prices with the GOOGLEFINANCE function for whatever tickers you are tracking in a workbook, you can download historical prices from each tab to your default Downloads directory with the Google Sheets File → Download → Comma Separated Values (.csv) command. Once all csv files are downloaded to the default download folder, you need to remove the workbook prefix name from the csv file names. Then, you can migrate the csv files to whatever target directory you are using to store the historical prices for your ticker set.
- This post uses the C:\Users\User\Documents\First_HA_Ticker_Set\ folder for storing csv files with historical data for each ticker from the First_HA_Ticker_Set workbook.
- It also uses the C:\Users\User\Documents\Long_term6_for_HA Ticker Set\ folder for storing csv files with historical data for each ticker from the Long_term 6 for HA workbook.
There are five sections to the script.
- Section 1 creates a fresh version of the dbo.stockhistory_stage table which temporarily holds the contents of a succession of csv files. The csv files are named after the ticker for which they hold historical OHLC prices.
- Section 2 creates a fresh version of the dbo.stockhistory_clean_gs table which the script populates with the csv file contents for tickers in the folder designated by the @folder_path local variable. The create table statement for the dbo.stockhistory_clean_gs table contains a primary key to arrange content within the table and facilitate rapid downstream processing.
- Section 3 allows a user to specify a source folder for the csv files as well as a temporary table with a column of ticker names for the csv files within the folder. Within the script below, the C:\Users\User\Documents\Long_term6_for_HA Ticker Set\ folder contains csv files downloaded from the Long_term 6 for HA Google Sheets workbook. Notice that the ticker column values in the #tickers table correspond to the tabs in the workbook.
- Section 4 contains a while loop and a dynamic sql script segment for moving each csv file’s contents to the dbo.stockhistory_stage table and then to the dbo.stockhistory_clean_gs table.
- Finally, section 5 displays selected results from the dbo.stockhistory_clean_gs table for an analyst to cross-check the table contents with the underlying worksheet tab contents.
/* dynamic ingestion for
googlefinance csv files into a tall sql table ver_4.5
date: 10/5/2026
authors: rick dobson & Google Gemini
& Claude
purpose: load 5-column googlefinance csv
exports with full OHLC price data,
convert date/numeric fields
directly (decimal 18,6),
populate dbo.stockhistory_clean_gs,
and verify row counts as well as
first and last dates per ticker.
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 1. recreate staging table (5
columns matching CSV layout)
---------------------------------------------------------
if
object_id('dbo.stockhistory_stage', 'U') is not null
drop table dbo.stockhistory_stage;
create table
dbo.stockhistory_stage
(
[date]
varchar(50),
[open]
varchar(50),
[high]
varchar(50),
[low]
varchar(50),
[close]
varchar(50)
);
---------------------------------------------------------
-- 2. recreate clean table for
google finance (_gs)
---------------------------------------------------------
if
object_id('dbo.stockhistory_clean_gs', 'U') is not null
drop table dbo.stockhistory_clean_gs;
create table
dbo.stockhistory_clean_gs
(
[date]
date not null,
[open]
decimal(18,6) not null,
[high]
decimal(18,6) not null,
[low]
decimal(18,6) not null,
[close]
decimal(18,6) not null,
[ticker] varchar(20) not null,
constraint pk_stockhistory_clean_gs primary
key clustered ([ticker], [date])
);
go
---------------------------------------------------------
-- 3. configure folder path and
tickers
---------------------------------------------------------
declare @folder_path
varchar(255);
set @folder_path =
'C:\Users\User\Documents\Long_term6_for_HA Ticker Set\';
if
object_id('tempdb..#tickers') is not null
drop table #tickers;
create table #tickers
(
ticker varchar(20) not null
);
insert into #tickers (ticker)
values
('CAT'), ('JPM'), ('LLY'), ('MSFT'),
('NVDA'), ('SHW');
---------------------------------------------------------
-- 4. iterate through tickers
with a while loop and process files
---------------------------------------------------------
declare @ticker varchar(20);
declare @file_path
varchar(500);
declare @sql nvarchar(max);
-- start with the
alphabetically first ticker
select @ticker = min(ticker)
from #tickers;
while @ticker is not null
begin
truncate table dbo.stockhistory_stage;
set @file_path = @folder_path + @ticker +
'.csv';
set @sql = '
bulk insert dbo.stockhistory_stage
from ' + nchar(39) + @file_path + nchar(39)
+ '
with
(
firstrow = 2,
fieldterminator = '','',
rowterminator = ''\n''
);';
exec sp_executesql @sql;
insert into dbo.stockhistory_clean_gs
([date], [open], [high], [low], [close], [ticker])
select
try_convert(date, s.[date]),
try_convert(decimal(18,6), s.[open]),
try_convert(decimal(18,6), s.[high]),
try_convert(decimal(18,6), s.[low]),
try_convert(decimal(18,6), s.[close]),
@ticker
from dbo.stockhistory_stage s
where try_convert(date, s.[date]) is not
null
and try_convert(decimal(18,6), s.[open])
is not null
and try_convert(decimal(18,6), s.[high])
is not null
and try_convert(decimal(18,6), s.[low])
is not null
and try_convert(decimal(18,6), s.[close])
is not null;
-- move to the next ticker alphabetically;
null ends the loop
select @ticker = min(ticker) from #tickers
where ticker > @ticker;
end;
go
---------------------------------------------------------
-- 5. verification check
---------------------------------------------------------
select
ticker,
count(*) as row_count,
min([date]) as min_date,
max([date]) as max_date
from dbo.stockhistory_clean_gs
group by ticker
order by ticker;
Here is the result set from the preceding script. Notice that each ticker has 2449 rows in the dbo.stockhistory_clean_gs table, and all tickers have a minimum date of 2017-01-03 and a maximum date of 2026-09-30.
By changing the @folder_path local variable assignment to 'C:\Users\User\Documents\First_HA_Ticker_Set\' and populating the #tickers table in section 3 as indicated below, the preceding script can load the csv files downloaded from the First_HA_Ticker_Set workbook.
insert into #tickers (ticker)
values
('AAPL'),
('AMD'), ('DELL'), ('GOOGL'), ('MSFT'),
('MU'),
('NVDA'), ('VLO'), ('SPY');
Here is the result set from the preceding script after the two modifications. Notice that each ticker, except for DELL, has 2449 rows in the dbo.stockhistory_clean_gs table. The exception for DELL is the consequence of a re-listing issue for the OHLC historical prices when transitioned from a private firm back to a public firm on December 28, 2018 (one trading day was inadvertently omitted). All tickers have a minimum date of 2017-01-03 and a maximum date of 2026-09-30.
A Table to Store Historical Price Series for Multiple Ticker Sets
As this blog continues to explore the evaluation of Heikin Ashi models for designating trade entries and exits, it will develop and analyze multiple ticker sets, such as the two in the previous section. By backtesting different models across multiple ticker sets, this blog expects to reveal how different Heikin Ashi trade models operate with different types of assets and different period durations. This blog will chronicle outcomes from these backtests in pursuit of high quality trade models that demonstrate the ability to improve returns for stock traders.
- The short T-SQL script in this section performs a simple task, which is to create a single table that can store multiple ticker sets for Heikin Ashi trade model evaluations.
- The table’s name is dbo.HA_Dataset.
- The table structures its contents with a primary key defined over ticker_set, bar period (for example, day, week, month), ticker, and date.
- In addition to the primary key columns, there are columns for historical OHLC prices and their associated Heikin Ashi candle values and colors.
/* ha dataset setup ver_2.0
date: 10/25/2026
authors: rick
dobson & Claude
purpose: create
the permanent table dbo.HA_Dataset, which holds heikin
ashi
(ha) values and colors for any number of ticker sets.
design: - one permanent table instead of a new table
per project
-
natural key: ticker_set + bar_period + ticker + date
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 1. create the permanent ha table (only if it does not
exist)
---------------------------------------------------------
if object_id('dbo.HA_Dataset', 'U') is null
begin
create table
dbo.HA_Dataset
(
ticker_set varchar(50) not null,
bar_period char(1) not null
constraint ck_HA_Dataset_bar_period
check
(bar_period in ('D', 'W', 'M')),
ticker varchar(20) not null,
[date] date not null,
[open] decimal(18,6) not null,
[high] decimal(18,6) not null,
[low] decimal(18,6) not null,
[close] decimal(18,6) not
null,
ha_open decimal(18,6) not
null,
ha_high decimal(18,6) not
null,
ha_low decimal(18,6) not null,
ha_close decimal(18,6) not
null,
ha_color varchar(5) not null,
constraint
pk_HA_Dataset
primary
key clustered (ticker_set, bar_period, ticker, [date])
);
end;
Calculating Heikin Ashi Values and Colors and Populating the dbo.HA_Dataset Table
The previous “Data Ingestion from Google Sheets to SQL Server” section details how to derive and validate the dbo.stockhistory_clean_gs table for two ticker sets: Long_term6_for_HA and First_HA_Ticker_Set. That section initially uses a T-SQL script to create and populate dbo.stockhistory_clean_gs for Long_term6_for_HA. It then demonstrates how to update the dynamic ingestion script to rebuild and populate the table for First_HA_Ticker_Set.
A ticker set name serves as a nickname for a specific collection of tickers and their associated data:
- Long_term6_for_HA: CAT, JPM, LLY, MSFT, NVDA, SHW
- First_HA_Ticker_Set: AAPL, AMD, DELL, GOOGL, MSFT, MU, NVDA, SPY, VLO
For background on Heikin Ashi candles and calculation formulas, refer to this StockCharts.com article. Additionally, the post Backtesting Heikin Ashi Candle Colors to Buy Dips and Exit When Runs End provides a brief tutorial on Heikin Ashi candles alongside a basic single ticker set analysis. This post expands on that foundation by introducing a framework capable of handling multiple ticker sets to compare Heikin Ashi buy-the-dip model performance against a Heikin Ashi follow-the-trend model.
The following script creates from the historical OHLC prices in a stockhistory_clean_gs table an alternative set of Heikin Ashi values and colors. Then, it saves the historical prices joined to the Heikin Ashi candle values and color values along with ticker set and descriptor column values to the dbo.HA_Dataset table under one ticker set name. The following script is configured to process OHLC prices for the First_HA_Ticker_Set, and it depends on the dbo.stockhistory_clean_gs table containing OHLC prices for that ticker set. To populate the dbo.HA_Dataset table for another ticker set, you need to first
- update the @ticker_set declare statement in section 0 for the alternative ticker set name and
- run the dynamic ingestion script with the folder directory for csv files belonging to the alternative ticker set to create a fresh dbo.stockhistory_clean_gs table.
There are six sections to the script.
- Section 0 assigns values to @ticker_set and the @bar_period local variables. For the current run, the @bar_period local variable is set to ‘D’ for a model analysis at daily granularity. This blog will publish at least one future post in which you will see the use of a ‘W’ assignment for a model analysis at weekly granularity.
- Section 1 deletes this set’s old rows from dbo.HA_Dataset, so a rerun replaces the set instead of duplicating it. Other sets are untouched. This section also drops the #with_dates temp table, which is populated freshly in section 2.
- Section 2 has two layers that build the temp table #with_dates:
- Layer 1 computes ha_close = (open + high + low + close) / 4 for every bar.
- Layer 2 adds each bar’s previous and next trading date within its ticker, using lag and lead.
- The script then puts a primary key on (ticker, date). That index is what makes the recursive step fast.
- Section 3 computes ha_open with a recursive CTE named ha_chain, then inserts the results into dbo.HA_Dataset.
- The anchor member selects each ticker's first bar from #with_dates (the row whose prev_date is null) and seeds ha_open = (open + close) / 2.
- The recursive member joins each prior ha_chain row to the next bar for the same ticker in #with_dates, matching that bar's date to the prior row's next_date. It computes ha_open = (prior ha_open + prior ha_close) / 2. The recursion ends at each ticker's last bar, where next_date is null.
- The final select joins ha_chain back to dbo.stockhistory_clean_gs by ticker and date to recover high and low. It computes ha_high as the largest of high, ha_open, and ha_close, and ha_low as the smallest of low, ha_open, and ha_close. ha_color is 'green' when ha_close > ha_open and 'red' otherwise.
- The insert tags each row with @ticker_set and @bar_period.
- option (maxrecursion 0) removes SQL Server's default limit of 100 recursive steps, since each ticker needs about 2,448 steps.
- Section 4 verifies the run. For each ticker in the set, it compares the row count in dbo.HA_Dataset with the row count in dbo.stockhistory_clean_gs. It also counts green, red, and flat bars, where a flat bar has ha_close equal to ha_open. Matching row counts and zero flat bars confirm a clean run.
- Section 5 is optional. It displays the first and last three rows for each ticker in the set, for a quick visual check of the HA values and colors.
/* heikin ashi candle computation with a layered cte
chain ver_2.0
date: 10/17/2026
authors: rick
dobson & Claude
purpose: compute
heikin ashi (ha) candle values and colors for every
ticker
in dbo.stockhistory_clean_gs and save them in the
permanent table dbo.HA_Dataset under the ticker set named
in
@ticker_set.
change from
ver_1.3: instead of dropping and recreating an output
table
for each project, this version writes to one permanent
table
(created by "ha dataset setup ver_2.0"). each run
deletes
and replaces only the rows for its own ticker set and
bar
period, so results for other ticker sets are kept. the
ha
calculation itself is unchanged from ver_1.3.
assumption:
dbo.stockhistory_clean_gs holds exactly the tickers and
date
range of the ticker set being processed (as it does
after
the ingestion script loads one set). a ticker set name
identifies both its tickers and its date range, so a run over
a
different date range should use a new @ticker_set name.
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 0. name the ticker set and bar period for this run
-- (bar_period
'D' = daily bars; weekly and monthly bars are a
-- planned
extension and are not built by this version)
---------------------------------------------------------
declare @ticker_set varchar(50) = 'First_HA_Ticker_Set';
declare @bar_period char(1) = 'D';
---------------------------------------------------------
-- 1. remove rows from a prior run of this ticker set and
bar period
---------------------------------------------------------
delete from dbo.HA_Dataset
where ticker_set = @ticker_set
and bar_period =
@bar_period;
drop table if exists #with_dates;
---------------------------------------------------------
-- 2. prepare each bar for the ha calculation and save
the
-- result in a
temp table (#with_dates)
---------------------------------------------------------
-- layer 1: every source row plus ha close
with source_rows as
(
select
ticker,
[date],
[open],
[close],
cast(([open] + [high] + [low] + [close]) / 4.0 as decimal(18,6)) as
ha_close
from
dbo.stockhistory_clean_gs
)
-- layer 2: prior and next trading dates within each
ticker
select
ticker,
[date],
[open],
[close],
ha_close,
lag([date]) over (partition by
ticker order by [date]) as prev_date,
lead([date])
over (partition by ticker order by [date]) as next_date
into #with_dates
from source_rows;
-- natural key on the temp table; this is the index the
recursive join seeks on
alter table #with_dates
add constraint
pk_with_dates primary key clustered (ticker, [date]);
---------------------------------------------------------
-- 3. recursive ha open and insert into the permanent
table
---------------------------------------------------------
with
-- layer 3: recursive ha open
-- anchor: each ticker's first bar seeds ha open as (open
+ close) / 2
-- recursive step: ha open = (prior ha open + prior ha
close) / 2
ha_chain as
(
-- anchor
member
select
w.ticker,
w.[date],
w.next_date,
cast((w.[open] + w.[close]) / 2.0 as decimal(18,6)) as ha_open,
w.ha_close
from
#with_dates w
where
w.prev_date is null
union all
-- recursive
member
select
n.ticker,
n.[date],
n.next_date,
cast((p.ha_open + p.ha_close) / 2.0 as decimal(18,6)) as ha_open,
n.ha_close
from ha_chain p
join
#with_dates n
on n.ticker = p.ticker
and
n.[date] = p.next_date
)
-- final layer: join ha values to daily ohlc by ticker
and date,
-- add ha high, ha low, and candle color, and insert the
result
-- tagged with the ticker set and bar period for this run
insert into dbo.HA_Dataset
(ticker_set,
bar_period, ticker, [date],
[open],
[high], [low], [close],
ha_open,
ha_high, ha_low, ha_close, ha_color)
select
@ticker_set,
@bar_period,
d.ticker,
d.[date],
d.[open],
d.[high],
d.[low],
d.[close],
h.ha_open,
(select max(v)
from (values (d.[high]), (h.ha_open), (h.ha_close)) as t(v)) as ha_high,
(select min(v)
from (values (d.[low]), (h.ha_open),
(h.ha_close)) as t(v)) as ha_low,
h.ha_close,
case
when
h.ha_close > h.ha_open then 'green'
else 'red'
end as ha_color
from ha_chain h
join dbo.stockhistory_clean_gs d
on d.ticker = h.ticker
and d.[date] =
h.[date]
option (maxrecursion 0);
---------------------------------------------------------
-- 4. verification check for this ticker set and bar
period
-- ha_row_count
should equal source_row_count for every ticker;
-- green_bars,
red_bars, and flat_bars count bars where ha close is
-- above, below,
or exactly equal to ha open, so the three counts
-- sum to
ha_row_count; a flat_bars count of zero confirms every
-- bar is
clearly green or red
---------------------------------------------------------
select
f.ticker_set,
f.bar_period,
f.ticker,
count(*) as
ha_row_count,
src.source_row_count,
sum(case when
f.ha_close > f.ha_open then 1 else 0 end) as green_bars,
sum(case when
f.ha_close < f.ha_open then 1 else 0 end) as red_bars,
sum(case when
f.ha_close = f.ha_open then 1 else 0 end) as flat_bars
from dbo.HA_Dataset f
join
(
select ticker,
count(*) as source_row_count
from
dbo.stockhistory_clean_gs
group by ticker
) src
on src.ticker =
f.ticker
where f.ticker_set = @ticker_set
and f.bar_period
= @bar_period
group by f.ticker_set, f.bar_period, f.ticker,
src.source_row_count
order by f.ticker;
--------------------------------------------------------------------------------
-- 5. optional: display colors for the first and last
three rows for each ticker
--------------------------------------------------------------------------------
with numbered as
(
select
*,
row_number() over (partition by ticker order by [date]) as from_first,
row_number() over (partition by ticker order by [date] desc) as
from_last
from
dbo.HA_Dataset
where
ticker_set = @ticker_set
and
bar_period = @bar_period
)
select
ticker_set,
bar_period, ticker, [date], [open], [high], [low], [close],
ha_open,
ha_high, ha_low, ha_close, ha_color
from numbered
where from_first <= 3
or
from_last <= 3
order by ticker, [date];
After you run the preceding script for both ticker sets in
this post, the following query should return precisely fifteen rows as shown
below. Notice that the fifteen-row
result set includes rows for MSFT and NVDA from both Long_term6_for_HA and
First_HA_Ticker_Set.
Implementing the Heikin Ashi Candle Buy-The-Dip Model Backtest
This section presents a script that performs a backtest for the Heikin Ashi Candle buy-the-dip model and summarizes backtest outcomes by ticker for the Long_term6_for_HA ticker set. The script for performing these tasks is adapted from an earlier script in the Backtesting Heikin Ashi Candle Colors to Buy Dips and Exit When Runs End post. The most strategic change is for the script to derive its OHLC prices and Heikin Ashi candle values and colors from one of the ticker sets stored in the dbo.HA_Dataset table.
The script for this section applies the color rules to one ticker set and saves every trade in dbo.HA_Trades. Its introductory comment section also includes a brief summary of the model logic for your easy reference. The script implements its tasks in five sections.
- Section 0 assigns values to the @ticker_set, @bar_period, and @start_date local variables. @start_date is set to 2017-02-01 so that no trade starts before the seed value for the first ha_open has faded.
- Section 1 creates the dbo.HA_Trades table if it does not already exist. It also deletes any trades from a prior run for the same ticker set and bar period, so a rerun replaces that set's trades instead of duplicating them.
- Section 2 finds the trades with a five-layer CTE and saves them in dbo.HA_Trades.
- Layer 1 (bars) adds to each bar the colors of the three preceding bars, the preceding bar's date, and the next trading day's date and open.
- Layer 2 (entry_signals) finds each green bar preceded by three red bars; the entry is at the next trading day's open.
- Layer 3 (exit_signals) finds each pair of consecutive red bars; the exit is at the next trading day's open.
- Layer 4 (trades) pairs each entry with the first exit signal whose two red bars both fall on or after the entry date. A trade still open at the end of the data has no exit.
- Layer 5 (priced) computes each trade's percent change from entry price to exit price.
- The final insert adds a running cumulative percent change for each ticker and saves the trades with the ticker set and bar period.
- Section 3 is optional. It displays every trade for the ticker set.
- Section 4 summarizes the backtest by ticker: the number of closed trades and model_net_pct, the sum of the closed trades' percent changes. If a dbo.HA_Trades row has an entry date and price without a matching exit date and price, then it is not completed, and it is not used to compute the trades and model_net_pct columns.
/* ha color entry_exit trades for 3 reds + 1 green in, 2
reds out ver_2.2
date: 10/29/2026
authors: rick
dobson & Claude
purpose: list
trades for every ticker in one ticker set of dbo.HA_Dataset
based
on ha candle colors, save them in the permanent table
dbo.HA_Trades, and summarize each ticker's number of closed
trades
and model net change.
rules: entry signal: a green ha bar immediately
preceded by
three consecutive red ha bars
entry: open price on the
trading day after the entry signal
exit
signal: the second of two consecutive
red ha bars,
both on or after the entry date
exit: open price on the
trading day after the exit signal
scope: trades entered on or
after 2/1/2017 (@start_date),
a few weeks after the data starts, so the ha open
seed has faded before any trade
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 0. name the ticker set, bar period, and first entry
date for this run
---------------------------------------------------------
declare @ticker_set varchar(50) = 'Long_term6_for_HA';
declare @bar_period char(1) = 'D';
declare @start_date date = '2017-02-01';
---------------------------------------------------------
-- 1. create the permanent trades table (only if it does
not exist)
-- and remove
this ticker set's trades from a prior run
---------------------------------------------------------
if object_id('dbo.HA_Trades', 'U') is null
begin
create table
dbo.HA_Trades
(
ticker_set varchar(50) not null,
bar_period char(1) not null,
ticker varchar(20) not null,
entry_date date not null,
entry_price decimal(18,6) not
null,
exit_date date null,
exit_price decimal(18,6) null,
trade_pct decimal(18,4) null,
cum_pct decimal(18,4) null,
constraint
pk_HA_Trades
primary
key clustered (ticker_set, bar_period, ticker, entry_date)
);
end;
delete from dbo.HA_Trades
where ticker_set = @ticker_set
and bar_period =
@bar_period;
---------------------------------------------------------
-- 2. find the trades and save them in dbo.HA_Trades
---------------------------------------------------------
with
-- layer 1: each bar with the colors of the prior bars,
-- plus
the prior date and the next trading day's date and open,
-- all
computed within each ticker of this ticker set
bars as
(
select
ticker,
[date],
ha_color,
lag(ha_color, 1) over (partition by ticker order by [date]) as
color_1_back,
lag(ha_color, 2) over (partition by ticker order by [date]) as
color_2_back,
lag(ha_color, 3) over (partition by ticker order by [date]) as
color_3_back,
lag([date]) over (partition
by ticker order by [date]) as prev_date,
lead([date]) over (partition
by ticker order by [date]) as next_date,
lead([open]) over (partition
by ticker order by [date]) as next_open
from
dbo.HA_Dataset
where
ticker_set = @ticker_set
and
bar_period = @bar_period
),
-- layer 2: entry signals (red, red, red, green);
-- the
trade enters at the next trading day's open
-- (a
signal on a ticker's last bar has no next day and is skipped)
entry_signals as
(
select
ticker,
next_date
as entry_date,
next_open
as entry_price
from bars
where
ha_color = 'green'
and
color_1_back = 'red'
and
color_2_back = 'red'
and
color_3_back = 'red'
and next_date
is not null
),
-- layer 3: exit signals (red, red); the pair's first red
is prev_date,
-- and the
trade exits at the next trading day's open
exit_signals as
(
select
ticker,
prev_date
as first_red_date,
[date] as exit_signal_date,
next_date
as exit_date,
next_open
as exit_price
from bars
where
ha_color = 'red'
and
color_1_back = 'red'
),
-- layer 4: pair each entry with the first exit signal
for the same ticker
-- whose
two reds both fall on or after the entry date
trades as
(
select
e.ticker,
e.entry_date,
e.entry_price,
x.exit_date,
x.exit_price
from
entry_signals e
outer apply
(
select top
1 xs.exit_date, xs.exit_price
from
exit_signals xs
where
xs.ticker = e.ticker
and
xs.first_red_date >= e.entry_date
order by
xs.exit_signal_date
) x
where
e.entry_date >= @start_date
),
-- layer 5: percent return for each trade
-- (a
trade still open at the end of the data has null exit
-- values
and a null return)
priced as
(
select
ticker,
entry_date,
entry_price,
exit_date,
exit_price,
cast((exit_price - entry_price) / entry_price * 100 as decimal(18,4)) as
trade_pct
from trades
)
-- final layer: add the cumulative percent return within
each ticker
-- and save the trades tagged with the ticker set and bar
period
insert into dbo.HA_Trades
(ticker_set,
bar_period, ticker, entry_date, entry_price,
exit_date,
exit_price, trade_pct, cum_pct)
select
@ticker_set,
@bar_period,
ticker,
entry_date,
entry_price,
exit_date,
exit_price,
trade_pct,
cast(sum(trade_pct) over (partition by ticker order by entry_date
rows unbounded
preceding) as decimal(18,4)) as cum_pct
from priced;
---------------------------------------------------------
-- 3. optional: display the trades
---------------------------------------------------------
select *
from dbo.HA_Trades
where ticker_set = @ticker_set
and bar_period =
@bar_period
order by ticker, entry_date;
---------------------------------------------------------
-- 4. summary by ticker: returns a three-column result
set:
-- ticker, trades, model_net_pct
-- (model_net_pct sums the percent change of the closed
trades;
-- an open trade at the end of the data drops out; spy is
listed last)
---------------------------------------------------------
select
ticker,
count(trade_pct)
as trades,
cast(sum(trade_pct) as decimal(10,2))
as model_net_pct
from dbo.HA_Trades
where ticker_set = @ticker_set
and bar_period =
@bar_period
group by ticker
order by case when ticker = 'SPY' then 1 else 0 end,
ticker;
After running the preceding script for the and then re-run scriptLong_term6_for_HA ticker set, you may want to re-run the script for any other ticker sets in the dbo.HA_Dataset table. For the current post, the only remaining ticker set is named First_HA_Ticker_Set. To run the script for First_HA_Ticker_Set, assign its name to the @ticker_set local variable, and then re-run the script.
The following image shows an excerpt from the dbo.HA_Trades table along with the summary-by-ticker result set for the Long_term6_for_HA ticker set.
- The top panel displays statistics for each of the initial nine trades generated by the Heikin Ashi candle buy-the-dip model for the CAT ticker in the Long_term6_for_HA ticker set. Rows are sorted by ticker and entry_date.
- Each row shows the entry date and price along with exit date and price. The final two columns show the percent change for a trade as well as the cumulative percentage change.
- Scanning these rows shows that the cumulative percentage change grows from 2.00 percent to 10.71 percent over the first nine trades.
- The bottom panel displays the number of trades created for each ticker along with the model net percent value, which is the cumulative percentage change through the final completed trade.
The next image displays a different excerpt from the dbo.HA_Trades table along with the summary-by-ticker result set for First_HA_Ticker_Set. This ticker set contains nine tickers comprised of eight active trading tickers plus one benchmark ticker (SPY).
- In the top panel of this image, the cumulative percentage change for AAPL grows from 7.69 percent to 15.10 percent over the first nine trades.
- In the bottom panel, you can see the number of trades and the model net percent by ticker. The GOOGL and NVDA tickers are abbreviated to display as GO… and NV…, respectively.
- The model returns a model net percentage value for each of the active trader tickers and the benchmark ticker.
This section closes with a short script to rename both the dbo.HA_Trades table and its primary key object (dbo.pk_HA_Trades). This script makes it possible for another fresh backtest script to populate a freshly created version of the dbo.HA_Trades table, and all the fresh script has to modify is the logic for creating entry and exit dates and prices. Then, by renaming the dbo.pk_HA_Trades and dbo.HA_Trades after all required backtests are run with the fresh backtest script, you can have two independent, but easily compared trades datasets. In fact, we demonstrate this strategy in the next section on implementing backtests for the Heikin Ashi follow-the-trend model.
-- rename dbo.pk_HA_Trades and dbo.HA_Trades after
backtests for both ticker sets
-- after the buy-the-dip model
-- alternative model backtests will create a fresh Trades
table and primary key
use SecurityTradingAnalytics
go
exec sp_rename 'dbo.pk_HA_Trades',
'pk_HA_Trades_Buy_Dip', 'OBJECT';
exec sp_rename 'dbo.HA_Trades', 'HA_Trades_Buy_Dip';
Implementing the Heikin Ashi Candle Follow-The-Trend Model Backtest
The second model backtested in this post is called a follow-the-trend model. Unlike the preceding section’s model, the model for this section does not use a dip to signal a trade entry nor two consecutive red bars to signal a trade exit. The model for this section follows the trend by letting red-to-green bar transitions signal trade entries and green-to-red bar transitions signal trade exits.
The comment block at the beginning of the script file includes a new script title that succinctly highlights the role of Heikin Ashi bar colors for trade entry and exit in the backtest script. Also, an updated rules section specifies at a more granular level the signals for trade entry and exit as well as when and at what price to act on the new signals. The T-SQL code in section 2 of the script file demonstrates how to implement the revised rules for trade entries and exits and save the trades in the dbo.HA_Trades table.
- The rules section of the introductory text at the beginning of the script is particularly worthy of your review. This is because the rules section indicates precisely when trade entries and trade exits are scheduled, which is one trading day after their corresponding signals.
- Section 2 reveals the T-SQL code for implementing the rules in the introductory section as a component of the overall backtest process, which is otherwise identical to the backtest process in the preceding section.
The output from the backtest in this section has the same format as the backtest in the preceding section. In other words, a trades log is populated and stored in the dbo.HA_Trades table, which is output as the first result set from the script. Additionally, T-SQL code summarizes trade counts and net percent changes by ticker, which is output as the second result set from the script.
The following script listing includes the full and complete backtest process for the follow-the-trend model.
/* ha color entry/exit trades: red to green in, green to
red out ver_3.0
date: 10/28/2026
authors: rick
dobson & Claude
purpose: list
trades for every ticker in one ticker set of dbo.HA_Dataset
based
on ha candle color changes, save them in the permanent
table
dbo.HA_Trades, and summarize each ticker's number of
closed
trades and model net change.
rules: entry signal: the first green ha bar after a
red stretch of
one or more red bars
entry: open price on the
trading day after the entry signal
exit
signal: the first red ha bar on or after
the entry date
(a red bar preceded by a green bar)
exit: open price on the
trading day after the exit signal
scope: trades entered on or
after 2/1/2017 (@start_date),
a few weeks after the data starts, so the ha open
seed has faded before any trade
*/
use securitytradinganalytics;
go
---------------------------------------------------------
-- 0. name the ticker set, bar period, and first entry
date for this run
---------------------------------------------------------
declare @ticker_set varchar(50) = 'Long_term6_for_HA';
declare @bar_period char(1) = 'D';
declare @start_date date = '2017-02-01';
---------------------------------------------------------
-- 1. create the permanent trades table (only if it does
not exist)
-- and remove
this ticker set's trades from a prior run
---------------------------------------------------------
if object_id('dbo.HA_Trades', 'U') is null
begin
create table
dbo.HA_Trades
(
ticker_set varchar(50) not null,
bar_period char(1) not null,
ticker varchar(20) not null,
entry_date date not null,
entry_price decimal(18,6) not
null,
exit_date date null,
exit_price decimal(18,6) null,
trade_pct decimal(18,4) null,
cum_pct decimal(18,4) null,
constraint
pk_HA_Trades
primary
key clustered (ticker_set, bar_period, ticker, entry_date)
);
end;
delete from dbo.HA_Trades
where ticker_set = @ticker_set
and bar_period =
@bar_period;
---------------------------------------------------------
-- 2. find the trades and save them in dbo.HA_Trades
---------------------------------------------------------
with
-- layer 1: each bar with the prior bar's color,
-- plus
the next trading day's date and open,
-- all
computed within each ticker of this ticker set
bars as
(
select
ticker,
[date],
ha_color,
lag(ha_color, 1) over (partition by ticker order by [date]) as
color_1_back,
lead([date]) over (partition
by ticker order by [date]) as next_date,
lead([open]) over (partition
by ticker order by [date]) as next_open
from
dbo.HA_Dataset
where
ticker_set = @ticker_set
and
bar_period = @bar_period
),
-- layer 2: entry signals (red to green);
-- the
trade enters at the next trading day's open
-- (a
signal on a ticker's last bar has no next day and is skipped)
entry_signals as
(
select
ticker,
next_date
as entry_date,
next_open
as entry_price
from bars
where
ha_color = 'green'
and
color_1_back = 'red'
and next_date
is not null
),
-- layer 3: exit signals (green to red);
-- the
trade exits at the next trading day's open
exit_signals as
(
select
ticker,
[date] as exit_signal_date,
next_date
as exit_date,
next_open
as exit_price
from bars
where
ha_color = 'red'
and
color_1_back = 'green'
),
-- layer 4: pair each entry with the first exit signal
for the same ticker
-- that
falls on or after the entry date
trades as
(
select
e.ticker,
e.entry_date,
e.entry_price,
x.exit_date,
x.exit_price
from
entry_signals e
outer apply
(
select top
1 xs.exit_date, xs.exit_price
from
exit_signals xs
where
xs.ticker = e.ticker
and
xs.exit_signal_date >= e.entry_date
order by
xs.exit_signal_date
) x
where
e.entry_date >= @start_date
),
-- layer 5: percent return for each trade
-- (a
trade still open at the end of the data has null exit
-- values
and a null return)
priced as
(
select
ticker,
entry_date,
entry_price,
exit_date,
exit_price,
cast((exit_price - entry_price) / entry_price * 100 as decimal(18,4)) as
trade_pct
from trades
)
-- final layer: add the cumulative percent return within
each ticker
-- and save the trades tagged with the ticker set and bar
period
insert into dbo.HA_Trades
(ticker_set,
bar_period, ticker, entry_date, entry_price,
exit_date,
exit_price, trade_pct, cum_pct)
select
@ticker_set,
@bar_period,
ticker,
entry_date,
entry_price,
exit_date,
exit_price,
trade_pct,
cast(sum(trade_pct) over (partition by ticker order by entry_date
rows unbounded
preceding) as decimal(18,4)) as cum_pct
from priced;
---------------------------------------------------------
-- 3. optional: display the trades
---------------------------------------------------------
select *
from dbo.HA_Trades
where ticker_set = @ticker_set
and bar_period =
@bar_period
order by ticker, entry_date;
---------------------------------------------------------
-- 4. summary by ticker: returns a three-column result
set:
-- ticker, trades, model_net_pct
-- (model_net_pct sums the percent change of the closed
trades;
-- an open trade at the end of the data drops out; spy is
listed last)
---------------------------------------------------------
select
ticker,
count(trade_pct)
as trades,
cast(sum(trade_pct) as decimal(10,2))
as model_net_pct
from dbo.HA_Trades
where ticker_set = @ticker_set
and bar_period =
@bar_period
group by ticker
order by case when ticker = 'SPY' then 1 else 0 end,
ticker;
Here are two images that display outputs from the follow-the-trend model in the same format as the buy-the-dip model in the preceding section. The top image shows its results are for the Long_term6_for_HA ticker set in the dbo.HA_Trades table, and the bottom image is derived from First_HA_Ticker_Set rows in the ticker_set column of the dbo.HA_Trades table. Within each image, the top panel shows an excerpt of the first nine rows from the trades log and the bottom panel shows the trade count and model return percentage change across tickers.
As in the preceding section, this section closes with a short script to rename both the dbo.HA_Trades table and its primary key object (dbo.pk_HA_Trades). This script makes it possible for model comparisons based on dbo.HA_Trades_Follow_Trend and dbo.HA_Trades_Buy_Dip tables.
-- rename dbo.pk_HA_Trades and dbo.HA_Trades after
backtests for both ticker sets
-- model comparison scripts can use
dbo.HA_Trades_Follow_Trend and
-- dbo.HA_Trades_Buy_Dip tables
use SecurityTradingAnalytics
go
exec sp_rename 'dbo.pk_HA_Trades',
'pk_HA_Trades_Follow_Trend', 'OBJECT';
exec sp_rename 'dbo.HA_Trades', 'HA_Trades_Follow_Trend';
Comparing the Buy-The-Dip and Follow-The-Trend Model Outcomes
This section compares the outcomes from the buy-the-dip and follow-the-trend models for the two ticker sets in this post. Before presenting the comparisons, the section briefly reviews general market conditions and ticker trends during the evaluation timeframe from January 3, 2017 through September 30, 2026. This background can help you understand the market environment in which both models were backtested.
The following chart shows monthly OHLC candles for the SPY benchmark. Each swoon is marked with a red oval and a number from 1 through 4. The table below the chart provides descriptive material for each of the four swoons from Google Gemini.
During swoons, it may be particularly advantageous to reference an automated trading model, such as the follow-the-trend model. This model has the potential to identify periods when it may be especially wise to liquidate your holdings to avoid deleterious impacts to a trading account balance. More search is necessary to unequivocably confirm this impact.
The following table shows historical performances for the tickers from First_HA_Ticker_Set, Long_term6_for_HA, and all twelve distinct tickers from both ticker sets. The total net percent change ranges from 588.55 to 1781.12. There is a slight, but consistent advantage across all three ticker sets in favor of the follow-the-trend model. In addition, the bottom two rows on the table show how the tickers in First_HA_Ticker_Set and All distinct tickers compare to the tickers in Long_term6_for_HA. Across both models, tickers in First_HA_Ticker_Set and All distinct tickers return enhanced results compared to tickers in Long_term6_for_HA. These results suggest that both the model and the ticker set combine to determine Total Net Percent Change.
The price for the model advantage is more trades. The following table shows that it requires nearly 2000 more trades to achieve the advantageous net percentage change. In these days of free trades free trades this just makes the extra trades a nuisance – especially if you do it from behind a tax free trading account.
Concluding Comments
This post shows a framework for processing multiple ticker sets across two different Heikin Ashi automated trading models. Both models are very simple, and neither model has been tested with multiple parameter values to seek optimum returns. Nevertheless, a couple of interesting findings emerged. The follow-the-trend model reported superior results to the buy-the-dip model across all three ticker sets (First_HA_Ticker_Set, Long_term6_for_HA, All distinct tickers). This tendency is worthy of further investigation with different tickers and/or different configuration settings.Next Steps for You
Don’t just read this post, develop your own custom Heikin Ashi automated trading model. This will allow you to assess your own custom ideas on how best to configure Heikin Ashi colors and/or values for automated trading models.
Comments
Post a Comment