lecture/Zipline Cancel Order.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "44bb5714",
   "metadata": {},
   "source": [
    "<span id=\"menu\"></span>\n",
    "# Zipline Cancel Order 取消訂單"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f69a0567",
   "metadata": {},
   "source": [
    "> - 若演算法受到**滑價（slippage）的成交量限制**或**股票暫停交易**的影響，可能會導致**下單後無法在一個交易日內成交**，尤其遇到**成交量小**的公司或**下單量很大**時，更是有可能要花費10個交易日以上才能完全成交。\n",
    "> - 若是不希望該筆訂單存續過久，可以透過`get_open_orders`與`cancel_order`兩個函數在特定時點**將未完全成交訂單取消**。\n",
    "> \n",
    ">     1. `get_open_orders(asset = None)`：取得未完全成交的訂單資料。\n",
    ">     2. `cancel_order(order)`：取消某筆訂單。\n",
    "> \n",
    "> \n",
    "> \n",
    "> ## 本文件包含以下三個部份：\n",
    ">   1. [函數介紹](#函數介紹)\n",
    ">   2. [範例：交易量限制、暫停交易搭配取消交易](#交易量限制、暫停交易)\n",
    ">   3. [範例：指定特定日期的取消交易搭配stop_price](#指定特定日期的取消交易)\n",
    ">   \n",
    "> ## 閱讀本篇之前請先閱讀以下文章：\n",
    "> 1. [TSMC buy and hold strategy.ipynb](https://github.com/tejtw/TQuant-Lab/blob/main/lecture/TSMC%20buy%20and%20hold%20strategy.ipynb) \n",
    "> \n",
    "> 2. [Zipline Order（order & order_target）.ipynb](https://github.com/tejtw/TQuant-Lab/blob/main/lecture/Zipline%20Order%20(order%20%26%20order_target).ipynb)\n",
    "> \n",
    "> 3. [Zipline Order（value & target_value）.ipynb](https://github.com/tejtw/TQuant-Lab/blob/main/lecture/Zipline%20Order%20(value%20%26%20target_value).ipynb)\n",
    ">\n",
    "> 4. [Zipline Order（percent & target_percent）.ipynb](https://github.com/tejtw/TQuant-Lab/blob/main/lecture/Zipline%20Order%20(percent%20%26%20target_percent).ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8476b529",
   "metadata": {},
   "source": [
    "## 建立環境"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "21194abe",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import tejapi\n",
    "import time\n",
    "import os\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# tej_key\n",
    "tej_key ='your key'\n",
    "tejapi.ApiConfig.api_key = tej_key\n",
    "os.environ['TEJAPI_BASE'] = \"https://api.tej.com.tw\"\n",
    "os.environ['TEJAPI_KEY'] = tej_key\n",
    "\n",
    "# date\n",
    "start='2022-10-15'\n",
    "end='2022-11-05'\n",
    "os.environ['mdate'] = '20221015 20221105'\n",
    "\n",
    "tz = 'UTC'\n",
    "start_dt, end_dt = pd.Timestamp(start, tz = tz), pd.Timestamp(end, tz = tz)\n",
    "\n",
    "# calendar\n",
    "calendar_name='TEJ'\n",
    "\n",
    "# bundle_name\n",
    "bundle_name = 'tquant'\n",
    "\n",
    "# ticker\n",
    "os.environ['ticker'] = \"1216 2330 2327 IR0001 5844\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5aeb6fb9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Merging daily equity files:\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2023-09-25 02:10:50.408398] INFO: zipline.data.bundles.core: Ingesting tquant.\n"
     ]
    }
   ],
   "source": [
    "!zipline ingest -b tquant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "de4f5164",
   "metadata": {},
   "outputs": [],
   "source": [
    "from zipline.api import *\n",
    "from zipline import run_algorithm\n",
    "from zipline.finance import commission, slippage\n",
    "from zipline.utils.calendar_utils import get_calendar\n",
    "\n",
    "from zipline.utils.run_algo import  (get_transaction_detail,\n",
    "                                     get_record_vars)\n",
    "\n",
    "from logbook import Logger, StderrHandler, INFO\n",
    "\n",
    "# 設定log顯示方式\n",
    "log_handler = StderrHandler(format_string='[{record.time:%Y-%m-%d %H:%M:%S.%f}]: ' +\n",
    "                            '{record.level_name}: {record.func_name}: {record.message}',\n",
    "                            level=INFO)\n",
    "log_handler.push_application()\n",
    "log = Logger('Algorithm')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2940ed8",
   "metadata": {},
   "source": [
    "<span id=\"函數介紹\"></span>\n",
    "## 函數介紹\n",
    "[Return to Menu](#menu)\n",
    "\n",
    "  \n",
    "<br>\n",
    "\n",
    "### zipline.api.get_open_orders(asset=None)\n",
    "取得未完全成交的訂單資料\n",
    "\n",
    "**Parameters:**  \n",
    "asset：*zipline.assets.Asset*\n",
    "- 預設為 None，會回傳**所有**未完全成交的訂單資料。\n",
    "- 若提供該股票的`Asset`物件（`zipline.assets.Asset`，例如：Equity(0 [1101])，透過`symbol(\"1101\")`可將 symbol 轉成`Asset`物件），則僅回傳**該股票**的未完全成交訂單資料。\n",
    "\n",
    "\n",
    "**Returns:**  \n",
    "回傳未完全成交的訂單資料，資料型態為`dict[list[Order]]`\n",
    "\n",
    "- 訂單資料為一個字典，key 是 asset 物件，value 是一個 list，list 中有 order 物件（`zipline.finance.order.Order`）。\n",
    "- order物件範例如下：\n",
    "```python\n",
    "Event({'id': '3096bfae27824859a4f9aba7a75fb31a', 'dt': Timestamp('2022-10-17 05:30:00+0000', tz='UTC'), 'reason': None, 'created': Timestamp('2022-10-17 05:30:00+0000', tz='UTC'), 'amount': 1500000, 'filled': 0, 'commission': 0, 'stop': None, 'limit': None, 'stop_reached': False, 'limit_reached': False, 'sid': Equity(1 [1216]), 'status': <ORDER_STATUS.OPEN: 0>})\n",
    "```\n",
    "- order 物件中的屬性，可以用 order.xxx 單獨呼叫，例如：使用 order.id 會回傳該張訂單的 order_id。\n",
    "- 屬性定義參考以下連結中`orders`的說明：[lecture/TSMC buy and hold strategy.ipynb](https://github.com/tejtw/TQuant-Lab/blob/main/lecture/TSMC%20buy%20and%20hold%20strategy.ipynb)。\n",
    "\n",
    "  \n",
    "<br>\n",
    "\n",
    "### zipline.api.cancel_order(order_param)\n",
    "取消某筆訂單。  \n",
    "\n",
    "**Parameters:**  \n",
    "order_param：*str or Order* \n",
    "- 可以使用 **order_id** （`zipline.finance.order.Order.id`）或 **order 物件**（`zipline.finance.order.Order`）來指定欲取消的訂單。\n",
    "- order_id：\n",
    "  - 每張訂單**獨一無二**的 16 進制編碼。\n",
    "  - order 系列函數在下單時的回傳值就是 order_id。\n",
    "- order 物件（`zipline.finance.order.Order`）：\n",
    "  - 可透過`zipline.api.get_open_orders`取得。\n",
    "  - 可將 order_id 傳入`zipline.api.get_order(order_id)`取得。\n",
    "  \n",
    "###  補充說明 \n",
    "- `get_open_orders`與`cancel_order`兩個函數通常在`handle_data`階段使用。\n",
    "- 使用函數前記得先做 import：`from zipline.api import cancel_order, get_open_orders`或`from zipline.api import *`。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9607df3d",
   "metadata": {},
   "source": [
    "<span id=\"交易量限制、暫停交易\"></span>\n",
    "## 範例：交易量限制、暫停交易\n",
    "若訂單在建立後的下一個交易日未能完全成交，希望取消訂單。\n",
    "\n",
    "\n",
    "[Return to Menu](#menu)\n",
    "\n",
    "在以下這個範例，我們設計了 **PART A** 及 **PART B** 兩部分程式碼：\n",
    "\n",
    "- **PART A**\n",
    "    - 在每天交易前利用`get_open_orders`檢查並用`cancel_order`取消還沒成交的訂單。\n",
    "    ```python\n",
    "    def handle_data(context, data):\n",
    "        \n",
    "        open_orders = get_open_orders()\n",
    "\n",
    "        for asset in open_orders:\n",
    "            for o in open_orders[asset]:\n",
    "                cancel_order(o)\n",
    "        ...\n",
    "    ```\n",
    "    - 設定滑價模型：`set_slippage(slippage.VolumeShareSlippage(volume_limit=0.025, price_impact=0.1))`，並限制買賣量最高只能是當天實際成交量的 **2.5%**。\n",
    "    \n",
    "    - 設定交易：\n",
    "      - 在2022/10/17 long 1500000 股的 1216 股票。\n",
    "      - 在2022/10/19 long 1000 股的 2327 股票。\n",
    "      - 在2022/10/20 long 1000 股的 2327 股票。    \n",
    "      - 每天顯示 2327 股票`data.is_stale`的狀態。\n",
    "\n",
    "    ```python\n",
    "    def handle_data(context, data):\n",
    "        ...\n",
    "        print(get_datetime(), symbol('2327'), 'is_stale =', data.is_stale(symbol('2327')))\n",
    "\n",
    "        if context.i == 0: # 2022-10-17\n",
    "            order(symbol('1216'), 1500000)\n",
    "\n",
    "        if context.i == 2: # 2022-10-19\n",
    "            order(symbol('2327'), 1000) \n",
    "\n",
    "        if context.i == 3: # 2022-10-20\n",
    "            order(symbol('2327'), 1000) \n",
    "        ...\n",
    "    ```\n",
    "\n",
    "- **PART B**  \n",
    "在每天**下單後**，檢查 **data.is_stale = True** 的股票，並取消該股票所有的訂單。\n",
    "```python\n",
    "def handle_data(context, data):\n",
    "    \n",
    "    ...\n",
    "    open_orders = get_open_orders()\n",
    "       \n",
    "    for asset in open_orders:\n",
    "        if data.is_stale(asset):\n",
    "            for o in open_orders[asset]:\n",
    "                cancel_order(o)\n",
    "    ...\n",
    "```\n",
    "\n",
    "###  補充說明\n",
    "\n",
    "> ### data.is_stale(assets)：  \n",
    "> - 若該股票未下市、曾經有交易紀錄且當天 volume = 0 則回傳 **True**，否則 **False**。\n",
    "> - 通常當股票當天**暫停交易**或是**成交量為 0** 時會回傳 **True**，這種情況下 zipline 是不會交易的。 \n",
    ">\n",
    "> **Parameters:**  \n",
    "> asset：*zipline.assets.Asset or iterable of zipline.assets.Asset*，該股票的`Asset`物件（例如：Equity(0 [1101])）。\n",
    "> \n",
    "> **Return type:**  \n",
    "> bool or pd.Series[bool]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "abb62ce7",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2023-09-25 02:10:52.690774]: INFO: handle_simulation_end: Simulated 15 trading days\n",
      "first open: 2022-10-17 01:01:00+00:00\n",
      "last close: 2022-11-04 05:30:00+00:00\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-10-17 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-10-18 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-10-19 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-10-20 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-21 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-24 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-25 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-26 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-27 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-28 05:30:00+00:00 Equity(1 [2327]) is_stale = True\n",
      "2022-10-31 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-11-01 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-11-02 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-11-03 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n",
      "2022-11-04 05:30:00+00:00 Equity(1 [2327]) is_stale = False\n"
     ]
    }
   ],
   "source": [
    "def initialize(context):\n",
    "    context.i = 0  \n",
    "    context.tickers = ['1216', '2330', '2327']\n",
    "    context.asset = [symbol(ticker) for ticker in context.tickers]      \n",
    "    set_slippage(slippage. VolumeShareSlippage(volume_limit=0.025, price_impact=0.1))\n",
    "    set_commission(commission.PerDollar(cost = commission_cost))\n",
    "    set_benchmark(symbol('IR0001'))\n",
    "    \n",
    "def handle_data(context, data):\n",
    "    \n",
    "    # PART A---------------------------------------------------------------------------------------\n",
    "    open_orders = get_open_orders()\n",
    "    \n",
    "    for asset in open_orders:\n",
    "        for o in open_orders[asset]:\n",
    "            cancel_order(o)\n",
    "\n",
    "    \n",
    "    print(get_datetime(), symbol('2327'), 'is_stale =', data.is_stale(symbol('2327')))\n",
    "\n",
    "    if context.i == 0: # 2022-10-17\n",
    "        order(symbol('1216'), 1500000)\n",
    "    \n",
    "    if context.i == 2: # 2022-10-19\n",
    "        order(symbol('2327'), 1000) \n",
    "\n",
    "    if context.i == 3: # 2022-10-20\n",
    "        order(symbol('2327'), 1000) \n",
    "     \n",
    "    # PART B---------------------------------------------------------------------------------------\n",
    "    open_orders = get_open_orders()\n",
    "       \n",
    "    for asset in open_orders:\n",
    "        if data.is_stale(asset):\n",
    "            for o in open_orders[asset]:\n",
    "                cancel_order(o)\n",
    "\n",
    "    \n",
    "    record(close=data.current(context.asset, 'close'))\n",
    "    record(volume=data.current(context.asset, 'volume')*1000)\n",
    "    context.i += 1\n",
    "    \n",
    "\n",
    "commission_cost = 0.001425\n",
    "capital_base = 1e8\n",
    "\n",
    "\n",
    "performance = run_algorithm(start=start_dt,\n",
    "                            end=end_dt,\n",
    "                            initialize=initialize,\n",
    "                            handle_data=handle_data,\n",
    "                            capital_base=capital_base,\n",
    "                            trading_calendar=get_calendar(calendar_name),\n",
    "                            bundle=bundle_name)\n",
    "\n",
    "closing_price = tejapi.fastget('TWN/APIPRCD',\n",
    "                               coid=['1216'],\n",
    "                               opts={'columns':['mdate','coid','open_d','close_d','vol']},\n",
    "                               mdate={'gte':start,'lte':end },\n",
    "                               paginate=True)\n",
    "\n",
    "closing_price['vol'] = closing_price['vol'] * 1000\n",
    "\n",
    "positions, transactions, orders = get_transaction_detail(performance)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f10a262c",
   "metadata": {},
   "source": [
    "## 講解說明\n",
    "### 10/17\n",
    "- 在10/17下單 1500 張統一（1216）股票，因為下單量超過10/18總交易量的2.5%，所以**10/18只成交總交易量的2.5%，剩下分批成交**。\n",
    "  - amount = 162350 股 = 6494000 * 2.5%。\n",
    "  - 滑價說明參考：[lecture/Zipline Slippage.ipynb](https://github.com/tejtw/TQuant-Lab/blob/main/lecture/Zipline%20Slippage.ipynb)。\n",
    "- 如果**希望達到10/18限制量就停止成交**，則可使用 **PART A** 程式碼，在**每天交易前檢查並取消還沒成交的訂單**。\n",
    "- 在 orders 資料中的 'status' 可以看到該訂單在買完第10/18的量時就被提前取消（**'status' 由 0 變成 2**）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "48f4230f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mdate</th>\n",
       "      <th>coid</th>\n",
       "      <th>open_d</th>\n",
       "      <th>close_d</th>\n",
       "      <th>vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-18</td>\n",
       "      <td>1216</td>\n",
       "      <td>66.0</td>\n",
       "      <td>65.3</td>\n",
       "      <td>6494000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       mdate  coid  open_d  close_d        vol\n",
       "1 2022-10-18  1216    66.0     65.3  6494000.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# vol：實際成交量，單位為股。\n",
    "closing_price.query('(coid == \"1216\") & (mdate == \"2022-10-18\")')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "86e277dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>id</th>\n",
       "      <th>dt</th>\n",
       "      <th>reason</th>\n",
       "      <th>created</th>\n",
       "      <th>amount</th>\n",
       "      <th>filled</th>\n",
       "      <th>commission</th>\n",
       "      <th>stop</th>\n",
       "      <th>limit</th>\n",
       "      <th>stop_reached</th>\n",
       "      <th>limit_reached</th>\n",
       "      <th>asset</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-17 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>dc9d0e5a7db945dab328108520feabfb</td>\n",
       "      <td>2022-10-17 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-17 13:30:00+08:00</td>\n",
       "      <td>1500000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-18 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>dc9d0e5a7db945dab328108520feabfb</td>\n",
       "      <td>2022-10-18 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-17 13:30:00+08:00</td>\n",
       "      <td>1500000</td>\n",
       "      <td>162350</td>\n",
       "      <td>15108.017567</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           sid symbol                                id  \\\n",
       "2022-10-17 13:30:00+08:00    0   1216  dc9d0e5a7db945dab328108520feabfb   \n",
       "2022-10-18 13:30:00+08:00    0   1216  dc9d0e5a7db945dab328108520feabfb   \n",
       "\n",
       "                                                 dt reason  \\\n",
       "2022-10-17 13:30:00+08:00 2022-10-17 13:30:00+08:00   None   \n",
       "2022-10-18 13:30:00+08:00 2022-10-18 13:30:00+08:00   None   \n",
       "\n",
       "                                            created   amount  filled  \\\n",
       "2022-10-17 13:30:00+08:00 2022-10-17 13:30:00+08:00  1500000       0   \n",
       "2022-10-18 13:30:00+08:00 2022-10-17 13:30:00+08:00  1500000  162350   \n",
       "\n",
       "                             commission  stop limit  stop_reached  \\\n",
       "2022-10-17 13:30:00+08:00      0.000000  None  None         False   \n",
       "2022-10-18 13:30:00+08:00  15108.017567  None  None         False   \n",
       "\n",
       "                           limit_reached             asset  status  \n",
       "2022-10-17 13:30:00+08:00          False  Equity(0 [1216])       0  \n",
       "2022-10-18 13:30:00+08:00          False  Equity(0 [1216])       2  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.query('symbol == \"1216\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4daaad85",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>amount</th>\n",
       "      <th>dt</th>\n",
       "      <th>price</th>\n",
       "      <th>order_id</th>\n",
       "      <th>asset</th>\n",
       "      <th>commission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-18 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>162350</td>\n",
       "      <td>2022-10-18 13:30:00+08:00</td>\n",
       "      <td>65.304081</td>\n",
       "      <td>dc9d0e5a7db945dab328108520feabfb</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           sid symbol  amount                        dt  \\\n",
       "2022-10-18 13:30:00+08:00    0   1216  162350 2022-10-18 13:30:00+08:00   \n",
       "\n",
       "                               price                          order_id  \\\n",
       "2022-10-18 13:30:00+08:00  65.304081  dc9d0e5a7db945dab328108520feabfb   \n",
       "\n",
       "                                      asset commission  \n",
       "2022-10-18 13:30:00+08:00  Equity(0 [1216])       None  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "216a6814",
   "metadata": {},
   "source": [
    "### 10/19\n",
    "- 國巨（2327） 在10/20~10/28暫停交易（因此 is_stale = True），按照原本 zipline 的機制，不管是暫停前一天，或是暫停期間下單，都會在恢復交易當天成交（如果有辦法的話）。\n",
    "- 如果要加入限制機制，可以參考以上程式　PART B：\n",
    "  - 在每天下單後，檢查任何 is_stale = True 的股票，並取消相關的訂單。\n",
    "  - 從 orders 資料中可以發現，10/19下的單子在10/20被取消；在10/20下的單子，當天就被取消。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "37c3bc91",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>id</th>\n",
       "      <th>dt</th>\n",
       "      <th>reason</th>\n",
       "      <th>created</th>\n",
       "      <th>amount</th>\n",
       "      <th>filled</th>\n",
       "      <th>commission</th>\n",
       "      <th>stop</th>\n",
       "      <th>limit</th>\n",
       "      <th>stop_reached</th>\n",
       "      <th>limit_reached</th>\n",
       "      <th>asset</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-19 13:30:00+08:00</th>\n",
       "      <td>1</td>\n",
       "      <td>2327</td>\n",
       "      <td>948a3e20ad1c4158a8233cc604bd21af</td>\n",
       "      <td>2022-10-19 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-19 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(1 [2327])</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-20 13:30:00+08:00</th>\n",
       "      <td>1</td>\n",
       "      <td>2327</td>\n",
       "      <td>948a3e20ad1c4158a8233cc604bd21af</td>\n",
       "      <td>2022-10-20 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-19 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(1 [2327])</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-20 13:30:00+08:00</th>\n",
       "      <td>1</td>\n",
       "      <td>2327</td>\n",
       "      <td>d3fa16e044514cc8b7e046d72a0812b8</td>\n",
       "      <td>2022-10-20 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-20 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(1 [2327])</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           sid symbol                                id  \\\n",
       "2022-10-19 13:30:00+08:00    1   2327  948a3e20ad1c4158a8233cc604bd21af   \n",
       "2022-10-20 13:30:00+08:00    1   2327  948a3e20ad1c4158a8233cc604bd21af   \n",
       "2022-10-20 13:30:00+08:00    1   2327  d3fa16e044514cc8b7e046d72a0812b8   \n",
       "\n",
       "                                                 dt reason  \\\n",
       "2022-10-19 13:30:00+08:00 2022-10-19 13:30:00+08:00   None   \n",
       "2022-10-20 13:30:00+08:00 2022-10-20 13:30:00+08:00   None   \n",
       "2022-10-20 13:30:00+08:00 2022-10-20 13:30:00+08:00   None   \n",
       "\n",
       "                                            created  amount  filled  \\\n",
       "2022-10-19 13:30:00+08:00 2022-10-19 13:30:00+08:00    1000       0   \n",
       "2022-10-20 13:30:00+08:00 2022-10-19 13:30:00+08:00    1000       0   \n",
       "2022-10-20 13:30:00+08:00 2022-10-20 13:30:00+08:00    1000       0   \n",
       "\n",
       "                           commission  stop limit  stop_reached  \\\n",
       "2022-10-19 13:30:00+08:00         0.0  None  None         False   \n",
       "2022-10-20 13:30:00+08:00         0.0  None  None         False   \n",
       "2022-10-20 13:30:00+08:00         0.0  None  None         False   \n",
       "\n",
       "                           limit_reached             asset  status  \n",
       "2022-10-19 13:30:00+08:00          False  Equity(1 [2327])       0  \n",
       "2022-10-20 13:30:00+08:00          False  Equity(1 [2327])       2  \n",
       "2022-10-20 13:30:00+08:00          False  Equity(1 [2327])       2  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.query('symbol == \"2327\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaf9035d",
   "metadata": {},
   "source": [
    "<span id=\"指定特定日期的取消交易\"></span>\n",
    "## 範例：指定特定日期的取消交易、stop_price應用\n",
    "希望未成交訂單不要保留超過10天。\n",
    "\n",
    "[Return to Menu](#menu)\n",
    "\n",
    "在以下這個範例，我們設計了 **PART C** 的程式碼：\n",
    "\n",
    "- 取消訂單邏輯：\n",
    "  - 每天利用`get_open_orders`產出未完全成交的訂單。\n",
    "  - 針對上述訂單逐筆進行以下判斷：若距離訂單建立日（`zipline.finance.order.Order.created`）達10個交易日（不含建立日），但還沒有完全成交時取消該訂單。\n",
    "\n",
    "    ```python\n",
    "    calendar_name='TEJ'\n",
    "    \n",
    "    def handle_data(context, data):\n",
    "        open_orders = get_open_orders()\n",
    "\n",
    "        for asset in open_orders:\n",
    "            for o in open_orders[asset]:\n",
    "                waiting_time = (len(get_calendar(calendar_name).sessions_in_range(o.created, get_datetime())))\n",
    "                if waiting_time == 10:\n",
    "                    cancel_order(o)\n",
    "        ...\n",
    "    ```\n",
    "- 設定交易：\n",
    "  - 在10/17 long 1000 股的 1216 股票，並設定 stop_price = 66。\n",
    "  - 在10/20 long 1000 股的 1216 股票，並設定 stop_price = 65.5。    \n",
    "\n",
    "    ```python    \n",
    "    def handle_data(context, data):\n",
    "        ...\n",
    "        if context.i == 0: # 2022-10-17\n",
    "            order(symbol('1216'), 1000, stop_price = 66)\n",
    "\n",
    "        if context.i == 3: # 2022-10-20\n",
    "            order(symbol('1216'), 1000, stop_price = 65.5)\n",
    "        ...\n",
    "    ```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "198f9e57",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[2023-09-25 02:10:52.891546]: INFO: handle_simulation_end: Simulated 15 trading days\n",
      "first open: 2022-10-17 01:01:00+00:00\n",
      "last close: 2022-11-04 05:30:00+00:00\n"
     ]
    }
   ],
   "source": [
    "def initialize(context):\n",
    "    context.i = 0  \n",
    "    context.tickers = ['1216', '2330', '2327']\n",
    "    context.asset = [symbol(ticker) for ticker in context.tickers]      \n",
    "    set_slippage(slippage. VolumeShareSlippage(volume_limit=0.025, price_impact=0.1))\n",
    "    set_commission(commission.PerDollar(cost = commission_cost))\n",
    "    set_benchmark(symbol('IR0001'))\n",
    "    \n",
    "def handle_data(context, data):\n",
    "    \n",
    "    #PART C    \n",
    "    open_orders = get_open_orders()\n",
    "\n",
    "    for asset in open_orders:\n",
    "        for o in open_orders[asset]:\n",
    "            waiting_time = (len(get_calendar(calendar_name).sessions_in_range(o.created, get_datetime())))\n",
    "            if waiting_time == 10:\n",
    "                cancel_order(o)\n",
    "    \n",
    "    if context.i == 0: # 2022-10-17\n",
    "        order(symbol('1216'), 1000, stop_price = 66)\n",
    "\n",
    "    if context.i == 3: # 2022-10-20\n",
    "        order(symbol('1216'), 1000, stop_price = 65.5)\n",
    "        \n",
    "   \n",
    "    record(close=data.current(context.asset, 'close'))\n",
    "    record(volume=data.current(context.asset, 'volume'))\n",
    "    context.i += 1\n",
    "\n",
    "\n",
    "commission_cost = 0.001425\n",
    "capital_base = 1e8\n",
    "\n",
    "performance = run_algorithm(start=start_dt,\n",
    "                            end=end_dt,\n",
    "                            initialize=initialize,\n",
    "                            handle_data=handle_data,\n",
    "                            capital_base=capital_base,\n",
    "                            trading_calendar=get_calendar(calendar_name),\n",
    "                            bundle=bundle_name)\n",
    "\n",
    "closing_price = tejapi.get('TWN/APIPRCD',\n",
    "                           coid=['1216'],\n",
    "                           opts={'columns':['mdate','coid','close_d']},\n",
    "                           mdate={'gte':start,'lte':end },\n",
    "                           paginate=True)\n",
    "\n",
    "positions, transactions, orders = get_transaction_detail(performance)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4f685a6",
   "metadata": {},
   "source": [
    "## 講解說明\n",
    "### 10/17\n",
    "- 在10/17下的訂單，設定 stop_price = 66，所以理論上這單會一直在 open_orders 中，直到在11/1才會買入（收盤價 >= 66）。\n",
    "- 因為理論上 zipline 會**無限期**等 **stop_price 和 limit_price 達到目標**，如果希望設下限制，當等待時間達到十個交易日（10/31）就取消，可以參考 **PART C** 程式碼。\n",
    "- 這段程式碼先運用了**order 物件中的created屬性**（`zipline.finance.order.Order.created`）取得訂單建立時間，再用`get_datetime()`取得當天時間，接著用 `get_calendar(calendar_name).sessions_in_range(start, end)` 算出經過了多少個交易日，最後建立迴圈並逐日檢查並取消等待太久的訂單。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d2d418bd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mdate</th>\n",
       "      <th>coid</th>\n",
       "      <th>close_d</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>None</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-10-17</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-10-18</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-10-19</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-20</td>\n",
       "      <td>1216</td>\n",
       "      <td>64.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-21</td>\n",
       "      <td>1216</td>\n",
       "      <td>64.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-10-24</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-10-26</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-10-27</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-10-28</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-10-31</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2022-11-01</td>\n",
       "      <td>1216</td>\n",
       "      <td>66.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          mdate  coid  close_d\n",
       "None                          \n",
       "0    2022-10-17  1216     65.9\n",
       "1    2022-10-18  1216     65.3\n",
       "2    2022-10-19  1216     65.3\n",
       "3    2022-10-20  1216     64.7\n",
       "4    2022-10-21  1216     64.1\n",
       "5    2022-10-24  1216     65.1\n",
       "6    2022-10-25  1216     65.5\n",
       "7    2022-10-26  1216     65.6\n",
       "8    2022-10-27  1216     65.8\n",
       "9    2022-10-28  1216     65.8\n",
       "10   2022-10-31  1216     65.5\n",
       "11   2022-11-01  1216     66.1"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "closing_price.query('(mdate <= \"2022-11-01\")')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fb672896",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>id</th>\n",
       "      <th>dt</th>\n",
       "      <th>reason</th>\n",
       "      <th>created</th>\n",
       "      <th>amount</th>\n",
       "      <th>filled</th>\n",
       "      <th>commission</th>\n",
       "      <th>stop</th>\n",
       "      <th>limit</th>\n",
       "      <th>stop_reached</th>\n",
       "      <th>limit_reached</th>\n",
       "      <th>asset</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-17 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>34653855667b4281a12db30677740148</td>\n",
       "      <td>2022-10-17 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-17 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-31 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>34653855667b4281a12db30677740148</td>\n",
       "      <td>2022-10-31 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-17 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           sid symbol                                id  \\\n",
       "2022-10-17 13:30:00+08:00    0   1216  34653855667b4281a12db30677740148   \n",
       "2022-10-31 13:30:00+08:00    0   1216  34653855667b4281a12db30677740148   \n",
       "\n",
       "                                                 dt reason  \\\n",
       "2022-10-17 13:30:00+08:00 2022-10-17 13:30:00+08:00   None   \n",
       "2022-10-31 13:30:00+08:00 2022-10-31 13:30:00+08:00   None   \n",
       "\n",
       "                                            created  amount  filled  \\\n",
       "2022-10-17 13:30:00+08:00 2022-10-17 13:30:00+08:00    1000       0   \n",
       "2022-10-31 13:30:00+08:00 2022-10-17 13:30:00+08:00    1000       0   \n",
       "\n",
       "                           commission  stop limit  stop_reached  \\\n",
       "2022-10-17 13:30:00+08:00         0.0  66.0  None         False   \n",
       "2022-10-31 13:30:00+08:00         0.0  66.0  None         False   \n",
       "\n",
       "                           limit_reached             asset  status  \n",
       "2022-10-17 13:30:00+08:00          False  Equity(0 [1216])       0  \n",
       "2022-10-31 13:30:00+08:00          False  Equity(0 [1216])       2  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.query('created.dt.strftime(\"%Y-%m-%d\") == \"2022-10-17\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fe7522a",
   "metadata": {},
   "source": [
    "### 10/20\n",
    "在10/20下的訂單，stop_price = 65.5，在10/25就成交，並沒有等待超過十天，因此不會被 **PART C** 這段程式給取消掉。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "90e1360d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mdate</th>\n",
       "      <th>coid</th>\n",
       "      <th>close_d</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>None</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-10-20</td>\n",
       "      <td>1216</td>\n",
       "      <td>64.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-10-21</td>\n",
       "      <td>1216</td>\n",
       "      <td>64.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-10-24</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-10-25</td>\n",
       "      <td>1216</td>\n",
       "      <td>65.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          mdate  coid  close_d\n",
       "None                          \n",
       "3    2022-10-20  1216     64.7\n",
       "4    2022-10-21  1216     64.1\n",
       "5    2022-10-24  1216     65.1\n",
       "6    2022-10-25  1216     65.5"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "closing_price.query('(mdate <= \"2022-10-25\") & (mdate >= \"2022-10-20\")')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "61195c68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>id</th>\n",
       "      <th>dt</th>\n",
       "      <th>reason</th>\n",
       "      <th>created</th>\n",
       "      <th>amount</th>\n",
       "      <th>filled</th>\n",
       "      <th>commission</th>\n",
       "      <th>stop</th>\n",
       "      <th>limit</th>\n",
       "      <th>stop_reached</th>\n",
       "      <th>limit_reached</th>\n",
       "      <th>asset</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-20 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>28ccb816137346b7b6c8b4e26639cb8d</td>\n",
       "      <td>2022-10-20 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-20 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>65.5</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022-10-25 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>28ccb816137346b7b6c8b4e26639cb8d</td>\n",
       "      <td>2022-10-25 13:30:00+08:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-10-20 13:30:00+08:00</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>93.3375</td>\n",
       "      <td>65.5</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           sid symbol                                id  \\\n",
       "2022-10-20 13:30:00+08:00    0   1216  28ccb816137346b7b6c8b4e26639cb8d   \n",
       "2022-10-25 13:30:00+08:00    0   1216  28ccb816137346b7b6c8b4e26639cb8d   \n",
       "\n",
       "                                                 dt reason  \\\n",
       "2022-10-20 13:30:00+08:00 2022-10-20 13:30:00+08:00   None   \n",
       "2022-10-25 13:30:00+08:00 2022-10-25 13:30:00+08:00   None   \n",
       "\n",
       "                                            created  amount  filled  \\\n",
       "2022-10-20 13:30:00+08:00 2022-10-20 13:30:00+08:00    1000       0   \n",
       "2022-10-25 13:30:00+08:00 2022-10-20 13:30:00+08:00    1000    1000   \n",
       "\n",
       "                           commission  stop limit  stop_reached  \\\n",
       "2022-10-20 13:30:00+08:00      0.0000  65.5  None         False   \n",
       "2022-10-25 13:30:00+08:00     93.3375  65.5  None          True   \n",
       "\n",
       "                           limit_reached             asset  status  \n",
       "2022-10-20 13:30:00+08:00          False  Equity(0 [1216])       0  \n",
       "2022-10-25 13:30:00+08:00          False  Equity(0 [1216])       1  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.query('created.dt.strftime(\"%Y-%m-%d\") == \"2022-10-20\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a8668fb7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sid</th>\n",
       "      <th>symbol</th>\n",
       "      <th>amount</th>\n",
       "      <th>dt</th>\n",
       "      <th>price</th>\n",
       "      <th>order_id</th>\n",
       "      <th>asset</th>\n",
       "      <th>commission</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2022-10-25 13:30:00+08:00</th>\n",
       "      <td>0</td>\n",
       "      <td>1216</td>\n",
       "      <td>1000</td>\n",
       "      <td>2022-10-25 13:30:00+08:00</td>\n",
       "      <td>65.5</td>\n",
       "      <td>28ccb816137346b7b6c8b4e26639cb8d</td>\n",
       "      <td>Equity(0 [1216])</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           sid symbol  amount                        dt  \\\n",
       "2022-10-25 13:30:00+08:00    0   1216    1000 2022-10-25 13:30:00+08:00   \n",
       "\n",
       "                           price                          order_id  \\\n",
       "2022-10-25 13:30:00+08:00   65.5  28ccb816137346b7b6c8b4e26639cb8d   \n",
       "\n",
       "                                      asset commission  \n",
       "2022-10-25 13:30:00+08:00  Equity(0 [1216])       None  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df749bec",
   "metadata": {},
   "source": [
    "[Return to Menu](#menu)"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}