how to calculate the rate of return for a list of prices in excel using python?

Question

I am trying to get a rate of return for a list of prices in excel using python. I wrote the following code but I can't find out what the problem is.

for row in range(2, sheet.max_row + 1):
    close = sheet.cell(row, 5)
    returned = sheet.cell(row, 7)
    operation = pd.DataFrame.pct_change(close)
    returned.value = operation

enter image description here

this is the error that I keep getting: axis = self._get_axis_number(kwargs.pop("axis", self._stat_axis_name)) AttributeError: 'Cell' object has no attribute '_get_axis_number'

Any help would be appreciated.

question from:https://stackoverflow.com/questions/65713113/how-to-calculate-the-rate-of-return-for-a-list-of-prices-in-excel-using-python

Answer 1

When you are using pct_change for Close, you didn't need to going through loop. Just simple like that:

import pandas as pd

df = pd.read_excel('StackOverFlow.xlsx', sheet_name='Sheet1')

df['Operation'] = df['Close'].pct_change()

df

Besides that, I suggest you utilize pandas dataframe by field name, it could be easier for maintenance

for index, row in df.iterrows():
    close = row['Close']