Sunday, October 5, 2014

SQL query for the total PnL for each trader, by symbol

I am cleaning up my laptop tonight, preparing to buy a new one... hopefully before this one dies. Today I found something that I had intended to include the other day when I posted this example for calculating sums across two tables:

Just like that example, this was something the same customer needed very quickly after the close, to report the PnL for each trader account, by symbol.  I've trimmed it down a bit, including only the objects necessary to collect the PnL, but here it is: 

   -- first let's create a table for our traders
   CREATE TABLE dbo.Traders (
      TraderID INT IDENTITY(1,1),
      TraderName VARCHAR(45)
   INSERT dbo.Traders (TraderID,TraderName)
   VALUES (10,'John Doe'),
              (11,'Jane Smith');

-- now let's create our daily trades table
CREATE TABLE dbo.Trades (
    TraderID INT,
    TradeID INT,
    Symbol VARCHAR(35),
    Volume BIGINT,
    Side CHAR(1)
   -- write some trades
   INSERT dbo.Trades (TraderID,TradeID,Symbol,Volume,Side)
   VALUES ( 10,1, 'AAPL', 10000,1),
                 ( 10,2, 'AAPL', 10000,2),
( 10,3, 'AAPL', -10000,2);

   -- check your data
   SELECT * FROM dbo.Trades

Ok.  Let's say closing price is 1.5.  To return the eod(end-of-day) PnL for each trader by symbol, we'd multiply each symbol's net volume by the closing price.  In this case, it's just AAPL:

  -- declarations
@price MONEY = 1.5,
@pnl MONEY

   -- collect the @pnl for AAPL
   SELECT @pnl = @price * SUM(Volume) 
   FROM dbo.Trades 
   WHERE Symbol = 'AAPL'

   -- bring it back out for each trader  - in this example we only have one trader... :-).   
   SELECT t1.TraderName, t2.Symbol, @pnl [PnL]
   FROM dbo.Traders t1 INNER JOIN dbo.Trades t2
          ON t1.TraderID = t2.TraderID
   GROUP BY t1.TraderName, t2.Symbol

Your results:

   TraderName   Symbol    PnL     
   John Doe     AAPL   15000.00

There you go.  Nothing too elaborate... just a SQL query for the closing profit and loss for each trader, by symbol.

I've mentioned before that I have virtually lived with the stock market data for almost 20 years. I have miles of tSQL; current, daily and month-to-date statistics, message ratios, PnL, open order reports, clearing... and more.  If any of you are managing trader volumes and pnl, you know what a pain it can be!  I have decided to share some of this, so you may begin seeing more logic like the one above. Let me know if you're looking for something that you don't see. I may have it already, or I can help you put it together.

No comments:

Post a Comment