For code/output blocks: Use ``` (aka backtick or grave accent) in a single line before and after the block. See: http://commonmark.org/help/

how to upload several stocks data in backtrader?



  • Hi all,

    Can I know what shall I do to geed data in cerebro using a daframe read from a mysql database?
    the dataframe is like that:
    date, columns where columns are only tickers names of stocks;

    example:
    date AAPL MMM, AAA
    2020-01-01, 30, 30

    I have this piece of code:

    
    class MySQLdata():
    
    
        def __init__(self):
            conn = sqlite3.connect('sp500')
            self.conn = conn
    
        def start(self):
            
            c = self.conn.cursor()
            self.result = c.execute(
            
                       "SELECT `price_date`,`open_price`,`high_price`,`low_price`,`close_price`,`volume` FROM `daily_price_in` WHERE `symbol_id` = 'SBIN.NS' " + " AND `price_date` between '" + self.p.fromdate.strftime(
                    "%Y-%m-%d") + "' and '" + self.p.todate.strftime("%Y-%m-%d") + "' ORDER BY `price_date` ASC")
    
    
        def stop(self):
            c = self.conn.cursor()
            c.close() 
    
        def _load(self):
            ticker = 'AAPL'
            one_row = self.result.fetchone()
            if one_row is None:
                return False
    
            self.lines.datetime[0] = date2num(one_row[ticker][0]) # for intraday data, time also need to be combined here.
            self.lines.close[0] = float(one_row[ticker][4])
            return True
    
        def islive(self):
            return True
    
    class FixedCommisionScheme(bt.CommInfoBase):
        '''
        This is a simple fixed commission scheme
        '''
        params = (
            ('commission', 2),
            ('stocklike', True),
            ('commtype', bt.CommInfoBase.COMM_FIXED),
            )
    
        def _getcommission(self, size, price, pseudoexec):
            return self.p.commission
        
    # Create a subclass of SignaStrategy to define the indicators and signals
    
    class SmaCross(bt.SignalStrategy):
        # list of parameters which are configurable for the strategy
        params = dict(
            pfast=10,  # period for the fast moving average
            pslow=200   # period for the slow moving average
        )
    
        def __init__(self):
            sma1 = bt.ind.SMA(period=self.p.pfast)  # fast moving average
            sma2 = bt.ind.SMA(period=self.p.pslow)  # slow moving average
            crossover = bt.ind.CrossOver(sma1, sma2)  # crossover signal
            self.signal_add(bt.SIGNAL_LONG, crossover)  # use it as LONG signal
    
    
            print('Starting Portfolio Value: %.2f' % cerebro.broker.getvalue())
    
    #create a "Cerebro" engine instance
    cerebro = bt.Cerebro() 
    
    #slippage configured
    cerebro.broker = bt.brokers.BackBroker(slip_perc=0.0001)  # 0.01%
    cerebro.broker.setcash(100000.0)
    
    #Set commissions
    comminfo = FixedCommisionScheme()
    cerebro.broker.addcommissioninfo(comminfo)
    
    # Create a data feed
    # Pass it to the backtrader datafeed and add it to the cerebro
    #stocks = STOCKS_DB()
    #s= stocks.get()
    #s.index = pd.to_datetime(s['Date'])
    #ticker='AAPL'
    #dataframe = pd.DataFrame(s[ticker])
    #data = bt.feeds.PandasData(dataname=dataframe)
    
    data = MySQLdata()
        
    cerebro.adddata(data)  # Add the data feed
    
    cerebro.addstrategy(SmaCross)  # Add the trading strategy
    #print('Starting Portfolio Value: %.2f' % cerebro.broker.getvalue())
    cerebro.run()  # run it all
    print('Final Portfolio Value: %.2f' % cerebro.broker.getvalue())
    #cerebro.plot();
    
    

    thanks in advance.



  • Here's an example from something I built a while back. Might help.

        conn = sqlite3.connect("data/TestDatabase.db")    
        tickers = ["MSFT", "FB"]
        for t in tickers:
            sql = "SELECT datetime, open, high, low, close, vol FROM stuffToPlot WHERE datetime > '2020-01-01' and ticker='{}'".format(
                t
            )
            dataframe = pd.read_sql(sql, con=conn)
            dataframe["datetime"] = pd.to_datetime(dataframe["datetime"])
            dataframe = dataframe.set_index("datetime")
            dataframe = dataframe.rename(columns={'vol':'volume'})
            data = bt.feeds.PandasData(dataname=dataframe)
    
            print(dataframe.head())
            cerebro.adddata(data)
    
    


  • Thanks.

    I have tried this below but it gives nan values now. any ideas?

    class FixedCommisionScheme(bt.CommInfoBase):
        '''
        This is a simple fixed commission scheme
        '''
        params = (
            ('commission', 2),
            ('stocklike', True),
            ('commtype', bt.CommInfoBase.COMM_FIXED),
            )
    
        def _getcommission(self, size, price, pseudoexec):
            return self.p.commission
        
    # Create a subclass of SignaStrategy to define the indicators and signals
    
    class SmaCross(bt.SignalStrategy):
        # list of parameters which are configurable for the strategy
        params = dict(
            pfast=10,  # period for the fast moving average
            pslow=200   # period for the slow moving average
        )
    
        def __init__(self):
            sma1 = bt.ind.SMA(period=self.p.pfast)  # fast moving average
            sma2 = bt.ind.SMA(period=self.p.pslow)  # slow moving average
            crossover = bt.ind.CrossOver(sma1, sma2)  # crossover signal
            self.signal_add(bt.SIGNAL_LONG, crossover)  # use it as LONG signal
    
    
            print('Starting Portfolio Value: %.2f' % cerebro.broker.getvalue())
    
    cerebro = bt.Cerebro() 
    
    
    cerebro.broker = bt.brokers.BackBroker(slip_perc=0.0001)  # 0.01%
    cerebro.broker.setcash(100000.0)
    
    
    comminfo = FixedCommisionScheme()
    cerebro.broker.addcommissioninfo(comminfo)
    
    conn = sqlite3.connect("stocks")    
    tickers = ["AAPL", "A"]
    for t in tickers:
        sql = "SELECT * FROM stocks WHERE Date < '2020-01-01' and Date > '2010-01-01'"
        dataframe = pd.read_sql(sql, con=conn)
        dataframe["datetime"] = pd.to_datetime(dataframe["Date"])
        dataframe = dataframe.set_index("datetime")
        dataframe = dataframe.filter([t]) 
        data = bt.feeds.PandasData(dataname=dataframe)
    
        cerebro.adddata(data)
        
    cerebro.addstrategy(SmaCross)  # Add the trading strategy
    
    cerebro.run()  # run it all
    print('Final Portfolio Value: %.2f' % cerebro.broker.getvalue())
    
    


  • @coddy said in how to upload several stocks data in backtrader?:

    conn = sqlite3.connect("stocks")

    Is "stocks" the file location of your database?



  • yes.

    here is the code without database.
    I have got nan values.

    def runstrat(args=None):
    
        #args = parse_args()
        parser = argparse.ArgumentParser(description='Pandas test script')
        args, unknown = parser.parse_known_args()
            # Create a cerebro entity
        cerebro = bt.Cerebro()
        
        #slippage configured
        cerebro.broker = bt.brokers.BackBroker(slip_perc=0.0001)  # 0.01%
        cerebro.broker.setcash(100000.0)
    
        #Set commissions
        comminfo = FixedCommisionScheme()
        cerebro.broker.addcommissioninfo(comminfo)
    
    
        # Add a strategy
        cerebro.addstrategy(TestStrategy2)
        
    
        # Datas are in a subfolder of the samples. Need to find where the script is
        # because it could have been called from anywhere
        #modpath = os.path.dirname(os.path.abspath(sys.argv[0]))
        datapath = 'stock_dfs/AAPL.csv'
        dataPathVIX = 'stock_dfs/VIX.csv'
        dataPathSP500 ='stock_dfs/GSPC.csv'
    
        # Create a Data Feed
        # Simulate the header row isn't there if noheaders requested
        skiprows = 1 
        header = 1
    
        dataframe = pd.read_csv(datapath,
                                    skiprows=skiprows,
                                    header=header,
                                    parse_dates=True,
                                    index_col=0)
        data = MYPandasData(dataname=dataframe)
        
        dataframeVIX = pd.read_csv(dataPathVIX,
                                    skiprows=skiprows,
                                    header=header,
                                    parse_dates=True,
                                    index_col=0)
        dataVIX = MYPandasData(dataname=dataframeVIX)
        
        dataframeSP500 = pd.read_csv(dataPathSP500,
                                    skiprows=skiprows,
                                    header=header,
                                    parse_dates=True,
                                    index_col=0)
        dataSP500 = MYPandasData(dataname=dataframeSP500)
        #print(dataframe)
        
        #data = bt.feeds.YahooFinanceCSVData(dataname=datapath,fromdate=dt.datetime(2010, 1, 1),todate=dt.datetime(2020, 1, 1),reverse=False,
        #                                    timeframe=bt.TimeFrame.Days,compression=1,name='Yahoo')   
        #dataVIX = bt.feeds.YahooFinanceCSVData(dataname=dataPathVIX,fromdate=dt.datetime(2010, 1, 1),todate=dt.datetime(2020, 1, 1),reverse=False,
        #                                       timeframe=bt.TimeFrame.Days,compression=1,name='Yahoo')  
        #dataSP500 = bt.feeds.YahooFinanceCSVData(dataname=dataPathSP500,fromdate=dt.datetime(2010, 1, 1),todate=dt.datetime(2020, 1, 1),reverse=False,
        #                                         timeframe=bt.TimeFrame.Days,compression=1,name='Yahoo')  
       # bt_data = bt.feeds.PandasData(dataname=df_input,datetime='datetime',open=None,high=None,low=None,close='close',volume=None,openinterest=None)
        
        #dataframe = dataframe.rename(columns={'vol':'volume'})
        #data = bt.feeds.PandasData(dataname=dataframe)
    
        # Add the Data Feed to Cerebro
        cerebro.adddata(data)
        cerebro.adddata(dataVIX)
        cerebro.adddata(dataSP500)
        # Set our desired cash start
        cerebro.broker.setcash(1000000.0)
        # Analyzer
        #cerebro.addanalyzer(btanalyzers.SharpeRatio, _name='mysharpe')
    
        # Print out the starting conditions
        print('Starting Portfolio Value: %.2f' % cerebro.broker.getvalue())
    
        # Run over everything
        cerebro.addanalyzer(bt.analyzers.PyFolio, _name='pyfolio')
        results = cerebro.run()
        print('Ending Portfolio Value: %.2f' % cerebro.broker.getvalue())
            
    
        strat = results[0]
        pyfoliozer = strat.analyzers.getbyname('pyfolio')
    
        returns, positions, transactions, gross_lev = pyfoliozer.get_pf_items()
    
        #print('-- RETURNS')
        #print(returns)
        #print('-- POSITIONS')
        #print(positions)
        #print('-- TRANSACTIONS')
        #print(transactions)
        #print('-- GROSS LEVERAGE')
        #print(gross_lev)
    
        pf.create_full_tear_sheet(
            returns,
            positions=positions,
            transactions=transactions,
            round_trips=True)
    
        #if args.plot:
        #    cerebro.plot(style=args.plot_style)
            
            
    
    
    Starting Portfolio Value: 1000000.00
    <backtrader.linebuffer.LineBuffer object at 0x7fa9612c4310>
    2010-10-19, close, nan
    2010-10-20, close, nan
    2010-10-21, close, nan
    2010-10-22, close, nan
    2010-10-25, close, nan
    2010-10-26, close, nan
    2010-10-27, close, nan
    2010-10-28, close, nan
    2010-10-29, close, nan
    2010-11-01, close, nan
    


  • here is the kind of data in csv:

    download.png



  • @coddy said in how to upload several stocks data in backtrader?:

    class SmaCross(bt.SignalStrategy):

    Try changing this to:

    class SmaCross(bt.Strategy):
    


  • Just for the reference, you may take a look at the following PR that introduces the PSQL DataFeed: https://github.com/mementum/backtrader/pull/393


Log in to reply
 

});