ETF Price Puzzles: Examples Showing Tiingo’s Raw and Adjusted Series Don’t Always Differ

As I was preparing an analysis of a buy‑sell model for several major ETFs, I stumbled across some unexpected puzzles in the price data.  Tiingo provides both raw and adjusted price series, but for certain ETFs the two are identical — even across known split dates.

This post explains how I discovered these puzzles in selected ETF ticker prices from Tiingo.  The post also describes why the puzzles matter, and how you can adjust for them when performing ETF analyses with Tiingo data.

Where I Discovered Tiingo ETF Price Puzzles

I was analyzing a model based on EMA proper orders for prices when I first noticed ETF price puzzles.  Any ETF price series can have multiple EMAs depending on their period lengths.  No matter what the period length for an EMA, it is always dependent on its underlying price data.  For any trading date, EMAs with shorter period lengths that have higher price values than EMAs with longer period lengths are in EMA proper order.

The raw prices for a security, such as an ETF, are generally based on the demand for the security among traders and investors.  When the demand declines, the raw ETF price declines.  Conversely, when the demand increases, the raw ETF price increases.

Corporate actions, such as when an ETF sponsor initiates a stock split, can also cause raw ETF prices to change.  For example, a 2-for-1 forward stock split means the number of shares doubles for each shareholder.  While the number of shares doubles, the raw price drops by 50% on the day of the stock split.  To compensate for this halving on the day of the split and additional days going forward, the historical price values before the split are also decreased by 50%.  The modified historical prices are referred to as adjusted prices.  To properly compare prices before and after a split (or EMAs before and after a split) you need to use adjusted prices instead of raw prices before and after the split.

A reverse split works in the opposite direction of a forward split. Instead of increasing the number of shares and lowering the price, the ETF sponsor reduces the number of shares outstanding while proportionally increasing the raw price. For example, in a 1‑for‑3 reverse split, every three shares are consolidated into one, and the raw price triples on the effective date. Just as with forward splits, the historical series is adjusted so that pre‑split prices reflect the new share count, ensuring continuity across the time series. Whether the split is forward or reverse, the adjusted series is designed to remove the artificial jumps (sometimes called price discontinuities) caused by corporate actions, allowing analysts to focus on genuine price movements driven by market demand.

No matter what buy-sell model or trading strategy you are examining, you do not want stock splits disrupting the continuity of prices over time.  Instead, you want adjusted price series that are based on trader and investor demand for shares.

When I was examining model performance for TQQQ adjusted price data from Tiingo, I discovered discontinuities.  These discontinuities caused the buy-sell model to underperform because they caused price trends to be impacted by stock splits instead of investor and trader demand trends before and after a split forward.

The following screenshot displays 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 factors 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.  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.

 

 

History of Stock Splits for Four Tickers

The history of stock splits for a ticker is important because stock splits are a root cause of the stock puzzles that motivate the release of this blog post.  I include in this section a brief history of stock splits that document the stock split history for four tickers (QQQ, TQQQ, SPY, SPXL).  The table values are based on Google Search and Copilot prompt replies for split history.  Buy-sell choices for these four tickers will be modeled with an EMA proper order criterion in an upcoming blog post with the correction factors covered in this post for migrating data from raw stock prices to valid adjusted stock prices.

 

Stock Splits for QQQ, TQQQ, SPY, and SPXL
Ticker	Date(s)	Split Ratio	Type
QQQ	Mar 20, 2000	2‑for‑1	Forward Split
TQQQ	Feb 25, 2011	2‑for‑1	Forward Split
	May 11, 2012	2‑for‑1	Forward Split
	Jan 24, 2014	2‑for‑1	Forward Split
	Jan 12, 2017	2‑for‑1	Forward Split
	May 24, 2018	2‑for‑1	Forward Split
	Jan 21, 2021	2‑for‑1	Forward Split
	Jan 13, 2022	2‑for‑1	Forward Split
SPY	None	N/A	N/A
SPXL	Apr 2, 2013	1‑for‑3	Reverse Split
	May 1, 2017	4‑for‑1	Forward Split
	Jan 12, 2022	2‑for‑1	Forward Split

 

