def update_db_time_range(symbol):
with classes.SQLite(config.DB_SYMBOLS_PATH, 'update_db_time_range:',
None) as cur:
# Fill the DB first, this code thinks it will return something anyway
exec_string = f"SELECT [DateTime], Close From {symbol} ORDER BY [DateTime]"
dates = db_time_ranges[symbol]
date_start = None
if dates[0] is None:
first_row = cur.execute(exec_string).fetchone()
result = first_row[0]
last_price = first_row[1]
date_start = helper.str_to_utc_datetime(result, "UTC",
config.DB_DATE_FORMAT)
else:
date_start = dates[0]
exec_string = f"{exec_string} DESC"
result = cur.execute(exec_string).fetchone()
date_end = helper.str_to_utc_datetime(result[0], "UTC",
config.DB_DATE_FORMAT)
last_price = result[1]
db_time_ranges[symbol] = (date_start, date_end, last_price)
dates = f"symbol:{symbol}, date_start: {date_start}, date_end: {date_end}"
logging.info(dates)
def process_price_data(symbol, access_type):
symbol_last_datetime = db_time_ranges[symbol][1]
if access_type == AccessType.bitfinex:
sorted_items = parsers.parse_bitfinex(symbol, symbol_last_datetime)
elif access_type == AccessType.alphavantage:
sorted_items = parsers.parse_alphavantage(symbol, symbol_last_datetime)
elif access_type == AccessType.investing:
sorted_items = parsers.parse_investing(symbol, symbol_last_datetime)
else:
return
with classes.SQLite(config.DB_SYMBOLS_PATH, 'process_price_data:',
lock) as cur:
for price_item in sorted_items:
utc_date = price_item[0]
if symbol_last_datetime.replace(tzinfo=None) > utc_date.replace(
tzinfo=None):
continue
utc_date_str = utc_date.strftime(config.DB_DATE_FORMAT)
exec_string = f"INSERT INTO {symbol} VALUES ('{utc_date_str}',{price_item[1]},{price_item[2]},{price_item[3]},{price_item[4]}) ON CONFLICT(DateTime) DO UPDATE SET Close=excluded.Close"
cur.execute(exec_string)
def write_db(input_typle):
(orders_list, symbol, channel_id) = input_typle
try:
if orders_list is None:
return
logging.info(
'analyze_channel: writing, symbol: %s, channel_id: %s, BUSY_THREADS: %s',
symbol, channel_id, BUSY_THREADS)
with classes.SQLite(config.DB_STATS_PATH, 'process_channel_symbol:',
lock) as cur:
exec_string = f"DELETE FROM 'Order' WHERE IdChannel = {channel_id} AND Symbol = '{symbol}'"
cur.execute(exec_string)
for order in orders_list:
params = {}
params["IdOrder"] = order["id"]
params["IdChannel"] = channel_id
params["Symbol"] = symbol
params["IsBuy"] = 1 if order["is_buy"] else 0
params["Date"] = order["datetime"]
params["PriceSignal"] = float(order["price_signal"])
params["PriceActual"] = float(order["price_actual"])
params["IsOpen"] = 1 if order["is_open"] else 0
params["StopLoss"] = float(
order["stop_loss"]) if "stop_loss" in order else None
params["TakeProfit"] = float(
order["take_profit"]) if "take_profit" in order else None
params["CloseDate"] = order.get("close_datetime")
params["ClosePrice"] = float(
order["close_price"]) if "close_price" in order else None
params["ManualExit"] = 1 if "manual_exit" in order else 0
params["SlExit"] = 1 if "sl_exit" in order else 0
params["TpExit"] = 1 if "tp_exit" in order else 0
params["ErrorState"] = ";".join(
order["error_state"]) if "error_state" in order else None
columns = ', '.join(params.keys())
placeholders = ':' + ', :'.join(params.keys())
exec_string = "INSERT INTO 'Order' (%s) VALUES (%s)" % (
columns, placeholders)
cur.execute(exec_string, params)
now_str = helper.get_now_utc_iso()
update_string = f"UPDATE Channel SET HistoryAnalyzed = 1, HistoryAnalysisUpdateDate = '{now_str}' WHERE Id={channel_id}"
cur.execute(update_string)
logging.info(
'analyze_channel: writing done, symbol: %s, channel_id: %s, BUSY_THREADS: %s',
symbol, channel_id, BUSY_THREADS)
finally:
atomic_decrement()
def gel_last_signals(symbol, top_n=10):
with classes.SQLite(config.DB_STATS_PATH, 'save_signal, db:', None) as cur:
exec_string = f"SELECT IsBuy FROM 'Signal' WHERE Symbol= '{symbol}' ORDER BY Date DESC LIMIT {top_n}"
results = cur.execute(exec_string).fetchall()
if len(results) == 0:
return None
signals_string = f"{symbol}:"
for result in results:
signals_string += "🟢" if result[0] else "🔴"
return signals_string
return None
def analyze_history():
logging.info('analyze_history')
# gold like one of
min_date = db_poll.db_time_ranges[classes.Symbol.XAUUSD][0]
if min_date is None:
logging.info('analyze_channel: symbol data is not loaded yet')
return
exec_string = "SELECT Id FROM Channel WHERE HistoryLoaded = 1 AND (HistoryAnalyzed <> 1 OR HistoryAnalyzed IS NULL)"
channels_ids = None
with classes.SQLite(config.DB_STATS_PATH, 'analyze_history, db:',
None) as cur:
channels_ids = cur.execute(exec_string).fetchall()
channels_ready = 0
for channel_id in channels_ids:
local_channel_id = channel_id[0]
analyze_channel(local_channel_id)
channels_ready += 1
if WAIT_EVENT_OUTER.is_set():
return
def save_signal(symbol,
id_channel,
id_message,
is_buy,
date,
price,
tp=None,
sl=None):
try:
buy_int = 0
if is_buy:
buy_int = 1
params = {}
params["IdChannel"] = int(id_channel)
params["Symbol"] = symbol
params["IdMessage"] = int(id_message)
params["IsBuy"] = int(buy_int)
params["Date"] = date
params["PriceSignal"] = float(price)
if sl is not None:
params["StopLoss"] = float(sl)
if tp is not None:
params["TakeProfit"] = float(tp)
columns = ', '.join(params.keys())
placeholders = ':' + ', :'.join(params.keys())
with classes.SQLite(config.DB_STATS_PATH, 'save_signal, db:',
lock) as cur:
exec_string = "INSERT INTO 'Signal' (%s) VALUES (%s)" % (
columns, placeholders)
cur.execute(exec_string, params)
return True
except Exception as ex:
logging.error("Cannot save signal. Error %s", ex)
return False
def upsert_channel(id_, access_url, title):
with classes.SQLite(config.DB_STATS_PATH, 'upsert_channel, db:',
lock) as cur:
exec_string = f"SELECT Id, Name, AccessLink, CreateDate, UpdateDate, HistoryLoaded, HistoryUpdateDate, HistoryAnalyzed, HistoryAnalysisUpdateDate FROM Channel WHERE Id = {id_}"
result = cur.execute(exec_string)
now_str = helper.get_now_utc_iso()
select_channel = result.fetchone()
if access_url is None and title is None:
return select_channel
if title is None:
title = select_channel[0]
title_safe = get_db_safe_title(title)
if select_channel is None:
insert_string = f"INSERT INTO Channel VALUES ({id_},'{title_safe}','{access_url}','{now_str}', NULL, NULL, NULL, NULL, NULL) ON CONFLICT(Id) DO UPDATE SET UpdateDate=excluded.UpdateDate, Name = excluded.Name"
cur.execute(insert_string)
return (id_, title_safe, access_url, now_str, None, None, None,
None, None)
(id_, name, link, create_date, update_date, history_loaded,
history_update_date, history_analyzed,
history_analysis_update_date) = select_channel
if access_url is None:
access_url = link
if title_safe != name or access_url != link:
update_string = f"UPDATE Channel SET Name='{title_safe}', AccessLink='{access_url}', UpdateDate='{now_str}' WHERE Id = {id_}"
cur.execute(update_string)
return (id_, title_safe, access_url, create_date, update_date,
history_loaded, history_update_date, history_analyzed,
history_analysis_update_date)
def analyze_channel(channel_id):
logging.info('analyze_channel, channel id: %s', channel_id)
out_path = os.path.join(config.CHANNELS_HISTORY_DIR, f"{channel_id}.json")
messages = None
try:
messages = config.get_json(out_path)
except Exception as ex:
logging.error('analyze_channel: %s, error: %s', ex,
traceback.format_exc())
with classes.SQLite(config.DB_STATS_PATH, 'analyze_channel_error:',
lock) as cur:
update_string = f"UPDATE Channel SET HistoryLoaded = 0 WHERE Id={channel_id}"
cur.execute(update_string)
return
if messages is None or len(messages) < 1:
logging.info('analyze_channel: no data from %s', out_path)
return
ordered_messges = sorted(messages, key=lambda x: x["id"], reverse=False)
min_channel_date = helper.str_to_utc_datetime(ordered_messges[0]["date"])
max_channel_date = helper.str_to_utc_datetime(
ordered_messges[len(ordered_messges) - 1]["date"])
for symbol in signal_parser.symbols_regex_map:
min_date = db_poll.db_time_ranges[symbol][0]
max_date = db_poll.db_time_ranges[symbol][1]
if (min_channel_date > min_date):
min_date = min_channel_date
if (max_channel_date < max_date):
max_date = max_channel_date
min_date_rounded_minutes = min_date - datetime.timedelta(
seconds=min_date.second, microseconds=min_date.microsecond)
max_date_rounded_minutes = max_date - datetime.timedelta(
seconds=max_date.second, microseconds=max_date.microsecond)
while not WAIT_EVENT_OUTER.is_set():
if is_theads_busy():
WAIT_EVENT_INNER.wait(STATS_ANALYZE_LOOP_GAP_SEC)
else:
logging.info(
'analyze_channel: id: %s, symbol: %s, start: %s, end: %s',
channel_id, symbol, min_date_rounded_minutes,
max_date_rounded_minutes)
process_channel_typle = (ordered_messges, symbol,
min_date_rounded_minutes,
max_date_rounded_minutes, channel_id)
atomic_increment()
pool.apply_async(signal_parser.analyze_channel_symbol,
process_channel_typle,
callback=write_db)
break
if WAIT_EVENT_OUTER.is_set():
return
def reset_random_channels():
with classes.SQLite(config.DB_STATS_PATH, 'reset_random_channels:',
lock) as cur:
exec_string = f"update 'Channel' SET HistoryAnalyzed = 0, HistoryLoaded = 0 where Id in (select Id from 'Channel' WHERE HistoryAnalyzed = 1 and HistoryAnalysisUpdateDate < date('now','-{RESET_HISTORY_DEEP_DAYS} day') ORDER BY RANDOM() LIMIT {RESET_HISTORY_BATCH})"
cur.execute(exec_string)
async def set_pinned(client: TelegramClient, forwards, primary_chat):
msg_id = config.PINNED_INFO_MESSAGE_ID
if msg_id is None or forwards is None or len(forwards) == 0:
return
template = config.get_file_text(config.TEMPLATE_PINNED_PATH)
if template is None:
return
symbol = classes.Symbol.XAUUSD
channel_ids = list()
for forward in forwards:
from_chat_id = str(forward['from_chat_id'])
if from_chat_id == config.PINNED_EXCEPT_CHANNEL_ID:
continue
channel_ids.append(from_chat_id)
channels_string = ",".join(channel_ids)
channels_stats_query_xau = f"""select
Name,
AccessLink,
round(avg(diff*10),1) as avg_diff,
round(max(diff*10),1) as avg_max,
round(min(diff*10),1) as avg_min,
round(avg(sl*10),1) as avg_sl,
max(time_h) as time_h_max,
round(avg(time_h),1) as time_h_avg,
count(IdChannel) as amount,
IdChannel,
strftime('%d.%m.%Y',max(close_date)) as last_date,
strftime('%d.%m.%Y',min(close_date)) as first_date
from (select CASE IsBuy WHEN 0 THEN o.PriceActual - o.ClosePrice ELSE o.ClosePrice - o.PriceActual END diff,
o.CloseDate as close_date,
o.IdChannel as IdChannel,
c.Name as Name,
c.AccessLink as AccessLink ,
abs(o.PriceActual - o.StopLoss) as sl,
(select Cast ((JulianDay(o.CloseDate) - JulianDay(o.Date )) * 24 As Integer)) as time_h
from 'Order' o join 'Channel' c on o.IdChannel = c.Id
where ErrorState is NULL and CloseDate is not NULL and Symbol = '{symbol}'
and abs(o.PriceActual - o.PriceSignal)<20)
group by IdChannel having IdChannel in ({channels_string}) order by avg_diff desc"""
template = str(template)
channel_strings = list()
count = 1
with classes.SQLite(config.DB_STATS_PATH, 'set_pinned, db:', None) as cur:
channels_stats = cur.execute(channels_stats_query_xau).fetchall()
for channels_stat in channels_stats:
name = channels_stat[0]
link = channels_stat[1]
avg_diff = channels_stat[2]
avg_max = channels_stat[3]
avg_min = channels_stat[4]
avg_sl = channels_stat[5]
time_h_max = channels_stat[6]
time_h_avg = channels_stat[7]
amount = channels_stat[8]
id_channel = channels_stat[9]
last_date = channels_stat[10]
first_date = channels_stat[11]
channel_string = f"{count}. [{name}]({link}) ({id_channel})\n**•avg score, pips:\t{avg_diff}** (▲{avg_max} ▼{avg_min})\n•total:\t{amount} (from {first_date} to {last_date})\n•avg sl, pps:\t{avg_sl}\n•avg time, hrs:\t{time_h_avg} (▲{time_h_max})"
channel_strings.append(channel_string)
count += 1
channels_string_res = "\n\n".join(channel_strings)
out_string = template.replace("{0}", channels_string_res)
msg_pinned = await client.get_messages(primary_chat,
ids=int(
config.PINNED_INFO_MESSAGE_ID))
if msg_pinned is None:
logging.info('No pinned message found (id is %s)',
config.PINNED_INFO_MESSAGE_ID)
return
try:
await client.edit_message(msg_pinned,
out_string,
link_preview=False,
parse_mode='md')
except Exception as ex:
logging.error('Edit pinned message: %s', ex)
def delete_primary_message_id(id_message, id_channel):
with classes.SQLite(config.DB_STATS_PATH, 'update_primary_message_id, db:',
lock) as cur:
del_string = f"DELETE FROM ChannelMessageLink WHERE IdMessage = {id_message} AND IdChannel = {id_channel}"
cur.execute(del_string)
async def download_history():
logging.info('download_history')
exec_string = "SELECT Id, AccessLink FROM Channel WHERE HistoryLoaded IS NULL OR HistoryLoaded <> 1"
channels = None
with classes.SQLite(config.DB_STATS_PATH, 'download_history, db:',
None) as cur:
channels = cur.execute(exec_string).fetchall()
async with TelegramClient(SESSION, config.api_id,
config.api_hash) as client:
for channel_item in channels:
channel_id = channel_item[0]
channel = await forwarder.get_in_channel(channel_id, client)
channel_link = None
if channel is None:
channel_link = channel_item[1]
channel = await forwarder.join_link(channel_link, client)
if channel is None:
logging.info('Channel id: %s, cannot join or already in',
channel_id)
continue
messages_list = list()
messages = await client.get_messages(channel, None)
for message in messages:
msg_dict = message.to_dict()
msg_props = dict()
msg_props["id"] = msg_dict["id"]
msg_props["date"] = helper.datetime_to_utc_datetime(
msg_dict["date"]).isoformat()
message_item = msg_dict.get("message")
if message_item is not None:
msg_props["text"] = message_item
reply_to_message = msg_dict.get("reply_to")
if reply_to_message is not None:
msg_props["reply_to_msg_id"] = reply_to_message[
"reply_to_msg_id"]
messages_list.append(msg_props)
out_path = os.path.join(config.CHANNELS_HISTORY_DIR,
f"{channel_id}.json")
config.set_json(out_path, messages_list)
now_str = helper.get_now_utc_iso()
update_string = f"UPDATE Channel SET HistoryLoaded = 1, HistoryUpdateDate = '{now_str}' WHERE Id={channel_id}"
with classes.SQLite(config.DB_STATS_PATH,
'download_history, db update:', lock) as cur:
channels = cur.execute(update_string)
if channel_link is not None:
await forwarder.exit_if_needed(channel_link, channel_id,
client)
await client.disconnect()
if WAIT_EVENT_OUTER.is_set():
return
WAIT_EVENT_OUTER.clear()
WAIT_EVENT_OUTER.wait(STATS_COLLECT_LOOP_GAP_SEC)
def analyze_channel_symbol(ordered_messges, symbol, min_date, max_date,
channel_id):
symbol_regex = symbols_regex_map[symbol]
order_book = list()
min_date_str = min_date.strftime(config.DB_DATE_FORMAT)
max_date_str = max_date.strftime(config.DB_DATE_FORMAT)
symbol_data = None
exec_string = f"SELECT [DateTime], High, Low, Close FROM {symbol} WHERE [DateTime] BETWEEN '{min_date_str}' AND '{max_date_str}' ORDER BY [DateTime]"
with classes.SQLite(config.DB_SYMBOLS_PATH, 'analyze_channel_symbol, db:',
None) as cur:
symbol_data = cur.execute(exec_string).fetchall()
if symbol_data is None or len(symbol_data) == 0:
logging.info('analyze_channel_symbol: no data for symbol %s', symbol)
min_date_str_iso = min_date.strftime(config.ISO_DATE_FORMAT)
max_date_str_iso = max_date.strftime(config.ISO_DATE_FORMAT)
filtered_messages = list(
filter(
lambda x: x["date"] >= min_date_str_iso and x["date"] <=
max_date_str_iso, ordered_messges))
symbol_data_len = len(symbol_data)
i = 0
current_date_str = None
next_date_str = None
logging.info('analyze_channel_symbol: setting orders... %s', symbol)
for symbol_value in symbol_data:
if i < symbol_data_len - 1:
next_value = symbol_data[i + 1]
else:
break
i += 1
current_date_str = helper.str_to_utc_datetime(
symbol_value[0], input_format=config.DB_DATE_FORMAT).isoformat()
next_date_str = helper.str_to_utc_datetime(
next_value[0], input_format=config.DB_DATE_FORMAT).isoformat()
orders_open = list(filter(lambda x: x["is_open"] is True, order_book))
found_messages = list(
filter(
lambda x: x["date"] >= current_date_str and x["date"] <
next_date_str, filtered_messages))
has_messages_in_min = len(found_messages) > 0
has_orders_open = len(orders_open) > 0
if not has_messages_in_min and not has_orders_open:
continue
value_high = symbol_value[1]
value_low = symbol_value[2]
value_close = symbol_value[3]
if has_orders_open:
update_orders(next_date_str, value_high, value_low, value_close,
orders_open)
for msg in found_messages:
id_ = msg["id"]
reply_to_message_id = msg.get("reply_to_msg_id")
is_reply = reply_to_message_id is not None
target_orders = None
if is_reply:
reply_sources = list(
filter(lambda x: x["id"] == reply_to_message_id,
orders_open))
target_orders = reply_sources
else:
target_orders = orders_open
string_to_orders(msg, symbol_regex, target_orders, next_date_str,
value_close, order_book)
return (order_book, symbol, channel_id)
