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.

.. code-block:: sql
   :caption: ATR Indicator from Trade Data

    -- 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.

.. code-block:: sql
   :caption: ATR Indicator from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Bollinger Bands from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Bollinger Bandwidth as Percentage from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Donchian Channels from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Donchian Channels from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Maximum Drawdown from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Maximum Drawdown from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: On-Balance Volume from Trade Data

    -- 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.

.. code-block:: sql
   :caption: On-Balance Volume from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Rate of Change from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Rate of Change from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: RSI Indicator from Trade Data

    -- 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.

.. code-block:: sql
   :caption: RSI Indicator from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Stochastic Oscillator for CSCO based on a Day of Trades

    -- 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.

.. code-block:: sql
   :caption: Stochastic Oscillator for CSCO based on a Day of 1 Minute Trade Bars

    -- 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.

.. code-block:: sql
   :caption: Realized Volatility from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Realized Volatility from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Rolling Standard Deviation from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Rolling Standard Deviation from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Volume Bars from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Volume Profile from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Volume Profile by Samples from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Volume Profile by Tick Size from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Volume Spike Detection from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Volume Spike Detection from 1-Minute Bars

    -- 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.

.. code-block:: sql
   :caption: Volume Surge Indicator from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Volume Surge Indicator from 1-Minute Bars

    -- 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)``.

.. code-block:: sql
   :caption: Aggressor Volume Imbalance from Trade Data

    -- 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.

.. code-block:: sql
   :caption: Order Flow Imbalance from NBBO Data

    -- 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.

.. code-block:: sql
   :caption: VPIN from Trade Data

    -- 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
    )