hummingbot · cardosofede · Aug 1, 2023 · Jul 31, 2023 · Jul 31, 2023 · Jul 31, 2023
diff --git a/pages/strategy_performance/app.py b/pages/strategy_performance/app.py
@@ -1,6 +1,7 @@
 import os
 import pandas as pd
 import streamlit as st
+import math
 
 from utils.database_manager import DatabaseManager
 from utils.graphs import CandlesGraph
@@ -23,102 +24,143 @@
 
 
 @st.cache_resource
-def get_database(db_name: str):
-    db_manager = DatabaseManager(db_name)
-    return db_manager
+def get_databases():
+    sqlite_files = [db_name for db_name in os.listdir("data") if db_name.endswith(".sqlite")]
+    databases_list = [DatabaseManager(db) for db in sqlite_files]
+    return {database.db_name: database for database in databases_list}
 
 
-with st.container():
-    col1, col2 = st.columns(2)
+def download_csv(df: pd.DataFrame, filename: str, key: str):
+    csv = df.to_csv(index=False).encode('utf-8')
+    return st.download_button(
+                label="Press to Download",
+                data=csv,
+                file_name=f"{filename}.csv",
+                mime="text/csv",
+                key=key
+            )
+
+
+st.session_state["dbs"] = get_databases()
+db_names = [x.db_name for x in st.session_state["dbs"].values() if x.status == 'OK']
+if not db_names:
+    st.warning("No trades have been recorded in the selected database")
+    selected_db_name = None
+    selected_db = None
+else:
+    st.subheader("⚙️ Filters")
+    col1, col2, col3, col4 = st.columns(4)
     with col1:
-        db_names = [db_name for db_name in os.listdir("data") if db_name.endswith(".sqlite")]
-        selected_db_name = st.selectbox("Select a database to use:",
-                                        db_names if len(db_names) > 0 else ["No databases found"])
-    if selected_db_name == "No databases found":
-        st.warning("No databases available to analyze. Please run a backtesting first.")
-    else:
-        db_manager = get_database(selected_db_name)
-        config_files = db_manager.get_config_files()
-        if config_files == []:
-            with col1:
-                st.warning('No trades have been recorded in the selected database')
-        with col2:
-            selected_config_file = st.selectbox("Select a config file to analyze:", config_files)
-            if selected_config_file is not None:
-                exchanges_trading_pairs = db_manager.get_exchanges_trading_pairs_by_config_file(selected_config_file)
-                strategy_data = db_manager.get_strategy_data(selected_config_file)
-
+        selected_db_name = st.selectbox("Select a database to use:", db_names)
+        st.session_state["selected_db"] = st.session_state["dbs"][selected_db_name]
+    with col2:
+        if st.session_state.selected_db:
+            st.session_state.selected_config_file = st.selectbox("Select a config file to analyze:", st.session_state.selected_db.config_files)
+        else:
+            st.session_state.selected_config_file = None
+    with col3:
+        if st.session_state.selected_config_file:
+            st.session_state.selected_exchange = st.selectbox("Exchange:", st.session_state.selected_db.configs[st.session_state.selected_config_file].keys())
+    with col4:
+        if st.session_state.selected_exchange:
+            st.session_state.selected_trading_pair = st.selectbox("Trading Pair:", options=st.session_state.selected_db.configs[st.session_state.selected_config_file][st.session_state.selected_exchange])
+
+    single_market = True
+    if single_market:
+        strategy_data = st.session_state["dbs"][selected_db_name].get_strategy_data(st.session_state.selected_config_file)
+        single_market_strategy_data = strategy_data.get_single_market_strategy_data(st.session_state.selected_exchange, st.session_state.selected_trading_pair)
+        date_array = pd.date_range(start=strategy_data.start_time, end=strategy_data.end_time, periods=60)
+        start_time, end_time = st.select_slider("Select a time range to analyze",
+                                                options=date_array.tolist(),
+                                                value=(date_array[0], date_array[-1]))
+        strategy_data_filtered = single_market_strategy_data.get_filtered_strategy_data(start_time, end_time)
+
+        st.markdown("<hr>", unsafe_allow_html=True)
         with st.container():
-            col1, col2, col3 = st.columns(3)
+            col1, col2 = st.columns(2)
             with col1:
-                selected_exchange = st.selectbox("Select an exchange:", [] if selected_config_file is None else list(exchanges_trading_pairs.keys()))
+                st.subheader(f"🏦 Market")
             with col2:
-                selected_trading_pair = st.selectbox("Select a trading pair:", [] if selected_config_file is None else exchanges_trading_pairs[selected_exchange])
+                st.subheader("📋 General stats")
+            col1, col2, col3, col4 = st.columns(4)
+            with col1:
+                st.metric(label="Exchange", value=strategy_data_filtered.exchange.capitalize())
+            with col2:
+                st.metric(label="Trading pair", value=strategy_data_filtered.trading_pair.upper())
             with col3:
