dual.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://zhuanlan.zhihu.com/p/90487361\n",
    "# https://www.quantconnect.com/research/15258/dual-thrust-trading-algorithm/p1\n",
    "\n",
    "import duckdb\n",
    "con = duckdb.connect(':memory:')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<duckdb.duckdb.DuckDBPyConnection at 0x10a673c30>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "CREATE TABLE market_data AS\n",
    "FROM 'bitcoin_2024-01-01_2024-09-27.csv' t\n",
    "\"\"\"\n",
    "con.execute(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────┬────────────┬──────────┬──────────┬──────────┬──────────┬────────────────────┬───────────────────┐\n",
       "│   Start    │    End     │   Open   │   High   │   Low    │  Close   │       Volume       │    Market Cap     │\n",
       "│    date    │    date    │  double  │  double  │  double  │  double  │       double       │      double       │\n",
       "├────────────┼────────────┼──────────┼──────────┼──────────┼──────────┼────────────────────┼───────────────────┤\n",
       "│ 2024-01-01 │ 2024-01-02 │ 42276.34 │ 44122.03 │ 42227.99 │ 44122.03 │  25446134905.26736 │ 838250699610.9688 │\n",
       "│ 2024-01-02 │ 2024-01-03 │  44179.5 │ 45846.21 │  44179.5 │ 44957.63 │  46593616092.15278 │ 885906624909.5868 │\n",
       "│ 2024-01-03 │ 2024-01-04 │ 44944.83 │ 45474.95 │ 40931.46 │ 42818.03 │  99124375067.69098 │ 860210389676.8438 │\n",
       "│ 2024-01-04 │ 2024-01-05 │ 42845.26 │ 44664.55 │ 42635.44 │ 44188.15 │ 163905349005.13196 │ 851366855527.3577 │\n",
       "│ 2024-01-05 │ 2024-01-06 │ 44168.03 │ 44229.51 │ 43142.74 │ 44112.11 │  142446432011.0521 │  857139251515.882 │\n",
       "│ 2024-01-06 │ 2024-01-07 │ 44145.12 │ 44180.03 │ 43454.05 │ 43941.78 │ 116258879408.92361 │ 858402098110.8472 │\n",
       "│ 2024-01-07 │ 2024-01-08 │ 43966.93 │ 44442.83 │  43625.9 │ 43850.46 │  96328651324.82639 │ 863400987919.2222 │\n",
       "│ 2024-01-08 │ 2024-01-09 │ 43923.68 │ 47200.03 │ 43260.51 │ 46900.01 │     122271995738.0 │ 878477896044.7153 │\n",
       "│ 2024-01-09 │ 2024-01-10 │ 46951.07 │ 47842.37 │ 45412.61 │ 46082.08 │ 111771707928.25694 │ 913768700835.1875 │\n",
       "│ 2024-01-10 │ 2024-01-11 │ 46106.95 │ 47629.98 │ 44632.42 │ 46564.21 │  62473093954.77778 │ 897575436530.2361 │\n",
       "├────────────┴────────────┴──────────┴──────────┴──────────┴──────────┴────────────────────┴───────────────────┤\n",
       "│ 10 rows                                                                                            8 columns │\n",
       "└──────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "select t.* \n",
    "from market_data t order by Start limit 10\n",
    "\"\"\"\n",
    "con.sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 5\n",
    "K1 = 0.2\n",
    "K2 = 0.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌────────────┬────────────┬──────────┬──────────┬──────────┬──────────┬────────────────────┬────────────────────┬──────────┬──────────┬──────────┬──────────┬────────────────────┬──────────┬────────────────────┬────────────────────┐\n",
       "│   Start    │    End     │   Open   │   High   │   Low    │  Close   │       Volume       │     Market Cap     │    HH    │    HC    │    LC    │    LL    │       range        │   Open   │    buy_trigger     │    sell_trigger    │\n",
       "│    date    │    date    │  double  │  double  │  double  │  double  │       double       │       double       │  double  │  double  │  double  │  double  │       double       │  double  │       double       │       double       │\n",
       "├────────────┼────────────┼──────────┼──────────┼──────────┼──────────┼────────────────────┼────────────────────┼──────────┼──────────┼──────────┼──────────┼────────────────────┼──────────┼────────────────────┼────────────────────┤\n",
       "│ 2024-09-26 │ 2024-09-27 │ 63140.63 │ 65616.04 │ 62792.05 │ 65142.08 │ 103723189296.06621 │ 1270017035677.3135 │ 65616.04 │ 65142.08 │ 63165.61 │ 62957.27 │  2450.429999999993 │ 63140.63 │          63441.498 │          62689.328 │\n",
       "│ 2024-09-25 │ 2024-09-26 │ 64208.02 │ 64669.95 │ 62957.27 │ 63165.61 │ 103767866644.80139 │   1259728792995.54 │ 64669.95 │ 64335.62 │ 63165.61 │ 62957.27 │ 1504.3399999999965 │ 64208.02 │          64516.606 │ 63745.140999999996 │\n",
       "│ 2024-09-24 │ 2024-09-25 │  63346.7 │ 64587.13 │ 62752.04 │ 64335.62 │ 103073420384.87805 │ 1254924424630.1917 │ 64644.96 │ 64335.62 │ 63102.03 │ 62842.02 │ 1542.9300000000003 │  63346.7 │  63684.89599999999 │ 62839.405999999995 │\n",
       "│ 2024-09-23 │ 2024-09-24 │ 63467.95 │ 64644.96 │ 62842.02 │ 63332.69 │ 107862272444.62021 │ 1255023646422.6865 │ 64644.96 │ 63575.97 │ 62953.98 │ 62842.02 │  1690.979999999996 │ 63467.95 │          63988.914 │          62686.504 │\n",
       "│ 2024-09-22 │ 2024-09-23 │ 63359.99 │ 63868.07 │ 62476.04 │ 63575.97 │    84841750994.446 │ 1243803840310.3623 │ 64053.96 │ 63575.97 │ 61449.14 │ 62823.98 │ 2604.8199999999997 │ 63359.99 │ 64110.081999999995 │          62234.852 │\n",
       "│ 2024-09-21 │ 2024-09-22 │  63112.9 │ 63519.41 │ 62823.98 │ 63389.92 │   94051059382.0453 │ 1246516946650.5818 │ 64053.96 │ 63389.92 │  60303.5 │ 62823.98 │  3750.459999999999 │  63112.9 │          64277.852 │          61365.472 │\n",
       "│ 2024-09-20 │ 2024-09-21 │ 62935.88 │ 64053.96 │ 62400.04 │ 63102.03 │ 115934019619.80139 │ 1248769660324.1533 │ 64053.96 │ 63102.03 │  58229.2 │ 62400.04 │  5824.760000000002 │ 62935.88 │          64054.024 │          61258.664 │\n",
       "│ 2024-09-19 │ 2024-09-20 │ 61831.96 │ 63819.92 │  61592.0 │ 62953.98 │ 127035448554.81534 │ 1237744694233.0417 │ 63819.92 │ 62953.98 │  58229.2 │  61592.0 │  5590.720000000001 │ 61831.96 │          62475.948 │ 60865.977999999996 │\n",
       "│ 2024-09-18 │ 2024-09-19 │ 60313.98 │ 61449.14 │ 59216.01 │ 61449.14 │ 110043990808.47038 │ 1188172297080.9094 │ 61449.14 │ 61449.14 │  58229.2 │ 59530.84 │ 3219.9400000000023 │ 60313.98 │ 60920.138000000006 │          59404.743 │\n",
       "│ 2024-09-17 │ 2024-09-18 │ 58267.94 │ 61259.99 │  57620.0 │  60303.5 │ 101668766635.31708 │ 1172633447284.5715 │ 61259.99 │ 60575.47 │  58229.2 │ 59530.84 │  3030.790000000001 │ 58267.94 │          58763.512 │          57524.582 │\n",
       "│     ·      │     ·      │     ·    │     ·    │     ·    │     ·    │          ·         │          ·         │     ·    │     ·    │     ·    │     ·    │          ·         │     ·    │              ·     │              ·     │\n",
       "│     ·      │     ·      │     ·    │     ·    │     ·    │     ·    │          ·         │          ·         │     ·    │     ·    │     ·    │     ·    │          ·         │     ·    │              ·     │              ·     │\n",
       "│     ·      │     ·      │     ·    │     ·    │     ·    │     ·    │          ·         │          ·         │     ·    │     ·    │     ·    │     ·    │          ·         │     ·    │              ·     │              ·     │\n",
       "│ 2024-01-10 │ 2024-01-11 │ 46106.95 │ 47629.98 │ 44632.42 │ 46564.21 │  62473093954.77778 │  897575436530.2361 │ 47842.37 │ 46900.01 │ 43850.46 │ 45412.61 │ 3991.9100000000035 │ 46106.95 │ 46905.331999999995 │  44909.37699999999 │\n",
       "│ 2024-01-09 │ 2024-01-10 │ 46951.07 │ 47842.37 │ 45412.61 │ 46082.08 │ 111771707928.25694 │  913768700835.1875 │ 47842.37 │ 46900.01 │ 43850.46 │ 45412.61 │ 3991.9100000000035 │ 46951.07 │          47620.984 │          45946.199 │\n",
       "│ 2024-01-08 │ 2024-01-09 │ 43923.68 │ 47200.03 │ 43260.51 │ 46900.01 │     122271995738.0 │  878477896044.7153 │ 47200.03 │ 46900.01 │ 43850.46 │  43625.9 │ 3349.5699999999997 │ 43923.68 │          44455.064 │          43126.604 │\n",
       "│ 2024-01-07 │ 2024-01-08 │ 43966.93 │ 44442.83 │  43625.9 │ 43850.46 │  96328651324.82639 │  863400987919.2222 │ 45474.95 │ 44188.15 │ 42818.03 │  43625.9 │ 2656.9199999999983 │ 43966.93 │          44572.566 │          43058.476 │\n",
       "│ 2024-01-06 │ 2024-01-07 │ 44145.12 │ 44180.03 │ 43454.05 │ 43941.78 │ 116258879408.92361 │  858402098110.8472 │ 45846.21 │ 44957.63 │ 42818.03 │  44179.5 │ 3028.1800000000003 │ 44145.12 │          44750.756 │ 43236.666000000005 │\n",
       "│ 2024-01-05 │ 2024-01-06 │ 44168.03 │ 44229.51 │ 43142.74 │ 44112.11 │  142446432011.0521 │   857139251515.882 │ 45846.21 │ 44957.63 │ 42818.03 │  44179.5 │ 3028.1800000000003 │ 44168.03 │          44773.666 │          43259.576 │\n",
       "│ 2024-01-04 │ 2024-01-05 │ 42845.26 │ 44664.55 │ 42635.44 │ 44188.15 │ 163905349005.13196 │  851366855527.3577 │ 45846.21 │ 44957.63 │ 42818.03 │  44179.5 │ 3028.1800000000003 │ 42845.26 │          43450.896 │ 41936.806000000004 │\n",
       "│ 2024-01-03 │ 2024-01-04 │ 44944.83 │ 45474.95 │ 40931.46 │ 42818.03 │  99124375067.69098 │  860210389676.8438 │ 45846.21 │ 44957.63 │ 42818.03 │  44179.5 │ 3028.1800000000003 │ 44944.83 │ 45289.666000000005 │          44427.576 │\n",
       "│ 2024-01-02 │ 2024-01-03 │  44179.5 │ 45846.21 │  44179.5 │ 44957.63 │  46593616092.15278 │  885906624909.5868 │ 45846.21 │ 44957.63 │ 44122.03 │  44179.5 │ 1724.1800000000003 │  44179.5 │          44558.308 │          43611.288 │\n",
       "│ 2024-01-01 │ 2024-01-02 │ 42276.34 │ 44122.03 │ 42227.99 │ 44122.03 │  25446134905.26736 │  838250699610.9688 │ 44122.03 │ 44122.03 │ 44122.03 │ 42227.99 │ 1894.0400000000009 │ 42276.34 │               NULL │               NULL │\n",
       "├────────────┴────────────┴──────────┴──────────┴──────────┴──────────┴────────────────────┴────────────────────┴──────────┴──────────┴──────────┴──────────┴────────────────────┴──────────┴────────────────────┴────────────────────┤\n",
       "│ 270 rows (20 shown)                                                                                                                                                                                                      16 columns │\n",
       "└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "WITH dual_thrust AS (\n",
    "    SELECT *, IF(HH - LC > HC - LL, HH - LC, HC - LL) as range\n",
    "    FROM(\n",
    "        SELECT\n",
    "            *,\n",
    "            max(High) OVER (ORDER BY Start ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS HH,\n",
    "            max(Close) OVER (ORDER BY Start ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS HC,\n",
    "            min(Close) OVER (ORDER BY Start ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS LC,\n",
    "            max(Low) OVER (ORDER BY Start ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS LL\n",
    "        FROM\n",
    "            market_data\n",
    "    )\n",
    ")\n",
    "SELECT *, Open, Open + lead(0.2 * range) OVER validity as buy_trigger, Open - lead(0.3 * range) OVER validity as sell_trigger\n",
    "FROM dual_thrust\n",
    "WINDOW validity AS (ORDER BY Start desc)\n",
    "ORDER BY Start desc\n",
    "\"\"\"\n",
    "con.sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "con.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}