Calculate profit and loss and average cost for my portfolio in stock market

[nalnofal]

Hi Everyone 

i have created Sample test for my portfolio and is already attached .

my question is to help me to add formula for my excel sheet to calculate current average cost for shares. i have multiple action BUY/SELL for period time and i know how calculate profit and loss and cost average but i really don’t know how i add formula for auto calculation for all action.

i already fill all needed data in table .

i appreciate to help me to solve this issue

here is the test example excel file

test example.xlsx 

[#2]

Hi @nalnofal 

if I did not get you wrong, you could use the SUMPRODUCT function for this:

=(SUMPRODUCT(Table5545[BUY QTY];Table5545[Cost])+SUMPRODUCT(Table5545[SELL QTY];Table5545[Cost]))/SUM(Table5545[[BUY QTY]:[SELL QTY]])

It multiplies all buy-quantities with the cost, then adds the sell-quantities multiplied with the cost and in the end it divides that number by the total quantities buy/sell.

[#3]

Hi @Martin_Weiss 

very interesting from you to use sumproduct formula for my buy/sell , its work fine in my first Example but unfortunately not work with other trading company that i have .

the problem happen when i make more trading Buy/sell for same company in shares market for multiple action 

for example i put tow more company in my excel sheet : (CompanyB and CompanyC) with more data i fill, so that you can look into it to understanding all trading i make and how my bank calculations loss/profit and how they calculate my shares average cost/total Shares cost
i reshare my Test Example