Backtrader Community

lampalork

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

lampalork

@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

lampalork

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.

lampalork

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

lampalork

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'

lampalork

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'

lampalork

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

lampalork

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

lampalork

Hi @ab_trader ,
Great insight but unfortunately it does not solve the issue. I have also tried to set coc to false at broker level. Traces can be found below (same result in term of exit price but obviously entry price is different; in both case, the stop price is "ignored"). It is quite strange.

If you have any other ideas to investigate, I'm all ears :)

Regards
Lamp'

set coc=False for stop order

2020-07-16 17:13:54,243 [MainThread  ] [INFO ]  * * * * * REBALANCING * * * * *
2020-07-16 17:13:54,243 [MainThread  ] [INFO ]  Date: Friday 2016-06-24
2020-07-16 17:13:54,244 [MainThread  ] [INFO ]  Portfolio Value: 960,320.20
2020-07-16 17:13:54,244 [MainThread  ] [INFO ]  Cash: 1,000,000.00
2020-07-16 17:13:54,244 [MainThread  ] [INFO ]  Open Positions:
2020-07-16 17:13:54,244 [MainThread  ] [INFO ]  GBPUSD LONG size: 333668 entry price: 1.48380 unrealized pnl: -39,679.80 trading unit: 0
2020-07-16 17:13:54,244 [MainThread  ] [INFO ]  - - -
2020-07-16 17:13:54,259 [MainThread  ] [INFO ]  -- STOPLOSS for LONG Order Completed 2016-06-27 00:00:00, Status Completed: Ref: 2, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: -333668, Price: 1.45383 Created: 2016-06-23 00:00:00 Price: 1.3413 Size: -333668

set coc=False at broker level

2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  * * * * * REBALANCING * * * * *
2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  Date: Friday 2016-06-24
2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  Portfolio Value: 960,006.55
2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  Cash: 1,000,000.00
2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  Open Positions:
2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  GBPUSD LONG size: 333668 entry price: 1.48474 unrealized pnl: -39,993.45 trading unit: 0
2020-07-16 18:04:23,495 [MainThread  ] [INFO ]  - - -
2020-07-16 18:04:23,508 [MainThread  ] [INFO ]  -- STOPLOSS for LONG Order Completed 2016-06-27 00:00:00, Status Completed: Ref: 2, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: -333668, Price: 1.45383 Created: 2016-06-23 00:00:00 Price: 1.3413 Size: -333668

@ab_trader said in Stoploss not "working" on first day of trade:

coc=False

lampalork

Dear All,

I'm using stoploss with the parent-child mechanism. It seems that the stoploss order I define is NOT working properly on the first day of the trade and I would like to understand why. I have built a toy strategy to illustrate behaviour. The system is built for GBPUSD on daily bars and we use cheat-on-close. The scenario is as follows:

on Thursday 23-Jun-2016, we enter a long position (market order) with an attached stop-loss (stop order)
entry order filled (submitted, accepted and completed) on the close of 23-Jun-2016 (cheat-on-close): 1.48380
stoploss order to sent (submitted/accepted) at price 1.453830065123425
stoploss is hit on Friday 24-Jun-2016 (see status completed) but order is filled with the open price of next business day (Monday 27-Jun-2016) instead of the stop price (1.34130 instead of 1.453830065123425)
5 Finally, if I do the same thing but enter the position one day earlier (Wednesday 22-Jun-2016), the stop loss is indeed hit properly on Friday 24-Jun-2016 at the stop price

Any help on this would be greatly appreciated!

Regards
Lamp'

Market data looks like (O/H/L/C):

2020-07-16 10:14:43,685 [MainThread  ] [INFO ]  2016-06-23 00:00:00 - GBPUSD - 1.47176 - 1.49457 - 1.47108 - 1.48380- 0
2020-07-16 10:14:43,701 [MainThread  ] [INFO ]  2016-06-24 00:00:00 - GBPUSD - 1.48474 - 1.50160 - 1.32227 - 1.36488 - 0
2020-07-16 10:14:43,726 [MainThread  ] [INFO ]  2016-06-27 00:00:00 - GBPUSD - 1.34130 - 1.34813 - 1.31171 - 1.32250 - 0

