Monday, October 13, 2014

SQL Aggregate functions for count & quantity traded

You're managing a lot of trading data, and you've been asked to provide some statistics back to the trading desks. There are numerous different types of statistics that you could provide, all using SQL Server aggregate functions such as COUNT, SUM, AVG, etc. Today I am going to show a very simple example for reporting how many buys and sells they are trading on each symbol.

First, we need an Orders table that all of trades are written to.  Very simple design, like this:

CREATE TABLE dbo.Orders (
OrderID INT IDENTITY(1,1),
TraderID CHAR (12) NOT NULL,
Exchange CHAR (8) NOT NULL,
Symbol CHAR (32) NOT NULL,
Side CHAR (1) NOT NULL,
Quantity BIGINT NOT NULL ,
Price DECIMAL(18, 8) NOT NULL ,
OrderDate DATETIME NOT NULL 
) ON YourDB_Data
GO

I should note, MUCH better practices would be a numeric identifier for Exchange, Symbol and even Side. But... then I'd have to do all kinds of JOINs to get the data back to you meaningfully. For this example, we can ignore the performance impediments of character strings vs integers.  ;-)

For the insert into Orders, I am only going to use a small chunk of orders by only one trader, John Doe.

-- load some orders
SET IDENTITY_INSERT Orders ON
INSERT dbo.Orders (OrderID,TraderID,Exchange,Symbol,Side,Quantity,Price,OrderDate)
VALUES
(1,10,'NYSE','AAPL','B',55,12.45,'10/13/2012 09:30'),
(2,10,'NSDQ','YHOO','B',50,61.75,'10/13/2012 10:30'),
(3,10,'CME','AAPL','B',5,17.50,'10/13/2012 09:00'),
(4,10,'NYSE','DDM9','S',5,1.75,'10/13/2012 08:30'),
(5,10,'NSDQ','ABS','B',3,66.502,'10/13/2012 09:35'),
(61,10,'NYSE','BEAS','B',55,1.75,'10/13/2012 12:20'),
(7,10,'NSDQ','BEAS','S',100,1.025,'10/13/2012 12:55'),
(8,10,'CME','CAG','B',10,11.53,'10/13/2012 13:30'),
(9,10,'CSX','YHOO','S',10,17.705,'10/13/2012 16:55'), 
(10,10,'NYSE','YHOO','S',50,10.45,'10/13/2012 10:30'),
(11,10,'NSDQ','YHOO','S',55,60.75,'10/13/2012 11:30'),
(12,10,'CME','YHOO','S',5,19.50,'10/13/2012 10:00');
SET IDENTITY_INSERT Orders OFF

Ok. We've got some order data for trader John Doe. With the query below, you will return a count for how many orders he's traded, and how much quantity was for buys vs sells. Basically, counts and quantities for each trader, by symbol and side ('side' is trading speak for buy or sell).

  DECLARE 
      @OrderDate DATE = '10/13/2012',
@TraderID INT =  10

  -- collect the trade count (#) and Qty for each trader, by symbol
  SELECT 
CONVERT(VARCHAR(10),o.OrderDate,101)  [Date],
t.TraderName,
o.Symbol,
COUNT(*) [#Trades],
LTRIM(RTRIM(LEFT(CONVERT(CHAR(16),CAST(SUM(CASE WHEN o.Side = 'b'
          THEN o.Quantity ELSE 0 END) AS MONEY),1),13))) [BuyQty],
LTRIM(RTRIM(LEFT(CONVERT(CHAR(16),CAST(SUM(CASE WHEN o.Side = 's'
          THEN o.Quantity ELSE 0 END) AS MONEY),1),13))) [SellQty]
  FROM 
dbo.Orders o INNER JOIN dbo.Traders t
         ON o.TraderID = t.TraderID
  WHERE 
CONVERT(VARCHAR(10),o.OrderDate,101) = @OrderDate
AND o.TraderID = @TraderID
  GROUP BY 
CONVERT(VARCHAR(10),o.OrderDate,101),
t.TraderName,
o.Symbol
  ORDER BY
CONVERT(VARCHAR(10),o.OrderDate,101),
o.Symbol

The results:

     OrderDate   Trader   Symbol   #Trades  BuyQty   SellQty
     10/13/2012  John Doe   AAPL     2      60        0
     10/13/2012  John Doe    ABS     1       3        0
     10/13/2012  John Doe   BEAS     2      55      100
     10/13/2012  John Doe    CAG     1      10        0
     10/13/2012  John Doe   DDM9     1       0        5
     10/13/2012  John Doe   YHOO     5      50      120

I added these two statements after the one above just to help me confirm that it's accurate. Looking at the raw and the agg data side by side makes it easier to validate:

    SELECT COUNT(*) FROM dbo.Orders WHERE TraderID = 10 AND Symbol = 'AAPL'
    SELECT * FROM dbo.Orders WHERE TraderID = 10 AND Symbol = 'ABS' AND Side = 'b'

Like I said, there are many different ways to report the data using SQL Server's aggregate functions. This is just one example. I'll try to come back soon with more. Until then, take a look at this, for more detail:


No comments:

Post a Comment