Technical Analysis#
This section contains examples of calculating financial indicators and technical analysis metrics from market data using OneTick SQL. These examples demonstrate how to compute common technical analysis indicators including momentum, volatility, trend, and volume-based metrics.
Technical analysis indicators are calculated using window functions to compute rolling statistics over time or tick-based windows. Many examples include both tick-based calculations and pre-calculated bar aggregations for performance comparison.
Average True Range (ATR)#
The Average True Range (ATR) measures volatility by calculating the average of true ranges over a specified period. The true range is the greatest of: (1) the high-low range, (2) absolute value of high minus prior close, or (3) absolute value of low minus prior close.
Returns the Average True Range (ATR) Indicator. The Period is defined as 1 minute. The Prevailing Price 1 Minute Previously (PRICE_N_BACK) is calculated using the TIME_SHIFT() function. The HIGH is calculated as a rolling Maximum across the period and the LOW is calculated as a rolling Minimum across the period. The HIGH, LOW, and PRICE_N_BACK are used to calculate three ranges: High Low Range = HIGH - LOW, Absolute value of High to Prior Price Range = HIGH - PRICE_N_BACK, and Absolute value of Low to Prior Price = LOW - PRICE_N_BACK. The maximum of these three ranges produces the True Range (TR). The Average True Range is calculated as a 14 Period Moving Average.
-- Calculates Average True Range (ATR) Indicator from Trade Data
select PRICE, HIGH, LOW, PRICE_N_BACK,
TR,
AVG(TR) over (order by TIMESTAMP asc range interval '14' minute preceding) as ATR
from
(
-- Calculate True Range
select PRICE, HIGH, LOW, PRICE_N_BACK,
case
when HIGH-LOW >= ABS(HIGH - PRICE_N_BACK) and HIGH-LOW >= ABS(LOW - PRICE_N_BACK) then HIGH-LOW
when ABS(HIGH - PRICE_N_BACK) >= HIGH-LOW and ABS(HIGH - PRICE_N_BACK) >= ABS(LOW - PRICE_N_BACK) then ABS(HIGH - PRICE_N_BACK)
else ABS(LOW - PRICE_N_BACK)
end as TR
from
(
-- Retrieve Price, Prevailing Price 1 minute earlier, and rolling 1 minute High and Low
select PRICE,
TIME_SHIFT('PRICE',-(60 * 1000)) as PRICE_N_BACK,
MAX(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as HIGH, -- Rolling Maximum
MIN(PRICE) OVER(order by TIMESTAMP asc range interval '1' minute preceding) as LOW -- Rolling Minimum
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000000
)
)
Returns the Average True Range (ATR) Indicator from 1 Minute Trade Bars. The Previous LAST Price is retrieved using LAG(LAST). The HIGH, LOW, LAST and PRIOR_LAST are used to calculate three ranges: High Low Range = HIGH - LOW, Absolute value of High to Prior Last Range = HIGH - PRIOR_LAST, and Absolute value of Low to Prior Last Range = LOW - PRIOR_LAST. The maximum of these three ranges produces the True Range (TR). The Average True Range is calculated as a 14 Period Moving Average.
-- Calculates Average True Range (ATR) Indicator from 1 Minute Trade Bars
select LAST, HIGH, LOW, PRIOR_LAST,
TR,
AVG(TR) over (order by TIMESTAMP asc range interval '14' minute preceding) as ATR
from
(
-- Calculate True Range
select LAST, HIGH, LOW, PRIOR_LAST,
case
when HIGH-LOW >= ABS(HIGH - PRIOR_LAST) and HIGH-LOW >= ABS(LOW - PRIOR_LAST) then HIGH-LOW
when ABS(HIGH - PRIOR_LAST) >= HIGH-LOW and ABS(HIGH - PRIOR_LAST) >= ABS(LOW - PRIOR_LAST) then ABS(HIGH - PRIOR_LAST)
else ABS(LOW - PRIOR_LAST)
end as TR
from
(
-- Retrieve Last, High, Low and Prior Last
select LAST, HIGH, LOW,
LAG(LAST) OVER(ORDER BY TIMESTAMP) as PRIOR_LAST
from US_COMP_SAMPLE_BARS.TRD_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
)
Bollinger Bands#
Bollinger Bands consist of a moving average (middle band) and two standard deviation bands (upper and lower) calculated from price data. The bands widen during volatile periods and narrow during calm periods, providing insight into volatility and potential overbought/oversold conditions.
In the example below, the subquery calculates a moving average and moving standard deviation across Trade Price with a window of 5 minutes. The parent query then calculates the Upper and Lower Bollinger Bands by adding/subtracting twice the moving standard deviation from the moving average price.
-- Calculates Bollinger Bands from Trade Data
select PRICE, SIZE, MVG_AVG_PRICE,
mvg_avg_price + 2 * MVG_STDDEV_PRICE as BOLLINGER_UPPER_BAND,
mvg_avg_price - 2 * MVG_STDDEV_PRICE as BOLLINGER_LOWER_BAND
from
(
-- Calculate Moving Averages and Standard Deviations
select
AVG(PRICE) over (order by TIMESTAMP asc range interval '5' minute preceding) as MVG_AVG_PRICE,
STDDEV(PRICE) over (order by TIMESTAMP asc range interval '5' minute preceding) as MVG_STDDEV_PRICE,
PRICE, SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
)
In the example below, the subquery calculates a moving average and moving standard deviation across Trade Price with a window of 5 minutes. The parent query calculates the Upper and Lower Bollinger Bands, Bandwidth as 4 times the moving standard deviation, and percentage Bandwidth by dividing by the moving average price and multiplying by 100.
-- Calculates Bollinger Bandwidth from Trade Data
select PRICE, SIZE, MVG_AVG_PRICE,
mvg_avg_price + 2 * MVG_STDDEV_PRICE as BOLLINGER_UPPER_BAND,
mvg_avg_price - 2 * MVG_STDDEV_PRICE as BOLLINGER_LOWER_BAND,
4 * MVG_STDDEV_PRICE as BOLLINGER_BANDWIDTH,
100 * (4 * MVG_STDDEV_PRICE / MVG_AVG_PRICE) as PCNT_BOLLINGER_BANDWIDTH
from
(
-- Calculate Moving Averages and Standard Deviations
select
AVG(PRICE) over (order by TIMESTAMP asc range interval '5' minute preceding) as MVG_AVG_PRICE,
STDDEV(PRICE) over (order by TIMESTAMP asc range interval '5' minute preceding) as MVG_STDDEV_PRICE,
PRICE, SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
)
Donchian Channels#
Donchian Channels are volatility bands that track the highest high and lowest low over a specified period, with a middle channel representing the midpoint. These channels are commonly used to identify breakouts and support/resistance levels.
The most common period is 20. The Upper Donchian Channel is the rolling Maximum over the last n periods, the Lower Donchian Channel is the rolling Minimum over the last n periods, and the Middle Channel is the midpoint between them.
-- Calculates Donchian Channels from Trade Data
select LAST, UPPER_CHANNEL, LOWER_CHANNEL,
(UPPER_CHANNEL + LOWER_CHANNEL)/2 as MID_CHANNEL
from
(
-- Calculates the Upper and Lower Channels
select LAST,
MAX(HIGH) OVER(order by TIMESTAMP asc range interval '20' minute preceding) as UPPER_CHANNEL, -- Upper Donchian Channel
MIN(LOW) OVER(order by TIMESTAMP asc range interval '20' minute preceding) as LOWER_CHANNEL -- Upper Donchian Channel
from US_COMP_SAMPLE_BARS.TRD_1M t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
limit 1000
)
The most common period is 20. The Upper Donchian Channel is the rolling Maximum over the last n periods, the Lower Donchian Channel is the rolling Minimum over the last n periods, and the Middle Channel is the midpoint between them.
-- Calculates Donchian Channels from from 1 Minute Trade Bars
select LAST, UPPER_CHANNEL, LOWER_CHANNEL,
(UPPER_CHANNEL + LOWER_CHANNEL)/2 as MID_CHANNEL
from
(
-- Calculates the Upper and Lower Channels
select LAST,
MAX(HIGH) OVER(order by TIMESTAMP asc rows 19 preceding) as UPPER_CHANNEL, -- Upper Donchian Channel
MIN(LOW) OVER(order by TIMESTAMP asc rows 19 preceding) as LOWER_CHANNEL -- Upper Donchian Channel
from US_COMP_SAMPLE_BARS.TRD_1M t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
limit 1000
)
Maximum Drawdown (MDD)#
Maximum Drawdown measures the largest peak-to-trough decline from the highest price to the lowest subsequent price, expressed as a percentage. It quantifies the worst-case loss that could have occurred during a trading period.
The Running High Price is calculated across the period. The percentage difference between the Trade Price and Running High is calculated as the percentage Drawdown. The minimum drawdown is calculated across the period.
-- Calculates % Maximum Drawdown (MDD) from Trade Data
select
PRICE, RUNNING_HIGH_PRICE,
PCNT_DRAWDOWN,
MIN(PCNT_DRAWDOWN) OVER(order by TIMESTAMP asc) as MAX_PCNT_DRAWDOWN
from
(
-- Calculate % Drawdown
select
PRICE, RUNNING_HIGH_PRICE,
100 * (PRICE - RUNNING_HIGH_PRICE) / RUNNING_HIGH_PRICE as PCNT_DRAWDOWN
from
(
-- Calculate Running High Price
select
PRICE,
MAX(PRICE) OVER(order by TIMESTAMP asc) as RUNNING_HIGH_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
)
)
The Running High Price is calculated across the period. The percentage difference between the Last Price and Running High is calculated as the percentage Drawdown. The minimum drawdown is calculated across the period.
-- Calculates % Maximum Drawdown (MDD) from 1 Minute Trade Bars
select
LAST, RUNNING_HIGH_PRICE,
PCNT_DRAWDOWN,
MIN(PCNT_DRAWDOWN) OVER(order by TIMESTAMP asc) as MAX_PCNT_DRAWDOWN
from
(
-- Calculate % Drawdown
select
LAST, RUNNING_HIGH_PRICE,
100 * (LAST - RUNNING_HIGH_PRICE) / RUNNING_HIGH_PRICE as PCNT_DRAWDOWN
from
(
-- Calculate Running High PRice
select
LAST,
MAX(HIGH) OVER(order by TIMESTAMP asc) as RUNNING_HIGH_PRICE
from US_COMP_SAMPLE_BARS.TRD_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
)
)
On-Balance Volume (OBV)#
On-Balance Volume is a momentum indicator that relates price change to volume. It accumulates volume with a positive sign when prices rise and a negative sign when prices fall, helping identify the strength of price trends.
Retrieves the PRICE, SIZE and PRIOR_PRICE using LAG(). Calculates the Signed SIZE based on whether the PRICE is greater, less or equal to the Prior PRICE. The On-Balance Volume is calculated as the sum of the current and previous SIGNED_SIZE.
-- Calculates On-Balance Volume (OBV) from Trade Data
select PRICE, SIZE, SIGNED_SIZE,
SUM(SIGNED_SIZE) over (order by TIMESTAMP asc rows between 1 preceding and current row) as OBV
from
(
--Calculate Signed Volume based on whether the PRICE is higher or lower than the Prior PRICE.
select PRICE, SIZE,
case
when PRICE > PRIOR_PRICE then SIZE
when PRICE < PRIOR_PRICE then -SIZE
else 0
end as SIGNED_SIZE
from
(
-- Return Size, Price and Prior Price
select SIZE, PRICE, LAG(PRICE) OVER(ORDER BY TIMESTAMP) as PRIOR_PRICE
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
limit 1000
)
)
Retrieves the LAST, VOLUME and PRIOR_LAST using LAG(). Calculates the Signed VOLUME based on whether the LAST is greater, less or equal to the Prior LAST. The On-Balance Volume is calculated as the sum of the current and previous SIGNED_VOLUME.
-- Calculates On-Balance Volume (OBV) from 1 Minute Trade Bars
select LAST, VOLUME, SIGNED_VOLUME,
SUM(SIGNED_VOLUME) over (order by TIMESTAMP asc rows between 1 preceding and current row) as OBV
from
(
--Calculate Signed Volume based on whether the Last is higher or lower than the Prior Last.
select LAST, VOLUME,
case
when LAST > PRIOR_LAST then VOLUME
when LAST < PRIOR_LAST then -VOLUME
else 0
end as SIGNED_VOLUME
from
(
-- Return Volume, Last and Prior Last
select VOLUME, LAST, LAG(LAST) OVER(ORDER BY TIMESTAMP) as PRIOR_LAST
from US_COMP_SAMPLE_BARS.TRD_1M t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
limit 1000
)
)
Rate of Change (ROC)#
Rate of Change measures the percentage change in price over a specified time period. It indicates the speed at which prices are changing and can help identify momentum and potential reversals.
Returns the Rate of Change (ROC) Indicator. The lookback Period is set to 7 seconds. The TIME_SHIFT() function is used to return the prevailing PRICE a set number of milliseconds previously. ROC is calculated as 100 * (PRICE - PRICE_N_BACK) / PRICE_N_BACK.
-- Calculates Rate of Change (ROC) Indicator from Trade Data
select PRICE, PRICE_N_BACK,
100 * (PRICE - PRICE_N_BACK) / PRICE_N_BACK as ROC
from
(
--Retrieve Price and Prevailing Price 7 seconds earlier
select PRICE,
TIME_SHIFT('PRICE',-(7 * 1000)) as PRICE_N_BACK
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
Returns the Rate of Change (ROC) Indicator from 1 Minute Trade Bars. The lookback Period is set to 7 bars. LAG(LAST,7) returns the LAST field 7 records previous as LAST_N_BACK. ROC is calculated as 100 * (LAST - LAST_N_BACK) / LAST_N_BACK.
-- Calculates Rate of Change (ROC) Indicator from 1 Minute Trade Bars
select LAST, LAST_N_BACK,
100 * (LAST - LAST_N_BACK) / LAST_N_BACK as ROC
from
(
--Retrieve Price and Prevailing Price 7 Bars earlier
select LAST, LAG(LAST,7) OVER(ORDER BY TIMESTAMP) as LAST_N_BACK
from US_COMP_SAMPLE_BARS.TRD_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
Relative Strength Index (RSI)#
The Relative Strength Index measures momentum by comparing the magnitude of recent gains to recent losses. It oscillates between 0 and 100, with values above 70 suggesting overbought conditions and values below 30 suggesting oversold conditions.
Returns the RSI together with the RS (Average Gain over Average Loss). Uses LAG() to get previous Price and calculate the Change in Price. Case Statements separate the Gains from the Losses. The Gains and Losses are averaged across a rolling 14 minute period. RS and RSI are calculated from the Moving Average Gains and Losses.
-- Calculates RSI Indicator from Trade Data
select PRICE,
MVG_AVG_GAIN / MVG_AVG_LOSS as RS,
100 - (100 / (1 + (MVG_AVG_GAIN / MVG_AVG_LOSS))) as RSI,
MVG_AVG_GAIN,MVG_AVG_LOSS
from
(
-- Calculate 14 Minute Rolling Average Gain and Loss
select TIMESTAMP, PRICE,
AVG(GAIN) over (order by TIMESTAMP asc range interval '14' minute preceding) as MVG_AVG_GAIN,
AVG(LOSS) over (order by TIMESTAMP asc range interval '14' minute preceding) as MVG_AVG_LOSS
from
(
-- Calculate whether Change is a Gain or a Loss
select TIMESTAMP, PRICE,
case when CHANGE_PRICE > 0 then CHANGE_PRICE else 0 end as GAIN,
case when CHANGE_PRICE < 0 then -CHANGE_PRICE else 0 end as LOSS
from
(
-- Retrieve Prior Price and Change in Price
select TIMESTAMP, PRICE, SIZE,
LAG(PRICE) OVER(ORDER BY TIMESTAMP) as PRIOR_PRICE,
PRICE - LAG(PRICE) OVER(ORDER BY TIMESTAMP) as CHANGE_PRICE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
)
)
Returns the RSI together with the RS (Average Gain over Average Loss) from 1 Minute Trade Bars. Uses LAG() to get previous Last Price and calculate the Change in Last Price. Case Statements separate the Gains from the Losses. The Gains and Losses are averaged across a rolling 14 minute period.
-- Calculates RSI Indicator from 1 Minute Trade Bars
select LAST,
MVG_AVG_GAIN / MVG_AVG_LOSS as RS,
100 - (100 / (1 + (MVG_AVG_GAIN / MVG_AVG_LOSS))) as RSI,
MVG_AVG_GAIN,MVG_AVG_LOSS
from
(
-- Calculate 14 Minute Rolling Average Gain and Loss
select TIMESTAMP, LAST,
AVG(GAIN) over (order by TIMESTAMP asc range interval '14' minute preceding) as MVG_AVG_GAIN,
AVG(LOSS) over (order by TIMESTAMP asc range interval '14' minute preceding) as MVG_AVG_LOSS
from
(
-- Calculate whether Change is a Gain or a Loss
select TIMESTAMP, LAST,
case when CHANGE_LAST > 0 then CHANGE_LAST else 0 end as GAIN,
case when CHANGE_LAST < 0 then -CHANGE_LAST else 0 end as LOSS
from
(
-- Retrieve Prior Last and Change in Last
select TIMESTAMP, LAST,
LAG(LAST) OVER(ORDER BY TIMESTAMP) as PRIOR_LAST,
LAST - LAG(LAST) OVER(ORDER BY TIMESTAMP) as CHANGE_LAST
from US_COMP_SAMPLE_BARS.TRD_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
)
)
Stochastic Oscillator#
The Stochastic Oscillator compares a closing price to the price range over a specified period. It includes %K (raw value) and %D (smoothed value), with values above 80 indicating overbought and values below 20 indicating oversold conditions.
Returns the Stochastic Oscillator. The period is set to minutes in this case but could equally be seconds or days. Uses a 14 period MAX() to calculate the highest price traded during the period and a 14 period MIN() to calculate the lowest price. %K is calculated as 100 * (PRICE - MLOW) / (MHIGH - MLOW) and %D is calculated as a 3 period AVG() moving average.
-- Calculates Stochastic Oscillator from Trade Data
select PRICE, MLOW, MHIGH, PCNT_K,
AVG(PCNT_K) over (order by TIMESTAMP asc range interval '3' minute preceding) as PCNT_D
from
(
-- Calculate %K
select PRICE, MLOW, MHIGH,
100 * (PRICE - MLOW) / (MHIGH - MLOW) as PCNT_K
from
(
-- Calculate 14 Minute Rolling Minimum and Maximum Prices
select PRICE,
MIN(PRICE) over (order by TIMESTAMP asc range interval '14' minute preceding) as MLOW,
MAX(PRICE) over (order by TIMESTAMP asc range interval '14' minute preceding) as MHIGH
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
)
Returns the Stochastic Oscillator from 1 Minute Trade Bars. The period is set to minutes, given the data is 1 minute bars. Uses a 14 period MAX() to calculate the highest price traded during the period and a 14 period MIN() to calculate the lowest price. %K is calculated as 100 * (LAST - MLOW) / (MHIGH - MLOW) and %D is calculated as a 3 period AVG() moving average.
-- Calculates Stochastic Oscillator from 1 Minute Trade Bars
select LAST, MLOW, MHIGH, PCNT_K,
AVG(PCNT_K) over (order by TIMESTAMP asc range interval '3' minute preceding) as PCNT_D
from
(
-- Calculate %K
select LAST, MLOW, MHIGH,
100 * (LAST - MLOW) / (MHIGH - MLOW) as PCNT_K
from
(
-- Calculate 14 Minute Rolling Minimum and Maximum Prices
select LAST,
MIN(LOW) over (order by TIMESTAMP asc rows 13 preceding) as MLOW,
MAX(HIGH) over (order by TIMESTAMP asc rows 13 preceding) as MHIGH
from US_COMP_SAMPLE_BARS.TRD_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
)
Realized Volatility#
Realized Volatility measures the standard deviation of logarithmic returns over a rolling window and annualizes the result. It provides an estimate of volatility based on historical price movements and is commonly used for risk assessment.
The Log Returns are calculated by taking the natural Log of Price divided by the Price Back 1 Period. A 30 minute rolling standard deviation is calculated for the Log Returns. The Result is annualized by multiplying by the number of trading days (252) and the number of 30 minute periods in a day.
-- Calculates Realized Volatility (RV) from Trade Data
select
LOG_RETURN,
ROLLING_STDDEV_LOG_RETURN,
ROLLING_STDDEV_LOG_RETURN * 252 * 13 as ANNUALIZED_RV -- Calculate the Annualized Realized Volatility
from
(
-- Calculate Rolling Standard Deviation of Log Returns Across a 30 Minute Period
select
LOG_RETURN,
STDDEV(LOG_RETURN) OVER(order by TIMESTAMP asc range interval '30' minute preceding) as ROLLING_STDDEV_LOG_RETURN -- Rolling Standard Deviation
from
(
--Calculate Log Return for the Minute Periods.
select LOG(LAST_PRICE / LAG(LAST_PRICE) OVER(ORDER BY TIMESTAMP)) as LOG_RETURN
from
(
-- Divide Into Minute Periods
select LAST(PRICE) as LAST_PRICE
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
group by time_bucket(INTERVAL '1' MINUTE)
)
)
)
The Log Returns are calculated by taking the natural Log of Price divided by the Prior Price. A 30 minute rolling standard deviation is calculated for the Log Returns. The Result is annualized by multiplying by the number of trading days (252) and the number of 30 minute periods in a day.
-- Calculates Realized Volatility (RV) from 1 Minute Trade Bars
select
LOG_RETURN,
ROLLING_STDDEV_LOG_RETURN,
ROLLING_STDDEV_LOG_RETURN * 252 * 13 as ANNUALIZED_RV
from
(
-- Calculate Rolling Standard Deviation of Log Returns Across a 30 Minute Period
select
LOG_RETURN,
STDDEV(LOG_RETURN) OVER(order by TIMESTAMP asc rows 29 preceding) as ROLLING_STDDEV_LOG_RETURN -- Rolling Standard Deviation
from
(
--Calculate Log Return for the Minute Periods.
select LOG(LAST / LAG(LAST) OVER(ORDER BY TIMESTAMP)) as LOG_RETURN
from US_COMP_SAMPLE_BARS.TRD_1M t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
limit 1000
)
)
Rolling Standard Deviation#
Rolling Standard Deviation calculates the standard deviation of prices within a rolling time window. It measures price volatility and adapts to changing market conditions, increasing during volatile periods and decreasing during stable ones.
Returns the Rolling Standard Deviation. The Period is defined as 5 minutes. The rolling Standard Deviation is calculated across the period.
-- Calculates Rolling Standard Deviation from Trade Data
select PRICE,
STDDEV(PRICE) OVER(order by TIMESTAMP asc range interval '5' minute preceding) as ROLLING_STDDEV_PRICE -- Rolling Standard Deviation
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
Returns the Rolling Standard Deviation from 1 Minute Trade Bars. The Period is defined as 5 minutes. The rolling Standard Deviation is calculated across the period.
-- Calculates Rolling Standard Deviation from 1 Minute Trade Bars
select LAST,
STDDEV(LAST) OVER(order by TIMESTAMP asc rows 4 preceding) as ROLLING_STDDEV_PRICE -- Rolling Standard Deviation
from US_COMP_SAMPLE_BARS.TRD_1M t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
Volume Bars#
Volume Bars aggregate trades into fixed-volume bins rather than fixed-time intervals. This approach focuses analysis on periods of significant trading activity and can reveal patterns masked by time-based aggregation during varying liquidity conditions.
The accumulative volume is calculated across the period and divided by the required Fixed Volume, then floored to create a set of Volume Bins. The trading day is then aggregated grouped by the Volume Bin. As the Volume Bins cover different time ranges, the Start and End time for each Bin are also retrieved.
-- Calculates Bars by Fixed Volume Bin from Trade Data
select
VOL_BIN,
MIN(TIMESTAMP) as BIN_START,
MAX(TIMESTAMP) as BIN_END,
FIRST(PRICE) as FIRST,
MAX(PRICE) as HIGH,
MIN(PRICE) as LOW,
LAST(PRICE) as LAST,
COUNT(SIZE) as TRADE_COUNT,
SUM(SIZE) as VOLUME
from
(
-- Retrieve Volume and Calculate Volume Bins
select
PRICE,
SIZE,
floor(sum(SIZE) over(order by TIMESTAMP asc) / 100000) as VOL_BIN
from LSE_SAMPLE.TRD
where SYMBOL_NAME = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
)
group by VOL_BIN
Volume Profile#
Volume Profile shows the distribution of trading volume across different price levels during a trading period. It identifies support and resistance levels based on where the most trading activity occurred.
Retrieves the VOLUME and the TRADE_COUNT grouped by PRICE.
-- Calculates Volume Profile or Volume Histogram from Trade Data
select PRICE, sum(SIZE) as VOLUME,
count(SIZE) as TRADE_COUNT
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
group by PRICE
Calculate the Price Range Across the Trading Period and divide by the number of Required Samples Minus 1 to produce the TICK_SIZE. Join the TICK_SIZE to the Trading Data and calculate the Price floored to a specific sample as PRICE_BIN. Retrieves the VOLUME and the TRADE_COUNT grouped by PRICE_BIN.
-- Calculates Volume Profile or Volume Histogram by Sample from Trade Data
select FLOOR(PRICE / TICK_SIZE) as PRICE_BIN,
sum(SIZE) as VOLUME,
count(SIZE) as TRADE_COUNT
from
(
select t.PRICE as PRICE,
r.TICK_SIZE as TICK_SIZE,
t.SIZE as SIZE
from US_COMP_SAMPLE.TRD t,
(
-- Calculates the Tick Size based on the Price Range
select SYMBOL_NAME, (MAX(PRICE) - MIN(PRICE))/99 as TICK_SIZE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME = 'CSCO'
group by SYMBOL_NAME
) r
where t.SYMBOL_NAME = 'CSCO'
and t.SYMBOL_NAME = r.SYMBOL_NAME
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
)
group by PRICE_BIN
Specify a Tick Size (in this case 1 cent, 0.01). Calculate the Price floored to a specific Tick Size as PRICE_BIN. Retrieves the VOLUME and the TRADE_COUNT grouped by PRICE_BIN.
-- Calculates Volume Profile or Volume Histrogram by Tick Size from Trade Data
select FLOOR(PRICE / 0.01) * 0.01 as PRICE_BIN,
sum(SIZE) as VOLUME,
count(SIZE) as TRADE_COUNT
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
group by PRICE_BIN
Volume Spike Detection#
Volume Spike Detection identifies periods of abnormally high trading volume compared to recent historical averages. This can signal significant market events, breakouts, or changes in market sentiment.
Volume is aggregated based on defined bucket period. Average Volume is calculated based on the last N buckets. Spikes are identified if the Volume is greater than twice the average volume. Retrieves the VOLUME and the TRADE_COUNT grouped by PRICE.
-- Identifying Volume Spikes from Trade Data
select LAST_PRICE, VOLUME, TRADE_COUNT, MAVG_VOLUME,
case
when VOLUME > 2 * MAVG_VOLUME then 1
else 0
end as SPIKES
from
(
-- Calculate Average Volume Across Recent Prior Intervals
select LAST_PRICE, VOLUME, TRADE_COUNT,
AVG(VOLUME) over (order by TIMESTAMP asc rows 4 preceding) as MAVG_VOLUME
from
(
-- Bucket Volume Per Time Interval
select last (PRICE) as LAST_PRICE,
sum(SIZE) as VOLUME,
count(SIZE) as TRADE_COUNT
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
group by time_bucket(INTERVAL '1' MINUTE)
)
)
Volume is aggregated based on defined bucket period from 1 Minute Trade Bars. Average Volume is calculated based on the last N buckets. Spikes are identified if the Volume is greater than twice the average volume.
-- Identifying Volume Spikes from 1 Minute Trade Bars
select LAST, VOLUME, MAVG_VOLUME,
case
when VOLUME > 2 * MAVG_VOLUME then 1
else 0
end as SPIKES
from
(
-- Calculate Average Volume Across Recent Prior Intervals
select LAST, VOLUME,
AVG(VOLUME) over (order by TIMESTAMP asc rows 4 preceding) as MAVG_VOLUME
from US_COMP_SAMPLE_BARS.TRD_1M
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
)
Volume Surge Indicator#
The Volume Surge Indicator measures the ratio of current volume to average volume, highlighting periods when volume significantly deviates from normal levels. Volume is aggregated based on a defined bucket period. Average Volume is calculated based on the last 100 buckets. Volume Surge is calculated as the Current Volume divided by the Average Volume, expressed as a percentage ratio.
-- Calculates Volume Surge Indicator from Trade Data
select LAST_PRICE, VOLUME, TRADE_COUNT, MAVG_VOLUME,
100 * VOLUME / MAVG_VOLUME as VOLUME_SURGE
from
(
-- Calculate Average Volume Across Recent Prior Intervals
select LAST_PRICE, VOLUME, TRADE_COUNT,
AVG(VOLUME) over (order by TIMESTAMP asc rows 99 preceding) as MAVG_VOLUME
from
(
-- Bucket Volume Per Time Interval
select last (PRICE) as LAST_PRICE,
sum(SIZE) as VOLUME,
count(SIZE) as TRADE_COUNT
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
group by time_bucket(INTERVAL '1' MINUTE)
)
)
Volume is aggregated based on a defined bucket period from 1-Minute Bars. Average Volume is calculated based on the last 100 buckets. Volume Surge is calculated as a percentage ratio of current to average volume.
-- Calculates Volume Surge Indicator from 1 Minute Trade Bars
select LAST_PRICE, VOLUME, TRADE_COUNT, MAVG_VOLUME,
100 * VOLUME / MAVG_VOLUME as VOLUME_SURGE
from
(
-- Calculate Average Volume Across Recent Prior Intervals
select LAST_PRICE, VOLUME, TRADE_COUNT,
AVG(VOLUME) over (order by TIMESTAMP asc rows 99 preceding) as MAVG_VOLUME
from
(
-- Bucket Volume Per Time Interval
select last (PRICE) as LAST_PRICE,
sum(SIZE) as VOLUME,
count(SIZE) as TRADE_COUNT
from US_COMP_SAMPLE.TRD t
where t.SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
group by time_bucket(INTERVAL '1' MINUTE)
)
)
Aggressor Volume Imbalance#
Aggressor Volume Imbalance measures the imbalance between buy-side and sell-side volume, determined by the aggressor side of trades. High imbalances can indicate directional pressure in the market and potential price movements.
Aggressor Volume Imbalance is calculated for Venues that publish the AGGRESSOR_SIDE field. BUY_VOLUME is returned when AGGRESSOR_SIDE = ‘B’ and SELL_VOLUME when AGGRESSOR_SIDE = ‘S’. Volume is aggregated based on a defined bucket period. IMBALANCE_VOLUME = BUY_VOLUME - SELL_VOLUME and percentage imbalance = (BUY_VOLUME - SELL_VOLUME) / (BUY_VOLUME + SELL_VOLUME).
-- Calculates Aggressor Volume Imbalance from Trade Data
select VOLUME, BUY_VOLUME, SELL_VOLUME,
BUY_VOLUME - SELL_VOLUME as IMBALANCE_VOLUME,
100 * (BUY_VOLUME - SELL_VOLUME)/(BUY_VOLUME + SELL_VOLUME) as PCNT_IMBALANCE_VOLUME
from
(
-- Sum Buy and Sell Volume
select
sum(SIZE) as VOLUME,
sum(BUY_VOLUME) as BUY_VOLUME,
sum(SELL_VOLUME) as SELL_VOLUME
from
(
-- Calculate Buy and Sell Volume based on Aggressor Side
select AGGRESSOR_SIDE,
SIZE,
case when AGGRESSOR_SIDE = 'B' then SIZE else 0 end as BUY_VOLUME,
case when AGGRESSOR_SIDE = 'S' then SIZE else 0 end as SELL_VOLUME
from LSE.TRD
where SYMBOL_NAME = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
)
group by time_bucket(INTERVAL '1' MINUTE)
)
Order Flow Imbalance#
Order Flow Imbalance (OFI) is a quantitative metric that measures the net change in supply and demand at the best bid and ask prices. It captures order flow dynamics and can be used to detect informed trading activity. OFI is a metric introduced by Rama Cont, Arseniy Kukanov, and Sasha Stoikov in 2014, measuring the net change in supply and demand at the best bid and ask prices across a specific time interval. As a Composite is used (US_COMP), the NBBO table is retrieved, rather than the QTE table.
-- Calculates Order Flow Imbalance (OFI) from Quote Data
select BID_FLOW - ASK_FLOW as OFI
from
(
-- Calculate Bid and Ask Flow
select
case
when BID_PRICE > PRIOR_BID_PRICE then BID_SIZE
when BID_PRICE < PRIOR_BID_PRICE then PRIOR_BID_SIZE
else BID_SIZE - PRIOR_BID_SIZE
end as BID_FLOW,
case
when ASK_PRICE > PRIOR_ASK_PRICE then ASK_SIZE
when ASK_PRICE < PRIOR_ASK_PRICE then PRIOR_ASK_SIZE
else ASK_SIZE - PRIOR_ASK_SIZE
end as ASK_FLOW
from
(
-- Retrieve Current and Prior Bid and Ask Prices and Sizes
select
BID_PRICE,BID_SIZE,
ASK_PRICE,ASK_SIZE,
LAG(BID_PRICE) OVER(ORDER BY TIMESTAMP) as PRIOR_BID_PRICE,
LAG(BID_SIZE) OVER(ORDER BY TIMESTAMP) as PRIOR_BID_SIZE,
LAG(ASK_PRICE) OVER(ORDER BY TIMESTAMP) as PRIOR_ASK_PRICE,
LAG(ASK_SIZE) OVER(ORDER BY TIMESTAMP) as PRIOR_ASK_SIZE
from US_COMP_SAMPLE.NBBO
where SYMBOL_NAME = 'CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:30:00 America/New_York'
limit 1000
)
)
Volume Synchronized Probability of Informed Trading (VPIN)#
VPIN is an advanced metric that estimates the probability of informed trading based on volume-synchronized order flow imbalance. It divides trading into equal-volume buckets and analyzes the imbalance within each bucket to detect informed trading activity. Volume Bins are calculated based on fixed volume thresholds (e.g., every 100,000 shares). BUY_VOLUME is calculated when AGGRESSOR_SIDE = ‘B’ and SELL_VOLUME when AGGRESSOR_SIDE = ‘S’. Volume is aggregated within each Volume Bin. BIN_IMBALANCE is calculated as the absolute difference between buy and sell volume divided by total volume. VPIN is calculated as a rolling average of BIN_IMBALANCE over a specified number of bins.
-- Calculates Volume Synchronized Probability of Informed Trading (VPIN) from Trade Data
select
VOL_BIN, BIN_START, BIN_END, TRADE_COUNT, VOLUME, BUY_VOLUME, SELL_VOLUME, BIN_IMBALANCE,
AVG(BIN_IMBALANCE) over (order by VOL_BIN asc rows 49 preceding) as VPIN
from
(
-- Calculate Aggregates for Volume Bins
select
VOL_BIN,
MIN(TIMESTAMP) as BIN_START,
MAX(TIMESTAMP) as BIN_END,
COUNT(SIZE) as TRADE_COUNT,
SUM(SIZE) as VOLUME,
SUM(BUY_VOLUME) as BUY_VOLUME,
SUM(SELL_VOLUME) as SELL_VOLUME,
ABS(SUM(BUY_VOLUME) - SUM(SELL_VOLUME)) / SUM(SIZE) as BIN_IMBALANCE
from
(
-- Retrieve Buy and Sell Volume based on Aggressor Side and Calculate Volume Bins
select
AGGRESSOR_SIDE,
SIZE,
case when AGGRESSOR_SIDE = 'B' then SIZE else 0 end as BUY_VOLUME,
case when AGGRESSOR_SIDE = 'S' then SIZE else 0 end as SELL_VOLUME,
floor(sum(SIZE) over(order by TIMESTAMP asc) / 100000) as VOL_BIN
from LSE.TRD
where SYMBOL_NAME = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 Europe/London'
and TIMESTAMP < '2024-01-03 16:00:00 Europe/London'
)
group by VOL_BIN
)