def getData(provider, symbol, candle_size, start_date, end_date):
db = sqliteDB()
db.connect()
table_name = getTableName(provider, symbol, candle_size)
candleDf = db.getCandles(table_name, start_date, end_date)
db.disconnect()
return candleDf
def importIndustries(filepath='/Users/knbo/Downloads/industries.csv'):
def createTableIfNotExists(db, table_name):
sql = '''CREATE TABLE IF NOT EXISTS {} \
(industry_id int primary key, \
sector varchar(100), \
industry varchar(100)
)'''.format(table_name)
c = db.conn.cursor()
c.execute(sql)
db.conn.commit()
def insertIndustry(db, table_name, industry_info):
sql = '''INSERT INTO {} (industry_id, sector, industry) VALUES (?,?,?)'''.format(table_name)
c = db.conn.cursor()
try:
c.execute(sql, tuple(industry_info))
db.conn.commit()
return c.rowcount
except:
pass
table_name = 'simfin_industries'
db = sqliteDB()
db.connect()
createTableIfNotExists(db, table_name)
industries = csvToList(filepath)
for industry in industries:
ind = map(lambda x: x.replace('"', ''), industry[0].split(';'))
insertIndustry(db, table_name, list(ind))
db.disconnect()
def importCompanies():
def createTableIfNotExists(db, table_name):
sql = '''CREATE TABLE IF NOT EXISTS {} \
(simfin_id int primary key, \
ticker varchar(6), \
company_name varchar(100), \
industry_id int, \
month_fy_end int, \
num_employees int, \
description varchar(300)
)'''.format(table_name)
c = db.conn.cursor()
c.execute(sql)
db.conn.commit()
def insertCompany(db, table_name, company_info):
sql = '''INSERT INTO {} (simfin_id, ticker, company_name, industry_id, month_fy_end, num_employees, \
description) VALUES (?,?,?,?,?,?,?)'''.format(table_name)
c = db.conn.cursor()
try:
c.execute(sql, tuple(company_info))
db.conn.commit()
return c.rowcount
except:
pass
table_name = 'simfin_companies'
db = sqliteDB()
db.connect()
createTableIfNotExists(db, table_name)
companies = simfin.getListOfAllCompanies()
for company in companies['data']:
if (len(company) > 1 and company[1] > 'A'):
print(company[1])
company_info = simfin.getCompanyInfo(simfin_id=company[0])
insertCompany(db, table_name, company_info[0]['data'])
db.disconnect()
def __init__( self, flag, namecon, main ):
wx.Frame.__init__ ( self, parent=None, id = wx.ID_ANY, title = u"Data Quality -- Ввод данных подключения", pos = wx.DefaultPosition, size = wx.Size( 313,351 ), style = wx.CAPTION|wx.STAY_ON_TOP|wx.TAB_TRAVERSAL )
self.main = main
self.wc = work_con()
self.SetSizeHintsSz( wx.Size( 313,344 ), wx.Size(500,500 ))
bSizer2 = wx.BoxSizer( wx.VERTICAL )
self.m_splitter2 = wx.SplitterWindow( self, wx.ID_ANY, wx.DefaultPosition, wx.Size( -1,-1 ), 0 )
self.m_splitter2.SetSashSize( 0 )
self.m_splitter2.Bind( wx.EVT_IDLE, self.m_splitter2OnIdle )
self.m_panel4 = wx.Panel( self.m_splitter2, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
fgSizer2 = wx.FlexGridSizer( 1, 2, 0, 20 )
fgSizer2.SetFlexibleDirection( wx.HORIZONTAL )
fgSizer2.SetNonFlexibleGrowMode( wx.FLEX_GROWMODE_NONE )
self.m_staticText9 = wx.StaticText( self.m_panel4, wx.ID_ANY, u"Имя соединения:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText9.Wrap( -1 )
fgSizer2.Add( self.m_staticText9, 0, wx.ALL, 5 )
self.name_ctrl = wx.TextCtrl( self.m_panel4, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 170,-1 ) )
fgSizer2.Add( self.name_ctrl, 0, wx.ALL, 5 )
self.m_panel4.SetSizer( fgSizer2 )
self.m_panel4.Layout()
fgSizer2.Fit( self.m_panel4 )
self.m_panel5 = wx.Panel( self.m_splitter2, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
sbSizer8 = wx.StaticBoxSizer( wx.StaticBox( self.m_panel5, wx.ID_ANY, u"Свойства соединения" ), wx.VERTICAL )
fgSizer4 = wx.FlexGridSizer( 2, 2, 0, 0 )
fgSizer4.SetFlexibleDirection( wx.BOTH )
fgSizer4.SetNonFlexibleGrowMode( wx.FLEX_GROWMODE_SPECIFIED )
self.m_staticText12 = wx.StaticText( self.m_panel5, wx.ID_ANY, u"Имя:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText12.Wrap( -1 )
fgSizer4.Add( self.m_staticText12, 0, wx.ALL, 5 )
self.login_ctrl = wx.TextCtrl( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 190,-1 ) )
fgSizer4.Add( self.login_ctrl, 0, wx.ALL, 5 )
self.m_staticText14 = wx.StaticText( self.m_panel5, wx.ID_ANY, u"Пароль:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText14.Wrap( -1 )
fgSizer4.Add( self.m_staticText14, 0, wx.ALL, 5 )
self.passw_ctrl = wx.TextCtrl( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 190,-1 ), wx.TE_PASSWORD )
fgSizer4.Add( self.passw_ctrl, 0, wx.ALL, 5 )
self.m_staticText18 = wx.StaticText( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText18.Wrap( -1 )
fgSizer4.Add( self.m_staticText18, 0, wx.ALL, 5 )
self.sv_passw = wx.CheckBox( self.m_panel5, wx.ID_ANY, u"Сохранить пароль?", wx.DefaultPosition, wx.DefaultSize, 0 )
fgSizer4.Add( self.sv_passw, 0, wx.ALL, 5 )
self.m_staticText19 = wx.StaticText( self.m_panel5, wx.ID_ANY, u"IP адрес:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText19.Wrap( -1 )
fgSizer4.Add( self.m_staticText19, 0, wx.ALL, 5 )
self.ip_ctrl = wx.TextCtrl( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 190,-1 ), 0, Numbers() )
fgSizer4.Add( self.ip_ctrl, 0, wx.ALL, 5 )
self.m_staticText20 = wx.StaticText( self.m_panel5, wx.ID_ANY, u"Порт:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText20.Wrap( -1 )
fgSizer4.Add( self.m_staticText20, 0, wx.ALL, 5 )
self.port_ctrl = wx.TextCtrl( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 190,-1 ), 0, Numbers() )
fgSizer4.Add( self.port_ctrl, 0, wx.ALL, 5 )
self.sid_radio = wx.RadioButton( self.m_panel5, wx.ID_ANY, u"SID:", wx.DefaultPosition, wx.DefaultSize, 0 )
fgSizer4.Add( self.sid_radio, 0, wx.ALL, 5 )
self.sid_ctrl = wx.TextCtrl( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 190,-1 ) )
fgSizer4.Add( self.sid_ctrl, 0, wx.ALL, 5 )
self.service_radio = wx.RadioButton( self.m_panel5, wx.ID_ANY, u"Имя сервиса:", wx.DefaultPosition, wx.DefaultSize, 0 )
fgSizer4.Add( self.service_radio, 0, wx.ALL, 5 )
self.service_ctrl = wx.TextCtrl( self.m_panel5, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 190,-1 ) )
fgSizer4.Add( self.service_ctrl, 0, wx.ALL, 5 )
self.cancel_btn = wx.Button( self.m_panel5, wx.ID_ANY, u"Выход", wx.DefaultPosition, wx.DefaultSize, 0 )
fgSizer4.Add( self.cancel_btn, 0, wx.ALL, 5 )
self.ok_btn = wx.Button( self.m_panel5, wx.ID_ANY, u"OK", wx.DefaultPosition, wx.DefaultSize, 0 )
fgSizer4.Add( self.ok_btn, 0, wx.ALL|wx.ALIGN_RIGHT, 5 )
sbSizer8.Add( fgSizer4, 1, wx.EXPAND|wx.LEFT, 5 )
self.m_panel5.SetSizer( sbSizer8 )
self.m_panel5.Layout()
sbSizer8.Fit( self.m_panel5 )
self.m_splitter2.SplitHorizontally( self.m_panel4, self.m_panel5, 7 )
bSizer2.Add( self.m_splitter2, 1, 0, 5 )
self.SetSizer( bSizer2 )
self.Layout()
self.Centre( wx.BOTH )
# Working class with BD
self.sqlite = sqlite.sqliteDB(self.main.schema, self.main.table)
self.ok_btn.Bind( wx.EVT_BUTTON, self.OnOk )
self.cancel_btn.Bind( wx.EVT_BUTTON, self.OnCancel )
# Choose New or Edit
self.newcon = flag
self.namecon = namecon
if self.newcon is True:
self.move = True
logging.info(u'new connection creating')
elif self.newcon is False:
self.move = False
logging.info(u'editing connection: %s' % (namecon))
dbdata = self.wc.take_data_con(namecon)
try:
self.name_ctrl.SetValue(namecon)
self.ip_ctrl.SetValue(dbdata[0])
self.port_ctrl.SetValue(dbdata[1])
self.login_ctrl.SetValue(dbdata[3])
self.passw_ctrl.SetValue(dbdata[4])
if dbdata[5] == u'sid':
self.sid_radio.SetValue(True)
self.sid_ctrl.SetValue(dbdata[2])
elif dbdata[5] == u'service':
self.service_radio.SetValue(True)
self.service_ctrl.SetValue(dbdata[2])
except (IndexError, TypeError), info:
info = str(info)
info = info.encode('utf8')
if "'NoneType' object is not subscriptable" in info:
wx.MessageBox(u'Создайте новое подключение!')
logging.error(u'filling forms connection error:', info)
self.name_ctrl.SetValue(namecon)
return
availQuarters = getQuartersAvailable(availStmts)
gotQuarters = getCompanysStmtQuarters(db, table_name) if table_exists else []
missingQuarters = getMissingQuarters(availQuarters, gotQuarters)
for quarter in missingQuarters:
quarterSplit = quarter.split('_')
stmt = simfin.getQuarterlyStatement(quarter=quarterSplit[1], fyear=quarterSplit[0], ticker=ticker)
if 'error' in stmt:
return stmt
#print('Report Date:', data['report_date'])
if 'report_date' in stmt:
print(ticker, quarter, 'Report Date:', stmt['report_date']['val'])
createFundamentalTableIfNotExists(db, table_name, stmt)
insertFundamentalStmt(db, table_name, stmt)
#%%
start = time.time()
db = sqliteDB()
db.connect()
simfin = SimfinApi()
#%%
#importFundamentals(db, ticker, start_year=2010)
#importAllFundamentals(start_year=2000, ticker_start='ABMD')
importMissingFundamentalStmts(db, simfin)
#%%
db.disconnect()
total_secs = time.time() - start
print('\nTime taken:', int(total_secs/60), 'mins', int(total_secs%60), 'secs')
#%%
def __init__(self, parent, schema, table, connection):
bSizer3 = wx.BoxSizer( wx.VERTICAL )
self.main = parent
self.panel_regexps = wx.Panel( self.main, wx.ID_ANY, wx.DefaultPosition, (-1,-1), wx.TAB_TRAVERSAL )
#bSizer3.Add(self.panel_regexps, wx.EXPAND, 1)
title_page = (u"Редактор регулярных выражений %s:%s" % (self.main.schema, self.main.table))
self.main.notebook.AddPage( self.panel_regexps, title_page, False, wx.NullBitmap )
bSizer1 = wx.BoxSizer( wx.VERTICAL )
self.label1 = wx.StaticText( self.panel_regexps, wx.ID_ANY, u"Параметры оценки качества данных:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.label1.Wrap( -1 )
bSizer1.Add( self.label1, 0, wx.ALL, 5 )
self.params_regexps_choices = [u'Не несущие информацию значения', u'Не соответствующие формату значения',
u'Значение уровня шума', u'Идентифицируемость', u'Согласованность']
self.regexp_choice_pull = wx.Choice( self.panel_regexps, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize,
self.params_regexps_choices, 0 )
self.regexp_choice_pull.SetSelection( 0 )
bSizer1.Add( self.regexp_choice_pull, 0, wx.ALL|wx.EXPAND, 5 )
self.label2 = wx.StaticText( self.panel_regexps, wx.ID_ANY, u"Регулярные выражения:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.label2.Wrap( -1 )
bSizer1.Add( self.label2, 0, wx.ALL, 5 )
regexps_listboxChoices = []
self.regexps_listbox = wx.ListBox( self.panel_regexps, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, regexps_listboxChoices, wx.LB_HSCROLL )
bSizer1.Add( self.regexps_listbox, 1, wx.ALL|wx.EXPAND, 5 )
self.staticline = wx.StaticLine( self.panel_regexps, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.LI_HORIZONTAL )
bSizer1.Add( self.staticline, 0, wx.EXPAND |wx.ALL, 5 )
self.label3 = wx.StaticText( self.panel_regexps, wx.ID_ANY, u"Поле для редактирования регулярного выражения:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.label3.Wrap( -1 )
bSizer1.Add( self.label3, 0, wx.ALL, 5 )
# wx.TE_MULTILINE ставит ограничение на горячую клавишу Ctrl+A.
self.edit_regexp_txt = wx.TextCtrl( self.panel_regexps, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( -1,150 ), wx.HSCROLL|wx.TE_MULTILINE )
bSizer1.Add( self.edit_regexp_txt, 0, wx.ALL|wx.EXPAND, 5 )
self.add_regexp_btn = wx.Button( self.panel_regexps, wx.ID_ANY, u"Добавить регулярное выражение", wx.DefaultPosition, wx.DefaultSize, 0 )
bSizer1.Add( self.add_regexp_btn, 0, wx.ALL|wx.ALIGN_CENTER_HORIZONTAL, 5 )
self.del_regexp_btn = wx.Button( self.panel_regexps, wx.ID_ANY, u"Удалить регулярное выражение", wx.DefaultPosition, wx.DefaultSize, 0 )
bSizer1.Add( self.del_regexp_btn, 0, wx.ALL|wx.ALIGN_CENTER_HORIZONTAL, 5 )
self.checksql_btn = wx.Button( self.panel_regexps, wx.ID_ANY, u"Выполнить запрос", wx.DefaultPosition, wx.DefaultSize, 0 )
bSizer1.Add( self.checksql_btn, 0, wx.ALL|wx.ALIGN_CENTER_HORIZONTAL, 5 )
self.panel_regexps.SetSizer( bSizer1 )
self.panel_regexps.Layout()
bSizer1.Fit( self.panel_regexps )
bSizer3.Add( self.panel_regexps, wx.EXPAND, 1 )
# Binds Events
self.regexps_listbox.Bind(wx.EVT_LISTBOX_DCLICK, self.OnChRegexp)
self.regexp_choice_pull.Bind(wx.EVT_CHOICE, self.OnChParamRegexp)
self.edit_regexp_txt.Bind(wx.EVT_TEXT, self.OnEditRegexp)
#Events Buttons
self.checksql_btn.Bind(wx.EVT_BUTTON, self.OnTestBtn)
self.add_regexp_btn.Bind(wx.EVT_BUTTON, self.OnAddBtn)
self.del_regexp_btn.Bind(wx.EVT_BUTTON, self.OnDelBtn)
#SQLITE
self.db = sqlite.sqliteDB(self.main.schema, self.main.table)
#Table
self.table = table
self.schema = schema
#Connection
self.connection = connection
def backtestRunner(provider, candle_size, start_date, end_date, init_balance,
stratOpts):
delta = {
'1m': timedelta(minutes=1),
'5m': timedelta(minutes=5),
'15m': timedelta(minutes=15),
'1h': timedelta(hours=1),
'4h': timedelta(hours=4),
'1D': timedelta(days=1)
}[candle_size]
current_datetime = start_date
db = sqliteDB()
db.connect()
# Setup. Get warm up candles:
data = {}
trader = FakeTrader(init_balance)
data['price_target'] = {}
data['price_target']['candles'] = pd.DataFrame(data,
columns=[
'date', 'open', 'high',
'low', 'close',
'volume', 'price_target'
])
trader.trades['price_target'] = []
plotter = Plotter(['price_target'])
#plotter.createLine('price_target', 'price_target', 'green')
pos_size = stratOpts['pos_size']
symbols = db.getKoyfinTickers()
# strategy = Strategy(stratOpts, trader, plotter, symbols)
while current_datetime < end_date:
print(f'{current_datetime.strftime("%Y-%m-%d")}')
for symbol in symbols:
table_name = getTableName(provider, symbol, '1D')
candle = db.getCandle(table_name, current_datetime)
data['price_target']['candles'] = data['price_target'][
'candles'].append(candle, ignore_index=True)
#plotter.addPtToLine('price_target', 'price_target', candle['date'], candle['price_target'])
if candle == None or candle['price_target'] == None:
# No enough data for this day
pass
else:
# if
price_dif_pct = (candle['price_target'] -
candle['close']) / candle['close'] * 100
if price_dif_pct > 30 and not trader.isPosOpen(symbol):
order = {
'symbol': symbol,
'quantity': pos_size / candle['close'],
'mkt_price': candle['close'],
'placed': candle['date']
}
trader.openPosition(order, current_datetime)
print('buy', symbol, 'price:', candle['close'], 'target:',
candle['price_target'], 'price dif:', price_dif_pct)
for order in trader.orders:
if order['status'] == 'active' and order[
'symbol'] == symbol and price_dif_pct < 20:
trader.closePosition(order, candle['close'],
current_datetime)
print('sell', symbol, 'price dif:', price_dif_pct)
current_datetime += delta
reporter = Reporter(['price_target'], plotter, init_balance)
reporter.setCandles('price_target', data['price_target']['candles'])
reporter.setTrades('price_target', trader.trades['price_target'],
trader.balance, trader.num_trades, trader.num_wins,
trader.total_r)
db.disconnect()
# profit = trader.balance - init_balance
# profitPct = profit / init_balance * 100
# winRate = trader.num_wins / trader.num_trades * 100
# print('\nFinal balance: {:.2f} Profit: {:.2f} Profit%: {:.2f}%'.format(trader.balance, profit, profitPct))
# print('Num trades: {} Num wins: {} Win rate: {:.2f}%\n'.format(trader.num_trades, trader.num_wins, winRate))
# TODO: Return a reporter class instance that includes functions such as trades_to_excel, show_chart, chart_to_html, print_performance, etc.
# The strategy needs access to the plotter class in order to add lines and dots
# The dots that correspond to trades should be added automatically
return reporter
def __init__(self, parent, schema, table, connection):
wx.Frame.__init__ ( self, parent, id = wx.ID_ANY, title = u'Выбор параметров для оценки качества данных', pos = wx.DefaultPosition, size = wx.Size( 400,200 ), style = wx.CAPTION|wx.STAY_ON_TOP|wx.SYSTEM_MENU|wx.TAB_TRAVERSAL )
self.main = parent
self.SetSizeHintsSz( wx.DefaultSize, wx.DefaultSize )
bSizer5 = wx.BoxSizer( wx.VERTICAL )
self.m_panel2 = wx.Panel( self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
bSizer6 = wx.BoxSizer( wx.VERTICAL )
self.label1 = wx.StaticText( self.m_panel2, wx.ID_ANY, u"Выберите параметры для оценки:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.label1.Wrap( -1 )
bSizer6.Add( self.label1, 0, wx.ALL, 5 )
self.staticline = wx.StaticLine( self.m_panel2, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.LI_HORIZONTAL )
bSizer6.Add( self.staticline, 0, wx.EXPAND |wx.ALL, 5 )
bSizer7 = wx.BoxSizer( wx.HORIZONTAL )
bSizer7.SetMinSize( wx.Size( 100,200 ) )
self.params_quality_choices = [u'Пустые значения', u'Не несущие информацию значения', u'Не соответствующие формату значения',
u'Значение уровня шума', u'Идентифицируемость', u'Согласованность', u'Унификация',
u'Оперативность', u'Противоречивость', u'Степень классификации',
u'Степень структуризации']
self.params_choice_pull = wx.Choice( self.m_panel2, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize,
self.params_quality_choices, 0 )
bSizer7.Add( self.params_choice_pull, 0, wx.ALL, 5 )
self.use_param_checkbox = wx.CheckBox( self.m_panel2, wx.ID_ANY, u"Использовать параметр", wx.DefaultPosition, wx.DefaultSize, 0 )
bSizer7.Add( self.use_param_checkbox, 0, wx.ALL, 5 )
bSizer6.Add( bSizer7, 1, wx.EXPAND, 5 )
self.label2 = wx.StaticText( self.m_panel2, wx.ID_ANY, u"Весовой коэффициент параметра:", wx.DefaultPosition, wx.DefaultSize, 0 )
self.label2.Wrap( -1 )
bSizer6.Add( self.label2, 0, wx.ALL, 5 )
self.weights_txt = wx.TextCtrl( self.m_panel2, validator = WeightsValidator() )
bSizer6.Add( self.weights_txt, 0, wx.ALL, 5 )
self.OK_btn = wx.Button( self.m_panel2, wx.ID_ANY, u"OK", wx.DefaultPosition, wx.DefaultSize, 0 )
bSizer6.Add( self.OK_btn, 0, wx.ALL|wx.ALIGN_CENTER_HORIZONTAL, 5 )
self.exit_btn = wx.Button( self.m_panel2, wx.ID_ANY, u"Выход", wx.DefaultPosition, wx.DefaultSize, 0 )
bSizer6.Add( self.exit_btn, 1, wx.ALL|wx.ALIGN_CENTER_HORIZONTAL, 5 )
self.m_panel2.SetSizer( bSizer6 )
self.m_panel2.Layout()
bSizer6.Fit( self.m_panel2 )
bSizer5.Add( self.m_panel2, 1, wx.EXPAND |wx.ALL, 5 )
self.Layout()
self.Centre( wx.BOTH )
self.use_param_checkbox.Bind(wx.EVT_CHECKBOX, self.OnCheckUseParam)
self.params_choice_pull.Bind(wx.EVT_CHOICE, self.OnChParamDQ)
self.Bind(wx.EVT_BUTTON, self.OnConfirmBtn, self.OK_btn)
self.Bind(wx.EVT_BUTTON, self.OnExitBtn, self.exit_btn)
self.using_params = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
self.weights_params = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
self.user_number_catalog = None
self.user_number_composite_fileds = None
self.user_number_allfields = None
self.user_days_can_be = None
self.incon_fields = None
#SQLITE
self.db = sqlite.sqliteDB(self.main.schema, self.main.table)
#Table
self.table = table
self.schema = schema
#Connection
self.connection = connection
def __init__( self, main, schema, table, connection ):
wx.Frame.__init__ ( self, parent=None, id = wx.ID_ANY, title = u"Data Quality -- Выбор и отладка регулярных выражений",
pos = wx.DefaultPosition, size = wx.Size( 510,477 ), style = wx.CAPTION|wx.TAB_TRAVERSAL )
self.SetSizeHintsSz( wx.DefaultSize, wx.DefaultSize )
sizer1 = wx.BoxSizer( wx.HORIZONTAL )
self.regexps_notebook = wx.Notebook( self, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, 0 )
self.dq_params_tab = wx.Panel( self.regexps_notebook, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
sizer2 = wx.BoxSizer( wx.VERTICAL )
sizer2.SetMinSize( wx.Size( 2,2 ) )
self.use_param_checkbox = wx.CheckBox( self.dq_params_tab, wx.ID_ANY, u"Использовать параметр", wx.DefaultPosition,
wx.DefaultSize, 0 )
sizer2.Add( self.use_param_checkbox, 0, wx.ALL, 5 )
self.params_quality_choices = [u'Пустые значения', u'Не несущие информацию значения', u'Не соответствующие формату значения',
u'Значение уровня шума', u'Идентифицируемость', u'Согласованность', u'Унификация',
u'Оперативность', u'Противоречивость', u'Достоверность', u'Степень классификации',
u'Степень структуризации']
self.params_choice_pull = wx.Choice( self.dq_params_tab, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize,
self.params_quality_choices, 0 )
self.params_choice_pull.SetSelection( 0 )
sizer2.Add( self.params_choice_pull, 0, wx.ALL, 5 )
self.static_line = wx.StaticLine( self.dq_params_tab, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.LI_HORIZONTAL )
sizer2.Add( self.static_line, 0, wx.EXPAND |wx.ALL, 5 )
self.static_text = wx.StaticText( self.dq_params_tab, wx.ID_ANY, u"Ввод весового коэффициента", wx.DefaultPosition,
wx.DefaultSize, 0 )
self.static_text.Wrap( -1 )
sizer2.Add( self.static_text, 0, wx.ALL, 5 )
self.weights_txt = wx.TextCtrl( self.dq_params_tab, validator = WeightsValidator() )
sizer2.Add( self.weights_txt, 0, wx.ALL, 5 )
self.OK_btn = wx.Button( self.dq_params_tab, wx.ID_ANY, u"OK", wx.DefaultPosition, wx.DefaultSize, 0 )
sizer2.Add( self.OK_btn, 0, wx.ALL, 5 )
self.dq_params_tab.SetSizer( sizer2 )
self.dq_params_tab.Layout()
sizer2.Fit( self.dq_params_tab )
self.regexps_notebook.AddPage( self.dq_params_tab, u"Параметры оценки", False )
self.regexps_tab = wx.Panel( self.regexps_notebook, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
sizer3 = wx.BoxSizer( wx.VERTICAL )
self.static_text9 = wx.StaticText( self.regexps_tab, wx.ID_ANY, u"Список критериев", wx.DefaultPosition, wx.DefaultSize, 0 )
self.static_text9.Wrap( -1 )
sizer3.Add( self.static_text9, 0, wx.ALL, 5 )
self.params_regexps_choices = [u'Не несущие информацию значения', u'Не соответствующие формату значения',
u'Значение уровня шума', u'Идентифицируемость', u'Согласованность', u'Оперативность',
u'Противоречивость', u'Достоверность', u'Степень классификации', u'Степень структуризации']
self.regexp_choice_pull = wx.Choice( self.regexps_tab, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize,
self.params_regexps_choices, 0 )
self.regexp_choice_pull.SetSelection( 0 )
sizer3.Add( self.regexp_choice_pull, 0, wx.ALL|wx.EXPAND, 5 )
regexps_listboxChoices = []
self.regexps_listbox = wx.ListBox( self.regexps_tab, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize,
regexps_listboxChoices, wx.LB_HSCROLL|wx.LB_SINGLE )
sizer3.Add( self.regexps_listbox, 1, wx.ALL|wx.EXPAND, 5 )
self.m_staticText10 = wx.StaticText( self.regexps_tab, wx.ID_ANY, u"Поле для ввода/редактирования регулярного выражения",
wx.DefaultPosition, wx.DefaultSize, 0 )
self.m_staticText10.Wrap( -1 )
sizer3.Add( self.m_staticText10, 0, wx.ALL, 5 )
self.edit_regexp_txt = wx.TextCtrl( self.regexps_tab, wx.ID_ANY, wx.EmptyString, wx.DefaultPosition, wx.Size( 465,50 ), 0 )
sizer3.Add( self.edit_regexp_txt, 0, wx.ALL, 5 )
self.add_regexp_btn = wx.Button( self.regexps_tab, wx.ID_ANY, u"Добавить", wx.DefaultPosition, wx.DefaultSize, 0 )
sizer3.Add( self.add_regexp_btn, 0, wx.ALL|wx.EXPAND, 5 )
self.del_regexp_btn = wx.Button( self.regexps_tab, wx.ID_ANY, u"Удалить", wx.DefaultPosition, wx.DefaultSize, 0 )
sizer3.Add( self.del_regexp_btn, 0, wx.ALL|wx.ALIGN_RIGHT|wx.EXPAND, 5 )
self.checksql_btn = wx.Button( self.regexps_tab, wx.ID_ANY, u"Проверка синтаксиса SQL", wx.DefaultPosition, wx.DefaultSize, 0 )
sizer3.Add( self.checksql_btn, 0, wx.ALL|wx.EXPAND, 5 )
self.exit_btn = wx.Button( self.regexps_tab, wx.ID_ANY, u"Выход", wx.DefaultPosition, wx.DefaultSize, 0 )
sizer3.Add( self.exit_btn, 0, wx.ALL|wx.EXPAND, 5 )
self.regexps_tab.SetSizer( sizer3 )
self.regexps_tab.Layout()
sizer3.Fit( self.regexps_tab )
self.regexps_notebook.AddPage( self.regexps_tab, u"Ввод регулярных выражений", True )
#self.check_sql_tab = wx.Panel( self.regexps_notebook, wx.ID_ANY, wx.DefaultPosition, wx.DefaultSize, wx.TAB_TRAVERSAL )
sizer4 = wx.BoxSizer( wx.VERTICAL )
#Вкладка с маленьким гридом, на при этом багает большой грид. Есть мысля переиначить интерфейс модуля редактирования регулярных выражений.
#=======================================================================
# self.check_grid = wx.grid.Grid( self.check_sql_tab, wx.ID_ANY, wx.DefaultPosition, wx.Size(480,407), wx.HSCROLL|wx.VSCROLL )
#
# # Grid
# self.check_grid.CreateGrid( 5, 5 )
# self.check_grid.EnableEditing( False )
# self.check_grid.EnableGridLines( True )
# self.check_grid.EnableDragGridSize( False )
# self.check_grid.SetMargins( 0, 0 )
#
# # Columns
# self.check_grid.EnableDragColMove( False )
# self.check_grid.EnableDragColSize( False )
# self.check_grid.SetColLabelSize( 20 )
# self.check_grid.SetColLabelAlignment( wx.ALIGN_CENTRE, wx.ALIGN_CENTRE )
#
# # Rows
# self.check_grid.AutoSizeRows( True )
# self.check_grid.EnableDragRowSize( True )
# self.check_grid.SetRowLabelSize( 40 )
# self.check_grid.SetRowLabelAlignment( wx.ALIGN_CENTRE, wx.ALIGN_CENTRE )
#
# # Label Appearance
#
# # Cell Defaults
# self.check_grid.SetDefaultCellAlignment( wx.ALIGN_LEFT, wx.ALIGN_TOP )
# sizer4.Add( self.check_grid, 0, wx.ALL, 5 )
#=======================================================================
#=======================================================================
# self.check_sql_tab.SetSizer( sizer4 )
# self.check_sql_tab.Layout()
# sizer4.Fit( self.check_sql_tab )
# self.regexps_notebook.AddPage( self.check_sql_tab, u"Проверка регулярных выражений", False )
#=======================================================================
sizer1.Add( self.regexps_notebook, 1, wx.EXPAND |wx.ALL, 5 )
self.SetSizer( sizer1 )
self.Layout()
self.Centre( wx.BOTH )
# Binds Events
self.use_param_checkbox.Bind(wx.EVT_CHECKBOX, self.OnCheckUseParam)
self.regexps_listbox.Bind(wx.EVT_LISTBOX_DCLICK, self.OnChRegexp)
self.params_choice_pull.Bind(wx.EVT_CHOICE, self.OnChParamDQ)
self.regexp_choice_pull.Bind(wx.EVT_CHOICE, self.OnChParamRegexp)
self.edit_regexp_txt.Bind(wx.EVT_TEXT, self.OnEditRegexp)
#self.check_sql_tab.Bind( wx.EVT_LEFT_DCLICK, self.OnGridTab)
#Events Buttons
self.Bind(wx.EVT_BUTTON, self.OnTestBtn, self.checksql_btn)
self.Bind(wx.EVT_BUTTON, self.OnAddBtn, self.add_regexp_btn)
self.Bind(wx.EVT_BUTTON, self.OnDelBtn, self.del_regexp_btn)
self.Bind(wx.EVT_BUTTON, self.OnConfirmBtn, self.OK_btn)
self.Bind(wx.EVT_BUTTON, self.OnExitBtn, self.exit_btn)
# Variables
self.using_params = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
self.weights_params = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
self.main = main
#SQLITE
self.db = sqlite.sqliteDB(self.main.schema, self.main.table)
#Table
self.table = table
self.schema = schema
#Connection
self.connection = connection
def updateDayCandleData():
def mergeCandlesAndPriceTarget(candles, priceTargetHist):
targets = {}
last_know_pt = None
if not priceTargetHist:
return candles
for target in priceTargetHist:
targets[target['date']] = target['value']
for candle in candles:
if candle['date'] in targets:
candle['price_target'] = targets[candle['date']]
last_know_pt = targets[candle['date']]
else:
candle['price_target'] = last_know_pt
return candles
tickers = getUsExchangeTickers()
db = sqliteDB()
db.connect()
for ticker in tickers:
table_name = getTableName('koyfin', ticker['symbol'], '1D')
koyfinId = getSymbolId(ticker['symbol'])
num_analysts = getNumAnalysts(koyfinId)
print('Processing', ticker['symbol'], koyfinId, 'num_analysts:',
num_analysts)
if num_analysts != None and num_analysts > 15:
if not db.candleTableExists(table_name):
candles = getAllDayCandlesForTicker(koyfinId)
priceTargetHist = getCompletePriceTargetHistory(koyfinId)
new_candles = mergeCandlesAndPriceTarget(
candles, priceTargetHist
) if priceTargetHist and candles else candles
if new_candles:
db.createCandleTableIfNotExists(table_name)
for candle in new_candles:
price_target = candle.get('price_target')
db.insertCandle(
table_name,
datetime.strptime(candle['date'],
'%Y-%m-%d'), candle['open'],
candle['high'], candle['low'], candle['close'],
candle['volume'], price_target, num_analysts)
else:
next_candle_to_get = db.getLastCandleTime(
table_name) + timedelta(days=1)
yesterday = datetime.today() - timedelta(days=1)
candles = getCandles(
koyfinId, datetime.strftime(next_candle_to_get,
'%Y-%m-%d'),
datetime.strftime(yesterday, '%Y-%m-%d'))
priceTargetHist = getTargetPriceHistory(
koyfinId, next_candle_to_get, yesterday)
new_candles = mergeCandlesAndPriceTarget(
candles, priceTargetHist
) if priceTargetHist and candles else candles
if new_candles:
for candle in new_candles:
price_target = candle[
'price_target'] if 'price_target' in candle else None
db.insertCandle(
table_name,
datetime.strptime(candle['date'],
'%Y-%m-%d'), candle['open'],
candle['high'], candle['low'], candle['close'],
candle['volume'], price_target, num_analysts)
db.disconnect()
def backtestRunner(Strategy, symbols, provider, candle_size, start_date,
end_date, init_balance, stratOpts):
delta = {
'1m': timedelta(minutes=1),
'5m': timedelta(minutes=5),
'15m': timedelta(minutes=15),
'1h': timedelta(hours=1),
'4h': timedelta(hours=4),
'1D': timedelta(days=1)
}[candle_size]
current_datetime = start_date
db = sqliteDB()
db.connect()
def getTable(symbol):
return getTableName(provider, symbol, candle_size)
# Setup. Get warm up candles:
data = {}
trader = FakeTrader(init_balance)
for symbol in symbols:
data[symbol] = {}
data[symbol]['candles'] = db.getLastCandles(
getTable(symbol), start_date, stratOpts['numWarmUpCandles'])
trader.trades[symbol] = []
plotter = Plotter(symbols)
strategy = Strategy(stratOpts, trader, plotter, symbols)
while current_datetime < end_date:
#print(f' {current_date.strftime("%Y-%m-%d")}')
for symbol in symbols:
candle = db.getCandle(getTable(symbol), current_datetime)
if candle == None:
#print('Not a trading day')
pass
else:
#trader.checkPendingOrders(symbol, data[symbol]['candles'].iloc[-1], stratOpts['closeOrderAfter'])
data[symbol]['candles'] = data[symbol]['candles'].append(
candle, ignore_index=True)
#logging.debug(f'appended to candles:\n{candles[symbol]}')
if len(data[symbol]
['candles'].index) > stratOpts['numWarmUpCandles']:
strategy.on_candle(symbol, data[symbol]['candles'], db)
#print('\n', datetime.strftime(current_date, '%Y-%m-%d'), symbol, candles[symbol])
current_datetime += delta
reporter = Reporter(symbols, plotter, init_balance)
for symbol in symbols:
strategy.on_end(symbol, data[symbol]['candles'])
reporter.setCandles(symbol, data[symbol]['candles'])
reporter.setTrades(symbol, trader.trades[symbol], trader.balance,
trader.num_trades, trader.num_wins, trader.total_r)
db.disconnect()
# profit = trader.balance - init_balance
# profitPct = profit / init_balance * 100
# winRate = trader.num_wins / trader.num_trades * 100
# print('\nFinal balance: {:.2f} Profit: {:.2f} Profit%: {:.2f}%'.format(trader.balance, profit, profitPct))
# print('Num trades: {} Num wins: {} Win rate: {:.2f}%\n'.format(trader.num_trades, trader.num_wins, winRate))
# TODO: Return a reporter class instance that includes functions such as trades_to_excel, show_chart, chart_to_html, print_performance, etc.
# The strategy needs access to the plotter class in order to add lines and dots
# The dots that correspond to trades should be added automatically
return reporter
def mathDQ(self, weights, using_params, user_choice_catalog, user_number_allfields, user_number_composite_fileds, user_days_can_be, list_of_fields, schema, table):
self.table = table
self.schema = schema
logging.info(u'starting calculation data quality model')
self.data = []
dt = datetime.now()
timedq = dt.strftime('%Y.%m.%d-%H-%M-%S')
self.data.append(timedq)
sql = sqlite.sqliteDB(self.schema, self.table)
stat = statistic.stats(self.schema, self.table)
orcl = oracle.WorkDB(self.connection)
self.countall = orcl.get_all_count(self.schema, self.table)
self.weights = weights
self.using_params = using_params
if self.weights is None or self.weights == []:
wx.MessageBox(u'Выберите параметры оценки прежде чем запускать оценку качества данных.')
return None
for i in range(len(self.weights)):
self.weights[i] = float(self.weights[i])
self.namecols = orcl.get_cols(self.table)
# Пустые значения
if self.using_params[0] == 1:
# Количество значений подпадающих под критерий
count0 = orcl.get_empty_values(self.schema, self.table)
count0 = count0 * self.weights[0]
emptyvalues = count0 / self.countall * 100
emptyvalues = str(round(emptyvalues, 2))
self.data.append(emptyvalues)
else:
emptyvalues = 100
self.data.append(u'-')
self.extend_stat = []
# Не несущие информации значения
try:
if self.using_params[1] == 1:
count1 = []
param = 'no_information'
regexp = sql.take_regexps(param)
for col in self.namecols:
count1.append(0.0)
for reg in regexp:
if col in reg:
col_index = self.namecols.index(col)
count1[col_index] = orcl.get_regexp_count(self.schema, self.table, reg)
avgnoinf = (sum(count1) / len(count1)) * self.weights[1]
avgnoinf = float(avgnoinf)
avgnoinf = avgnoinf / self.countall * 100
avgnoinf = str(round(avgnoinf, 2))
self.data.append(avgnoinf)
# Расчет расширенной статистики по колонкам
ext_stat = []
info = [u'Не несущие значения']
for i in count1:
if i == 0.0 and type(i) is float:
ext_stat.append('-')
else:
ext_stat.append(str(round((float(i) / self.countall * 100),2)))
for i in ext_stat:
info.append(i)
self.extend_stat.append(info)
logging.info(u'not informable parameter calculation successfully')
else:
avgnoinf = 100
self.data.append(u'-')
self.extend_stat.append(None)
except Exception, info:
logging.error(u'not informable parameter calculation failed: %s' % str(info))
