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?
Bookmarks