## Calculating average return price of a stock

Occasional Contributor
Posts: 6

# Calculating average return price of a stock

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
Occasional Contributor
Posts: 6

## Re: Calculating average return price of a stock

Posted in reply to 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)
Super User
Posts: 9,599

## Re: Calculating average return price of a stock

Posted in reply to Sandeep_Gupta

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;

Super User
Posts: 13,565

## Re: Calculating average return price of a stock

Posted in reply to Sandeep_Gupta

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

Valued Guide
Posts: 864

## Re: Calculating average return price of a stock

Posted in reply to Sandeep_Gupta

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?

Occasional Contributor
Posts: 6

## Re: Calculating average return price of a stock

Posted in reply to Steelers_In_DC

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

Discussion stats
• 5 replies
• 229 views
• 0 likes
• 4 in conversation