turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Weighted Average of a stock price

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-12-2015 07:22 AM

Dear All,

I am having trouble coding the average pricing of stock.

Data

FLAG QTY PRICE AVG_PRICE CALCULATION

Buy 50 100

Buy 50 101

Buy 50 102

Sell 100 101.5 101 (50*100+50*101+50*102)/150

Buy 50 99

Sell 50 99.5 100.5 (50*100+50*101+50*102+50*99)/(150+50)

I need to generate weighted average price against flag 'S' as shown above with calculation how to calculate avg_price.

In case of first sell,I need weighted average price of all buys. total quantity purchased before first sell is 150 and quantity sold is 100.

Balance quanity is 50. To generate price against second 'Sell' we nee to take average of first 3 buys and 4th buy.

Kindly help me out.

Regards

Sandeep Gupta

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to san7981

10-12-2015 12:05 PM

Hello Sandeep,

The code here below should do the job:

data results (drop= SUM_:); set input; retain SUM_COST SUM_QTY; if _N_ = 1 then do; SUM_COST = 0; SUM_QTY = 0; end; if strip(upcase(Flag)) = 'BUY' then do; SUM_COST = SUM(SUM_COST, QTY * PRICE); SUM_QTY = SUM(SUM_QTY, QTY); AVG_PRICE = .; end; else if strip(upcase(Flag)) = 'SEL' then do; AVG_PRICE = SUM_COST / SUM_QTY; end; run;

Considering that the content of your "input" dataset has the following colomns: FLAG, QTY, PRICE.

Please let me know should there be something missing in my answer.

Regards,

Florent

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Florent

10-13-2015 02:05 AM

Hi Florent,

Thanks for a quick reply.

I am getting the desired output for a single account. But if there are multiple accounts then the code is not giving desired output.

I am attaching the sample data for multiple accounts. Kindly help me out with this problem.

Sample data is as below:

ACCOUNT_NO | TRADE_DATE | FLAG | QTY | PRICE |

1 | 3-Mar-14 | FRESH | 50 | 6233.75 |

1 | 3-Mar-14 | COVER | 50 | 6242.1 |

1 | 4-Mar-14 | FRESH | 50 | 6250.3 |

1 | 4-Mar-14 | FRESH | 50 | 6249 |

1 | 4-Mar-14 | FRESH | 50 | 6251.2 |

1 | 4-Mar-14 | COVER | 50 | 6267.8 |

1 | 4-Mar-14 | FRESH | 100 | 6269.95 |

1 | 4-Mar-14 | FRESH | 50 | 6291.65 |

1 | 4-Mar-14 | FRESH | 50 | 6303.35 |

1 | 4-Mar-14 | FRESH | 50 | 6333 |

1 | 5-Mar-14 | COVER | 350 | 6341 |

2 | 1-Apr-14 | FRESH | 200 | 6751 |

2 | 1-Apr-14 | FRESH | 100 | 6761 |

2 | 4-Apr-14 | COVER | 500 | 6739 |

2 | 4-Apr-14 | FRESH | 200 | 6769 |

2 | 9-Apr-14 | FRESH | 300 | 6795 |

2 | 9-Apr-14 | FRESH | 200 | 6828 |

2 | 15-Apr-14 | COVER | 500 | 6787 |

3 | 28-Feb-15 | FRESH | 25 | 8858 |

3 | 28-Feb-15 | COVER | 25 | 8890 |

4 | 18-Feb-15 | FRESH | 50 | 8951.375 |

4 | 26-Feb-15 | COVER | 25 | 8750 |

4 | 28-Feb-15 | COVER | 25 | 8938 |

4 | 28-Feb-15 | FRESH | 25 | 8950 |

5 | 28-Feb-15 | FRESH | 25 | 8961.7 |

5 | 28-Feb-15 | COVER | 25 | 8949.8 |

6 | 20-Feb-15 | FRESH | 50 | 8857.2 |

6 | 20-Feb-15 | COVER | 25 | 8888 |

6 | 20-Feb-15 | COVER | 25 | 8848.3 |

6 | 23-Feb-15 | FRESH | 50 | 8859.225 |

6 | 23-Feb-15 | COVER | 25 | 8863 |

6 | 23-Feb-15 | COVER | 75 | 8740 |

6 | 24-Feb-15 | COVER | 25 | 8780.8 |

6 | 24-Feb-15 | FRESH | 25 | 8743 |

6 | 26-Feb-15 | COVER | 25 | 8684.35 |

6 | 28-Feb-15 | FRESH | 25 | 8924.35 |

6 | 28-Feb-15 | COVER | 25 | 8990 |

7 | 26-Feb-15 | FRESH | 50 | 8766.775 |

Regards

Sandeep

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to san7981