code for placing orders is as follows:

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

and the logs are as follows:

2020-07-16 10:14:43,689 [MainThread  ] [INFO ]  * * * * * REBALANCING * * * * *
2020-07-16 10:14:43,689 [MainThread  ] [INFO ]  Date: Thursday 2016-06-23
2020-07-16 10:14:43,689 [MainThread  ] [INFO ]  Portfolio Value: 1,000,000.00
2020-07-16 10:14:43,689 [MainThread  ] [INFO ]  Cash: 1,000,000.00
2020-07-16 10:14:43,689 [MainThread  ] [INFO ]  Open Positions:
2020-07-16 10:14:43,689 [MainThread  ] [INFO ]  - - -
2020-07-16 10:14:43,700 [MainThread  ] [INFO ]  -- ENTRY LONG Order Submitted 2016-06-24 00:00:00, Status Submitted: Ref: 1, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: 333668, Price: NA Created: 2016-06-23 00:00:00 Price: 1.4838 Size: 333668
2020-07-16 10:14:43,700 [MainThread  ] [INFO ]  -- STOPLOSS for LONG Order Submitted 2016-06-24 00:00:00, Status Submitted: Ref: 2, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: -333668, Price: 1.45383 Created: 2016-06-23 00:00:00 Price: 1.453830065123425 Size: -333668
2020-07-16 10:14:43,700 [MainThread  ] [INFO ]  -- ENTRY LONG Order Accepted 2016-06-24 00:00:00, Status Accepted: Ref: 1, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: 333668, Price: NA Created: 2016-06-23 00:00:00 Price: 1.4838 Size: 333668
2020-07-16 10:14:43,700 [MainThread  ] [INFO ]  -- STOPLOSS for LONG Order Accepted 2016-06-24 00:00:00, Status Accepted: Ref: 2, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: -333668, Price: 1.45383 Created: 2016-06-23 00:00:00 Price: 1.453830065123425 Size: -333668
2020-07-16 10:14:43,700 [MainThread  ] [INFO ]  -- ENTRY LONG Order Completed 2016-06-24 00:00:00, Status Completed: Ref: 1, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: 333668, Price: NA Created: 2016-06-23 00:00:00 Price: 1.4838 Size: 333668
2020-07-16 10:14:43,700 [MainThread  ] [INFO ]  2016-06-24 00:00:00 - JPYUSD - 0.009425 - 0.010113 - 0.009364 - 0.009790 - 0 [USDJPY - 106.099000 - 106.789000 - 98.887000 - 102.143000]
2020-07-16 10:14:43,704 [MainThread  ] [INFO ]  * * * * * REBALANCING * * * * *
2020-07-16 10:14:43,705 [MainThread  ] [INFO ]  Date: Friday 2016-06-24
2020-07-16 10:14:43,705 [MainThread  ] [INFO ]  Portfolio Value: 960,320.20
2020-07-16 10:14:43,705 [MainThread  ] [INFO ]  Cash: 1,000,000.00
2020-07-16 10:14:43,705 [MainThread  ] [INFO ]  Open Positions:
2020-07-16 10:14:43,705 [MainThread  ] [INFO ]  GBPUSD LONG size: 333668 entry price: 1.48380 unrealized pnl: -39,679.80 trading unit: 0
2020-07-16 10:14:43,705 [MainThread  ] [INFO ]  - - -
2020-07-16 10:14:43,725 [MainThread  ] [INFO ]  -- STOPLOSS for LONG Order Completed 2016-06-27 00:00:00, Status Completed: Ref: 2, Name: GBPUSD-GBPUSD-FX-0-1.0-5, Size: -333668, Price: 1.45383 Created: 2016-06-23 00:00:00 Price Executed : 1.3413 Size: -333668

lampalork

@lampalork

Posts made by lampalork