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)