help in lookup

  • Thread starter Thread starter Krishna
  • Start date Start date
K

Krishna

Hi,
I have a worksheet that updates positions for the stocks traded
everyday and calculates the profit.For every stock traded I have two
rows of data.I need help in summing up enter and exit positions.Here
is the format of the data.

Date stock name entryqty entry price exit qty exit price
qty_close
01/7/2004 buy Microsoft 10 5 o
02/7/2004 sell Microsoft 0 0 10 6
0

the idea is to check if the stock has been sold from a different sheet
,then check the qty at entry in the active sheet,check the qty at exit
and sum up.
If the qty at entry and exit do not match ,the absolute value of the
difference should be posted.

thanks in advance for all the help.

krishna
 
Hi

I think you did overdo a bit with compexity here.

Consider next setup:
Date, StockName, Transaction, Qty, Price

Your data will then be like:
01/7/2004 Microsoft buy 10 5
02/7/2004 Microsoft sell 10 6

you can add an additional column for running stock saldo
p.e. with StockSaldo in column E, enter into E2
=SUMPRODUCT((B$2:B2=B2)*(C$2:C2="sell")*(D$2:D2))-SUMPRODUCT((B$2:B2=B2)*(C$
2:C2="buy")*(D$2:D2))
but you can calculate it also on separate sheet - maybe on one where you
keep your stocks list - fixed foe current date maybe
=SUMPRODUCT((StockName="Microsoft")*(Transaction="sell")*(Quantity))-SUMPROD
UCT((StockName="Microsoft")*(Transaction="buy")*(Quantity))
where StockName, Transaction and Quantity are dynamic named ranges based on
transactions table

Likewise you can calculate all other data (like profit, or stock quantities
buyed or sold in some time interval, or average price for remaining stock
etc.)
 
Back
Top