Calculating average return price of a stock

[Sandeep_Gupta]

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

[Sandeep_Gupta]

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)

[RW9]

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;

[ballardw]

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

[Steelers_In_DC]

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?

[Sandeep_Gupta]

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