-                interval = st.selectbox("Candles Interval:", intervals.keys(), index=2)
+                st.metric(label='Start date', value=strategy_data_filtered.start_time.strftime("%Y-%m-%d %H:%M"))
+                st.metric(label='End date', value=strategy_data_filtered.end_time.strftime("%Y-%m-%d %H:%M"))
+            with col4:
+                st.metric(label='Duration (Hours)', value=round(strategy_data_filtered.duration_seconds / 3600, 2))
+                st.metric(label='Price change', value=f"{round(strategy_data_filtered.price_change * 100, 2)} %")
 
-        if selected_exchange and 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)
-            start_time, end_time = st.select_slider("Select a time range to analyze", options=date_array.tolist(),
-                                                    value=(date_array[0], date_array[-1]))
+            st.markdown("<hr>", unsafe_allow_html=True)
+            st.subheader("📈 Performance")
+            col131, col132, col133, col134 = st.columns(4)
+            with col131:
+                st.metric(label=f'Net PNL {strategy_data_filtered.quote_asset}',
+                          value=round(strategy_data_filtered.net_pnl_quote, 2))
+                st.metric(label=f'Trade PNL {strategy_data_filtered.quote_asset}',
+                          value=round(strategy_data_filtered.trade_pnl_quote, 2))
+                st.metric(label=f'Fees {strategy_data_filtered.quote_asset}',
+                          value=round(strategy_data_filtered.cum_fees_in_quote, 2))
+            with col132:
+                st.metric(label='Total Trades', value=strategy_data_filtered.total_orders)
+                st.metric(label='Total Buy Trades', value=strategy_data_filtered.total_buy_trades)
+                st.metric(label='Total Sell Trades', value=strategy_data_filtered.total_sell_trades)
+            with col133:
+                st.metric(label='Inventory change in Base asset',
+                          value=round(strategy_data_filtered.inventory_change_base_asset, 4))
+                st.metric(label='Total Buy Trades Amount',
+                          value=round(strategy_data_filtered.total_buy_amount, 2))
+                st.metric(label='Total Sell Trades Amount',
+                          value=round(strategy_data_filtered.total_sell_amount, 2))
+            with col134:
+                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))
 
-            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:
-                with col11:
-                    st.header(f"🏦 Market")
-                    st.metric(label="Exchange", value=strategy_data_filtered.exchange.capitalize())
-                    st.metric(label="Trading pair", value=strategy_data_filtered.trading_pair.upper())
-                with col12:
-                    st.header("📋 General stats")
-                    col121, col122 = st.columns(2)
-                    with col121:
-                        st.metric(label='Duration (Hours)', value=round(strategy_data_filtered.duration_seconds / 3600, 2))
-                        st.metric(label='Start date', value=strategy_data_filtered.start_time.strftime("%Y-%m-%d %H:%M"))
-                        st.metric(label='End date', value=strategy_data_filtered.end_time.strftime("%Y-%m-%d %H:%M"))
-                    with col122:
-                        st.metric(label='Price change', value=f"{round(strategy_data_filtered.price_change * 100, 2)} %")
-                with col13:
-                    st.header("📈 Performance")
-                    col131, col132, col133, col134 = st.columns(4)
-                    with col131:
-                        st.metric(label=f'Net PNL {strategy_data_filtered.quote_asset}', value=round(strategy_data_filtered.net_pnl_quote, 2))
-                        st.metric(label=f'Trade PNL {strategy_data_filtered.quote_asset}', value=round(strategy_data_filtered.trade_pnl_quote, 2))
-                        st.metric(label=f'Fees {strategy_data_filtered.quote_asset}', value=round(strategy_data_filtered.cum_fees_in_quote, 2))
-                    with col132:
-                        st.metric(label='Total Trades', value=strategy_data_filtered.total_orders)
-                        st.metric(label='Total Buy Trades', value=strategy_data_filtered.total_buy_trades)
-                        st.metric(label='Total Sell Trades', value=strategy_data_filtered.total_sell_trades)
-                    with col133:
-                        st.metric(label='Inventory change in Base asset',
-                                  value=round(strategy_data_filtered.inventory_change_base_asset, 4))
-                        st.metric(label='Total Buy Trades Amount', value=round(strategy_data_filtered.total_buy_amount, 2))
-                        st.metric(label='Total Sell Trades Amount', value=round(strategy_data_filtered.total_sell_amount, 2))
-                    with col134:
-                        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))
-            if strategy_data_filtered.market_data is not None:
-                candles_df = strategy_data_filtered.get_market_data_resampled(interval=f"{intervals[interval]}S")
+        st.markdown("<hr>", unsafe_allow_html=True)
+        st.subheader("🕯️ Candlestick")
+        if strategy_data_filtered.market_data is not None:
+            with st.expander("Market activity", expanded=True):
+                col1, col2, col3 = st.columns([1, 1, 2])
+                with col1:
+                    interval = st.selectbox("Candles Interval:", intervals.keys(), index=2)
+                with col2:
+                    rows_per_page = st.number_input("Candles per Page", value=100, min_value=1, max_value=5000)
+                with col3:
+                    total_rows = len(strategy_data_filtered.get_market_data_resampled(interval=f"{intervals[interval]}S"))
+                    total_pages = math.ceil(total_rows / rows_per_page)
+                    if total_pages > 1:
+                        selected_page = st.select_slider("Select page", list(range(total_pages)), key="page_slider")
+                    else:
+                        selected_page = 0
+                start_idx = selected_page * rows_per_page
+                end_idx = start_idx + rows_per_page
+                candles_df = strategy_data_filtered.get_market_data_resampled(interval=f"{intervals[interval]}S").iloc[
+                             start_idx:end_idx]
+                start_time_page = candles_df.index.min()
+                end_time_page = candles_df.index.max()
+                page_data_filtered = single_market_strategy_data.get_filtered_strategy_data(start_time_page, end_time_page)
                 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")
