Back to Community
Problem resampling 1 minute bars into 60 minute bars

Hi all,

I have some experience building algorithms, but I'm very new when it comes to Quantopian and Python. Right now, I'm trying to port all of my algorithms from TradingView to Quantopian because this platform is much more powerful and provides me with more historical data. However, I'm running into some issues with creating 60 minute bars from the 1 minute data that Quantopian provides. I tried to resample the data following these two posts (https://www.quantopian.com/posts/writing-algos-for-time-frames-other-than-1m-and-1d and https://www.quantopian.com/posts/custom-intraday-bars) as closely as possible, but I can't quite get it to work. I printed the resampled DataFrame 'prices_60m' to troubleshoot, and I noticed something very weird. Some timestamps give me the correct price every hour, but most give me NaN. Could I get some help with this?

Here is a snippet of the logs I'm talking about:
2014-03-07 09:30 PRINT prices_60m: 2013-12-30 14:00:00+00:00 3.800
2013-12-30 15:00:00+00:00 3.795
2013-12-30 16:00:00+00:00 3.878
2013-12-30 17:00:00+00:00 3.870
2013-12-30 18:00:00+00:00 3.850
2013-12-30 19:00:00+00:00 3.840
2013-12-30 20:00:00+00:00 3.850
2013-12-30 21:00:00+00:00 3.840
2013-12-30 22:00:00+00:00 NaN
2013-12-30 23:00:00+00:00 NaN
2013-12-31 00:00:00+00:00 NaN
2013-12-31 01:00:00+00:00 NaN
2013-12-31 02:00:00+00:00 NaN
2013-12-31 03:00:00+00:00 NaN
2013-12-31 04:00:00+00:00 NaN
2013-12-31 05:00:00+00:00 NaN
2013-12-31 06:00:00+00:00 NaN
2013-12-31 07:00:00+00:00 NaN
2013-12-31 08:00:00+00:00 NaN
2013-12-31 09:00:00+00:00 NaN
2013-12-31 10:00:00+00:00 NaN
2013-12-31 11:00:00+00:00 NaN
2013-12-31 12:00:00+00:00 NaN
2013-12-31 13:00:00+00:00 NaN
2013-12-31 14:00:00+00:00 3.865
2013-12-31 15:00:00+00:00 3.840
2013-12-31 16:00:00+00:00 3.845
2013-12-31 17:00:00+00:00 3.860
2013-12-31 18:00:00+00:00 3.8...

Clone Algorithm
1
Loading...
Backtest from to with initial capital
Total Returns
--
Alpha
--
Beta
--
Sharpe
--
Sortino
--
Max Drawdown
--
Benchmark Returns
--
Volatility
--
Returns 1 Month 3 Month 6 Month 12 Month
Alpha 1 Month 3 Month 6 Month 12 Month
Beta 1 Month 3 Month 6 Month 12 Month
Sharpe 1 Month 3 Month 6 Month 12 Month
Sortino 1 Month 3 Month 6 Month 12 Month
Volatility 1 Month 3 Month 6 Month 12 Month
Max Drawdown 1 Month 3 Month 6 Month 12 Month
import numpy as np
import pandas as pd
import talib

def initialize(context):
    
    # Trade stock AMD
    context.stock = symbol('AMD')
    
    # Record long entry
    context.long_entry = False
    
    # Record short entry
    context.short_entry = False
    
    # Trade every 60 minutes
    for i in range(1, 390):
        n = 60
        if i % n == 0:
            schedule_function(trade, date_rules.every_day(), time_rules.market_open(minutes=i))
    
    # Record leverage every day
    schedule_function(record_leverage, date_rules.every_day(), time_rules.market_close())
    
    # Track orders every minute
    for i in range(1, 391):
        schedule_function(track_orders, date_rules.every_day(), time_rules.market_open(minutes=i))
    
