RollingBacktestNotebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FreqTrade backtesting analysis and plotting notebook\n",
    "\n",
    "Version: v2\n",
    "\n",
    "Date: 2023-07-22\n",
    "\n",
    "GitHub: https://github.com/froggleston/freqtrade_analysis_notebook\n",
    "\n",
    "Authors:\n",
    "* **@froggleston** (for the notebook and some of the notebook_helper code)\n",
    "* **@rk** (for most of the notebook_helper code)\n",
    "\n",
    "### Welcome to the Notebook\n",
    "\n",
    "This notebook should make it easier to run sequential test backtests and compare them against a single benchmark backtest.\n",
    "\n",
    "### Things to Note\n",
    "\n",
    "* If parallel is set to True, it runs a per month backtest per core unlike the usual freqtrade backtests.\n",
    "* This will use more memory, like hyperopting, so backtests can be killed if you use too many cores and run out of memory\n",
    "* If parallel, backtests will \"reset\" each month so unlimited stake backtests will only give you cumulative results per month."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load prerequisites\n",
    "\n",
    "**Only needs to be run once**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%matplotlib inline\n",
    "%autoreload 2\n",
    "\n",
    "import joblib, json, os, random, sys, time, traceback\n",
    "from time import perf_counter\n",
    "from collections import deque\n",
    "from datetime import datetime, timedelta\n",
    "from pathlib import Path\n",
    "\n",
    "import pandas as pd\n",
    "from pandas import DataFrame, Series\n",
    "import numpy as np\n",
    "\n",
    "from freqtrade.enums import RunMode, CandleType\n",
    "from freqtrade.misc import deep_merge_dicts\n",
    "from freqtrade.configuration import Configuration, TimeRange\n",
    "from freqtrade.data.btanalysis import load_trades_from_db, load_backtest_data, load_backtest_stats\n",
    "from freqtrade.data.history import load_pair_history\n",
    "from freqtrade.data.dataprovider import DataProvider\n",
    "import freqtrade.data.entryexitanalysis as eea\n",
    "from freqtrade.plugins.pairlistmanager import PairListManager\n",
    "from freqtrade.exceptions import ExchangeError, OperationalException\n",
    "from freqtrade.exchange import Exchange\n",
    "from freqtrade.resolvers import ExchangeResolver, StrategyResolver\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "import notebook_helper\n",
    "notebook_helper.setup()\n",
    "\n",
    "import nest_asyncio\n",
    "nest_asyncio.apply()\n",
    "\n",
    "from IPython.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore', category=pd.errors.PerformanceWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "os.chdir(\"/freqtrade/\")\n",
    "\n",
    "def load_candles(pairlist, timerange, data_location, timeframe=\"5m\", data_format=\"json\", candle_type=CandleType.SPOT):\n",
    "    all_candles = dict()\n",
    "    print(f'Loading all {data_format} {candle_type} candle data: {timerange} [{data_location}]')\n",
    "\n",
    "    for pair in pairlist:\n",
    "        if timerange is not None:\n",
    "            ptr = TimeRange.parse_timerange(timerange)\n",
    "            candles = load_pair_history(datadir=data_location,\n",
    "                                        timeframe=timeframe,\n",
    "                                        timerange=ptr,\n",
    "                                        pair=pair,\n",
    "                                        data_format=data_format,\n",
    "                                        candle_type=candle_type,\n",
    "                                        )\n",
    "        else:\n",
    "            candles = load_pair_history(datadir=data_location,\n",
    "                                        timeframe=timeframe,\n",
    "                                        pair=pair,\n",
    "                                        data_format=data_format,\n",
    "                                        candle_type=candle_type,\n",
    "                                        )\n",
    "        all_candles[pair] = candles\n",
    "    return all_candles\n",
    "\n",
    "def do_plot(pair, data, trades, d_start, d_end, plot_config=None, buy_tags=None, sell_tags=None, width=1400, height=1200):\n",
    "    try:\n",
    "        from freqtrade.plot.plotting import generate_candlestick_graph\n",
    "        import plotly.offline as pyo\n",
    "\n",
    "        trades_red = pd.DataFrame()\n",
    "        \n",
    "        if trades.shape[0] > 0:\n",
    "            # Filter trades to one pair\n",
    "            trades_red = trades.loc[trades['pair'] == pair].copy()\n",
    "        \n",
    "        buyf = data[data.filter(regex=r'^enter', axis=1).values==1]\n",
    "\n",
    "        if buyf.shape[0] > 0 and trades_red.shape[0] > 0:\n",
    "            for t, v in trades_red.open_date.items():\n",
    "                tc = buyf.loc[(buyf['date'] < v)]\n",
    "                if tc is not None and tc.shape[0] > 0:\n",
    "                    bt = tc.iloc[-1].filter(regex=r'^enter', axis=0)\n",
    "                    bt.dropna(inplace=True)\n",
    "                    tbt = trades_red.loc[t, 'enter_tag']\n",
    "                    tst = trades_red.loc[t, 'exit_reason']\n",
    "                    \n",
    "                    if isinstance(tbt, Series):\n",
    "                        tbt = tbt.iloc[0]\n",
    "                    if isinstance(tst, Series):\n",
    "                        tst = tst.iloc[0]\n",
    "                        \n",
    "                    if buy_tags is not None and tbt not in buy_tags and t in trades_red:\n",
    "                        trades_red.drop(t, inplace=True)\n",
    "                    else:\n",
    "                        trades_red.loc[t, 'exit_reason'] = f\"{tbt} / {trades_red.loc[t, 'exit_reason']}\"\n",
    "                        \n",
    "                    if sell_tags is not None and tst not in sell_tags and t in trades_red:\n",
    "                        trades_red.drop(t, inplace=True)\n",
    "                    else:\n",
    "                        trades_red.loc[t, 'exit_reason'] = f\"{tst} / {trades_red.loc[t, 'exit_reason']}\"\n",
    "\n",
    "        # Limit graph period to your BT timerange\n",
    "        data_red = data[d_start:d_end]\n",
    "\n",
    "        # Generate candlestick graph\n",
    "        graph = generate_candlestick_graph(pair=pair,\n",
    "                                           data=data_red,\n",
    "                                           trades=trades_red,\n",
    "                                           plot_config=plot_config\n",
    "                                           )\n",
    "\n",
    "        graph.update_layout(autosize=False,width=width,height=height)\n",
    "        pyo.iplot(graph, show_link = False)\n",
    "    except Exception as e:\n",
    "        traceback.print_exc(*sys.exc_info())\n",
    "        print(\"You got frogged: \", e)\n",
    "\n",
    "if 'executed' not in globals():\n",
    "    executed = True         # Guard against running this multiple times\n",
    "    cwd = os.getcwd()       # equivalent to !pwd\n",
    "    # cwd = cwd[0]          # only use if using !pwd above\n",
    "    sys.path.append(cwd)    # Add notebook dir to python path for utility imports\n",
    "    # cd to root directory to make relative paths in config valid\n",
    "    %cd .\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## main configuration for everything ##\n",
    "\n",
    "# parallelise backtests by month\n",
    "parallel = True\n",
    "proportion_cores_parallel = 0.33 # set to a third of available cores, e.g. 12 cores, will use 4\n",
    "\n",
    "# set to true if you want to open all slots to test buys without rejections\n",
    "reject_test = False\n",
    "if reject_test:\n",
    "    max_open_trades = -1\n",
    "    dry_run_wallet = 6900 # nice\n",
    "else:\n",
    "    max_open_trades = 5\n",
    "    dry_run_wallet = 2000\n",
    "\n",
    "# set to true if you want a static stake amount\n",
    "static_amount = True\n",
    "\n",
    "stake_amount = dry_run_wallet / max_open_trades\n",
    "if stake_amount < 100 or static_amount:\n",
    "    stake_amount = 100\n",
    "\n",
    "# shorting or not\n",
    "short = False\n",
    "\n",
    "# exchange\n",
    "exchange = \"binance\"\n",
    "\n",
    "# set benchmark and test strategies to compare\n",
    "bench_strat = \"name_of_your_benchmark_strat\"\n",
    "test_strat = \"name_of_your_test_strat\"\n",
    "\n",
    "# set your config file\n",
    "config_file = \"your_config.json\"\n",
    "\n",
    "# set your format and path to downloaded data\n",
    "data_format = \"json\"\n",
    "data_location = Path('/path', 'to', 'your', 'data', f'{exchange}')\n",
    "\n",
    "# set your stake currency and stake format\n",
    "stake_currency = \"USDT\"\n",
    "    \n",
    "# set your chosen stoploss\n",
    "stoploss = -0.08\n",
    "\n",
    "# set your minimal roi\n",
    "minimal_roi = { '0': 10 }\n",
    "\n",
    "# turn off/on protections\n",
    "enable_protections = True\n",
    "\n",
    "ft_config = Configuration.from_files(files=[config_file])\n",
    "pairlist = ft_config['exchange'].get('pair_whitelist', None)\n",
    "pair_count = len(pairlist)\n",
    "\n",
    "timeframe_detail = None\n",
    "# uncomment for 1m detail\n",
    "# timeframe_detail = \"1m\"\n",
    "# ft_config['timeframe_detail'] = timeframe_detail\n",
    "\n",
    "ft_config['datadir'] = data_location\n",
    "\n",
    "if short:\n",
    "    trading_mode = CandleType.FUTURES\n",
    "    stake = f\"{stake_currency}:{stake_currency}\"\n",
    "    \n",
    "    config = {\n",
    "        'max_open_trades': max_open_trades,\n",
    "        'dry_run_wallet': dry_run_wallet,\n",
    "        'stake_amount': stake_amount,\n",
    "        'stake_currency': stake_currency,\n",
    "        'exchange': {\n",
    "            'name': exchange,\n",
    "        },\n",
    "        'export': 'signals',\n",
    "        'datadir': str(data_location),\n",
    "        'trading_mode': 'futures',\n",
    "        'margin_mode': 'isolated',\n",
    "        'dataformat_ohlcv':data_format,\n",
    "        'stoploss': stoploss,\n",
    "        'minimal_roi': minimal_roi,\n",
    "        \"entry_pricing\": {\n",
    "            \"price_side\": \"ask\",\n",
    "            \"price_last_balance\": 0,\n",
    "            \"use_order_book\": True,\n",
    "            \"order_book_top\": 1,\n",
    "            \"check_depth_of_market\": {\n",
    "                \"enabled\": False,\n",
    "                \"bids_to_ask_delta\": 1\n",
    "            }\n",
    "        },\n",
    "        \"exit_pricing\": {\n",
    "            \"price_side\": \"bid\",\n",
    "            \"use_order_book\": True,\n",
    "            \"order_book_top\": 1\n",
    "        }   \n",
    "    }\n",
    "else:\n",
    "    trading_mode = CandleType.SPOT\n",
    "    stake = f\"{stake_currency}\"\n",
    "    \n",
    "    config = {\n",
    "        'max_open_trades': max_open_trades,\n",
    "        'dry_run_wallet': dry_run_wallet,\n",
    "        'stake_amount': stake_amount,\n",
    "        'stake_currency': stake_currency,\n",
    "        'exchange': {\n",
    "            'name': exchange,\n",
    "        },\n",
    "        'stoploss': stoploss,\n",
    "        'export': 'signals',\n",
    "        'datadir': str(data_location),\n",
    "        'dataformat_ohlcv':data_format,\n",
    "        'minimal_roi': minimal_roi,\n",
    "        'enable_protections': enable_protections,\n",
    "    }\n",
    "\n",
    "bench_config = {\n",
    "    'strategy': bench_strat,\n",
    "    'pair_count': [pair_count],\n",
    "    'config': [\n",
    "        config_file,\n",
    "        deep_merge_dicts({\n",
    "        }, config)\n",
    "    ]\n",
    "}\n",
    "\n",
    "strat_config = {\n",
    "    'strategy': test_strat,\n",
    "    'pair_count': [pair_count],\n",
    "    'config': [\n",
    "        config_file,\n",
    "        deep_merge_dicts({\n",
    "        }, config)\n",
    "    ]\n",
    "}    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Benchmark Strategy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%t\n"
    }
   },
   "outputs": [],
   "source": [
    "# set overall full timerange to use for benchmark and test backtests\n",
    "# start day should be 01 due to issue #8 https://github.com/froggleston/freqtrade_analysis_notebook/issues/8\n",
    "timerange = \"20220901-20221201\"\n",
    "\n",
    "# shouldn't need to change anything below here\n",
    "\n",
    "# prepare configs and timeranges\n",
    "test_timeranges = list(notebook_helper.split_timerange(timerange))\n",
    "both_configs = [bench_config,strat_config]\n",
    "for c in both_configs:\n",
    "    c['timeranges'] = test_timeranges\n",
    "\n",
    "# file to cache benchmark results\n",
    "# so we can run multiple test strat runs without having to redo this\n",
    "pklf = f\"user_data/backtest_results/{bench_config['strategy']}-results_{timerange}-{max_open_trades}_{dry_run_wallet}_{int(stake_amount)}.pkl\"\n",
    "\n",
    "if not os.path.exists(pklf):\n",
    "    print(\"Creating\", pklf)\n",
    "    print(bench_config, data_location, data_format, trading_mode)\n",
    "\n",
    "    # do backtesting per month\n",
    "    try:\n",
    "        start_time = perf_counter()\n",
    "        results = notebook_helper.backtest_all([bench_config], parallel, proportion_cores_parallel, data_location=data_location, data_format=data_format, trading_mode=trading_mode, timeframe_detail=timeframe_detail)\n",
    "        end_time = perf_counter()\n",
    "\n",
    "        pkl_results = []\n",
    "\n",
    "        for rrs in results:\n",
    "            for rrss in rrs[1]:\n",
    "                if bench_config['strategy'] == rrss[\"key\"]:\n",
    "                    pkl_results.append(rrs)\n",
    "\n",
    "        file = open(pklf, \"wb\")\n",
    "        joblib.dump(pkl_results, file)\n",
    "        file.close()\n",
    "\n",
    "        print(f\"Done creating benchmark backtest. Elapsed time: {(end_time - start_time)/60}\")\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "else:\n",
    "    print(f\"Loaded previous backtest result from: {pklf}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Test Strategy\n",
    "\n",
    "**Only run a backtest for the test strat, not the benchmark. Once a benchmark backtest is complete, this can be run multiple times.**\n",
    "\n",
    "As long as the single_tr is a smaller timerange than the benchmark strategy timerange, this can speed up comparison a lot.\n",
    "\n",
    "This will use the previously cached bechmark backtest result from the pkl file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# run the test strategy for the same timerange as benchmark \n",
    "# start day should be 01 due to issue #8 https://github.com/froggleston/freqtrade_analysis_notebook/issues/8\n",
    "single_tr = timerange\n",
    "\n",
    "# uncomment to specify a timerange that is the same as or smaller than the original timerange\n",
    "# single_tr = \"20220901-20221201\"\n",
    "\n",
    "if os.path.exists(pklf):\n",
    "    redo_results = []\n",
    "    \n",
    "    dtformat = \"%Y%m%d\"\n",
    "    basedate = single_tr.split(\"-\")[0]\n",
    "    basedate_rounded = pd.Series(basedate).astype('datetime64[ns, UTC]')\n",
    "    basedate_rounded = (basedate_rounded - pd.tseries.offsets.MonthBegin(1)).iloc[0].strftime(dtformat)\n",
    "    \n",
    "    load_results = []\n",
    "    with (open(pklf, \"rb\")) as openfile:\n",
    "        while True:\n",
    "            try:\n",
    "                load_results.append(joblib.load(openfile))\n",
    "            except EOFError:\n",
    "                break\n",
    "                \n",
    "    single_config = [strat_config.copy()]\n",
    "    redo_config = [bench_config.copy(), strat_config.copy()]\n",
    "    single_trs = list(notebook_helper.split_timerange(single_tr))\n",
    "    \n",
    "    single_config[0][\"timeranges\"] = single_trs\n",
    "    redo_config[0][\"timeranges\"] = single_trs\n",
    "    redo_config[1][\"timeranges\"] = single_trs\n",
    "    \n",
    "    start_time = perf_counter()\n",
    "    single_results = notebook_helper.backtest_all(single_config, parallel, proportion_cores_parallel, data_location=data_location, data_format=data_format, trading_mode=trading_mode, timeframe_detail=timeframe_detail)\n",
    "    end_time = perf_counter()\n",
    "    print(f\"Elapsed time: {(end_time - start_time)/60}\")\n",
    "    \n",
    "    redo_dict = {}\n",
    "    redo_trs = [x.split(\"-\")[0] for x in single_trs]\n",
    "    \n",
    "    ## add results:\n",
    "    ## - if the original monthly dates start on the first day of the month and match the redo dates\n",
    "    ## - if the original monthly dates do not start on the first day of the month and neither do the redo dates\n",
    "    rounded_redo_trs = []\n",
    "    for i in redo_trs:\n",
    "        tm_rounded = pd.Series(i).astype('datetime64[ns, UTC]')\n",
    "        \n",
    "        if tm_rounded.iloc[0].day != 1:\n",
    "            tm_rounded = (tm_rounded - pd.tseries.offsets.MonthBegin(1)).iloc[0].strftime(dtformat)\n",
    "        else:\n",
    "            tm_rounded = tm_rounded.iloc[0].strftime(dtformat)\n",
    "        \n",
    "        rounded_redo_trs.append(tm_rounded)\n",
    "    \n",
    "    for lrs in load_results[0]:\n",
    "        resdate = lrs[1][0][\"date\"].replace(\"-\",\"\")\n",
    "        \n",
    "        if resdate in rounded_redo_trs:\n",
    "            redo_dict[resdate] = lrs\n",
    "        elif resdate in redo_trs:\n",
    "            tm_rounded = pd.Series(i).astype('datetime64[ns, UTC]')\n",
    "            if tm_rounded.iloc[0].day != 1:\n",
    "                tm_rounded = (tm_rounded - pd.tseries.offsets.MonthBegin(1)).iloc[0].strftime(dtformat)\n",
    "            else:\n",
    "                tm_rounded = tm_rounded.iloc[0].strftime(dtformat)\n",
    "            redo_dict[tm_rounded] = lrs\n",
    "    \n",
    "    for srs in single_results:\n",
    "        srsdate = srs[1][0][\"date\"].replace(\"-\",\"\")\n",
    "        \n",
    "        tm_rounded = pd.Series(srsdate).astype('datetime64[ns, UTC]')\n",
    "        \n",
    "        if tm_rounded.iloc[0].day != 1:\n",
    "            tm_rounded = (tm_rounded - pd.tseries.offsets.MonthBegin(1)).iloc[0].strftime(dtformat)\n",
    "        else:\n",
    "            tm_rounded = tm_rounded.iloc[0].strftime(dtformat)\n",
    "        \n",
    "        redo_results.append(redo_dict[tm_rounded])\n",
    "        \n",
    "        # reset config_i\n",
    "        new_srs_tuple = (srs[0], srs[1], srs[2], srs[3], 1, srs[5])\n",
    "        redo_results.append(new_srs_tuple)\n",
    "    \n",
    "    load_strategy_comparison, load_strategy_trades, load_strategy_signal_candles = notebook_helper.prepare_results(redo_config, redo_results)\n",
    "    notebook_helper.print_quant_stats(redo_config, load_strategy_comparison, load_strategy_trades, table=True, output='user_data/notebooks/{strategy}-vs-{benchmark}.html')\n",
    "\n",
    "    br_comparison = load_strategy_comparison\n",
    "    br_trades = load_strategy_trades\n",
    "    br_candles = load_strategy_signal_candles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prepare Test Strategy Backtesting Results\n",
    "\n",
    "**This section needs to be run after each test strategy backtest for comparison, analysis and plotting** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# do backtesting-analysis output\n",
    "from tabulate import tabulate\n",
    "\n",
    "import nest_asyncio\n",
    "nest_asyncio.apply()\n",
    "\n",
    "stratnames = []\n",
    "for tcs in both_configs:\n",
    "    stratnames += [tcs['strategy']]\n",
    "\n",
    "stratnames += [single_config[0]['strategy']]\n",
    "\n",
    "atd_results={}\n",
    "trade_dict={}\n",
    "\n",
    "all_results = []\n",
    "atd={}\n",
    "\n",
    "for strat in stratnames:\n",
    "    atd[strat] = {}\n",
    "    trade_dict[strat] = pd.DataFrame()\n",
    "\n",
    "for stratname in stratnames:\n",
    "    ft_config['strategy'] = stratname\n",
    "    \n",
    "    if stratname in br_candles:\n",
    "        stratdf = br_candles[stratname]\n",
    "\n",
    "        for sts in br_trades:\n",
    "            if br_trades[sts].strategy_name == stratname:\n",
    "                trades = br_trades[sts].trades\n",
    "                trade_dict[stratname] = trades\n",
    "\n",
    "        strat_results = []\n",
    "\n",
    "        current_count = 1\n",
    "        for pair in pairlist:\n",
    "            if pair in stratdf and len(stratdf[pair]) > 0:\n",
    "                trades_red = eea._analyze_candles_and_indicators(pair, trades, stratdf[pair])\n",
    "                atd[stratname][pair] = trades_red\n",
    "                strat_results.append((pair,atd))\n",
    "                current_count += 1\n",
    "        \n",
    "        print(f\"Adding {stratname} to comparison\")\n",
    "        atd_results[stratname] = strat_results\n",
    "    else:\n",
    "        print(f\"Ignoring '{stratname}' in previous output.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Dry/Live vs Backtest Comparison\n",
    "\n",
    "**This requires a dry run / live sqlite database to be available on the db_name path specified**\n",
    "\n",
    "Can be skipped if you don't want to compare dry/live to backtest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from freqtrade.data.btanalysis import load_backtest_data, load_backtest_stats\n",
    "\n",
    "# specify your actual trades from a dry/live DB\n",
    "db_path = \"your_db_name.sqlite\"\n",
    "\n",
    "# if backtest_dir points to a directory, it'll automatically load the last backtest file\n",
    "backtest_dir = \"user_data/backtest_results\"\n",
    "\n",
    "# or specify a specific backtest results file\n",
    "# backtest_dir = config[\"user_data_dir\"] / \"backtest_results/backtest-result-2020-07-01_20-04-22.json\"\n",
    "\n",
    "dat = sqlite3.connect(db_path)\n",
    "sel_cols = \"pair,open_date,close_date,min_rate,max_rate,enter_tag,exit_reason,open_rate,close_rate,close_profit,close_profit_abs\"\n",
    "query = dat.execute(f\"SELECT {sel_cols} FROM trades\")\n",
    "cols = [column[0] for column in query.description]\n",
    "sql_trades = pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "sql_trades['real_open_date'] = sql_trades['open_date'].astype('datetime64[ns, UTC]')\n",
    "sql_trades['open_date'] = sql_trades['real_open_date'].dt.floor('T')\n",
    "sql_trades['close_date'] = sql_trades['close_date'].astype('datetime64[ns, UTC]')\n",
    "\n",
    "analyse_strat = stratnames[0]\n",
    "\n",
    "# bt_trades = load_backtest_data(backtest_dir)\n",
    "bt_trades = trade_dict[analyse_strat].copy() #.sort_values(by='open_date')\n",
    "bt_trades.reset_index(drop=True, inplace=True)\n",
    "\n",
    "# bt_signals = atd_results[analyse_strat]\n",
    "bt_signals = atd[analyse_strat]\n",
    "\n",
    "if \"orders\" in bt_trades:\n",
    "    bt_trades.drop('orders', axis=1, inplace=True)    \n",
    "\n",
    "num_real_trades = sql_trades.shape[0]\n",
    "num_bt_trades = bt_trades.shape[0]\n",
    "\n",
    "if (num_real_trades > 0):\n",
    "    print(analyse_strat)\n",
    "## round down open_date to nearest minute\n",
    "compare_start_date = sql_trades.iloc[0]['open_date'].floor('T')\n",
    "compare_end_date = sql_trades.iloc[len(sql_trades)-1]['open_date'].floor('T')\n",
    "bt_end_date = bt_trades.iloc[len(bt_trades)-1]['open_date'].floor('T')\n",
    "\n",
    "bt_trades.rename(columns = {'profit_ratio':'close_profit','profit_abs':'close_profit_abs'}, inplace = True)\n",
    "\n",
    "bt_trades = bt_trades.loc[(bt_trades['open_date'] >= compare_start_date) & (bt_trades['open_date'] <= compare_end_date)]\n",
    "sql_trades = sql_trades.loc[(sql_trades['open_date'] <= bt_end_date)]\n",
    "\n",
    "print(\"REAL\\n\", tabulate(sql_trades, headers='keys', tablefmt='psql', showindex=True))\n",
    "print(\"BT\\n\", tabulate(bt_trades[sel_cols.split(\",\")], headers='keys', tablefmt='psql', showindex=True))\n",
    "\n",
    "merged_df = pd.merge(sql_trades, bt_trades, how ='outer', on =['pair', 'open_date'], suffixes=('_sql', '_bt')).sort_values(by='open_date')\n",
    "\n",
    "# rebuild df from all signal pairs\n",
    "alldf = pd.DataFrame()\n",
    "for pair, sig_df in bt_signals.items(): # pairs\n",
    "    dropped_sig_df = sig_df.reset_index(drop=True, inplace=False)\n",
    "    dropped_sig_df = dropped_sig_df.loc[(dropped_sig_df['open_date'] >= compare_start_date) & (dropped_sig_df['open_date'] <= compare_end_date) & (dropped_sig_df['open_date'] <= bt_end_date)]            \n",
    "    alldf = pd.concat([alldf, dropped_sig_df])\n",
    "    \n",
    "num_all_trades = merged_df.shape[0]\n",
    "\n",
    "merged_df['time_pair_match'] = np.where(\n",
    "    (merged_df[\"close_profit_bt\"].isnull() | merged_df[\"close_profit_sql\"].isnull()),\n",
    "    False,\n",
    "    True\n",
    ")\n",
    "\n",
    "merged_df.loc[(merged_df['time_pair_match'] == True) & merged_df[\"enter_tag_sql\"].notna() & merged_df[\"enter_tag_bt\"].notna(), ['side']] = \"<>\"\n",
    "merged_df.loc[(merged_df['time_pair_match'] == False) & merged_df[\"enter_tag_sql\"].isna() & merged_df[\"enter_tag_bt\"].notna(), ['side']] = \"B<\"\n",
    "merged_df.loc[(merged_df['time_pair_match'] == False) & merged_df[\"enter_tag_sql\"].notna() & merged_df[\"enter_tag_bt\"].isna(), ['side']] = \">R\"\n",
    "\n",
    "both_df = merged_df.loc[(merged_df['side'] == \"<>\")]\n",
    "bt_only = merged_df.loc[(merged_df['side'] == \"B<\")]\n",
    "sql_only = merged_df.loc[(merged_df['side'] == \">R\")]\n",
    "\n",
    "num_match = both_df.shape[0]\n",
    "num_sql_only = sql_only.shape[0]\n",
    "num_bt_only = bt_only.shape[0]\n",
    "\n",
    "sum_profit_match_sql = round(both_df['close_profit_abs_sql'].sum(), 2)\n",
    "sum_profit_pct_match_sql = round(both_df['close_profit_sql'].sum(), 2)\n",
    "\n",
    "sum_profit_match_bt = round(both_df['close_profit_abs_bt'].sum(), 2)\n",
    "sum_profit_pct_match_bt = round(both_df['close_profit_bt'].sum(), 2)\n",
    "\n",
    "sum_profit_sql_only = round(sql_only['close_profit_abs_sql'].sum(), 2)\n",
    "sum_profit_pct_sql_only = round(sql_only['close_profit_sql'].sum(), 2)\n",
    "\n",
    "sum_profit_bt_only = round(bt_only['close_profit_abs_bt'].sum(), 2)\n",
    "sum_profit_pct_bt_only = round(bt_only['close_profit_bt'].sum(), 2)\n",
    "\n",
    "merged_df['entry_match'] = np.where(\n",
    "    (merged_df[\"enter_tag_sql\"] == merged_df[\"enter_tag_bt\"]),\n",
    "    True,\n",
    "    False\n",
    ")\n",
    "\n",
    "merged_df['exit_match'] = np.where(\n",
    "    (merged_df[\"exit_reason_sql\"] == merged_df[\"exit_reason_bt\"]),\n",
    "    True,\n",
    "    False\n",
    ")\n",
    "\n",
    "merged_df['good_entry_price'] = np.where(\n",
    "    (merged_df[\"open_rate_sql\"] <= merged_df[\"open_rate_bt\"]),\n",
    "    True,\n",
    "    False\n",
    ")\n",
    "    \n",
    "merged_df['no_entry_lag'] = np.where(\n",
    "    ((merged_df[\"real_open_date\"] - merged_df[\"open_date\"]) < timedelta(seconds=60)),\n",
    "    True,\n",
    "    False\n",
    ")\n",
    "\n",
    "merged_df['close_profit_pct_diff'] = round(abs(merged_df['close_profit_sql'] - merged_df['close_profit_bt'])*100, 5)\n",
    "merged_df['close_rate_pct_diff'] = round((merged_df['close_rate_sql'] - merged_df['close_rate_bt'])/merged_df['open_rate_sql']*100, 5)\n",
    "merged_df['open_rate_pct_diff'] = round((merged_df['open_rate_sql'] - merged_df['open_rate_bt'])/merged_df['open_rate_sql']*100, 5)        \n",
    "\n",
    "print(merged_df[['pair','open_date','side','time_pair_match','entry_match','exit_match','no_entry_lag','good_entry_price','open_rate_pct_diff','close_rate_pct_diff']])\n",
    "\n",
    "# ------------\n",
    "\n",
    "print(\"\")\n",
    "print(f\"Avg difference in closed profit : {round(merged_df['close_profit_pct_diff'].mean(), 2)}% \")\n",
    "print(f\"Absolute profit (real vs bt)    : {round(merged_df['close_profit_abs_sql'].sum(), 2)} vs {round(merged_df['close_profit_abs_bt'].sum(), 2)}\")\n",
    "print(f\"Pct profit (real vs bt)         : {round(merged_df['close_profit_sql'].sum(), 2)} vs {round(merged_df['close_profit_bt'].sum(), 2)}\")\n",
    "print(\"\")\n",
    "\n",
    "print(f\"Match count                     : {num_match} / {num_all_trades}\")\n",
    "print(f\"Match profit pct (real vs bt)   : {sum_profit_pct_match_sql} vs {sum_profit_pct_match_bt}\")\n",
    "print(f\"Match abs profit (real vs bt)   : {sum_profit_match_sql} vs {sum_profit_match_bt}\")\n",
    "print(\"\")\n",
    "\n",
    "print(f\"Num real only                   : {num_sql_only} / {num_all_trades}\")\n",
    "print(f\"Abs Profit real only            : {sum_profit_sql_only}\")\n",
    "print(f\"Pct Profit real only            : {sum_profit_pct_sql_only}\")\n",
    "print(\"\")\n",
    "\n",
    "print(f\"Num backtest only               : {num_bt_only} / {num_all_trades}\")\n",
    "print(f\"Abs Profit backtest only        : {sum_profit_bt_only}\")\n",
    "print(f\"Pct Profit backtest only        : {sum_profit_pct_bt_only}\")\n",
    "print(\"\")\n",
    "\n",
    "num_perfect_matches = merged_df.loc[(merged_df['time_pair_match'] & merged_df['entry_match'] & merged_df['exit_match'] & merged_df['no_entry_lag'] & merged_df['good_entry_price'])].shape[0]\n",
    "print(f\"Perfect trades                  : {round(100 - (((num_all_trades-num_perfect_matches)/num_all_trades) * 100), 2)}%\")\n",
    "\n",
    "num_very_good_matches = merged_df.loc[(merged_df['time_pair_match'] & merged_df['entry_match'] & merged_df['no_entry_lag'] & merged_df['good_entry_price'])].shape[0]\n",
    "print(f\"Very good trades and above      : {round(100 - (((num_all_trades-num_very_good_matches)/num_all_trades) * 100), 2)}%\")\n",
    "\n",
    "num_good_matches = merged_df.loc[(merged_df['time_pair_match'] & merged_df['no_entry_lag'] & (merged_df['good_entry_price'] | (merged_df['open_rate_pct_diff'] < 0.1)))].shape[0]\n",
    "print(f\"Good trades and above           : {round(100 - (((num_all_trades-num_good_matches)/num_all_trades) * 100), 2)}%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Backtesting Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "enter_tags=\"all\"\n",
    "# enter_tags=\"your_buy_tag\"\n",
    "\n",
    "exit_tags=\"all\"\n",
    "# exit_tags=\"trailing_stop_loss,stop_loss\"\n",
    "# exit_tags=\"your_sell_tag_a,your_sell_tag_b\"\n",
    "\n",
    "il=\"open_date,close_date,close,high,low,profit_abs,profit_ratio\"\n",
    "\n",
    "for sr in atd_results.keys():\n",
    "    print(sr)\n",
    "    prepped_results = eea.prepare_results(atd, sr, enter_tags.split(\",\"), exit_tags.split(\",\"), timerange=TimeRange.parse_timerange(single_tr))\n",
    "    eea.print_results(prepped_results, [\"0\",\"1\",\"2\",\"5\"], il.split(\",\"), \".\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Plotting\n",
    "\n",
    "**Set a small plot_tr timerange if your benchmark timerange was large**\n",
    "\n",
    "Usually 2-3 months maximum per pair is OK, anything longer or with more pairs gets slooooow."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot the same timerange as the test strategy\n",
    "plot_tr = single_tr\n",
    "\n",
    "# uncomment to specify a timerange that is the same as or smaller than the benchmark or test timerange\n",
    "# plot_tr = \"20221101-20221122\"\n",
    "\n",
    "# uncomment to set timerange to None for full benchmark strategy candle timerange - not recommended\n",
    "# plot_tr = None\n",
    "\n",
    "# set the pairs you want to plot\n",
    "# for longer timeranges, consider only plotting one pair at a time\n",
    "pairs_to_plot = [\"ETH\"]\n",
    "\n",
    "\n",
    "## do not edit. used for plotting.\n",
    "if plot_tr is not None:\n",
    "    dfs = plot_tr.split(\"-\")[0]\n",
    "    dfe = plot_tr.split(\"-\")[1]\n",
    "else:\n",
    "    dfs = single_tr.split(\"-\")[0]\n",
    "    dfe = single_tr.split(\"-\")[1]\n",
    "\n",
    "# set plot output dimensions\n",
    "width=2120\n",
    "height=2000\n",
    "\n",
    "plot_config = {\n",
    "    'main_plot': {\n",
    "        'ema_8': {'color': 'gold'},\n",
    "        'ema_20': {'color': 'darkgoldenrod'},\n",
    "        #'ema_34': {'color': 'rgba(0, 255, 0, 0.8)'},\n",
    "        #'ema_50': {'color': 'rgba(0, 255, 0, 1)'},\n",
    "    },\n",
    "    'subplots': {\n",
    "        \"ATR\": {\n",
    "            'ATR': {'color': 'red'},\n",
    "        },        \n",
    "        \"RSI\": {\n",
    "            'rsi': {'color': 'pink'},\n",
    "        },\n",
    "        \"MFI\": {\n",
    "            'mfi': {'color': 'fuchsia'},\n",
    "        },\n",
    "    },    \n",
    "}\n",
    "\n",
    "# if true don't print both benchmark and strategy plots for each pair\n",
    "no_benchmark_plots = True\n",
    "\n",
    "# load candles to plot\n",
    "full_candles = load_candles(pairlist, plot_tr, data_location, data_format=data_format, candle_type=trading_mode)\n",
    "strat_name = strat_config['strategy']\n",
    "\n",
    "if plot_tr is not None:\n",
    "    all_candles = full_candles\n",
    "    strat_trades = trade_dict[strat_name]\n",
    "    strat_trades = strat_trades.loc[(strat_trades['open_date'] > dfs) & (strat_trades['open_date'] < dfe)]\n",
    "else:\n",
    "    ## print all - NOT RECOMMENDED WITH LONG TIMERANGES\n",
    "    all_candles = full_candles\n",
    "    strat_trades = trade_dict[strat_name]\n",
    "\n",
    "# only plot trades with certain buy_tags or sell_tags\n",
    "buy_tags = None\n",
    "sell_tags = None\n",
    "\n",
    "if enter_tags != \"all\" and enter_tags != None:\n",
    "    buy_tags = enter_tags.split(\",\")\n",
    "\n",
    "if exit_tags != \"all\" and exit_tags != None:\n",
    "    sell_tags = exit_tags.split(\",\")\n",
    "\n",
    "if no_benchmark_plots:\n",
    "    ft_config['strategy'] = strat_name\n",
    "    ft_exchange = ExchangeResolver.load_exchange(config=ft_config, validate=False)\n",
    "    ft_pairlists = PairListManager(ft_exchange, ft_config)\n",
    "    ft_dataprovider = DataProvider(ft_config, ft_exchange, ft_pairlists)\n",
    "    \n",
    "    # Load strategy using values set above\n",
    "    strategy = StrategyResolver.load_strategy(ft_config)\n",
    "    strategy.dp = ft_dataprovider\n",
    "\n",
    "    try:\n",
    "        ## don't do this for more than a few pairs and for a few days otherwise Slowness Will Occur\n",
    "        for coin in pairs_to_plot:\n",
    "            pair = f\"{coin}/{stake}\"\n",
    "            print(f\"[{strat_name}] Loaded \" + str(len(all_candles[pair])) + f\" rows of {ft_config['trading_mode']} data for {pair} from {data_location}\")\n",
    "            analysed_candles = strategy.analyze_ticker(all_candles[pair], {'pair': f\"{pair}\"})\n",
    "            analysed_candles = analysed_candles.set_index('date', drop=False)\n",
    "            do_plot(f\"{pair}\", analysed_candles, strat_trades, dfs, dfe, plot_config=plot_config, buy_tags=buy_tags, sell_tags=sell_tags, width=width, height=height)\n",
    "    except Exception as e:\n",
    "        traceback.print_exc(*sys.exc_info())\n",
    "        print(\"You got frogged: \", e)       \n",
    "    \n",
    "else:\n",
    "    for strat_name in stratnames:\n",
    "        ft_config['strategy'] = strat_name\n",
    "        ft_config['timeframe_detail'] = timeframe_detail\n",
    "        \n",
    "        if ft_config.get('strategy') and short:\n",
    "            ft_config['trading_mode'] = 'futures'\n",
    "            ft_config['candle_type_def'] = CandleType.FUTURES\n",
    "            ft_config['margin_mode'] = \"isolated\"\n",
    "            ft_config['dataformat_ohlcv'] = data_format\n",
    "\n",
    "        ft_exchange = ExchangeResolver.load_exchange(ft_config['exchange']['name'], config=ft_config, validate=False)\n",
    "        ft_pairlists = PairListManager(ft_exchange, ft_config)\n",
    "        ft_dataprovider = DataProvider(ft_config, ft_exchange, ft_pairlists)        \n",
    "            \n",
    "        # Load strategy using values set above\n",
    "        strategy = StrategyResolver.load_strategy(ft_config)\n",
    "        strategy.dp = ft_dataprovider\n",
    "\n",
    "        try:\n",
    "            ## don't do this for more than a few pairs and for a few days otherwise Slowness Will Occur\n",
    "            for coin in pairs_to_plot:\n",
    "                pair = f\"{coin}/{stake}\"\n",
    "                print(f\"[{strat_name}] Loaded \" + str(len(all_candles[pair])) + f\" rows of {ft_config['trading_mode']} data for {pair} from {data_location}\")\n",
    "                analysed_candles = strategy.analyze_ticker(all_candles[pair], {'pair': f\"{pair}\"})\n",
    "                analysed_candles = analysed_candles.set_index('date', drop=False)\n",
    "                do_plot(f\"{pair}\", analysed_candles, trade_dict[strat_name], dfs, dfe, plot_config=plot_config, buy_tags=buy_tags, sell_tags=sell_tags)\n",
    "        except Exception as e:\n",
    "            traceback.print_exc(*sys.exc_info())\n",
    "            print(\"You got frogged: \", e)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "922be7b7f1c27c6936dcdeebb74a751540d129aa0bd142078e48e7b5d552e54d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}