python 融資融券彙總 (全部)"資料(頁 1) - 程式設計不分區

lpk187 發表於 2016-9-22 16:22

python 融資融券彙總 (全部)"資料

還不會製作執行檔！其內容僅供參考之[code]#-*- coding:utf-8 -*-

'''
本程序由 Python 3.52 寫成，為作者參考麻辣家族討論區 "python上市櫃三大法人買賣超日報資料下載" 討論內容練習
其內容大部份參考 zyzzyva大大、c_c_lai 大大、koshi0413 之討論內容，感謝！
本程序會自動生成 Access 的 twsedata.mdb資料庫，及建立各所需之資料表，但只能下載"融資融券彙總 (全部)"資料
ㄟ！沒有信用交易統計資料，只建立其資料表
本程序需要 pip 安裝 requests 、BeautifulSoup4、pypyodbc
以及需下載 pywin32 : https://sourceforge.net/projects/pywin32/files/pywin32/Build%20220/
使用方法：只在 input 時，輸入開始擷取日期及結束擷取日期即可，第一次執行時，會建構資料庫，而後只增加資料，
每日資料擷取到儲存至資料庫的時間約 21秒(依作者電腦執行計時 )
本程序僅供參考，不負責更新內容，不負責除錯(因為我也是新手，除錯對我來說太#%%$...)，搞壞使用者電腦，也不關作者的事！！
作者：麻辣家族討論區 lpk187
完成日期：2016/9/22
'''

import requests
from bs4 import BeautifulSoup
import random
import os
import win32com.client
import pypyodbc
import datetime
import time

# 隨機更改 headers
def head_random():
   for i in range(10):
         hs = ['Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
               'Mozilla/5.0 (iPhone; U; CPU iPhone OS 3_0 like Mac OS X; en-us) AppleWebKit/528.18 (KHTML, like Gecko) Version/4.0 Mobile/7A341 Safari/528.16',
               'Mozilla/5.0 (Linux; U; Android 4.1.2; zh-tw; GT-I9300 Build/JZO54K) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30',
               'Mozilla/5.0 (iPad; U; CPU OS 3_2 like Mac OS X; en-us) AppleWebKit/531.21.10 (KHTML, like Gecko) Version/4.0.4 Mobile/7B334b Safari/531.21.10'
               'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0)']
         hes = {"User-Agent":random.choice(hs)}
   return hes

# 集台證所融資融券餘額全部資料
def read_network_data(date,from_number):
   url = 'http://www.twse.com.tw/ch/trading/exchange/MI_MARGN/MI_MARGN.php'
   head =head_random()
   payload = {"download":'',
               "qdate":date,
               "selectType":"ALL"}
   res = requests.post(url, headers=head, data=payload)
   soup=BeautifulSoup(res.text, "lxml")
   tab = soup.select('tbody')[from_number] # 2個表格之2
   regs=[] # 初始陣列

   for i in range(len(tab.select('tr')[0].select('td'))): # 定義2維陣列維度
         regs.append([])

   for tr in tab.select('tr'): #擷取表格資料
         for i in range(len(tab.select('tr')[0].select('td'))):
               regs[i].append(tr.select('td')[i].text)
   return regs

def conn_db():
   Conn = win32com.client.Dispatch("ADODB.Connection")
   strMDB ='Provider=Microsoft.ACE.OLEDB.12.0; Data Source=' + os.getcwd() + '\\twsedata.mdb;'
   Conn.ConnectionString = strMDB
   Conn.Open()
   return Conn

