用外部现金流计算复合收益

Question

面对外部现金流，我试图计算时间序列的累积价格。

这是示例数据集：

reportdate  fund     mtd_return cashflow    Desired Output
30/11/2018  Fund X  -0.00860    15687713    15552798.98
31/12/2018  Fund X  -0.00900                15412823.78
31/01/2019  Fund X   0.00920                15554621.76
28/02/2019  Fund X   0.00630                15652615.88
31/03/2019  Fund X   0.00700                15762184.19
30/04/2019  Fund X   0.01220                15954482.84
31/05/2019  Fund X   0.00060    1000000     16964655.53
30/06/2019  Fund X   0.00570    1200000     18268194.07
31/07/2019  Fund X   0.00450                18350400.94
31/08/2019  Fund X   0.00210                18388936.78
30/09/2019  Fund X   0.00530                18486398.15
31/10/2019  Fund X   0.00200                18523370.94
30/11/2019  Fund X   0.00430                18603021.44
31/12/2019  Fund X   0.00660                18725801.38
31/01/2020  Fund X   0.01070                18926167.45
29/02/2020  Fund X  -0.00510                18829644.00
31/03/2020  Fund X  -0.10700                16814872.09
30/04/2020  Fund X   0.02740    3400000     20768759.59
31/05/2020  Fund X   0.02180    2000000     23265118.55
30/06/2020  Fund X   0.02270                23793236.74
31/07/2020  Fund X   0.01120                24059720.99
31/08/2020  Fund X   0.01260                24362873.47
30/09/2020  Fund X   0.00750                24545595.02
31/10/2020  Fund X   0.00410    -8110576    16502402.68
30/11/2020  Fund X   0.02790                16962819.72
31/12/2020  Fund X   0.01230                17171462.40

在上面，所需的输出列是通过接受前一行的期望输出，加上当前期间的任何现金流(乘以1+ mtd_return )来计算的。实际上，面对外部现金流，我正在寻找一种计算复合回报的好方法。

非常感谢！

麦克

任何在python中实现这一点的帮助都将不胜感激。

Answer 1

import pandas as pd


df = pd.read_csv('df7.txt', sep=',', header=0)

df['reportdate'] = pd.to_datetime(df['reportdate'])
df = df.fillna(0)

qqq = []

def func_data(x):
    a = 0
    ind = x.index[0] - 1
    if x.index[0] > 0:
        a = (qqq[ind] + x['cashflow']) * (1 + x['mtd_return'])
        qqq.append(a.values[0])
    else:
        qqq.append(df.loc[0, 'cashflow'] * (1 + df.loc[0, 'mtd_return']))


    return a


df.groupby(['reportdate']).apply(func_data)
df['new'] = qqq
print(df)

输出

   reportdate      fund  mtd_return    cashflow  Desired Output           new
0  2018-11-30    Fund X     -0.0086  15687713.0     15552798.98  1.555280e+07
1  2018-12-31    Fund X     -0.0090         0.0     15412823.78  1.541282e+07
2  2019-01-31    Fund X      0.0092         0.0     15554621.76  1.555462e+07
3  2019-02-28    Fund X      0.0063         0.0     15652615.88  1.565262e+07
4  2019-03-31    Fund X      0.0070         0.0     15762184.19  1.576218e+07
5  2019-04-30    Fund X      0.0122         0.0     15954482.84  1.595448e+07
6  2019-05-31    Fund X      0.0006   1000000.0     16964655.53  1.696466e+07
7  2019-06-30    Fund X      0.0057   1200000.0     18268194.07  1.826819e+07
8  2019-07-31    Fund X      0.0045         0.0     18350400.94  1.835040e+07
9  2019-08-31    Fund X      0.0021         0.0     18388936.78  1.838894e+07
10 2019-09-30    Fund X      0.0053         0.0     18486398.15  1.848640e+07
11 2019-10-31    Fund X      0.0020         0.0     18523370.94  1.852337e+07
12 2019-11-30    Fund X      0.0043         0.0     18603021.44  1.860302e+07
13 2019-12-31    Fund X      0.0066         0.0     18725801.38  1.872580e+07
14 2020-01-31    Fund X      0.0107         0.0     18926167.45  1.892617e+07
15 2020-02-29    Fund X     -0.0051         0.0     18829644.00  1.882964e+07
16 2020-03-31    Fund X     -0.1070         0.0     16814872.09  1.681487e+07
17 2020-04-30    Fund X      0.0274   3400000.0     20768759.59  2.076876e+07
18 2020-05-31    Fund X      0.0218   2000000.0     23265118.55  2.326512e+07
19 2020-06-30    Fund X      0.0227         0.0     23793236.74  2.379324e+07
20 2020-07-31    Fund X      0.0112         0.0     24059720.99  2.405972e+07
21 2020-08-31    Fund X      0.0126         0.0     24362873.47  2.436287e+07
22 2020-09-30    Fund X      0.0075         0.0     24545595.02  2.454559e+07
23 2020-10-31    Fund X      0.0041  -8110576.0     16502402.68  1.650240e+07
24 2020-11-30    Fund X      0.0279         0.0     16962819.72  1.696282e+07
25 2020-12-31    Fund X      0.0123         0.0     17171462.40  1.717146e+07

在文件中生成由逗号分隔的所有​​值，也是空的(即逗号之间的值是空的)。阅读熊猫的文件，创建一个数据文件。header=0意味着第一行用作列标题。接下来，将“reportdate”列转换为日期时间格式，并将空值替换为零。数据按日期分组。func_data函数是为调用创建的。如果这是第一次调用，则计算else中的代码，其余的都在if中。计算结果被写入qqq数组，然后qqq数组填充“新”列。