FIFO on Stock Capital Gains/Losses

[Tequilashot]

I'm looking at calculating the capital gains or losses on a stock portfolio using the first in first out system and also taking into account fees and fx rates for the given dates.

I've tried playing around with a few SUMIFS functions but its just not clicking with me today. So far I've only gotten as far as managing an account balance (quantity of shares held) on each line depending on the actions and company, but after this I'm kind of stuck on how to compile a function that determines the profit or loss from a FIFO perspective.

Below is a sample table of what I am working with.

[TABLE="width: 722"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Action[/TD]
[TD]Company[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD]Fees[/TD]
[TD]Amount[/TD]
[TD]FX Rate £/$[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2015[/TD]
[TD]Buy[/TD]
[TD]PAC[/TD]
[TD]1000[/TD]
[TD]25.00[/TD]
[TD]8.95[/TD]
[TD]25,000.00[/TD]
[TD]1.52[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]08/02/2015[/TD]
[TD]Buy[/TD]
[TD]PAC[/TD]
[TD]1000[/TD]
[TD]30.00[/TD]
[TD]8.95[/TD]
[TD]30,000.00[/TD]
[TD]1.49[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10/03/2015[/TD]
[TD]Buy[/TD]
[TD]GAF[/TD]
[TD]800[/TD]
[TD]120.00[/TD]
[TD]1.43[/TD]
[TD]96,000.00[/TD]
[TD]1.53[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12/04/2015[/TD]
[TD]Sell[/TD]
[TD]PAC[/TD]
[TD]700[/TD]
[TD]32.00[/TD]
[TD]1.25[/TD]
[TD]22,400.00[/TD]
[TD]1.51[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14/05/2015[/TD]
[TD]Sell[/TD]
[TD]GAF[/TD]
[TD]800[/TD]
[TD]110.00[/TD]
[TD]1.26[/TD]
[TD]88,000.00[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]16/06/2015[/TD]
[TD]Buy[/TD]
[TD]BLU[/TD]
[TD]600[/TD]
[TD]14.00[/TD]
[TD]1.27[/TD]
[TD]8,400.00[/TD]
[TD]1.52[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]18/07/2015[/TD]
[TD]Buy[/TD]
[TD]BLU[/TD]
[TD]600[/TD]
[TD]12.00[/TD]
[TD]1.28[/TD]
[TD]7,200.00[/TD]
[TD]1.52[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]20/08/2015[/TD]
[TD]Sell[/TD]
[TD]BLU[/TD]
[TD]600[/TD]
[TD]16.00[/TD]
[TD]1.29[/TD]
[TD]9,600.00[/TD]
[TD]1.49[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]22/09/2015[/TD]
[TD]Sell[/TD]
[TD]PAC[/TD]
[TD]700[/TD]
[TD]30.00[/TD]
[TD]1.30[/TD]
[TD]21,000.00[/TD]
[TD]1.45[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]24/10/2015[/TD]
[TD]Sell[/TD]
[TD]BLU[/TD]
[TD]600[/TD]
[TD]10.00[/TD]
[TD]1.31[/TD]
[TD]6,000.00[/TD]
[TD]1.42[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tequilashot]

Bump, any help appreciated.

 

[Tequilashot]

Bump, thought I got a work around but was wrong