Weighted Average - Challenges

Hello There,

I was just working on a project of creating a database for a relative that have alots of clients. His company managed the shares portfolio of clients and he requires the buy price of each stock in a particular client accounts to be weighted averaged every time there is a purchase in stock, but use the previous weighted average for a sold in stock.

say:

For Stock XYZ's, in A Portfolio
1. Buy 1000 shares of XYZ @ $2
2. Buy 1000 shares of XYZ @ $2.50
3. Sell 500 shares of XYZ @ $2
4. Buy 1000 shares of XYZ @ $1

Weighted Average Price:
1. 1000 shares $2
2. 2000 shares $2.25
3. 1500 shares $2.25
4. 2500 shares $1.75

I look forward for your help. thank you very much in advanced

Sincerely


Edmund