Skip to content

Instantly share code, notes, and snippets.

@rs6000

rs6000/big3trading_plus.ipynb

Created May 22, 2018 00:30
Show Gist options
  • Save rs6000/4c85faf6bbe330e33c5773fa59d6f050 to your computer and use it in GitHub Desktop.
Save rs6000/4c85faf6bbe330e33c5773fa59d6f050 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
big3trading_plus.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"#20180522\n",
"#多日交易紀錄寫入資料庫\n",
"#抓取三大法人買賣金額 by 大數學堂\n",
"#http://www.largitdata.com\n",
"\n",
"import pandas as pd\n",
"import time\n",
"import datetime\n",
"from datetime import date,timedelta\n",
"import sqlite3\n",
"\n",
"#今天日期\n",
"todayis=date.today()\n",
"\n",
"#source\n",
"url=\"http://www.twse.com.tw/fund/BFI82U?response=html&dayDate={0}\"\n",
"\n",
"def getTradeValue(dt):\n",
" \n",
" dfs=pd.read_html(url.format(dt))\n",
" dfs[0].columns = dfs[0].columns.droplevel()\n",
" new_dfs=dfs[0]\n",
" new_dfs['Date']=todayis.strftime('%Y-%m-%d')\n",
" new_dfs['Date']=pd.to_datetime(new_dfs['Date'])\n",
" #寫入資料庫\n",
" with sqlite3.connect('big3trade_plus.sqlite') as db:\n",
" new_dfs.to_sql('new_dfs',con=db,if_exists='append')\n",
" #印出\n",
" #print(new_dfs)\n",
"\n",
"#在range(1,10) 10就是要抓取的天數，可自行修改\n",
"for i in range(1,10):\n",
" todayis=todayis+timedelta(days=-1)\n",
" week= datetime.datetime.strptime(str(todayis),\"%Y-%m-%d\").isoweekday()\n",
" if week <6:\n",
" try:\n",
" dayary=str(todayis).replace('-','')\n",
" time.sleep(4)\n",
" getTradeValue(dayary)\n",
" except:\n",
" pass\n",
" else:\n",
" pass"
]
},
{
"cell_type": "code",
"execution_count": 2,
"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>index</th>\n",
" <th>單位名稱</th>\n",
" <th>買進金額</th>\n",
" <th>賣出金額</th>\n",
" <th>買賣差額</th>\n",
" <th>Date</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>0</td>\n",
" <td>自營商(自行買賣)</td>\n",
" <td>1834297450</td>\n",
" <td>1196758890</td>\n",
" <td>637538560</td>\n",
" <td>2018-05-21 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>1</td>\n",
" <td>自營商(避險)</td>\n",
" <td>6918153413</td>\n",
" <td>6983813793</td>\n",
" <td>-65660380</td>\n",
" <td>2018-05-21 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2</td>\n",
" <td>投信</td>\n",
" <td>1035000420</td>\n",
" <td>1406119730</td>\n",
" <td>-371119310</td>\n",
" <td>2018-05-21 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>3</td>\n",
" <td>外資及陸資</td>\n",
" <td>27354763942</td>\n",
" <td>24267983920</td>\n",
" <td>3086780022</td>\n",
" <td>2018-05-21 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>4</td>\n",
" <td>合計</td>\n",
" <td>37142215225</td>\n",
" <td>33854676333</td>\n",
" <td>3287538892</td>\n",
" <td>2018-05-21 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>0</td>\n",
" <td>自營商(自行買賣)</td>\n",
" <td>1370031100</td>\n",
" <td>1365738030</td>\n",
" <td>4293070</td>\n",
" <td>2018-05-18 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>1</td>\n",
" <td>自營商(避險)</td>\n",
" <td>5779471161</td>\n",
" <td>5847991887</td>\n",
" <td>-68520726</td>\n",
" <td>2018-05-18 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2</td>\n",
" <td>投信</td>\n",
" <td>1128062990</td>\n",
" <td>2082651184</td>\n",
" <td>-954588194</td>\n",
" <td>2018-05-18 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>3</td>\n",
" <td>外資及陸資</td>\n",
" <td>29927358002</td>\n",
" <td>29145877869</td>\n",
" <td>781480133</td>\n",
" <td>2018-05-18 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>4</td>\n",
" <td>合計</td>\n",
" <td>38204923253</td>\n",
" <td>38442258970</td>\n",
" <td>-237335717</td>\n",
" <td>2018-05-18 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>0</td>\n",
" <td>自營商(自行買賣)</td>\n",
" <td>1937490360</td>\n",
" <td>1560124016</td>\n",
" <td>377366344</td>\n",
" <td>2018-05-17 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>1</td>\n",
" <td>自營商(避險)</td>\n",
" <td>5353949133</td>\n",
" <td>6566869697</td>\n",
" <td>-1212920564</td>\n",
" <td>2018-05-17 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>2</td>\n",
" <td>投信</td>\n",
" <td>1250658550</td>\n",
" <td>1476746788</td>\n",
" <td>-226088238</td>\n",
" <td>2018-05-17 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>3</td>\n",
" <td>外資及陸資</td>\n",
" <td>29135388160</td>\n",
" <td>32433020335</td>\n",
" <td>-3297632175</td>\n",
" <td>2018-05-17 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>4</td>\n",
" <td>合計</td>\n",
" <td>37677486203</td>\n",
" <td>42036760836</td>\n",
" <td>-4359274633</td>\n",
" <td>2018-05-17 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>0</td>\n",
" <td>自營商(自行買賣)</td>\n",
" <td>1747148420</td>\n",
" <td>2300657992</td>\n",
" <td>-553509572</td>\n",
" <td>2018-05-16 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>1</td>\n",
" <td>自營商(避險)</td>\n",
" <td>5940920068</td>\n",
" <td>6680530000</td>\n",
" <td>-739609932</td>\n",
" <td>2018-05-16 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>2</td>\n",
" <td>投信</td>\n",
" <td>1313398320</td>\n",
" <td>2085051983</td>\n",
" <td>-771653663</td>\n",
" <td>2018-05-16 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>3</td>\n",
" <td>外資及陸資</td>\n",
" <td>30524448392</td>\n",
" <td>30188625251</td>\n",
" <td>335823141</td>\n",
" <td>2018-05-16 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>4</td>\n",
" <td>合計</td>\n",
" <td>39525915200</td>\n",
" <td>41254865226</td>\n",
" <td>-1728950026</td>\n",
" <td>2018-05-16 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>0</td>\n",
" <td>自營商(自行買賣)</td>\n",
" <td>1931944250</td>\n",
" <td>1827963871</td>\n",
" <td>103980379</td>\n",
" <td>2018-05-15 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>1</td>\n",
" <td>自營商(避險)</td>\n",
" <td>5920399308</td>\n",
" <td>7017742332</td>\n",
" <td>-1097343024</td>\n",
" <td>2018-05-15 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>2</td>\n",
" <td>投信</td>\n",
" <td>1557789890</td>\n",
" <td>1719789608</td>\n",
" <td>-161999718</td>\n",
" <td>2018-05-15 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>3</td>\n",
" <td>外資及陸資</td>\n",
" <td>34018838895</td>\n",
" <td>34931873914</td>\n",
" <td>-913035019</td>\n",
" <td>2018-05-15 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>4</td>\n",
" <td>合計</td>\n",
" <td>43428972343</td>\n",
" <td>45497369725</td>\n",
" <td>-2068397382</td>\n",
" <td>2018-05-15 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>0</td>\n",
" <td>自營商(自行買賣)</td>\n",
" <td>1815420010</td>\n",
" <td>1237173123</td>\n",
" <td>578246887</td>\n",
" <td>2018-05-14 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>1</td>\n",
" <td>自營商(避險)</td>\n",
" <td>6823080678</td>\n",
" <td>6298962049</td>\n",
" <td>524118629</td>\n",
" <td>2018-05-14 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>2</td>\n",
" <td>投信</td>\n",
" <td>1931464110</td>\n",
" <td>1963183478</td>\n",
" <td>-31719368</td>\n",
" <td>2018-05-14 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>3</td>\n",
" <td>外資及陸資</td>\n",
" <td>35432726127</td>\n",
" <td>26856176974</td>\n",
" <td>8576549153</td>\n",
" <td>2018-05-14 00:00:00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>4</td>\n",
" <td>合計</td>\n",
" <td>46002690925</td>\n",
" <td>36355495624</td>\n",
" <td>9647195301</td>\n",
" <td>2018-05-14 00:00:00</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" index 單位名稱 買進金額 賣出金額 買賣差額 \\\n",
"0 0 自營商(自行買賣) 1834297450 1196758890 637538560 \n",
"1 1 自營商(避險) 6918153413 6983813793 -65660380 \n",
"2 2 投信 1035000420 1406119730 -371119310 \n",
"3 3 外資及陸資 27354763942 24267983920 3086780022 \n",
"4 4 合計 37142215225 33854676333 3287538892 \n",
"5 0 自營商(自行買賣) 1370031100 1365738030 4293070 \n",
"6 1 自營商(避險) 5779471161 5847991887 -68520726 \n",
"7 2 投信 1128062990 2082651184 -954588194 \n",
"8 3 外資及陸資 29927358002 29145877869 781480133 \n",
"9 4 合計 38204923253 38442258970 -237335717 \n",
"10 0 自營商(自行買賣) 1937490360 1560124016 377366344 \n",
"11 1 自營商(避險) 5353949133 6566869697 -1212920564 \n",
"12 2 投信 1250658550 1476746788 -226088238 \n",
"13 3 外資及陸資 29135388160 32433020335 -3297632175 \n",
"14 4 合計 37677486203 42036760836 -4359274633 \n",
"15 0 自營商(自行買賣) 1747148420 2300657992 -553509572 \n",
"16 1 自營商(避險) 5940920068 6680530000 -739609932 \n",
"17 2 投信 1313398320 2085051983 -771653663 \n",
"18 3 外資及陸資 30524448392 30188625251 335823141 \n",
"19 4 合計 39525915200 41254865226 -1728950026 \n",
"20 0 自營商(自行買賣) 1931944250 1827963871 103980379 \n",
"21 1 自營商(避險) 5920399308 7017742332 -1097343024 \n",
"22 2 投信 1557789890 1719789608 -161999718 \n",
"23 3 外資及陸資 34018838895 34931873914 -913035019 \n",
"24 4 合計 43428972343 45497369725 -2068397382 \n",
"25 0 自營商(自行買賣) 1815420010 1237173123 578246887 \n",
"26 1 自營商(避險) 6823080678 6298962049 524118629 \n",
"27 2 投信 1931464110 1963183478 -31719368 \n",
"28 3 外資及陸資 35432726127 26856176974 8576549153 \n",
"29 4 合計 46002690925 36355495624 9647195301 \n",
"\n",
" Date \n",
"0 2018-05-21 00:00:00 \n",
"1 2018-05-21 00:00:00 \n",
"2 2018-05-21 00:00:00 \n",
"3 2018-05-21 00:00:00 \n",
"4 2018-05-21 00:00:00 \n",
"5 2018-05-18 00:00:00 \n",
"6 2018-05-18 00:00:00 \n",
"7 2018-05-18 00:00:00 \n",
"8 2018-05-18 00:00:00 \n",
"9 2018-05-18 00:00:00 \n",
"10 2018-05-17 00:00:00 \n",
"11 2018-05-17 00:00:00 \n",
"12 2018-05-17 00:00:00 \n",
"13 2018-05-17 00:00:00 \n",
"14 2018-05-17 00:00:00 \n",
"15 2018-05-16 00:00:00 \n",
"16 2018-05-16 00:00:00 \n",
"17 2018-05-16 00:00:00 \n",
"18 2018-05-16 00:00:00 \n",
"19 2018-05-16 00:00:00 \n",
"20 2018-05-15 00:00:00 \n",
"21 2018-05-15 00:00:00 \n",
"22 2018-05-15 00:00:00 \n",
"23 2018-05-15 00:00:00 \n",
"24 2018-05-15 00:00:00 \n",
"25 2018-05-14 00:00:00 \n",
"26 2018-05-14 00:00:00 \n",
"27 2018-05-14 00:00:00 \n",
"28 2018-05-14 00:00:00 \n",
"29 2018-05-14 00:00:00 "
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#讀檔(在jupyter上直接看)\n",
"with sqlite3.connect('big3trade_plus.sqlite') as db:\n",
" df=pd.read_sql_query('select * from new_dfs', con=db)\n",
"df"
]
}
],
"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.5.2"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment