Navigation

    Backtrader Community

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    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?

    General Code/Help
    4
    12
    310
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • C
      coddy last edited by

      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.

      1 Reply Last reply Reply Quote 0
      • run-out
        run-out last edited by

        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 Reply Last reply Reply Quote 1
        • C
          coddy last edited by

          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 1 Reply Last reply Reply Quote 0
          • run-out
            run-out last edited by

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

            conn = sqlite3.connect("stocks")

            Is "stocks" the file location of your database?

            1 Reply Last reply Reply Quote 0
            • C
              coddy last edited by

              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
              
              1 Reply Last reply Reply Quote 0
              • C
                coddy last edited by

                here is the kind of data in csv:

                download.png

                1 Reply Last reply Reply Quote 0
                • run-out
                  run-out @coddy last edited by

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

                  class SmaCross(bt.SignalStrategy):

                  Try changing this to:

                  class SmaCross(bt.Strategy):
                  
                  1 Reply Last reply Reply Quote 1
                  • vladisld
                    vladisld last edited by

                    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 Reply Last reply Reply Quote 1
                    • C
                      carsten last edited by

                      @coddy

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

                      1 Reply Last reply Reply Quote 0
                      • C
                        carsten last edited by

                        @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

                        1 Reply Last reply Reply Quote 0
                        • C
                          carsten last edited by

                          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 1 Reply Last reply Reply Quote 0
                          • run-out
                            run-out @carsten last edited by

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

                            cerebro.adddata(data)

                            Use:

                            cerebro.adddata(data, name="insert your security name here")
                            
                            1 Reply Last reply Reply Quote 0
                            • 1 / 1
                            • First post
                              Last post
                            Copyright © 2016, 2017, 2018 NodeBB Forums | Contributors
                            $(document).ready(function () { app.coldLoad(); }); }