DATA Step, Macro, Functions and more

Calculating average return price of a stock

Reply
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

ACCOUNT_NOSTOCK_CODEORDER_REFTRADE_DATEFLAGQTYPRICErequired_price
101NIFTY762213May2014FRESH507137.65 
101NIFTY1447422May2014FRESH507350.75 
101NIFTY1835929May2014COVER-507234.3(50*7137.65+50*7350.75)/(50+50)
101NIFTY1802605Jun2014FRESH507479.75 
101NIFTY46406Jun2014FRESH507528.75 
101NIFTY2083018Jun2014COVER-1007554.475(50*7479.75+50*7528.75)/(50+50)
101NIFTY1225319Jun2014COVER-507530.1(50*7479.75+50*7528.75+50*7137.65+50*7350.75)/(50+50=50+50)
101NIFTY1669022Jul2014FRESH1007768.4 
101NIFTY716423Jul2014FRESH1007800 
101NIFTY1607024Jul2014FRESH507834.1 
101NIFTY1172630Jul2014COVER-2507722.9(100*7768.4+100*7800+50*7834.1)
Super User
Super User
Posts: 7,392

Re: Calculating average return price of a stock

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: 10,483

Re: Calculating average return price of a stock

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: 858

Re: Calculating average return price of a stock

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 204 views
  • 0 likes
  • 4 in conversation