Search code examples
pythondataframefinance

Intraday daily return

I am quite new in python and I need your help guys! my data structure enter image description here

this data is intraday data 5 min from 2001/01/02 till 31/12/2019. As you can see from the data 0 indicated the date, and 2 indicate the prices of the stock. Each day, such as 2001/01/02 has 79 observation. First of all, I need to create a daily return as a new column. Normaly I was dealing with daily data and for the daily log return was as follow

def lr(x):
return np.log(x[1:]) - np.log(x[:-1])

How can I create new column for the daily return from the 5 min data.

Solution

  • If you load your data into a pandas.DataFrame, you can use df.groupby() and then apply your lr-function with minimal changes:

    df = pd.read_excel('path/to/your/file.xlsx', header=None, 
                   names=['Index', 'Date', 'Some_var', 'Stock_price'])

    The key thing to do, though, will be to decide how you want to generate your daily values from your 5 minute data. I'm no stock expert, but I'd guess you want to use the last value for each day to represent the stock value. If that's the case, you can use 

    daily_values = df.groupby('Date')['Stock_price'].agg('last')

    and then apply your lr function to get the returns

    lr(daily_values)