Hi All,
Long overdue. In case it can be useful to her folks.

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num
from sqlalchemy import create_engine

class SQLiteData(DataBase):
    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900,1,1)),
        ('todate', dt.datetime.max),
        )

    def __init__(self):
        try:
            self.engine = create_engine('sqlite:///{0}'.format(self.p.database), echo=False)
            self._timeframe = self.p.timeframe
            self._compression = self.p.compression
            self._dataname = '{0}'.format(self.p.symbol)
        except Exception as ex:
            print('Unable to open database {0} - {1}'.format(self.p.database, ex))

    def start(self):
        try:
            self.conn = self.engine.connect()
            sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '"+ self.p.fromdate.strftime("%Y-%m-%d %H:%M:%S") +"' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"
            #print(sql_query)
            self.result = self.conn.execute(sql_query)
        except Exception as ex:
            print('Unable to read table historical_data from database {0} - {1}'.format(self.p.database, ex))
            return

    def stop(self):
        self.engine.dispose()

    def _load(self):
        one_row = self.result.fetchone()
        if one_row is None:
            return False
        self.lines.datetime[0] = date2num(dt.datetime.strptime(str(one_row[0]),'%Y-%m-%d %H:%M:%S')) # date parsing
        self.lines.open[0] = float(one_row[1])
        self.lines.high[0] = float(one_row[2])
        self.lines.low[0] = float(one_row[3])
        self.lines.close[0] = float(one_row[4])
        self.lines.volume[0] = int(one_row[5])
        self.lines.openinterest[0] = -1
        return True

Finally, managed to spend some time on this, Here is the final code for the benefit of others

class CommissionFF(CommInfoBase):
    params = (
        ('commtype', CommInfoBase.COMM_FIXED),
        ('commission', 0.0),
        ('commtype', None),
        ('interest_long', True),
        ('spread_long', 0.0050), # FF + 50bp
        ('spread_short', -0.0050) # FF - 50bp
    )

    def _get_credit_interest(self, data, size, price, days, dt0, dt1):
        # (days / 365) * abs(size) * price * (interest + spread)
        # long: positive commission (i.e. debit)
        # short: negative commission (i.e. credit)
        if size > 0: # Long
            return (days / 365) * size * price * max(data.fed_fund[0] + self.params.spread_long, 0)
        else: # Short
            return (days / 365) * size * price * max(data.fed_fund[0] + self.params.spread_short, 0)

Hi Eric,
You can implement your strategy with all your indicators. In the next() function, you can spool the OHLCV + all your indicators for every bar (or store these values into a structure) and write all these to a file at the end of your backtest (self.stop() method)

Regards

@tianjixuetu hi, as always, if you expect people to help you, you have to prepare a mini reproduceable example with code and data that highlights the problem you are facing. You can't expect people to try to guess what you are doing. This is not intended to be mean but rather to help you get the support you need

ok, my bad, very stupid issue. I'll now experiment with the use of a data line. I'll keep you posted.
Sorry for the mistake.

this line should be changed:

comminfo = CommissionFF
# must be changed to 
comminfo = CommissionFF()

regards,
Lamp'

Hi @run-out, essentially, I want to store data in sqlite, create data feeds for each symbol from SQLite (instead of genericCsv of Panda feed) and run a backtest. Down the road, I want to snap latest bars from broker and append these prices to sqlite table.
Regards
Lamp'

@run-out thanks a lot. I m building a dedicated feed using sqlalchemy (i.e. not using pandas). I ll publish as soon as I get a stable version

Latest version that resolves the memory consumption error described here:
https://community.backtrader.com/topic/2397/high-memory-consumption-while-optimizing-using-influxdb-data-feed

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num


