def run_instance(self, category_path, category):
print 'Estimating volume for path', category_path
try:
image_data = ImageData(category_path)
instance = category_path.split('/')[-2] if category_path.endswith(
'/') else category_path.split('/')[-1]
self.stats.add_true_for_instance(category, instance,
image_data.volume['value'])
volume = self.volume_estimator.with_kmeans().estimate(image_data)
self.stats.add_predicted_for_instance(
category, instance, self.volume_estimator.method.desc, volume)
if debug():
print u'Predicted volume for target %s = %3.9f %s\u00B3 with %s.' % (
instance, volume, image_data.volume['units'],
self.volume_estimator.method.desc)
volume = self.volume_estimator.with_meanshift().estimate(
image_data)
self.stats.add_predicted_for_instance(
category, instance, self.volume_estimator.method.desc, volume)
if debug():
print u'Predicted volume for target %s = %3.9f %s\u00B3 with %s.' % (
instance, volume, image_data.volume['units'],
self.volume_estimator.method.desc)
except Exception as e:
print "Exception occurred dring volume estimation, skipping image at", category_path, e
def get_data_day(what, symbol, date_start, date_end, connection):
""" xxx """
ret = 0
cursor = connection.cursor(pymysql.cursors.SSCursor)
if what == 'u':
sql = 'SELECT COUNT(*) FROM price_instruments_data WHERE symbol="'+\
str(symbol) +'" AND change_1d >0 AND date >=' +\
str(date_start) + ' AND date <=' + str(date_end)
if what == 'd':
sql = 'SELECT COUNT(*) FROM price_instruments_data WHERE symbol="'+\
str(symbol) +'" AND change_1d <0 AND date >=' +\
str(date_start) + ' AND date <=' + str(date_end)
if what == 'avgu':
sql = 'SELECT AVG(change_1d) FROM price_instruments_data WHERE symbol="'+\
str(symbol) +'" AND change_1d >0 AND date >=' +\
str(date_start) + ' AND date <=' + str(date_end)
if what == 'avgd':
sql = 'SELECT AVG(change_1d) FROM price_instruments_data WHERE symbol="'+\
str(symbol) +'" AND change_1d <0 AND date >=' +\
str(date_start) + ' AND date <=' + str(date_end)
if what == 's':
sql = 'SELECT sentiment_1d FROM price_instruments_data WHERE symbol="'+\
str(symbol) +'" AND date >=' + str(date_start) +\
' AND date <=' + str(date_end)
debug(sql)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
ret = row[0]
cursor.close()
return ret
def set_brokers():
"""
Import brokers and affiliate link to the database.
Args:
None
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "DELETE FROM brokers"
cursor.execute(sql)
sql = """
INSERT IGNORE INTO brokers(broker_id, burl, affiliate_link) VALUES
('eToro','https://www.etoro.com/markets/','http://partners.etoro.com/A52784_TClick.aspx'),
('googleSiteSmartAlpha','https://sites.google.com/view/','https://sites.google.com/view/about-smartalpha'),
('Tradingview','https://app.smartalphatrade.com/s/','20367')
"""
debug(sql + ": " + os.path.basename(__file__))
cursor.execute(sql)
connection.commit()
cursor.close()
def send_mail(to_email, to_display_name, bcc, subject, textmsg):
"""
Send email
Args:
String: Sender used in the email
String: Display name of the sender used in the email
String: BCC which is email(s) to be send to...
String: email subject
String: email text content
Returns:
String: Related information.
"""
ret = ''
tolist = [to_email] + bcc
smtp_user = ACCESS_OBJ.smtp_username()
smtp_pwd = ACCESS_OBJ.smtp_password()
smtpserver = smtplib.SMTP(ACCESS_OBJ.smtp_server(), ACCESS_OBJ.smtp_port())
smtpserver.ehlo()
smtpserver.starttls()
smtpserver.ehlo() # extra characters to permit edit
smtpserver.login(smtp_user, smtp_pwd)
header = 'To:' + to_email + '\n' + 'From: '+ to_display_name +' <' + to_email + '>\n' +\
'Subject:'+ subject +' \n'
debug(header)
seperator = ', '
debug(seperator.join(bcc))
msg = header + '\n' + textmsg + '\n' + get_email_txt_signature() + '\n'
smtpserver.sendmail(smtp_user, tolist, msg)
smtpserver.quit()
return ret
def set_asset_class():
"""
Import asset classes to database.
Args:
None
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "DELETE FROM asset_class"
cursor.execute(sql)
sql = "INSERT IGNORE INTO asset_class(asset_class_id, asset_class_name) VALUES "+\
"('CR:','Crypto'), "+\
"('EQ:','Stocks'), "+\
"('FX:','Forex'), "+\
"('PRF:','Portfolio'), "+\
"('CO:','Commodities'), "+\
"('BD:','Bonds'), "+\
"('MA:','Multi-asset')"
debug(sql + ": " + os.path.basename(__file__))
cursor.execute(sql)
connection.commit()
cursor.close()
def get_portf_perf_summ(symbol, uid, connection):
"""
Get and calculate strategy portfolio performance summary
Args:
String: Symbol of the strategy portfolio
Int: uid of the strategy portfolio
Returns:
None
"""
pps = InstrumentSummaryData(symbol, uid, connection)
y1_pct = pps.get_pct_1_year_performance()
m6_pct = pps.get_pct_6_month_performance()
m3_pct = pps.get_pct_3_month_performance()
m1_pct = pps.get_pct_1_month_performance()
w1_pct = pps.get_pct_1_week_performance()
date_last_month = datetime.datetime.now() - timedelta(days=30)
date_last_month = date_last_month.strftime('%Y%m%d')
sql = "SELECT price_close FROM chart_data WHERE uid="+ str(uid) +\
" AND date >="+ str(date_last_month) +" ORDER BY date"
stdev_st = get_stdev(sql)
maximum_dd_st = get_mdd(sql)
romad_st = get_romad(sql)
volatility_risk_st = get_volatility_risk(sql, True, symbol)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "UPDATE instruments SET y1="+ str(y1_pct) +", m6="+ str(m6_pct) +", m3="+\
str(m3_pct) +", m1="+ str(m1_pct) +", w1="+ str(w1_pct) +", "+\
" stdev_st="+ str(stdev_st) + ", maximum_dd_st="+ str(maximum_dd_st) +\
", romad_st="+ str(romad_st) + ", volatility_risk_st="+ str(volatility_risk_st) +\
" WHERE symbol='"+ str(symbol) +"' "
debug(sql)
cursor.execute(sql)
connection.commit()
cursor.close()
def get_day_up_dwn_stat(symbol, uid):
"""
Write to csv file count results
Args:
String: Instrument symbol
Integer: Instrument unique ID
Returns:
None
"""
m1_up = get_count_d(symbol, 1, 30)
m1_dn = get_count_d(symbol, -1, 30)
w1_up = get_count_d(symbol, 1, 7)
w1_dn = get_count_d(symbol, -1, 7)
file_this = SETT.get_path_src() + "\\" + str(uid) + "ud.csv"
with open(file_this, 'w', newline='') as csvfile:
fieldnames = [
"symbol", "7_up_days", "7_down_days", "30_up_days", "30_down_days"
]
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
debug(symbol + ": " + os.path.basename(__file__))
writer.writerow({
"symbol": str(symbol),
"7_up_days": str(w1_up),
"7_down_days": str(w1_dn),
"30_up_days": str(m1_up),
"30_down_days": str(m1_dn)
})
def rm_portf_from(table, column, symbol):
"""
Remove strategy portfolio from table as per args
Args:
String: Name of the table
String: Column used for filtering
String: Symbol of the strategy portfolio
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'DELETE FROM ' + str(table) + ' WHERE ' + column + ' = "' + str(
symbol) + '"'
debug(sql)
cursor.execute(sql)
connection.commit()
cursor.close()
connection.close()
gc.collect()
def get_market_currency(m):
"""
Description
Args:
None
Returns:
None
"""
r = ''
try:
connection = pymysql.connect(host=db_srv,
user=db_usr,
password=db_pwd,
db=db_name,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT currency_code FROM markets WHERE market_id = '"+ str(m) +"'"
cr.execute(sql)
rs = cr.fetchall()
for row in rs: r = row[0]
cr.close()
connection.close()
except Exception as e: debug(e)
return r
def set_labels():
"""
Import labels for each available language into the database.
Args:
None
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "DELETE FROM labels"
cursor.execute(sql)
connection.commit()
######## English ########
lang_en = "en"
portf_description_en = "This {market_asset_class} portfolio is designed by {nickname}."
sql = "INSERT IGNORE INTO labels(lang, portf_description) VALUES "+\
"('"+lang_en+"', '"+ portf_description_en +"') "
debug(sql)
cursor.execute(sql)
connection.commit()
cursor.close()
connection.close()
def get_stdev(sql):
"""
sql with just one numerical value to compute standard deviation
Compute standard deviation from a database table column.
Args:
String: SQL query with only 1 numerical column
Returns:
Double: Standard deviation
"""
ret = 0
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
cursor.execute(sql)
std_is = list(cursor.fetchall())
ret = np.std(std_is)
debug('stdev=' + str(ret))
cursor.close()
connection.close()
if math.isnan(ret):
ret = 0
return ret
def set_lang():
"""
Import language definition into the database
Args:
None
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "DELETE FROM languages"
cursor.execute(sql)
sql = "INSERT IGNORE INTO languages(id, language) VALUES "+\
"('en','English'), "+\
"('fr','Français'), "+\
"('th','ไทย')"
debug(sql + ": " + os.path.basename(__file__))
cursor.execute(sql)
connection.commit()
cursor.close()
def set_market():
"""
Import market definition into the database
Args:
None
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "DELETE FROM markets"
cursor.execute(sql)
sql = "INSERT IGNORE INTO markets(market_id, market_label, currency_code)"+\
" VALUES "+\
"('GO>','Global','pts'), "+\
"('US>','U.S.','USD')"
debug(sql + ": " + os.path.basename(__file__))
cursor.execute(sql)
connection.commit()
cursor.close()
def correct_stock_split_price(symbol, to_this_date_included, split_factor):
"""
Correction of the historical prices according to stock split.
Args:
String: Provide symbol of the instrument to update
Int: Change historical price up to this date included. (yyyymmdd)
Double: Split multiplier.
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'SELECT date, price_close, target_price, ma200, ma10, ma20, ma30, '+\
'ma40, ma50 FROM price_instruments_data WHERE symbol = "'+\
symbol +'" AND date <= '+ str(to_this_date_included) +' '
cursor.execute(sql)
res = cursor.fetchall()
sql_update = ''
for row in res:
this_date = row[0]
price_close = row[1]
target_price = row[2]
ma200 = row[3]
ma10 = row[4]
ma20 = row[5]
ma30 = row[6]
ma40 = row[7]
ma50 = row[8]
new_price_close = price_close * split_factor
new_target_price = target_price * split_factor
new_ma200 = ma200 * split_factor
new_ma10 = ma10 * split_factor
new_ma20 = ma20 * split_factor
new_ma30 = ma30 * split_factor
new_ma40 = ma40 * split_factor
new_ma50 = ma50 * split_factor
sql_update = 'UPDATE price_instruments_data SET price_close = ' +\
str(new_price_close) + ', target_price = ' + str(new_target_price) +\
', ma200 = ' + str(new_ma200) + ', ma10 = ' + str(new_ma10) + ', ma20 = ' +\
str(new_ma20) + ', ma30 = ' + str(new_ma30) + ', ma40 = ' + str(new_ma40) +\
', ma50 = ' + str(new_ma50) + ' ' +\
'WHERE symbol = "'+ symbol +'" AND date = ' + str(this_date.strftime('%Y%m%d'))
debug(sql_update)
cursor.execute(sql_update)
connection.commit()
get_update_instr_data(1, True, symbol)
cursor.close()
connection.close()
debug('Done!')
def insert_db_price_data():
"""
Collect and import instruments price data from Oanda into the database
Args:
None
Returns:
None
"""
log_this('1. oanda_insert_db_price_data', 0)
csvdir = SETT.get_path_r_oanda_src()
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT symbol, uid FROM symbol_list WHERE oanda<>'' "
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
symbol = row[0]
uid = row[1]
file_str = csvdir + str(uid) + '.csv'
filepath = Path(file_str)
if filepath.exists():
with open(file_str) as csvfile:
csv_file = csv.reader(csvfile, delimiter=',')
i = 0
inserted_values = ''
for row in csv_file:
time.sleep(0.2)
price_date = row[0]
price_date = price_date.replace('.', '-')
price_date = price_date.replace('X', '')
price_date = price_date.replace('-', '')
price_date = '%.8s' % price_date
price_close = row[1]
if price_close != "NA" and i > 0:
if i == 1:
sep = ''
else:
sep = ','
inserted_values = inserted_values + sep +\
"('"+symbol+"',"+price_date+","+price_close+")"
debug(symbol + ": " + os.path.basename(__file__) +
" - " + inserted_values)
i += 1
cr_q_ins = connection.cursor(pymysql.cursors.SSCursor)
sql_q_ins = "INSERT IGNORE INTO price_instruments_data "+\
"(symbol, date, price_close) VALUES " + inserted_values
cr_q_ins.execute(sql_q_ins)
connection.commit()
cr_q_ins.close()
cursor.close()
connection.close()
log_this('1. oanda_insert_db_price_data', 1)
def gen_users(number_of_users):
"""
Generate X number of template users for the create of example
of strategy portfolios.
Args:
Int: number of users to create.
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
date_today = datetime.datetime.now()
date_today = date_today.strftime('%Y%m%d')
for i in range(number_of_users):
if i == 0:
default_user = '******'
uid = set_user_uid()
name = default_user
nickname = name
username = name + '@smartalphatrade.com'
password = set_user_uid()
avatar_id = set_user_avatar_id()
created_on = str(date_today)
default_profile = ''
lang = 'en'
is_bot = 1
else:
uid = set_user_uid()
name = set_nickname()
nickname = name
username = name + '@smartalphatrade.com'
password = set_user_uid()
avatar_id = set_user_avatar_id()
created_on = str(date_today)
default_profile = set_default_profile()
lang = 'en'
is_bot = 1
sql = "INSERT IGNORE INTO users"+\
"(uid, name, nickname, username, password, avatar_id, created_on, "+\
"default_profile, lang, is_bot) VALUES "+\
"('"+ str(uid) +"','"+ str(name) +"','"+ str(nickname) +\
"','"+ str(username) +"','"+ str(password) +"',"+ str(avatar_id) +\
",'"+ str(created_on) +"','"+ str(default_profile) +"','"+ str(lang) +\
"',"+str(is_bot)+")"
debug(sql)
cursor.execute(sql)
connection.commit()
cursor.close()
connection.close()
def get_model_price_action_10d(uid, date_str, connection):
"""
Get model price prediction
Args:
Integer: Instrument unique id
Returns:
Double: price prediction
"""
################################################
# (1) Logic according to model
# Logic as per specific to the model
################################################
ret = 0
column_data_name = 'price_action_10d'
stdev_st = 0
symbol = ''
price_close = 0
model_data = 0
model_tp = 0
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT instruments.stdev_st, instruments.symbol FROM instruments "+\
"JOIN symbol_list ON symbol_list.symbol = instruments.symbol "+\
"WHERE symbol_list.uid = " + str(uid)
cursor.execute(sql)
res = cursor.fetchall()
symbol = ''
for row in res:
stdev_st = row[0]
symbol = row[1]
sql = "SELECT price_close, "+ column_data_name +\
" FROM price_instruments_data WHERE symbol = '"+\
str(symbol) +"' AND date = " + str(date_str)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
price_close = row[0]
model_data = row[1]
# Model Logic
#-----------------------------------------------------------------------
if model_data < 1:
model_tp = price_close - stdev_st
if model_data >= 1:
model_tp = price_close + stdev_st
#-----------------------------------------------------------------------
ret = model_tp
cursor.close()
debug(str(symbol) + ' ::: '+ str(ret) +' = ' +\
str(date_str) + ' ::: ' + str(price_close) +\
' ::: stdev=' + str(stdev_st))
return ret
def bundle_email(num_of_email_in_group, num_of_second_interval,
to_email, to_display_name, subject, textmsg):
"""
Create the mailing list
Args:
Integer: Number of email max to group in a single mail to avoid
limitation from the internet service provider
Integer: Number of seconds interval between mail send to avoid
limitation from the internet service provider
String: Email address used as a sender
String: Display name used for the sender
String: Subject of the email
String: Text of the email
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'SELECT DISTINCT username FROM users JOIN instruments ON '+\
'instruments.owner = users.id '+\
'WHERE users.is_bot=0 AND users.deactivated=0 AND '+\
'(email_subscription="ALL" OR email_subscription="DIR") '
cursor.execute(sql)
res = cursor.fetchall()
i = 1
bcc = []
for row in res:
email = row[0]
if i <= num_of_email_in_group:
bcc.append(email)
i += 1
else:
send_mail(to_email, to_display_name, bcc, subject, textmsg)
debug('waiting for '+ str(num_of_second_interval) + ' seconds before the next batch...')
time.sleep(num_of_second_interval)
bcc.clear()
bcc.append(email)
i = 1
send_mail(to_email, to_display_name, bcc, subject, textmsg)
cursor.close()
connection.close()
def analyze_sentiment_of_this(text):
"""
Provide a sentiment score from a given text.
Args:
String: text to evaluate sentiment
Returns:
Double: Sentiment score
"""
ret = 0
analyser = SentimentIntensityAnalyzer()
score = analyser.polarity_scores(text)
ret = (score.get('compound'))
debug(str(ret))
return ret
def get_mdd(sql):
"""
Get maximum drawdown from a list of price
Args:
String: SQL query with one column containing price
Returns:
Double: Return the maximum drawdown in percentage
"""
ret = 0
#sql with just one numerical value to compute maximum drawdown
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
cursor.execute(sql)
res = cursor.fetchall()
top = 0
breset = math.pow(10, 100)
bottom = breset
pct_dd = 0
cur_dd = 0
for row in res:
val = row[0]
if val > top:
top = val
bottom = breset
if val < bottom:
bottom = val
if bottom < top:
cur_dd = abs(get_pct_change(bottom, top))
else:
cur_dd = 0
if cur_dd > pct_dd:
pct_dd = cur_dd
cursor.close()
connection.close()
ret = pct_dd
if math.isnan(ret):
ret = 0
debug('mdd=' + str(ret))
return ret
def get_data():
api = TodoistAPI(get_api_token())
api.sync()
tasks = api.items.all()
tasks_with_labels = [t for t in tasks if len(t['labels']) != 0]
tasks_without_labels = [t for t in tasks if len(t['labels']) == 0]
test_data_size = math.floor(len(tasks_with_labels) * 0.8)
test_data = tasks_with_labels[test_data_size:]
training_data = tasks_with_labels[:test_data_size]
save(test_data, 'test_data.p')
save(training_data, 'training_data.p')
save(tasks_without_labels, 'unlabeled_data.p')
if debug():
print('Num Tasks with no labels %s' % len(tasks_without_labels))
print('Num Tasks with labels %s' % len(tasks_with_labels))
print('Size of Training Data: %s' % len(training_data))
print('Size of Test Data: %s' % len(test_data))
return {
'test_data': test_data,
'training_data': training_data,
'unlabeled_data': tasks_without_labels
}
def get_conviction_coef(c):
"""
Description
Args:
None
Returns:
None
"""
r = 1.01
try:
if c == 'weak': r = random.randint(3,8)
if c == 'neutral': r = random.randint(8,20)
if c == 'strong': r = random.randint(21,80)
r = (r * 0.01) + 1
except Exception as e: debug(e)
return r
def forward(
self,
*params: Any,
batch_shape: Optional[torch.Size] = None,
shape: Optional[torch.Size] = None,
noise: Optional[Tensor] = None,
) -> DiagLazyTensor:
if noise is not None:
return DiagLazyTensor(noise)
training = self.noise_model.training # keep track of mode
self.noise_model.eval(
) # we want the posterior prediction of the noise model
with settings.detach_test_caches(False), settings.debug(False):
if len(params) == 1 and not torch.is_tensor(params[0]):
output = self.noise_model(*params[0])
else:
output = self.noise_model(*params)
self.noise_model.train(training)
if not isinstance(output, MultivariateNormal):
raise NotImplementedError(
"Currently only noise models that return a MultivariateNormal are supported"
)
# note: this also works with MultitaskMultivariateNormal, where this
# will return a batched DiagLazyTensors of size n x num_tasks x num_tasks
noise_diag = output.mean if self._noise_indices is None else output.mean[
..., self._noise_indices]
return DiagLazyTensor(self._noise_constraint.transform(noise_diag))
def __init__(self, uid, connection):
target_price = -9
date_today = datetime.datetime.now()
date_today = date_today.strftime('%Y%m%d')
date_yesterday = datetime.datetime.now() - timedelta(days=1)
date_yesterday = date_yesterday.strftime('%Y%m%d')
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT price_instruments_data.target_price FROM trades "+\
"JOIN symbol_list ON trades.symbol = symbol_list.symbol "+\
"JOIN price_instruments_data ON "+\
"(trades.symbol = price_instruments_data.symbol AND "+\
"price_instruments_data.date = "+ str(date_yesterday) +") "+\
"WHERE symbol_list.uid = "+ str(uid) +" AND trades.entry_date = " + str(date_today)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
target_price = row[0]
cursor.close()
forc_src = SETT.get_path_src()
file_str = forc_src+str(uid)+'f.csv'
filepath = Path(file_str)
if filepath.exists():
with open(file_str) as csvfile:
csv_file = csv.reader(csvfile, delimiter=',')
i = 1
for row in csv_file:
if i == 2:
self.ent_1_b = row[2] #lower 80 first row row[2]
self.sl_1_b = row[4] #lower 95 first row row[4]
self.tp_1_b = row[5] #upper 95 first row row[5]
self.ent_1_s = row[3] #upper 80 first row row[3]
self.sl_1_s = row[5] #upper 95 first row row[5]
self.tp_1_s = row[4] #lower 95 first row row[4]
if i == 8:
self.ent_2_b = row[2] #lower 80 last row row[2]
self.sl_2_b = row[4] #lower 95 last row row [4]
self.tp_2_b = row[5] #upper 95 last row row[5]
self.ent_2_s = row[3] #upper 80 last row row[3]
self.sl_2_s = row[5] #upper 95 last row row[5]
self.tp_2_s = row[4] #lower 95 last row row[4]
self.frc_pt = target_price
i += 1
debug(str(uid) +": "+ os.path.basename(__file__))
def get_romad(sql):
"""
Get return over max drawdown
Args:
String: SQL query with one column containing price.
Returns:
Double: Return RoMaD
"""
ret = 0
#sql with one column as numerical value to compute return on maximum drawdown
#ordered by date ASC
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
cursor.execute(sql)
res = cursor.fetchall()
i = 0
first = 0
last = 0
for row in res:
if i == 0:
first = row[0]
last = row[0]
i += 1
cursor.close()
connection.close()
debug('f=' + str(first) + ' l=' + str(last))
percent_return = get_pct_change(first, last)
max_drawdown = get_mdd(sql)
if max_drawdown != 0 and max_drawdown is not None:
ret = percent_return / max_drawdown
if math.isnan(ret):
ret = 0
debug('romad=' + str(ret))
return ret
def get_pct_from_date(date_from, sql_select, last_price):
"""
Get percentage performance from specified date to current date.
Args:
String: date in string format YYYYMMDD
String: SQL query to select the appropriate column
Double: Last price
Returns:
Double: Percentage performance
"""
pct = 0
initial_price = 0
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = sql_select + "AND date <= '" + str(
date_from) + "' ORDER BY date DESC LIMIT 1"
debug(sql)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
initial_price = row[0]
cursor.close()
connection.close()
debug('pp: ' + str(initial_price) + ' date=' + str(date_from))
if initial_price != 0:
pct = ((last_price - initial_price) / initial_price)
debug(str(pct) + ' = '+ '('+ str(last_price) +' - '+\
str(initial_price) +') / '+ str(initial_price))
return pct
def __init__(self, symbol, uid, connection):
""" Select and initialize instrument data according to args """
self.symbol_selection = symbol
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT symbol from symbol_list WHERE uid=" + str(uid)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
symbol_is_portf = row[0]
if symbol_is_portf.find(get_portf_suffix()) > -1:
self.sql_select = "SELECT price_close, date FROM chart_data "+\
"WHERE symbol='"+ self.symbol_selection +"' "
else:
self.sql_select = "SELECT price_close, date "+\
"FROM price_instruments_data WHERE symbol='"+ self.symbol_selection + "' "
self.sql_select_signal = "SELECT signal_price, date FROM chart_data "+\
"WHERE symbol='"+ self.symbol_selection +"' AND forecast = 0 "
sql = self.sql_select_signal + " ORDER BY Date DESC LIMIT 1"
debug(sql)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
self.lp_signal = row[0]
sql = self.sql_select + " ORDER BY Date DESC LIMIT 1"
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
self.last_price = row[0]
self.last_date = row[1]
cursor.close()
self.uid = uid
self.d_1_year_perf = self.last_date - (timedelta(days=365))
self.d_6_month_perf = self.last_date - (timedelta(days=180))
self.d_3_month_perf = self.last_date - (timedelta(days=90))
self.d_1_month_perf = self.last_date - (timedelta(days=30))
self.d_1_week_perf = self.last_date - (timedelta(days=7))
self.d_1_day_perf = get_prev_session_date(self.symbol_selection)
self.d_1_week_forcast = 0
def get_portf_pnl(portf_symbol, date_last_year_str, connection):
"""
Get strategy portfolio profit and loss according to args.
Args:
String: Symbol of the strategy portfolio
String: date from last year in string format
Returns:
Double: Strategy portfolio profit and loss
"""
ret = 0
portf_pnl = 0
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT price_instruments_data.pnl, portfolios.quantity, "+\
"instruments.pip, "+\
"price_instruments_data.pnl_long, price_instruments_data.pnl_short, "+\
"portfolios.strategy_order_type " +\
"FROM portfolios JOIN price_instruments_data ON "+\
"portfolios.symbol = price_instruments_data.symbol "+\
"JOIN instruments ON portfolios.symbol = instruments.symbol "+\
"WHERE portfolios.portf_symbol = '"+ portf_symbol +"' AND date="+\
date_last_year_str +" ORDER BY portfolios.portf_symbol"
debug(sql)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
pnl_c = row[0]
quantity_c = row[1]
pip_c = row[2]
pnl_long_c = row[3]
pnl_short_c = row[4]
strategy_order_type_c = row[5]
if strategy_order_type_c == 'long/short':
portf_pnl = portf_pnl + (pnl_c * quantity_c * pip_c)
if strategy_order_type_c == 'long' and pnl_long_c != 999:
portf_pnl = portf_pnl + (pnl_long_c * quantity_c * pip_c)
if strategy_order_type_c == 'short' and pnl_short_c != 999:
portf_pnl = portf_pnl + (pnl_short_c * quantity_c * pip_c)
ret = portf_pnl
cursor.close()
return ret
def check_price_inconsist_price_move(symbol, connection):
"""
Check if a particular symbol experienced an unexpected price movement
greater than 40% due to error or possible stock split.
In that case add symbol to log.
Args:
String: Instrument symbol
Returns:
None
"""
k = 7
module = '{symbol} = Price inconsistent or stock split'
status = 1
date_range = datetime.datetime.now() - timedelta(days=k)
date_range = date_range.strftime("%Y%m%d")
cr_c = connection.cursor(pymysql.cursors.SSCursor)
sql_c = "SELECT AVG(price_close) FROM price_instruments_data WHERE symbol = '"+\
symbol + "' AND date >=" + date_range
cr_c.execute(sql_c)
res_c = cr_c.fetchall()
average_price = 0
for row in res_c:
average_price = row[0]
sql_c = "SELECT price_close FROM price_instruments_data WHERE symbol = '"+\
symbol +"' ORDER BY date DESC LIMIT 1"
cr_c.execute(sql_c)
res_c = cr_c.fetchall()
last_price = 0
for row in res_c:
last_price = row[0]
cr_c.close()
debug(str(average_price) + " ::: " + str(last_price))
if average_price is not None:
if abs(get_pct_change(average_price, last_price)) >= 0.4:
log_this(module.replace('{symbol}', str(symbol)), status)
else:
log_this(module.replace('{symbol}', str(symbol)), status)
def add_feed_type(feed_id, feed_type):
"""
Import brokers and affiliate link to the database.
Args:
Integer: id for the feed.
String: short name or description for the type of feed.
Returns:
None
"""
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "INSERT IGNORE INTO feed_type(id, feed_type) VALUES ('" + str(
feed_id) + "','" + feed_type + "')"
cursor.execute(sql)
connection.commit()
debug(sql + ": " + os.path.basename(__file__))
cursor.close()
connection.close()