+        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.markdown("<hr>", unsafe_allow_html=True)
+        st.subheader("Tables")
+        with st.expander("💵 Trades"):
             st.write(strategy_data_filtered.trade_fill)
-
-            st.subheader("📩 Orders")
+            download_csv(strategy_data_filtered.trade_fill, "trade_fill", "download-trades")
+        with st.expander("📩 Orders"):
             st.write(strategy_data_filtered.orders)
-
-            st.subheader("⌕ Order Status")
+            download_csv(strategy_data_filtered.orders, "orders", "download-orders")
+        with st.expander("⌕ Order Status"):
             st.write(strategy_data_filtered.order_status)
+            download_csv(strategy_data_filtered.order_status, "order_status", "download-order-status")
diff --git a/utils/database_manager.py b/utils/database_manager.py
@@ -16,6 +16,28 @@ def __init__(self, db_name):
         self.engine = create_engine(self.db_path, connect_args={'check_same_thread': False})
         self.session_maker = sessionmaker(bind=self.engine)
 
+    @property
+    def status(self):
+        try:
+            with self.session_maker() as session:
+                query = 'SELECT DISTINCT config_file_path FROM TradeFill'
+                config_files = pd.read_sql_query(query, session.connection())
+            if len(config_files) > 0:
+            # TODO: improve error handling, think what to do with other cases
+                return "OK"
+            else:
+                return "No records found in the TradeFill table with non-null config_file_path"
+        except Exception as e:
+            return f"Error: {str(e)}"
+
+    @property
+    def config_files(self):
+        return self.get_config_files()
+
+    @property
+    def configs(self):
+        return {config_file: self.get_exchanges_trading_pairs_by_config_file(config_file) for config_file in self.config_files}
+
     def get_config_files(self):
         with self.session_maker() as session:
             query = 'SELECT DISTINCT config_file_path FROM TradeFill'

diff --git a/utils/graphs.py b/utils/graphs.py
@@ -16,6 +16,8 @@ def __init__(self, candles_df: pd.DataFrame, show_volume=True, extra_rows=1):
         specs = [[{"secondary_y": True}]] * rows
         self.base_figure = make_subplots(rows=rows, cols=1, shared_xaxes=True, vertical_spacing=0.005,
                                          row_heights=heights, specs=specs)
+        self.min_time = candles_df.reset_index().timestamp.min()
+        self.max_time = candles_df.reset_index().timestamp.max()
         self.add_candles_graph()
         if self.show_volume:
             self.add_volume()
@@ -231,7 +233,9 @@ def update_layout(self):
                 x=1
             ),
             height=1500,
-            xaxis_rangeslider_visible=False,
+            xaxis=dict(rangeslider_visible=False,
+                       range=[self.min_time, self.max_time]),
+            yaxis=dict(range=[self.candles_df.low.min(), self.candles_df.high.max()]),
             hovermode='x unified'
         )
         self.base_figure.update_yaxes(title_text="Price", row=1, col=1)

diff --git a/utils/st_utils.py b/utils/st_utils.py
@@ -5,11 +5,12 @@
 import streamlit as st
 
 
-def initialize_st_page(title: str, icon: str, layout="wide"):
+def initialize_st_page(title: str, icon: str, layout="wide", initial_sidebar_state="collapsed"):
     st.set_page_config(
         page_title=title,
         page_icon=icon,
         layout=layout,
+        initial_sidebar_state=initial_sidebar_state
     )
     st.title(f"{icon} {title}")
     caller_frame = inspect.currentframe().f_back