-
-
Save yuyueugene84/5972f8779df6b42a01c97ac7e6eb83ce to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"cells": [ | |
{ | |
"cell_type": "code", | |
"execution_count": 18, | |
"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": 19, | |
"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, \"C\").formula = formula" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 16, | |
"metadata": {}, | |
"outputs": [], | |
"source": [ | |
"# 從 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" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 17, | |
"metadata": {}, | |
"outputs": [], | |
"source": [ | |
"# 實作交易策略\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" | |
] | |
} | |
], | |
"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 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment