March 8, 2012 at 12:18 pm
Hi,
I am newbee and i need help in writing a stored procedure that calculates holding period for my securities (stocks). I have two tables, the first table (trade_table) contains the trading information how many shares bought in each transaction and what symbol and what time etc. The second table (pnl_table) contains the consolidated P&L (i.e. trader may bought 500 shares of MSFT then buy another 1000 shares in the next transaction. P&L are consolidated at security level).
I want to write a stored procedure to get holding period and pnl for any given trader and for all traders. By holding period i mean if he bought and sold in 0min - 1hr, 1hr - 2hr, 2hr-day and >day.
trade_table has these columns:
time, account, side, symbol, quantity, price, etc
09:31:20 abcd123 buy MSFT 500 31.20
10:12:14 abcd123 buy MSFT 1000 31.11
12:40:12 abcd123 sell MSFT 800 32.07
15:35:48 abcd123 sell MSFT 700 31.01
P&L table
Account symbol position pnl
abcd123 MSFT 0 653
Note that starttime is first time trader buys a stock and when he sells all his shares of MSFT then position in pnl table become 0 that will be endtime. If the difference between endtime-startime less than 1hr then it will belong to 0-1hr bucket, if 1hr<x<2hr then second bucket etc. We want to find pnl for those trades where position becomes 0.
I really appreciate your help. I know how to do this in matlab or c++. I can give you pseudo code but I new to stored procedures can some one help me with this, I really appreciate your help.
Thanks
AP
March 8, 2012 at 12:27 pm
junkid1376 (3/8/2012)
Hi,I am newbee and i need help in writing a stored procedure that calculates holding period for my securities (stocks). I have two tables, the first table (trade_table) contains the trading information how many shares bought in each transaction and what symbol and what time etc. The second table (pnl_table) contains the consolidated P&L (i.e. trader may bought 500 shares of MSFT then buy another 1000 shares in the next transaction. P&L are consolidated at security level).
I want to write a stored procedure to get holding period and pnl for any given trader and for all traders. By holding period i mean if he bought and sold in 0min - 1hr, 1hr - 2hr, 2hr-day and >day.
trade_table has these columns:
time, account, side, symbol, quantity, price, etc
09:31:20 abcd123 buy MSFT 500 31.20
10:12:14 abcd123 buy MSFT 1000 31.11
12:40:12 abcd123 sell MSFT 800 32.07
15:35:48 abcd123 sell MSFT 700 31.01
P&L table
Account symbol position pnl
abcd123 MSFT 0 653
Note that starttime is first time trader buys a stock and when he sells all his shares of MSFT then position in pnl table become 0 that will be endtime. If the difference between endtime-startime less than 1hr then it will belong to 0-1hr bucket, if 1hr<x<2hr then second bucket etc. We want to find pnl for those trades where position becomes 0.
I really appreciate your help. I know how to do this in matlab or c++. I can give you pseudo code but I new to stored procedures can some one help me with this, I really appreciate your help.
Thanks
AP
Please see the article by Jeff Moden in my signature so that we can help you easier.
Jared
CE - Microsoft
March 8, 2012 at 3:51 pm
Here is what I have done, I am sure there is much better way of doing it. Since I am used to matlab I wrote is similar style.
CREATE PROCUDURE apex_holdingpnl
@startdate datetime,
@enddate datetime,
@account varchar(99),
@starttime varchar(99),
@endtime varchar(99)
@position int,
@holdingPeriod varchar(99)
// want to iterate through the number of rows in pnl_table
iter=1
AS
WHILE (tier < = (SELECT count(position) FROM pnl_table))
BEGIN;
if (SELECT position FROM pnl_table) = 0
BEGIN;
SELECT
pnl_table.symbol, pnl_table.pnl, pnl_table.account, pnl_table.date
FROM
pnl_table
SELECT
trade_table.date, trade_table.symbol, trade_table.time
WHERE
pnl_table.account = trade_table.account
AND
pnl_table.symbol=trade_table.symbol
AND
trade_table.date = pnl_table.date
IF(count(side) == 1)
holdingPeriod = gtrDay
ELSE
IF((SELECT SUM(Exe Qty) FROM trade_table WHERE Side = 'BUY' )== (SELECT SUM(Exe Qty) FROM trade_table WHERE Side IN ('SELL', 'SELL SHORT'))
BEGIN;
IF(COUNT(Side)>=1)
BEGIN;
@starttime = min(time)
@endtime = max(time)
SELECT
(cast(@endtime as float) - floor(cast(@endtime as float)) - (cast(@starttime as float) - floor(cast(@starttime as float))
AS
difference
IF(difference) < 60 // I know this won't work
holdingPeriod = lessthan1hr
ELSE
IF(difference) > 60 AND (difference) < 120)
holdingPeriod = 1to2hr
ELSE
holdingPeriod = day
END;
END;
END;
ELSE
CONTINUE;
END;
O am not sure what happened to my alignment.
March 8, 2012 at 3:58 pm
junkid1376 (3/8/2012)
Here is what I have done, I am sure there is much better way of doing it. Since I am used to matlab I wrote is similar style.
CREATE PROCUDURE apex_holdingpnl
@startdate datetime,
@enddate datetime,
@account varchar(99),
@starttime varchar(99),
@endtime varchar(99)
@position int,
@holdingPeriod varchar(99)
// want to iterate through the number of rows in pnl_table
iter=1
AS
WHILE (tier < = (SELECT count(position) FROM pnl_table))
BEGIN;
if (SELECT position FROM pnl_table) = 0
BEGIN;
SELECT
pnl_table.symbol, pnl_table.pnl, pnl_table.account, pnl_table.date
FROM
pnl_table
SELECT
trade_table.date, trade_table.symbol, trade_table.time
WHERE
pnl_table.account = trade_table.account
AND
pnl_table.symbol=trade_table.symbol
AND
trade_table.date = pnl_table.date
IF(count(side) == 1)
holdingPeriod = gtrDay
ELSE
IF((SELECT SUM(Exe Qty) FROM trade_table WHERE Side = 'BUY' )== (SELECT SUM(Exe Qty) FROM trade_table WHERE Side IN ('SELL', 'SELL SHORT'))
BEGIN;
IF(COUNT(Side)>=1)
BEGIN;
@starttime = min(time)
@endtime = max(time)
SELECT
(cast(@endtime as float) - floor(cast(@endtime as float)) - (cast(@starttime as float) - floor(cast(@starttime as float))
AS
difference
IF(difference) < 60 // I know this won't work
holdingPeriod = lessthan1hr
ELSE
IF(difference) > 60 AND (difference) < 120)
holdingPeriod = 1to2hr
ELSE
holdingPeriod = day
END;
END;
END;
ELSE
CONTINUE;
END;
O am not sure what happened to my alignment.
Just as I did in the quote above, you need to surround your code with IFCode tags [ code="sql" ] and [ /code ] (without the spaces inside the brackets).
March 8, 2012 at 4:27 pm
Thanks Lynn Pettis for the alignment.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply