# Conditional Stock Purchase Average Price

• Hi Everyone:

Hope everyone is doing well and safe.

I need an help to find out the average purchase price of a stock. When i sell the entire position and initiate a new position for the same stock the average calculation should not include the purchases initated prior to "sale of entire position". I have attached a sample spreadsheet. On the average price column, i have calculated the average price using SUMIFS, unfortunately it wont gives me the correct average price if there are multiple purchase and sales. I have created a column for the expected result.

I am using Office 365 and any solution using formula or query is fine.

Best Regards

Maqbool

## Files

• Hi,

Just to make sure my understanding is correct, the Average Purchase Price you are looking for is to be worked out on a Monthly basis

And NOT on the entire database ...

For example in cell G2, for the month of February where there is a single transaction, your formula

=SUMIFS(\$F\$2:\$F\$18,\$B\$2:\$B\$18,B2,\$A\$2:\$A\$18,A2)/SUMIFS(\$C\$2:\$C\$18,\$B\$2:\$B\$18,B2,\$A\$2:\$A\$18,A2)

would produce the expected result ...

Is that right ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Thank you verymuch for the reply and appreciated.

The Average Purchase Price needs to be calculated based on the entire database considering the sale, not on a monthly basis.

This February position sold out on Oct. 25th.

Then initated another purchase (100 qty) on 17th Dec. If there is no purchase after that your above formula will work fine. But another purchase booked on 23 Dec. (25 qty). Out this only 75 shares sold on 28/12. Until the entire position is sold out, need to get the average using the purchase value of Dec. 23 and Dec. 25 so on.....

I have updated the spreasheet with expected result in column I.

If not clear let me know will try to explain it further.

Best Regards

Maqbool

## Files

To recap, since your transactions are stored in chronological order, your definition of the "entire database" has to be somewhat tweaked by an adjusted starting record.

It is my understanding that there is compulsory reset of the "starting record" which is caused by any sold-out position -

i.e. a cumulative outstanding quantity of Zero for each ticker...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Please find attached your test file with a helper column for the "Start Row" (Column K) and the expected average formula.

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim:

Thank you very much for the help. Really appreciated.

Sorry:

Doesn't work as expected.

I have added another transaction in the last row. Expected average is 442.01 instead the formula suggested is giving me 400.40

Best Regards,

Maqbool

## Files

Edited 2 times, last by Maqbool: wrong file uploaded. ().

• Hi Maqbool,

As indicated in my message #4, the trap is centered around the "starting record" ... given the worksheet structure you have selected...

Should you type in 2 in cell K20, you would get the expected result ...

Since you are operating daily with your file, would you know of a method for each row to determine its correct starting row ?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi again,

In an attempt to patch the "starting row" issue, attached is your test file

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• You are welcome.

If your tests are conclusive, in my opinion, you could design your own UDF

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi,

Have you had a chance to carry out your own tests to validate the proposed logic ?

Indeed, if your real-life tests are conclusive, your workbook would 'deserve' a dedicated UDF ...

in order to get rid of all the worksheets formulas ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• ## CarimJan 20th 2023

Changed the title of the thread from “Conditional Average” to “Conditional Stock Purchase Average Price”.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!