'How to calculate for each month the overall value of a portfolio given each month I invested a different amount?
I have a dataset like this:
df.head(10)
Adj Close macd signal hist investment current_price returns
Date
2010-06-01 75.489998 0.382352 0.022635 0.359717 5000 392.32 25984.899397
2010-07-01 69.430000 0.983398 0.383040 0.600357 0 392.32 0.000000
2010-08-02 73.230003 -0.227501 0.276055 -0.503556 5000 392.32 26786.834768
2010-09-01 71.739998 -0.804435 -0.002094 -0.802340 5000 392.32 27343.184533
2010-10-01 71.370003 -0.340653 -0.138923 -0.201731 10000 392.32 54969.873182
2010-11-01 73.040001 -0.454064 -0.273669 -0.180395 15000 392.32 80569.549921
2010-12-01 78.309998 1.038864 -0.086955 1.125819 5000 392.32 25049.164362
2011-01-03 81.720001 3.581994 0.792240 2.789753 0 392.32 0.000000
2011-02-01 80.820000 4.674431 2.074357 2.600074 0 392.32 0.000000
where Adj Close is the price of a stock at that date, in the investment column I put how much I want to invest that month. How can I compute for each month the overall value of my portfolio?
Solution 1:[1]
Keep a count of the total number of stocks you own and multiply this by "Adj Close" to get the value of your portfolio:
df["Value"] = df["investment"].div(df["Adj Close"]).cumsum().mul(df["Adj Close"])
>>> df
Date Adj Close macd ... current_price returns Value
0 2010-06-01 75.489998 0.382352 ... 392.32 25984.899397 5000.000000
1 2010-07-01 69.430000 0.983398 ... 392.32 0.000000 4598.622456
2 2010-08-02 73.230003 -0.227501 ... 392.32 26786.834768 9850.311627
3 2010-09-01 71.739998 -0.804435 ... 392.32 27343.184533 14649.888126
4 2010-10-01 71.370003 -0.340653 ... 392.32 54969.873182 24574.332153
5 2010-11-01 73.040001 -0.454064 ... 392.32 80569.549921 40149.350842
6 2010-12-01 78.309998 1.038864 ... 392.32 25049.164362 48046.214965
7 2011-01-03 81.720001 3.581994 ... 392.32 0.000000 50138.383799
8 2011-02-01 80.820000 4.674431 ... 392.32 0.000000 49586.198348
[9 rows x 9 columns]
Solution 2:[2]
You can have a column representing your rolling stocks and the overall value will just be the price * rolling_total_stocks until that date.
df['total_stocks'] = df["investment"]/df['Adj Close']
df['portfolio_value'] = df['total_stocks'].cumsum() * df['Adj Close']
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | not_speshal |
| Solution 2 |