Our start-up stock advisory service successfully recommends long-term winningstocks. Some of our clients additionally want specific advice about when to buyand sell recommended stocks. Please provide an example SQL Server T-SQL solutionfor generating advice about when to buy and sell our recommended stocks.
Data mining tools can be used to assess a simple baseline strategy for generatingbuy and sell stock strategies. Additionally, you can use data mining tools to assessif refinements to a baseline strategy improve trading outcomes for buy and sellrecommendations. This tip draws on SQL Server T-SQL code and data mining techniquesfor 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 ofthe 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 stocktiming models using T-SQL code.
A previous MSSQLTips.com tip illustratedbasic stock trading development strategies. The prior tip demonstrated thata 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 anextended period of time, such as 2 years or more. Others, find it disquieting tohold stock positions when they see losses accumulating if only for a couple of weeksor months. Easy-to-understand and implement advice about when to buy and sell recommendedstocks will likely be welcomed by these individuals.
As with theearlier tip on trading strategies, this tip also uses the Google Finance siteas a data source for stock price histories. There is no significant change to howdata were retrieved for this tip versus the prior one. Basically, the approach isas simple as inserting a URL into an Internet browser, such as IE or Chrome. TheURL specifies the ticker symbol for a security along with a start date, end date,and data format for the data returned from the site.
Data for ten securities in four categories were downloaded from Google Financefor this tip. For easy reference, we refer in this tip to all securities as stocksalthough some are other investment instruments.
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. Forcommercial applications that require perfectly accurate data, you should considerimplementing some verification and cleaning steps that reflect the requirementsof your application for the downloaded data. These steps were not taken for thistip so as not to detract from the main focus on developing buy and sell recommendations.
Because Google Finance downloads one file per stock and this tip uses 10 tickersymbols, there are 10 downloaded csv files. When the files are downloaded, theirname is symbol.csv. For example, the file with data for the ulta symbol has thename ulta.csv. After the csv files are downloaded, you can copy them to whateverdirectory is convenient for processing. This tip uses the \\stock_price_histories_and_tradesfolder on the C: drive as a repository for downloaded data.
As the WHILE_LOOP_FIELDS table name implies, a WHILE loop is used for successivelyimporting data from each of the downloaded csv files. Within the WHILE loop,Dynamic T-SQL code relies on aBULK INSERT statement to import all data rows from each csv file to the imported_stock_pricestable. Then, anINSERT statement transfers the imported data along with a stock symbol valueto the stocks_symbol_ohlcv table.
Here's the script from a sql file named import_downloaded_data_into_SQL_Server.sqlthat implements the steps described above. Notice the code starts with a referenceto the stock_price_histories_trades database. The tip uses this database to storedata for implementing and evaluating the trading rules. The resource files folderfor this tip contains T-SQL code for creating a fresh, empty version of the stock_price_histories_tradesdatabase.
This tip uses 10-day and 30-day moving averages as well as the closing pricefor a trading day to verify if a stock is rising. Moving average computational techniquesfor stock prices are examined in aprior tip. Here are two specific criteria for assessing when stocks are rising.
This tip examines the two trading rules successively. First, the rising-stockprice rule is implemented and examined. Second, the buy-low rule for not executingbuys when the buy price is above the preceding buy price is implemented and comparedto the initial rising-stock trade outcomes.
To issue rising-stock buy and sell recommendations according to the rising-stockrule, an application needs to have available data for the current day, the nextday, and each of three preceding days. This tip collects all these inputs into rowsfor the current day within the stocks_symbol_trade_inputs table. Each row of thistable has the information needed to determine if the next day is a recommended buyday or sell day. It also has the date and open price for the next day, which willbe useful when evaluating the trading results for the rising-stock rule.
In addition to the values downloaded from Google Finance, the code to implementthe rising-stock rule needs 10-day and 30-day moving averages. These moving averagesare based on the close price for 10 or 30 trading days preceding and through thecurrent trading day. The 10-day and 30-day moving averages are compared to eachother and the 10-day moving average is compared to the close price of the currentday. The comparison outcomes need to be available for the current day as well asthe prior three days.
This tip examines the two trading rules successively. First the rising-stockprice rule is implemented and examined. Second, the buy-low rule for not executingbuys when the buy price is above the preceding buy price is implemented and comparedto 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 stocksymbol. Before the WHILE loop starts, a CREATE TABLE statement creates the stocks_symbol_trade_inputstable. Within the WHILE loop, there is a nested sub-query for computing 10-day and30-day moving averages. Then, and outer query computes comparisons for the currentday and three prior days. The outer query derives the open price and date for thenext trading day. The open price and date for the next day are candidate valuesfor buy or sell recommended date and price.
The stocks_symbol_trade_inputs table serves as input to the code for yet anothertable (stocks_symbol_buy_sell_recs) in the assignment of recommended buy and selldates and prices. The code for going from the stocks_symbol_trade_inputs table tothe 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 isto view selected rows from it. The following screen shot shows the last set of matchingbuy and sell recommendations for the ulta and crus ticker symbols in 2016 (throughthe 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 pricetrend 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 ultaon 2016-02-03 dropped from the open to the close so the close price was below the10-day moving average; the 10-day moving average was already below the 30-day movingaverage. 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_inputstable.
The four rows for the crus symbol show the starting date and price versus theending date and price for crus. For crus, several months separated the initial buyand 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 outcomesfor yourself by examining appropriate rows from the stocks_symbol_trade_inputs table.
As with the scripts for other tables in the application, the code for creatingand populating the stocks_symbol_buy_sell_recs table commences with a CREATE TABLEstatement followed by a WHILE loop. Within the WHILE loop, the buy and sell recommendationsfor 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 thebuy and sell recommendations for a stock symbol. This table is, in turn, createdby an outer query based on a union of two sub-queries.
An outer query does some minor processing to facilitate ordering the buy andsell rows by date. Then, a second query does some minor cleaning to remove somesell rows that have no matching buy rows and pumps the cleansed result set for asymbol into the stocks_symbol_buy_sell_recs table.
As you can see from the preceding screen shot, it is a relatively simple matterto compute the gain or loss associated with each trade by subtracting the buy pricefrom the sell price. However, the structure of the stocks_symbol_buy_sell_recs tabledoes not readily facilitate this computation because the buy and sell prices foreach trade are on different rows. In addition, for some stock symbols there canbe buy recommendations with no matching sell recommendation at the end of the tradesfor a stock. This is simply because the buy signal was issued, but the sell criteriato conclude the trade did not occur within the data downloaded for this tip (itoccurred 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, thistip reviewed only the detailed implementation steps for the stock-rising tradingstrategy. However, we need to include a second trading strategy that builds on thestock-rising strategy. This second trading rule builds on the stock-rising strategyby only including gains and losses when the buy price for a trade is less than thebuy price for the immediately preceding trade. Because this buy-low trading strategybuilds on the stock-rising strategy, its implementation was purposely deferred untilthis section. 59ce067264