class SQLiteData(DataBase):
    '''
    Fetches data from SQLite, and wraps it into a Feed consumable by cerebro
    takes url connection string in form of :
        sqlite://{database}

    this implementation assumes a single table (historical_data) with all prices,
    conforming to a schema similar to the following:

            symbol        TEXT,
            date          TEXT (YYYY-mm-dd HH:mm),
            open          REAL,
            high          REAL,
            low           REAL,
            close         REAL,
            volume        INTEGER,
            unique (symbol, date)

    if your databases are set up differently, you can override the
    start() method.
    '''

    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900, 1, 1)),
        ('todate', dt.datetime.max),

        # parameterized column indices for ease of overwriting/re-implementing
        ('datetime', 0),
        ('open', 1),
        ('high', 2),
        ('low', 3),
        ('close', 4),
        ('volume', 5),
        ('openinterest', -1),
    )

    def __init__(self):
        self._timeframe = self.p.timeframe
        self._compression = self.p.compression
        self._dataname = '{0}-{1:.2f}'.format(self.p.symbol, self.p.tick_value)

    def start(self):
        super(SQLiteData, self).start()
        self.biter = None
        self.preloaded = False

    def _preload(self):
        engine = self._connect_db()

        sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '" + self.p.fromdate.strftime(
            "%Y-%m-%d %H:%M:%S") + "' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"

        result = engine.execute(sql_query)
        dbars = result.fetchall()
        result.close()

        self.biter = iter(dbars)

    def preload(self):
        if not self.biter:
            self._preload()

        while self.load():
            pass

        self._last()
        self.home()

        self.biter = None
        self.preloaded = True

    def _load(self):
        if self.preloaded:
            return False

        if not self.biter:
            self._preload()

        try:
            bar = next(self.biter)
        except StopIteration:
            return False

        for field in self.getlinealiases():
            if field == 'datetime':
                self.lines.datetime[0] = date2num(dt.datetime.strptime(bar[self.p.datetime], '%Y-%m-%d %H:%M:%S'))
            elif field == 'volume':
                self.lines.volume[0] = bar[self.p.volume]
            else:
                # get the column index
                colidx = getattr(self.params, field)
                if colidx < 0:
                # column not present -- skip
                    continue

                # get the line to be set
                line = getattr(self.lines, field)
                line[0] = float(bar[colidx])

        return True


    def _connect_db(self):
        from sqlalchemy import create_engine
        url = 'sqlite:///{0}'.format(self.p.database)
        engine = create_engine(url, echo=False)
        return engine

thanks very much, guys. I finally took the time to look at this and it works. Furthermore solution is much simpler than expected. A simple override of prenext() does the job! hopefully, I manage to reduce the memory footprint of my backtest (20 years on a basket of 12 commodities futures with individual contracts; you can imagine how many Datafeed I have to handle!)

    def prenext(self):
        current_date = bt.utils.date.num2date(self.datetime[0])
        if current_date >= self.params.start_date:
            self.next()

Latest version that resolves the memory consumption error described here:
https://community.backtrader.com/topic/2397/high-memory-consumption-while-optimizing-using-influxdb-data-feed

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num


class SQLiteData(DataBase):
    '''
    Fetches data from SQLite, and wraps it into a Feed consumable by cerebro
    takes url connection string in form of :
        sqlite://{database}

    this implementation assumes a single table (historical_data) with all prices,
    conforming to a schema similar to the following:

            symbol        TEXT,
            date          TEXT (YYYY-mm-dd HH:mm),
            open          REAL,
            high          REAL,
            low           REAL,
            close         REAL,
            volume        INTEGER,
            unique (symbol, date)

    if your databases are set up differently, you can override the
    start() method.
    '''

    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900, 1, 1)),
        ('todate', dt.datetime.max),

        # parameterized column indices for ease of overwriting/re-implementing
        ('datetime', 0),
        ('open', 1),
        ('high', 2),
        ('low', 3),
        ('close', 4),
        ('volume', 5),
        ('openinterest', -1),
    )

    def __init__(self):
        self._timeframe = self.p.timeframe
        self._compression = self.p.compression
        self._dataname = '{0}-{1:.2f}'.format(self.p.symbol, self.p.tick_value)

    def start(self):
        super(SQLiteData, self).start()
        self.biter = None
        self.preloaded = False

    def _preload(self):
        engine = self._connect_db()

        sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '" + self.p.fromdate.strftime(
            "%Y-%m-%d %H:%M:%S") + "' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"

        result = engine.execute(sql_query)
        dbars = result.fetchall()
        result.close()

        self.biter = iter(dbars)

    def preload(self):
        if not self.biter:
            self._preload()

        while self.load():
            pass

        self._last()
        self.home()

        self.biter = None
        self.preloaded = True

    def _load(self):
        if self.preloaded:
            return False

        if not self.biter:
            self._preload()

        try:
            bar = next(self.biter)
        except StopIteration:
            return False

        for field in self.getlinealiases():
            if field == 'datetime':
                self.lines.datetime[0] = date2num(dt.datetime.strptime(bar[self.p.datetime], '%Y-%m-%d %H:%M:%S'))
            elif field == 'volume':
                self.lines.volume[0] = bar[self.p.volume]
            else:
                # get the column index
                colidx = getattr(self.params, field)
                if colidx < 0:
                # column not present -- skip
                    continue

                # get the line to be set
                line = getattr(self.lines, field)
                line[0] = float(bar[colidx])

        return True


    def _connect_db(self):
        from sqlalchemy import create_engine
        url = 'sqlite:///{0}'.format(self.p.database)
        engine = create_engine(url, echo=False)
        return engine

