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, 30I 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:
-
@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
-
Im using as well a database, did you got a final solution, could you post it here?
Thanks -
@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 -
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 namecould someone help my?
thanks in advanceengine = 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)
-
@carsten said in how to upload several stocks data in backtrader?:
cerebro.adddata(data)
Use:
cerebro.adddata(data, name="insert your security name here")