Search code examples
pythondataframestatisticsanalyticsfinance

how to get profit/loss of a full day when there are rows with same date but different time

Unnamed: 0  open    high    low close   volume  Date    time    Profit  Loss    proforloss
0   0   252.70  254.25  252.35  252.60  319790  2022-01-03  09:15:00    0.000000    0.039573    -0.039573
1   1   252.60  253.65  251.75  252.80  220927  2022-01-03  09:30:00    0.079177    0.000000    0.079177
2   2   252.95  254.90  252.30  252.85  526445  2022-01-03  09:45:00    0.000000    0.039534    -0.039534
3   3   252.85  253.15  252.40  252.55  280414  2022-01-03  10:00:00    0.000000    0.118647    -0.118647
4   4   252.55  253.10  252.25  252.80  112875  2022-01-03  10:15:00    0.098990    0.000000    0.098990

this is the data given to me i have got the profit and loss for 15 min time frame but how can i get profit of a day starting time is 09:30:00 and closing time is 15:00:00. how can i also get the max profit and min profit of the day . when capital is 100,000.

thanks for your help;

Solution

  • You could filter for rows during the relevant trading times first, then use groupby() to get the max / min profit of the day. Finally, multiply with the desired volume.

    df['time'] = pd.to_timedelta(df['time'])
df = df[
    (pd.Timedelta(hours=9, minutes=30) <= df["time"])
    & (df["time"] <= pd.Timedelta(hours=15))
]
df = df.groupby("Date", sort=False)[["Profit", "Loss"]].agg(["min", "max"])
df = df * 100_000

    Output:

               Profit         Loss         
              min     max  min      max
Date                                   
2022-01-03    0.0  9899.0  0.0  11864.7