Analyzing When to Buy and Sell Stocks Using T-SQL in SQL Server

By: Rick Dobson   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > TSQL

Problem

Please provide examples of how to use the data science techniques of segmentation and cross validation to improve and assess the performance of technical indicators, such as the MACD line indicator, for estimating profitable buy and sell dates for stock symbols.

Solution

A prior tip introduced the MACD (Moving Average Convergence/Divergence) technical indicators and demonstrated how one of these indicators was able to designate buy and sell dates for ten stock symbols over an approximately four-year time span. As is common for technical indicators, the indicators did not always specify buy and sell dates that lead to a profit, but the MACD line indicator did specify buy and sell dates that on average resulted in profits. The prior tip was relevant to SQL Server professionals because the demonstration was for stock price data in a SQL Server database, and the process for designating buy and sell dates was based on code in a T-SQL script.

Modern-day technical analysis tools have been growing in popularity for stock market analysis since at least the 1970's - and the MACD indicators are arguably the tools with the greatest current interest. In contrast, the emergence of data science is more recent, and its practitioners tend to be computer professionals instead of stock market technical analysts. Both technical analysis and data science offer capabilities for analyzing and predicting time-series data, such as historical price and volume data for stock symbols. This tip is an introductory exploration of interfaces between technical analysis and data science which demonstrates the application of two data science techniques for enhancing the efficacy of MACD line estimates for when to buy and sell stocks. This tip is also of more general interest because it illustrates a framework for evaluating enhancements to any predictive model based on any data stored within a SQL Server database.

• The first data science technique that this tip demonstrates is segmentation. With segmentation, a data scientist can group data to yield better predictive outcomes. For this tip, data are grouped by the duration of a stock trade, different supplemental models are applied to enhance the MACD line estimates of when to sell stocks for optimizing the profit from a trade.
• A second data science technique adapted within this tip is cross validation. You can cull through and/or calibrate prospective model specifications that are initially tested on one set of data and then validated on another data set. Each of two or more data sets can successively and alternatively serve as a development sample and a validation sample. This tip uses a simple adaptation of cross validation by developing models on one data set and then just validating the models on another data set.

Another special benefit delivered by this tip is the release of a refreshed version of the AllNasdaqTickerPricesfrom2014into2017 database. This database has time-series data for a subset of NASDAQ exchange stocks.

• An earlier version of the database was released along with a preceding tip that focused on arithmetic moving averages; that version of the database includes historical price and volume data for NASDAQ stocks as well as arithmetic moving averages for stock symbol close prices.
• The refreshed database released with this tip includes all tables from the initial release as well as tables for
  • exponential moving averages for NASDAQ stock symbol close prices
  • three MACD indicators for NASDAQ stocks
  • comparison tables that allow you to confirm how to contrast different models for time-series data

A quick review of the MACD indicators

An introductory tip on MACD indicators defined three indicators and then drilled down on how to apply one of these indicators.

• The MACD line indicator, sometimes just called the MACD, is typically defined as the 12-day exponential moving average less the 26-day exponential moving average for a time-series data item, such as a stock symbol's close price on successive trading days. This indicator has a centerline value of zero. When the MACD line indicator rises from below to above its centerline value, it is a good time to buy a stock so long as the MACD line value remains above its centerline value. This is because the short-term moving average is above a longer-term moving average. In other words, prices are going up.
• The signal line indicator is typically defined as a 9-day exponential moving average of the MACD line indicator. Because the signal line is a moving average based on the MACD line indicator, the signal line value trails the MACD line value. As a result, when the MACD line value falls below the signal line value, the MACD line value is beginning to fall relative to its recent values. This is sometimes interpreted as indicating near term stock price falls even though the MACD line value may be above its centerline value of zero.
• The MACD histogram indicator is the MACD line value less the signal line value. MACD histogram values are often plotted as histogram bars around the MACD centerline value.

Aside from confirming how to compute all three MACD indicators, the introductory MACD tip focused on how to derive buy and sell date recommendations for a stock symbol with the MACD line indicator.

• A recommended buy date is where a MACD line value moves from below its centerline value of zero to above its centerline value.
• A recommended sell date is the last date after a recommended buy date where the MACD line exceeds its centerline value.

This tip revisits these rules as it looks for ways of improving the realized profit associated with buy and sell dates. Ad hoc analyses of profits resulting from buy and sell recommendations created with the introductory tip on MACD indicators suggest two supplementary rules for enhancing profits.

• One new rule emerges from an understanding that the MACD line value typically declines as it approaches its centerline value of zero from above. As a result, profit enhancements may be realized by selling a stock before the MACD line crosses its centerline value.
• A second new rule takes advantage of the understanding that the MACD line decelerates growth as it passes from above to below its signal line. This deceleration can be a precursor to a period of declining prices. Therefore, a sell date based on the MACD line value falling below the signal line may help to preserve profits gained since a buy date.
• Empirical ad hoc testing suggests these two new rules apply best to different types of buy and sell recommendations.
  • The falling of the MACD line value below the signal line value works best for buy and sell recommendation dates that have relatively fewer trading days between them.
  • The selling of a stock slightly before the MACD line falls below its centerline value works better for buy and sell recommendation dates that have relatively more trading days between them.
  • Neither of these rules offer any obvious advantage to recommended buy and sell dates that are just a few days apart.

A quick review of the buy and sell recommendations from the prior tip

There are ten stocks in the base sample; these stocks were studied for profitability in the introductory MACD tip. The following script shows centerline cross-over price percent change for the ten stock symbols from that tip.

• There is a separate row in the result set for each symbol from the base sample.
• The number of trades column indicates the number of trades that the centerline cross-over model specified for a symbol. While each trade for each symbol is characterized by a buy date and a sell date, these dates do not show in the result set because the query aggregates results across all trades for a symbol.
• The first_last_change_% column returns the average percent change between the first close price (close_first) and last close price (close_last) across each of the trades for a symbol.
• The earlier_close_comparison_report table in the AllNasdaqTickerPricesfrom2014into2017 database released with this tip has a separate row for each trade for every symbol in both the base and validation samples. This table is one of two comparison report tables for this tip. This tip includes separate sections on the creation and population of each of these tables later in the tip.
  • The database name reminds you that all trades are for historical price and volume data from as early as the first trading day in 2014.
  • The data extend through November 8, 2017 or earlier if the data pull from Yahoo Finance had no historical data for that date at the time of the data extraction.
• The where clause excludes from the result set stock symbols in the validation sample. This where clause restricts the results to stock symbols from the base sample. The validation sample is based on a set of stock symbols not included in the introductory MACD tip.
• The group by clause returns one row per symbol from the base sample.

-- centerline cross-over price percent change for all symbols in the base sample
-- where clause excludes cross validation sample
select 
 symbol
,count(*) [number of trades]
,(sum(close_last - close_first)/avg(close_first) * 100)  [first_last_change_%]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where 
symbol not in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL') 
group by symbol

The following screen shot displays the results from the preceding query.

• The ten stock symbols in the result set denote the base sample stock symbols. All model development is performed with these ten stock symbols. Many readers may be familiar with Microsoft (MSFT), Amazon (AMZN) and PriceLine (PCLN). Other symbols are for less commonly known stocks, but all symbols are for widely traded stocks that have ample volume, so they can be readily traded.
• The second column shows the number of trades specified by the MACD line centerline model. Because each trade has both a buy date and a sell date, there are twice as many dates as there are trades for each symbol.
• The third column returns the percent change from the buy price through to the sell price across all trades for a stock symbol.
  • For all except one stock symbol (PCLN), the average percent change is positive.
  • Three stock symbols have average percent change values of over 100 percent.
  • The median percent close price change is between 59.05 percent and 64.17 percent across all ten stock symbols.
  • Please keep in mind that these percent close price changes were achieved over the four-year span from 2014 through 2017.

The following set of four scripts shows more detail about the MSFT trades from perspectives that may help you to understand the segmentation model used for enhancing the profits from the centerline rule for specifying buy and sell dates.

• The first script returns a separate row for each of the thirteen pairs of buy and sell dates for Microsoft (MSFT) stock.
• The second script returns a separate row for each Microsoft stock trade that has fifty or more days between its buy date, or start date, and its sell date, or end date. Analysis of the base sample trades of fifty days or more shows them often benefitting from terminating the trade prior to reaching a sell date that matches the MACD line just before falling below its centerline value.
• The third script returns a separate row for each Microsoft stock trade that is between four and forty-nine days in length. For these trades, the base sample analysis showed that profit could be improved consistently by exiting a trade after the MACD line fell below the signal line.
• The fourth script is for Microsoft trades that have a duration of three days or less. These trades are characterized by a MACD line value that briefly popped above the centerline and then quickly returned below the centerline value. The empirical analysis of these base sample trades did not suggest a supplemental model that could improve their profitability.

-- summary of trades for MSFT

-- all trades for MSFT
select 
 start_date
,end_date
,symbol
,number_of_row_numbers
,close_first
,close_last
,first_last_change
,[first_last_change_%]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where symbol = 'MSFT'

-- MSFT trades of 50 days or longer
select 
 start_date
,end_date
,symbol
,number_of_row_numbers
,close_first
,close_last
,first_last_change
,[first_last_change_%]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where symbol = 'MSFT'
and number_of_row_numbers >= 50

-- MSFT trades of 4 through 49 days
select 
 start_date
,end_date
,symbol
,number_of_row_numbers
,close_first
,close_last
,first_last_change
,[first_last_change_%]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where symbol = 'MSFT'
and number_of_row_numbers > 3 and number_of_row_numbers <= 49

-- MSFT trades of 3 days or less
select 
 start_date
,end_date
,symbol
,number_of_row_numbers
,close_first
,close_last
,first_last_change
,[first_last_change_%]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where symbol = 'MSFT'
and number_of_row_numbers <= 3

The following screen shot shows the result sets from the four queries in the same order as the preceding scripts.

• The top result set has a separate row for each of the 13 Microsoft trades designated by the centerline rule.
  • The start_date value corresponds to the buy date for each trade, and the end_date designates the sell date for each trade.
  • The symbol column is not strictly necessary in this result set because all trades are for a single symbol - MSFT. However, there are ten different symbols in the full base sample.
  • The number_of_row_numbers column indicates the number of trading days from the buy date through the sell date for a trade. For example, the first listed trade is bought one trading day (January 31, 2014) and sold on the very next trading day (February 3, 2014). The number of calendar days is greater than two, but there are only two trading days from the buy date through to the sell date because there is no trading on weekend days (or stock market holidays either).
  • The close_first and close_last column values denote the close price on the buy and sell dates, respectively.
  • The first_last_change column is the close_last column value less the close_first column value. This value represents the total change in value of a single share in a stock from the buy date through to the sell date.
  • The first_last_change_% column is the first_last_change column value divided by the close_first column value. This column displays the percentage change between buy and sell dates relative to the buy price for a stock.
• The second, third and fourth result sets correspond to the second, third, and fourth scripts from the preceding code listing. All columns for each of the result sets are defined in an identical way to the columns of the top result set. The only difference between the result sets is in the rows that appear in result set. Additionally, you should understand that the rows in the second, third, and fourth result sets are mutually exclusive to one another. That is, the data for a trade can exist in only one of these result sets.
  • The second result set is for trades that extend over fifty or more trading days.
    • There are five trades in this segment.
    • These trades all show a profit.
    • The profit percentage starts at a low of slightly over six percent up through slightly more than eighteen and a half percent.
  • The third result set is for trades that have a duration of four through forty-nine trading days.
    • There are also five trades in this segment.
    • Three of the five trades have a loss percentage, and only two of the trades result in a profit.
  • The fourth result set is for trades that have only two or three trading days from the buy date through the sell date.
    • There are three trades in this segment.
    • All three trades result in a loss.
    • The losses are of relatively modest magnitude.

Creating and populating a table with trade outcomes from the earlier close model

Before assessing the success of the supplementary models, it is necessary to collect trade outcomes for analysis. The first model is named the earlier close model because it exits a trade by leaving out about the last ten percent of the trading days from the centerline cross-over model. Empirical testing confirmed that the earlier close model preserved more of the maximum gain attained by close prices for trades lasting fifty days or more than the sell date for the centerline cross-over model. Results confirming this finding are presented in the "Did the earlier close and macd_below models improve profits?" section towards the end of this tip.

The following script can be used to generate and save the trade results for both the centerline cross-over model and the earlier close date model for a set of symbols. The trade results are archived in the earlier_close_comparison_report table in the AllNasdaqTickerPricesfrom2014into2017 database. The script is available from the close_last_vs_close_earlier_comparer.sql file, which is among the resources in the download for this tip.

Notice that the script starts with a default reference to the AllNasdaqTickerPricesfrom2014into2017 database. This reference is immediately followed by some commented code that creates a fresh copy of the earlier_close_comparison_report table. The commented code is intended to be run just once for each time that you create and populate the table for a set of stock symbols.

