tick_qry6.sql

Finds the 10 stocks with the highest percentage spreads.

Spread is the difference between the last ask-price and the last bid-price. Percentage spread is calculated as a percentage of the bid-point price (average of ask and bid price).

Output

The rows returned by this query are:

TRADING_SYMBOL          PER
ARG                     0.005943536404160475482912
ACV                     0.005161802660313678777049
BEY                     0.004888481515429269783073
AJE                     0.004440649017933390264730
AMX                     0.004440649017933390264730
AUM                     0.004429678848283499446290
AKO                     0.004239084357778719796523
AUN                     0.004237288135593220338983
BED                     0.004135338345864661654135
AJM                     0.004063134864822628535708

SQL Statements

The following script contains the SQL statements for this query. Note that there are separate scripts optimized for RAPCache and RAPStore.

RAPStore Database Script

-- Find the 10 stocks with the highest percentage spreads.
-- Spread is the difference between the last ask-price and
-- the last bid-price.
-- Percentage spread is calculated as a percentage of the
-- bid-point price (average of ask and bid price).

-- This query is optimized to run on IQ.

commit
;
SELECT TOP 10 TRADING_SYMBOL, PER, RANK() OVER ( ORDER BY per DESC)
AS PER_RANK
FROM   (SELECT a.INSTRUMENT_ID, a.TRADING_SYMBOL,(ap-bp)*2/(ap+bp)
AS per
FROM   (SELECT INSTRUMENT_ID, TRADING_SYMBOL, BID_PRICE as bp
FROM STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME)
AS maxtime
from STOCK_QUOTE where QUOTE_DATE = '2005-11-14'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
group by INSTRUMENT_ID) y
WHERE st.QUOTE_DATE = '2005-11-14'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
			       AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID
	 group by INSTRUMENT_ID,TRADING_SYMBOL, BID_PRICE) a,
(SELECT INSTRUMENT_ID,TRADING_SYMBOL, ASK_PRICE AS ap
FROM STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME)
AS maxtime
from STOCK_QUOTE where QUOTE_DATE = '2005-11-14'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
group by INSTRUMENT_ID) x
WHERE  st.QUOTE_DATE = '2005-11-14'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID
	group by INSTRUMENT_ID, TRADING_SYMBOL, ASK_PRICE) b
WHERE  a.INSTRUMENT_ID=b.INSTRUMENT_ID
)c 
ORDER BY PER DESC
;

RAPCache Database Script

-- Find the 10 stocks with the highest percentage spreads.
-- Spread is the difference between the last ask-price and
-- the last bid-price.
-- Percentage spread is calculated as a percentage of the
-- bid-point price (average of ask and bid price).

-- This query will run on either the ASE or IQ platform.

set  rowcount 10
go

SELECT a.TRADING_SYMBOL, (ap-bp)*2/(ap+bp) AS PER
FROM   (SELECT INSTRUMENT_ID,TRADING_SYMBOL, BID_PRICE as bp
FROM STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME)
AS maxtime
from STOCK_QUOTE 
		   where 
		   QUOTE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
group by INSTRUMENT_ID,
TRADING_SYMBOL) y
WHERE
QUOTE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
AND BID_PRICE IS NOT NULL
AND BID_PRICE <> 0 
	       AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID
	       group by INSTRUMENT_ID, TRADING_SYMBOL,BID_PRICE) a,
(SELECT INSTRUMENT_ID, TRADING_SYMBOL, ASK_PRICE AS ap
FROM   STOCK_QUOTE st, (Select INSTRUMENT_ID AS idx, max(QUOTE_TIME)
AS maxtime
from STOCK_QUOTE 
		   where 
QUOTE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
group by INSTRUMENT_ID,
TRADING_SYMBOL) x
WHERE 
QUOTE_TIME between '2005-11-14 00:00:00' and '2005-11-14 23:59:59'
AND ASK_PRICE IS NOT NULL
AND ASK_PRICE <> 0 
AND st.QUOTE_TIME = maxtime and idx = st.INSTRUMENT_ID
group by INSTRUMENT_ID, TRADING_SYMBOL,ASK_PRICE) b
WHERE  a.INSTRUMENT_ID=b.INSTRUMENT_ID
ORDER BY PER DESC

go
set  rowcount 0
go