Thanks a lot. My previous stuff was working but it was breaking while running optimization with multiple cpu because of the way it was designed. This psql example has shown me what to do.

Here is the new version for the benefits of others. Happy Friday!

Lamp'

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num

class SQLiteData(DataBase):
   
    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900, 1, 1)),
        ('todate', dt.datetime.max),

        # parameterized column indices for ease of overwriting/re-implementing
        ('datetime', 0),
        ('open', 1),
        ('high', 2),
        ('low', 3),
        ('close', 4),
        ('volume', 5),
        ('openinterest', -1),
    )

    def __init__(self):
        self._timeframe = self.p.timeframe
        self._compression = self.p.compression
        self._dataname = '{0}-{1:.2f}'.format(self.p.symbol, self.p.tick_value)

    def start(self):
        engine = self._connect_db()

        sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '" + self.p.fromdate.strftime(
            "%Y-%m-%d %H:%M:%S") + "' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"

        result = engine.execute(sql_query)
        self.price_rows = result.fetchall()
        result.close()

        self.price_i = 0
        super(SQLiteData, self).start()

    def _load(self):
        if self.price_i >= len(self.price_rows):
            return False
        row = self.price_rows[self.price_i]
        self.price_i += 1

        for datafield in self.getlinealiases():
            if datafield == 'datetime':
                self.lines.datetime[0] = date2num(dt.datetime.strptime(row[self.p.datetime],'%Y-%m-%d %H:%M:%S'))
            elif datafield == 'volume':
                self.lines.volume[0] = row[self.p.volume]

            else:
                # get the column index
                colidx = getattr(self.params, datafield)
                if colidx < 0:
                    # column not present -- skip
                    continue
                # get the line to be set
                line = getattr(self.lines, datafield)
                line[0] = float(row[colidx])

        return True


    def _connect_db(self):
        from sqlalchemy import create_engine
        url = 'sqlite:///{0}'.format(self.p.database)
        engine = create_engine(url, echo=False)
        return engine


    def preload(self):
        super(SQLiteData, self).preload()
        # preloaded - no need to keep the price_rows as data is stored in the cerebro's Lines
        self.price_rows = None

Hi All,
Long overdue. In case it can be useful to her folks.

import datetime as dt
from backtrader import TimeFrame
from backtrader.feed import DataBase
from backtrader import date2num
from sqlalchemy import create_engine

