0

I'm currently working on calculating markouts for some post trade analysis. In short, a markout is the change between prices over a time interval.

I have two tables, FILLORDERACK and NBBO_20181001.

FILLORDERACK contains the following columns:

BIDPX, OFFERPX, TRANSACTTIME, TRADEDATE, SYMBOL

NBBO_20181001 contains the following columns:

BID, ASK, SYMBOL, TS

The data in NBBO_20181001 is a few million records of bid and offer prices for stocks in millisecond intervals.

What my query currently does it take a symbol and calculate the price markout at 1000 milliseconds after the trade:

i.e: Get the midpoint of the prices from FILLORDERACK, divide it by the midpoint of the prices at the closest TS from NBBO_20181001, and subtract 1 from the value.

I currently have it working but for only 1 specific occurrence where t2.symbol = 'F'.

I'm unsure how to append my query to work for all occurrences of a given symbol in the F from FILLORDERACK. If I have 200 rows in this table for that symbol, I should have 200 markouts calculated in the results. 

select (((t2.BIDPX + t2.OFFERPX) / 2) / ((t1.BID + t1.ASK) / 2)) - 1, t2.TRANSACTTIME
from NBBO_20181001 t1, FILLORDERACK t2
where t2.symbol = 'F' and t1.symbol = t2.symbol and t2.TRADEDATE = '20181001'
and t1.TS between t2.TRANSACTTIME and TIMESTAMPADD(SQL_TSI_ms, 1000, (t2.TRANSACTTIME))
order by t1.TS desc limit 1
Harrison
  • 5,095
  • 7
  • 40
  • 60
  • Your question is unclear and too broad from a bird's eye point of view. Please try to provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) – Madhur Bhaiya Nov 01 '18 at 17:58
  • Also, Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Nov 01 '18 at 17:59

0 Answers0