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?

  • $\begingroup$ 1 small observation: you can't do it with just Activity Records, you also need on certain dates Valuation Records which provide the prices of all portfolio securities on that date. $\endgroup$ – noob2 Apr 3 '17 at 15:53
  • 1
    $\begingroup$ You need to decide if you will be using LIFO or FIFO and whether you will be using a Time Weighted rate of return or a Money Weighted rate of return. Time Weighted is standard if you are going to be showing this performance to prospective investors. LIFO vs FIFO is up to you. It only matters for tax purposes if you don't Mark to Market at the end of each year. Googling 'portfolio accounting rules' should yield plenty of results. Or you could consult someone with portfolio accounting experience. $\endgroup$ – amdopt Apr 3 '17 at 16:19
  • 1
    $\begingroup$ Other than the fact it throws you into censoring problems, you do not need to valuations at all as you have the actual cash flows. Time series are used only because of a lack of customer records. As mentioned above you will have to solve inventory accounting, but logically that would be a tax based system since people rationally minimize their taxation. I would answer this question directly, but there is the censored observations on either side and it is late and I don't want to think about it right now. If I get time I will post an answer. $\endgroup$ – Dave Harris Apr 5 '17 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.