Creating TimeFrames from Minute-Based Data Using Python
This is a programming lesson. So there will be very less amount of explanation and more code. If you’re stuck somewhere, feel free to comment.
Objective
Let’s design a function that transforms minute-level financial data into larger timeframes, such as 120 minutes. This is especially useful in financial market analysis where detailed data is essential for informed decision-making.
Note – The structure of historical data and live data from Zerodha is identical. During the development and testing of functions, it’s not feasible to wait for days or months to validate their performance using live data.
To address this, we’ll initially test and validate our functions using historical data. Once we have confirmed that the functions generate accurate signals and execute trades correctly with historical data, we can seamlessly transition to using live data for real-time trading.
Python Code
So, We will get the minute data using Historical Function. You can swap that with live data whenever you want.
from datetime import date,timedelta
starting_date= (date.today() - timedelta(days=15)).strftime("%Y-%m-%d")
ending_date=date.today().strftime("%Y-%m-%d")
token = "738561" #Reliance Token
zap=kite.historical_data(token,starting_date,ending_date,"minute")
zap= pd.DataFrame(zap)
zap
Output –
date open high low close volume
0 2023-11-10 09:15:00+05:30 2305.55 2308.90 2303.55 2305.00 41248
1 2023-11-10 09:16:00+05:30 2304.95 2306.25 2303.00 2305.70 28564
2 2023-11-10 09:17:00+05:30 2305.05 2308.80 2305.05 2308.60 33568
3 2023-11-10 09:18:00+05:30 2308.60 2310.45 2308.20 2310.45 39478
4 2023-11-10 09:19:00+05:30 2310.45 2311.95 2310.00 2310.10 23513
... ... ... ... ... ... ...
3805 2023-11-24 15:25:00+05:30 2395.95 2396.75 2395.00 2395.65 18228
3806 2023-11-24 15:26:00+05:30 2395.55 2396.00 2394.85 2394.85 12166
3807 2023-11-24 15:27:00+05:30 2395.00 2395.30 2394.50 2394.80 17623
3808 2023-11-24 15:28:00+05:30 2394.55 2394.80 2394.00 2394.00 19879
3809 2023-11-24 15:29:00+05:30 2394.40 2394.90 2394.00 2394.25 9111
3810 rows × 6 columns
As You can see it has data of 3810 rows i.e. 3810 minutes.
The function we’re discussing is minute_conversion. It takes two parameters: a DataFrame (data) that contains minute-by-minute trading data and a numerical value (timeframe) representing the desired time interval in minutes for aggregation.
import pandas as pd
def minute_conversion(data, timeframe):
# Ensure 'date' column is in datetime format
data['date'] = pd.to_datetime(data['date'])
# Initialize an empty list to store the aggregated data
aggregated_data = []
# Group data by the desired timeframe
grouped = data.groupby(pd.Grouper(key='date', freq=f'{timeframe}T'))
# Aggregate data within each group
for name, group in grouped:
if not group.empty:
open_price = group.iloc[0]['open']
high_price = group['high'].max()
low_price = group['low'].min()
close_price = group.iloc[-1]['close']
volume = group['volume'].sum()
# Append the aggregated values to the list
aggregated_data.append([name, open_price, high_price, low_price, close_price, volume])
# Convert the list to a DataFrame
new_df = pd.DataFrame(aggregated_data, columns=['date', 'open', 'high', 'low', 'close', 'volume'])
return new_df
# Example usage:
# zap is your original DataFrame
timeframe = 120 # 120 minutes
converted_df = minute_conversion(zap, timeframe)
converted_df
Output –
date open high low close volume
0 2023-11-10 08:00:00+05:30 2305.55 2316.35 2298.90 2301.00 1417820
1 2023-11-10 10:00:00+05:30 2301.00 2307.35 2298.05 2305.60 645230
2 2023-11-10 12:00:00+05:30 2305.65 2315.10 2305.10 2309.10 813375
3 2023-11-10 14:00:00+05:30 2309.15 2316.00 2307.00 2315.90 942613
4 2023-11-12 18:00:00+05:30 2326.05 2332.00 2322.70 2330.90 685748
5 2023-11-13 08:00:00+05:30 2322.90 2324.60 2316.20 2317.00 295834
6 2023-11-13 10:00:00+05:30 2317.05 2318.85 2311.70 2315.50 418459
7 2023-11-13 12:00:00+05:30 2315.30 2318.00 2312.00 2314.75 367694
8 2023-11-13 14:00:00+05:30 2314.95 2316.75 2312.00 2315.10 798828
9 2023-11-15 08:00:00+05:30 2340.00 2342.60 2327.00 2335.55 1015224
10 2023-11-15 10:00:00+05:30 2335.55 2348.00 2335.00 2344.10 1867153
11 2023-11-15 12:00:00+05:30 2344.00 2352.90 2343.85 2346.85 992835
12 2023-11-15 14:00:00+05:30 2346.95 2361.95 2346.80 2356.10 2003118
13 2023-11-16 08:00:00+05:30 2351.10 2364.00 2346.95 2355.25 923205
14 2023-11-16 10:00:00+05:30 2355.00 2369.60 2354.70 2366.45 1049582
15 2023-11-16 12:00:00+05:30 2366.50 2369.35 2363.80 2367.00 727641
16 2023-11-16 14:00:00+05:30 2366.75 2374.15 2350.55 2359.00 3758974
17 2023-11-17 08:00:00+05:30 2352.90 2370.30 2352.05 2369.95 513251
18 2023-11-17 10:00:00+05:30 2370.05 2373.25 2363.80 2367.00 1017076
19 2023-11-17 12:00:00+05:30 2366.80 2371.60 2364.25 2366.80 849327
20 2023-11-17 14:00:00+05:30 2366.80 2368.00 2353.05 2354.85 1454868
21 2023-11-20 08:00:00+05:30 2348.55 2358.40 2342.00 2345.45 507002
22 2023-11-20 10:00:00+05:30 2346.35 2347.25 2336.40 2341.30 478746
23 2023-11-20 12:00:00+05:30 2341.25 2347.95 2340.35 2342.25 354916
24 2023-11-20 14:00:00+05:30 2342.25 2352.70 2341.15 2349.00 826822
25 2023-11-21 08:00:00+05:30 2366.00 2380.00 2360.20 2372.70 876474
26 2023-11-21 10:00:00+05:30 2372.95 2379.65 2371.00 2379.00 857184
27 2023-11-21 12:00:00+05:30 2378.80 2388.00 2378.30 2381.20 1150310
28 2023-11-21 14:00:00+05:30 2381.20 2382.85 2375.00 2377.00 1065302
29 2023-11-22 08:00:00+05:30 2375.00 2394.45 2375.00 2387.90 1362028
30 2023-11-22 10:00:00+05:30 2387.70 2392.80 2378.05 2379.95 1190981
31 2023-11-22 12:00:00+05:30 2379.95 2385.00 2372.20 2380.00 921422
32 2023-11-22 14:00:00+05:30 2379.75 2390.00 2378.05 2387.00 702310
33 2023-11-23 08:00:00+05:30 2388.20 2399.75 2388.20 2396.25 1124039
34 2023-11-23 10:00:00+05:30 2396.20 2400.00 2390.25 2392.75 1194248
35 2023-11-23 12:00:00+05:30 2393.00 2399.00 2392.15 2398.45 819897
36 2023-11-23 14:00:00+05:30 2398.45 2399.10 2390.50 2393.90 1067299
37 2023-11-24 08:00:00+05:30 2391.60 2402.60 2391.05 2397.50 493994
38 2023-11-24 10:00:00+05:30 2397.40 2401.00 2393.55 2400.05 971111
39 2023-11-24 12:00:00+05:30 2400.10 2401.00 2395.10 2397.85 941540
40 2023-11-24 14:00:00+05:30 2397.90 2399.50 2392.05 2394.25 934921
These are data of 120 minute candles. Now we have converted the data of 1 minutes to 120 minutes.
-
Data Formatting: The function first ensures that the ‘date’ column in the data is in datetime format. This is crucial because the aggregation process depends on correctly interpreting these timestamps.
-
Aggregation Logic: The heart of the function lies in its ability to group data by the specified timeframe. It uses the groupby method combined with pd.Grouper. This technique groups the data based on the ‘date’ column, aggregated over intervals defined by the timeframe parameter.
For each group (representing a unique time block), the function calculates:- The opening price (open_price): Taken from the first row in the group.
- The highest price (high_price): The maximum of the ‘high’ values within the group.
- The lowest price (low_price): The minimum of the ‘low’ values.
- The closing price (close_price): Taken from the last row in the group.
- The total volume (volume): The sum of the ‘volume’ values.
-
Constructing the New DataFrame: Each aggregated set of values is appended to a list named aggregated_data. This list is then transformed into a new DataFrame (new_df). This DataFrame mirrors the structure of the original but reflects the aggregated time intervals.
-
Usage: To use this function, you need a DataFrame like zap with minute-level data and a specific timeframe. For instance, if you set timeframe = 120, the function will convert the data into 120-minute intervals.