2020.01.27

Sale condition Source: adopted from here

Introduction¶

NYSE TAQ data provides T+1 trades reported to SIP and each trade has some basic information like timestamp, price, exchange, volume and trade qualifiers. The open/close auction trade on the primary exchange is marked by qualifiers 0 and 6, respectively. Define the daily volume as the total volume traded between the open/close auction trades on the primary, inclusively.

genTrades:{[seed;nTrades]
    / Randomly generate each trade's timestamp
    system "S ",string seed;
    times:`time$09:28:00.000+nTrades?392*60*1000;

    / Randomly generate trade size
    system "S ",string seed;
    volumes:`long$100*1+nTrades?10;

    / Randomly generate each trade's sale condition
    system "S ",string seed;
    saleConditions:{x?`B`C`H`L`N`P`R`T`U`V`X`Z`7`4`5`8} each nTrades?3;

    / Create a table of trades
    trades:([] time:times;volume:volumes;saleCondition:saleConditions);

    / Add the open and close auction trades
    trades:trades upsert (`time$09:30:00.000+rand 60000;24700j;`O`X);
    trades:trades upsert (`time$16:00:00.000+rand 2000;53800j;enlist `6);

    / Sort the trades time by time
    `time xasc trades
  };
simTrades:genTrades[-314159;10000];
simTrades

Question¶

To simplify the question, it is assumed that open/close auction trades are ALWAYS present. The function genTrades above simulates 10,002 trades. Note that each trade has up to two sale condition codes. Find the total volume between the two auction trades, inclusively.

Answer¶

We just need to find the timestamp of the two auction trades and then sum up volume of all trades between these two auction trades. This can be done like:

auctionTimes:exec time from simTrades where any each saleCondition like\: "*[O6]*";
select sum volume from simTrades where time within auctionTimes

A few notable points worth mentioning:

any each saleCondition like\: "*[O6]*": This where clause identifies the open/close auction trades.
exec time from simTrades...: Extract the open and close auction time as a list of two elements. The first element is the time for open auction trade and the second is for close auction trade.