Opening Cross

Question: Every morning, NYSE determines the opening price for all securities based on their opening auction. For each ticker, they select the auction price that will match the most buy and sell orders. If there are more orders on one side, then there is an order imbalance. Given the auction quotes below, compute the opening price, matched quantity, and imbalance quantity for each security. The imbalance quantity can be positive or negative. Positive means there are more buy orders than sell, and vice versa. The expected columns are ticker, px, matchedQty, imblQty.

More Information:

https://www.nyse.com/markets/nyse-american/trading-info

Example

                                
                                q)auction:`time xasc flip `time`ticker`side`px`qty!raze each flip ((asc 09:30-"v"$til 100;100#`AAPL;(70#`b),30#`a;raze 10 20 70#'289+-.5 0 .5;200+"j"$100*sin cos til 100);(asc 09:30-"v"$til 100;100#`MSFT;(35#`b),65#`a;raze 10 70 20#'174+-1.5 0 1.5;200+"j"$100*sin til 100);(asc 09:30-"v"$til 100;100#`IBM;(50#`b),50#`a;raze 100#121.7;200+"j"$100*cos til 100))
q)auction
time     ticker side px    qty
------------------------------
09:28:21 AAPL   b    288.5 284
09:28:21 MSFT   b    172.5 200
09:28:21 IBM    b    121.7 300
09:28:22 AAPL   b    288.5 251
09:28:22 MSFT   b    172.5 284
09:28:22 IBM    b    121.7 254
09:28:23 AAPL   b    288.5 160
09:28:23 MSFT   b    172.5 291
09:28:23 IBM    b    121.7 158
09:28:24 AAPL   b    288.5 116
09:28:24 MSFT   b    172.5 214
09:28:24 IBM    b    121.7 101
09:28:25 AAPL   b    288.5 139
09:28:25 MSFT   b    172.5 124
09:28:25 IBM    b    121.7 135
09:28:26 AAPL   b    288.5 228
09:28:26 MSFT   b    172.5 104
09:28:26 IBM    b    121.7 228
09:28:27 AAPL   b    288.5 282
09:28:27 MSFT   b    172.5 172
..

==== Expected ====

ticker px    matchedQty imblQty
-------------------------------
AAPL   289.5 5889       2230
IBM    121.7 9977       -3
MSFT   174   5000       -3956
                                
                            

Solution

Tags:
finance sql tables
Searchable Tags
algorithms api architecture csv data structures dictionaries disk feedhandler finance functions ingestion ipc iterators math multithreading optimizations realtime sql streaming strings tables temporal websockets

Email sent!

Email not sent