A SQL Model for Buying and Selling Tickers Based on Historical Prices from Tiingo and EMAs
This Security Trading Analytics blog exists to illustrate analytical approaches for trading securities. This post describes and demonstrates T-SQL code for choosing buy and sell dates for four securities based on raw close prices downloaded with PowerShell and EMA value sets computed in SQL Server.
The model chooses to buy a ticker share when the security price concurrently rises above its long-term and short-term price trends. The model chooses to sell an owned ticker share when the security price falls below a short-term price trend that is above the one used to select a buy date. Recognize that the model is just one hypothesis among many for choosing buy and sell dates. Within the context of this post, the main objective of implementing the model is to establish a framework for backtesting the model returns. A follow-up post to this one will illustrate how to implement backtesting the model and identify opportunities for tweaking the model to improve its performance.
The ticker share prices are downloaded from Tiingo, a firm that provides expansive financial datasets, primarily through its robust API (Application Programming Interface). Tiingo services a diverse range of clients from asset managers and financial technology firms through to individual traders and developers. The visitors to this blog are likely to be Individual traders and/or developers that are interested in the free-tier plan for end-of-day data. This plan allows the downloading from a vast reservoir of tens of thousands of tickers for stocks and ETFs. The limits on amount of data you can download with the free plan are described in this Tiingo documentation page, and my initial impression is that they may be sufficient for blog visitors to get started using Tiingo and make an assessment about whether Tiingo suits their needs. A prior post from this blog includes a mini tutorial for Tiingo showing how to get your free API key as well as a simple PowerShell script for downloading data from Tiingo to your local computer.
Tickers Tracked in this Post
Four tickers are tracked in this post.
The SPY ticker is a traditional non-leveraged ETF that tracks the S&P 500 Index. The SPY ticker aims to reflect the daily price performance of the index on a 1-to-1 basis. The SPXL ticker is a leveraged ETF that tracks three times the daily price performance of its underlying index (the S&P 500 Index).
The QQQ ticker is a traditional non-leveraged ETF that tracks the Nasdaq-100 Index. The QQQ ticker aims to reflect daily price performance of its underlying index on a 1-to-1 basis. The TQQQ ticker is a leveraged ETF that tracks three times the daily price performance of its underlying index (the Nasdaq-100 Index).
PowerShell Script for Downloading Tiingo Data with Excerpted Results
The PowerShell script in this section illustrates a four-step process for extracting either adjusted or unadjusted prices for the four tickers tracked in this post. By allowing for the return of either type of price, the script can be suitable for other requirements besides those described in this post.
The unadjusted price is the actual close price on a trading day that a security was bought or sold. There is no adjustment for corporate actions, including stock splits and dividends paid.
The adjusted price is the unadjusted price modified to reflect corporate actions, including stock splits and dividends paid.
It is common among other providers of security price data to not report raw, unadjusted security prices. Instead, most other providers commonly return security prices adjusted for splits. Another less common option is to return security prices adjusted for splits as well as security prices adjusted for both splits and dividends.
Among the reasons that adjusted prices are used is to perform long-term performance evaluations that need to reflect the impact of stock splits on stock prices as well the return of dividends to stockholders. The model examined in this post aims to designate buy and sell dates based on raw end-of-day prices relative to exponential moving averages based on the raw prices at the time of a trade. Therefore, the Tiingo unadjusted prices are most appropriate for this post.
Here is a brief summary of each step in the four-step process implemented by the following PowerShell script. Notice that the script allows you to return either Tiingo unadjusted prices or Tiingo adjusted prices.
Step 1 permits you to identify yourself to the Tiingo API server with your free API token.
Step 2 allows you to specify a parent folder and child folders for storing the downloaded data in csv files on storage devices directly connected to your local computer or to remote storage devices mapped to your local computer. The example below includes different destinations for storing unadjusted and adjusted prices. It also wipes the destination folders clean before downloading fresh csv files.
Step 3 includes the list of tickers tracked in this report. Change the list according to your needs (keeping in mind that there are limits for the number tickers and rate at which prices can be downloaded with free Tiingo accounts).
Step 4 loops the ticker list designated in step 3 as it attempts to download data as csv files from the Tiingo API server to your designated storage device. Error catching is also included in this step for tickers whose prices do not download for any reason.
# Step 1: Define API token and toggle
$token = "Your Tiingo API token goes here" # Replace with your actual token
$useAdjusted = $false # Set to $true for adjusted prices and $false for raw prices
# Step 2: Define folder path
$folderBase = "C:\SecurityTradingAnalytics"
$folderPath = if ($useAdjusted) { "$folderBase\AdjustedPrices" } else { "$folderBase\UnadjustedPrices" }
if (!(Test-Path $folderPath)) { New-Item -ItemType Directory -Path $folderPath }
# Cleanup: remove old CSV files before fresh download
Get-ChildItem -Path $folderPath -Filter *.csv | Remove-Item
# Step 3: Define tickers (update this list as needed)
$tickers = @("SPY", "SPXL", "QQQ", "TQQQ")
# Step 4: Loop through each ticker
foreach ($ticker in $tickers) {
Write-Host "Processing $ticker..."
$startDate = "2022-01-01"
$endDate = (Get-Date).ToString("yyyy-MM-dd")
$url = "https://api.tiingo.com/tiingo/daily/$ticker/prices?startDate=$startDate&endDate=$endDate&token=$token"
try {
$response = Invoke-WebRequest -Uri $url -UseBasicParsing
$data = $response.Content | ConvertFrom-Json
$priceField = if ($useAdjusted) { "adjClose" } else { "close" }
Write-Host "Using price field: $priceField"
$longFormat = $data | Select-Object @{Name="date";Expression={$_.date}}, @{Name="price";Expression={$_.$priceField}}
$outputPath = "$folderPath\$ticker-$priceField.csv"
$longFormat | Export-Csv $outputPath -NoTypeInformation
Write-Host "✅ Success: Saved $ticker to $outputPath"
}
catch {
$errorMessage = "❌ Failed to download data for $ticker on $(Get-Date)"
Write-Warning $errorMessage
Add-Content "$folderPath\error_log.txt" $errorMessage
}
}
The following table shows excerpts populated with the first five data rows from each of the unadjusted csv files saved on the local computer from the Tiingo API server. For this tip’s demonstration, data collection commenced on January 3, 2022 and ran through October 23, 2025.
Notice date column values are in a format that ends with a Z for Zulu time. Zulu time is an alternative representation in Tiingo for SQL Server’s way of representing datetime values in an ISO 8601 format. T-SQL code in the next section illustrates how to translate Zulu date values to SQL Server datetime values for saving date column values in a Microsoft SQL Server table.
Importing Downloaded Tiingo prices into a SQL Server Table
A T-SQL script can sequentially import to a SQL Server destination table (dbo.ticker_date_prices in the SecurityTradingAnalytics database) each ticker’s csv file downloaded by the PowerShell script in the preceding section.
The script for populating the destination table assumes it was previously created. If that is not the case, you can run this script to create a fresh empty copy of the destination table.
use [SecurityTradingAnalytics]
go
create table [dbo].[ticker_date_prices](
[ticker] [varchar](10) NULL,
[date] [date] NULL,
[close] [decimal](10, 6) NULL
)
go
The csv file for each ticker consists of two columns, named date and price, respectively. Notice there is no column for the ticker in the csv files because each csv file holds date and price values for just one ticker, and the ticker string value is included in the csv filename. However, the following T-SQL script ultimately stores data from price values for all the tickers in a single SQL server table. The process for importing the file contents from each ticker’s csv file starts by importing the two columns for date and price to a truncated staging table named dbo.date_prices_stage. This staging table is created towards the top of the script.
The staging table is truncated just before a bulk insert statement that transfers the csv file contents to the staging table. After the staging table is freshly populated with a ticker’s csv file, a SQL Server insert statement transfers the contents of the staging table to the ultimate destination table (dbo.ticker_date_prices) across all tickers. By successively repeating this process for each ticker from QQQ, TQQQ, SPY, and SPXL, the destination table ends up containing data for all the tickers in the $tickers variable from the preceding PowerShell script.
use [SecurityTradingAnalytics];
go
-- ============================================
-- Drop and recreate staging table for raw input
-- ============================================
if object_id('dbo.date_prices_stage', 'U') is not null
drop table dbo.date_prices_stage;
go
create table dbo.date_prices_stage (
date_str varchar(50),
close_str varchar(50)
);
go
-- ============================================
-- Clear the target table before reloading
-- ============================================
truncate table dbo.ticker_date_prices;
-- ============================
-- Import and tag QQQ price data
-- ============================
bulk insert dbo.date_prices_stage
from 'C:\SecurityTradingAnalytics\UnadjustedPrices\QQQ-close.csv'
with (
firstrow = 2,
fieldterminator = ',',
rowterminator = '\n',
tablock,
codepage = '65001',
datafiletype = 'char'
);
insert into dbo.ticker_date_prices (ticker, [date], [close])
select
'QQQ',
try_convert(date, replace(date_str, '"', ''), 126),
convert(decimal(10,6), replace(close_str, '"', '')) raw_close
from dbo.date_prices_stage
where try_convert(date, replace(date_str, '"', ''), 126) is not null;
-- =============================
-- Import and tag TQQQ price data
-- =============================
truncate table dbo.date_prices_stage;
bulk insert dbo.date_prices_stage
from 'C:\SecurityTradingAnalytics\UnadjustedPrices\TQQQ-close.csv'
with (
firstrow = 2,
fieldterminator = ',',
rowterminator = '\n',
tablock,
codepage = '65001',
datafiletype = 'char'
);
insert into dbo.ticker_date_prices (ticker, [date], [close])
select
'TQQQ',
try_convert(date, replace(date_str, '"', ''), 126),
convert(decimal(10,6), replace(close_str, '"', '')) raw_close
from dbo.date_prices_stage
where try_convert(date, replace(date_str, '"', ''), 126) is not null;
-- ============================
-- Import and tag SPY price data
-- ============================
truncate table dbo.date_prices_stage;
bulk insert dbo.date_prices_stage
from 'C:\SecurityTradingAnalytics\UnadjustedPrices\SPY-close.csv'
with (
firstrow = 2,
fieldterminator = ',',
rowterminator = '\n',
tablock,
codepage = '65001',
datafiletype = 'char'
);
insert into dbo.ticker_date_prices (ticker, [date], [close])
select
'SPY',
try_convert(date, replace(date_str, '"', ''), 126),
convert(decimal(10,6), replace(close_str, '"', '')) raw_close
from dbo.date_prices_stage
where try_convert(date, replace(date_str, '"', ''), 126) is not null;
-- =============================
-- Import and tag SPXL price data
-- =============================
truncate table dbo.date_prices_stage;
bulk insert dbo.date_prices_stage
from 'C:\SecurityTradingAnalytics\UnadjustedPrices\SPXL-close.csv'
with (
firstrow = 2,
fieldterminator = ',',
rowterminator = '\n',
tablock,
codepage = '65001',
datafiletype = 'char'
);
insert into dbo.ticker_date_prices (ticker, [date], [close])
select
'SPXL',
try_convert(date, replace(date_str, '"', ''), 126),
convert(decimal(10,6), replace(close_str, '"', '')) raw_close
from dbo.date_prices_stage
where try_convert(date, replace(date_str, '"', ''), 126) is not null;
-- select * from dbo.ticker_date_prices
If you were to run the last select statement in the preceding script (the statement is commented out), it would appear in a long-format shape. Its shape is a long format because it has just three columns for ticker, date, and price, but 3,824 rows. There are 956 rows for each of the four tickers tracked in this post. This long table has just a small fraction of the total data that is available for the four tickers. This is because each ticker’s start data is arbitrarily started from the first trading date in 2022 (2022-01-03) through October 23, 2025. While the inception dates for different tickers are typically different, all four tickers tracked in this post have inception dates that are substantially earlier than 2022-01-03.
The following two screenshots show the first five trading days for the QQQ ticker and the last five trading days for the SPXL ticker in the dbo.ticker_date_prices table. In between the last trading days for the QQQ ticker and the first trading days for the SPXL ticker are date and raw prices data for the TQQQ and SPY tickers.
Calculating EMAs from Raw Prices
Sometimes the price for a ticker’s share price is increasing, which makes the ticker attractive to own. At other times, ticker prices can be flat or declining, which is a time when it is not profitable for a trader to own the shares for a ticker.
EMAs are a commonly used technique for tracking ticker prices in charts, but they can also be used for estimating when to buy and sell securities. EMA series have period lengths associated with them. EMAs with shorter period lengths follow their underlying values more closely than EMAs with longer period lengths. The underlying time series values move first, but EMA values move in synchrony with their underlying values.
This post section presents and describes a T-SQL script to compute EMAs with three different period lengths (5-day, 10-day, and 200-day EMAs). The sql script follows the guidelines from the StockChart.com site on how to compute EMAs along with standard sql coding conventions. The code is heavily commented for readers who care to study code and adapt it into their own solutions. Several points are particularly worthy of mention.
In order to make the script easy to follow and fast to code, neither loops nor common table expressions are used. Instead, it computes, 5-day, 10-day, and 200-day EMA value sets with straight-line code for each of the four tickers in this post.
The script successively adds three EMA series to the dbo.ticker_date_ema table in four sequential sections – one per ticker.
Within a ticker section, the code delays the start date for an EMA series by the number of days in its period length.
For example, the 5-day EMA series does not start until the fifth trading day in its raw price series.
In contrast, the code delays the start of the 10-day EMA until the tenth trading day.
Likewise, the script postpones the start of the 200-day EMA until the two-hundredth trading day.
The EMA value on the start date for each EMA series is computed as a simple moving average of the underlying series’ start date through the period corresponding to the EMA’s period length.
The EMA values for trading days after the start date are computed as a weighted average of the prior period’s EMA value and the current period’s underlying value. The weights for each term are dependent on an EMA’s period length.
The final select statement returns a long-format table with column values for ticker and date as well as the three EMA period-length values.
use [SecurityTradingAnalytics];
go
-- step 1.
truncate table dbo.ticker_date_ema
-- step 2.
declare @ticker varchar(10) = 'QQQ';
-- Step 2a: Ordered price series
with price_ordered as (
select
[date],
[close],
row_number() over (order by [date]) as rn
from dbo.ticker_date_prices
where ticker = @ticker
),
-- Step 2b: Seed EMA(5) at row 5
ema_5 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 4 preceding and current row) as decimal(10,6)) as ema_5
from price_ordered
where rn = 5
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 6.0)) + (r.ema_5 * (1 - (2.0 / 6.0))), 6) as decimal(10,6))
from price_ordered p
join ema_5 r on p.rn = r.rn + 1
),
-- Step 2c: Seed EMA(10) at row 10
ema_10 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 9 preceding and current row) as decimal(10,6)) as ema_10
from price_ordered
where rn = 10
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 11.0)) + (r.ema_10 * (1 - (2.0 / 11.0))), 6) as decimal(10,6))
from price_ordered p
join ema_10 r on p.rn = r.rn + 1
),
-- Step 2d: Seed EMA(200) at row 200
ema_200 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 199 preceding and current row) as decimal(10,6)) as ema_200
from price_ordered
where rn = 200
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 201.0)) + (r.ema_200 * (1 - (2.0 / 201.0))), 6) as decimal(10,6))
from price_ordered p
join ema_200 r on p.rn = r.rn + 1
)
-- Final merge and insert
insert into dbo.ticker_date_ema (ticker, [date], ema_5, ema_10, ema_200)
select
@ticker,
po.[date],
e5.ema_5,
e10.ema_10,
e200.ema_200
from price_ordered po
left join ema_5 e5 on po.rn = e5.rn
left join ema_10 e10 on po.rn = e10.rn
left join ema_200 e200 on po.rn = e200.rn
option (maxrecursion 0);
-----------------------------------------------------------------------------------------------------------------------
-- step 3.
set @ticker = 'TQQQ';
-- Step 3a: Ordered price series
with price_ordered as (
select
[date],
[close],
row_number() over (order by [date]) as rn
from dbo.ticker_date_prices
where ticker = @ticker
),
-- Step 3b: Seed EMA(5) at row 5
ema_5 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 4 preceding and current row) as decimal(10,6)) as ema_5
from price_ordered
where rn = 5
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 6.0)) + (r.ema_5 * (1 - (2.0 / 6.0))), 6) as decimal(10,6))
from price_ordered p
join ema_5 r on p.rn = r.rn + 1
),
-- Step 3c: Seed EMA(10) at row 10
ema_10 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 9 preceding and current row) as decimal(10,6)) as ema_10
from price_ordered
where rn = 10
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 11.0)) + (r.ema_10 * (1 - (2.0 / 11.0))), 6) as decimal(10,6))
from price_ordered p
join ema_10 r on p.rn = r.rn + 1
),
-- Step 3d: Seed EMA(200) at row 200
ema_200 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 199 preceding and current row) as decimal(10,6)) as ema_200
from price_ordered
where rn = 200
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 201.0)) + (r.ema_200 * (1 - (2.0 / 201.0))), 6) as decimal(10,6))
from price_ordered p
join ema_200 r on p.rn = r.rn + 1
)
-- Final merge and insert
insert into dbo.ticker_date_ema (ticker, [date], ema_5, ema_10, ema_200)
select
@ticker,
po.[date],
e5.ema_5,
e10.ema_10,
e200.ema_200
from price_ordered po
left join ema_5 e5 on po.rn = e5.rn
left join ema_10 e10 on po.rn = e10.rn
left join ema_200 e200 on po.rn = e200.rn
option (maxrecursion 0);
-----------------------------------------------------------------------------------------------------------------------
-- step 4.
set @ticker = 'SPY';
-- Step 4a: Ordered price series
with price_ordered as (
select
[date],
[close],
row_number() over (order by [date]) as rn
from dbo.ticker_date_prices
where ticker = @ticker
),
-- Step 4b: Seed EMA(5) at row 5
ema_5 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 4 preceding and current row) as decimal(10,6)) as ema_5
from price_ordered
where rn = 5
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 6.0)) + (r.ema_5 * (1 - (2.0 / 6.0))), 6) as decimal(10,6))
from price_ordered p
join ema_5 r on p.rn = r.rn + 1
),
-- Step 4c: Seed EMA(10) at row 10
ema_10 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 9 preceding and current row) as decimal(10,6)) as ema_10
from price_ordered
where rn = 10
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 11.0)) + (r.ema_10 * (1 - (2.0 / 11.0))), 6) as decimal(10,6))
from price_ordered p
join ema_10 r on p.rn = r.rn + 1
),
-- Step 3d: Seed EMA(200) at row 200
ema_200 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 199 preceding and current row) as decimal(10,6)) as ema_200
from price_ordered
where rn = 200
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 201.0)) + (r.ema_200 * (1 - (2.0 / 201.0))), 6) as decimal(10,6))
from price_ordered p
join ema_200 r on p.rn = r.rn + 1
)
-- Final merge and insert
insert into dbo.ticker_date_ema (ticker, [date], ema_5, ema_10, ema_200)
select
@ticker,
po.[date],
e5.ema_5,
e10.ema_10,
e200.ema_200
from price_ordered po
left join ema_5 e5 on po.rn = e5.rn
left join ema_10 e10 on po.rn = e10.rn
left join ema_200 e200 on po.rn = e200.rn
option (maxrecursion 0);
-----------------------------------------------------------------------------------------------------------------------
-- step 5.
set @ticker = 'SPXL';
-- Step 5a: Ordered price series
with price_ordered as (
select
[date],
[close],
row_number() over (order by [date]) as rn
from dbo.ticker_date_prices
where ticker = @ticker
),
-- Step 5b: Seed EMA(5) at row 5
ema_5 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 4 preceding and current row) as decimal(10,6)) as ema_5
from price_ordered
where rn = 5
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 6.0)) + (r.ema_5 * (1 - (2.0 / 6.0))), 6) as decimal(10,6))
from price_ordered p
join ema_5 r on p.rn = r.rn + 1
),
-- Step 5c: Seed EMA(10) at row 10
ema_10 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 9 preceding and current row) as decimal(10,6)) as ema_10
from price_ordered
where rn = 10
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 11.0)) + (r.ema_10 * (1 - (2.0 / 11.0))), 6) as decimal(10,6))
from price_ordered p
join ema_10 r on p.rn = r.rn + 1
),
-- Step 5d: Seed EMA(200) at row 200
ema_200 as (
select
rn, [date], [close],
cast(avg([close]) over (order by [date] rows between 199 preceding and current row) as decimal(10,6)) as ema_200
from price_ordered
where rn = 200
union all
select
p.rn, p.[date], p.[close],
cast(round((p.[close] * (2.0 / 201.0)) + (r.ema_200 * (1 - (2.0 / 201.0))), 6) as decimal(10,6))
from price_ordered p
join ema_200 r on p.rn = r.rn + 1
)
-- Final merge and insert
insert into dbo.ticker_date_ema (ticker, [date], ema_5, ema_10, ema_200)
select
@ticker,
po.[date],
e5.ema_5,
e10.ema_10,
e200.ema_200
from price_ordered po
left join ema_5 e5 on po.rn = e5.rn
left join ema_10 e10 on po.rn = e10.rn
left join ema_200 e200 on po.rn = e200.rn
option (maxrecursion 0);
-- optional display EMA values
-- select * from dbo.ticker_date_ema
The following screenshot shows the first ten rows for the three EMA value sets. The first four values of the ema_5 column are null. Likewise, the first nine values of the ema_10 column are null. In contrast to the previous two columns, all ten values for the ema_200 column are null.
The next screenshot highlights how the ema_200 contains non-null values starting in row 200 of the dbo.ticker_date_ema table.
The final screenshot in this section begins with the last five rows of EMA values for the QQQ ticker followed by the first five rows of EMA values for the TQQQ ticker. The only non-null EMA value for the TQQQ ticker is the one (144.070000) in the fifth row of the ema_5 column.
Sample Model Results
The following sql script implements the sql model in three steps.
Step 1 contains a base non-recursive common table expression that joins the dbo.ticker_date_prices table and the dbo.ticker_date_ema table created and populated within the previous post sections. Additionally, Step 1 contains a case statement for adding and populating buy_signal column values to the joined result set. Step 1 additionally returns an ordered result set by ticker and date column values.
Step 2 is for implementing the functionality of the own_status_recursive common table expression based on the non-recursive common table expression from Step 1. Within Step 2,
the anchor row adds own_status and sell_signal column values populated with zero to the result set returned from the base common table expression, and
the recursive step, updates own_status and sell_signal column values with a pair of case statements
Step 3 displays the output from the own_status_recursive common table expression.
use [SecurityTradingAnalytics];
go
-- Step 1: Base signals
with base as (
select
p.[ticker],
p.[date],
p.[close],
e.[ema_5],
e.[ema_10],
e.[ema_200],
-- buy_signal logic
case
when p.[close] > e.[ema_10]
and p.[close] > e.[ema_200]
and lag(p.[close], 1) over (
partition by p.[ticker] order by p.[date]
) <= e.[ema_10]
then 1 else 0
end as [buy_signal],
row_number() over (partition by p.[ticker] order by p.[date]) as rn
from dbo.ticker_date_prices p
join dbo.ticker_date_ema e
on p.ticker = e.ticker and p.date = e.date
),
-- Step 2: Recursive own_status tracker
own_status_recursive as (
-- Anchor row
select
b.*,
cast(0 as int) as own_status,
cast(0 as int) as sell_signal
from base b
where b.rn = 1
union all
-- Recursive step
select
b.*,
case
when r.own_status = 0 and b.buy_signal = 1 then 1
when r.own_status = 1 and (
b.[close] < b.[ema_5] or
b.[close] < b.[ema_10] or
b.[close] < b.[ema_200]
) then 0
else r.own_status
end as own_status,
case
when r.own_status = 1 and (
b.[close] < b.[ema_5] or
b.[close] < b.[ema_10] or
b.[close] < b.[ema_200]
) then 1
else 0
end as sell_signal
from base b
join own_status_recursive r
on b.ticker = r.ticker and b.rn = r.rn + 1
)
-- Step 3: Final output
select
ticker,
[date],
[close],
ema_5,
ema_10,
ema_200,
buy_signal,
own_status,
sell_signal
from own_status_recursive
order by ticker, [date]
option (maxrecursion 0);
The result set displayed by the code in Step 3 has four sets of rows for the tickers in this post with each set ordered by date. The full result set is a dataset that reveals the operation of the sql model created by the code in this post. The following excerpts show 20 rows each.
The first excerpt shows the top of the result set for the first ticker before any of its EMA value sets are calculated. This screenshot reveals the first twenty rows of the result set, and you can see that some rows have ema_5 and ema_10 column values, but none of the first twenty rows include non-null ema_200 values. Consequently, all the buy_signal, own_status, and sell_signal column values evaluate to zero for the rows in the excerpt.
The next screenshot reveals the first twenty rows with non-null ema_200 column values for the QQQ ticker. Additionally, rows 203 through 207 are highlighted to show the first buy-sell cycle for the QQQ ticker. The first row in the buy-sell cycle has a value of 1 in the buy_signal column for row 203. You can see from the date column that the buy-sell cycle begins on 2022-10-21 and ends on 2022-10-27. The close column reveals that the buy-sell cycle was not profitable because the close column value for 2022-10-21 was greater than the close column value for 2022-10-27. On the other hand, the close column values from 2022-10-24 through 2022-10-26 were all greater than the close column value for the beginning of the buy-sell cycle. If this pattern was supported by many buy-sell cycles, it would suggest that the sql model was better at detecting profitable start dates for buy-sell cycles than end dates.
The following screenshot shows the last twenty rows in the result set from the final select statement in the preceding query. The highlighted rows from row 3821 through row 3823 show the last complete buy-sell cycle for the TQQQ ticker, which is the last ticker value set available in the dataset. The last row again has a smaller close value (104.600000) than the first row in the final buy-sell cycle (107.890000). While the middle row in the final buy-sell cycle does not have a larger close value (107.770000) than the first row in the final buy-sell cycle, the middle row’s close value is noticeably more similar to the first close value in the final buy-sell cycle. This outcome again confirms the assertion that the sql model picks start close values more favorably than ending close values for a buy-sell cycle.
Concluding Comments
This post demonstrates how to create a dataset for a buy-sell model that is implemented in SQL Server based on raw close values from Tiingo. One or two follow-up posts will statistically examine the full final dataset created by the preceding section to uncover patterns in trade behavior — especially around sell timing — that may guide refinements to the model and inform future decisions about signal thresholds, including EMA period lengths. These future posts will also convey how to analyze a time series dataset of trades instead of just their underlying date-by-date close values. This kind of analysis is what security trading analytics is all about.
Comments
Post a Comment