Skip to content

Instantly share code, notes, and snippets.

@gary136

gary136/TwStockIdvs.ipynb

Last active December 1, 2019 15:08
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save gary136/2ae1f70b8f4bc412ea825eb09413b6f4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save gary136/2ae1f70b8f4bc412ea825eb09413b6f4 to your computer and use it in GitHub Desktop.
Download ZIP
TwStockIdvs.ipynb
Display the source blob
Display the rendered blob
Raw
TwStockIdvs.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import requests\n",
"import numpy as np\n",
"from io import StringIO\n",
"import datetime"
]
},
{
"cell_type": "code",
"execution_count": 242,
"metadata": {},
"outputs": [],
"source": [
"class TwStockIdvs:\n",
"# 對照表\n",
"# m_mapping = {'上市':'sii','上櫃':'otc'}\n",
" base_url = 'https://mops.twse.com.tw/mops/web/'\n",
" p_mapping = {'資產負債表':base_url+'t164sb03'\n",
" ,'綜合損益表':base_url+'t164sb04'\n",
" ,'現金流量表':base_url+'t164sb05'\n",
" ,'權益變動表':base_url+'t164sb06'}\n",
" old_p_mapping = {'資產負債表':base_url+'t05st31'\n",
" ,'綜合損益表':base_url+'t05st34'\n",
" ,'現金流量表':base_url+'t05st39'\n",
" ,'權益變動表':base_url+'t05st38'}\n",
" \n",
" def __init__(self, co_id, year, season, purpose, \\\n",
" isnew=None, p_mapping=p_mapping, old_p_mapping=old_p_mapping):\n",
" cn_year = year if year < 1000 else year-1911\n",
" eg_year = year if year > 1000 else year+1911\n",
" ct_year = datetime.datetime.now().year\n",
" \n",
" if (eg_year>ct_year) or (purpose not in p_mapping):\n",
" print('條件有誤，請重新輸入')\n",
" elif eg_year==ct_year:\n",
" isnew='true'\n",
" elif eg_year<ct_year:\n",
" isnew='false'\n",
" \n",
" p_mapping = p_mapping if cn_year>=102 else old_p_mapping\n",
" \n",
" self.co_id = str(co_id)\n",
" self.year = str(cn_year)\n",
" self.season = '0'+str(season)\n",
" self.purpose = purpose\n",
" self.isnew = isnew\n",
" \n",
" self.url = p_mapping[self.purpose] if self.purpose in p_mapping else None\n",
" \n",
" def add_raw(self):\n",
" form = {\"encodeURIComponent\": \"1\",\n",
" \"step\": \"1\",\n",
" \"firstin\": \"1\",\n",
" \"off\": \"1\",\n",
" \"keyword4\": \"\",\n",
" \"code1\": \"\",\n",
" \"TYPEK2\": \"\",\n",
" \"checkbtn\": \"\",\n",
" \"queryName\": \"co_id\",\n",
" \"inpuType\": \"co_id\",\n",
" \"TYPEK\": \"all\",\n",
" \"isnew\": self.isnew,\n",
" \"co_id\": self.co_id,\n",
" \"year\": self.year,\n",
" \"season\": self.season}\n",
" headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) \\\n",
" AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36'}\n",
" \n",
" r = requests.post(self.url, form, headers=headers)\n",
" r.encoding = 'utf8'\n",
" self.raw_data = pd.read_html(StringIO(r.text))\n",
" \n",
"# 資產負債表\n",
" def unify_baln(self):\n",
"# ifrs 後\n",
" if int(self.year)>=102:\n",
" data = self.raw_data[-2].drop(['Unnamed: 7_level_2', 'Unnamed: 8_level_2'], axis=1, level=2)\n",
" data.columns = data.columns.droplevel([0,1])\n",
" data = data.set_index('會計項目')\n",
" data.index = [i[0] for i in data.index]\n",
" data.columns = data.columns.remove_unused_levels()\n",
" self.data = data\n",
"# ifrs 前\n",
" else:\n",
" data = x.raw_data[-2]\n",
" head = list(set(data.iloc[3]))[1:]\n",
" data.columns = list(data.iloc[2])\n",
" data = data.drop([0,1,2,3])\n",
" data = data.set_index('會計科目')\n",
" time = list(set(data.columns))\n",
" data.columns = pd.MultiIndex.from_product([time, head])\n",
" data.index = list(data.index)\n",
" self.data = data\n",
" \n",
"# 損益表\n",
" def unify_incm(self):\n",
"# ifrs 後\n",
" if int(self.year)>=102:\n",
" data = self.raw_data[-2].drop(['Unnamed: 9_level_2'], axis=1, level=2)\n",
" data.columns = data.columns.droplevel([0,1])\n",
" data = data.set_index('會計項目')\n",
" data.index = [i[0] for i in data.index]\n",
" data.columns = data.columns.remove_unused_levels()\n",
" self.data = data\n",
"# ifrs 前\n",
" else:\n",
" data = x.raw_data[-2]\n",
" head = list(set(data.iloc[3]))[1:]\n",
" data.columns = list(data.iloc[2])\n",
" data = data.drop([0,1,2,3])\n",
" data = data.set_index('會計科目')\n",
" time = list(set(data.columns))\n",
" data.columns = pd.MultiIndex.from_product([time, head])\n",
" data.index = list(data.index)\n",
" self.data = data\n",
"\n",
" def save(self):\n",
" name = f'{self.co_id}_{self.year}_{self.season}_{self.purpose}.csv'\n",
" self.data.to_csv(name, encoding='utf-8')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# ifrs 後 / 資產負債表"
]
},
{
"cell_type": "code",
"execution_count": 243,
"metadata": {},
"outputs": [],
"source": [
"x = TwStockIdvs(co_id='1104', year=2019, season=3, purpose='資產負債表')\n",
"x.add_raw()\n",
"x.unify_baln()"
]
},
{
"cell_type": "code",
"execution_count": 244,
"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 tr th {\n",
" text-align: left;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th colspan=\"2\" halign=\"left\">108年09月30日</th>\n",
" <th colspan=\"2\" halign=\"left\">107年12月31日</th>\n",
" <th colspan=\"2\" halign=\"left\">107年09月30日</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>金額</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>流動資產</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>現金及約當現金</th>\n",
" <td>227580.0</td>\n",
" <td>0.99</td>\n",
" <td>269426.0</td>\n",
" <td>1.19</td>\n",
" <td>222929.0</td>\n",
" <td>1.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>透過損益按公允價值衡量之金融資產－流動</th>\n",
" <td>1343.0</td>\n",
" <td>0.01</td>\n",
" <td>1166.0</td>\n",
" <td>0.01</td>\n",
" <td>1399.0</td>\n",
" <td>0.01</td>\n",
" </tr>\n",
" <tr>\n",
" <th>透過其他綜合損益按公允價值衡量之金融資產－流動</th>\n",
" <td>2266891.0</td>\n",
" <td>9.86</td>\n",
" <td>2114511.0</td>\n",
" <td>9.37</td>\n",
" <td>2334918.0</td>\n",
" <td>10.46</td>\n",
" </tr>\n",
" <tr>\n",
" <th>按攤銷後成本衡量之金融資產－流動</th>\n",
" <td>89065.0</td>\n",
" <td>0.39</td>\n",
" <td>89136.0</td>\n",
" <td>0.39</td>\n",
" <td>85937.0</td>\n",
" <td>0.38</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 108年09月30日 107年12月31日 107年09月30日 \n",
" 金額 % 金額 % 金額 %\n",
"流動資產 NaN NaN NaN NaN NaN NaN\n",
"現金及約當現金 227580.0 0.99 269426.0 1.19 222929.0 1.00\n",
"透過損益按公允價值衡量之金融資產－流動 1343.0 0.01 1166.0 0.01 1399.0 0.01\n",
"透過其他綜合損益按公允價值衡量之金融資產－流動 2266891.0 9.86 2114511.0 9.37 2334918.0 10.46\n",
"按攤銷後成本衡量之金融資產－流動 89065.0 0.39 89136.0 0.39 85937.0 0.38"
]
},
"execution_count": 244,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"x.data.head()"
]
},
{
"cell_type": "code",
"execution_count": 245,
"metadata": {},
"outputs": [],
"source": [
"x.save()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# ifrs 前 / 資產負債表"
]
},
{
"cell_type": "code",
"execution_count": 260,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"{'co_id': '1104',\n",
" 'year': '98',\n",
" 'season': '03',\n",
" 'purpose': '資產負債表',\n",
" 'isnew': 'false',\n",
" 'url': 'https://mops.twse.com.tw/mops/web/t05st31'}"
]
},
"execution_count": 260,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"x = TwStockIdvs(co_id='1104', year=2009, season=3, purpose='資產負債表')\n",
"x.__dict__"
]
},
{
"cell_type": "code",
"execution_count": 261,
"metadata": {},
"outputs": [],
"source": [
"x.add_raw()\n",
"x.unify_baln()\n",
"x.save()"
]
},
{
"cell_type": "code",
"execution_count": 262,
"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 tr th {\n",
" text-align: left;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th colspan=\"2\" halign=\"left\">97年09月30日</th>\n",
" <th colspan=\"2\" halign=\"left\">98年09月30日</th>\n",
" </tr>\n",
" <tr>\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>資產</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>流動資產</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>現金及約當現金</th>\n",
" <td>192453.00</td>\n",
" <td>1.13</td>\n",
" <td>132825.00</td>\n",
" <td>0.79</td>\n",
" </tr>\n",
" <tr>\n",
" <th>應收票據淨額</th>\n",
" <td>236729.00</td>\n",
" <td>1.39</td>\n",
" <td>315293.00</td>\n",
" <td>1.88</td>\n",
" </tr>\n",
" <tr>\n",
" <th>應收帳款淨額</th>\n",
" <td>434471.00</td>\n",
" <td>2.56</td>\n",
" <td>403666.00</td>\n",
" <td>2.41</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 97年09月30日 98年09月30日 \n",
" 金額 ％ 金額 ％\n",
"資產 NaN NaN NaN NaN\n",
"流動資產 NaN NaN NaN NaN\n",
"現金及約當現金 192453.00 1.13 132825.00 0.79\n",
"應收票據淨額 236729.00 1.39 315293.00 1.88\n",
"應收帳款淨額 434471.00 2.56 403666.00 2.41"
]
},
"execution_count": 262,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"x.data.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# ifrs 後 / 綜合損益表"
]
},
{
"cell_type": "code",
"execution_count": 249,
"metadata": {},
"outputs": [],
"source": [
"x = TwStockIdvs(co_id='1104', year=2019, season=3, purpose='綜合損益表')\n",
"x.add_raw()\n",
"x.unify_incm()"
]
},
{
"cell_type": "code",
"execution_count": 250,
"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 tr th {\n",
" text-align: left;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th colspan=\"2\" halign=\"left\">108年第3季</th>\n",
" <th colspan=\"2\" halign=\"left\">107年第3季</th>\n",
" <th colspan=\"2\" halign=\"left\">108年01月01日至108年09月30日</th>\n",
" <th colspan=\"2\" halign=\"left\">107年01月01日至107年09月30日</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>金額</th>\n",
" <th>%</th>\n",
" <th>金額</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>營業收入合計</th>\n",
" <td>1169889.0</td>\n",
" <td>100.00</td>\n",
" <td>1121587.0</td>\n",
" <td>100.00</td>\n",
" <td>3551356.0</td>\n",
" <td>100.0</td>\n",
" <td>3436442.0</td>\n",
" <td>100.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>營業成本合計</th>\n",
" <td>1035166.0</td>\n",
" <td>88.48</td>\n",
" <td>981554.0</td>\n",
" <td>87.51</td>\n",
" <td>3185655.0</td>\n",
" <td>89.7</td>\n",
" <td>3031159.0</td>\n",
" <td>88.21</td>\n",
" </tr>\n",
" <tr>\n",
" <th>營業毛利（毛損）</th>\n",
" <td>134723.0</td>\n",
" <td>11.52</td>\n",
" <td>140033.0</td>\n",
" <td>12.49</td>\n",
" <td>365701.0</td>\n",
" <td>10.3</td>\n",
" <td>405283.0</td>\n",
" <td>11.79</td>\n",
" </tr>\n",
" <tr>\n",
" <th>營業毛利（毛損）淨額</th>\n",
" <td>134723.0</td>\n",
" <td>11.52</td>\n",
" <td>140033.0</td>\n",
" <td>12.49</td>\n",
" <td>365701.0</td>\n",
" <td>10.3</td>\n",
" <td>405283.0</td>\n",
" <td>11.79</td>\n",
" </tr>\n",
" <tr>\n",
" <th>營業費用</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 108年第3季 107年第3季 108年01月01日至108年09月30日 \\\n",
" 金額 % 金額 % 金額 % \n",
"營業收入合計 1169889.0 100.00 1121587.0 100.00 3551356.0 100.0 \n",
"營業成本合計 1035166.0 88.48 981554.0 87.51 3185655.0 89.7 \n",
"營業毛利（毛損） 134723.0 11.52 140033.0 12.49 365701.0 10.3 \n",
"營業毛利（毛損）淨額 134723.0 11.52 140033.0 12.49 365701.0 10.3 \n",
"營業費用 NaN NaN NaN NaN NaN NaN \n",
"\n",
" 107年01月01日至107年09月30日 \n",
" 金額 % \n",
"營業收入合計 3436442.0 100.00 \n",
"營業成本合計 3031159.0 88.21 \n",
"營業毛利（毛損） 405283.0 11.79 \n",
"營業毛利（毛損）淨額 405283.0 11.79 \n",
"營業費用 NaN NaN "
]
},
"execution_count": 250,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"x.data.head()"
]
},
{
"cell_type": "code",
"execution_count": 251,
"metadata": {},
"outputs": [],
"source": [
"x.save()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# ifrs 前 / 綜合損益表"
]
},
{
"cell_type": "code",
"execution_count": 263,
"metadata": {},
"outputs": [],
"source": [
"x = TwStockIdvs(co_id='1104', year=2009, season=3, purpose='綜合損益表')\n",
"x.add_raw()\n",
"x.unify_incm()"
]
},
{
"cell_type": "code",
"execution_count": 264,
"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 tr th {\n",
" text-align: left;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th colspan=\"2\" halign=\"left\">97年09月30日</th>\n",
" <th colspan=\"2\" halign=\"left\">98年09月30日</th>\n",
" </tr>\n",
" <tr>\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>銷貨收入總額</th>\n",
" <td>4692142.00</td>\n",
" <td>100.36</td>\n",
" <td>6306614.00</td>\n",
" <td>100.14</td>\n",
" </tr>\n",
" <tr>\n",
" <th>銷貨折讓</th>\n",
" <td>17106.00</td>\n",
" <td>0.36</td>\n",
" <td>9368.00</td>\n",
" <td>0.14</td>\n",
" </tr>\n",
" <tr>\n",
" <th>銷貨收入淨額</th>\n",
" <td>4675036.00</td>\n",
" <td>100.00</td>\n",
" <td>6297246.00</td>\n",
" <td>100.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>營業收入合計</th>\n",
" <td>4675036.00</td>\n",
" <td>100.00</td>\n",
" <td>6297246.00</td>\n",
" <td>100.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>銷貨成本</th>\n",
" <td>4392465.00</td>\n",
" <td>93.95</td>\n",
" <td>5899492.00</td>\n",
" <td>93.68</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 97年09月30日 98年09月30日 \n",
" 金額 ％ 金額 ％\n",
"銷貨收入總額 4692142.00 100.36 6306614.00 100.14\n",
"銷貨折讓 17106.00 0.36 9368.00 0.14\n",
"銷貨收入淨額 4675036.00 100.00 6297246.00 100.00\n",
"營業收入合計 4675036.00 100.00 6297246.00 100.00\n",
"銷貨成本 4392465.00 93.95 5899492.00 93.68"
]
},
"execution_count": 264,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"x.data.head()"
]
},
{
"cell_type": "code",
"execution_count": 265,
"metadata": {},
"outputs": [],
"source": [
"x.save()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment