投資組合回測.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "\n",
    "# 以我們上一堂課的 Excel 檔案爲例，打開你的 tsmc_back_test.xlsx 檔案\n",
    "wb = xw.Book(r'你的 tsmc_back_test.xlsx 檔案路徑')\n",
    "\n",
    "tsmc_sheet = wb.sheets[\"TW2330\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from xlwings.constants import Direction\n",
    "\n",
    "last_cell = tsmc_sheet.cells(1, \"A\").end(Direction.xlDown)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 最後一個儲存格的行數\n",
    "last_cell.row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 最後一個儲存格的欄數\n",
    "last_cell.column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 把最後一個儲存格的底色變紅色\n",
    "last_cell.color = (255, 0, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "import time\n",
    "\n",
    "# 以我們上一堂課的 Excel 檔案爲例，打開你的 tsmc_back_test.xlsx 檔案\n",
    "wb = xw.Book(r'你的 portfolio_back_test.xlsx 檔案路徑')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 動態找出所有試算表\n",
    "wb.sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用迴圈遍歷每一個試算表\n",
    "for sheet in wb.sheets:\n",
    "    print(sheet.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tsmc_sheet = wb.sheets[\"TW2330\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 開啓一個試算表\n",
    "tsmc_sheet.activate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "for sheet in wb.sheets:\n",
    "    sheet.activate()\n",
    "    time.sleep(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from xlwings.constants import Direction\n",
    "\n",
    "def run_back_test(tsmc_sheet):\n",
    "\n",
    "    last_cell = tsmc_sheet.range('A1').end(Direction.xlDown)\n",
    "    last_row = last_cell.row\n",
    "\n",
    "    for i in range(4, last_row+1):\n",
    "        formula = \"=Average(B{}:B{})\".format(i-2, i)\n",
    "        tsmc_sheet.cells(i, \"C\").formula = formula\n",
    "        \n",
    "    # 從 K14 截取我們的交易股數，也就是 1000\n",
    "    stock_shares = tsmc_sheet.cells(14, \"K\").value\n",
    "    # 手動設定第四行，也就是第一天的資料\n",
    "    tsmc_sheet.cells(4, \"D\").value = stock_shares\n",
    "    tsmc_sheet.cells(4, \"E\").value = 0\n",
    "    tsmc_sheet.cells(4, \"F\").value = tsmc_sheet.cells(4, \"D\").value\n",
    "    tsmc_sheet.cells(4, \"G\").value = tsmc_sheet.cells(13, \"K\").value - stock_shares * tsmc_sheet.cells(4, \"B\").value\n",
    "    tsmc_sheet.cells(4, \"H\").value = tsmc_sheet.cells(4, \"G\").value + tsmc_sheet.cells(4, \"F\").value * tsmc_sheet.cells(4, \"B\").value\n",
    "    \n",
    "    # 實作交易策略\n",
    "    for i in range(5, last_row+1):\n",
    "        # 截取當天的 3日移動平均\n",
    "        sma_3d = tsmc_sheet.cells(i, \"C\").value\n",
    "        # 截取當天收盤價\n",
    "        price_today = tsmc_sheet.cells(i, 'B').value\n",
    "        # 若 5日 > 10日，而且我有足夠買入以今日收盤價計價的 1000 股的現金，就買入 1000 股（在 E 欄顯示 1000）\n",
    "        if (price_today > sma_3d) and (tsmc_sheet.cells(i-1, \"G\").value > price_today * stock_shares):\n",
    "            tsmc_sheet.cells(i, \"D\").value = stock_shares\n",
    "        else:\n",
    "        # 若上述條件不符和，就買入 0 股，（在 E 欄顯示 0）\n",
    "            tsmc_sheet.cells(i, \"D\").value = 0\n",
    "        # 若 3日 > ，而且昨天的持有股數大於 1000 股，就賣出 1000 股\n",
    "        if (price_today < sma_3d) and (tsmc_sheet.cells(i-1, \"F\").value >= stock_shares):\n",
    "            tsmc_sheet.cells(i, \"E\").value = stock_shares\n",
    "        else:\n",
    "            tsmc_sheet.cells(i, \"E\").value = 0\n",
    "        # 持有股數，算法是前一天的持有股數 + 今天的買入股數 - 今天的賣出股數\n",
    "        tsmc_sheet.cells(i, \"F\").value = tsmc_sheet.cells(i-1, \"F\").value + tsmc_sheet.cells(i, \"D\").value - tsmc_sheet.cells(i, \"E\").value\n",
    "        # 持有資金，算法是前一天的持有資金 + 今日收盤價 x (今天的賣出股數 - 今天的買入股數)\n",
    "        tsmc_sheet.cells(i, \"G\").value = tsmc_sheet.cells(i-1, \"G\").value + price_today * (tsmc_sheet.cells(i, \"E\").value - tsmc_sheet.cells(i, \"D\").value)\n",
    "        # 總資產則是持有股數 x 今日收盤價 + 今日持有資金\n",
    "        tsmc_sheet.cells(i, \"H\").value = tsmc_sheet.cells(i, \"G\").value + tsmc_sheet.cells(i, \"F\").value * price_today\n",
    "\n",
    "    # 計算并且將總收益顯示在 L20\n",
    "    tsmc_sheet.cells(15, \"K\").value = tsmc_sheet.cells(last_row, \"H\").value - tsmc_sheet.cells(13, \"K\").value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import win32api\n",
    "\n",
    "balance = 0\n",
    "\n",
    "for sheet in wb.sheets:\n",
    "    sheet.activate()\n",
    "    run_back_test(sheet)\n",
    "    balance += sheet.cells(15, 'K').value\n",
    "    \n",
    "print(\"投資組合收益： ${}\".format(balance))\n",
    "win32api.MessageBox(wb.app.hwnd, \"投資組合收益： ${}\".format(balance), \"回測結果\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import win32api\n",
    "win32api.MessageBox(wb.app.hwnd, \"投資組合收益： ${}\".format(balance), \"回測結果\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import win32api\n",
    "balance = 0\n",
    "\n",
    "for sheet in wb.sheets:\n",
    "    sheet.activate()\n",
    "    run_back_test(sheet)\n",
    "    balance += sheet.cells(15, 'K').value\n",
    "\n",
    "print(\"投資組合收益： ${}\".format(balance))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "win32api.MessageBox(wb.app.hwnd, \"投資組合收益： ${}\".format(balance), \"回測結果\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}