topic Re: Calculating average return price of a stock in SAS Programming

Calculating average return price of a stock

Sandeep_Gupta — Thu, 24 Sep 2015 11:17:31 GMT

Hi Everyone, I am having trouble calculating weighted average return from the stock. I want required_price to be returned wherever FLAG is 'COVER' as shown below: ACCOUNT_NO STOCK_CODE ORDER_REF TRADE_DATE FLAG QTY PRICE required_price 101 NIFTY 7622 13May2014 FRESH 50 7137.65 101 NIFTY 14474 22May2014 FRESH 50 7350.75 101 NIFTY 18359 29May2014 COVER -50 7234.3 (50*7137.65+50*7350.75)/(50+50) 101 NIFTY 18026 05Jun2014 FRESH 50 7479.75 101 NIFTY 464 06Jun2014 FRESH 50 7528.75 101 NIFTY 20830 18Jun2014 COVER -100 7554.475 (50*7479.75+50*7528.75)/(50+50) 101 NIFTY 12253 19Jun2014 COVER -50 7530.1 (50*7479.75+50*7528.75+50*7137.65+50*7350.75)/(50+50+50+50) 101 NIFTY 16690 22Jul2014 FRESH 100 7768.4 101 NIFTY 7164 23Jul2014 FRESH 100 7800 101 NIFTY 16070 24Jul2014 FRESH 50 7834.1 101 NIFTY 11726 30Jul2014 COVER -250 7722.9 (100*7768.4+100*7800+50*7834.1) Regards Sandeep Gupta

Re: Calculating average return price of a stock

Sandeep_Gupta — Thu, 24 Sep 2015 12:00:19 GMT

ACCOUNT_NO	STOCK_CODE	ORDER_REF	TRADE_DATE	FLAG	QTY	PRICE	required_price
101	NIFTY	7622	13May2014	FRESH	50	7137.65
101	NIFTY	14474	22May2014	FRESH	50	7350.75
101	NIFTY	18359	29May2014	COVER	-50	7234.3	(507137.65+507350.75)/(50+50)
101	NIFTY	18026	05Jun2014	FRESH	50	7479.75
101	NIFTY	464	06Jun2014	FRESH	50	7528.75
101	NIFTY	20830	18Jun2014	COVER	-100	7554.475	(507479.75+507528.75)/(50+50)
101	NIFTY	12253	19Jun2014	COVER	-50	7530.1	(507479.75+507528.75+507137.65+507350.75)/(50+50=50+50)
101	NIFTY	16690	22Jul2014	FRESH	100	7768.4
101	NIFTY	7164	23Jul2014	FRESH	100	7800
101	NIFTY	16070	24Jul2014	FRESH	50	7834.1
101	NIFTY	11726	30Jul2014	COVER	-250	7722.9	(1007768.4+1007800+50*7834.1)

Re: Calculating average return price of a stock

RW9 — Thu, 24 Sep 2015 12:22:06 GMT

Sorry, what is the question. You seem to have it all there.

data want;

set have;

retain inter;

inter=qty*price;

if flag="COVER" then do;

required_price=inter;

inter=.;

end;

run;

This will get you some of the way, however I don't see why the Cover in the 7th row should have all that formula, maybe as it follows another cover row, if so then just update the logic above:

data want;

set have;

retain inter;

if flag="COVER" and lag(flag) ne "COVER then inter=.;

inter=qty*price;

if flag="COVER" then do;

required_price=inter;

end;

run;

Re: Calculating average return price of a stock

Steelers_In_DC — Thu, 24 Sep 2015 13:36:57 GMT

It looks like you are leaving out some important information. Why does the first and second cover have two fresh calculations a piece but the third has 4 and the 4th has 3?

Re: Calculating average return price of a stock

ballardw — Thu, 24 Sep 2015 15:30:25 GMT

It appears that you have two heirarchies of data. I would consider adding a variable that defines which group is being looked

Re: Calculating average return price of a stock

Sandeep_Gupta — Mon, 28 Sep 2015 04:40:47 GMT

Hi,

I want to put weighted average price in front of cover. On 29may the required price will be weighted average of price on 13th and 22nd may2015.

Regards

Sandeep