Following the commented code for creating the earlier_close_comparison_report table is a declaration statement for the @symbol local variable, which you can use to assign @symbol a value with a varchar data type having a maximum length of up to five characters. The script below assigns the string AAOI, but this value is to be changed in successive runs of the script.

• At the end of each run of the script, the trades for the stock symbol with the current value of the @symbol local variable are inserted into the earlier_close_comparison_report table.
• In populating the earlier_close_comparison_report table for this tip, the values for the @symbol variable were successively selected from the base sample (AAOI, AMZN, BIDU, CRUS, HTHT, INTC, MSFT, NVDA, PCLN, SIVB) and the validation sample (BZUN, ACXM, MMSI, CORT, MU, TIL).

After the preliminary steps described above, the main body of the script commences. The processing steps for populating the earlier_close_comparison_report table for the current value of @symbol begin by freshly creating and populating the #macd_indicators_with_row_numbers_for_symbol temporary table. This temporary table is, in turn, based on the macd_indicators table, which was created initially by a prior tip on MACD indicators. With the exception of row_number, the columns are the same as in the macd_indicators table; the column names are: symbol, date, close, row_number, macd, signal, and macd_histogram.

The next significant code block freshly creates and populates the #start_end_dates_b4_odd_drop temporary table. The code for populating this table finds dates matching MACD line value transitions from below to above the centerline and just before the MACD line moves from above to not above the centerline. For this tip, we are only interested in beginning and ending dates for when the MACD line is above the centerline.

The data from the #start_end_dates_b4_odd_drop temporary table are successively filtered and transformed until they end up in the #start_end_dates_on_single_rows temporary table. This temporary table has a single row with start and end dates for each block of trading days in which the MACD line is above its centerline value. The key role for the table is to provide information for documenting the trades based on the centerline line model. Critical columns in the table include the following:

• start_date and end_date values correspond to buy and sell dates, respectively, for the trade documented on a row
• symbol for the current value of @symbol
• number_of_row_numbers for the number of trading days from the buy date through the sell date
• close_first for the close price on the buy date
• close_last for the close price on the sell date
• close_max for the maximum close price starting on the buy date through the sell date

Next, the #for_close_earlier_dates_and_close_values table is freshly created and populated. While the #start_end_dates_on_single_rows table has a single row for each trade no matter what the duration of the trade, the code for the #for_close_earlier_dates_and_close_values table populates its rows only for trades with a duration of fifty or more days as originally specified by the centerline model. The key role of this table is to document the sell date and close price for trades specified by the close model. The columns from the #for_close_earlier_dates_and_close_values table include the following:

