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.

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

<tbody>
</tbody>

 

[Tequilashot]

Bump, any help appreciated.

 

[Tequilashot]

Bump, thought I got a work around but was wrong