Stock Portfolio Construction and Tracking

Hi All,

Been getting some great tips from this site in the short time that I have been here. Loving it so far

I have been wanting to construct a workbook to track a portfolio of stocks. While I have started doing some basic ground work for it. I am still struggling with some key features that I need. Below is a quick run down of what I need from the workbook, the inputs, the values etc. Will try my best to explain this in as much detail as I can.

Construct of the Workbook
A. There is a starting corpus available for investment - Lets say INR 10 mn

B. I already have a list of stocks from which to select the investments. It basically is connected to bloomberg and tracks the stocks of a particular index - lets say NIFTY CNX 500. So this ensures that at a click I get a list of companies, their current prices. For the investment I only need these 2 inputs (Company Name and Price). I have simply added a 'Stock Code' to each company starting from 1000 to 1500.

C. In the portfolio sheet I need to input the Stock Code, Transaction Date, Transaction Type (More on this later) and # of Shares to Transact. The other information - Company Name, Trade Price is simply picked from the value sheet through VLOOKUP using stock codes.

D. Now there are 2 2-way transactions which I intend to enter - Buy and Short Sell. These will both result in an outflow of cash. And when I need to reverse the transaction it will have to be a corresponding Sell and Short Cover which will result in inflow of cash.

E. I have been able to construct the sheet thus far. Even managing to ensure that the total outflow will never exceed INR 10 mn.

Struggles

A. The key struggle for me is to get the Net Value of the Portfolio. It is easy to get the Net Value if on Day 1 I have Bought 5 securities at a particular price. I simply compare them to the current price and the difference is the gain or loss on the total amount invested.

B. The trouble starts when there are reversal of transactions. Example below

Day 1
Buy - 100,000 shares at Rs 10 of Company A - Total Outflow - Rs 1,000,000
Short Sell - 100,000 shares at Rs 20 of Company B - Total Outflow - Rs 2,000,000
Total Investment Value - Rs 3,000,000
Cash Left - Rs 7,000,000

Day 2
No transactions
Current Price of Company A - Rs 11 - Gain of Rs 100,000
Current Price of Company B - Rs 22 - Loss of Rs 200,000
Total Investment Value - Rs 2,900,000
Cash Left - Rs 7,000,000

Day 3
Sell - 50,000 shares at Rs 11 of Company A - Cash Inflow - Rs 550,000 (Gain of Rs 50,000)
Total Investment Value - Rs 2,350,000
Cash Left - Rs 7,550,000

C. So now what happens is there is one stock which is completely outstanding (Company B Short Sold), there is part sale of one stock (Company A Sold 50,000) and another outstanding Stock (Company A balance 50,000 shares)

D. Where I am struggling is that when this happens I am finding it difficult to actually know the value of the portfolio - current value of the portfolio, current gain / loss and past recorded gain / loss on transactions completed.

I am completely confused on how to get all of this into one sheet in one table. On reading the above it looks not as complicated to me. But when there are multiple buy / sell and many transactions its a complete chaos !!!

I am attaching the sample sheet for your reference. Would be very grateful if someone can help me complete this sheet with as much automation as possible. Basically the biggest problem area is how do I separate the transactions completed and outstanding in one sheet without manual intervention at each transaction.

Thanks all...sorry for the long post...