Metrics Catalogue

Metric Code Snippets

Copy the code to paste and execute the metric in Databricks platform

Databricks

Databricks One minute Time bar

Formula Name: One minute Time bar
Formula Description: The 1 minute time bar visually represents a price action of security within a minute. It depicts the open, high, low and close prices showing the price movement within that period.

SELECT
    Instrument,
    Date,
    TimeSub AS Time,
    Open,
    High,
    Low,
    Close,
    Volume
FROM (
    SELECT
        Instrument,
        Date,
        SUBSTRING(Time, 1, 5) AS TimeSub,
        FIRST_VALUE(Price) OVER (
            PARTITION BY Instrument, Date, SUBSTRING(Time, 1, 5)
            ORDER BY Date, Time, SequenceNumber ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS Open,
        MAX(Price) OVER (
            PARTITION BY Instrument, Date, SUBSTRING(Time, 1, 5)
        ) AS High,
        MIN(Price) OVER (
            PARTITION BY Instrument, Date, SUBSTRING(Time, 1, 5)
        ) AS Low,
        LAST_VALUE(Price) OVER (
            PARTITION BY Instrument, Date, SUBSTRING(Time, 1, 5)
            ORDER BY Date, Time, SequenceNumber ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS Close,
        ROW_NUMBER() OVER (
            PARTITION BY Instrument, Date, SUBSTRING(Time, 1, 5)
            ORDER BY Date, Time, SequenceNumber ASC
        ) AS row_no,
        SUM(Volume) OVER (
            PARTITION BY Instrument, Date, SUBSTRING(Time, 1, 5)
            ORDER BY Date, Time, SequenceNumber ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS Volume
    FROM
        RZT_Managed_TickService.morningstar_151_20191106_taq_sample
    WHERE
        Instrument = 'BHP'
        AND MessageType = 'Trade'
        AND TradeCondition != '0'
        AND Volume > 0 )
WHERE
    row_no = 1
ORDER BY
    Instrument, Date, Time 

Databricks End of Day Price

Formula Name: End of Day Price
Formula Description: The end of day of price shows the close price of a security.

%sql

SELECT
    Instrument,
    Date,
    OpenPrice,
    HighPrice,
    LowPrice,
    ClosePrice,
    Volume
FROM (
        SELECT
            Instrument,
            Date,            
            FIRST_VALUE(Price) OVER (
                PARTITION BY Instrument, Date
                ORDER BY Date, Time, sequencenumber ASC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS OpenPrice,
            MAX(Price) OVER (
                PARTITION BY Instrument, Date                
            ) AS HighPrice,
            MIN(Price) OVER (
                PARTITION BY Instrument, Date               
            ) AS LowPrice,
            LAST_VALUE(Price) OVER (
                PARTITION BY Instrument, Date
                ORDER BY Date, Time, sequencenumber ASC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS ClosePrice,            
            LAST_VALUE(CumulativeVolume) OVER (
                PARTITION BY Instrument, Date
                ORDER BY Date, Time, sequencenumber ASC 
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) AS Volume,            
            ROW_NUMBER() OVER (
                PARTITION BY Instrument, Date
                ORDER BY Date, Time, sequencenumber
            ) AS row_no
        FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
        WHERE MessageType = 'Trade'
            AND Instrument = 'VOD'
            AND Volume > 0
            AND TradeCondition != '0'
    )
WHERE row_no = 1
ORDER BY Instrument, Date; 

Databricks Average VWAP

Formula Name: Average VWAP
Formula Description: The Average VWAP shows the average price a security has traded throughout the day, based on both volume and price.

%sql
WITH cte_trades AS (
    -- Preprocess for filtering valid trades
    SELECT Instrument,
        SecurityType,
        Date, 
        Time, 
        SequenceNumber,
        Volume,
        Price,
        TradeID,
        ROW_NUMBER() OVER (            
            ORDER BY Instrument, SecurityType, Date, Time, SequenceNumber ASC
        ) AS TradeSequence
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE MessageType = 'Trade'
        AND TradeCondition != '0'
        AND Volume > 0
        AND Instrument = 'BHP'
    ORDER BY TradeSequence
),
cte_time_interval AS (
    -- Preprocess for 5 min intervals
    SELECT DISTINCT
        Time AS ActualTime, 
        CONCAT_WS(':', 
            SUBSTRING(Time, 1, 2), 
            LPAD(CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 AS STRING), 2, '0'),
            '00'
        ) AS StartTime,
        CONCAT_WS(':',
            SUBSTRING(Time, 1, 2),       
            LPAD(CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 + 4 AS STRING), 2, '0'),
            '59' 
        ) AS EndTime
  FROM cte_trades
)
SELECT Instrument,
    Date,
    StartTime,
    EndTime,
    VWAP
FROM (
    SELECT Instrument,
        Date,
        StartTime,
        EndTime,
        TradeSequence,
        SUM(Price * Volume) OVER (PARTITION BY Instrument, Date, StartTime)
            / SUM(Volume) OVER (PARTITION BY Instrument, Date, StartTime) 
            AS VWAP,
        ROW_NUMBER() OVER (
            PARTITION BY Instrument, SecurityType, Date, StartTime
            ORDER BY TradeSequence ASC
        ) AS row_no
    FROM cte_trades trades
        JOIN cte_time_interval ti on trades.Time = ti.ActualTime
)
WHERE
    row_no = 1 
ORDER BY TradeSequence; 

Databricks Statistics - 5 Minute Market Share Statistics

Formula Name: Statistics - 5 Minute Market Share Statistics
Volume

  • Volume of trade measures the total number of shares or contracts transacted for a specified security during a specified time period. Our calculation shows the number of shares contracted for a single security in 5 minutes.

Value

  • The value of the trade shows the last price of the instrument during the interval displayed. Our calculation shows the last price of the instrument during a 5 minute interval.

Trade Count

  • The trade count measures the total number of trades for a single security in 5 minutes.
WITH cte_trades AS (
    -- Preprocess for filtering valid trades
    SELECT Instrument,
        SecurityType,
        Date, 
        Time, 
        SequenceNumber,
        Volume,
        Price,
        TradeID
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE MessageType = 'Trade'
        AND TradeCondition != '0'
        AND Volume > 0
        AND Instrument = 'BHP'   
),
cte_time_interval AS (
  -- Preprocess for 5 min intervals
    SELECT DISTINCT
        Time AS ActualTime, 
        CONCAT_WS(':', 
            SUBSTRING(Time, 1, 2), 
            LPAD(CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 AS STRING), 2, '0'),
            '00'
        ) AS StartTime,
        CONCAT_WS(':',
            SUBSTRING(Time, 1, 2),       
            LPAD(CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 + 4 AS STRING), 2, '0'),
            '59' 
        ) AS EndTime
    FROM cte_trades
)
SELECT Instrument,
    SecurityType,
    Date,    
    StartTime,
    EndTime,
    Volume,
    Value,
    TradeCount
FROM (
    SELECT Instrument,
        SecurityType,
        Date,    
        StartTime,
        EndTime,
        SUM(Volume) OVER (
            PARTITION BY Instrument, SecurityType, Date, StartTime
        ) AS Volume,
        LAST_VALUE(Price) OVER (
            PARTITION BY Instrument, SecurityType, Date, StartTime
            ORDER BY Instrument, SecurityType, Date, Time, SequenceNumber ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS Value,
        COUNT(TradeID) OVER (
            PARTITION BY Instrument, SecurityType, Date, StartTime
        ) AS TradeCount,
        ROW_NUMBER() OVER (
            PARTITION BY Instrument, SecurityType, Date, StartTime
            ORDER BY Instrument, SecurityType, Date, Time, SequenceNumber ASC
        ) AS row_no
    FROM cte_trades
        JOIN cte_time_interval ti on cte_trades.Time = ti.ActualTime  
)
WHERE row_no = 1
ORDER BY Instrument, SecurityType, Date, StartTime;

Databricks Spread

Formula Name: Spread
Formula Description: The spread is the gap between the bid and the ask prices of a security.

%sql
SELECT Date,
    Time, 
    Instrument, 
    Askprice, 
    Bidprice, 
    ABS(LAST_VALUE(Askprice, true)  OVER (PARTITION BY Date, Instrument 
    ORDER BY Date, Time, SequenceNumber ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
    - LAST_VALUE(Bidprice, true)  OVER (PARTITION BY Date, Instrument 
    ORDER BY Date, Time, SequenceNumber ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as Spread
FROM taq_sample
WHERE Messagetype = 'Quote' 
    AND
    (AskPrice IS NOT NULL
    OR
    BidPrice IS NOT NULL)
    AND Instrument = 'BHP' 
ORDER BY
    Instrument,
    Date,
    Time; 

Databricks Number of Quote Updates

Formula Name: Number of Quote Updates
Formula Description: The Number of Quote updates measures the total number of quote update messages for a single security.A quote update is defined as any change in the best bid or offer(BBO) quote or size across all quote reporting venues.

%sql

WITH cte_bids AS(
    -- Preprocess bids
    SELECT Instrument,    
        Date,
        Time,
        BidPrice,
        BidSize,
        ROW_NUMBER() OVER (            
            ORDER BY Instrument, Date, Time, SequenceNumber ASC
        ) AS BidSequence
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE BidPrice > 0
        AND BidSize > 0
        AND MessageType = 'Quote'        
        AND Instrument = 'BHP'
    ORDER BY BidSequence
),
cte_asks AS(
    -- Preprocess asks
    SELECT Instrument,    
        Date,
        Time,
        AskPrice,
        AskSize,
        ROW_NUMBER() OVER (            
            ORDER BY Instrument, Date, Time, SequenceNumber ASC
        ) AS AskSequence
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE AskPrice > 0
        AND AskSize > 0
        AND MessageType = 'Quote'        
        AND Instrument = 'BHP' 
    ORDER BY AskSequence
),
cte_time_interval AS (
    -- Preprocess for 5 min intervals
    SELECT DISTINCT
        Time AS ActualTime, 
        CONCAT_WS(':', 
            SUBSTRING(Time, 1, 2), 
            LPAD(CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 AS STRING), 2, '0'),
            '00'
        ) AS StartTime,
        CONCAT_WS(':',
            SUBSTRING(Time, 1, 2),       
            LPAD(CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 + 4 AS STRING), 2, '0'),
            '59' 
        ) AS EndTime
    FROM (
        SELECT Time FROM cte_bids
        UNION DISTINCT
        SELECT Time FROM cte_asks
    )
),
cte_bid_count AS (
    SELECT bid.Instrument,
        bid.Date,    
        StartTime,
        EndTime,    
        COUNT (
            CASE WHEN bid.BidPrice = last_bid.BidPrice AND bid.BidSize = last_bid.BidSize
                THEN NULL 
                ELSE 1 
                END
            ) 
            OVER (PARTITION BY bid.Instrument, bid.Date, StartTime) 
        AS BidChanges,        
        ROW_NUMBER() OVER (
            PARTITION BY bid.Instrument, bid.Date, StartTime
            ORDER BY bid.BidSequence ASC
        ) AS row_no
    FROM cte_bids bid        
        JOIN cte_bids last_bid ON bid.BidSequence = last_bid.BidSequence + 1
        JOIN cte_time_interval ti ON bid.Time = ti.ActualTime  
),
cte_ask_count AS (
    SELECT ask.Instrument,
        ask.Date,    
        StartTime,
        EndTime,            
        COUNT (
            CASE WHEN ask.AskPrice = last_ask.AskPrice AND ask.AskSize = last_ask.AskSize 
                THEN NULL 
                ELSE 1 
                END
            ) 
            OVER (PARTITION BY ask.Instrument, ask.Date, StartTime) 
        AS AskChanges,
        ROW_NUMBER() OVER (
            PARTITION BY ask.Instrument, ask.Date, StartTime
            ORDER BY ask.AskSequence ASC
        ) AS row_no
    FROM cte_asks ask        
        JOIN cte_asks last_ask ON ask.AskSequence = last_ask.AskSequence + 1
        JOIN cte_time_interval ti ON ask.Time = ti.ActualTime  
)
SELECT COALESCE(cte_bid_count.Instrument, cte_ask_count.Instrument) As Instrument,
    COALESCE(cte_bid_count.Date, cte_ask_count.Date) As Date,   
    COALESCE(cte_bid_count.StartTime, cte_ask_count.StartTime) As StartTime,
    COALESCE(cte_bid_count.EndTime, cte_ask_count.EndTime) As EndTime,
    COALESCE(cte_bid_count.BidChanges, 0) + COALESCE(cte_ask_count.AskChanges, 0) AS QuoteChanges    
FROM cte_bid_count
    FULL OUTER JOIN cte_ask_count
        ON cte_bid_count.Instrument = cte_ask_count.Instrument
          AND cte_bid_count.Date = cte_ask_count.Date
          AND cte_bid_count.StartTime = cte_ask_count.StartTime 
WHERE cte_bid_count.row_no = 1
    AND cte_ask_count.row_no = 1                
ORDER BY Instrument, Date, StartTime;

Databricks Quote to Trade Ratio

Formula Name: Quote to Trade Ratio
Formula Description: The quote to trade ratio calculates the ratio of quotes to trades for a single security.

%sql

WITH cte_trades AS (
    -- Count trades
    SELECT Instrument,
        Date,        
        COUNT(*) OVER (
            PARTITION BY Instrument, Date
        ) AS Trades,
        ROW_NUMBER() OVER (
            PARTITION BY Instrument, Date
            ORDER BY Instrument, Date
        ) AS row_no
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE MessageType = 'Trade' 
        AND TradeCondition != '0' 
        AND Volume > 0
        AND Instrument = 'BHP'
),
cte_bids AS(
    -- Preprocess bids
    SELECT Instrument,    
        Date,
        Time,
        BidPrice,
        BidSize,
        ROW_NUMBER() OVER (            
            ORDER BY Instrument, Date, Time, SequenceNumber ASC
        ) AS BidSequence
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE BidPrice > 0
        AND BidSize > 0
        AND MessageType = 'Quote'        
        AND Instrument = 'BHP'
    ORDER BY BidSequence       
),
cte_asks AS(
    -- Preprocess asks
    SELECT Instrument,    
        Date,
        Time,
        AskPrice,
        AskSize,
        ROW_NUMBER() OVER (            
            ORDER BY Instrument, Date, Time, SequenceNumber ASC
        ) AS AskSequence
    FROM RZT_MANAGED_TICKSERVICE.Morningstar_151_20191106_TAQ_sample
    WHERE AskPrice > 0
        AND AskSize > 0
        AND MessageType = 'Quote'        
        AND Instrument = 'BHP'
    ORDER BY AskSequence       
),
cte_bid_count AS (
    -- Count non-consecutive bids
    SELECT bid.Instrument,
        bid.Date, 
        COUNT (CASE WHEN bid.BidPrice = last_bid.BidPrice AND bid.BidSize = last_bid.BidSize
                THEN NULL 
                ELSE 1 END
            ) OVER (PARTITION BY bid.Instrument, bid.Date) AS BidChanges,        
        ROW_NUMBER() OVER (
            PARTITION BY bid.Instrument, bid.Date
            ORDER BY bid.BidSequence ASC
        ) AS row_no
    FROM cte_bids bid        
        JOIN cte_bids last_bid ON bid.BidSequence = last_bid.BidSequence + 1
),
cte_ask_count AS (
    -- Count non-consecutive asks
    SELECT ask.Instrument,
        ask.Date,          
        COUNT (CASE WHEN ask.AskPrice = last_ask.AskPrice AND ask.AskSize = last_ask.AskSize 
                THEN NULL 
                ELSE 1 END
            ) OVER (PARTITION BY ask.Instrument, ask.Date) AS AskChanges,
        ROW_NUMBER() OVER (
            PARTITION BY ask.Instrument, ask.Date
            ORDER BY ask.AskSequence ASC
        ) AS row_no
    FROM cte_asks ask        
        JOIN cte_asks last_ask ON ask.AskSequence = last_ask.AskSequence + 1
)
SELECT cte_trades.Instrument,
    cte_trades.Date,
    (COALESCE(cte_bid_count.BidChanges, 0.0) + COALESCE(cte_ask_count.AskChanges, 0.0)) 
        / NULLIF(cte_trades.Trades, 0.0) 
        AS QuoteToTrade    
FROM cte_bid_count
    FULL OUTER JOIN cte_ask_count
        ON cte_bid_count.Instrument = cte_ask_count.Instrument
          AND cte_bid_count.Date = cte_ask_count.Date
    JOIN cte_trades 
        ON cte_trades.Instrument = COALESCE(cte_bid_count.Instrument, cte_ask_count.Instrument)
            AND cte_trades.Date = COALESCE(cte_bid_count.Date, cte_ask_count.Date)
WHERE cte_bid_count.row_no = 1
    AND cte_ask_count.row_no = 1
    AND cte_trades.row_no = 1             
ORDER BY Instrument, Date;

Databricks Intra-day volatility

Formula Name: Intra-day volatility
Formula Description: The Intra-day volatility measures the intraday mid-point price return volatility of a single security.

%sql

SELECT Date, Instrument, Volatility FROM (
    SELECT
            Instrument,
            Date,
            VARIANCE(Price) OVER (
                PARTITION BY Instrument, Date
                ORDER BY Price
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            ) as Volatility,
            ROW_NUMBER() OVER (
                PARTITION BY Instrument, Date
                ORDER BY Price
            ) AS row_no
        FROM
            RZT_Managed_TickService.morningstar_151_20191106_taq_sample
        
        WHERE Instrument in ('BHP', 'VOD', 'PTY')
              AND TradeCondition != '0'
)
WHERE row_no = 1
ORDER BY Date, Instrument; 

Get in touch to find out more