Posts by Maqbool

    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

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


    Please read the forum rules. You need to tag the code when you post.


    I am not sure i understand you fully.


    Try the following:




    otherwise post a sample workbook with expected result.



    Maqbool

    Hi
    Can someone guide me how to dynamically change the source of a Power Query; My source files are in a folder. I am trying to change the source of the query by typing the file name in a cell. I found few videos which takes all files from the folder. But I need to use a single file as my data source. This source files are created on a weekly basis with a date suffix.
    Thank you very much
    Any help will be highly appreciated.

    Hi


    Try the code below:



    Regards


    Maqbool

    Hi


    Code below will solve both issues:


    Please leave blank row 1 on consolidation workbook.



    Regards


    Maqbool

    Hi


    Change the code as below:



    Regards


    Maqbool

    Hi


    Try the following. I have noticed an issues with your file extension (why you do have January 2010.xlsm.xlsx) . This needs to be corrected. Or change the file names on row 8.





    Regards


    Maqbool

    Hi


    I can't upload the workbook now. I will upload the workbook later today.


    Follow the below steps:


    Create three ranged names as below:


    Use below formula against each name


    [F]
    tblData: Sheet1!$C$1:INDEX(Sheet1!$C$1:$G$1000,COUNTA(Sheet1!$C:$C),5)


    rngCat : =INDEX(tblData,0,2)


    rngPercent =OFFSET(Sheet1!$C$1,MATCH(Sheet1!$J$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!$J$1))
    [/F]


    Then create the following formula on your sample sheet
    L21 - enter as an array formula (CTRL+SHIFT+Enter]


    [F]


    N21 = LARGE(rngPercent,$K21)


    L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)


    M21= VLOOKUP(L21,tblData,MATCH($M$20,$C$1:$G$1,0),FALSE)


    [/F]



    Regards


    Maqbool

    Hi


    try the below :



    Regards


    Maqbool