def check_db_exists(regs):
   if not os.path.exists('twsedata.mdb'):
         pypyodbc.win_create_mdb( os.getcwd() + '\\twsedata.mdb')
         Conn=conn_db()
         sql = "CREATE TABLE 股票(股票代號  char(10) PRIMARY KEY,股票名稱  char(20));"
         Conn.Execute(sql)
         sql = "CREATE TABLE  融資融券信用交易統計(日期  datetime,項目  char(20),買進 INTEGER,買出 INTEGER,現金（券）償還 INTEGER,前日餘額 INTEGER,今日餘額 INTEGER);"
         Conn.Execute(sql)
   n = 0
   Conn=conn_db()
   for i in regs[0]:
         try:
               Conn.Execute('select * from ' + i); # table does not exist
         except Exception as e:
               sql="CREATE TABLE " + i + "(日期  datetime,"
               sql= sql + "資買進  INTEGER,"
               sql= sql + "資賣出 INTEGER,"
               sql= sql + "資現金償還 INTEGER,"
               sql= sql + "資前日餘額 INTEGER,"
               sql= sql + "資今日餘額 INTEGER,"
               sql= sql + "資限額 INTEGER,"
               sql= sql + "券買進  INTEGER,"
               sql= sql + "券賣出 INTEGER,"
               sql= sql + "券現金償還 INTEGER,"
               sql= sql + "券前日餘額 INTEGER,"
               sql= sql + "券今日餘額 INTEGER,"
               sql= sql + "券限額 INTEGER,"
               sql= sql + "資券互抵 INTEGER,"
               sql= sql + "註記 char(5));"
               Conn.Execute(sql)
               rs = win32com.client.Dispatch('ADODB.RecordSet')
               rs.ActiveConnection = Conn
               rs.Open('select * from 股票', Conn, 3, 3)
               rs.AddNew()
               rs.Fields.Item(0).Value = regs[0][n]
               rs.Fields.Item(1).Value = regs[1][n]
               rs.Update()
         n += 1

def add_data(regs,date):
   Conn=conn_db()
   for i in range(len(regs[0])):
         rs = win32com.client.Dispatch('ADODB.RecordSet')
         rs.ActiveConnection = Conn
         sql='select * from ' + regs[0][i]
         rs.Open(sql, Conn, 3, 3)
         for j in range(2,len(regs)):
               if j==2:
                     rs1 = win32com.client.Dispatch('ADODB.RecordSet')
                     rs1.ActiveConnection = Conn
                     sql="SELECT * FROM " + regs[0][i] + " WHERE 日期 = #" + date + "#;"
                     rs1.Open(sql,Conn,3,3)
                     if rs1.EOF:
                           rs.AddNew()
                           rs.Fields.Item(0).Value = date
                           rs.Fields.Item(j-1).Value = regs[j][i]
                           rs1.Close()
                     else:
                           rs1.Close()
                           break
               rs.Fields.Item(j-1).Value = regs[j][i]
         rs.Update()

# 以下為程式的啟始點：
StartDate=input('請輸入開始擷取日期(西元日期如：2016/1/1)：')
EndDate=input('請輸入結束擷取日期(西元日期如：2016/1/31)：')
#處理日期還真的很麻煩
ts=time.time()
StartDate = time.strptime(StartDate, "%Y/%m/%d")
EndDate =time.strptime(EndDate, "%Y/%m/%d")
StartDate = datetime.date(StartDate[0], StartDate[1], StartDate[2])
EndDate =  datetime.date(EndDate[0], EndDate[1], EndDate[2])
RangeDate = datetime.timedelta(days = 1)
while StartDate <= EndDate:
   yy,mm,dd=str(StartDate).split('-')
   dat=datetime.datetime(int(yy), int(mm), int(dd))
   dd=dat.strftime('%Y/%m/%d')
   year=str(int(dd[0:4])-1911)
   date=dd.replace(dd[0:4], year)
   print('目前執行網路擷取',date ,'日的資料，請稍後...')
   re=read_network_data(date,1)
   if re[0][0]=='查無資料':
         print(date,'日，可能為休市日，查無資料。')
         StartDate = StartDate + RangeDate
         continue
   print('檢查是否有新股票加入，請稍後...')
   check_db_exists(regs=re)
   print('將資料寫入資料庫中，請稍後...')
   add_data(regs=re,date=dd)
   StartDate = StartDate + RangeDate
   te = time.time()
   print('截至目前，時間已耗費：', int(te - ts),'秒')

print('執行完畢！')

[/code]

c_c_lai 發表於 2016-9-22 20:11

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94094&ptid=18396]1#[/url] [i]lpk187[/i] [/b]
太棒了！
先來試試看，謝謝囉！

