stepan1987
Board Regular
- Joined
- May 6, 2011
- Messages
- 92
Hi guys,
Could you help me out with a SUMPRODUCT formula and its conditions specification for calculating average opening price (for further return calculation).
Here's the situation:
I have the following columns:
D Security name
F Quantity
G Price
H Volume (=Price*Quantity)
K Open/Close (shows whether the transaction is opening or closing the position; only closing positions need the return calculation and thus the average opening price calculation)
O Date of transaction
There are multiple stocks (31) and opening and closing transactions (2:275) on them with respective transaction dates, prices and volumes.
I need a formula that would give me an average opening price corresponding to each closing transaction. For each CLOSE transaction it should sum values in column Volume for the respective Security name, for all OPEN transactions prior the taken CLOSE transaction but after the previous CLOSE transaction and than divide it on the same formula for Quantity.
Right now I am using the following formula:
=IF(K2="OPEN";0;(SUMPRODUCT(--(D2:D$275=D2);--(K2:K$275="OPEN");--(H2:H$275))/(SUMPRODUCT(--(D2:D$275=D2);--(K2:K$275="OPEN");--(F2:F$275)))))
It unfortunately gives my average price on all opening transactions on that security, not those after the previous closing transation. It also does not work if I do not use newest-to-oldest dates
Could you please help me out with that?
Kindest thanks in advance, guys.
Could you help me out with a SUMPRODUCT formula and its conditions specification for calculating average opening price (for further return calculation).
Here's the situation:
I have the following columns:
D Security name
F Quantity
G Price
H Volume (=Price*Quantity)
K Open/Close (shows whether the transaction is opening or closing the position; only closing positions need the return calculation and thus the average opening price calculation)
O Date of transaction
There are multiple stocks (31) and opening and closing transactions (2:275) on them with respective transaction dates, prices and volumes.
I need a formula that would give me an average opening price corresponding to each closing transaction. For each CLOSE transaction it should sum values in column Volume for the respective Security name, for all OPEN transactions prior the taken CLOSE transaction but after the previous CLOSE transaction and than divide it on the same formula for Quantity.
Right now I am using the following formula:
=IF(K2="OPEN";0;(SUMPRODUCT(--(D2:D$275=D2);--(K2:K$275="OPEN");--(H2:H$275))/(SUMPRODUCT(--(D2:D$275=D2);--(K2:K$275="OPEN");--(F2:F$275)))))
It unfortunately gives my average price on all opening transactions on that security, not those after the previous closing transation. It also does not work if I do not use newest-to-oldest dates
Could you please help me out with that?
Kindest thanks in advance, guys.