def trade(context, data):
    
    # Retrieve current price
    price = data.current(context.stock, 'price')
    
    # Retrieve close price in 60 minute bars going back 300 bars
    prices_60m = data.history(context.stock, 'close', 18000, '1m').resample('60T').last()
    
    print('prices_60m: ' + str(prices_60m))
    
    # Bollinger Bands
    upper, middle, lower = talib.BBANDS(
        prices_60m,
        timeperiod=50,
        nbdevup=1.9,
        nbdevdn=1.9,
        matype=0)
    
    # Bollinger Bands %B
    bbr = (price - lower[-1])/(upper[-1] - lower[-1])
    
    # Moving average
    ma = talib.MA(
        prices_60m,
        timeperiod=300,
        matype=0)
    
    # Moving average difference
    ma_diff = price - ma[-1]
    
    # Long entry conditions
    long_entry = bbr > 1 and ma_diff > 0 and not context.long_entry and not get_open_orders()
    
    # Long exit conditions
    long_exit = (bbr < 1 or ma_diff < 0) and context.long_entry and not get_open_orders()
    
    # Short entry conditions
    short_entry = bbr < 0 and ma_diff < 0 and not context.short_entry and not get_open_orders()
    
    # Short exit conditions
    short_exit = (bbr > 0 or ma_diff > 0) and context.short_entry and not get_open_orders()
    
    # Long entry trade
    if long_entry:
        order_target_value(context.stock, 100000)
        print('Long Entry')
        print('Bollinger Bands %: ' + str(bbr))
        print('Moving Average Difference: ' + str(ma_diff))
        context.long_entry = True
    
    # Long exit trade
    if long_exit:
        order_target_value(context.stock, 0)
        print('Long Exit')
        print('Bollinger Bands %: ' + str(bbr))
        print('Moving Average Difference: ' + str(ma_diff))
        context.long_entry = False
    
    # Short entry trade
    if short_entry:
        order_target_value(context.stock, -100000)
        print('Short Entry')
        print('Bollinger Bands %: ' + str(bbr))
        print('Moving Average Difference: ' + str(ma_diff))
        context.short_entry = True
    
    # Short exit trade
    if short_exit:
        order_target_value(context.stock, 0)
        print('Short Exit')
        print('Bollinger Bands %: ' + str(bbr))
        print('Moving Average Difference: ' + str(ma_diff))
        context.short_entry = False
        
def record_leverage(context, data):
    record('Leverage', context.account.leverage)
    
