3. how to upload several stocks data in backtrader?
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?

  • C

    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.

    0

  • 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)
    1
  • C

    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())
    0 1 Reply

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

    conn = sqlite3.connect("stocks")

    Is "stocks" the file location of your database?

    0
  • C

    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
    0
  • C

    here is the kind of data in csv:

    download.png

    0

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

    class SmaCross(bt.SignalStrategy):

    Try changing this to:

    class SmaCross(bt.Strategy):
    1

  • 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

    1
Log in to reply
 
});