Calculating average price in excel

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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
If there is anything unclear I'd be happy to provide additional details.
It's so f tricky, I ve broken my head against it.
 
Upvote 0
Guys, I will try to repost the question as I guess it seems too unclear what is needed)

This spreadsheet is actually a list of transactions of securities over a period. Column E shows whether a transaction was a Buy or a Sell. Column I shows the volume of the security bought or sold. Column J returns the position in the security up-to-date: positive if the security was bought more than sold and is now held, and negative in case it was shorted more (security was sold more than bought).

Everytime there is a transaction that goes the opposite direction to the actual position (i.e. the position is positive and the transaction is a sell; or when there is a buy in case of a negative position) it closes the position or part of it and there is a "CLOSE" in column K.
Formula in column K returns "OPEN" when the position turned positive or negative from nil after the transaction.
I wrote this formula myself but it seems to be functioning.

What is needed:
For every cell in column K with value "CLOSE" I need a calculation of an average of opening prices (sum of Volumes in column H divided by sum of Quantities in column F). So it is a SUMPRODUCT divided by SUMPRODUCT both with the same constraints:
- for a given security
- that had "OPEN" in column K
- on dates earlier in column O
- BUT only after the previous "CLOSE" in column K occured.

Then I need to expand it down so it would work for all securities and for the case when I do not put dates descending from latest to earliest.

The formula should return nil in case there is "OPEN" in column K

Taking security C as an example.
If we put dates descending from latest to earliest there were 6 straight CLOSE transactions that covered 3 straight prior OPEN transactions.

The formula should calculate the same average opening price for them dividing SUM of Volume for 3 OPEN transactions by SUM of respective Quantity

Next CLOSE transaction should get Volume divided by quantity of the single OPEN transaction that follows.

Next 2 CLOSE transactions should get Volume divided by quantity of the single OPEN transaction that follows.

In case the transaction is an OPEN one the formula should return nil.

Hope that makes some more sence)
Thanks!
 
Upvote 0

Forum statistics

Threads
1,224,544
Messages
6,179,430
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top