ETF Price Puzzles Solved: Tiingo Updates Adjusted Prices for TQQQ and Beyond
This blog post is a follow-up to an earlier one (published November 30, 2025) titled “ETF Price Puzzles: Examples Showing Tiingo’s Raw and Adjusted Series Don’t Always Differ”. That prior post reported that adjusted prices from Tiingo for the TQQQ ticker did not follow the expected pattern for a 2-for-1 stock split on 2014-01-24. As a result, the prior post developed and demonstrated a T-SQL script for calculating custom adjusted price values based on Tiingo raw prices and stock-split ratios.
Several days before publishing the prior post on November 30, 2025, I alerted Tiingo about the aberrant adjusted close values. This gave Tiingo an opportunity to correct the issue for future downloads from the Tiingo site by other analysts and stock traders.
This post, which is published 2 to 3 weeks after the earlier post, expands the scope of the examination to cover more tickers and more stock splits. To verify how widespread the issue reported in the prior post was, this post examines Tiingo adjusted price performance for four tickers (SPY, SPXL, QQQ, and TQQQ) since the earliest trading day shared by all four tickers through the final day of trading shared by all four tickers. Custom adjusted prices were also calculated based on Tiingo raw prices and stock split ratios for all stock splits since the inception date for each ticker.
Before I dive into the details, I want to confirm that Tiingo did update its adjusted prices between when I collected prices for the earlier post and when I started collecting Tiingo adjusted price for this post. For me at least, I was very excited by the performance of the updated Tiingo adjusted prices. I have a feeling you may be pleased as well. Please read on to find out why.
Comparing TQQQ Performance Before and After Updated TQQQ Adjusted Prices
The following screenshot displays from the preceding post raw and adjusted TQQQ price values based on downloads from Tiingo.com. The tradedate values begin on 2014-01-16 and end on 2014-01-30. Notice the discontinuity in prices between 2014-01-23 and 2014-01-24.
I initially checked historical market data to discover if the discontinuity was driven by some news or market action initiatives on the part of investors and traders. However, that search did not lead to any plausible explanation for the discontinuity.
Next, I compared price discontinuities with historical records for stock splits. For these comparisons there was a strong and consistent pattern between stock splits and price discontinuities. There was a 2-for-1 forward stock split for TQQQ shares on January 24, 2014. Notice that both rawclose and adjustedclose column values dropped by about fifty percent on January 24 relative to the preceding trading date, which is generally consistent with the outcome of a 2-for-1 stock split for rawclose values, but not for adjusted close values. These price declines are puzzling because the TQQQ sponsor did not actually decrease the market value of aggregate TQQQ shares. Instead, the share price was cut in half to offset the doubling of share volume. As a result, the adjusted share price should not have declined by about 50 percent.
The next screenshot shows the outcome from the T-SQL script for computing custom adjusted close values based the T-SQL script from the prior post and the Tiingo rawclose values from the download for this post. As you can see, the rawclose value for the day of the split (2014-01-24) is about forty-seven percent as large as rawclose value on the day before the split (2014-01-23). In contrast, the custom_adjusted_close value on the day of the split is nearly ninety-four percent as large as the custom adjusted close value on the day before the split. In other words, the split did not materially affect the custom close value on the split date. This is the expected outcome because while the split cut the rawclose value in half, it also doubled the number of shares. The change in custom adjusted close value generally reflects the change in the stock’s market value between the day before the split and the day of the split.
Generating Close Performance Metrics Across Three Metrics
The discrepancies discovered and reported on in the prior post motivated this more in-depth examination and comparison of the three types of close values for four tickers (SPY, SPXL, QQQ, TQQQ). The tickers were specifically chosen to enable meaningful comparisons across the four tickers and the three metrics. This post section drills down on the steps for readying the three data streams for comparisons. It is my hope that programmatically oriented analysts and traders will find this section of value for crafting their own ticker analysis projects.
Downloading Data from Tiingo
The first step is to download raw prices and adjusted prices for the four tickers from the Tiingo.com site. This blog addressed this topic in a couple of other prior posts. The Tiingo Mini-Tutorial on Downloading Historical Prices section from the “Comparing Four Data Providers for Six Tickers Over Five Years” post introduces Tiingo to followers of this blog, including how to get your own free API key for downloading stock market data from Tiingo. Tiingo is widely recognized for its reliable API, broad coverage, and consistent delivery of high‑quality historical end‑of‑day stock market data.
The following PowerShell script file shows the sample code for downloading historical data for the four tickers tracked in this post. I have found it very helpful to work with Microsoft Copilot when designing PowerShell scripts for Tiingo.
As you can see, the script has five main steps.
Step 1 allows you to define your Tiingo API key for running the script on your computer.
Step 2 designates folders on your computer for downloading end‑of‑day stock market data from Tiingo.
Step 3 permits you to designate inception dates for each of the tickers tracked in this post.
Step 4 is a PowerShell function for downloading and saving data.
Step 5 loops through the tickers and invokes the function in step 4.
# Download ETF data (raw and adjusted closes) from Tiingo
# Save as Download-ETFData.ps1 and run in PowerShell
# Step 1: Define API token
$token = "Your Tiingo API Key" # Replace with your actual token
# Step 2: Define base folders
$rawFolder = "C:\SecurityTradingAnalytics\Four MM ETF Raw Closes"
$adjustedFolder = "C:\SecurityTradingAnalytics\Four MM ETF Adjusted Closes"
# Ensure folders exist and clean old CSVs
foreach ($folder in @($rawFolder, $adjustedFolder)) {
if (!(Test-Path $folder)) { New-Item -ItemType Directory -Path $folder }
Get-ChildItem -Path $folder -Filter *.csv | Remove-Item -Force
}
# Step 3: Define tickers and inception dates
$tickers = @{
"SPY" = "1993-01-29"
"SPXL" = "2009-11-05"
"QQQ" = "1999-03-10"
"TQQQ" = "2010-02-11"
}
# Step 4: Function to download and save data
function Download-ETFData {
param (
[string]$ticker,
[string]$startDate,
[string]$folderPath,
[string]$priceField
)
$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
$longFormat = $data | Select-Object @{Name="date";Expression={$_.date}}, @{Name="price";Expression={$_.$priceField}}
$outputPath = "$folderPath\$ticker-close.csv"
$longFormat | Export-Csv $outputPath -NoTypeInformation
Write-Host "✅ Success: Saved $ticker ($priceField) to $outputPath"
}
catch {
$errorMessage = "❌ Failed to download data for $ticker on $(Get-Date)"
Write-Warning $errorMessage
Add-Content "$folderPath\error_log.txt" $errorMessage
}
}
# Step 5: Loop through tickers for both raw and adjusted
foreach ($ticker in $tickers.Keys) {
$startDate = $tickers[$ticker]
# Raw closes
Download-ETFData -ticker $ticker -startDate $startDate -folderPath $rawFolder -priceField "close"
# Adjusted closes
Download-ETFData -ticker $ticker -startDate $startDate -folderPath $adjustedFolder -priceField "adjClose"
}
Here is an excerpt for the first six lines of a csv file downloaded from Tiingo for the QQQ ticker. The Powershell script directs the full csv file to the C:\SecurityTradingAnalytics\Four MM ETF Raw Closes target folder path on the computer running the script as well as three more csv files to the same folder path. Additionally, four more csv files for adjusted close values from Tiingo are sent to the C:\SecurityTradingAnalytics\Four MM ETF Adjusted Closes folder path.
The raw market price of a stock is the actual market price at the close of a specific trading day. The adjusted price incorporates the impact of corporate actions to provide a continuous and accurate picture of a stock's historical performance and value.
Storing Raw and Adjusted Prices in a SQL Server Table
The following T-SQL script excerpts walk you through the main steps for migrating the csv files from the preceding subsection into a SQL Server table.
Here are excerpts from the first section of a script for creating a SQL Server table named dbo.ticker_prices_full. At the end of this script segment, the table will be ready to store from the downloaded csv files the raw close and Tiingo adjusted close values.
use [SecurityTradingAnalytics];
go
-- always run script straight through from top to bottom
drop table if exists dbo.ticker_prices_full;
create table dbo.ticker_prices_full (
ticker varchar(10) not null,
[date] date not null,
SPXL_rawclose decimal(18,6) null,
tiingo_SPXL_adjusted decimal(18,6) null,
custom_SPXL_adjusted decimal(18,6) null,
SPY_rawclose decimal(18,6) null,
tiingo_SPY_adjusted decimal(18,6) null,
custom_SPY_adjusted decimal(18,6) null,
constraint pk_ticker_prices_full primary key (ticker, [date])
);
go
Here is an excerpt from the code for copying the downloaded csv files for the raw and adjusted files to the dbo.ticker_prices_full table. When working with downloaded csv files for end-of-day data, you will typically use a two-step process.
The first step in this case populates via a bulk insert statement an empty version of the dbo.date_prices_stage table with the contents of the SPXL-close.csv file from the C:\SecurityTradingAnalytics\Four MM ETF Raw Closes folder path (see steps 1 and 2 below).
The second step extracts, transforms, and loads the contents of the staging table (dbo.date_prices_stage) into the dbo.ticker_prices_full table (see step 3 below).
The two-step process is repeated for any other csv files that are eligible to be copied to the permanent table (dbo.ticker_prices_full). In this case there are three other files that can be copied into the permanent table:
SPXL-close.csv from the C:\SecurityTradingAnalytics\Four MM ETF Adjusted Closes folder path,
SPY-close.csv from the C:\SecurityTradingAnalytics\Four MM ETF Raw Closes folder path, and
SPY-close.csv from the C:\SecurityTradingAnalytics\Four MM ETF Adjusted Closes folder path.
-- Populate SPXL Tiingo raw and adjusted columns
-- in dbo.ticker_prices_full
-- Step 1: clear staging
truncate table dbo.date_prices_stage;
-- Step 2: bulk insert raw SPXL closes
begin try
bulk insert dbo.date_prices_stage
from 'C:\SecurityTradingAnalytics\Four MM ETF Raw Closes\SPXL-close.csv'
with (
fieldterminator = ',',
rowterminator = '\n',
firstrow = 2,
datafiletype = 'char'
);
insert into dbo.ingestion_log (ticker, status)
values ('SPXL', 'Raw Bulk Success');
end try
begin catch
insert into dbo.ingestion_log (ticker, status, error_message)
values ('SPXL', 'Raw Bulk Failed', error_message());
end catch;
-- Step 3: move into ticker_prices_full
insert into dbo.ticker_prices_full (ticker, [date], SPXL_rawclose)
select 'SPXL',
try_convert(date, left(replace(date_str, '"', ''), 10), 126),
convert(decimal(18,6), replace(close_str, '"', ''))
from dbo.date_prices_stage;
----------------------------------------------------------------------------
-- Step 1b: clear staging again
truncate table dbo.date_prices_stage;
-- Step 2b: bulk insert Tingo adjusted SPXL closes
begin try
bulk insert dbo.date_prices_stage
from 'C:\SecurityTradingAnalytics\Four MM ETF Adjusted Closes\SPXL-close.csv'
with (
fieldterminator = ',',
rowterminator = '\n',
firstrow = 2,
datafiletype = 'char'
);
insert into dbo.ingestion_log (ticker, status)
values ('SPXL', 'Adjusted Bulk Success');
end try
begin catch
insert into dbo.ingestion_log (ticker, status, error_message)
values ('SPXL', 'Adjusted Bulk Failed', error_message());
end catch;
-- Step 3b: move adjusted closes into ticker_prices_full
update t
set tiingo_spxl_adjusted = convert(decimal(18,6), replace(s.close_str, '"', ''))
from dbo.ticker_prices_full t
join dbo.date_prices_stage s
on t.[date] = try_convert(date, left(replace(s.date_str, '"', ''), 10), 126)
where t.ticker = 'SPXL';
After completing the two-step process four times, the dbo.ticker_prices_full table is populated with eight columns – four for SPXL and four more for SPY. The following screenshot displays the first five rows for SPXL over the first five rows SPY.
Populating the dbo.ticker_prices_full Table
To meet the requirements of the current post, we need to populate the dbo.ticker_prices_full table for two more tickers (TQQQ and QQQ). This can be achieved by altering dbo.ticker_prices_full to add new columns for the remaining tickers that need to be processed. The following T-SQL code segment shows how to achieve this goal with the add operator within an alter table statement.
Three more columns are added to the table for each new ticker: one for rawclose values, another for Tiingo adjust values, and another for custom adjusted values.
The custom adjusted columns are not used in this table. Because of the special processing requirements for custom adjusted columns, they are populated in a subsequent table that incorporates column values from dbo.ticker_prices_full.
-- alter dbo.ticker_prices_full table with
-- columns for TQQQ and QQQ tickers
alter table dbo.ticker_prices_full
add TQQQ_rawclose decimal(18,6) null,
tiingo_TQQQ_adjusted decimal(18,6) null,
custom_TQQQ_adjusted decimal(18,6) null,
QQQ_rawclose decimal(18,6) null,
tiingo_QQQ_adjusted decimal(18,6) null,
custom_QQQ_adjusted decimal(18,6) null;
go
Next, the code for populating dbo.ticker_prices_full for the first pair of tickers is adapted for populating a second pair of tickers. The following screenshot shows the first five rows for each ticker in dbo.ticker_prices_full. Note that the first trading dates for the four tickers are years apart. This is a consequence of the four tickers being launched at different points in time.
SPY has the earliest trading date (1993-01-29) which is well before the dot-com bubble.
TQQQ has the most recent trading date (2010-02-11), which is after the 2008 financial crisis that served as a catalyst for the Great Recession.
These substantially different starting dates create a need for choosing a common shared beginning date when comparing overall growth across tickers. The next post section introduces code to automate the achieving of this objective.
Custom Adjusted Price Calculations From this Blog
The method used in this blog to calculate custom adjusted price values differs from Tiingo’s standard adjustments. Unlike Tiingo, which follows the Center for Research in Security Prices (CRSP) methodology and incorporates dividends, splits, and other corporate actions, the custom adjusted values here reflect only stock splits. This narrower approach is useful for isolating split-adjusted prices from Tiingo’s more comprehensive adjustments that also account for dividends and other shareholder returns.
The following T-SQL script shows a collection of historical splits for the tickers tracked in this analysis. Tracking splits can sometimes lead to discrepancies between data providers and other public sources about adjusted security prices. As a consequence, the data in the script was cross-checked against some widely referenced data sources (Microsoft Copilot, Google Gemini, and Google Search). Other sources not checked for this blog post include SEC filings (8-K or 10-Q) and Center for Research in Security Prices (CRSP). When applying the code in this script for your own analyses choose sources that are meaningful for your target audience.
use SecurityTradingAnalytics
go
-- Create a fresh copy of the splits table
drop table if exists dbo.splits;
go
create table dbo.splits (
Ticker varchar(10),
SplitDate date,
SplitRatio varchar(10), -- e.g. '2-for-1', '1-for-3'
Factor decimal(18,4) -- numeric multiplier
);
-- populate the splits table
insert into dbo.splits (Ticker, SplitDate, SplitRatio, Factor) values
-- QQQ
('QQQ', '2000-03-20', '2-for-1', 0.5),
-- TQQQ
('TQQQ', '2011-02-25', '2-for-1', 0.5),
('TQQQ', '2012-05-11', '2-for-1', 0.5),
('TQQQ', '2014-01-24', '2-for-1', 0.5),
('TQQQ', '2017-01-12', '2-for-1', 0.5),
('TQQQ', '2018-05-24', '3-for-1', 0.3333),
('TQQQ', '2021-01-21', '2-for-1', 0.5),
('TQQQ', '2022-01-13', '2-for-1', 0.5),
('TQQQ', '2025-11-20', '2-for-1', 0.5),
-- SPY
-- No splits, so no rows needed
-- SPXL
('SPXL', '2013-04-02', '3-for-1', 0.3333),
('SPXL', '2017-05-01', '4-for-1', 0.25);
-- optionally display dbo.splits
select * from dbo.splits;
Here is the result set from the preceding script. For the four tickers tracked in this post, the eleven splits listed in the result set are their inception date through the download date for Tiingo raw prices as of November 28, 2025. The Split Date column denotes the date of each script. The Split Ratio column values (e.g., '2-for-1') indicate the new share volume (2) for every old share volume (1). This ratio is used to determine the necessary price adjustment. The Factor column values convert the split ratio into the numeric basis for adjustment. This factor is the multiplicative constant applied to all historical raw prices preceding the split date to ensure the price series is continuous and accurately reflects total returns.
Custom adjusted prices in this post are derived from the dbo.splits table and original raw price values that populate the SPXLraw_close, SPYraw_close, TQQQraw_close, and QQQraw_close columns in the dbo.ticker_prices_full table. For computational convenience, designated columns from dbo.ticker_prices_full are reconfigured in a tall format table named dbo.ticker_date_prices before any custom adjusted prices are calculated. At a conceptual level, the Factor column values from dbo.splits are multiplied by all raw price values from dbo.ticker_date_prices in rows preceding each split to calculate custom adjusted prices.
The following T-SQL script shows code for migrating values from the wide format dbo.ticker_prices_full table to the long format dbo.ticker_date_prices table.
-- truncate and populate a fresh dbo.ticker_date_prices table
truncate table dbo.ticker_date_prices
-- populate dbo.ticker_date_prices from dbo.ticker_prices_full
insert into dbo.ticker_date_prices
select *
from
(
-- create temp table (#temp.date_prices) for SPXL
select [ticker]
,[date]
,[SPXL_rawclose] rawclose
-- ,tiingo_SPXL_adjusted
from dbo.ticker_prices_full
where SPXL_rawclose is not null
union
select [ticker]
,[date]
,[SPY_rawclose] rawclose
-- ,tiingo_SPY_adjusted
from dbo.ticker_prices_full
where SPY_rawclose is not null
union
select [ticker]
,[date]
,[TQQQ_rawclose] as rawclose
-- ,tiingo_TQQQ_adjusted
from dbo.ticker_prices_full
where TQQQ_rawclose is not null
union
select [ticker]
,[date]
,[QQQ_rawclose] as rawclose
-- ,tiingo_QQQ_adjusted
from dbo.ticker_prices_full
where QQQ_rawclose is not null
) for_dbo_ticker_date_prices;
-- optionally display dbo.ticker_date_prices
select * from dbo.ticker_date_prices
The next script shows the T-SQL code for transforming raw prices to custom adjusted prices. This SQL rebuilds a table called dbo.ticker_date_custom_adjusted_close to store custom-adjusted closing prices for stock tickers. Principal actions performed by the script include
Dropping the table if it already exists,
Recreating it with a primary key on (ticker, date),
Calculating adjusted closing prices by applying cumulative split factors to raw closing prices,
Inserting those adjusted values into the new table, and
Optionally displays the table contents at the end.
The cumulative Common Table Expression (CTE) and its trailing insert into…select statement merit special commentary since this code segment creates a result set with the custom adjusted close values and saves them in dbo.ticker_date_custom_adjusted_close. The actions performed by this code segment
Pulls ticker, date, and raw close price from dbo.ticker_date_prices,
Adjusts the close price by multiplying it with the exponential of the sum of log(factors) for splits occurring after the given date,
Ensures no adjustment if no splits exist,
The CTE’s math effectively applies cumulative split adjustments from the least recent split date for a ticker through the most recent split; the earlier the date, the more adjustment cycles the custom adjusted price because earlier custom adjusted prices have more adjustments.
-- create a fresh copy of dbo.ticker_date_custom_adjusted_close
-- drop the table if it exists
if object_id('dbo.ticker_date_custom_adjusted_close', 'U') is not null
drop table dbo.ticker_date_custom_adjusted_close;
go
-- create a fresh copy of dbo.ticker_date_custom_adjusted_close
create table dbo.ticker_date_custom_adjusted_close(
[ticker] [varchar](10) NOT NULL,
[date] [date] NOT NULL,
[custom_adjusted_close] [decimal](10, 6) NULL,
constraint [pk_ticker_date_custom_adjusted_close] primary key clustered
(
[ticker] ASC,
[date] ASC
)
);
-- return custom adjusted close values for all tickers
with cumulative as (
select
p.ticker,
p.[date],
p.[close],
p.[close] * exp(
isnull(
(
select sum(log(s.factor))
from dbo.splits s
where s.ticker = p.ticker
and s.splitdate > p.[date]
), 0
)
) as adjustedclose
from dbo.ticker_date_prices p
)
insert into dbo.ticker_date_custom_adjusted_close (ticker, [date], custom_adjusted_close)
select
ticker,
[date],
adjustedclose
from cumulative;
-- optionally display table contents
select * from dbo.ticker_date_custom_adjusted_close;
Consolidating All Three Metrics
The next-to-final script for this section creates and populates the dbo.ticker_date_tiingo_adjusted_prices table. This table has the same tall format as the dbo.ticker_date_prices and dbo.ticker_date_custom_adjusted_close tables. The objective for creating and populating this third table is to have available a set of three tables that can be joined by ticker and date into a single table (dbo.ticker_date_consolidated_prices) for a comparative analysis of all three metrics (Tiingo Raw Prices, Tiingo Adjusted Prices, Custom Adjusted Close value) across all four tickers.
-- create a fresh copy of dbo.ticker_date_tiingo_adjusted_prices
-- drop the table if it exists
if object_id('dbo.ticker_date_tiingo_adjusted_prices', 'U') is not null
drop table dbo.ticker_date_tiingo_adjusted_prices;
go
-- create the fresh table
create table dbo.ticker_date_tiingo_adjusted_prices (
ticker varchar(10) not null,
[date] date not null,
tiingo_adjusted_price decimal(10,6) null,
constraint pk_ticker_date_tiingo_adjusted_prices primary key clustered (ticker asc, [date] asc)
);
-- populate from dbo.ticker_prices_full
insert into dbo.ticker_date_tiingo_adjusted_prices (ticker, [date], tiingo_adjusted_price)
select ticker, [date], tiingo_SPXL_adjusted
from dbo.ticker_prices_full
where tiingo_SPXL_adjusted is not null
union all
select ticker, [date], tiingo_SPY_adjusted
from dbo.ticker_prices_full
where tiingo_SPY_adjusted is not null
union all
select ticker, [date], tiingo_TQQQ_adjusted
from dbo.ticker_prices_full
where tiingo_TQQQ_adjusted is not null
union all
select ticker, [date], tiingo_QQQ_adjusted
from dbo.ticker_prices_full
where tiingo_QQQ_adjusted is not null;
-- optionally display table contents
select * from dbo.ticker_date_tiingo_adjusted_prices;
Here is the code for creating and populating the dbo.ticker_date_consolidated_prices table. This table will be analyzed in the next section to reveal how the metrics compare versus each other and how ticker performance varies across tickers.
-- drop the consolidated table if it exists
if object_id('dbo.ticker_date_consolidated_prices', 'U') is not null
drop table dbo.ticker_date_consolidated_prices;
go
-- create the consolidated table
create table dbo.ticker_date_consolidated_prices (
ticker varchar(10) not null,
[date] date not null,
[close] decimal(10,6) null,
tiingo_adjusted_price decimal(10,6) null,
custom_adjusted_close decimal(10,6) null,
constraint pk_ticker_date_consolidated_prices primary key clustered (ticker asc, [date] asc)
);
-- populate the consolidated table by joining the three source tables
insert into dbo.ticker_date_consolidated_prices (ticker, [date], [close], tiingo_adjusted_price, custom_adjusted_close)
select
raw.ticker,
raw.[date],
raw.[close],
tiingo.tiingo_adjusted_price,
custom.custom_adjusted_close
from dbo.ticker_date_prices raw
inner join dbo.ticker_date_tiingo_adjusted_prices tiingo
on raw.ticker = tiingo.ticker
and raw.[date] = tiingo.[date]
inner join dbo.ticker_date_custom_adjusted_close custom
on raw.ticker = custom.ticker
and raw.[date] = custom.[date];
Comparing the Three Performance Metrics Across Four Tickers
This post’s final step is about the comparison of the three performance metrics across four tickers. Recall that the four tickers have different inception dates that span a period of about seventeen years. As a consequence, comparisons across tickers can yield different results just based on the time span differences between them and not the characteristics of the metrics nor the types of tickers being tracked. To avoid this potential time span impact, comparisons started from the least recent start date through the most recent end date across all four tickers. The most recent end date can also vary across tickers even when ticker prices are collected from a data provider on the same date. This is because not all tickers may have their prices updated at the same time for the current date on the download date from a data provider, such as Tiingo. At the most, the difference between ticker end dates is likely to be just one day, but if a seismic move occurs on the end date, it will be important to ensure that the end date is the same for all tickers.
Because of the importance of the empirical results in this section, this section begins with the presentation and discussion of SQL Server results set that compares the three metrics across four tickers. The following excerpt from SQL Server Management Studio (SMS) shows a result set that returns percent change between shared start date and shared end date for each ticker. For formatting reasons, the titles for the last two columns was modified from tiingo_adjusted_percent_change and custom_adjusted_percent_change, respectively, to tiingo_percent_change and custom_percent_change.
The tiingo_percent_change column values are larger than their corresponding raw_close_percent_change column values. Recall that Tiingo adjusted column values adjust Tiingo raw column values for splits, dividends, and other capital distributions. Therefore, it is reasonable that Tiingo adjusted close values be greater than Tiingo raw close values.
The custom_percent_change column values are larger than their corresponding ticker raw_close_percent_change column values for the SPXL and TQQQ tickers. Additionally, for the SPY and QQQ tickers the tiingo_percent_change column values perfectly match the raw_close_percent_change column values. These two types of outcomes are reasonable.
For the SPY ticker, there are no splits for which to adjust. As a result, there are no percent change differences for SPY between raw prices and custom adjusted prices.
For the QQQ ticker, there is just one split, but it occurs about ten years before the first shared start date. Consequently, there’s plenty of time for the custom adjustment impact to diminish to zero, which appears to be what happened.
For SPXL and TQQQ tickers, there are multiple splits that occur after the shared start date for assessing ticker price performance. However, there are more splits for TQQQ than for SPXL. In a corresponding manner, both custom_percent_change column values are greater for these two tickers than the raw_close_percent_change column values. Also, the relative advantage of the TQQQ ticker is much greater than for the SPXL ticker.
The last set of percent change column values is for tiingo_percent_change column values versus custom_percent_change column values. Recall that tiingo_percent_change column values reflect impacts for splits, dividends, and other capital distributions while custom_percent_change column values just reflect the impacts of splits. For every comparison, the tiingo_percent_change column values are greater than their corresponding custom percent column values.
Finally, over the time span from the shared start date through the shared end date. Both of the tickers for leveraged ETFs (TQQQ and SPXL) have substantially greater than percent change values than the tickers for unleveraged ETFs (QQQ and SPY).
Collectively, these comparative findings suggest answers to several important questions about ETF prices, such as
Why do raw prices underperform Tiingo adjusted prices?
Why do Tiingo adjusted prices outperform custom adjusted prices?
Why do leveraged ETFs, such as TQQQ and SPXL, outperform their unleveraged counterparts, such as QQQ and SPY?
Next, this section switches its focus from a discussion of the results to the trailing T-SQL script that calculates the percent change for each of three different price metrics ( [close], tiingo_adjusted_price, and custom_adjusted_close) for each of four tickers ( SPY, SPXL, QQQ, and TQQQ). A set of five sequential CTEs creates a base data source for the percent change calculations. There are two fundamental relationships in the sequence of five CTEs.
First, target data --> earliest_dates_per_ticker --> earliest_shared_date_info to find the maximum date among those four minimum dates. This maximum date is the date when the last of the four tickers became available, thus establishing the single shared_start_date.
Second, target data --> latest_dates_per_ticker --> latest_shared_date_info to find the minimum date among those four maximum dates. This minimum date is the date when the first of the four tickers' data ends, thus establishing the single shared_end_date.
The trailing select statement after the five sequenced CTEs extract and calculate the column values in the result set discussed above. The data source for the select statement is designated in its from clause.
The from clause and join operators within it are responsible for creating the four final rows of data—one for each ticker—containing all the necessary start and end prices for the percent change calculations.
Step 1: Establish the Shared Start Date
Source: from earliest_shared_date_info esd
Result: A single-row table containing one column: esd.shared_start_date (which is 2010-02-11).
Step 2: Add the Shared End Date
Action: cross join latest_shared_date_info lsd
Result: Still a single-row table, but now containing two columns: esd.shared_start_date and lsd.shared_end_date (which is 2025-11-28). This establishes the analysis window.
Step 3: Attach the Start Prices
Action: inner join target_data td_start on td_start.date = esd.shared_start_date
Result: The query now expands to four rows (one for each ticker: SPY, SPXL, QQQ, TQQQ). Each row contains the start date, the end date, and all the price columns ([close], tiingo_adjusted_price, custom_adjusted_close) for that ticker on the start date.
Step 4: Attach the End Prices
Action: inner join target_data td_end on td_end.date = lsd.shared_end_date and td_start.ticker = td_end.ticker
Result: The final four rows are complete. This join is crucial because it ensures:
We pull prices only on the shared_end_date.
The td_end values are matched correctly to the corresponding td_start values based on the ticker name.
Once these four rows are constructed, the final select statement can access the fields (e.g., td_start.[close], td_end.tiingo_adjusted_price) to perform the percentage calculations. The order by clause determines the order for displaying rows. The select list items (such as td_start.ticker, raw_close_percent_change, tiingo_percent_change, and custom_percent_change) then finalize the column values for the result set.
/*
-- Calculates the total change and percent change for [close], tiingo_adjusted_price,
-- and custom_adjusted_close across SPY, SPXL, QQQ, and TQQQ.
-- The analysis is conducted over the maximum shared historical period:
-- from the latest common start date to the earliest common end date.
*/
with target_data as (
select
ticker,
date,
[close],
tiingo_adjusted_price,
custom_adjusted_close
from
SecurityTradingAnalytics.dbo.ticker_date_consolidated_prices
where
ticker in ('SPY', 'SPXL', 'QQQ', 'TQQQ')
),
earliest_dates_per_ticker as (
select ticker, min(date) as min_date
from target_data
group by ticker
),
earliest_shared_date_info as (
select max(min_date) as shared_start_date
from earliest_dates_per_ticker
),
latest_dates_per_ticker as (
select ticker, max(date) as max_date
from target_data
group by ticker
),
latest_shared_date_info as (
select min(max_date) as shared_end_date
from latest_dates_per_ticker
)
select
td_start.ticker,
esd.shared_start_date,
lsd.shared_end_date,
-- Raw Close Calculations
cast(floor((td_end.[close] - td_start.[close]) * 100.0 / td_start.[close] * 100.0) / 100.0 as decimal(10,2)) as raw_close_percent_change,
-- Tiingo Adjusted Price Calculations
cast(floor((td_end.tiingo_adjusted_price - td_start.tiingo_adjusted_price) * 100.0 / td_start.tiingo_adjusted_price * 100.0) / 100.0 as decimal(10,2)) as tiingo_percent_change,
-- Custom Adjusted Close Calculations
cast(floor((td_end.custom_adjusted_close - td_start.custom_adjusted_close) * 100.0 / td_start.custom_adjusted_close * 100.0) / 100.0 as decimal(10,2)) as custom_percent_change
from earliest_shared_date_info esd
cross join latest_shared_date_info lsd
inner join target_data td_start on td_start.date = esd.shared_start_date
inner join target_data td_end on td_end.date = lsd.shared_end_date and td_start.ticker = td_end.ticker
order by
case td_start.ticker
when 'SPY' then 1
when 'SPXL' then 2
when 'QQQ' then 3
when 'TQQQ' then 4
end;
Concluding Comments
The in-depth analysis of raw, Tiingo-adjusted, and custom-split-adjusted prices for SPY, SPXL, QQQ, and TQQQ demonstrates crucial differences in how various metrics represent security performance. A primary takeaway is the impact of corporate actions, particularly splits and dividends, on total return calculations. Our initial investigation spurred Tiingo to successfully update its adjusted price series, highlighting the value of independent scrutiny in financial data analysis. The subsequent comparative analysis over the shared 2010-02-11 to 2025-11-28 period confirms that Tiingo's adjusted prices consistently yield the highest percent change values across all four tickers. This outcome is reasonable, as Tiingo’s methodology (CRSP-based) incorporates dividends and other capital distributions, whereas the raw prices and our custom split-adjusted prices do not, explaining the performance gaps observed.
Furthermore, the results provide compelling evidence of the long-term potential and volatility associated with leveraged ETFs. Both TQQQ and SPXL, despite the inherent risks, generated substantially greater returns over the analyzed period than their unleveraged counterparts, QQQ and SPY. This exercise provides a reusable framework for analysts, demonstrating how to isolate and compare different price adjustment methods using T-SQL. By creating a custom split-adjusted series, we've provided a middle-ground metric useful for separating the effect of splits from the combined effect of splits and dividends. Ultimately, this work reaffirms that for accurate total return analysis, relying on a fully-adjusted price series—such as the now-updated Tiingo series—is essential for capturing the full picture of an ETF's historical performance.
Comments
Post a Comment