MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula Problem

Posted by Mo on June 07, 2001 10:59 AM

Hello, I have this problem which I will try to simplify. For the sake of clarity lets just assume 4 columns. First column is Stock name, second column is bought/sold, this column is filled as B or S. Third column is amount of shares bought, and the fourth column is value, i.e amount of shares multiplied by price. If anyone wants a sample of the data to analyse, I’ll be more than happy to supply.

What I’m trying to do is calculate profit and loss. Clients buy and sell a particular stock many times over a period of time, sometimes buying same stock 3 times in different days, and then sell them in six small chunks, so to get a profit/loss, one needs to make sure shares sold are equal to shares bought, i.e I only want to calculate when a position is closed, sometime shares sold are not equal to shares bought, i.e the position is still open.

So is there a formula that will look for a stock, add all shares bought for that particular stock, see if it matches with shares sold. If so add the value of shares sold and minus the value of shares bought.
Is this possible or am I taking liberties? Lol.

PS: if this problem is unsolvable, please say it too.

Posted by IML on June 07, 2001 11:20 AM

One option would be to use subtotals. You add a column and use an if statment saying if stocks are sold show negative shares.

By formula you could use this array formula (hit control shift enter)

=IF(SUM((A17=stock)*("B"=bs)*shares)-SUM((A17=stock)*("s"=bs)*shares)=0,SUM((A17=stock)*("B"=bs)*value)-SUM((A17=stock)*("s"=bs)*value),"Position Open"

where stock is your stock range, BS is bought or sold range, shares is shares traded and value is value.

It also assumes you enter the stock you are interested in A17. This would also work for all stock if you had a summary of holdings.

Good luck.

Posted by IML on June 08, 2001 7:36 AM


Sorry, this shows gains as losses and vice versa, here is the correction
=IF(SUM((A17=stock)*("B"=bs)*shares)-SUM((A17=stock)*("s"=bs)*shares)=0,SUM((A17=stock)*("s"=bs)*value)-SUM((A17=stock)*("b"=bs)*value),"Position Open")

Posted by Aladin Akyurek on June 08, 2001 4:01 PM


Grown curious about your data and its domain. Would you post a snippet here?