c_c_lai 發表於 2016-9-22 21:13

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94094&ptid=18396]1#[/url] [i]lpk187[/i] [/b]
請問 pypyodbc 如何下再安裝？
我安裝了 Anaconda 內附的 pyodbc 結果還是一樣。
[attach]25299[/attach]

lpk187 發表於 2016-9-22 22:00

[i=s] 本帖最後由 lpk187 於 2016-9-22 22:04 編輯 [/i]

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94099&ptid=18396]3#[/url] [i]c_c_lai[/i] [/b]

pypyodbc 和pyodbc是不同的，要下載pypyodbc 還是要在CMD 中下載
pip install pypyodbc

[attach]25300[/attach]

pypyodbc 其用法我也不會，不過當時只看到一句指令就可以產生資料庫，所以我就下載了
其語法為 pypyodbc.win_create_mdb( os.getcwd() + '\\twsedata.mdb')

lpk187 發表於 2016-9-22 22:33

以下是我執行的過程：
[attach]25305[/attach]

[attach]25306[/attach]

[attach]25307[/attach]

[attach]25308[/attach]

koshi0413 發表於 2016-9-22 23:46

[i=s] 本帖最後由 koshi0413 於 2016-9-23 00:00 編輯 [/i]

厲害，小弟學習先，有問題在發問~

對了，先提問一下 acc 的資料查詢速度跟 sqlite3 比較一下，何者快呢？

小弟是程式新手，所以什麼都不懂，會亂發問，請見諒嘿~~

lpk187 發表於 2016-9-23 00:26

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94103&ptid=18396]6#[/url] [i]koshi0413[/i] [/b]

查詢速度，應該和資料庫本身有很大的關係！Access本身，就只是個人電腦所使用的小小資料庫，
當然要處理像股市資訊的話，我不會推薦用Access ，雖然微軟號稱可達極限為2G，雖然我沒試過達到2G極限值的速度，
不過依我想大概算它10%好了，其速度大約就會有明顯的差異了。
還有各方模塊，都有其優勢，當我正在學習python時，有某個模塊的某函數比另外模塊的函數強時，我會用這個模塊，
當然，這只是指我目前正在學習階段的想法，(因為還不是很瞭解其函數用法)

c_c_lai 發表於 2016-9-23 07:59

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94100&ptid=18396]4#[/url] [i]lpk187[/i] [/b]
Thanks a lot!
[attach]25309[/attach]

zyzzyva 發表於 2016-9-23 08:50

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94094&ptid=18396]1#[/url] [i]lpk187[/i] [/b]
哇，最近也想研究一下資料庫，剛好L大就發了這篇，3Q~

c_c_lai 發表於 2016-9-24 10:31

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94102&ptid=18396]5#[/url] [i]lpk187[/i] [/b]
昨天測了一下，從 2016/1/1 ~ 2016/9/22，
結果發現每筆資料只寫入到 2016/8/29。
這是正確嗎？
[attach]25326[/attach]
[attach]25327[/attach]
這實在是很棒的實用範例，再次謝謝你的分享！

lpk187 發表於 2016-9-24 12:53

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94130&ptid=18396]10#[/url] [i]c_c_lai[/i] [/b]

[attach]25329[/attach]

c_c_lai 發表於 2016-9-24 13:00

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94134&ptid=18396]11#[/url] [i]lpk187[/i] [/b]
我再試試看 !
(試著先將資料庫移除、在執行)>

c_c_lai 發表於 2016-9-25 10:16

[i=s] 本帖最後由 c_c_lai 於 2016-9-25 10:21 編輯 [/i]

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94134&ptid=18396]11#[/url] [i]lpk187[/i] [/b]
重新測試結果：
[attach]25339[/attach]
謝謝你！
如果要從資料庫讀取出資料又該如何撰寫，
譬如同步'寫入 pandas 裡？或是其他方式？

lpk187 發表於 2016-9-25 11:19

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94169&ptid=18396]13#[/url] [i]c_c_lai[/i] [/b]

在python 中，我也還是在學習，所以知道的甚少
要從資料庫中拿出來，以Pywin32 ADODB的用法和VBA很類似

