I have a personal investment tracker, where I am recording all my stock transactions. For my open positions I want to calculate the book value and the average share price purchased of my open positions using the LIFO method.
My current implementation of calculating book value is incorrect, as it simply calculates the weighted average of all previous transactions, including the ones that have been closed. Here's an example of how I want things to be calculated in the statistics tab:
The current book value of QQQ should be: 3 * 181.55 (remaining stocks from Sep 10 transaction) + 10 * 231.7899 (Remaining stocks from Feb 13 transaction) = 2862.55
The average share price for QQQ should be 2862.55 / 13 = 220.196
I want the statistics tab to update automatically if I add more entries to the transactions tab. I think it would make much more sense if you take a look at the attached spreadsheet.
Any help would be greatly appreciated.