I have recently started trading which is irrelevant but i have created an excel spreadsheet to track my portfolio and that is very relevant to the problem.
The problem is as follows:
for simplicity let's say i bought 100 of 'A' at a total price of 1,000 and an average price of 10.
Then, i sell all 100 of A (the price at which i am selling does not matter because i am only trying to establish the average purchase price)
Then, i buy another 100 of A at a total price of 500 and an average cost of 5.
I calculate my average price by dividing total cost by total amount
The issue i have is that my spreadsheet returns an average price of 7.5 not 5.
The reason for this is because it does not recognise that i have sold out of my position so it gives me the total average price of all purchases no matter how many times i have sold. For instance, after the above example i would be left with 100 of A. But, my spreadsheet thinks i've got 200 because it doesn't know i have sold my original 100.
This problem is the final error i need to deal with in my spreadsheet (i hope). All other problems i have been able to resolve and i really don't want to have to buy a portfolio tracker when i have got this far in making my own!
I have attached a document that gives an example of how my 'trades' tab works on my spreadsheet.
Any advice would be much appreciated.
Honestly, at this point i am desperate for a solution. I have combed through youtube and google but cant seem to phrase my problem in a way that gets any results.
If i haven't explained my problem well enough i am more than happy to try explaining again.
This is my first time using this sort of forum for a solution so i apologies in advance if the way i have constructed this issue is problematic.