I am looking for a clean and efficient way to obtain the portfolio returns from a list of activity records.
Specifically, the activity file consist of BUY, SELL, COVER, SHORT, etc. records with additional referential data (e.g. SecurityID, Quantity, Fees, BaseAmount, etc.). Here is an example file.
A possible layout for a security:
t TransactionType Quantity Price 1 BUY 100 5 2 BUY 50 4 ... 3 SELL 50 6
So what I thought would be the best to do is to create a time series of the portfolio value over time. Hence, for each date, I take the current stock price and I check all the past records up to that date to determine the quantity that the user has in its portfolio of that particular security.
In the previous example, this would give the following evolution:
t Quantity_Portfolio Return 1 100 - 2 150 150*4 / ( (50*4)+(100*5) ) ... 3 100 ...?
As you can see it becomes quite complex to calculate the portfolio value at time 3, as then - even in this simple example. What is the return at t=3?
I was therefore wondering whether there are some sets of rules or resources that I could use (preferably Matlab or Python) to parse such activity records, instead of reinventing the wheel and trying to think of all possible situations?