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
