Hello, I'm building a spreadsheet to track positions and profit/loss for multiple stock trades. I'm pretty capable using Excel functions, and can usually tweak VBA code to make it do what I want, but I don't have the programming skills to just write VBA code.

I'm having trouble separating out closed positions from open positions. For instance if I have the following data on Sheet1:

DATE TRADE VOLUME STOCK PRICE VALUE
11/01/2012 Buy 10,000 GE $50 -$500,000
11/15/2012 Sell 5,000 GE $52 $260,000
11/18/2012 Sell 8,000 GE $53 $424,000
12/04/2012 Sell 3,000 GE $55 $165,000

where the VALUE column is just the calculated cash flow from the trade. The total outstanding position is short 6,000 shares. I need a VBA loop that will total up the realized profit/loss (on a first-in-first-out basis), then calculate the remaining open position and weighted average cost. I think the simplest way would be to start at the bottom and start subtracting out sold volumes (and associated cash flow) until the 6,000 share short position is flat.

Thoughts?