RollingBacktestNotebook.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FreqTrade backtesting analysis and plotting notebook\n",
    "\n",
    "Version: v1.2\n",
    "\n",
    "Date: 2023-10-21\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",
    "### Plotting Frameworks\n",
    "\n",
    "Since v1.1, this notebook supports plotting using bokeh as well as plotly. bokeh has some major benefits in that many of\n",
    "the glyphs are rendered using webgl, improving speed greatly. However, the library is immature and more prone to change\n",
    "than plotly.\n",
    "\n",
    "To use bokeh, you don't need to change anything.\n",
    "\n",
    "To use plotly, in the final plotting cell, change the line to read `plotter = PlotHelper(plotlib=\"plotly\").plotter`\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."
   ]
  },
  {
   "attachments": {},
   "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 notebook_helper\n",
    "notebook_helper.setup()\n",
    "\n",
    "from plot_helper import PlotHelper\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",
    "display(HTML(\"<style>:root { --jp-notebook-max-width: 100% !important; }</style>\"))\n",
    "display(HTML(\"<style>.output { overflow: hidden; }</style>\"))\n",
    "\n",
    "import warnings\n",
    "warnings.simplefilter(action='ignore', category=pd.errors.PerformanceWarning)\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## main configuration for everything ##\n",
    "\n",
    "# main freqtrade dir\n",
    "# use '.' if you copy the notebook files into your ft dir\n",
    "# use an absolute path, e.g. `/path/to/freqtrade` if you don't\n",
    "# NB if you use an absolute path here, you need to set the `user_dir` and `strategy_path` in your config to absolute too\n",
    "freqtrade_dir = \".\"\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 freqtrade_dir\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",
    "# futures?\n",
    "futures = False\n",
    "\n",
    "# set to true if you want to open all slots to test buys without rejections\n",
    "reject_test = False\n",
    "\n",
    "# protections?\n",
    "enable_protections = True\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 base config file and any separate pairlist files\n",
    "config_files = [f\"{freqtrade_dir}/your_config.json\"]\n",
    "pairlist_files = []\n",
    "\n",
    "# load configs\n",
    "config_files.extend(pairlist_files)\n",
    "ft_config = Configuration.from_files(files=config_files)\n",
    "\n",
    "# do final config checks\n",
    "if \"exchange\" not in ft_config:\n",
    "    raise Exception(\"No exchange object found in your config/pairlist file(s)\")\n",
    "\n",
    "pairlist = ft_config[\"exchange\"].get(\"pair_whitelist\", None)\n",
    "pair_count = len(pairlist)\n",
    "\n",
    "if pair_count == 0:\n",
    "    raise Exception(\"No pairs in pairlist. Check your config/pairlist files\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "config_overrides = {}\n",
    "\n",
    "## set any config file / strategy overrides below\n",
    "\n",
    "# override stake currency\n",
    "# config_overrides['stake_currency'] = \"USDT\"\n",
    "\n",
    "# override stake amount\n",
    "# config_overrides['stake_amount'] = 400\n",
    "\n",
    "# override max_open_trades\n",
    "# config_overrides['max_open_trades'] = 5\n",
    "\n",
    "# override data format, default \"feather\"\n",
    "# config_overrides['data_format'] = \"json\"\n",
    "\n",
    "# override path to downloaded data\n",
    "# config_overrides['datadir'] = f\"/path/to/data/{ft_config['exchange']['name']}\"\n",
    "\n",
    "# override strategy stoploss\n",
    "# config_overrides['stoploss'] = -0.99\n",
    "\n",
    "# override strategy minimal roi\n",
    "# config_overrides['minimal_roi'] = { '0': 10 }\n",
    "\n",
    "# set or override timeframe-detail\n",
    "# config_overrides['timeframe_detail'] = \"1m\"\n",
    "\n",
    "#### DO NOT EDIT BELOW HERE\n",
    "\n",
    "stake_currency = config_overrides.get('stake_currency', ft_config['stake_currency'])\n",
    "stake_amount = config_overrides.get('stake_amount', ft_config['stake_amount'])\n",
    "max_open_trades = config_overrides.get('max_open_trades', ft_config['max_open_trades'])\n",
    "dry_run_wallet = config_overrides.get('dry_run_wallet', ft_config['dry_run_wallet'])\n",
    "datadir = config_overrides.get('datadir', ft_config['datadir'] if \"datadir\" in ft_config else f\"{freqtrade_dir}/user_data/data\")\n",
    "data_format = config_overrides.get('dataformat_ohlcv', ft_config['dataformat_ohlcv'] if \"dataformat_ohlcv\" in ft_config else \"feather\")\n",
    "timeframe_detail = config_overrides.get('timeframe_detail', ft_config['timeframe_detail'] if \"timeframe_detail\" in ft_config else None)\n",
    "\n",
    "if reject_test:\n",
    "    max_open_trades = -1\n",
    "    dry_run_wallet = pair_count * stake_amount\n",
    "\n",
    "trading_mode = CandleType.SPOT\n",
    "stake = f\"{stake_currency}\"\n",
    "\n",
    "if futures:\n",
    "    trading_mode = CandleType.FUTURES\n",
    "    stake = f\"{stake_currency}:{stake_currency}\"\n",
    "    config_overrides['trading_mode'] = \"futures\"\n",
    "    config_overrides['margin_mode'] = \"isolated\"\n",
    "\n",
    "final_config_files = config_files + [\n",
    "    deep_merge_dicts({\n",
    "        \"export\": \"signals\",\n",
    "        \"enable_protections\": enable_protections,\n",
    "        \"dataformat_ohlcv\": data_format,\n",
    "        \"datadir\": str(datadir),\n",
    "        \"dry_run_wallet\": dry_run_wallet,\n",
    "        'max_open_trades': max_open_trades,\n",
    "        'stake_amount': stake_amount,\n",
    "        'stake_currency': stake_currency,\n",
    "    }, config_overrides)\n",
    "]\n",
    "\n",
    "bench_config = {\n",
    "    'strategy': bench_strat,\n",
    "    'pair_count': [pair_count],\n",
    "    'config': [\n",
    "        config_files[0],\n",
    "        deep_merge_dicts({\n",
    "        }, config_overrides)\n",
    "    ]\n",
    "}\n",
    "\n",
    "strat_config = {\n",
    "    'strategy': test_strat,\n",
    "    'pair_count': [pair_count],\n",
    "    'config': [\n",
    "        config_files[0],\n",
    "        deep_merge_dicts({\n",
    "        }, config_overrides)\n",
    "    ]\n",
    "}"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Benchmark Strategy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%t\n"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "## set overall full timerange to use for benchmark and test backtests\n",
    "timerange = \"20230601-20230625\"\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",
    "bench_pklf = f\"{freqtrade_dir}/user_data/backtest_results/{bench_config['strategy']}-results_{timerange}-{max_open_trades}_{dry_run_wallet}_{stake_amount}.pkl\"\n",
    "\n",
    "if not os.path.exists(bench_pklf):\n",
    "    print(\"Creating\", bench_pklf)\n",
    "    print(bench_config, str(datadir), 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, 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(bench_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: {bench_pklf}\")"
   ]
  },
  {
   "attachments": {},
   "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": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# run the test strategy for the same timerange as benchmark\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",
    "# pickle test strat backtest. useful if backtesting a large timerange you want to come back to\n",
    "single_pickle = False\n",
    "\n",
    "if os.path.exists(bench_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(bench_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",
    "    single_pklf = f\"{freqtrade_dir}/user_data/backtest_results/{strat_config['strategy']}-results_{single_tr}-{max_open_trades}_{dry_run_wallet}_{stake_amount}.pkl\"\n",
    "    if single_pickle:\n",
    "        if not os.path.exists(single_pklf):\n",
    "            print(f\"Running backtest for {test_strat}\")\n",
    "            start_time = perf_counter()\n",
    "            single_results = notebook_helper.backtest_all(single_config, parallel, proportion_cores_parallel, timeframe_detail=timeframe_detail)\n",
    "            end_time = perf_counter()\n",
    "            print(f\"Elapsed time: {(end_time - start_time)/60}\")\n",
    "\n",
    "            single_pkl_results = []\n",
    "\n",
    "            for rrs in single_results:\n",
    "                for rrss in rrs[1]:\n",
    "                    if strat_config['strategy'] == rrss[\"key\"]:\n",
    "                        single_pkl_results.append(rrs)\n",
    "\n",
    "            sfile = open(single_pklf, \"wb\")\n",
    "            joblib.dump(single_pkl_results, sfile)\n",
    "            sfile.close()\n",
    "        else:\n",
    "            print(f\"Loading backtest for {test_strat}\")\n",
    "            single_results = []\n",
    "            with (open(single_pklf, \"rb\")) as openfile:\n",
    "                while True:\n",
    "                    try:\n",
    "                        single_results.append(joblib.load(openfile))\n",
    "                    except EOFError:\n",
    "                        break\n",
    "            single_results = single_results[0]\n",
    "    else:\n",
    "        print(f\"Running backtest for {test_strat} {single_config[0]['timeranges']}\")\n",
    "        start_time = perf_counter()\n",
    "        single_results = notebook_helper.backtest_all(single_config, parallel, proportion_cores_parallel, 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=f\"{freqtrade_dir}/user_data/notebooks/\"+\"{strategy}-vs-{benchmark}.html\")\n",
    "\n",
    "    br_config = srs[3]\n",
    "    br_comparison = load_strategy_comparison\n",
    "    br_trades = load_strategy_trades\n",
    "    br_candles = load_strategy_signal_candles"
   ]
  },
  {
   "attachments": {},
   "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",
    "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"
   ]
  },
  {
   "attachments": {},
   "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": {
    "scrolled": false
   },
   "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)}%\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Backtesting Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "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,min_rate,max_rate,profit_abs,profit_ratio\"\n",
    "\n",
    "sr = list(atd_results.keys())[1]\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(\",\"), csv_path=\".\", to_csv=False)"
   ]
  },
  {
   "attachments": {},
   "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": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# construct the plotting framework\n",
    "# DEFAULTS TO bokeh + webgl\n",
    "# to revert back to plotly plots use `plotter = PlotHelper(plotlib=\"plotly\").plotter`\n",
    "plotter = PlotHelper().plotter\n",
    "\n",
    "# plot the same timerange as the current 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 = \"20210301-20210401\"\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 = [\"BTC\"]\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=900\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",
    "        # uncomment if using bokeh and want to plot markers on candles\n",
    "        # the indicators used here must be set to 1 in the main freqtrade dataframe\n",
    "        # marker list is: https://docs.bokeh.org/en/2.4.2/docs/reference/models/markers.html#scatter\n",
    "\n",
    "        # 'indicator_a': {'marker':'star', 'color':'orange'},\n",
    "        # 'indicator_b': {'marker':'hex', 'color':'black'},\n",
    "        # 'indicator_c': {'marker':'square_pin', 'color':'fuchsia'},\n",
    "\n",
    "        # uncomment if using bokeh and want to plot annotation spans across candles\n",
    "        # the indicators used here must be set to 1 in the main freqtrade dataframe\n",
    "        # 'indicator_d': {'box': 'orange'},\n",
    "    },\n",
    "    'subplots': {\n",
    "        # uncomment if using bokeh and want to plot the cumulative profit subplot\n",
    "        # \"Cumulative Profit\": {\n",
    "        #     'plot_cumprof': {'color':'black'},\n",
    "        # },\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",
    "plot_pairlist = []\n",
    "for p in pairs_to_plot:\n",
    "    plot_pairlist.append(f\"{p}/{stake}\")\n",
    "\n",
    "print(f'Loading all {data_format} {trading_mode} candle data: {plot_tr} [{datadir}]')\n",
    "full_candles = notebook_helper.load_candles(plot_pairlist, plot_tr, Path(br_config['datadir']), timeframe=br_config['timeframe'], data_format=br_config['dataformat_ohlcv'], 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",
    "    plot_strats = [strat_name]\n",
    "    br_config['timerange'] = single_tr\n",
    "else:\n",
    "    plot_strats = stratnames\n",
    "\n",
    "for strat_name in plot_strats:\n",
    "    br_config['strategy'] = strat_name\n",
    "    ft_exchange = ExchangeResolver.load_exchange(config=br_config, validate=False)\n",
    "    ft_pairlists = PairListManager(ft_exchange, br_config)\n",
    "    ft_dataprovider = DataProvider(br_config, ft_exchange, ft_pairlists)\n",
    "\n",
    "    # Load strategy using values set above\n",
    "    strategy = StrategyResolver.load_strategy(br_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 pair in plot_pairlist:\n",
    "            # pair = f\"{coin}/{stake}\"\n",
    "            print(f\"[{strat_name}] Loaded \" + str(len(all_candles[pair])) + f\" rows of {br_config['trading_mode']} data for {pair} from {datadir}\")\n",
    "            analysed_candles = strategy.analyze_ticker(all_candles[pair], {'pair': f\"{pair}\"})\n",
    "            analysed_candles = analysed_candles.set_index('date', drop=False)\n",
    "\n",
    "            print(\"Plotting...\")\n",
    "            plotter.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)"
   ]
  }
 ],
 "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.8.10"
  },
  "vscode": {
   "interpreter": {
    "hash": "922be7b7f1c27c6936dcdeebb74a751540d129aa0bd142078e48e7b5d552e54d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}