NASDAQ Level 3 Order Data

git clone git@github.com:cedardb/examples.git
cd examples/nasdaq
./prepare.sh

DROP TABLE IF EXISTS executions;
DROP TABLE IF EXISTS cancellations;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS marketMakers;
DROP TABLE IF EXISTS stocks;

CREATE TABLE stocks
(
    stockId                     int primary key,
    name                        text unique,
    marketCategory              text,
    financialStatusIndicator    text,
    roundLotSize                int,
    roundLotsOnly               bool,
    issueClassification         text,
    issueSubType                text,
    authenticity                text,
    shortSaleThresholdIndicator bool,
    IPOFlag                     bool,
    LULDReferencePriceTier      text,
    ETPFlag                     bool,
    ETPLeverageFactor           int,
    InverseIndicator            bool
);

CREATE TABLE marketmakers
(
    timestamp   bigint,
    stockId     int,
    name        text,
    isPrimary   bool,
    mode        text,
    state       text
);

CREATE TABLE orders
(
    stockId     int not null,
    timestamp   bigint not null,
    orderId     bigint primary key not null,
    side        text,
    quantity    int not null,
    price       numeric(10,4) not null,
    attribution text,
    prevOrder   bigint
);

CREATE TABLE executions
(
    timestamp   bigint not null,
    orderId     bigint,
    stockId     int not null,
    quantity    int not null,
    price       numeric(10,4)
);


CREATE TABLE cancellations
(
    timestamp   bigint not null,
    orderId     bigint not null,
    stockId     int not null,
    quantity    int
);

\i schema.sql
\copy stocks from 'stocks.csv' DELIMITER ';' CSV NULL '' ESCAPE '\' HEADER;
\copy marketMakers from 'marketMakers.csv' DELIMITER ';' CSV NULL '' ESCAPE '\' HEADER;
\copy executions from 'executions.csv' DELIMITER ';' CSV NULL '' ESCAPE '\' HEADER;
\copy orders from 'orders.csv' DELIMITER ';' CSV NULL '' ESCAPE '\' HEADER;
\copy cancellations from 'cancellations.csv' DELIMITER ';' CSV NULL '' ESCAPE '\' HEADER;

select arg_max(coalesce(e.price, o.price), e.timestamp) as price
from executions e, stocks s, orders o
where e.stockid = s.stockid 
    and e.orderid = o.orderid
    and s.name = 'AAPL';

select count(*) as new from orders where prevOrder is null;

with executions_per_order as (
    select orderid, count(*) as num from executions where orderid is not null group by orderid
)
select num as executions, count(*) from executions_per_order group by num order by num asc;

with priced_executions as(
    select e.*, coalesce(e.price, o.price) as real_price 
    from orders o, executions e 
    where e.orderId = o.orderId)
select name as ticker, quantity, real_price as price, quantity*real_price as total 
from priced_executions pe, stocks s
where pe.stockid = s.stockid
order by quantity * real_price desc limit 10;

with exec_distance as (
    select s.name as ticker, 
        o.side, coalesce(e.price, o.price) as price, 
        o.quantity as orderquant, 
        e.quantity as executedquant, 
        round((e.timestamp - o.timestamp) / (1000.0 * 1000 * 1000 * 60))::int as minutes
    from orders o, executions e, stocks s
    where o.orderId = e.orderId 
        and e.stockid = s.stockid
)
select *
from exec_distance
order by minutes desc limit 10;

#!/usr/bin/Rscript
#install.packages(c("RPostgres", "ggplot2"))
library(RPostgres)
library(ggplot2)

# Connect to CedarDB
con <- dbConnect(RPostgres::Postgres(), host="localhost", user="postgres", password="postgres", dbname="postgres")
on.exit(dbDisconnect(con))

ordersHistogram <- dbGetQuery(con, "
with limits as (
    select 34200000000000 as start, -- 9:30 AM (market open)
    57600000000000 - 1 as end, -- just before 4:00 PM (market close)
    60::bigint * 1000 * 1000 * 1000 as step -- bin size of 1 minute
), bins as (
    select generate_series(l.start, l.end, l.step) as time
    from limits l
), events as (
  select timestamp, orderid, 'exec' as type from executions
  union all
  select timestamp, orderid, 'new' as type from orders where prevOrder is null
  union all
  select timestamp, orderid, 'update' as type from orders where prevOrder is not null
  union all
  select timestamp, orderid, 'cancel' as type from cancellations
), binned as (
    select b.time, event.*
    from limits l, bins b left outer join events event on (event.timestamp >= b.time
    and event.timestamp < b.time + l.step )
)
select (time - start) / (step * 60.0) as hours, type, count(*) / 60 as eventspersecond
from binned, limits
group by hours, type
order by hours asc;
")

ggplot(ordersHistogram) + geom_line(aes(x=hours, y=eventspersecond, color=type))
ggsave("nasdaq.png", width = 8, height = 4.5, dpi = 200)