你可以參考[url]http://wiki.alarmchang.com/index.php?title=Python[/url] 裡面有很多例子參考
甚至控制Excel都可以，就要看從資料庫中提取出來後要做什麼(因為我沒玩股票，也不知道這些數據該用什麼？這問題可能要請教各位大大了)

koshi0413 發表於 2016-9-25 14:25

[i=s] 本帖最後由 koshi0413 於 2016-9-25 14:30 編輯 [/i]

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94169&ptid=18396]13#[/url] [i]c_c_lai[/i] [/b]

給您參考  SQLite3 導入 Excel
acc作法應該也差不多

注意!!! 這是一次全導入，所以資料量太大，會很慢
應該可以導入局部，只是小弟尚未用到，還沒深入[code]Sub SQLite3_to_Excel()
印像中可以在 "SELECT  *  FROM  " & TableName & " "  加入只提取十行

Dim cn, rs, f, SQLName, TableName, ix%, SheetsName

SQLName = "C:\Users\Koshi\Documents\Python Scripts\SQLite3\2330.sqlite"
TableName = "sql表格名稱"
SheetsName = "Excel工作表名稱"
ix = 10 '開始於第十行

Sheets(SheetsName).Select
Set cn = CreateObject("adodb.connection")
      cn.Open ("Driver={SQLite3 ODBC Driver};database=" & SQLName)    '開啟sqlite指定資料庫，路徑與檔案名稱要對
      Set rs = cn.Execute("SELECT  *  FROM  " & TableName & " ")    '寫出SQL查詢語法
         Sheets(SheetsName).Cells.Delete  '清除工作表資料
         For f = 0 To rs.Fields.Count - 1
            Sheets(SheetsName).Cells(ix, f + 1).Value = rs.Fields(f).Name '導入欄位名稱
         Next
         Sheets(SheetsName).Cells(ix + 1, 1).CopyFromRecordset (rs)       '導入欄位資料
      cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub[/code]

koshi0413 發表於 2016-9-25 16:07

[quote]回復 c_c_lai

給您參考 SQLite3 導入 Excel
acc作法應該也差不多

注意!!! 這是一次全導入，所以資 ...
[size=2][color=#999999]koshi0413 發表於 2016-9-25 14:25[/color] [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94179&ptid=18396][img]http://forum.twbts.com/images/common/back.gif[/img][/url][/size][/quote]

找到了此代碼為 joey0415 大大寫的
[url]http://forum.twbts.com/viewthread.php?tid=9984[/url]

lpk187 發表於 2016-9-25 17:20

[i=s] 本帖最後由 lpk187 於 2016-9-25 17:22 編輯 [/i]

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94169&ptid=18396]13#[/url] [i]c_c_lai[/i] [/b]

研究了一下 pandas 取出再導入到CSV 或Excel[code]import pypyodbc
import pandas as pd

MDB = 'D:\\Python\\融資融券彙總\\twsedata.mdb'
DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
con = pypyodbc.connect('DRIVER={};DBQ={}'.format(DRV, MDB))
data = pd.read_sql('select * from 1220', con) # 取出
data.to_excel('1220.xlsx', sheet_name='1220') #導入到Excel
data.to_csv('1220.csv') #導入到 CSV
data[/code]

c_c_lai 發表於 2016-9-26 15:42

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94187&ptid=18396]17#[/url] [i]lpk187[/i] [/b]
測試結果如下：
[attach]25352[/attach]
從多個角度來測試，實在太棒了。
謝謝囉！

c_c_lai 發表於 2016-9-27 10:16

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94187&ptid=18396]17#[/url] [i]lpk187[/i] [/b]
請教如果換成 15# 樓 koshi0413 所說的 SQLite3
要如何連結？

lpk187 發表於 2016-9-27 11:18

[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=94225&ptid=18396]19#[/url] [i]c_c_lai[/i] [/b]

不好意思！沒用過SQLite3，不知怎麼回答你
你可以參考[url]http://www.runoob.com/sqlite/sqlite-python.html[/url]
以及[url]https://docs.python.org/3.5/library/sqlite3.html[/url]

頁: [1] 2 3

麻辣家族討論版版's Archiver

python 融資融券彙總 (全部)"資料