loading data from a database

[julienbalas]

Hi
i try to apply the first program of the tutorial "smaCross" to data i already have in a sqlite Database.

from backtesting.lib import crossover
from backtesting.test import SMA
import sqlite3
import pandas as pd
cnx = sqlite3.connect('../finance_v2/finance_v2.db')
df = pd.read_sql_query('select "date" as datetime, open as Open, high as High, low as Low, "close" as Close from quotes where symbol =\'ATO.PA\' order by date limit 1000', cnx)

print(df)

class SmaCross(Strategy):
    def init(self):
        price = self.data.Close
        self.ma1 = self.I(SMA, price, 10)
        self.ma2 = self.I(SMA, price, 20)

    def next(self):
        if crossover(self.ma1, self.ma2):
            self.buy()
        elif crossover(self.ma2, self.ma1):
            self.sell()

bt = Backtest(df, SmaCross, commission=.002,
              exclusive_orders=True)
stats = bt.run()
#bt.plot()

the data seems to be correctly loaded, the print(df) shows
datetime Open High Low Close
0 2000-01-03 127.100754 138.434097 127.024696 131.436325
1 2000-01-04 133.261841 135.391586 121.091805 123.525810
2 2000-01-05 119.038109 119.798737 113.257347 113.257347
3 2000-01-06 113.333412 118.353546 110.366966 113.333412
4 2000-01-07 115.615288 121.624245 114.094032 120.331177
.. ... ... ... ... ...
995 2003-10-27 41.264008 41.264008 39.970943 40.807632
996 2003-10-28 40.883698 42.937389 40.769604 42.785263
997 2003-10-29 43.355732 44.192425 43.203609 43.926205
998 2003-10-30 44.078327 44.382580 42.975422 43.545891
999 2003-10-31 43.013451 44.344547 42.252823 43.659985

[1000 rows x 5 columns]

but on the "bt .run()"
i get
5_load_from_db.py:24: UserWarning: Data index is not datetime. Assuming simple periods, but pd.DateTimeIndex is advised.
exclusive_orders=True)

Q1) how can i convert my date to the correct datatype ?

then i get

/home/julien/.local/lib/python3.7/site-packages/backtesting/backtesting.py:149: RuntimeWarning: divide by zero encountered in true_divide
x = value / self._data.Close
/home/julien/.local/lib/python3.7/site-packages/backtesting/backtesting.py:864: RuntimeWarning: divide by zero encountered in double_scalars
// adjusted_price), size)
Traceback (most recent call last):
File "5_load_from_db.py", line 25, in
stats = bt.run()
File "/home/julien/.local/lib/python3.7/site-packages/backtesting/backtesting.py", line 1165, in run
broker.next()
File "/home/julien/.local/lib/python3.7/site-packages/backtesting/backtesting.py", line 765, in next
self._process_orders()
File "/home/julien/.local/lib/python3.7/site-packages/backtesting/backtesting.py", line 864, in _process_orders
// adjusted_price), size)
OverflowError: cannot convert float infinity to integer

i don't understand why there is a divide by zero as the Close values seems to be OK and i checked into the database, there is no null or 0 values

backtesting.py/backtesting/backtesting.py

Line 149 in 0107d4a

x = value / self._data.Close

Q2) how can i get around this error ?
Q3) is there another things to do in order to load the data into the dataframe ?
is df = pd.read_sql_query("my_query") suffisant ?

Best regards

[kernc]

Q1) how can i convert my date to the correct datatype ?

As the quick start tutorial points out, use pd.to_datetime() on your datetime column and use the result as df index:

df.index = pd.to_datetime(df['datetime'])

Q2) how can i get around this error ?

Ensure none of the Close values is 0! Also update backtesting.py to current master branch and see if the issue is maybe already fixed (a new release is pending shortly). Please provide a MWE that can be used to reproduce the issue.

[julienbalas]

Thanks for your quick reply
About Q1 --> my index is not longer showing a warning
About Q2 --> I've checked my database and there is indeed a 0 value, i was pretty sure there was none

Thanks