By: Rick Dobson | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > TSQL
Problem
Our start-up stock advisory service successfully recommends long-term winning stocks. Some of our clients additionally want specific advice about when to buy and sell recommended stocks. Please provide an example SQL Server T-SQL solution for generating advice about when to buy and sell our recommended stocks.
Solution
Data mining tools can be used to assess a simple baseline strategy for generating buy and sell stock strategies. Additionally, you can use data mining tools to assess if refinements to a baseline strategy improve trading outcomes for buy and sell recommendations. This tip draws on SQL Server T-SQL code and data mining techniques for generating buy and sell stock advice and assessing if the advice grows capital.
The code is presented in a modular style so that others can replace parts of the approach with their own ideas or even just test the tip with different stocks. Hopefully, the framework will inspire others to implement better buy and sell stock timing models using T-SQL code.
A previous MSSQLTips.com tip illustrated basic stock trading development strategies. The prior tip demonstrated that a buy-and-hold strategy for a select set of stocks is a reliable way to grow capital. Nevertheless, some stock investors feel uncomfortable tying up their funds for an extended period of time, such as 2 years or more. Others, find it disquieting to hold stock positions when they see losses accumulating if only for a couple of weeks or months. Easy-to-understand and implement advice about when to buy and sell recommended stocks will likely be welcomed by these individuals.
Downloading the data from the Google Finance site
As with the earlier tip on trading strategies, this tip also uses the Google Finance site as a data source for stock price histories. There is no significant change to how data were retrieved for this tip versus the prior one. Basically, the approach is as simple as inserting a URL into an Internet browser, such as IE or Chrome. The URL specifies the ticker symbol for a security along with a start date, end date, and data format for the data returned from the site.
Returned data starts with the start date or the first trading date for which Google Finance has data. The returned data ends with the last trading date for which Google Finance has data. Historical prices include the current day sometime after the close of trading for a day. The following URL specifies the download of data for Ulta Salon, Cosmetics & Fragrance, Inc., which has a ticker symbol of ulta.
http://www.google.com/finance/historical?q=ulta&startdate=Jan+1%2C+2008&enddate=Dec+31%2C+2016&output=csv
Data for ten securities in four categories were downloaded from Google Finance for this tip. For easy reference, we refer in this tip to all securities as stocks although some are other investment instruments.
- A core set of stocks includes six selected because I was watching them based on "due diligence." The symbols for these stocks are: bzun, crus, meet, nvda, tsn, and ulta
- One additional stock was selected because it is for a company that many readers of this tip are familiar -- Microsoft Corporation (msft)
- Two more symbols represent inverse ETF securities. The INVESTOPEDIA site defines an inverse etf as: an exchange-traded fund (ETF) constructed by using various derivatives for the purpose of profiting from a decline in the value of an underlying benchmark
- The benchmark for one etf (sco) is crude oil prices
- The benchmark for the other etf (sds) is for the S & P 500 index
- The final symbol is for a regular etf (spy) that tracks the S & P 500 index. Instead of moving inversely to its benchmark, this etf seeks performance that correlates with its benchmark. As with all etf securities, the spy does not perfectly match its benchmark because of various considerations, such as management fees, stock trading commissions, composition of the underlying securities, pace of changes to the benchmark versus changes to the etf, etc.
The following screen shot from Notepad++ shows the first 12 rows downloaded for the ulta stock symbol.
- The first row indicates the column names for the data rows that follow
- The columns for data rows start with a date for the rest of the columns on that row
- The Open, High, Low, and Close columns display information about the price for the stock symbol over the course of the day denoted by the first column
- The last column for data rows indicates the number of shares traded during the day
The download was initiated for ulta and the other nine ticker symbols used in this tip on September 7, 2016 at around 2 PM Eastern Time. Therefore, the first data row is September 6, 2016 - one day before the download date.
All the data in the screen shot above, are valid and correctly formatted. However, Google Finance very infrequently returns data rows that contain invalid data. For commercial applications that require perfectly accurate data, you should consider implementing some verification and cleaning steps that reflect the requirements of your application for the downloaded data. These steps were not taken for this tip so as not to detract from the main focus on developing buy and sell recommendations.
Import downloaded data to SQL Server
Because Google Finance downloads one file per stock and this tip uses 10 ticker symbols, there are 10 downloaded csv files. When the files are downloaded, their name is symbol.csv. For example, the file with data for the ulta symbol has the name ulta.csv. After the csv files are downloaded, you can copy them to whatever directory is convenient for processing. This tip uses the \stock_price_histories_and_trades folder on the C: drive as a repository for downloaded data.
Notice from the screen shot above, that there is no symbol column in the individual ticker symbol files. However, when processing data for more than one symbol in a single table, it is necessary to have a symbol column in the SQL Server table along with the open, high, low, close, volume data.
This tip demonstrates a two-step approach to importing data from downloaded stock price and volume files to SQL Server tables.
- Initially, the data for each file is successively inserted into a staging table
- Then, the data are transferred from the staging table to another table and a column is added for the investment's ticker symbol
The overall import process relies on four tables within a database.
- The imported_stock_prices table is the staging table that successively holds the csv data for each ticker symbol
- The stocks_symbol_ohlcv table serves as the repository for data from all ten ticker symbols
- A third table (WHILE_LOOP_FIELDS) contains three columns to facilitate looping through the files and copying the data with each of the ten symbols to the stocks_symbol_ohlcv table
- One column for each row contains a ticker symbol
- Another column contains a numeric field (from 1 through the number of files) for each symbol
- A third column for each row contains the path and file name downloaded from Google Finance
- A fourth temporary table (T#) is used to track which of the ten ticker symbols is currently being processed
As the WHILE_LOOP_FIELDS table name implies, a WHILE loop is used for successively importing data from each of the downloaded csv files. Within the WHILE loop, Dynamic T-SQL code relies on a BULK INSERT statement to import all data rows from each csv file to the imported_stock_prices table. Then, an INSERT statement transfers the imported data along with a stock symbol value to the stocks_symbol_ohlcv table.
Here's the script from a sql file named import_downloaded_data_into_SQL_Server.sql that implements the steps described above. Notice the code starts with a reference to the stock_price_histories_trades database. The tip uses this database to store data for implementing and evaluating the trading rules. The resource files folder for this tip contains T-SQL code for creating a fresh, empty version of the stock_price_histories_trades database.
USE stock_price_histories_trades
GO
-- Populate stocks_symbol_ohlcv table
-- with imported ohlcv data for each stock symbol
-- from csv files
-- execute once before WHILE LOOP for
-- each row in table of stock symbols and file paths/names
-- table for receiving data from each csv stock file
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'imported_stock_prices' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE imported_stock_prices
CREATE TABLE imported_stock_prices(
[date] [date] NULL,
[open] [money] NULL,
[high] [money] NULL,
[low] [money] NULL,
[close] [money] NULL,
[volume] [bigint] NULL
) ON [PRIMARY]
-- table with consolidated ohlcv data
-- for all securities being tracked
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'stocks_symbol_ohlcv' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE stocks_symbol_ohlcv
CREATE TABLE [dbo].stocks_symbol_ohlcv(
[SYMBOL] [varchar](4) NOT NULL,
[date] [date] NULL,
[open] [money] NULL,
[high] [money] NULL,
[low] [money] NULL,
[close] [money] NULL,
[volume] [bigint] NULL
) ON [PRIMARY]
-- table with ticker symbols and csv
-- file paths and names being tracked
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'WHILE_LOOP_FIELDS' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE WHILE_LOOP_FIELDS
CREATE TABLE [dbo].WHILE_LOOP_FIELDS(
SYMBOL_ID int IDENTITY(1,1) PRIMARY KEY,
SYMBOL [varchar](4) NOT NULL,
path_file_name [varchar](50) NOT NULL
) ON [PRIMARY]
INSERT INTO [WHILE_LOOP_FIELDS]
VALUES
('ULTA', 'C:\stock_price_histories_and_trades\ulta.csv'),
('TSN','C:\stock_price_histories_and_trades\tsn.csv'),
('SPY','C:\stock_price_histories_and_trades\spy.csv'),
('SDS','C:\stock_price_histories_and_trades\sds.csv'),
('SCO','C:\stock_price_histories_and_trades\sco.csv'),
('NVDA','C:\stock_price_histories_and_trades\nvda.csv'),
('MSFT','C:\stock_price_histories_and_trades\msft.csv'),
('MEET','C:\stock_price_histories_and_trades\meet.csv'),
('CRUS','C:\stock_price_histories_and_trades\crus.csv'),
('BZUN','C:\stock_price_histories_and_trades\bzun.csv')
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-- Set up for loop
-- create temporary sequence in #T with identity #s
-- for each row in WHILE_LOOP_FIELDS
Select Identity(int, 1,1) AS PK
Into #T
From WHILE_LOOP_FIELDS
-- set variables for importing each data file
-- and adding symbols to consolidated table
Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1
DECLARE @SYMBOL varchar(4), @path_file_name varchar(50)
DECLARE @sql varchar(1000)
----------------------------------------------------------------------------
-- Loop through rows with symbols and
-- data file paths and names
WHILE @pk <= @maxPK
BEGIN
-- Get a row from WHILE_LOOP_FIELDS
Select @SYMBOL = [SYMBOL], @path_file_name = [path_file_name]
From WHILE_LOOP_FIELDS
Where SYMBOL_ID = @pk
-- Clear table for new file
TRUNCATE TABLE imported_stock_prices
-- Use path_file_name column value on the row to bulk insert
-- csv file for stock symbol to imported_stock_prices table
-- start from csv file row after one with column headers
SET @sql = 'BULK INSERT imported_stock_prices '
+ 'FROM ''' + @path_file_name + ''' '
+ 'WITH '
+ '('
+ 'FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''0x0a'''
+ ');'
EXEC (@sql)
-- Populate table with consolidated ohlcv data
-- for all stock symbols in WHILE_LOOP_FIELDS
INSERT INTO stocks_symbol_ohlcv
SELECT
@SYMBOL SYMBOL
,*
FROM imported_stock_prices;
-- update @pk value for next row
-- from WHILE_LOOP_FIELDS
Select @pk = @pk + 1
END
DROP TABLE #T
Review of trading rules for assigning buy and sell dates for stocks
Two trading rules for assigning buy and sell dates to a stock are examined in this tip.
- One rule is to buy stocks with rising prices that were previously not rising, and then to sell those stocks when they stop rising. This rule is named the stock-rising rule.
- A second buy-low rule rides on top of the first rule so that a buy is only executed when its buy price is less than the buy price for the immediately preceding trade. This rule helps to avoid buying a stock when it already has a relatively high price from which there is little upside potential. Sell recommendations are issued with the buy-low rule in the same exact way as the stock-rising rule.
This tip uses 10-day and 30-day moving averages as well as the closing price for a trading day to verify if a stock is rising. Moving average computational techniques for stock prices are examined in a prior tip. Here are two specific criteria for assessing when stocks are rising.
- The close price for a stock is greater than the 10-day moving average. This indicates the most recent price for a stock is greater than its average price over the prior 10 days.
- The 10-day moving average is greater than the 30-day moving average. This confirms the price trend over the past 10 days is greater than the price trend over the past 30 days.
The buy recommendation for the rising-stock rule depends on the following conditions over a 5-day period.
- The stock must not be rising for two successive days
- Followed by two more successive days in which the stock is rising
- The buy price is the open price on the fifth day in the sequence
The sell recommendation for the rising-stock rule depends on the following conditions over a 2-day period.
- The stock price must no longer be rising. More specifically, a day must occur in which neither the close price is above the 10-day moving average nor the 10-day moving average is above the 30-day moving average.
- The sell price is the open price on the next trading day.
This tip examines the two trading rules successively. First, the rising-stock price rule is implemented and examined. Second, the buy-low rule for not executing buys when the buy price is above the preceding buy price is implemented and compared to the initial rising-stock trade outcomes.
Creating and populating a table with inputs for the rising-stock rule
To issue rising-stock buy and sell recommendations according to the rising-stock rule, an application needs to have available data for the current day, the next day, and each of three preceding days. This tip collects all these inputs into rows for the current day within the stocks_symbol_trade_inputs table. Each row of this table has the information needed to determine if the next day is a recommended buy day or sell day. It also has the date and open price for the next day, which will be useful when evaluating the trading results for the rising-stock rule.
In addition to the values downloaded from Google Finance, the code to implement the rising-stock rule needs 10-day and 30-day moving averages. These moving averages are based on the close price for 10 or 30 trading days preceding and through the current trading day. The 10-day and 30-day moving averages are compared to each other and the 10-day moving average is compared to the close price of the current day. The comparison outcomes need to be available for the current day as well as the prior three days.
This tip examines the two trading rules successively. First the rising-stock price rule is implemented and examined. Second, the buy-low rule for not executing buys when the buy price is above the preceding buy price is implemented and compared to the initial rising-stock trade outcomes.
As with the code to import downloaded values, the computations for current, lagging, and leading day values are performed inside a WHILE loop successively for each stock symbol. Before the WHILE loop starts, a CREATE TABLE statement creates the stocks_symbol_trade_inputs table. Within the WHILE loop, there is a nested sub-query for computing 10-day and 30-day moving averages. Then, and outer query computes comparisons for the current day and three prior days. The outer query derives the open price and date for the next trading day. The open price and date for the next day are candidate values for buy or sell recommended date and price.
Here's the script from a sql file named compute_trade_inputs.sql that implements the steps described above.
USE stock_price_histories_trades
GO
-- Populate stocks_symbol_trade_inputs successively
-- with rows for each stock symbol in WHILE_LOOP_FIELDS
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'stocks_symbol_trade_inputs' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE stocks_symbol_trade_inputs
CREATE TABLE [dbo].[stocks_symbol_trade_inputs](
[SYMBOL] [varchar](4) NOT NULL,
[date] [date] NULL,
[open] [money] NULL,
date_lead_1 [date] NULL,
open_lead_1 [money] NULL,
[close] [money] NULL,
mav_10 [money] NULL,
mav_30 [money] NULL,
mav_10_gt_mav_30 bit NULL,
close_gt_mav_10 bit NULL,
mav_10_gt_mav_30_lag_3 bit NULL,
mav_10_gt_mav_30_lag_2 bit NULL,
mav_10_gt_mav_30_lag_1 bit NULL,
close_gt_mav_10_lag_3 bit NULL,
close_gt_mav_10_lag_2 bit NULL,
close_gt_mav_10_lag_1 bit NULL
) ON [PRIMARY]
-- Set up for loop
-- create temporary sequence in #T with identity #s
-- for each row in WHILE_LOOP_FIELDS
Select Identity(int, 1,1) AS PK
Into #T
From WHILE_LOOP_FIELDS
-- set variables for importing each data file
-- and adding symbols to consolidated table
Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1
DECLARE @SYMBOL varchar(4), @path_file_name varchar(50)
DECLARE @sql varchar(1000)
----------------------------------------------------------------------------
-- Loop through rows with stock symbols and
-- data file paths and names
WHILE @pk <= @maxPK
BEGIN
-- Get a row from WHILE_LOOP_FIELDS
Select @SYMBOL = [SYMBOL], @path_file_name = [path_file_name]
From WHILE_LOOP_FIELDS
Where SYMBOL_ID = @pk
INSERT INTO [stocks_symbol_trade_inputs]
SELECT
SYMBOL
,[date]
,[open]
,LEAD([date],1) OVER (ORDER BY [date]) date_lead_1
,LEAD([open],1) OVER (ORDER BY [date]) open_lead_1
,[close]
,mav_10
,mav_30
,mav_10_gt_mav_30
,close_gt_mav_10
,LAG(mav_10_gt_mav_30,3) OVER (ORDER BY [date]) mav_10_gt_mav_30_lag_3
,LAG(mav_10_gt_mav_30,2) OVER (ORDER BY [date]) mav_10_gt_mav_30_lag_2
,LAG(mav_10_gt_mav_30,1) OVER (ORDER BY [date]) mav_10_gt_mav_30_lag_1
,LAG(close_gt_mav_10,3) OVER (ORDER BY [date]) close_gt_mav_10_lag_3
,LAG(close_gt_mav_10,2) OVER (ORDER BY [date]) close_gt_mav_10_lag_2
,LAG(close_gt_mav_10,1) OVER (ORDER BY [date]) close_gt_mav_10_lag_1
FROM
(
SELECT
SYMBOL
,[date]
,[open]
,[close]
,mav_10
,mav_30
,
CASE
WHEN mav_10 > mav_30 AND mav_10 IS NOT NULL AND mav_30 IS NOT NULL THEN 1
WHEN mav_10 IS NULL THEN NULL
WHEN mav_30 IS NULL THEN NULL
ELSE 0
END mav_10_gt_mav_30
,
CASE
WHEN [close] > mav_10 AND mav_10 IS NOT NULL THEN 1
WHEN mav_10 IS NULL THEN NULL
ELSE 0
END close_gt_mav_10
FROM
(
-- add mav_10 and mav_30 to SYMBOL and date columns
Select
SYMBOL
,[date]
,[open]
,[close]
,
mav_10 = CASE WHEN ROW_NUMBER() OVER (ORDER BY [Date]) > 9
THEN SUM([Close]) OVER (ORDER BY [Date] ROWS BETWEEN 9 PRECEDING AND CURRENT ROW)
END/10
,
mav_30 = CASE WHEN ROW_NUMBER() OVER (ORDER BY [Date]) > 29
THEN SUM([Close]) OVER (ORDER BY [Date] ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
END/30
FROM stocks_symbol_ohlcv
WHERE SYMBOL = @SYMBOL
) adding_mav_10_and_mav_30
) adding_rising_mav_indicators
-- update @pk value for next row
-- from WHILE_LOOP_FIELDS
Select @pk = @pk + 1
END
DROP TABLE #T
Creating a table with buy and sell recommendations for the rising-stock trading strategy
The stocks_symbol_trade_inputs table serves as input to the code for yet another table (stocks_symbol_buy_sell_recs) in the assignment of recommended buy and sell dates and prices. The code for going from the stocks_symbol_trade_inputs table to the stocks_symbol_buy_sell_recs table implements the rising-stock trading strategy.
An easy way to understand the role of the stocks_symbol_buy_sell_recs table is to view selected rows from it. The following screen shot shows the last set of matching buy and sell recommendations for the ulta and crus ticker symbols in 2016 (through the final trading date of September 6, 2016 for the data used by this tip).
In the first pair of rows for ulta, there was a reversal of the rising price trend on the day (2016-02-02) for which a buy recommendation was issued. As a result, a sell recommendation was set for the next day (2016-02-03). The price for ulta on 2016-02-03 dropped from the open to the close so the close price was below the 10-day moving average; the 10-day moving average was already below the 30-day moving average. It is the close price on 2016-02-02 that caused the trade to end on 2016-02-03. In the case of the second set of buy and sell recommendations for ulta in 2016, there was a several month gap between the buy recommendation and the sell recommendation. You can verify this gap is appropriate by checking appropriate rows from the stocks_symbol_trade_inputs table.
The four rows for the crus symbol show the starting date and price versus the ending date and price for crus. For crus, several months separated the initial buy and sell recommendation, but only a few days separated the second buy and sell recommendations. As with the buy and sell recommendations for ulta, you can verify these crus outcomes for yourself by examining appropriate rows from the stocks_symbol_trade_inputs table.
This tip examines more deeply the gains and losses associated with trades in a subsequent section.
As with the scripts for other tables in the application, the code for creating and populating the stocks_symbol_buy_sell_recs table commences with a CREATE TABLE statement followed by a WHILE loop. Within the WHILE loop, the buy and sell recommendations for each symbol are added to the stocks_symbol_buy_sell_recs table.
A temporary table named #to_clean_recs serves as a staging table to accept the buy and sell recommendations for a stock symbol. This table is, in turn, created by an outer query based on a union of two sub-queries.
- The first sub-query extracts selected rows from the stocks_symbol_trade_inputs table for the current symbol being processed that match the buy recommendation criteria
- The second sub-query performs the same function for the stocks_symbol_trade_inputs table rows that match sell recommendation criteria
An outer query does some minor processing to facilitate ordering the buy and sell rows by date. Then, a second query does some minor cleaning to remove some sell rows that have no matching buy rows and pumps the cleansed result set for a symbol into the stocks_symbol_buy_sell_recs table.
The script (compute_buy_and_sell_recommendations.sql) that implements these steps appears below.
USE stock_price_histories_trades
GO
-- Populate stocks_symbol_buy_sell_recs table successively
-- with rows for each stock symbol in WHILE_LOOP_FIELDS
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'stocks_symbol_buy_sell_recs' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE stocks_symbol_buy_sell_recs
CREATE TABLE [dbo].[stocks_symbol_buy_sell_recs](
[SYMBOL] [varchar](4) NOT NULL,
[date] [date] NULL,
[open] [money] NULL,
[recommendation] varchar(4) NULL
) ON [PRIMARY]
-- Set up for loop
-- create temporary sequence in #T with identity #s
-- for each row in WHILE_LOOP_FIELDS
Select Identity(int, 1,1) AS PK
Into #T
From WHILE_LOOP_FIELDS
-- set variables for importing each data file
-- and adding symbols to consolidated table
Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1
DECLARE @SYMBOL varchar(4), @path_file_name varchar(50)
DECLARE @sql varchar(1000)
----------------------------------------------------------------------------
-- Loop through rows with stock symbols and
-- data file paths and names
WHILE @pk <= @maxPK
BEGIN
-- Get a row from WHILE_LOOP_FIELDS
Select @SYMBOL = [SYMBOL], @path_file_name = [path_file_name]
From WHILE_LOOP_FIELDS
Where SYMBOL_ID = @pk
-- Compute candidate buy_sell recommendations
-- for all trades
BEGIN TRY
DROP TABLE #to_clean_recs
END TRY
BEGIN CATCH
END CATCH
SELECT
SYMBOL
,date_lead_1
,open_lead_1
,recommendation
,LAG(recommendation,1) OVER (ORDER BY date_lead_1) rec_lag_1
INTO #to_clean_recs
FROM
(
-- rows with buy_sell recommendation
SELECT
'buy' 'recommendation'
,*
FROM stocks_symbol_trade_inputs
WHERE
SYMBOL = @SYMBOL
AND mav_10_gt_mav_30_lag_3 = 0
AND close_gt_mav_10_lag_3 = 0
AND mav_10_gt_mav_30_lag_2 = 0
AND close_gt_mav_10_lag_2 = 0
AND (mav_10_gt_mav_30_lag_1 = 1 OR close_gt_mav_10_lag_1 = 1 )
AND (mav_10_gt_mav_30 = 1 OR close_gt_mav_10 = 1)
UNION
-- rows with potential sell dates (date_lead_1)
SELECT
'sell' 'recommendation'
,*
FROM stocks_symbol_trade_inputs
WHERE
SYMBOL = @SYMBOL
AND mav_10_gt_mav_30 = 0
AND close_gt_mav_10 = 0
AND (mav_10_gt_mav_30_lag_1 = 1 OR close_gt_mav_10_lag_1 = 1)
) to_clean_recs
ORDER BY to_clean_recs.date_lead_1
-- Compute cleaned set of buy_sell recommendations
-- for all trades
INSERT INTO stocks_symbol_buy_sell_recs
SELECT
SYMBOL
,date_lead_1 [date]
,open_lead_1 [open]
,recommendation
FROM #to_clean_recs
WHERE
(recommendation = 'sell' AND rec_lag_1 != 'sell')
OR
(recommendation = 'buy')
-- update @pk value for next row
-- from WHILE_LOOP_FIELDS
Select @pk = @pk + 1
END
DROP TABLE #T
Compute Gain_Loss outcomes for rising-stock and buy-low trade strategies
As you can see from the preceding screen shot, it is a relatively simple matter to compute the gain or loss associated with each trade by subtracting the buy price from the sell price. However, the structure of the stocks_symbol_buy_sell_recs table does not readily facilitate this computation because the buy and sell prices for each trade are on different rows. In addition, for some stock symbols there can be buy recommendations with no matching sell recommendation at the end of the trades for a stock. This is simply because the buy signal was issued, but the sell criteria to conclude the trade did not occur within the data downloaded for this tip (it occurred or will occur at a later date, namely after September 6, 2016).
There is one other matter that bears on gain and loss computations. So far, this tip reviewed only the detailed implementation steps for the stock-rising trading strategy. However, we need to include a second trading strategy that builds on the stock-rising strategy. This second trading rule builds on the stock-rising strategy by only including gains and losses when the buy price for a trade is less than the buy price for the immediately preceding trade. Because this buy-low trading strategy builds on the stock-rising strategy, its implementation was purposely deferred until this section.
The gains and losses per share for each trade are saved in one of two tables depending on the trading rule.
- The gains and losses for the stock-rising strategy are saved in the all_trades_gain_loss table
- The gains and losses for the buy-low strategy are saved in the buy_low_trades_gain_loss table
The following screen shot shows the gain and loss outcomes for the buy and sell recommendations in the preceding screen shot. Recall that these are for completed trades for ulta and crus during 2016. These data are from the all_trades_gain_loss table. You can readily confirm for yourself from the rows in the following screen shot that the gain or loss is simply the sell price less the buy price.
Also note that the per share gain is nearly $52 for ulta in the trade ending on May 24, 2016! This oversized gain is associated with a trade having a buy price that is well below the buy price of the preceding trade. In an inversely corresponding way, the crus trade ending on May 5, 2016 ends in a loss of $1.85. This trade has a buy price that is greater than the preceding trade. The buy-low trade strategy would keep the trade having a $52 per share gain and exclude the trade ending in a loss of $1.85. The buy-low trading rule can selectively include and exclude trades without actually performing the trade because it is based on a comparison of the current prospective trade buy price to the previous trade buy price. If the buy price for current prospective trade is greater than the buy price for the previous trade, the trade is not selected for execution by the buy-low trading strategy.
As discussed, the gain and loss computations depend on corresponding buy and sell prices for the buy date and sell date in a trade. The data source for these computations is the all_trades_gain_loss table for the stock-rising trade strategy. The data source for gains and losses for the buy-low trading strategy is the buy_low_trades_gain_loss table.
The following listing (create_and_populate_gain_loss_tables.sql) shows the code for creating and populating the gains and losses for each of the two trading strategies. It follows the discussion within this section as well as the general coding conventions of preceding sections about computing results for each symbol in turn.
USE stock_price_histories_trades
GO
-- create and populate gain_loss tables
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'all_trades_gain_loss' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE all_trades_gain_loss
CREATE TABLE [dbo].[all_trades_gain_loss](
[SYMBOL] [varchar](4) NOT NULL,
[buy_date] [date] NULL,
[sell_date] [date] NULL,
[buy_price] [money] NULL,
[sell_price] [money] NULL,
[gain_loss] [money] NULL
) ON [PRIMARY]
IF EXISTS
(
SELECT * from INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'buy_low_trades_gain_loss' AND TABLE_SCHEMA = 'dbo'
)
DROP TABLE buy_low_trades_gain_loss
CREATE TABLE [dbo].[buy_low_trades_gain_loss](
[SYMBOL] [varchar](4) NOT NULL,
[buy_date] [date] NULL,
[sell_date] [date] NULL,
[buy_price] [money] NULL,
[sell_price] [money] NULL,
[gain_loss] [money] NULL
) ON [PRIMARY]
-- Set up for loop
-- create temporary sequence in #T with identity #s
-- for each row in WHILE_LOOP_FIELDS
Select Identity(int, 1,1) AS PK
Into #T
From WHILE_LOOP_FIELDS
-- set variables for importing each data file
-- and adding symbols to consolidated table
Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1
DECLARE @SYMBOL varchar(4), @path_file_name varchar(50)
DECLARE @sql varchar(1000)
----------------------------------------------------------------------------
-- Loop through rows with stock symbols and
-- data file paths and names
WHILE @pk <= @maxPK
BEGIN
-- Get a row from WHILE_LOOP_FIELDS
Select @SYMBOL = [SYMBOL], @path_file_name = [path_file_name]
From WHILE_LOOP_FIELDS
Where SYMBOL_ID = @pk
-- populate all_trades_gain_loss and buy_low_trades_gain_loss
-- for current @SYMBOL value
INSERT INTO all_trades_gain_loss
SELECT
SYMBOL
,buy_date
,sell_date
,buy_price
,sell_price
,gain_loss
FROM
(
-- for clean gain_loss data
SELECT
[SYMBOL]
,[date] buy_date
,LEAD([date],1) OVER (ORDER BY [date]) sell_date
,[open] buy_price
,LEAD([open],1) OVER (ORDER BY [date]) sell_price
,
(
LEAD([open],1) OVER (ORDER BY [date])
-
[open]
) gain_loss
,recommendation
FROM [stock_price_histories_trades].[dbo].[stocks_symbol_buy_sell_recs]
WHERE
SYMBOL = @SYMBOL
) for_clean_gain_loss_data
WHERE
sell_date IS NOT NULL
AND recommendation = 'buy'
ORDER BY SYMBOL, [buy_date]
INSERT INTO buy_low_trades_gain_loss
SELECT
[SYMBOL]
,[buy_date]
,[sell_date]
,[buy_price]
,[sell_price]
,[gain_loss]
FROM
(
SELECT
[SYMBOL]
,[buy_date]
,[sell_date]
,[buy_price]
,LAG(buy_price,1) OVER (ORDER BY buy_date) buy_price_lag_1
,[sell_price]
,[gain_loss]
FROM [stock_price_histories_trades].[dbo].[all_trades_gain_loss]
WHERE SYMBOL = @SYMBOL
) for_buy_low_gain_loss
WHERE buy_price < buy_price_lag_1
-- update @pk value for next row
-- from WHILE_LOOP_FIELDS
Select @pk = @pk + 1
END
DROP TABLE #T
Comparison of gain_loss performance for trading rules
The reports in this section are from an SSRS project based on queries for the all_trades_gain_loss and buy_low_trades_gain_loss tables in the stock_price_histories_trades database. In order not to distract from focusing on the findings, the T-SQL code for the report queries do not appear in the tip. A resource folder available for download with this tip contains the SSRS project, which, in turn, has the T-SQL code for each report's query (see the Dataset Properties window of the dataset for each report within the SSRS project).
The following screen shot shows an SSRS report confirming that both the rising-stock and buy_low trading rules can return gains for selected stock categories. Results in the table are across all trades made for a stock category with a trading rule. Buy and sell prices as well as gain_loss values are in dollars on a per share basis. Broker commissions for trades are not included because these can vary depending on the broker that executes the trades.
- The due diligence stocks (bzun, crus, meet, nvda, tsn, and ulta) returned a 2.56 percent gain on shares per trade with the rising-stock strategy
- With the buy_low trading strategy for due diligence stocks, the percentage gain per trade rises to 5.18 percent; this gain is driven substantially by a huge drop in the buy price for trades selected by the buy_low trading rule (sum of buy prices per trade was $6,119.90 with the rising-stock rule, but it was only $2,593.44 for the buy_low rule)
- The spy etf, which has the S & P 500 index as its benchmark, returned the same kind of trend, but not to the same extent, as the due diligence stocks
- Microsoft's stock did not generate gains with either rule
- Instead, it returned slight losses per trade basis with the rising stock rule
- However, the buy_low rule did cut the percentage for the size of the loss about in half
- The two inverse etf securities (sds and sco) did not return positive results, and the trade outcomes were about the same on a percentage basis with either rule for inverse etf securities
There are at least three reasons why the buy_low strategy is a winner with the due diligence stocks. The following chart documents these for you.
- The gain per share is nearly 70% greater for buy_low trades than rising-stock trades ($1.08 per share versus $0.64)
- The percentage of winning trades is marginally better with the buy_low strategy than with the rising-stock rule (by a ratio of about 32% to 30%)
- The number of trades with the buy_low rule is about half as many as for the rising-stock rule. The reduced number of trades makes more capital available for gains with other trades for other stocks (normally, a stock advisory firm will recommend many more than the 6 due diligence stocks covered in this tip)
Before closing, it is worthwhile taking a look at the percent gain_loss from the two trading rules with individual due diligence stocks. The buy price, sell price, and gain_loss values are the sums across all trades for a stock with a trading model. The gain loss percent is the summed gain_loss value divided by the summed buy price value represented as a percentage.
- Overall, the magnitude of the gains were up 7 to 8 percent for the best performing stocks and trading strategies
- With the exception of one stock, the buy_low strategy improves gains or changes a loss into a gain relative to the rising-stock strategy
- For the one stock (nvda) that did not improve its performance with the buy_low strategy, the percentage gain was nearly identical between the two strategies
- The variations in outcomes by stock and trading strategy point to the value of historically testing the performance for a stock with a trading strategy before endorsing its use for trading a stock
Next Steps
This tip demonstrates how to implement a baseline trading model (rising-stock) and optionally augment it with a supplemental approach (buy-low). The concepts are easy to understand, and their implementation is also relatively simple to follow. The two trading strategies can work together to preserve and grow capital.
The tip describes and demonstrates the logic behind a suite of four T-SQL scripts used for importing data, pre-processing data for implementing the trading models, and running the trading models. One goal of the tip is to encourage you to replace its trading models with ones that you prefer instead. At the very least, you are urged to replace the stocks used in this tip with those of specific interest to you.
Here are a few questions for you to consider in deciding your next steps after reading this tip.
- Would you like to try this approach to see how it works with your stocks?
- What are some better ways of operationalizing the concepts described in the trading models examined within this tip?
- Will moving averages over different periods than 10 and 30 days improve the trade outcomes?
- Can you enhance the model for sell recommendations to retain more of the gain achieved while a stock is rising?
- Can you enhance the model for buy recommendations to reduce the percentage of trades that end before a stock ever starts rising?
- Can you design, implement, and test vastly superior trading models based on other data mining tools besides moving averages and if-then-else rules?
The resource files folder for this tip includes the four main T-SQL scripts for importing, pre-processing, and implementing the trading rules as well as selected other scripts for data displayed in screen shots that support the explanation of the trading rules. Additionally, the resource files include the SSRS project files for the reports in the Comparison of gain_loss performance for trading rules section. Please be sure and review the readme.txt file in the resource solution folder before trying to reproduce any results in this tip based on the files in the resource files folder.
Finally, I close by indicating that this tip is not recommending any particular stocks, including the ten analyzed ones. These stocks were chosen 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, I or other immediate family members held positions in seven stocks among the ten in the tip.
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