10-13-2015 08:50 AM - edited 10-13-2015 08:51 AM

Hi Sandeep,

Just to make sure, are the rows with the value "FRESH" in the variable 'FLAG' to be considered as the "Buy" you mentioned in your first message ? I would expect to have a Buy as first operation for each account (you cannot sell something you haven't bought earlier in my point of view).

Regards,

Florent

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to san7981

10-13-2015 09:18 AM

Hi Florent

Fresh is buy and cover is sell.

Regards

Sandeep

Fresh is buy and cover is sell.

Regards

Sandeep

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to san7981

10-13-2015 11:55 AM - edited 10-13-2015 11:58 AM

Hi Sandeep,

Would the following version of the code do what you want ?

```
data results (drop= ACCOUNT SUM_:);
set input;
retain ACCOUNT SUM_COST SUM_QTY;
if ACCOUNT ne ACCOUNT_NO then do;
ACCOUNT = ACCOUNT_NO;
SUM_COST = 0;
SUM_QTY = 0;
end;
if strip(upcase(Flag)) = 'FRESH' then do;
SUM_COST = SUM(SUM_COST, QTY * PRICE);
SUM_QTY = SUM(SUM_QTY, QTY);
AVG_PRICE = .;
end; else
if strip(upcase(Flag)) = 'COVER' then do;
AVG_PRICE = SUM_COST / SUM_QTY;
end;
run;
```

It's important that all the data related to the same Account are consecutive (i.e. first all lines related to the account 1 then the ones of the account 2, ...etc) in your input otherwise this will not work as expected.

Regards,

Florent

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Florent

10-14-2015 01:34 AM

Dear Florent,

The code you shared is not giving the desired output. I am attaching excel containing desired output with formula to calculate the output. Let me know in case you require extra information.

ACCOUNT_NO | TRADE_DATE | FLAG | QTY | PRICE | Desired_AVG_PRICE |

1 | 3-Mar-14 | FRESH | 50 | 6233.75 | |

1 | 3-Mar-14 | COVER | 50 | 6242.1 | 6233.75 |

1 | 4-Mar-14 | FRESH | 50 | 6250.3 | |

1 | 4-Mar-14 | FRESH | 50 | 6249 | |

1 | 4-Mar-14 | FRESH | 50 | 6251.2 | |

1 | 4-Mar-14 | COVER | 50 | 6267.8 | 6250.166667 |

1 | 4-Mar-14 | FRESH | 100 | 6269.95 | |

1 | 4-Mar-14 | FRESH | 50 | 6291.65 | |

1 | 4-Mar-14 | FRESH | 50 | 6303.35 | |

1 | 4-Mar-14 | FRESH | 50 | 6333 | |

1 | 5-Mar-14 | COVER | 350 | 6341 | 6277.3 |

3 | 28-Feb-15 | FRESH | 25 | 8858 | |

3 | 28-Feb-15 | COVER | 25 | 8890 | 8858 |

4 | 18-Feb-15 | FRESH | 50 | 8951.375 | |

4 | 26-Feb-15 | COVER | 25 | 8750 | 8951.375 |

4 | 28-Feb-15 | COVER | 25 | 8938 | 8951.375 |

4 | 28-Feb-15 | FRESH | 25 | 8950 | |

5 | 28-Feb-15 | FRESH | 25 | 8961.7 | |

5 | 28-Feb-15 | COVER | 25 | 8949.8 | 8961.7 |

6 | 20-Feb-15 | FRESH | 50 | 8857.2 | |

6 | 20-Feb-15 | COVER | 25 | 8888 | 8857.2 |

6 | 20-Feb-15 | COVER | 25 | 8848.3 | 8857.2 |

6 | 23-Feb-15 | FRESH | 50 | 8859.225 | |

6 | 23-Feb-15 | COVER | 25 | 8863 | 8859.225 |

6 | 23-Feb-15 | COVER | 25 | 8740 | 8859.225 |

6 | 24-Feb-15 | FRESH | 25 | 8743 | |

6 | 26-Feb-15 | COVER | 25 | 8684.35 | 8743 |

6 | 28-Feb-15 | FRESH | 25 | 8924.35 | |

6 | 28-Feb-15 | COVER | 25 | 8990 | 8924.35 |

7 | 26-Feb-15 | FRESH | 50 | 8766.775 |

Regards

Sandeep

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to san7981

10-14-2015 01:41 PM

Hello Sandeep,

I don't understand why the logic to calculate the DESIRED_AVG_PRICE of the cell F7 of your Excel does not take into account the QTY and PRICE of the of row 2, whereas the calculation of the DESIRED_AVG_PRICE of the cell F12 of your Excel takes into account the QTY and PRICE values of the rows 4 to 6. It seems to me that the logic is not in aligned...

I would expect to find 6246.0625 as DESIRED_AVG_PRICE in the cell F7 of your excel (calculated as with the formula =(D4*E4+D5*E5+D6*E6+D2*E2)/200).

Kr,

Florent

I don't understand why the logic to calculate the DESIRED_AVG_PRICE of the cell F7 of your Excel does not take into account the QTY and PRICE of the of row 2, whereas the calculation of the DESIRED_AVG_PRICE of the cell F12 of your Excel takes into account the QTY and PRICE values of the rows 4 to 6. It seems to me that the logic is not in aligned...

I would expect to find 6246.0625 as DESIRED_AVG_PRICE in the cell F7 of your excel (calculated as with the formula =(D4*E4+D5*E5+D6*E6+D2*E2)/200).

Kr,

Florent

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Florent

10-23-2015 05:36 AM

Hi Florent,

Please find the attached excel. I have mentioned steps how to calculate weighted avg price. Kindly let me know if have any doubts regarding data or logic.

Regards

Sandeep

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to san7981

01-08-2016 12:36 PM

Hi Sandeep,

Sorry for the delay but I had a lot to do on my projects.

I don't know if you have solved your problem in the meantime but I'll still give it a last try. Please find below a new SAS program which, according to the logic I saw in your Excel, should match with your expectations.

Do not hesitate to comment the DROP statements in the code so that you can have a look at the temporary variables that are created. Also, adding PUT statements before, inside, and after the DO loops may help in visualizing what is happening during the execution of the datasteps.

Kind regards,

Florent

```
data tmp_Results (drop= tmp_: len_:);
set input;
length List_Row List_Qty List_Price $1000 tmp_Row $100;
retain List_Row List_Qty List_Price;
tmp_row_num = _N_;
tmp_Total_Qty = Qty;
/* To keep exactly the same amount of rows as in the excel example */
if tmp_row_num <= 22 then do;
/* In case of Fresh, add the values to the lists */
if strip(upcase(Flag)) = 'FRESH' then do;
List_Row = strip(List_Row) || strip(put(tmp_row_num, best12.))|| ';';
List_Qty = strip(List_Qty) || strip(put(Qty, best12.))|| ';';
List_Price = strip(List_Price) || strip(put(Price, best12.))|| ';';
end; else
/* In case of Cover, remove values from the lists */
if strip(upcase(Flag)) = 'COVER' then do;
do until (tmp_Total_Qty <= 0);
tmp_Row = strip(scan(List_Row, 1, ';') || ';');
tmp_Qty = input(scan(List_Qty, 1, ';'), best12.);
tmp_Price = strip(scan(List_Price, 1, ';') || ';');
len_Price = lengthn(strip(tmp_Price));
len_Qty = lengthn(strip(scan(List_Qty, 1, ';') || ';'));
if tmp_Qty >= tmp_Total_Qty and tmp_Total_Qty > 0 then do;
tmp_Total_Qty = tmp_Total_Qty - tmp_Qty;
if tmp_Total_Qty >= 0 then do;
List_Row = strip(tranwrd(List_Row, strip(tmp_Row), ''));
List_Qty = strip(substr(List_Qty, len_Qty+1));
List_Price = strip(substr(List_Price, len_Price+1));
end; else
if tmp_Total_Qty < 0 then do;
List_Qty = strip(put(-tmp_Total_Qty, best12.)) || ';' || strip(substr(List_Qty, len_Qty+1));
end;
end; else
if tmp_Total_Qty > tmp_Qty and tmp_Total_Qty > 0 then do;
tmp_Total_Qty = tmp_Total_Qty - tmp_Qty;
List_Row = strip(tranwrd(List_Row, strip(tmp_Row), ''));
List_Qty = strip(substr(List_Qty, len_Qty+1));
List_Price = strip(substr(List_Price, len_Price+1));
end;
end;
end;
output tmp_Results;
end;
run;
data Results (drop= List_: tmp_:);
set tmp_Results;
List_Row = lag1(List_Row);
List_Qty = lag1(List_Qty);
List_Price = lag1(List_Price);
if strip(upcase(Flag)) = 'COVER' then do;
tmp_nb_items = countw(List_Row);
tmp_numerator = 0;
tmp_denominator = 0;
do tmp_i=1 to tmp_nb_items;
tmp_Qty = input(scan(List_Qty, tmp_i, ';'), best12.);
tmp_Price = input(scan(List_Price, tmp_i, ';'), best12.);
tmp_numerator = sum(tmp_numerator, tmp_Qty * tmp_Price);
tmp_denominator = sum(tmp_denominator, tmp_Qty);
end;
avg_price = divide(tmp_numerator, tmp_denominator);
end;
run;
```