FIFO Method - Tracking

Hi All,

I need help to come up with a solution for FIFO (First In First Out) method for tracking the "buys" and "sells" in a list of stocks. I would prefer a solution without UDF or VB programming (Im not good with those). Just a clever Excel setup/formulas would help tons.

Example:

Bond Trans Date Amount
A Buy 1/1/2010 1,000.00
A Buy 1/15/2010 1,000.00
A Sell 1/20/2010 (1,000.00)
A Buy 1/25/2010 1,000.00
A Sell 1/30/2010 (2,000.00)
B Buy 2/1/2010 500.00
B Buy 2/5/2010 300.00
B Sell 2/15/2010 (300.00)
B Sell 2/27/2010 (500.00)
C Buy 3/6/2010 250.00
C Sell 3/10/2010 (250.00)
C Buy 5/2/2010 400.00
C Sell 5/24/2010 (400.00)

So, basically I need to apply the first "Sell" date to the first "Buy" date. Stock A was first sold on 1/20/2010 for $1,000, therefore, I would reduce the first "Buy" on 1/1/2010 by the 1,000. This is FIFO method. The raw data comes in this format and we can change/adapt the layout to get FIFO working.

Thanks for your help!!!

How do i attach an excel file?

Yes, that is the correct interpretation.

Actually, lets do a different example. We can forget about the first one. I'm actually looking for the difference between the "Sell" dates and the "Buy" Dates for the related Stocks AND allocate the appropriate amounts.

Example:

Stock Buy/Sell Date Amount
A Buy 1/1/2010 $1,500
A Buy 5/1/2010 $1,000
A Sell 6/1/2010 ($1,000)
A Sell 12/1/2010 ($1,500)

So, in this example; Im looking to get the days between the First "Sell" date 6/1/2010 and First "Buy" date 1/1/2010. AND apply any remaining amount to the latter dates.

Was this query solved? I would like to see the solution in a formula (series of formulas).