Copy the code to paste and execute the metric in Snowflake platform
Snowflake 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.PUBLIC.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
Snowflake 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.
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.PUBLIC.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;
Snowflake 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.
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.PUBLIC.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,
TIME_FROM_PARTS(
CAST(SUBSTRING(Time, 1, 2) AS INT),
CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 AS INT),
0
) AS StartTime,
TIME_FROM_PARTS(
CAST(SUBSTRING(Time, 1, 2) AS INT),
CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 + 4 AS INT),
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;
Snowflake 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.PUBLIC.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,
TIME_FROM_PARTS(
CAST(SUBSTRING(Time, 1, 2) AS INT),
CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 AS INT),
0
) AS StartTime,
TIME_FROM_PARTS(
CAST(SUBSTRING(Time, 1, 2) AS INT),
CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 + 4 AS INT),
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;
Snowflake Spread
Formula Name: Spread
Formula Description: The spread is the gap between the bid and the ask prices of a security.
SELECT Date,
Time,
Instrument,
AskPrice,
Bidprice,
ABS(LAST_VALUE(AskPrice) IGNORE NULLS OVER (PARTITION BY Date, Instrument
ORDER BY Date, Time, SequenceNumber ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
- LAST_VALUE(BidPrice) IGNORE NULLS OVER (PARTITION BY Date, Instrument
ORDER BY Date, Time, SequenceNumber ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as Spread
FROM morningstar_151_20191106_taq_sample
WHERE Messagetype = 'Quote'
AND
(AskPrice IS NOT NULL
OR
BidPrice IS NOT NULL)
AND Instrument = 'BHP'
ORDER BY
Instrument,
Date,
Time;
Snowflake 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.
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.PUBLIC.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.PUBLIC.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,
TIME_FROM_PARTS(
CAST(SUBSTRING(Time, 1, 2) AS INT),
CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 AS INT),
0
) AS StartTime,
TIME_FROM_PARTS(
CAST(SUBSTRING(Time, 1, 2) AS INT),
CAST(FLOOR(CAST(SUBSTRING(Time, 4, 2) AS INT) / 5) * 5 + 4 AS INT),
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;
Snowflake 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.
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.PUBLIC.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.PUBLIC.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.PUBLIC.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;
Snowflake 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.
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.PUBLIC.Morningstar_151_20191106_TAQ_sample
WHERE Instrument in ('BHP', 'VOD', 'PTY')
AND TradeCondition != '0'
)
WHERE row_no = 1
ORDER BY Date, Instrument;