Share inventory & capital gains on fifo basis

Can any one help me on my project for calculating capital gains & generating closing inventory on FIFO basis from opening stock sheet with columns as A:Name of Scrip, B:Batch no., C:Purchase date, D: quantity, E: Rate, F: Cost Amount and
Transaction Sheet with columns as B:Trade date, D: Purchase Scrip, E: Purchase qty., F: Purchase amount, H:Sale scrip, I:Sale qty., J:Sale amount (negative figure).

I want to generate a capital gain sheet with columns as A:Name of scrip, B:quantity, C:batch details (B-1,B-2,B-3....), D:Purchase date, E:Purchase Cost, F:Sale Date, G:Sales value, H:Capital Gain/Loss
(The qty in purchase or sale should automatically be splitted to match the respective batch balances & the cost or sales value also splitted in the proportion of respective quantities. The same should be automatically be picked up from the op.stock sheet & trans sheet. The batch details should also be automatically be generated from op.stock sheet or as per the date of transaction from trans sheet. ON FIFO (First in First out basis only)

Also a closing stock sheet with the same columns as the opening stock sheet.