Copy the code to paste and execute the metric in Amazon Redshift platform
AWS Redshift 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"
AWS Redshift 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";
AWS Redshift 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 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,
SUBSTRING("Time", 1, 2) || ':' ||
RIGHT('000' || CAST(FLOOR(CAST(SUBSTRING("Time", 4, 2) AS INT) / 5) * 5 AS VARCHAR), 2) || ':' ||
'00'
AS StartTime,
SUBSTRING("Time", 1, 2) || ':' ||
RIGHT('00' || CAST(FLOOR(CAST(SUBSTRING("Time", 4, 2) AS INT) / 5) * 5 + 4 AS VARCHAR), 2) || ':' ||
'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;
AWS Redshift 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 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,
SUBSTRING("Time", 1, 2) || ':' ||
RIGHT('000' || CAST(FLOOR(CAST(SUBSTRING("Time", 4, 2) AS INT) / 5) * 5 AS VARCHAR), 2) || ':' ||
'00'
AS StartTime,
SUBSTRING("Time", 1, 2) || ':' ||
RIGHT('00' || CAST(FLOOR(CAST(SUBSTRING("Time", 4, 2) AS INT) / 5) * 5 + 4 AS VARCHAR), 2) || ':' ||
'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;
AWS Redshift 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 public.morningstar_151_20191106_taq_sample
WHERE Messagetype = 'Quote'
AND Instrument = 'BHP'
ORDER BY
Instrument,
Date,
Time
AWS Redshift 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,
SUBSTRING("Time", 1, 2) || ':' ||
RIGHT('000' || CAST(FLOOR(CAST(SUBSTRING("Time", 4, 2) AS INT) / 5) * 5 AS VARCHAR), 2) || ':' ||
'00'
AS StartTime,
SUBSTRING("Time", 1, 2) || ':' ||
RIGHT('00' || CAST(FLOOR(CAST(SUBSTRING("Time", 4, 2) AS INT) / 5) * 5 + 4 AS VARCHAR), 2) || ':' ||
'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;
AWS Redshift 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 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 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 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";
AWS Redshift 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;