• start_date_with_close_earlier is for the start date for a trade; this column value matches start_date column value in the #start_end_dates_on_single_rows table
• end_date_with_close_earlier is for the date that a trade would end by the centerline model (derived from the #start_end_dates_on_single_rows table); this column, along with the start_date_with_close_earlier column, facilitates matching rows between the start_end_dates_on_single_rows table and the #for_close_earlier_dates_and_close_values table
• symbol is for the current value of @symbol
• ceiling_lookup_row is for the number of trading days in the revised trade based on closing the trade prior to the sell date for the centerline model
  • the ceiling_lookup_row value is equal to the ceiling function value of 90 percent of the trading days from the centerline model for a trade
  • the number_of_row_numbers column makes available the number of trading days from the centerline model; its value is available in this temporary table for your easy reference and to help validate the join
• The next two columns provide what could arguably be called the most critical values of the #for_close_earlier_dates_and_close_values table
  • the next two columns provide what could arguably be called the most critical values of the #for_close_earlier_dates_and_close_values table
  • the close_earlier column contains the close price when the trade closes for the earlier close model

After the #for_close_earlier_dates_and_close_values table is freshly created and populated, it is left joined to the #start_end_dates_on_single_rows table. The join result set is processed further to create metrics for documenting each trade. To facilitate the proper interpretation of these metrics, the columns of the joined result in the #for_close_earlier_comparison_report table are described briefly below.

• start_date is the buy date for a trade; this date is equally valid for both the centerline and the earlier close models
• end_date is the sell date for a trade in the centerline model
• close_earlier_date can have one of two values
  • when the number_of_row_numbers column value is greater than or equal to fifty, it is the sell date for the trade in the earlier close model
  • otherwise it is null
• symbol is the current value for @symbol
• number_of_row_numbers is the number of trading days derived from the centerline model
• close_first is the close price at the buy date
• close_last is the close price at the sell date from the centerline model
• close_earlier can have one of two values
  • when the number_of_row_numbers column value is greater than or equal to fifty, it is the close_earlier column value from the result set from the #for_close_earlier_comparison_report table
  • otherwise, it is close_last
• close_max is the maximum close price from the buy date through the sell date based on the centerline model; this column and associated columns related to it are not a central point of interest for this tip, but some developers may find it of value to know the maximum profit potential from a trade
• first_last_change is close_last less close_first; this quantity represents the profit or loss associated with the trade based on the centerline model
• first_last_change_% is first_last_change expressed as a percentage of close_first
• first_earlier_change is close_earlier less close_first; this quantity represents the profit or loss associated with the trade based on the earlier close model
• first_ealier_change_% is first_earlier_change expressed as a percentage of close_first
• first_max_change is close_max less close_first; this quantity represents the profit or loss from the centerline model if the trade was ended on the date when the profit was at its maximum value
• first_max_change_% is first_max_change expressed as a percentage of close_first

After the #for_close_earlier_comparison_report table is freshly created and populated for the current value of @symbol, two remaining steps complete the script.

• The result set for the #for_close_earlier_comparison_report table is displayed via a select statement.
• Then, the #for_close_earlier_comparison_report table result set is inserted into the earlier_close_comparison_report table. Completing this step for each ticker symbol in the base and validation samples builds a history of the trades that can be used to assess the efficacy of the early close model versus the centerline model.

use [AllNasdaqTickerPricesfrom2014into2017]
go

/*

-- run once before invoking the script below
-- for which to collect symbol comparison tables
-- for a set of symbols

begin try
drop table [dbo].[earlier_close_comparison_report]
end try
begin catch
print 'earlier_close_comparison_report not available to drop'
end catch

create table [dbo].[earlier_close_comparison_report](
	[start_date] [date] NULL,
	[end_date] [date] NULL,
	[close_earlier_date] [date] NULL,
	[symbol] [varchar](10) NULL,
	[number_of_row_numbers] [bigint] NULL,
	[close_first] [money] NULL,
	[close_last] [money] NULL,
	[close_earlier] [money] NULL,
	[close_max] [money] NULL,
	[first_last_change] [money] NULL,
	[first_last_change_%] [money] NULL,
	[first_earlier_change] [money] NULL,
	[first_earlier_change_%] [money] NULL,
	[first_max_change] [money] NULL,
	[first_max_change_%] [money] NULL
) ON [PRIMARY]

go


*/


declare @symbol varchar(5) = 'AAOI'

-- macd_indicators extract with symbol row_number

begin try
drop table #macd_indicators_with_row_numbers_for_symbol
end try
begin catch
print '#macd_indicators_with_row_numbers_for_symbol not available to drop'
end catch

select [symbol]
      ,[date]
      ,[close]
      ,row_number() over (order by date) row_number
      ,[macd]
      ,[signal]
      ,[macd_histogram]
into #macd_indicators_with_row_numbers_for_symbol
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[macd_indicators]
where 
symbol = @symbol
order by [date]

--select * from #macd_indicators_with_row_numbers_for_symbol order by [date]

-------------------------------------------------------------------------------------

-- processing code to derive start and end dates
-- for macd centerline cross overs

-- get start and end dates for successive, alternating macd blocks
-- before removing a trailing start block with no matching end block

begin try
drop table #start_end_dates_b4_odd_drop
end try
begin catch
print '#start_end_dates_b4_odd_drop not available to drop'
end catch



-- find all starts and ends for centerline cross-overs
select 
*
into #start_end_dates_b4_odd_drop
from
(
select 
[date]
,symbol
,[close]
,[row_number]
,macd
,lead(macd,1) over (order by row_number) next_macd
,sign(macd) sign_of_macd
,sign(lead(macd,1) over (order by row_number)) sign_of_next_macd
,sign(lag(macd,1) over (order by row_number)) sign_of_prior_macd
,
case
	
	when row_number = 3 
	and sign(macd) = 1 then 'start_pos'

	when sign(macd) = 1 
	and sign(lag(macd,1) over (order by row_number)) = -1 then 'start_pos'

	when 
	sign(macd) != sign(lead(macd,1) over (order by row_number)) 
	and sign(macd) = 1 then 'end_pos'

	when 
	sign(macd) != sign(lead(macd,1) over (order by row_number)) 
	and sign(macd) != 1 then 'end_not_pos'

	when row_number = 3 
	and sign(macd) = -1 then 'start_not_pos'
	
	when 
	sign(macd) != 1 
	and sign(macd) = sign(lead(macd,1) over (order by row_number)) 
	and sign(lead(macd,1) over (order by row_number)) != 1 
	and sign(lag(macd,1) over (order by row_number)) = 1 then 'start_not_pos'

	else NULL

end macd_pos_not_pos

from #macd_indicators_with_row_numbers_for_symbol 

where 
row_number > = 3
) for_is_not_start_or_end

where macd_pos_not_pos is not null



-- delete row with start_or_end equal to prior_start_or_end
-- gets rid of rows not continued with another matching pos or not pos_row

begin try
drop table #start_end_dates_b4_odd_drop_with_start_or_end
end try
begin catch
print '#start_end_dates_b4_odd_drop_with_start_or_end not available to drop'
end catch


-- add start_or_end and prior_start_or_end columns 
-- to #start_end_dates_b4_odd_drop_with_start_or_end 
-- from #start_end_dates_b4_odd_drop
select 
*
,
case
	when left(macd_pos_not_pos,5) = 'start' then 'start'
	when left(macd_pos_not_pos,3) = 'end' then 'end'
end start_or_end
,
case
	when left(lag(macd_pos_not_pos,1) over(order by row_number),5) = 'start' then 'start'
	when left(lag(macd_pos_not_pos,1) over (order by row_number),3) = 'end' then 'end'
end prior_start_or_end
into #start_end_dates_b4_odd_drop_with_start_or_end
from #start_end_dates_b4_odd_drop





-- select * from #start_end_dates_b4_odd_drop

delete from #start_end_dates_b4_odd_drop_with_start_or_end where start_or_end = prior_start_or_end



-- restore #start_end_dates_b4_odd_drop columns
-- after fix for contiguous duplicate start_or_end values
-- where start and end dates are the same

GO
begin try
drop table #start_end_dates_b4_odd_drop
end try
begin catch
print '#start_end_dates_b4_odd_drop not available to drop'
end catch

select 
 [date]
,symbol
,[close]
,[row_number]
,macd
,next_macd
,sign_of_macd
,sign_of_next_macd
,sign_of_prior_macd
,macd_pos_not_pos 
into #start_end_dates_b4_odd_drop 
from #start_end_dates_b4_odd_drop_with_start_or_end



-- eliminate a start date without a matching end date

declare @date_for_row_to_delte date =
(select top 1 [date] from #start_end_dates_b4_odd_drop order by date desc)


-- delete odd-row at end, if there is a start date without a matching end date

if (select count(*) % 2 from #start_end_dates_b4_odd_drop) = 1
begin
	delete from #start_end_dates_b4_odd_drop where date = @date_for_row_to_delte 
end



begin try
drop table #start_end_dates
end try
begin catch
print '#start_end_dates not available to drop'
end catch


-- save cleaned start and end dates
select * into #start_end_dates from #start_end_dates_b4_odd_drop

-- select * from #start_end_dates

--------------------------------------------------------------------------------------------

-- macd centerline cross-over start and end dates
-- on single rows with selected columns


begin try
drop table #start_end_dates_on_single_rows
end try
begin catch
print '#start_end_dates_on_single_rows not available to drop'
end catch


select 
 start_date
,end_date
,symbol
,[close]
,macd
,number_of_row_numbers
,(
 select 
 top 1 [close] close_first 
 from #macd_indicators_with_row_numbers_for_symbol 
 where date >= [start_date] and date <= [end_date] order by [date]
 ) close_first
,(
 select 
 top 1 [close] close_last 
 from #macd_indicators_with_row_numbers_for_symbol 
 where date >= [start_date] and date <= [end_date] 
 order by date desc
 ) close_last
,(
 select 
 max([close]) close_max
 from #macd_indicators_with_row_numbers_for_symbol 
 where date >= [start_date] and date <= [end_date]
 ) close_max
into #start_end_dates_on_single_rows
from
(
select 
start_date
,end_date
,symbol
,[close]
,macd
,number_of_row_numbers
from
(
select 
macd_pos_not_pos 
,[date] start_date
,lead([date],1) over (order by date) end_date
,symbol
,[close]
,macd
,row_number start_row_number
,lead(row_number,1) over (order by date) end_row_number
,lead(row_number,1) over (order by date)-row_number + 1 number_of_row_numbers
from #start_end_dates
where macd_pos_not_pos in ('start_pos', 'end_pos')
--where macd_pos_not_pos = 'start_pos'
) for_start_date_end_date
where macd_pos_not_pos = 'start_pos'
) for_close_prices_in_dates


-- select * from #start_end_dates_on_single_rows

---------------------------------------------------------------------------------------------


-- generate new earlier close dates with matching close prices
-- before last close dates and prices for macd centerline cross-overs
-- lasting 50 days or longer

begin try
drop table #for_close_earlier_dates_and_close_values
end try
begin catch
print '#for_close_earlier_dates_and_close_values not available to drop'
end catch



select 
 start_date [start_date_with_close_earlier]
,end_date [end_date_with_close_earlier]
,symbol
,number_of_row_numbers
,ceiling(number_of_row_numbers*.9) ceiling_lookup_row
,
 (
 select [close] from #macd_indicators_with_row_numbers_for_symbol where row_number = 
 (
 -- returns row_number from #macd_indicators_with_row_numbers_for_symbol for new close value
 select (row_number - (number_of_row_numbers - ceiling(number_of_row_numbers*.9)))
  from #macd_indicators_with_row_numbers_for_symbol 
  where [date] = end_date
 )
 ) close_earlier
,
 (
 select [date] from #macd_indicators_with_row_numbers_for_symbol where row_number = 
 (
 -- returns row_number from #macd_indicators_with_row_numbers_for_symbol for new close value
 select (row_number - (number_of_row_numbers - ceiling(number_of_row_numbers*.9)))
  from #macd_indicators_with_row_numbers_for_symbol 
  where [date] = end_date
 )
 ) close_earlier_date
into #for_close_earlier_dates_and_close_values
from #start_end_dates_on_single_rows
where number_of_row_numbers >= 50

-- select * from #for_close_earlier_dates_and_close_values

-- generate a report for macd centerline cross-overs and
-- compare buy/sell profits based on close_first versus 
-- close_last, close_earlier, and close_max 



begin try
drop table #for_close_earlier_comparison_report
end try
begin catch
print '#for_close_earlier_comparison_report not available to drop'
end catch




select 
 start_date
,end_date
,close_earlier_date
,symbol
,number_of_row_numbers
,close_first
,close_last
,
 case
	when close_earlier is not null then close_earlier
	else close_last
 end close_earlier
,close_max
,first_last_change
,[first_last_change_%]
,
 case
	when first_earlier_change is not null then first_earlier_change
	else first_last_change
 end first_earlier_change
,
 case
	when [first_earlier_change_%] is not null then [first_earlier_change_%]
	else [first_last_change_%]
 end [first_earlier_change_%]
,first_max_change
,[first_max_change_%]

into #for_close_earlier_comparison_report

from
(
select 
 start_date
,end_date
,close_earlier_date
,#start_end_dates_on_single_rows.symbol
,#start_end_dates_on_single_rows.number_of_row_numbers
,close_first
,close_last
,close_earlier
,close_max
,close_last - close_first [first_last_change]
,round(((close_last - close_first)/(close_first)*100),2) [first_last_change_%]
,close_earlier - close_first [first_earlier_change]
,round(((close_earlier - close_first)/(close_first)*100),2) [first_earlier_change_%]
,close_max - close_first [first_max_change]
,round(((close_max - close_first)/(close_first)*100),2) [first_max_change_%]
from #start_end_dates_on_single_rows
left join #for_close_earlier_dates_and_close_values
on 
#start_end_dates_on_single_rows.start_date = #for_close_earlier_dates_and_close_values.start_date_with_close_earlier
and
#start_end_dates_on_single_rows.end_date = #for_close_earlier_dates_and_close_values.end_date_with_close_earlier
) for_nulls_out




-- generate a report for macd centerline cross-overs and
-- compare buy/sell profits based on close_first versus 
-- close_last, close_earlier, and close_max 


select 
* 
from #for_close_earlier_comparison_report




-- populate earlier_close_comparison_report for current symbol
insert into  earlier_close_comparison_report
select 
* 
from #for_close_earlier_comparison_report

Creating and populating a table with trade outcomes from the macd_below model

The next script listing shows the code for saving a history of trades based on the model that closes a trade when the MACD descends below signal line after initially rising above its centerline value. This script is available from the close_last_vs_macd_line_signal_cross-over_comparer.sql file in the download for this tip. The model built by the code described in this section is called the macd_below model to distinguish it from the earlier close model described in the preceding section.

The code for this section is very much like that for the preceding section. Differences between the code for this section versus the preceding one include the following.

• The creation and use of a new table to store the history of trades (macd_below_signal_comparison_report in this section versus earlier_close_comparison_report in the preceding section).
• This section additionally shows new code that captures the move of the MACD below its signal line after the MACD crosses from below to above its centerline value. It also demonstrates how to document these trades after their buy and sell dates are identified so they can be saved in the macd_below_signal_comparison_report table.

Aside from these two distinctions noted above, the code between the two sections is identical. For that reason, a large block of code for developing content for populating the macd_below_signal_comparison_report table is not shown in this section. The excluded code begins at the declare statement for the @symbol variable and ends with the code for freshly creating and populating the #start_end_dates_on_single_rows table. Recall that this table contains buy and sell dates along with selected other trade data for the centerline cross-over model. The excluded code resides in the T-SQL script file for this section. If you feel the need to review the excluded code from the listing in this section, you can examine it from the preceding section or by opening the T-SQL script file for this section.

Here is the code to create the table for storing the history of trades based on the MACD line value descending below its signal line.

• The primary difference between this table and the one from the preceding section is the table name and names of selected columns.
• The main advantage of the table is that it provides a container for holding trade results from the macd_below model that is separate and distinct from the container for the earlier close model results.
• As with the earlier_close_comparison_report table from the preceding section, this table can accumulate trade results for a succession of different trading symbols. The code in this section enables the recording of transfer of trade results for the same ten base-sample symbols and six validation-symbol symbols as in the preceding section.
• Running the code in both sections as described generates comparable trade results by two different supplemental rules for the centerline cross-over model.
• Code and selected output in the next section demonstrate how to use the results from the macd_below_signal_comparison_report and earlier_close_comparison_report tables to assess the efficacy of the centerline model and its two supplemental models.

use [AllNasdaqTickerPricesfrom2014into2017]
go

-- run once before invoking the script below
-- for which to collect symbol comparison tables
-- for a set of symbols

begin try
drop table [dbo].[macd_below_signal_comparison_report]
end try
begin catch
print 'macd_below_signal_comparison_report not available to drop'
end catch

create table [dbo].[macd_below_signal_comparison_report](
	[start_date] [date] NULL,
	[end_date] [date] NULL,
	[close_macd_below_date] [date] NULL,
	[symbol] [varchar](10) NULL,
	[number_of_row_numbers] [bigint] NULL,
	[close_first] [money] NULL,
	[close_last] [money] NULL,
	[close_macd_below] [money] NULL,
	[close_max] [money] NULL,
	[first_last_change] [money] NULL,
	[first_last_change_%] [money] NULL,
	[first_macd_below_change] [money] NULL,
	[first_macd_below_change_%] [money] NULL,
	[first_max_change] [money] NULL,
	[first_max_change_%] [money] NULL
) ON [PRIMARY]
go

The next block of code for review in this section starts right after the fresh creation and population of the #start_end_dates_on_single_rows table. Recall that this table contains buy and sell dates for the stock denoted by the current value of @symbol along with close_first, close_last, and close_max values based on the centerline cross-over model.

The first step after the creation and population of the #start_end_dates_on_single_rows table is to left join a subquery named macd_and_signal_vals to the table. The values of the left joined result set are saved in the #start_end_dates_closes_line_below_signal table. The subquery is based on the macd_indicators table from the AllNasdaqTickerPricesfrom2014into2017 database. The join assigns all MACD and signal line values for each trading day from the first buy date through the last sell date for all trades for the symbol currently being processed. The subquery additionally adds a column named line_below_signal.

It is common in the technical analysis literature to require a confirmation before accepting a move as indicating a trend, such as the MACD moving below the signal line. In the script below, the second day of two consecutive trading days in which the MACD is below the signal line is the confirmation that the following script uses. The code for populating the #start_end_dates_closes_line_below_signal table enables a subsequent search for confirmed MACD values below signal line values by assigning a value of - 1 to each line_below_signal column value where the MACD value is below signal line value.

The line_below_signal column values are later processed to find the first confirmed trading day for each trade when the MACD value falls below the signal line. This step along with numerous other steps is implemented in the code to create and populate the #for_macd_below_comparison_report table. Another critical role of the code to create and populate the #for_macd_below_comparison_report table is to compute values for the following four fields: close_macd_below_date, close_macd_below, first_macd_below_change, and first_macd_below_change_% . Here's the definition for each of these fields. All four of these fields have conditional values.

• The close_macd_below_date column value is the confirmed date when the MACD line falls below the signal line for the first time in trade and the number of trading days for a trade is appropriate for the macd_below model.
  • When the number_of_row_numbers column value from the #start_end_dates_closes_line_below_signal table is greater than three and less than or equal to forty-nine, then its value is the confirmed trading date on which MACD first falls below the signal line in a trade.
  • Otherwise, its value is null.
• The close_macd_below column value is the close value on trading dates when there is a non-null close_macd_below_date value. Otherwise, this column value is the close value from the centerline cross-over model.
• The first_macd_below_change column value is the profit or loss associated with a trade.
  • When there is a non-null close_macd_below_date value, this value is computed as the close_macd_below value less the close_first value from the #start_end_dates_closes_line_below_signal table.
  • Otherwise, the first_macd_below_change column value is close_last less close_first from the #start_end_dates_closes_line_below_signal table.
• The first_macd_below_change_% is the first_macd_below_change column value expressed as a percentage of the close_first value from the #start_end_dates_closes_line_below_signal table.

After the #for_macd_below_comparison_report table is freshly created and populated for the current value of @symbol, two remaining steps complete the script.

• The result set for the #for_macd_below_comparison_report table is displayed via a select statement.
• Then, the #for_macd_below_comparison_report table result set is inserted into macd_below_signal_comparison_report table. As you complete this step for each ticker symbol in the base and validation samples, you will build a history of the trades that can be used to assess the efficacy of trades based on the early close model versus the centerline model.

-- create #start_end_dates_closes_line_below_signal as
-- left join of macd_and_signal_vals query to 
-- #start_end_dates_on_single_rows temp table
-- and computed line_below_signal value

begin try
drop table #start_end_dates_closes_line_below_signal
end try
begin catch
print '#start_end_dates_closes_line_below_signal not available to drop'
end catch


select 
 #start_end_dates_on_single_rows.[start_date]
,#start_end_dates_on_single_rows.end_date
,macd_and_signal_vals.date
,#start_end_dates_on_single_rows.symbol
,#start_end_dates_on_single_rows.[close]
,#start_end_dates_on_single_rows.number_of_row_numbers
,macd_and_signal_vals.macd
,macd_and_signal_vals.signal
,SIGN(macd_and_signal_vals.macd - macd_and_signal_vals.signal) line_below_signal

into #start_end_dates_closes_line_below_signal

from #start_end_dates_on_single_rows
left join
(
-- macd and signal values for a symbol
select distinct
       [macd_indicators].[symbol]
      ,[macd_indicators].[date]
      ,[macd_indicators].[macd]
      ,[macd_indicators].[signal]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[macd_indicators]
where 
symbol = (select distinct symbol from #start_end_dates_on_single_rows)
and signal is not null
) macd_and_signal_vals
on #start_end_dates_on_single_rows.symbol = macd_and_signal_vals.symbol
where 
date >= #start_end_dates_on_single_rows.start_date and date <= #start_end_dates_on_single_rows.end_date
order by #start_end_dates_on_single_rows.start_date


-- select * from #start_end_dates_closes_line_below_signal


-- for change and change% computations for macd centerline 
-- cross-overs for comparing buy/sell profits based on 
-- close_first versus 
-- close_last, close_macd_below, and close_max 

begin try
drop table #for_macd_below_comparison_report
end try
begin catch
print '#for_macd_below_comparison_report not available to drop'
end catch



select 
 for_computed_change_and_change_percent_fields.[start_date]
,for_computed_change_and_change_percent_fields.end_date
,for_computed_change_and_change_percent_fields.close_macd_below_date
,for_computed_change_and_change_percent_fields.symbol
,for_computed_change_and_change_percent_fields.number_of_row_numbers
,for_computed_change_and_change_percent_fields.close_first
,for_computed_change_and_change_percent_fields.close_last
,
case 
	when for_computed_change_and_change_percent_fields.close_macd_below_date 
	is not null and number_of_row_numbers < 50 and number_of_row_numbers > 3
	then for_computed_change_and_change_percent_fields.close_macd_below
	else for_computed_change_and_change_percent_fields.close_last
end close_macd_below
,for_computed_change_and_change_percent_fields.close_max
,close_last - close_first first_last_change
,(close_last - close_first)/close_first*100 [first_last_change_%]
,
case 
	when for_computed_change_and_change_percent_fields.close_macd_below_date 
	is not null and number_of_row_numbers < 50 and number_of_row_numbers > 3
	then for_computed_change_and_change_percent_fields.close_macd_below
	else for_computed_change_and_change_percent_fields.close_last
end 
-
close_first
first_macd_below_change
,
(
case 
	when for_computed_change_and_change_percent_fields.close_macd_below_date 
	is not null and number_of_row_numbers < 50 and number_of_row_numbers > 3
	then for_computed_change_and_change_percent_fields.close_macd_below
	else for_computed_change_and_change_percent_fields.close_last
end 
-
close_first
)
/
close_first*100 [first_macd_below_change_%]
,close_max - close_first first_max_change
,(close_max - close_first)/close_first*100 [first_max_change_%]

into #for_macd_below_comparison_report

from
(
select
 #start_end_dates_on_single_rows.[start_date]
,#start_end_dates_on_single_rows.end_date
,for_close_macd_below_for_start_date_end_date_groups.min_date close_macd_below_date
,#start_end_dates_on_single_rows.symbol
,#start_end_dates_on_single_rows.number_of_row_numbers
,#start_end_dates_on_single_rows.close_first
,#start_end_dates_on_single_rows.close_last
,(
  select [close] 
  from macd_indicators 
  where [date] = for_close_macd_below_for_start_date_end_date_groups.min_date
  and symbol = #start_end_dates_on_single_rows.symbol
 ) close_macd_below
,#start_end_dates_on_single_rows.close_max
from #start_end_dates_on_single_rows
left join
(
-- min date for line_below_signal_indicator = -2
-- within start_date...end_date blocks
select 
 [start_date]
,[end_date]
,min([date]) min_date
from
(
-- #start_end_dates_closes_line_below_signal with 
-- computed line_below_signal_indicator
select 
 *
,line_below_signal + lag(line_below_signal,1) over (order by [date]) line_below_signal_indicator
from #start_end_dates_closes_line_below_signal
group by [start_date], end_date, [date], symbol, [close], number_of_row_numbers, macd, signal, line_below_signal
) for_first_confirmed_line_below_indicator_within_start_date_end_date
where line_below_signal_indicator = -2
group by 
 [start_date]
,[end_date]
,line_below_signal_indicator
) for_close_macd_below_for_start_date_end_date_groups
on #start_end_dates_on_single_rows.start_date = for_close_macd_below_for_start_date_end_date_groups.start_date
) for_computed_change_and_change_percent_fields




-- generate a report for macd centerline cross-overs and
-- compare buy/sell profits based on close_first versus 
-- close_last, close_earlier, and close_max 


select 
* 
from #for_macd_below_comparison_report



-- populate macd_below_signal_comparison_report for current symbol
insert into  macd_below_signal_comparison_report
select 
* 
from #for_macd_below_comparison_report

Did the earlier close and macd_below models improve profits?

An analysis shown previously in this tip confirmed that the centerline cross-over model returned a median percent profit of between 59.05 percent and 64.17 percent across all ten stock symbols used in a prior tip. The current tip examines two alternative models for supplementing the centerline cross-over model. The earlier close model targeted trades that lasted fifty days or longer, and the macd_below model aimed to improve trades lasting between four and forty-nine days.

• Did the two alternative models provide enhanced profits for the base sample of ten stock symbols and was their effect also reflected in the validation sample of six additional stock symbols?
• Furthermore, were alternative model effects demonstrated at the level of individual stock symbols as well as overall in either the set of base symbols and validation symbols?

The following script generates a result set that shows the earlier close and macd_below model effects in both the base and validation sample. There are three parts to the script.

• The first part of the script returns a result set that shows earlier close effects in both the base sample with ten stock symbols and the validation sample with six additional stock symbols.
  • The results are derived from the earlier_close_comparison_report table. This table includes a row for each trade that reflects both the centerline cross-over model effect and the effect of the centerline cross-over model supplemented by earlier close model.
  • Results are shown separately for the base sample and validation sample. A where clause enables this capability with a not in operator or an in operator. Results are pulled for the base sample with the not in operator and for the validation sample with the in operator.
  • A union operator concatenates the results set from two separate select statements - one select statement pulls results for the base sample and a second select statement pulls results for the validation sample.
  • Within each select statement in the first part, a where clause filters for trades that are fifty days or longer. In this way, the first part restricts its result set to trades for which the earlier close model was applied.
• The second part of the script has a similar design, but this part displays results for the macd_below model. This second part of the script is almost identical to the first part except for two issues.
  • First, the from clause specifies the macd_below_signal_comparison_report table as the source for its result set.
  • Second, the where clause specifying duration of a trade targets trades lasting more than three days and less than fifty days.
• The third part of the script has four, instead of just two, separate select statements that have their results concatenated by union operators.
  • Two of the four select statements designate the earlier_close_comparison_report table as their source.
  • The two remaining select statements designate the macd_below_signal_comparison_report table as their source.
  • Within these two pair of select statements, the query design is similar to the queries for showing the earlier close and macd_below model effects, except for the where clause about the duration of the trade. Both pair of select statements target trades with a duration of three days or less.

-- earlier close and macd_below model effects
-- compared for base sample versus validation sample

-- earlier close effect size in base sample versus validation sample
select 
'base sample' [sample name]
,count(*) [number of trades]
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_earlier - close_first)/avg(close_first) * 100) [centerline crossover after earlier close change fix]
,((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total earlier close change effect fix size]
,(((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average earlier close change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where 
number_of_row_numbers >= 50
and
symbol not in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')

union

select 
'validation sample' [sample name]
,count(*) [number of trades]
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_earlier - close_first)/avg(close_first) * 100) [centerline crossover after earlier close change fix]
,((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total earlier close change effect fix size]
,(((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average earlier close change effect fix size]

from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where 
number_of_row_numbers >= 50
and
symbol in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')


--  macd_below effect size in base sample versus validation sample
select
'base sample' [sample name]
,count(*) [number of trades] 
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_macd_below - close_first)/avg(close_first) * 100) [centerline crossover after macd_below change fix]
,((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total macd_below change effect fix size]
,(((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average macd_below change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[macd_below_signal_comparison_report]
where 
number_of_row_numbers < 50 and number_of_row_numbers > 3
and
number_of_row_numbers > 3 and number_of_row_numbers < 50
and
symbol not in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')

union

select
'validation sample' [sample name]
,count(*) [number of trades]
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_macd_below - close_first)/avg(close_first) * 100) [centerline crossover after macd_below change fix]
,((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total macd_below change effect fix size]
,(((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average macd_below change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[macd_below_signal_comparison_report]
where 
number_of_row_numbers < 50 and number_of_row_numbers > 3
and
number_of_row_numbers > 3 and number_of_row_numbers < 50
and
symbol in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')


-- no fix effect size in base sample versus validation sample
select 
'base sample' [sample name]
,count(*) [number of trades]
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_earlier - close_first)/avg(close_first) * 100) [centerline crossover after no fix change fix]
,((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total no fix change effect fix size]
,(((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average no fix change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where 
number_of_row_numbers <= 3
and
symbol not in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')

union

select
'base sample' [sample name]
,count(*) [number of trades] 
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_macd_below - close_first)/avg(close_first) * 100) [centerline crossover after no fix change fix]
,((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total no fix change effect fix size]
,(((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average no fix change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[macd_below_signal_comparison_report]
where 
number_of_row_numbers <= 3
and
symbol not in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')

union

select 
'validation sample' [sample name]
,count(*)  [number of trades]
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_earlier - close_first)/avg(close_first) * 100) [centerline crossover after no fix change fix]
,((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total no fix change effect fix size]
,(((sum(close_earlier - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average no fix change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[earlier_close_comparison_report]
where 
number_of_row_numbers <= 3
and
symbol in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')

union

select
'validation sample' [sample name]
,count(*) [number of trades] 
,(sum(close_last - close_first)/avg(close_first) * 100)  [centerline crossover]
,(sum(close_macd_below - close_first)/avg(close_first) * 100) [centerline crossover after no fix change fix]
,((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100) [total no fix change effect fix size]
,(((sum(close_macd_below - close_first) - sum(close_last - close_first))/avg(close_first) * 100))/count(*) [average no fix change effect fix size]
from [AllNasdaqTickerPricesfrom2014into2017].[dbo].[macd_below_signal_comparison_report]
where 
number_of_row_numbers <= 3
and
symbol in ('BZUN', 'ACXM', 'MMSI', 'CORT', 'MU', 'TIL')

The following screen shot shows the result sets from the preceding script.

• The first two rows are for comparisons of the centerline model versus the earlier close model in the base sample and validation sample rows. The third column shows the centerline model profit without any supplemental model effect. The fourth column shows the centerline profit supplemented by the earlier close model. The fifth column shows the total size of the earlier close model effect, and the sixth column shows the size of the earlier close model effect on a per trade basis.
  • The earlier close model improves the performance of the centerline model by about $6.40 per share per trade in both base and validation samples. This is an informative outcome that confirms the earlier close model profit enhancement is just about the same for two mutually exclusive sets of stock symbols.
  • Because there are more stocks and more trades in the base sample, the total profit enhancement is greater in the base sample than for the validation sample.
  • For trades lasting 50 days or longer by centerline cross-over model, the earlier close model adds about thirty percent more profit to the centerline model profits.
• The second two rows highlight the improvement in trade performance for the centerline model when it is supplemented by the macd_below model.
  • The trades for these two rows result in losses for the centerline model. However, supplementing the centerline model with the macd_below model converts the losses into gains.
  • The average size of the improvement is nearly $2.00 per share per trade in the base sample for the macd_below model. The validation sample shows an even larger improvement for the stock symbols tracked by it.
• The third pair of rows is for the trades that do not attempt to supplement the centerline model with either the earlier close model or the macd_below model. Recall that these are very short trades that last just two or three days. The third and fourth columns show the same type of trade performance outcomes (a relatively small loss) in the base and validation samples.
• Across both the earlier close and macd_below models, the profit added relative to the centerline model grows by slightly over fifty percent so that the total profit per share grows from $1091.09 to $1673.49! These profits are based on buying and selling just a single stock share, but it is common for stock share transactions to be for many shares. In any event, this phenomenal outcome on a percentage basis confirms the value of applying basic data science techniques to traditional technical analysis methods for finding profitable trades.

The next screen shot shows the enhancement effects from the earlier close model and the macd_below model for individual stock symbols; the code to generate these results along with prior results are in the macd comparer report analysis.sql file within the download for this tip. As you can see, there are four results sets.

• The first two result sets are for the base sample with the first one being for the earlier close model and the second one being for the macd_below model.
• The last two result sets report outcomes for the stock symbols in the validation sample. The third result set is for the earlier close model, and the final result set is for the macd_below model.

With exception of the MU symbol (for Micron Technology, Inc.) for the macd_below model, the average size of the supplemental model effect is greater than zero for all stock symbols in both the base and validation samples! In other words, both supplemental models (earlier close and macd_below) were broadly successful at improving trade performance over the centerline model.

Next Steps

To start testing and adapting the scripts presented in this tip, you will need to download the backup file for the AllNasdaqTickerPricesfrom2014into2017 database. For your convenience in testing and adapting code from this tip and prior related tips ( here, here, here, here ), the download associated with the current tip includes a refreshed copy of the backup file for the AllNasdaqTickerPricesfrom2014into2017 database. In order to speed download times and reduce storage requirements, the refreshed copy includes a subset of the symbols from prior tips, such as the macd_indicators table. The key tables generated from this tip, including the earlier_close_comparison_report table and the macd_below_signal_comparison_report table have results for all symbols used in the tip. The download additionally includes key script files used to generate temporary and permanent tables referenced in this tip.

After restoring the AllNasdaqTickerPricesfrom2014into2017 database backup file on the computer from which you run SQL Server, you can download the script files.

Next, start examining the content in the earlier_close_comparison_report table and the macd_below_signal_comparison_report table from the database. These two tables summarize results from the two supplemental models. Make sure that you understand the content in the tables. Consider making copies of the earlier_close_comparison_report and macd_below_signal_comparison_report tables before running the two main script files (close_last_vs_close_earlier_comparer.sql and close_last_vs_macd_line_signal_cross-over_comparer.sql). This is because the two main script files modify the comparison report tables in the database in ways that can introduce duplicate records if you do not back them up first and create a fresh empty version of the tables for documenting trades. Recall that you can create a fresh empty copy of the comparison report tables with commented code in each of the main script files.

You may care to try running the code from the two main script files with different stock symbols than the 16 referenced in this tip. The AllNasdaqTickerPricesfrom2014into2017 database released with this tip also includes data for 16 stock symbols not reported on in the tip that you can use for additional testing.

Finally, I close by indicating that this tip is not recommending any particular stocks, including those for which results are reported in this tip. The stocks reported on in this tip were selected because I had been watching them as part of my normal investing due diligence. At the time that I submitted this tip to MSSQLTips.com, my family members held positions in a subset of these stocks.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips