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:

SQLite Database looks like:

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

@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

This is exactly what I was looking for.

Thank you very much.

Alfred Sisley

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

Have data for both the S&P500 (for which to trade) & economic data for which to use as a signal with technical indicators to trade on daily/weekly(resample)/monthly(resample) timeframes.

A few issues:

1. I'm trying to trade on a monthly basis and for some reason when a buy order occurs, it occurs for every day in the month (20 trading days), this is not intended.
2. I've attempted to isolate trading to SPX(S&P500) in next() but there may be a logic fail here.

class MyStrategy(bt.Strategy):
    params = (('roc1', 12),('roc2', 12),('roc3', 12))

    def __init__(self):

        #data sources
        self.daily_data = self.datas[0::3]
        self.weekly_data = self.datas[1::3]
        self.monthly_data = self.datas[2::3]
        #track orders
        self.order = {}

        #indicators
        self.roc1 = {}
        self.roc2 = {}
        self.roc3 = {}

        for i, d in enumerate(self.daily_data):   
            self.order[d._name] = None
            roc1 = bt.indicators.ROC100(d, period=self.params.roc1)      
            self.roc1[d] = roc1
     
        for i, d in enumerate(self.weekly_data):   
            self.order[d._name] = None   
            roc2 = bt.indicators.ROC100(d, period=self.params.roc2)     
            self.roc2[d] = roc2
  
        for i, d in enumerate(self.monthly_data):   
            self.order[d._name] = None
            roc3 = bt.indicators.ROC100(d, period=self.params.roc3)   
            self.roc3[d] = roc3

def notify_order(self, order):
        # Check if an order has been completed
        if order.status in [order.Completed]:
            self.log(
                f"{order.data._name:<6} {('BUY' if order.isbuy() else 'SELL'):<5} "
                # f"EXECUTED for: {dn} "
                f"Price: {order.executed.price:6.2f} "
                f"Value: {order.executed.value:6.2f} "
                f"Comm: {order.executed.comm:4.4f} "
                f"Size: {order.created.size:9.4f} "
            )

    def next(self):
        
        for i, (d, d1, d2) in enumerate(zip(self.daily_data, self.weekly_data, self.monthly_data)):
            dt, (dn, d1n, d2n) = self.datetime.datetime(), (d._name, d1._name, d2._name)

            pos = self.getposition(d2).size 
            cash =self.broker.getcash()

            if d2._name == "SPX":
 
                if not pos and not self.order["SPX"]:
                    if self.roc3[d2][0] < 0:
                        print(self.roc3[d2][0])
                        self.buy(data=d2,size=1)
            
                if pos and not self.order['SPX']:
                    if self.roc3[d2][0] > 0:
                        print(self.roc3[d2][0])
                        self.close(data=d2,size=1)

#Instantiate Cerebro engine
cerebro = bt.Cerebro(stdstats=True) 

#Set cash
cerebro.broker.setcash(100000.0)
#Set commissions
comminfo = FixedCommisionScheme()
cerebro.broker.addcommissioninfo(comminfo)
#Add strategy to Cerebro
cerebro.addstrategy(MyStrategy)

#Add data feeds
symbols = ['GDP','SPX']

for symbol in symbols:
    #Add data
    data0 = bt.feeds.InfluxDB(host='localhost', port='8086',
                            username='root',
                            password='root',
                            database='test_data12345',
                            dataname=symbol,
                            timeframe=bt.TimeFrame.Days, 
                            compression=1,
                            fromdate=datetime.datetime(1997, 1, 2),
                            todate=datetime.datetime(2022, 8, 12), 
                            dtformat=("%d-%m-%Y"),
                            high='high',
                            low='low',
                            open='open',
                            close='close',
                            volume='volume',
                            ointerest='openinterest')

    #Add the Data Feed to Cerebro
    cerebro.adddata(data0,name=symbol)
    cerebro.resampledata(data0, timeframe = bt.TimeFrame.Weeks, compression = 1, name=symbol)
    cerebro.resampledata(data0, timeframe = bt.TimeFrame.Months, compression = 1, name=symbol)

#Begining portfolio value
print('Starting Portfolio Value: %.2f' % cerebro.broker.getvalue())

#Add trading statistics/ look for time return
cerebro.addanalyzer(bt.analyzers.TradeAnalyzer, _name= 'Basic_Stats')

#Define Cerebro Engine
strats = cerebro.run()
strat = strats[0]

#Ending portfolio value
print('Final Portfolio Value: %.2f' % cerebro.broker.getvalue())

#Run Cerebro Engine
for s in strat.analyzers:
    s.print()


# And plot it with a single command
cerebro.plot()

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

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

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

@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

@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

@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:

Thanks

Alfred Sisley

@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:

5 Tickers After:

Thanks

Alfred Sisley

Thanks @vladisld!

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

• Memory Usage @ 5 tickers:
  

• Memory Usage of 10 tickers:
  

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

This is exactly what I was looking for.

Thank you very much.

Alfred Sisley