Skip to content

Instantly share code, notes, and snippets.

@tej87681088
Created June 13, 2023 07:39
Show Gist options
  • Select an option

  • Save tej87681088/994cb3e8f05700a8569ed435ecfcecd1 to your computer and use it in GitHub Desktop.

Select an option

Save tej87681088/994cb3e8f05700a8569ed435ecfcecd1 to your computer and use it in GitHub Desktop.
Display the source blob
Display the rendered blob
Raw
{
"cells": [
{
"cell_type": "markdown",
"id": "519de3c2",
"metadata": {},
"source": [
"套件導入"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "230e8419",
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd \n",
"import numpy as np \n",
"import tejapi\n"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "6dfb815e",
"metadata": {},
"outputs": [],
"source": [
"api_key = 'your_api_key'\n",
"tejapi.ApiConfig.api_key = api_key\n",
"tejapi.ApiConfig.ignoretz = True"
]
},
{
"cell_type": "markdown",
"id": "11d0ad14",
"metadata": {},
"source": [
"資料庫導入"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "0fc03dfd",
"metadata": {},
"outputs": [],
"source": [
"comp_data = tejapi.get('TWN/ANPRCSTD',\n",
" paginate = True,\n",
" opts = {\"columns\":[\"coid\", \"mdate\", \"mkt\", \"stype\", \"list_date\", \"delis_date\", \"tseind\"]}\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": 22,
"id": "18b2306f",
"metadata": {},
"outputs": [],
"source": [
"coid_lst = list(comp_data.loc[(comp_data[\"stype\"] == \"STOCK\") & (comp_data[\"tseind\"] == \"22\")][\"coid\"])"
]
},
{
"cell_type": "code",
"execution_count": 24,
"id": "d0658ecd",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"241"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"len(coid_lst)"
]
},
{
"cell_type": "code",
"execution_count": 70,
"id": "4ac80bc6",
"metadata": {
"scrolled": false
},
"outputs": [],
"source": [
"gte, lte = \"2020-01-01\", \"2022-12-31\"\n",
"price_data = tejapi.get('TWN/AAPRCDA',\n",
" paginate = True,\n",
" coid = coid_lst,\n",
" mdate = {\"gte\":gte, \"lte\":lte},\n",
" opts = {\"columns\":[\"coid\", \"mdate\", \"fld014\", \"cls60\", \"close_d\"]}\n",
" )"
]
},
{
"cell_type": "code",
"execution_count": 26,
"id": "5c3aff20",
"metadata": {},
"outputs": [],
"source": [
"# price_data = pd.read_csv(\"price_data.csv\")"
]
},
{
"cell_type": "markdown",
"id": "ccc5530a",
"metadata": {},
"source": [
"資料前處理"
]
},
{
"cell_type": "code",
"execution_count": 71,
"id": "cc1a677c",
"metadata": {},
"outputs": [],
"source": [
"price_data[\"coid\"] = price_data[\"coid\"].astype(str)"
]
},
{
"cell_type": "code",
"execution_count": 72,
"id": "04e52bb6",
"metadata": {},
"outputs": [],
"source": [
"inter_coid = list(set(coid_lst).intersection(set(price_data[\"coid\"].unique())))"
]
},
{
"cell_type": "code",
"execution_count": 73,
"id": "28b13f9b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['8432',\n",
" '6645',\n",
" '1760',\n",
" '4195',\n",
" '6661',\n",
" '1733',\n",
" '8436',\n",
" '4155',\n",
" '6872',\n",
" '6493',\n",
" '4114',\n",
" '1799',\n",
" '1783',\n",
" '4167',\n",
" '4126',\n",
" '6549',\n",
" '6885',\n",
" '4736',\n",
" '6586',\n",
" '6703',\n",
" '4911',\n",
" '3218',\n",
" '6580',\n",
" '6518',\n",
" '7595',\n",
" '6637',\n",
" '4129',\n",
" '4131',\n",
" '5312',\n",
" '4771',\n",
" '6572',\n",
" '6242',\n",
" '4151',\n",
" '6747',\n",
" '6527',\n",
" '6767',\n",
" '6535',\n",
" '4138',\n",
" '6612',\n",
" '1762',\n",
" '3176',\n",
" '1736',\n",
" '6130',\n",
" '6794',\n",
" '4116',\n",
" '8403',\n",
" '7575',\n",
" '6589',\n",
" '1789',\n",
" '6838',\n",
" '1598',\n",
" '4164',\n",
" '4108',\n",
" '1271',\n",
" '4105',\n",
" '6562',\n",
" '6543',\n",
" '6861',\n",
" '6762',\n",
" '6891',\n",
" '4166',\n",
" '6677',\n",
" '6610',\n",
" '4162',\n",
" '4175',\n",
" '6472',\n",
" '6492',\n",
" '6665',\n",
" '6496',\n",
" '4109',\n",
" '6758',\n",
" '6844',\n",
" '6879',\n",
" '1777',\n",
" '6461',\n",
" '6748',\n",
" '6929',\n",
" '4106',\n",
" '6734',\n",
" '6932',\n",
" '4119',\n",
" '3164',\n",
" '6569',\n",
" '7427',\n",
" '4732',\n",
" '4127',\n",
" '4728',\n",
" '6657',\n",
" '1786',\n",
" '4194',\n",
" '6566',\n",
" '6857',\n",
" '4183',\n",
" '4133',\n",
" '7561',\n",
" '4192',\n",
" '4743',\n",
" '6850',\n",
" '1784',\n",
" '6469',\n",
" '4173',\n",
" '4170',\n",
" '6547',\n",
" '4161',\n",
" '6633',\n",
" '4197',\n",
" '4735',\n",
" '6848',\n",
" '3118',\n",
" '1780',\n",
" '4130',\n",
" '1565',\n",
" '6615',\n",
" '6892',\n",
" '4169',\n",
" '6864',\n",
" '4174',\n",
" '6875',\n",
" '6634',\n",
" '4142',\n",
" '4104',\n",
" '6713',\n",
" '4132',\n",
" '1593',\n",
" '6446',\n",
" '4102',\n",
" '4153',\n",
" '6796',\n",
" '4160',\n",
" '6931',\n",
" '4198',\n",
" '1795',\n",
" '4111',\n",
" '6815',\n",
" '6814',\n",
" '6676',\n",
" '4188',\n",
" '4744',\n",
" '1731',\n",
" '1813',\n",
" '4747',\n",
" '6841',\n",
" '4726',\n",
" '4737',\n",
" '6539',\n",
" '8279',\n",
" '6575',\n",
" '3184',\n",
" '4168',\n",
" '6733',\n",
" '4150',\n",
" '6574',\n",
" '4128',\n",
" '6576',\n",
" '4163',\n",
" '6523',\n",
" '6712',\n",
" '4152',\n",
" '7555',\n",
" '4107',\n",
" '4117',\n",
" '3205',\n",
" '6817',\n",
" '1752',\n",
" '6491',\n",
" '6797',\n",
" '4147',\n",
" '6499',\n",
" '6785',\n",
" '4746',\n",
" '6620',\n",
" '3705',\n",
" '6709',\n",
" '4120',\n",
" '1707',\n",
" '6782',\n",
" '8409',\n",
" '6808',\n",
" '1788',\n",
" '6696',\n",
" '6621',\n",
" '4121',\n",
" '4115',\n",
" '6564',\n",
" '6926',\n",
" '1781',\n",
" '1734',\n",
" '1701',\n",
" '4123',\n",
" '6847',\n",
" '4191',\n",
" '6730',\n",
" '1720',\n",
" '6445',\n",
" '6652',\n",
" '4172',\n",
" '6744',\n",
" '6810',\n",
" '6919',\n",
" '6827',\n",
" '6649',\n",
" '6662',\n",
" '4186']"
]
},
"execution_count": 73,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"inter_coid"
]
},
{
"cell_type": "code",
"execution_count": 186,
"id": "81bc5077",
"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>coid</th>\n",
" <th>mdate</th>\n",
" <th>fld014</th>\n",
" <th>cls60</th>\n",
" <th>close_d</th>\n",
" </tr>\n",
" <tr>\n",
" <th>None</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1271</td>\n",
" <td>2021-01-18</td>\n",
" <td>70.30</td>\n",
" <td>70.3000</td>\n",
" <td>70.3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1271</td>\n",
" <td>2021-01-19</td>\n",
" <td>69.70</td>\n",
" <td>69.7000</td>\n",
" <td>69.1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>1271</td>\n",
" <td>2021-01-20</td>\n",
" <td>69.60</td>\n",
" <td>69.6000</td>\n",
" <td>69.4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>1271</td>\n",
" <td>2021-01-21</td>\n",
" <td>69.10</td>\n",
" <td>69.1000</td>\n",
" <td>67.6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>1271</td>\n",
" <td>2021-01-22</td>\n",
" <td>69.12</td>\n",
" <td>69.1200</td>\n",
" <td>69.2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>126739</th>\n",
" <td>8436</td>\n",
" <td>2022-12-26</td>\n",
" <td>165.75</td>\n",
" <td>132.6083</td>\n",
" <td>175.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>126740</th>\n",
" <td>8436</td>\n",
" <td>2022-12-27</td>\n",
" <td>167.75</td>\n",
" <td>133.5083</td>\n",
" <td>177.5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>126741</th>\n",
" <td>8436</td>\n",
" <td>2022-12-28</td>\n",
" <td>168.65</td>\n",
" <td>134.2917</td>\n",
" <td>171.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>126742</th>\n",
" <td>8436</td>\n",
" <td>2022-12-29</td>\n",
" <td>169.90</td>\n",
" <td>135.0917</td>\n",
" <td>172.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>126743</th>\n",
" <td>8436</td>\n",
" <td>2022-12-30</td>\n",
" <td>171.25</td>\n",
" <td>135.8083</td>\n",
" <td>171.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>126744 rows × 5 columns</p>\n",
"</div>"
],
"text/plain": [
" coid mdate fld014 cls60 close_d\n",
"None \n",
"0 1271 2021-01-18 70.30 70.3000 70.3\n",
"1 1271 2021-01-19 69.70 69.7000 69.1\n",
"2 1271 2021-01-20 69.60 69.6000 69.4\n",
"3 1271 2021-01-21 69.10 69.1000 67.6\n",
"4 1271 2021-01-22 69.12 69.1200 69.2\n",
"... ... ... ... ... ...\n",
"126739 8436 2022-12-26 165.75 132.6083 175.0\n",
"126740 8436 2022-12-27 167.75 133.5083 177.5\n",
"126741 8436 2022-12-28 168.65 134.2917 171.0\n",
"126742 8436 2022-12-29 169.90 135.0917 172.0\n",
"126743 8436 2022-12-30 171.25 135.8083 171.0\n",
"\n",
"[126744 rows x 5 columns]"
]
},
"execution_count": 186,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"price_data[price_data[\"coid\"].isin(inter_coid)]"
]
},
{
"cell_type": "markdown",
"id": "94e6a98b",
"metadata": {},
"source": [
"交易策略"
]
},
{
"cell_type": "code",
"execution_count": 209,
"id": "c147f6bf",
"metadata": {},
"outputs": [],
"source": [
"fltr_price_data = price_data[price_data[\"coid\"].isin(inter_coid)]\n",
"group = fltr_price_data.groupby(\"coid\")\n"
]
},
{
"cell_type": "code",
"execution_count": 210,
"id": "4b7318ff",
"metadata": {},
"outputs": [],
"source": [
"def MA_strategy(df, principal):\n",
" position = 0 \n",
" lst = []\n",
" \n",
" for i in range(len(df)):\n",
" if df[\"fld014\"].iloc[i] > df[\"cls60\"].iloc[i] and principal >= float(df[\"close_d\"].iloc[i])*1*1000:\n",
" principal -= float(df[\"close_d\"].iloc[i])*1*1000\n",
" position += 1\n",
" lst.append({\n",
" \"日期\": df[\"mdate\"].iloc[i],\n",
" \"標的\": df[\"coid\"].iloc[i],\n",
" \"買入/賣出\": \"買入\",\n",
" \"單價\": float(df[\"close_d\"].iloc[i]),\n",
" \"單位\": 1,\n",
" \"剩餘現金\": principal,\n",
" \"部位\" : position\n",
" })\n",
"\n",
" elif df[\"fld014\"].iloc[i] < df[\"cls60\"].iloc[i] and position > 0:\n",
" principal += float(df[\"close_d\"].iloc[i])*1*1000\n",
" position -= 1\n",
" \n",
" lst.append({\n",
" \"日期\": df[\"mdate\"].iloc[i],\n",
" \"標的\": df[\"coid\"].iloc[i],\n",
" \"買入/賣出\": \"賣出\",\n",
" \"單價\": float(df[\"close_d\"].iloc[i]),\n",
" \"單位\": 1,\n",
" \"剩餘現金\": principal,\n",
" \"部位\" : position\n",
" })\n",
" \n",
" elif i == len(df)-1 and position > 0:\n",
" principal += float(df[\"close_d\"].iloc[i])*position*1000\n",
" position -= position\n",
" lst.append({\n",
" \"日期\": df[\"mdate\"].iloc[i],\n",
" \"標的\": df[\"coid\"].iloc[i],\n",
" \"買入/賣出\": \"賣出\",\n",
" \"單價\": float(df[\"close_d\"].iloc[i]),\n",
" \"單位\": position,\n",
" \"剩餘現金\": principal,\n",
" \"部位\" : position\n",
" })\n",
" \n",
"\n",
" df_output = pd.DataFrame(lst)\n",
" \n",
" \n",
" \n",
" return (df_output, principal)\n",
" \n"
]
},
{
"cell_type": "markdown",
"id": "035dff17",
"metadata": {},
"source": [
"所有曾經上市櫃公司股價資訊"
]
},
{
"cell_type": "code",
"execution_count": 211,
"id": "25a299a1",
"metadata": {},
"outputs": [],
"source": [
"principal = 1000000\n",
"total_return_1 = 0\n",
"df = pd.DataFrame(columns = [\"日期\", \"標的\", \"買入/賣出\", \"單價\", \"單位\", \"剩餘現金\", \"部位\"])\n",
"\n",
"for g in group.groups.keys():\n",
" reuslt = MA_strategy(group.get_group(g), principal)\n",
" total_return_1 += round(reuslt[1], 2)\n",
" df = pd.concat([df, reuslt[0]], ignore_index=True)\n",
" "
]
},
{
"cell_type": "code",
"execution_count": 212,
"id": "c5b632d9",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"-14.269384236453197\n"
]
}
],
"source": [
"total_return = (total_return_1/(1000000*len(group.groups.keys())) - 1) *100\n",
"print(f\"ROI : {total_return}%\")"
]
},
{
"cell_type": "code",
"execution_count": 225,
"id": "db059aec",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2020–01–01 至 2022–12–31狀態曾經為上市櫃的公司數量:203\n"
]
}
],
"source": [
"print(f'2020–01–01 至 2022–12–31狀態曾經為上市櫃的公司數量:{len(list(comp_data.loc[(comp_data[\"coid\"].isin(inter_coid))][\"coid\"]))}')\n"
]
},
{
"cell_type": "markdown",
"id": "d03ca560",
"metadata": {},
"source": [
"剔除下市櫃公司"
]
},
{
"cell_type": "code",
"execution_count": 213,
"id": "736bf729",
"metadata": {},
"outputs": [],
"source": [
"without_dist_coid = comp_data.loc[(comp_data[\"coid\"].isin(inter_coid)) & (comp_data[\"mkt\"] != \"DIST\")][\"coid\"].unique()\n",
"fltr_price_data = price_data[price_data[\"coid\"].isin(without_dist_coid)]\n",
"group = fltr_price_data.groupby(\"coid\")"
]
},
{
"cell_type": "code",
"execution_count": 214,
"id": "902e9b86",
"metadata": {},
"outputs": [],
"source": [
"principal = 1000000\n",
"total_return_2 = 0\n",
"df_without_dist = pd.DataFrame(columns = [\"日期\", \"標的\", \"買入/賣出\", \"單價\", \"單位\", \"剩餘現金\", \"部位\"])\n",
"\n",
"for g in group.groups.keys():\n",
" reuslt = MA_strategy(group.get_group(g), principal)\n",
" total_return_2 += round(reuslt[1], 2)\n",
" df = pd.concat([df_without_dist, reuslt[0]], ignore_index=True)\n",
" \n"
]
},
{
"cell_type": "code",
"execution_count": 226,
"id": "9f40cb7d",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"ROI : -13.846355670103094%\n"
]
}
],
"source": [
"return_without_dist = (total_return_2/(1000000*len(group.groups.keys())) - 1) *100\n",
"print(f\"ROI : {return_without_dist}%\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "cf159e62",
"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.8.8"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment