Convert Excel Formula to Python Pandas code-CodePudding

Hi I have an excel formula that does cumulative product and summation, I tried to replicate the formula with python pandas using pd.cumsum() and pd.cumprod() but I am not getting the exact output.

The excel formula is =PRODUCT(1 $B$3:B6)^(4/COUNT($B$3:B6))-1 [cell c6]

Thanks in advance.

data:

Effective Date  Balanced YFYS   Annualised since incpetion
30-09-2014      0.01382434  
31-12-2014      0.03070278  
31-03-2015      0.04251546  
30-06-2015      -0.01200241     7.63%
30-09-2015      -0.00109375     5.97%
31-12-2015      0.02013989      6.35%
31-03-2016      0.0072328       5.86%
30-06-2016      0.02630933      6.48%
30-09-2016      0.02090324      6.72%
31-12-2016      0.01165302      6.52%
31-03-2017      0.02086588      6.71%
30-06-2017      0.01677891      6.72%

CodePudding user response：

$ in excel is a fixed index. So you want to slice your data from 3rd row. Try:

# 1 corresponds to column B
s = df.iloc[:,1]

df.loc[s.index, 'annulized'] = (1 s).cumprod() ** (4/s.notna().cumsum()) - 1

Output:

   Effective Date  Balanced YFYS Annualised since incpetion  annulized
0      30-09-2014       0.013824                       None   0.056455
1      31-12-2014       0.030703                       None   0.091924
2      31-03-2015       0.042515                       None   0.120912
3      30-06-2015      -0.012002                      7.63%   0.076303
4      30-09-2015      -0.001094                      5.97%   0.059662
5      31-12-2015       0.020140                      6.35%   0.063521
6      31-03-2016       0.007233                      5.86%   0.058556
7      30-06-2016       0.026309                      6.48%   0.064789
8      30-09-2016       0.020903                      6.72%   0.067155
9      31-12-2016       0.011653                      6.52%   0.065166
10     31-03-2017       0.020866                      6.71%   0.067053
11     30-06-2017       0.016779                      6.72%   0.067201