Different closing time of futures

Hello Emil,

In the research environment, you can use the pandas library's resample method to resample the data to daily frequency and then use the last method to get the last price of the day which would be the closing price. Here is an example:

# Assuming 'df' is your DataFrame
df = df.resample('D').last()

This will give you the closing price for each day for your futures data. The 'D' parameter in the resample method stands for 'Daily'. The last method will get the last non-NaN value of the day which should be the closing price.

Please note that this will still give you NaN values for the days when there is no trading activity. If you want to fill these NaN values, you can use the fillna method with the 'ffill' method which propagates the last valid observation to fill the gaps:

df = df.fillna(method='ffill')

This will fill the NaN values with the last valid observation. Please be aware that this might introduce lookahead bias into your model if the last valid observation is from a future date.

I hope this helps! If you have any more questions, feel free to ask.

Best,

Dear Mia. Let me add some clarifications with code:
 

qb.tickers = ["GC", "ZS", "ZM"]

for x in qb.tickers: 

    qb.AddFuture(x)

history = qb.History(qb.Securities.Keys, 100, Resolution.Daily)

history.index = history.index.droplevel([0]) #to remove meaningless data

# after that I get multi index dataframe. index 0 = ticker, Index 1 =  time in YY-DD-MM hh-mm-ss format. 
 

df = history['close'].unstack(level = 0)

# I unstack at level 0 and get ['close'] prices. as a result I get the mentioned frame (1st column is index now)

                                     ZZ              XX

YY-DD-MM 19-00     Nan      |     100.23

YY-DD-MM 20-00      98.15   |      Nan
 

At what stage I should use df = df.resample('D').last() ?
 

Hello Emil,

You should use the resample method after you have unstacked the DataFrame. Here is how you can do it:

# Assuming 'df' is your DataFrame after unstacking
df = df.resample('D').last()

This will resample your DataFrame to daily frequency and get the last price of the day which should be the closing price. The 'D' parameter in the resample method stands for 'Daily'. The last method will get the last non-NaN value of the day.

Please note that this will still give you NaN values for the times when there is no trading activity. If you want to fill these NaN values, you can use the fillna method with the 'ffill' method which propagates the last valid observation to fill the gaps:

df = df.fillna(method='ffill')

This will fill the NaN values with the last valid observation. Please be aware that this might introduce lookahead bias into your model if the last valid observation is from a future date.

I hope this helps! If you have any more questions, feel free to ask.

Best,

Thank you. That helped

I'm glad to hear that you found the information helpful, Emil! If you have any more questions or need further clarification, don't hesitate to ask. Happy coding!

Best,