I am analysing a stock-index in 4-year cycles and would like to start with a base of 1 at the beginning of the first year and the calculate the returns on top, so that I get a column in the dataframe that goes 1, 1.02, 1.03, 1.025... The return indexed calculation would be (todaysValue/yesterdaysValue)*yesterdaysIndexValue.
The df looks like this:
Datetimeindex Stockindex CycleYear Diff Daynumber
01.01.1968 96.47 0 1 1
...
03.01.1972 101.67 0 1 1
...
06.09.2022 3908.19 2 0 699
07.09.2022 3979.87 2 0 700
08.09.2022 4006.18 2 0 701
I would now like to add a column df['Retindex'] that starts every 4 years at 1 and calculates the indexed-returns until the end of year 4.
I have created the column to that has a 1 at the start of each cycle.
df['Retindex'] = df['Daynumber'].loc[df['Daynumber'] == 1]
Then I tried creating the rest of the index with this:
for id in df[df['Retindex'].isnull() == True].index: df.loc[id, 'Retindex'] = (df[Stockindex]/df[Stockindex].shift().loc[id]) * df['Retindex'].shift().loc[id]
Here I am getting the error: "ValueError: Incompatible indexer with Series"
I have tried other ways as well but I am unfortunately not progressing on this. Can anyone help?
