0
$\begingroup$

I'm trying to run backtest in a vectorized way using Python Pandas and need to calculate a portfolio cumulative return from price data and weight of asset data.

I have two Dataframes:

  1. price of each individual assets (https://www.dropbox.com/s/ve9ll3t1j5owfuc/test_price.csv?dl=0)

  2. weight of each individual assets (https://www.dropbox.com/s/hto9kq2g2wwfpm8/test_weight.csv?dl=0)


  • Both Dataframe has same shape

  • Weights of each assets change only at the end of month

    • Weights of the rest of days are filled by 'ffill' method, so weights are all same during the each month

What I have found out:

  1. portfolio_cum_rtn_df = (price_df.pct_change().fillna(0) + 1).multiply(weight_df).sum(axis=1)

  2. portfolio_rtn_df = price_df.pct_change().fillna(0).multiply(weight_df).sum(axis=1) portfolio_cum_rtn_df = (portfolio_rtn_df + 1).cumprod()

Both are not correct way to calculate portfolio cumulative return.

Need some helps

$\endgroup$
  • 1
    $\begingroup$ To calculate holding period return, you also need distributions (eg. dividends, splits, etc...) $\endgroup$ – Matthew Gunn Oct 2 '18 at 3:32
  • $\begingroup$ @MatthewGunn I'd like to make it simple. I'm concentrating on just the price of each asset $\endgroup$ – user3595632 Oct 2 '18 at 3:33
  • $\begingroup$ Take the dataframe of prices and convert it into a dataframe of returns. Then, multiply it by the matrix of weights, sum the rows, and then do the cumulative product. $\endgroup$ – Jared Marks Oct 2 '18 at 17:09
1
$\begingroup$

These answers are missing the idea of path dependency. Your weights are only updated monthly. That means your weight on t0 is w0 and weight on t1 is w0*(1 + r1), weight on t2 is w0*(1+r1)*(1+r2) where r(i) is the split adjusted total return on day i. I imagine you are keeping it simple, but it also matters if you are assuming your weights are beginning of day or end of day.

If you are assuming a daily rebal to the weights in your sheet, then you can forget about path dependency because weights are provided at each discrete time-step. If this is the case, your second formula is correct (slight edit below), but again the devil is in the details. It matters if your weights are assumed at the beginning or end of day. If end of day, you need to shift one day forward to get the intended beginning of day weights for simulation. If you are only trading once per month, your formula isn't correct - you need to incorporate path dependency.

portfolio_rtn_df = weight_df.multiply(price_df.pct_change().fillna(0)).sum(axis=1)

portfolio_cum_rtn_df = (1 + portfolio_rtn_df).cumprod() - 1

$\endgroup$
  • $\begingroup$ I can't understand how the multiplication of weight and daily return works. Let asy A's price : 10 -> 12 -> 6 (daily return: +0.2, -0.5) and B's price : 10 -> 5 -> 6 (daily return: -0.5, +0.2). If I buy 10shares of A and 10shares of B at the first of the day, the total portval would be 200 -> 170 -> 120. But If I followed your method,the 1st day of total return would be (0.5 * 0.2 + 0.5 *-0.5 = -0.15), the 2nd day of total return would be (0.5 * -0.5 + 0.5 *0.2 = -0.15). If I applied it to 200, it would be 200 -> 200*0.85 -> 200*(0.85)^2, which is different from "200 -> 170 -> 120" $\endgroup$ – user3595632 Oct 3 '18 at 1:19
  • $\begingroup$ Edited: This is what i mean by path dependency: "the 2nd day of total return would be (0.5 * -0.5 + 0.5 *0.2 = -0.15)" This is not correct. Your security weights are no longer 50% and 50%. The organic price movement of the security has changed them. Your weights on day 2 are: A: 120 / 170 = 0.70588235294 B: 50 / 170 = 0.29411764705 Therefore, your second calculation should be: (0.705 * -0.5 + 0.294 *0.2 = ~-0.2937). Tying it all together (1 + -0.15) * (1 + -0.293) = ~-0.4 which equals (120 / 200 - 1) = -0.4 $\endgroup$ – mperlow Oct 3 '18 at 17:00
  • $\begingroup$ I can also say that I've written a number of back-testing engines in pandas from scratch and it's trivial once you understand these concepts, but may take you a few days / weeks to work through the idiosyncrasies depending on your proficiency. Not sure what type of timeline you are on. Happy to answer targeted questions $\endgroup$ – mperlow Oct 3 '18 at 17:08
  • $\begingroup$ Now I've figured out all. So, that's why I have to use cumulative return of individual assets instead of daily return. The last problem that I have is how to deal with monthly change of weights.... I think I could use a for loop but let me think about the way to solve it using Pandas. Thanks for your kind explanation :) $\endgroup$ – user3595632 Oct 3 '18 at 23:42
  • $\begingroup$ Absolutely! I haven't heard this rule anywhere else, but it's a rule for me - no "for" loops allowed in pandas vectorized back-testing engine or most of pandas I should say :) $\endgroup$ – mperlow Oct 5 '18 at 0:20
0
$\begingroup$

Assume assets $a$, $b$, $c$ with weight $W$ and price $P$.

On day $i$, the return of asset $a$ is $R_{a}(i) = P_{a}(i)/P_{a}(i-1) - 1$.

Portfolio return $R_{p}(i)$ on day $i$ equals $W_{a}(i) \cdot R_{a}(i) + W_{b}(i) \cdot R_{b}(i) + W_{c}(i) \cdot R_{c}(i)$,

then portfolio cumulative return is $\Pi (1 + Rp(i)) - 1$, for $i$ from 1 to day end.

$\endgroup$
  • $\begingroup$ What does it mean 'put 100 in each asset' ? You buy a certain number of shares and then the value of thse shares fluctuates up and down until you decide to buy/sell some more shares. $\endgroup$ – Alex C Oct 2 '18 at 14:18
  • $\begingroup$ @AlexC It means buying 10 shares of A (total 100) and buying 10 shares of B(total 100$) $\endgroup$ – user3595632 Oct 3 '18 at 1:11
  • $\begingroup$ I can't understand how the multiplication of weight and daily return works. Let say A's price : 10 -> 12 -> 6 (daily return: +0.2, -0.5) and B's price : 10 -> 5 -> 6 (daily return: -0.5, +0.2). If I buy 10shares of A and 10shares of B at the first of the day, the total portval would be 200 -> 170 -> 120. But If I followed your method,the 1st day of total return would be (0.5 * 0.2 + 0.5 *-0.5 = -0.15), the 2nd day of total return would be (0.5 * -0.5 + 0.5 *0.2 = -0.15). If I applied it to 200, it would be 200 -> 200*0.85 -> 200*(0.85)^2, which is different from "200 -> 170 -> 120" $\endgroup$ – user3595632 Oct 3 '18 at 1:22

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.