how to upload several stocks data in backtrader?

[coddy]

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.

[run-out]

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)

[coddy]

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())

[run-out]

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

conn = sqlite3.connect("stocks")

Is "stocks" the file location of your database?

[coddy]

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

[coddy]

here is the kind of data in csv:

[run-out]

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

class SmaCross(bt.SignalStrategy):

Try changing this to:

class SmaCross(bt.Strategy):

[vladisld]

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

[carsten]

@coddy

Im using as well a database, did you got a final solution, could you post it here?
Thanks

[carsten]

@vladisld
just took a look at GitHub, but can't find the PSQL Datafeed in the code..does it handle as well dividends and splits? There are examples for Zipline which do that

[carsten]

basically it's sending data, but without the ticker name.
I'm clearly missing something. I was searching the documentation but did not find were to include the ticker name

could someone help my?
thanks in advance

engine = create_engine('mysql+mysqlconnector://root:root@localhost/securities_master')

def process_stocks():    
    ticker_id = available_stocks() # reads from db all available ticker and return ticker and ticker.id in db
    symbols = ticker_id.ticker

    for symbol in tqdm(symbols):

        # find the security_id for the symbol / ticker
        security_id = ticker_id.loc[ticker_id.ticker == symbol ].id.iloc[0]  
        # Make a database query
        query =  """SELECT trade_date as datetime, open, high, low, close, volume 
                      FROM daily_price WHERE security_id = {} order by trade_date """.format(security_id)            
        
        dataframe = pd.read_sql_query(query, engine, index_col='datetime', parse_dates=['datetime']) 
        if not dataframe.empty: 
            
            # what to do with 'symbol' or tickername???? 
       
            data = bt.feeds.PandasData(dataname=dataframe)
            cerebro.adddata(data)

[run-out]

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

cerebro.adddata(data)

Use:

cerebro.adddata(data, name="insert your security name here")