移動平均回測 Mac.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "\n",
    "wb = xw.Book(r\"在這裏輸入你放置 tsmc_back_test.xlsx 檔案在你的電腦的路徑，包含副檔名 .xlsx\")\n",
    "tsmc_sheet = wb.sheets[\"2330_python\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 指定最後一行資料為 175\n",
    "last_row = 175\n",
    "\n",
    "for i in range(4, last_row+1):\n",
    "    formula = \"=Average(B{}:B{})\".format(i-2, i)\n",
    "    tsmc_sheet.cells(i, 3).formula = formula"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 從 K14 截取我們的交易股數，也就是 1000\n",
    "stock_shares = tsmc_sheet.cells(14, 11).value\n",
    "# 手動設定第四行，也就是第一天的資料\n",
    "tsmc_sheet.cells(4, 4).value = stock_shares\n",
    "tsmc_sheet.cells(4, 5).value = 0\n",
    "tsmc_sheet.cells(4, 6).value = tsmc_sheet.cells(4, 4).value\n",
    "tsmc_sheet.cells(4, 7).value = tsmc_sheet.cells(13, 11).value - stock_shares * tsmc_sheet.cells(4, 2).value\n",
    "tsmc_sheet.cells(4, 8).value = tsmc_sheet.cells(4, 7).value + tsmc_sheet.cells(4, 6).value * tsmc_sheet.cells(4, 2).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 實作交易策略\n",
    "for i in range(5, last_row+1):\n",
    "    # 截取當天的 3日移動平均\n",
    "    sma_3d = tsmc_sheet.cells(i, 3).value\n",
    "    # 截取當天收盤價\n",
    "    price_today = tsmc_sheet.cells(i, 2).value\n",
    "    # 若 5日 > 10日，而且我有足夠買入以今日收盤價計價的 1000 股的現金，就買入 1000 股（在 E 欄顯示 1000）\n",
    "    if (price_today > sma_3d) and (tsmc_sheet.cells(i-1, 7).value > price_today * stock_shares):\n",
    "        tsmc_sheet.cells(i, 4).value = stock_shares\n",
    "    else:\n",
    "    # 若上述條件不符和，就買入 0 股，（在 E 欄顯示 0）\n",
    "        tsmc_sheet.cells(i, 4).value = 0\n",
    "    # 若 3日 > ，而且昨天的持有股數大於 1000 股，就賣出 1000 股\n",
    "    if (price_today < sma_3d) and (tsmc_sheet.cells(i-1, 6).value >= stock_shares):\n",
    "        tsmc_sheet.cells(i, 5).value = stock_shares\n",
    "    else:\n",
    "        tsmc_sheet.cells(i, 5).value = 0\n",
    "    # 持有股數，算法是前一天的持有股數 + 今天的買入股數 - 今天的賣出股數\n",
    "    tsmc_sheet.cells(i, 6).value = tsmc_sheet.cells(i-1, 6).value + tsmc_sheet.cells(i, 4).value - tsmc_sheet.cells(i, 5).value\n",
    "    # 持有資金，算法是前一天的持有資金 + 今日收盤價 x (今天的賣出股數 - 今天的買入股數)\n",
    "    tsmc_sheet.cells(i, 7).value = tsmc_sheet.cells(i-1, 7).value + price_today * (tsmc_sheet.cells(i, 5).value - tsmc_sheet.cells(i, 4).value)\n",
    "    # 總資產則是持有股數 x 今日收盤價 + 今日持有資金\n",
    "    tsmc_sheet.cells(i, 8).value = tsmc_sheet.cells(i, 7).value + tsmc_sheet.cells(i, 6).value * price_today\n",
    "\n",
    "# 計算并且將總收益顯示在 L20\n",
    "tsmc_sheet.cells(15, 11).value = tsmc_sheet.cells(last_row, 8).value - tsmc_sheet.cells(13, 11).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}