SPY is an example of a ticker that does not need to be checked for puzzling price continuities because it has no stock splits in its history from its inception date through when the date price data was collected for this blog post.  On the other hand, QQQ has just one stock split in its history.  TQQQ and SPXL stock split ticker history has a more extensive set of stock splits.  As the next section shows, the process for generating custom adjusted price values based on Tiingo raw prices grows more complex as the number of historical stock splits grow.

A T-SQL Script for Computing Custom Adjusted Prices

The following T-SQL script rebuilds a stock splits table (dbo.splits) with historical split data for QQQ, TQQQ, SPY, and SPXL.  It then uses that table to calculate custom adjusted closing prices for each ticker in dbo.ticker_date_prices. The adjustment ensures continuity across split dates by scaling historical prices with the cumulative product of split factors even if the data provider’s adjusted prices do not properly adjust historical adjusted prices for stock splits.

The core logic is contained in a final select statement using a Common Table Expression (CTE) named cumulative.  This query uses a correlated scalar subquery against the custom dbo.splits table, together with the price table (dbo.ticker_date_prices), to calculate the adjustedclose column values.  It uses a scalar subquery to find the cumulative product of all future split factors and multiplies this cumulative factor by the raw closing price to determine the corrected, split-adjusted historical price. This process effectively scales all historical prices backward in time to account for subsequent splits.

The scalar subquery runs once per row in dbo.ticker_date_prices. For large datasets, this can be expensive in runtime, but I did not observe long run times for the dataset used in this blog post.  If you were running this script for hundreds or even thousands of tickers in dbo.ticker_date_prices, you may prefer to precompute cumulative factors and then join them by ticker and date to dbo.ticker_date_prices.

 

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', '2-for-1', 0.5),

('TQQQ', '2021-01-21', '2-for-1', 0.5),

('TQQQ', '2022-01-13', '2-for-1', 0.5),

 

-- SPY

-- No splits, so no rows needed

 

-- SPXL

('SPXL', '2013-04-02', '1-for-3', 3.0),

('SPXL', '2017-05-01', '4-for-1', 0.25),

('SPXL', '2022-01-12', '2-for-1', 0.5);

 

 

-- optionally display dbo.splits

select * from dbo.splits;

 

-- return custom adjusted close values for all tickers

with cumulative as (

    select

        p.ticker,

        p.[date],

        p.[close] as rawclose,

        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

)

select

    ticker,

    date,

    rawclose,

    adjustedclose

from cumulative

order by ticker, date;

 

The following screenshot displays an excerpt from the result set from the preceding script with rawclose and adjustedclose column values for dates that match the result set pictured in the “Where I Discovered Tiingo ETF Price Puzzles” section.  The result set from the “Where I Discovered Tiingo ETF Price Puzzles” section shows adjustedclose column values derived from Tiingo.com.  The result set from the following screenshot shows custom adjustedclose column values.

As you can see, there is no major discontinuity in the adjustedclose column values between  2014-01-23 and 2014-01-24.  In contrast, the result set from the “Where I Discovered Tiingo ETF Price Puzzles” section shows a noticeable decline in adjustedclose values between 2014-01-23 and 2014-01-24.  This outcome confirms the effectiveness of the preceding script at removing discontinuity after a stock split relative to the reported adjustedclose column values from Tiingo.  A slightly reduced discontinuity effect shows for several days after the stock split relative to the earlier result set values from Tiingo.

 

 

Concluding Comments

This blog post alerts you to the fact that you can return improperly corrected adjusted price data from Tiingo (and perhaps other ohlcv end-of-day data providers).  It is important to detect and correct invalid adjusted price series because they can result in faulty conclusions about a buy-sell model or a strategy for buying and selling ticker shares.  This post reveals how to detect improperly adjusted price data from a data provider when the errors derive from stock splits.  The post also presents a T-SQL script that shows how to calculate alternative adjusted price data as a workaround for the problem of improperly returned adjusted price data from a data provider.