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

    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 ?


    Thanks for your clarification

    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

    Thanks a lot for your additional explanation.


    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...


    Thanks for your confirmation.

    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

    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 :)

    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 :)

  • Carim

    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!

Register Yourself Login