diff --git "a/pages/2_\360\237\232\200_Strategy_Performance.py" "b/pages/2_\360\237\232\200_Strategy_Performance.py" index 2d505ebc..72a09e5a 100644 --- "a/pages/2_\360\237\232\200_Strategy_Performance.py" +++ "b/pages/2_\360\237\232\200_Strategy_Performance.py" @@ -16,8 +16,10 @@ st.title("🚀 Strategy Performance") intervals = { - # "1m": 60, + "1m": 60, "3m": 60 * 3, + "5m": 60 * 5, + "15m": 60 * 15, "30m": 60 * 30, "1h": 60 * 60, "6h": 60 * 60 * 6, @@ -30,17 +32,6 @@ def get_database(db_name: str): return db_manager -@st.cache_data(ttl=60) -def get_ohlc(trading_pair: str, exchange: str, interval: str, start_timestamp: int, end_timestamp: int): - # TODO: Remove hardcoded exchange by using the new data collected by the bot. - connector = getattr(ccxt, "binance")() - limit = max(int((end_timestamp - start_timestamp) / intervals[interval]), 10) - bars = connector.fetch_ohlcv(trading_pair.replace("-", ""), timeframe=interval, since=start_timestamp * 1000, limit=limit) - df = pd.DataFrame(bars, columns=["timestamp", "open", "high", "low", "close", "volume"]) - df["datetime"] = pd.to_datetime(df["timestamp"], unit="ms") - return df - - with st.container(): col1, col2 = st.columns(2) with col1: @@ -68,22 +59,16 @@ def get_ohlc(trading_pair: str, exchange: str, interval: str, start_timestamp: i with col2: selected_trading_pair = st.selectbox("Select a trading pair:", [] if selected_config_file is None else exchanges_trading_pairs[selected_exchange]) with col3: - interval = st.selectbox("Candles Interval:", intervals.keys(), index=0) + interval = st.selectbox("Candles Interval:", intervals.keys(), index=2) if selected_exchange and selected_trading_pair: - single_market_strategy_data = strategy_data.get_single_market_strategy_data(selected_exchange, selected_trading_pair) + single_market_strategy_data = strategy_data.get_single_market_strategy_data(selected_exchange, + selected_trading_pair) date_array = pd.date_range(start=strategy_data.start_time, end=strategy_data.end_time, periods=60) - ohlc_extra_time = 60 - with st.spinner("Loading candles..."): - candles_df = get_ohlc(single_market_strategy_data.trading_pair, single_market_strategy_data.exchange, interval, - int(strategy_data.start_time.timestamp() - ohlc_extra_time), - int(strategy_data.end_time.timestamp() + ohlc_extra_time)) start_time, end_time = st.select_slider("Select a time range to analyze", options=date_array.tolist(), value=(date_array[0], date_array[-1])) - candles_df_filtered = candles_df[(candles_df["timestamp"] >= int(start_time.timestamp() * 1000)) & ( - candles_df["timestamp"] <= int(end_time.timestamp() * 1000))] - strategy_data_filtered = single_market_strategy_data.get_filtered_strategy_data(start_time, end_time) + strategy_data_filtered = single_market_strategy_data.get_filtered_strategy_data(start_time, end_time) row = st.container() col11, col12, col13 = st.columns([1, 2, 3]) with row: @@ -120,14 +105,18 @@ def get_ohlc(trading_pair: str, exchange: str, interval: str, start_timestamp: i st.metric(label='End Price', value=round(strategy_data_filtered.end_price, 4)) st.metric(label='Average Buy Price', value=round(strategy_data_filtered.average_buy_price, 4)) st.metric(label='Average Sell Price', value=round(strategy_data_filtered.average_sell_price, 4)) - - cg = CandlesGraph(candles_df_filtered, show_volume=True, extra_rows=2) - cg.add_buy_trades(strategy_data_filtered.buys) - cg.add_sell_trades(strategy_data_filtered.sells) - cg.add_base_inventory_change(strategy_data_filtered) - cg.add_pnl(strategy_data_filtered) - fig = cg.figure() - st.plotly_chart(fig, use_container_width=True) + if strategy_data_filtered.market_data is not None: + candles_df = strategy_data_filtered.get_market_data_resampled(interval=f"{intervals[interval]}S") + cg = CandlesGraph(candles_df, show_volume=False, extra_rows=2) + cg.add_buy_trades(strategy_data_filtered.buys) + cg.add_sell_trades(strategy_data_filtered.sells) + cg.add_pnl(strategy_data_filtered, row=2) + cg.add_base_inventory_change(strategy_data_filtered, row=3) + fig = cg.figure() + st.plotly_chart(fig, use_container_width=True) + else: + st.warning("Market data is not available so the candles graph is not going to be rendered. " + "Make sure that you are using the latest version of Hummingbot and market data recorder activated.") st.subheader("💵Trades") st.write(strategy_data_filtered.trade_fill) diff --git a/utils/data_manipulation.py b/utils/data_manipulation.py index 3171cc2e..e7c9dd18 100644 --- a/utils/data_manipulation.py +++ b/utils/data_manipulation.py @@ -8,17 +8,24 @@ class StrategyData: orders: pd.DataFrame order_status: pd.DataFrame trade_fill: pd.DataFrame + market_data: pd.DataFrame = None def get_single_market_strategy_data(self, exchange: str, trading_pair: str): orders = self.orders[(self.orders["market"] == exchange) & (self.orders["symbol"] == trading_pair)].copy() trade_fill = self.trade_fill[self.trade_fill["order_id"].isin(orders["id"])].copy() order_status = self.order_status[self.order_status["order_id"].isin(orders["id"])].copy() + if self.market_data is not None: + market_data = self.market_data[(self.market_data["exchange"] == exchange) & + (self.market_data["trading_pair"] == trading_pair)].copy() + else: + market_data = None return SingleMarketStrategyData( exchange=exchange, trading_pair=trading_pair, orders=orders, order_status=order_status, trade_fill=trade_fill, + market_data=market_data, ) @property @@ -69,19 +76,36 @@ class SingleMarketStrategyData: orders: pd.DataFrame order_status: pd.DataFrame trade_fill: pd.DataFrame + market_data: pd.DataFrame = None def get_filtered_strategy_data(self, start_date: datetime.datetime, end_date: datetime.datetime): - orders = self.orders[(self.orders["creation_timestamp"] >= start_date) & (self.orders["creation_timestamp"] <= end_date)].copy() + orders = self.orders[ + (self.orders["creation_timestamp"] >= start_date) & (self.orders["creation_timestamp"] <= end_date)].copy() trade_fill = self.trade_fill[self.trade_fill["order_id"].isin(orders["id"])].copy() order_status = self.order_status[self.order_status["order_id"].isin(orders["id"])].copy() + if self.market_data is not None: + market_data = self.market_data[ + (self.market_data.index >= start_date) & (self.market_data.index <= end_date)].copy() + else: + market_data = None return SingleMarketStrategyData( exchange=self.exchange, trading_pair=self.trading_pair, orders=orders, order_status=order_status, trade_fill=trade_fill, + market_data=market_data, ) + def get_market_data_resampled(self, interval): + data_resampled = self.market_data.resample(interval).agg({ + "mid_price": "ohlc", + "best_bid": "last", + "best_ask": "last", + }) + data_resampled.columns = data_resampled.columns.droplevel(0) + return data_resampled + @property def base_asset(self): return self.trading_pair.split("-")[0] diff --git a/utils/database_manager.py b/utils/database_manager.py index d452cab5..2a05cd79 100644 --- a/utils/database_manager.py +++ b/utils/database_manager.py @@ -1,4 +1,5 @@ import os +import streamlit as st import pandas as pd from sqlalchemy import create_engine @@ -73,6 +74,18 @@ def _get_trade_fills_query(config_file_path=None, start_date=None, end_date=None query += f" WHERE {' AND '.join(conditions)}" return query + @staticmethod + def _get_market_data_query(start_date=None, end_date=None): + query = "SELECT * FROM MarketData" + conditions = [] + if start_date: + conditions.append(f"timestamp >= '{start_date * 1e6}'") + if end_date: + conditions.append(f"timestamp <= '{end_date * 1e6}'") + if conditions: + query += f" WHERE {' AND '.join(conditions)}" + return query + def get_orders(self, config_file_path=None, start_date=None, end_date=None): with self.session_maker() as session: query = self._get_orders_query(config_file_path, start_date, end_date) @@ -80,6 +93,8 @@ def get_orders(self, config_file_path=None, start_date=None, end_date=None): orders["market"] = orders["market"].apply(lambda x: x.lower().replace("_papertrade", "")) orders["amount"] = orders["amount"] / 1e6 orders["price"] = orders["price"] / 1e6 + orders['creation_timestamp'] = pd.to_datetime(orders['creation_timestamp'], unit="ms") + orders['last_update_timestamp'] = pd.to_datetime(orders['last_update_timestamp'], unit="ms") return orders def get_trade_fills(self, config_file_path=None, start_date=None, end_date=None): @@ -98,6 +113,7 @@ def get_trade_fills(self, config_file_path=None, start_date=None, end_date=None) trade_fills.loc[:, "inventory_cost"] = trade_fills["cum_net_amount"] * trade_fills["price"] trade_fills.loc[:, "realized_trade_pnl"] = trade_fills["unrealized_trade_pnl"] + trade_fills["inventory_cost"] trade_fills.loc[:, "net_realized_pnl"] = trade_fills["realized_trade_pnl"] - trade_fills["cum_fees_in_quote"] + trade_fills["timestamp"] = pd.to_datetime(trade_fills["timestamp"], unit="ms") trade_fills["market"] = trade_fills["market"].apply(lambda x: x.lower().replace("_papertrade", "")) return trade_fills @@ -108,12 +124,24 @@ def get_order_status(self, order_ids=None, start_date=None, end_date=None): order_status = pd.read_sql_query(query, session.connection()) return order_status + def get_market_data(self, start_date=None, end_date=None): + with self.session_maker() as session: + query = self._get_market_data_query(start_date, end_date) + market_data = pd.read_sql_query(query, session.connection()) + market_data["timestamp"] = pd.to_datetime(market_data["timestamp"] / 1e6, unit="ms") + market_data.set_index("timestamp", inplace=True) + market_data["mid_price"] = market_data["mid_price"] / 1e6 + market_data["best_bid"] = market_data["best_bid"] / 1e6 + market_data["best_ask"] = market_data["best_ask"] / 1e6 + return market_data + def get_strategy_data(self, config_file_path=None, start_date=None, end_date=None): orders = self.get_orders(config_file_path, start_date, end_date) trade_fills = self.get_trade_fills(config_file_path, start_date, end_date) order_status = self.get_order_status(orders['id'].tolist(), start_date, end_date) - orders['creation_timestamp'] = pd.to_datetime(orders['creation_timestamp'], unit="ms") - orders['last_update_timestamp'] = pd.to_datetime(orders['last_update_timestamp'], unit="ms") - trade_fills["timestamp"] = pd.to_datetime(trade_fills["timestamp"], unit="ms") - strategy_data = StrategyData(orders, order_status, trade_fills) + try: + market_data = self.get_market_data(start_date, end_date) + except Exception as e: + market_data = None + strategy_data = StrategyData(orders, order_status, trade_fills, market_data) return strategy_data diff --git a/utils/graphs.py b/utils/graphs.py index 15163818..12678fc5 100644 --- a/utils/graphs.py +++ b/utils/graphs.py @@ -35,7 +35,7 @@ def figure(self): def add_candles_graph(self): self.base_figure.add_trace( go.Candlestick( - x=self.candles_df['datetime'], + x=self.candles_df.index, open=self.candles_df['open'], high=self.candles_df['high'], low=self.candles_df['low'], @@ -85,7 +85,7 @@ def add_bollinger_bands(self, length=20, std=2.0, row=1): df.ta.bbands(length=length, std=std, append=True) self.base_figure.add_trace( go.Scatter( - x=df['datetime'], + x=df.index, y=df[f'BBU_{length}_{std}'], name='Bollinger Bands', mode='lines', @@ -94,7 +94,7 @@ def add_bollinger_bands(self, length=20, std=2.0, row=1): ) self.base_figure.add_trace( go.Scatter( - x=df['datetime'], + x=df.index, y=df[f'BBM_{length}_{std}'], name='Bollinger Bands', mode='lines', @@ -103,7 +103,7 @@ def add_bollinger_bands(self, length=20, std=2.0, row=1): ) self.base_figure.add_trace( go.Scatter( - x=df['datetime'], + x=df.index, y=df[f'BBL_{length}_{std}'], name='Bollinger Bands', mode='lines', @@ -114,7 +114,7 @@ def add_bollinger_bands(self, length=20, std=2.0, row=1): def add_volume(self): self.base_figure.add_trace( go.Bar( - x=self.candles_df['datetime'], + x=self.candles_df.index, y=self.candles_df['volume'], name="Volume", opacity=0.5, @@ -132,7 +132,7 @@ def add_ema(self, length=20, row=1): df.ta.ema(length=length, append=True) self.base_figure.add_trace( go.Scatter( - x=df['datetime'], + x=df.index, y=df[f'EMA_{length}'], name='EMA', mode='lines', @@ -157,15 +157,15 @@ def add_base_inventory_change(self, strategy_data: StrategyData, row=3): merged_df = self.get_merged_df(strategy_data) self.base_figure.add_trace( go.Scatter( - x=merged_df.datetime, + x=merged_df.index, y=merged_df["cum_net_amount"], name="Cumulative Base Inventory Change", - mode="lines+markers+text", + mode="lines+markers", marker=dict(color="black", size=6), line=dict(color="royalblue", width=2), - text=merged_df["cum_net_amount"], - textposition="top center", - texttemplate="%{text:.2f}" + # text=merged_df["cum_net_amount"], + # textposition="top center", + # texttemplate="%{text:.2f}" ), row=row, col=1 ) @@ -173,10 +173,9 @@ def add_base_inventory_change(self, strategy_data: StrategyData, row=3): def add_pnl(self, strategy_data: SingleMarketStrategyData, row=4): merged_df = self.get_merged_df(strategy_data) - self.base_figure.add_trace( go.Scatter( - x=merged_df["datetime"], + x=merged_df.index, y=merged_df["cum_fees_in_quote"].apply(lambda x: round(-x, 2)), name="Cum Fees", mode='lines', @@ -189,7 +188,7 @@ def add_pnl(self, strategy_data: SingleMarketStrategyData, row=4): self.base_figure.add_trace( go.Scatter( - x=merged_df["datetime"], + x=merged_df.index, y=merged_df["trade_pnl_continuos"].apply(lambda x: round(x, 2)), name="Cum Trade PnL", mode='lines', @@ -201,15 +200,15 @@ def add_pnl(self, strategy_data: SingleMarketStrategyData, row=4): ) self.base_figure.add_trace( go.Scatter( - x=merged_df["datetime"], + x=merged_df.index, y=merged_df["net_pnl_continuos"].apply(lambda x: round(x, 2)), name="Cum Net PnL", - mode="lines+markers+text", + mode="lines+markers", marker=dict(color="black", size=6), line=dict(color="black", width=2), - textposition="top center", - text=merged_df["net_pnl_continuos"], - texttemplate="%{text:.1f}" + # textposition="top center", + # text=merged_df["net_pnl_continuos"], + # texttemplate="%{text:.1f}" ), row=row, col=1 ) @@ -241,7 +240,7 @@ def update_layout(self): self.base_figure.update_xaxes(title_text="Time", row=self.rows, col=1) def get_merged_df(self, strategy_data: StrategyData): - merged_df = pd.merge_asof(self.candles_df, strategy_data.trade_fill, left_on="datetime", right_on="timestamp", direction="backward") + merged_df = pd.merge_asof(self.candles_df, strategy_data.trade_fill, left_index=True, right_on="timestamp", direction="backward") merged_df["trade_pnl_continuos"] = merged_df["unrealized_trade_pnl"] + merged_df["cum_net_amount"] * merged_df["close"] merged_df["net_pnl_continuos"] = merged_df["trade_pnl_continuos"] - merged_df["cum_fees_in_quote"] return merged_df