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: http://www.sqlfingers.com/2014/09/calculate-sum-across-two-tables.html
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)
)
SET IDENTITY_INSERT dbo.Traders ON
INSERT dbo.Traders (TraderID,TraderName)
VALUES (10,'John Doe'),
(11,'Jane Smith');
SET IDENTITY_INSERT dbo.Traders OFF;
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
DECLARE @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