FIFO method calculations
 

Hello:
I am looking for some help. I have a worksheet of daily
stock trades for one individual, in one stock. Ther may be
as many as 100 buy trades and 100 sell trades on any given
day, but, there are sometimes 3 trades in one day. In any
case, I need to do two things:
1- Subtotal the NET number of shares and dollars
spent/received on each given day, which I can easily do by
using the Data/Subtotal menu selections. However, the work
I am doing has to be done on a First In First Out method
of Accounting. This means that, for example, if the guy
bought 5000 shares on day #1, and sold 3000 shares on day
#1, he would have a 2000 share "net carryover". I would
like to make this "carryover" amount become the new
first "trade" for day #2, and then calculate the buys and
sales for day #2. this goes on ad infinitum. I am only
given the data in "raw" form, meaning I get the date of
the trade, the buy or sell designation, the number of
shares, and the price per share. Is there a way to crate a
macro, or some other function, so that, at each change in
date, the subtotalled amount would automatically add
itself to the top of the next days trades, etc... all the
way down the sheet? How do I get Excel to automatically
insert a row where needed (below each subtotal, which will
be where it should place the "carryover" amount)??? Any
ideas???

Thanks in advance.

JM Attero