Subtract Cumulative Total until it reach zero with FIFO excel creation

[cmkong87]

Hi, i'm trying to create some subtract formula, but no idea how should i do it, the table below would show the result i desire:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]STOCK ITEMS NAME[/TD]
[TD]COST PER STOCK (IN USD)[/TD]
[TD]QUANTITY OF STOCKS[/TD]
[TD]CUMULATIVE/ BALANCE OF STOCKS[/TD]
[TD]REMARK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2019[/TD]
[TD]XYZ[/TD]
[TD]USD 1[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]BUY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]15/1/2019[/TD]
[TD]XYZ[/TD]
[TD]USD 1[/TD]
[TD](500)[/TD]
[TD]500[/TD]
[TD]SELL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]31/1/2019[/TD]
[TD]XYZ[/TD]
[TD]USD 2[/TD]
[TD]1000[/TD]
[TD]1500[/TD]
[TD]BUY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2/2/2019[/TD]
[TD]XYZ[/TD]
[TD]USD ???[/TD]
[TD](-1500)[/TD]
[TD]0[/TD]
[TD]SELL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


For ROW 5, as i sell the entire stock, i need the First 500 cost at USD1, the remaing 1,500 at USD2, so how can i create and present an automate formula in the excel table?
Thanks.

 

[Toadstool]

I posted a very similar question last year and Eric W gave me some options:
https://www.mrexcel.com/forum/excel-questions/1054045-average-cost-stock-buy-sell-transactions.html#post5061172