Navigation

    Backtrader Community

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search
    1. Home
    2. AlfredSisley
    For code/output blocks: Use ``` (aka backtick or grave accent) in a single line before and after the block. See: http://commonmark.org/help/
    A
    • Profile
    • Following 0
    • Followers 0
    • Topics 4
    • Posts 23
    • Best 4
    • Groups 0

    AlfredSisley

    @AlfredSisley

    4
    Reputation
    20
    Profile views
    23
    Posts
    0
    Followers
    0
    Following
    Joined Last Online

    AlfredSisley Unfollow Follow

    Best posts made by AlfredSisley

    • SQLite Database - IndexError: array index out of range

      Hi All,

      Been using BT for a couple of weeks now, looking to use SQLite database as datafeed since CSV's lead to a state of "insufficient memory" and don't run all the way through (6 month period/1-minute data / 500 stocks).

      I've set up the database on my drive and uploaded it with my data (source: IQFeed).

      The script I use to transfer CSV data to the database is this:

      import sqlite3
      import time
      import datetime
      import random
      import pandas as  pd
      from sp500tickers import get_sp500_tickers
      
      file_path = r"C:\Users\julia\Desktop\Python\Project - Automated_Trading\Tickers"
      
      conn = sqlite3.connect('test_database.db')
      c = conn.cursor()
      
      def create_table():
          c.execute('CREATE TABLE IF NOT EXISTS stuffToPlot (ticker TEXT, datetime TEXT, open REAL, low REAL, high REAL, close REAL, volume INTEGER, openinterest INTEGER)')
          
      def data_entry():
          
          tickers = get_sp500_tickers().head(1)
      
          for ticker in tickers:
              data = file_path + '/' + ticker +'.csv'
              df = pd.read_csv(data,header=None)
              df.columns=['datetime','open','low','high','close','volume','openinterest']
              df['ticker'] = ticker #Add ticker column
              df.set_index('datetime')
              print(df.index)
      
              df.to_sql('stuffToPlot', conn, if_exists='append', index= False)
      
          conn.commit()
      
      data_entry()
      
      

      Data looks like:

      d173e636-2664-48db-bb72-f6c6d93d1fbe-image.png
      SQLite Database looks like:

      5275a1ed-1783-4ad6-b7a6-4363fd1c4184-image.png

      I've included all the tickers in a single database table. Also note the ticker column.

      I'm getting the following error:

      Traceback (most recent call last):
        File "c:/Users/julia/Desktop/Python/Project - Automated_Trading/Algo_sql.py", line 254, in <module>
          cerebro.run()
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\cerebro.py", line 1127, in run
          runstrat = self.runstrategies(iterstrat)
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\cerebro.py", line 1293, in runstrategies
          self._runonce(runstrats)
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\cerebro.py", line 1652, in _runonce
          strat._once()
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\lineiterator.py", line 297, in _once
          indicator._once()
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\lineiterator.py", line 297, in _once
          indicator._once()
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\linebuffer.py", line 630, in _once
          self.oncestart(self._minperiod - 1, self._minperiod)
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\lineroot.py", line 165, in oncestart
          self.once(start, end)
        File "C:\Users\julia\AppData\Local\Programs\Python\Python38-32\lib\site-packages\backtrader\linebuffer.py", line 672, in once
          dst[i] = src[i + ago]
      IndexError: array index out of range
      

      For simplicity, I've kept next() blank.

      The datafeed class looks like (inspired by online examples):

      class MySQLData(DataBase): 
          params = (
          ('ticker', ''),
          ('fromdate', datetime.date(2020,1,2)),
          ('todate', datetime.date(2020, 5,22)),
          ('name', ''),
          ('compression', 1),
          ('timeframe', bt.TimeFrame.Minutes),
          
          )
      
      
          def start(self):
      
              DB_LOCATION = r"C:\Users\julia/test_database.db" 
              self.connection = sqlite3.connect(DB_LOCATION) 
              self.cur = self.connection.cursor()
              self.result = self.connection.execute("SELECT `datetime`,`open`,`low`,`high`,`close`,`volume`,'openinterest', 'ticker' FROM `stuffToPlot` WHERE 'ticker' ="  + '?' + " AND `datetime` between '"+ self.p.fromdate.strftime('%Y-%m-%d')+"' and '"+self.p.todate.strftime('%Y-%m-%d')+"' ORDER BY `datetime` ASC",(str(self.p.ticker),))
       
          def stop(self):
              self.connection.close()
              self.cur.close()
            
          def _load(self):
              one_row = self.result.fetchone()
              if one_row is None:
                  return False
              self.lines.datetime[0] = date2num(one_row[0]) 
              self.lines.open[0] = float(one_row[1])
              self.lines.high[0] = float(one_row[3])
              self.lines.low[0] = float(one_row[2])
              self.lines.close[0] = float(one_row[4])
              self.lines.volume[0] = int(one_row[5])
              self.lines.openinterest[0] = int(one_row[6])
              return True
      

      In the main, I run a loop to add all the feeds:

      if __name__ == '__main__':
          symbols = ['MMM']
      
          for s in symbols:
              data = MySQLData(ticker=s,name=s,timeframe=bt.TimeFrame.Minutes,compression=1,fromdate=datetime.date(2020,1,2),todate=datetime.date(2020, 5,22),datetime=0,open=1,low=2,high=3,close=4,volume=5,openinterest=6,nullvalue=0.0,dtformat=('%Y-%m-%d %H:%M:%S'),tmformat=('%H:%M:%S')) 
              
              
              cerebro.adddata(data)
              
      
          cerebro.run()
      

      It's entirely possible that this is more of a SQLite question but any help on this is greatly appreciated!

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: SQLite Database - IndexError: array index out of range

      @vladisld Thanks for the link.

      Getting the following error:

      usage: iqfeed-to-influxdb.py [-h] (--ticker TICKER | --ticker-list TICKER_LIST) [--dbhost DBHOST] [--dbport DBPORT] [--iqhost IQHOST] [--iqport IQPORT] [--username USERNAME] [--password PASSWORD]      
                                   [--database DATABASE] [--fromdate FROMDATE] [--todate TODATE] [--debug] [--info]
      iqfeed-to-influxdb.py: error: one of the arguments --ticker --ticker-list is required
      

      I'm not exactly sure where in the code to add my tickers.

      I believe it's here:

          def get_tickers_from_file(self, filename):   #def get_tickers_from_file(self, filename):
              """Load ticker list from txt file"""
              if not os.path.exists(filename):
                  log.error("Ticker List file does not exist: %s", filename)
      
              tickers = []
              with io.open(filename, 'r') as fd:
                  for ticker in fd:
                      tickers.append(ticker.rstrip())
              return tickers
      

      I've added a global variable called 'filename' with the name of of the file as a string but that doesn't seem to work.

      Any insight on how to get started with this. Appreciate your patience...

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: PercentRank

      This is exactly what I was looking for.

      Thank you very much.

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: PercentRank

      @run-out You are correct. Needed to go back to the drawing board as a result. Thank you.

      posted in General Code/Help
      A
      AlfredSisley

    Latest posts made by AlfredSisley

    • Stop Loss using Timing and Current Profit

      Hi All,

      How would one implement the following stop loss:

      Pseudo Code

      IF Length of Trade >= 20 minutes AND Current Profit on Trade < 0.1%:
           self.close()
      

      Thanks,
      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: PercentRank

      @run-out You are correct. Needed to go back to the drawing board as a result. Thank you.

      posted in General Code/Help
      A
      AlfredSisley
    • RE: PercentRank

      Looking to somehow use PctRank to get bottom percentile of historical ROC data (historical = entire data set).

      posted in General Code/Help
      A
      AlfredSisley
    • RE: PercentRank

      @run-out

      One quick follow up question. Is there a way to access all historical close values in the init for each ticker?

      Thanks

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: Adding InfluxDB Datafeeds

      @vladisld said in Adding InfluxDB Datafeeds:

      pecs makes it a completely different discussion of cause, however with all that said:

      @vladisld

      I'll look into upgrading my machine.

      Are you surprised by how long it takes me to run my simple skeleton backtests given my hardware?

      Does it make you think there's something very wrong with my code?

      Sounds like you have no problem backtesting with similar hardare specs, but I can't seem to get anywhere.

      Appreciate your thoughts.

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: Adding InfluxDB Datafeeds

      @vladisld said in Adding InfluxDB Datafeeds:

      s sense of cause

      @vladisld If you mean running 1 ticker at a time, I did not, I was hoping to get results from a portfolio level.

      Given the current limitations, I will try this.

      At this point, short of changing the guts of cerebro, is there anything I can do on the software side to improve things? If not, would a better machine solve the problem?

      Current laptop:

      c2832ee4-7ca0-403c-a5e1-ed9f12e699b1-image.png

      Thanks

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: Adding InfluxDB Datafeeds

      @vladisld I added cerebro.run(exactbars=1) and the program managed to run through 100 tickers, which is good news!

      However, as you will see below, by capping my memory, it also increased the time required.

      It took around 16 seconds per ticker (100 tickers).

      Below is a before/after example of memory consumption using 5 tickers.

      For 500 tickers, I would be looking at 8,000 seconds (or roughly 2 hours) to run a backtest.

      At this point, what else can I do to materially speed things up?

      5 Tickers Before:

      memory-profile - 5 tickers (Before).png

      5 Tickers After:

      memory-profile - 5 tickers (After).png

      Thanks

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: Adding InfluxDB Datafeeds

      Thanks @vladisld!

      • I've made the changes to influxfeed.py, believe there was an improvement on run time.

      • Memory Usage @ 5 tickers:
        memory-profile - 5 tickers.png

      • Memory Usage of 10 tickers:
        memory-profile - 10 tickers.png

      Is there anything noticeable in the charts above that would suggest issues with the code?

      Is there any way of unloading the historical data from memory at any point in the process and just maintaining the buy/sell decisions?

      At this point, is my only alternative to upgrade my machine?

      Much Appreciated!

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: PercentRank

      This is exactly what I was looking for.

      Thank you very much.

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley
    • RE: Adding InfluxDB Datafeeds

      @vladisld Great post!

      • Very much enjoyed seeing graphic representation of memory usage. How would one do something similar? Was this done with a particular utility program?

      • In regards to your fix on github, how would I go about incorporating this into the code I'm currently using for InfluxDB? Is it as simple as adding the missing functions?
        def _preload(self):
        def preload(self):

      • You mentioned "Using Pool.imap chunksize=1 illustrate this ( specifying chunksize causes the Pool to use each worker process only for chunksize work items)". Would this look like cerebro.run(chunksize=1)?

      As always, immensely appreciate your insights.

      Alfred Sisley

      posted in General Code/Help
      A
      AlfredSisley