def track_orders(context, data):  
    '''  Show orders when made and filled.  
           Info: https://www.quantopian.com/posts/track-orders  
    '''  
    c = context  
    try: c.trac  
    except:  
        c.t_opts = {        # __________    O P T I O N S    __________  
            'symbols'     : [],   # List of symbols to filter for, like ['TSLA', 'SPY']  
            'log_neg_cash': 1,    # Show cash only when negative.  
            'log_cash'    : 1,    # Show cash values in logging window or not.  
            'log_ids'     : 1,    # Include order id's in logging window or not.  
            'log_unfilled': 1,    # When orders are unfilled. (stop & limit excluded).  
            'log_cancels' : 0,    # When orders are canceled.  
        }    # Move these to initialize() for better efficiency.  
        c.t_dates  = {  # To not overwhelm the log window, start/stop dates can be entered.  
            'active': 0,  
            'start' : [],   # Start dates, option like ['2007-05-07', '2010-04-26']  
            'stop'  : []    # Stop  dates, option like ['2008-02-13', '2010-11-15']  
        }  
        c.trac = {}  
        log.info('track_orders active. Headers ...')  
        log.info('             Shares     Shares')  
        log.info('Min   Action Order  Sym  Now   at Price   PnL   Stop or Limit   Cash  Id')  
    from pytz import timezone as _tz  # Python only does once, makes this portable.  
                                      #   Move to top of algo for better efficiency.  
    # If 'start' or 'stop' lists have something in them, triggers ...  
    if c.t_dates['start'] or c.t_dates['stop']:  
        _date = str(get_datetime().date())  
        if   _date in c.t_dates['start']:    # See if there's a match to start  
            c.t_dates['active'] = 1  
        elif _date in c.t_dates['stop']:     #   ... or to stop  
            c.t_dates['active'] = 0  
    else: c.t_dates['active'] = 1           # Set to active b/c no conditions.  
    if c.t_dates['active'] == 0: return     # Skip if not active.  
    def _minute():   # To preface each line with the minute of the day.  
        bar_dt = get_datetime().astimezone(_tz('US/Eastern'))  
        return (bar_dt.hour * 60) + bar_dt.minute - 570 # (-570 = 9:31a)  
    def _trac(to_log):      # So all logging comes from the same line number,  
        log.info(' {}   {}'.format(str(_minute()).rjust(3), to_log))  # for vertical alignment in the logging window.

    for oid in c.trac.copy():               # Existing known orders  
      o = get_order(oid)  
      if c.t_opts['symbols'] and (o.sid.symbol not in c.t_opts['symbols']): continue  
      if o.dt == o.created: continue        # No chance of fill yet.  
      cash = ''  
      prc  = data.current(o.sid, 'price') if data.can_trade(o.sid) else c.portfolio.positions[o.sid].last_sale_price  
      if (c.t_opts['log_neg_cash'] and c.portfolio.cash < 0) or c.t_opts['log_cash']:  
        cash = str(int(c.portfolio.cash))  
      if o.status == 2:                     # Canceled  
        do = 'Buy' if o.amount > 0 else 'Sell' ; style = ''  
        if o.stop:  
          style = ' stop {}'.format(o.stop)  
          if o.limit: style = ' stop {} limit {}'.format(o.stop, o.limit)  
        elif o.limit: style = ' limit {}'.format(o.limit)  
        if c.t_opts['log_cancels']:  
          _trac('  Canceled {} {} {}{} at {}   {}  {}'.format(do, o.amount,  
             o.sid.symbol, style, prc, cash, o.id[-4:] if c.t_opts['log_ids'] else ''))  
        del c.trac[o.id]  
      elif o.filled:                        # Filled at least some.  
        filled = '{}'.format(o.amount)  
        filled_amt = 0  
        #if o.status == 1:                   # Nope, is either partial or complete  
        if o.filled == o.amount:             # Complete  
          if 0 < c.trac[o.id] < o.amount:  
            filled   = 'all {}/{}'.format(o.filled - c.trac[o.id], o.amount)  
          filled_amt = o.filled  
        else:                                    # c.trac[o.id] value is previously filled total  
          filled_amt = o.filled - c.trac[o.id]   # filled this time, can be 0  
          c.trac[o.id] = o.filled                # save fill value for increments math  
          filled = '{}/{}'.format(filled_amt, o.amount)  
        if filled_amt:  
          now = ' ({})'.format(c.portfolio.positions[o.sid].amount) if c.portfolio.positions[o.sid].amount else ' _'  
          pnl = ''  # for the trade only  
          amt = c.portfolio.positions[o.sid].amount ; style = ''  
          if (amt - o.filled) * o.filled < 0:    # Profit-taking scenario including short-buyback  
            cb = c.portfolio.positions[o.sid].cost_basis  
            if cb:  
              pnl  = -filled_amt * (prc - cb)  
              sign = '+' if pnl > 0 else '-'  
              pnl  = '  ({}{})'.format(sign, '%.0f' % abs(pnl))  
          if o.stop:  
            style = ' stop {}'.format(o.stop)  
            if o.limit: style = ' stop () limit {}'.format(o.stop, o.limit)  
          elif o.limit: style = ' limit {}'.format(o.limit)  
          if o.filled == o.amount: del c.trac[o.id]  
          _trac('   {} {} {}{} at {}{}{}'.format(  
            'Bot' if o.amount > 0 else 'Sold', filled, o.sid.symbol, now,  
            '%.2f' % prc, pnl, style).ljust(52) + '  {}  {}'.format(cash, o.id[-4:] if c.t_opts['log_ids'] else ''))  
      elif c.t_opts['log_unfilled'] and not (o.stop or o.limit):  
        _trac('      {} {}{} unfilled  {}'.format(o.sid.symbol, o.amount,  
         ' limit' if o.limit else '', o.id[-4:] if c.t_opts['log_ids'] else ''))

    oo = get_open_orders().values()  
    if not oo: return                       # Handle new orders  
    cash = ''  
    if (c.t_opts['log_neg_cash'] and c.portfolio.cash < 0) or c.t_opts['log_cash']:  
      cash = str(int(c.portfolio.cash))  
    for oo_list in oo:  
      for o in oo_list:  
        if c.t_opts['symbols'] and (o.sid.symbol not in c.t_opts['symbols']): continue  
        if o.id in c.trac: continue         # Only new orders beyond this point  
        prc = data.current(o.sid, 'price') if data.can_trade(o.sid) else c.portfolio.positions[o.sid].last_sale_price  
        c.trac[o.id] = 0 ; style = ''  
        now  = ' ({})'.format(c.portfolio.positions[o.sid].amount) if c.portfolio.positions[o.sid].amount else ' _'  
        if o.stop:  
          style = ' stop {}'.format(o.stop)  
          if o.limit: style = ' stop {} limit {}'.format(o.stop, o.limit)  
        elif o.limit: style = ' limit {}'.format(o.limit)  
        _trac('{} {} {}{} at {}{}'.format('Buy' if o.amount > 0 else 'Sell',  
          o.amount, o.sid.symbol, now, '%.2f' % prc, style).ljust(52) + '  {}  {}'.format(cash, o.id[-4:] if c.t_opts['log_ids'] else ''))
There was a runtime error.
5 responses

Harry,
Try to change this line:

prices_60m = data.history(context.stock, 'close', 18000, '1m').resample('60T').last().bfill()  

Hi Vladimir,

Thanks for the help! I put that in my code and it worked! I have one question though: is backfill a proper solution, or more of a quick fix? Shouldn’t none of the values be NaN?

Harry Ziegler.

To me bfill() is better solution than ffill() or dropna().

Hi again,

It seems that I have a gap in understanding with regards to how the data.history DataFrame works. Does the 1 minute DataFrame return NaN values before/after trading hours? If not, then why is the resampled DataFrame doing that?

Thanks,
Harry

Hey guys,

I just wanted to give you a little update on this issue. I figured it out by poring over previous Quantopian posts (https://www.quantopian.com/posts/how-to-use-the-resample-correctly, https://www.quantopian.com/posts/resampling-other-timeframes, and https://www.quantopian.com/posts/algorithm-takes-time-to-warm-up). Much thanks to Dan Whitnable for all the answers! Here's the code I ended up with for calculating 1 hour bars:

prices_60m = data.history(context.stock, 'close', 18000, '1m').resample('60T', label = 'right', closed = 'right').mean().dropna()