I am building up my stock portfolio tracking with excel file. since i use multiple different broker, i wish to track my investment performance, not only those holding stock, but including those closed position. and with the close position of the same stock, wish to recalculate the average buying price after deduct the return.
i had tried multiple ways, including pivot table, pivot power tools etc. but still end in vain. the closest is by using 2 pivot table, where
1. those holding stock, where i labelled the source table, those closed position which the profil/loses mentioned in one of the cell, it will labeled as C (CLOSED) or otherwise O(Open). then i apply filter in pivot table, to only seeing those OPEN position. by that, i will be able to track, how much stock i am holding from all the different broker.
2. then i build another pivot table and include only Closed position. where it will include only those closed positon stock ticker together with its profil or loss.
while i am trying to build up another new column to re-average the latest holding stock price. as ex: i bought few stock of AMD, after i sell some with profit, now i wish to re-average my buying price.
my problem is to look up those same stock ticker from two pivot table, which applied different filter, so i cant combining it. i tried vlookup, but i cant figure out how to include the profit and loss column once lookup the same ticker from 2 different pivot table.
i tried my best to express it. hopefully.... u guys able to help.
*excel version 365
i had attached part of the file and looking forward for your opinion.