class SQLiteData(DataBase):
    params = (
        ('database', None),
        ('symbol', 'XBTUSD'),
        ('tick_value', 0.01),
        ('timeframe ', TimeFrame.Minutes),
        ('compression', 1),
        ('fromdate', dt.datetime(1900,1,1)),
        ('todate', dt.datetime.max),
        )

    def __init__(self):
        try:
            self.engine = create_engine('sqlite:///{0}'.format(self.p.database), echo=False)
            self._timeframe = self.p.timeframe
            self._compression = self.p.compression
            self._dataname = '{0}'.format(self.p.symbol)
        except Exception as ex:
            print('Unable to open database {0} - {1}'.format(self.p.database, ex))

    def start(self):
        try:
            self.conn = self.engine.connect()
            sql_query = "SELECT `date`,`open`,`high`,`low`,`close`,`volume` FROM `historical_data` WHERE `symbol` = '" + self.p.symbol + "' AND `date` between '"+ self.p.fromdate.strftime("%Y-%m-%d %H:%M:%S") +"' and '" + self.p.todate.strftime("%Y-%m-%d %H:%M:%S") + "' ORDER BY `date` ASC"
            #print(sql_query)
            self.result = self.conn.execute(sql_query)
        except Exception as ex:
            print('Unable to read table historical_data from database {0} - {1}'.format(self.p.database, ex))
            return

    def stop(self):
        self.engine.dispose()

    def _load(self):
        one_row = self.result.fetchone()
        if one_row is None:
            return False
        self.lines.datetime[0] = date2num(dt.datetime.strptime(str(one_row[0]),'%Y-%m-%d %H:%M:%S')) # date parsing
        self.lines.open[0] = float(one_row[1])
        self.lines.high[0] = float(one_row[2])
        self.lines.low[0] = float(one_row[3])
        self.lines.close[0] = float(one_row[4])
        self.lines.volume[0] = int(one_row[5])
        self.lines.openinterest[0] = -1
        return True

@run-out thanks a lot. I m building a dedicated feed using sqlalchemy (i.e. not using pandas). I ll publish as soon as I get a stable version

In short, I'm designing an Expert Advisor based on backtrader. I worked with daily bars. SQLite will be clearly fast enough for my needs (in fact, I'm already ok with CSV perf). I will be snapping latest market data from a broker (for this; it will be easier/cleaner to add data to the database than CSV), compute signals and list down order required for rebalancing the portfolio.

It is a partial match; I was hoping someone had already ready-to-run code. But if nobody has anything better; I'll find some time to look at this PR and adapt it. Thanks for checking @run-out

Hi @run-out, essentially, I want to store data in sqlite, create data feeds for each symbol from SQLite (instead of genericCsv of Panda feed) and run a backtest. Down the road, I want to snap latest bars from broker and append these prices to sqlite table.
Regards
Lamp'

Dear Community,

Does anyone has a SQLite example (database schema + class extending AbstractDatabase) that she is willing to share?
For now, I'm just looking at setting of simple database with one table that will host daily bars (symbol, date, open, high, low, close, volume).

Thanks a lot in advance
Lamp'

For now, I'm just submitting 2 separate orders (one for LONG/SHORT and this other for stoploss). this achieves the results I want. regards

hello @ab_trader
quick update. I did not resolve the issue per se but I have found the root cause. Issue lies with the parent/child mechanism.

if I submit the 2 orders (main order and stoploss order) separately as follows:

main_order = self.buy(exectype=bt.Order.Market,
                                    data=data0,
                                    size=size,
                                    name='ENTRY LONG',
                                    transmit=False)
stoploss_order = self.sell(exectype=bt.Order.Stop,
                                     data=data0,
                                     price=stop_price,
                                     size=size,
                                     name='STOPLOSS for LONG',
                                     parent=main_order)

Then the stoploss is actually hit at stop price (1.453830065123425)
See output:

-- ENTRY LONG Order Submitted 2016-06-24 00:00:00, Status Submitted: Ref: 1, Name: GBPUSD, Size: 333668 Price: NA Created: 2016-06-23 00:00:00
-- STOPLOSS for LONG Order Submitted 2016-06-24 00:00:00, Status Submitted: Ref: 2, Name: GBPUSD, Size: -333668 Price: 1.45383 Created: 2016-06-23 00:00:00
-- ENTRY LONG Order Completed 2016-06-24 00:00:00, Status Completed: Ref: 1, Name: GBPUSD, Size: 333668, Price: NA Executed: 2016-06-24 00:00:00 Price: 1.48474 Size: 333668
-- STOPLOSS for LONG Order Completed 2016-06-24 00:00:00, Status Completed: Ref: 2, Name: GBPUSD, Size: -333668, Price: 1.45383 Executed: 2016-06-24 00:00:00 Price: 1.453830065123425 Size: -333668
--------------------------------  NOTIFY TRADE  --------------------------------
2016-06-24 00:00:00, Avg Price: 1.48474, Profit, Gross -10313.66, Net -10313.66
--------------------------------------------------------------------------------

Kind regards
Bertrand