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'
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