[心得] 抓股票公開資料 Excel v1.3

看板Stock作者時間6年前 (2017/11/24 20:20), 6年前編輯推噓56(56推 0噓 5→)
留言61則, 58人參與, 6年前最新討論串1/1
網誌好讀版： http://kslman.blogspot.tw/2017/11/excalvbav13.html -- 插入股票公開資料的檔案我做了更新，供大家參考： stock_sample_v1.3.xlsm https://drive.google.com/open?id=1g5saowjYAeXNi73a0pss96-xZeXPUTXr 使用方式： 1. 「關注」的分頁C列填入股票代碼。 2. 點擊「關注」分頁左上「refresh」按鈕就可以刷新全部。 （判斷方式是16:00以前只刷新興櫃，16:00以後則全部更新。如果想假日更新最新的前一 交易日，可以改TWN分頁的A10~A12） 2017/12/1補充：我觀察交易日14:00上市上櫃也都有資料了，有需要可以自己改成14:00 調整內容： 1. 新增更新全部分頁的方式 2. 修正了櫃買中心的興櫃csv連結為： http://www.gretai.org.tw/storage/emgstk/ch/new.csv 目前問題： 1. 興櫃股票我抓的政府資料開放平臺的資料沒有前一天價格，所以沒有辦法算漲跌、漲 跌幅和昨收。（如果有人知道哪邊抓的資料有漲跌或前一天價格可以跟我說一下） 2. P/E只有上市有。 3. 美股不知道哪邊有資料，有人知道那邊有美股類似證交所這樣一個表有全部股價資料 的網站嗎？ 新增的巨集內容如下。 Private Sub CommandButton1_Click() Sheets("TWN").Select '宣告變數 Dim QuerySheet As Worksheet Dim DataSheet As Worksheet Dim qurl As String Dim NOW, BN, LTH As Long NOW = Sheets("TWN").Range("A4") BN = Sheets("TWN").Range("A9") LTH = Sheets("TWN").Range("A8") '告訴Excel不要每更新一格就重新計算 Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual '將現在的工作表設為資料表 Set DataSheet = ActiveSheet qurl = "http://www.gretai.org.tw/storage/emgstk/ch/new.csv" '選擇TWN sheet Sheets("TWN").Range("B:Z").Clear If NOW >= BN Then If LTH < 16 Then '如果時間是16:00之前 '抓取資料（TWN sheet） QueryQuote: With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .RefreshStyle = xlInsertEntireRows .Delete End With '讓Excel重新活回來，讓資料能夠顯示 Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True '切數據（TWN sheet） Sheets("TWN").Columns("B:B").Select Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _ TrailingMinusNumbers:=True '否則（如果時間是16:00之後） Else '抓取資料（TWN sheet） QueryQuote2: With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .RefreshStyle = xlInsertEntireRows .Delete End With '讓Excel重新活回來，讓資料能夠顯示 Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True '切數據（TWN sheet） Sheets("TWN").Columns("B:B").Select Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _ TrailingMinusNumbers:=True '更新上一交易日（TWN sheet的BN變數） Sheets("TWN").Range("A4:A7").Select Selection.Copy Sheets("TWN").Range("A9").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False '選擇TWO sheet Sheets("TWO").Select '告訴Excel不要每更新一格就重新計算 Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual '將現在的工作表設為資料表 qurl = "http://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&d=" + Sheets("TWO").Range("A9") + "/" + Sheets("TWO").Range("A10") + "/" + Sheets("TWO").Range("A11") + "&se=EW&s=0,asc,0" Sheets("TWO").Range("B:Z").Clear '抓取資料（TWO） QueryQuote3: With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("TWO").Range("B1")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .RefreshStyle = xlInsertEntireRows .Delete End With '讓Excel重新活回來，讓資料能夠顯示 Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True '選擇TW sheet Sheets("TW").Select '將現在的工作表設為資料表 Set DataSheet = ActiveSheet qurl = "http://www.tse.com.tw/exchangeReport/MI_INDEX?response=csv&date=" + Sheets("TW").Range("A9") + Sheets("TW").Range("A10") + Sheets("TW").Range("A11") + "&type=ALLBUT0999" Sheets("TW").Range("B:Z").Clear '抓取資料（TW sheet） QueryQuote4: With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1")) .BackgroundQuery = True .TablesOnlyFromHTML = False .Refresh BackgroundQuery:=False .SaveData = True .RefreshStyle = xlInsertEntireRows .Delete End With '讓Excel重新活回來，讓資料能夠顯示 Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True '切數據（TW sheet） Sheets("TW").Columns("B:B").Select Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _ TrailingMinusNumbers:=True End If End If '選擇關注 sheet Sheets("關注").Select End Sub -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 118.163.68.55 ※ 文章網址: https://www.ptt.cc/bbs/Stock/M.1511526020.A.C9C.html
→
11/24 20:21, 6年前 , 1F
補充，前一個版本可參考 #1Q0noLYE (Stock)
11/24 20:21, 1F
→
11/24 20:33, 6年前 , 2F
先幫推
11/24 20:33, 2F
推
11/24 20:34, 6年前 , 3F
先推 謝大大
11/24 20:34, 3F
推
11/24 20:38, 6年前 , 4F
推
11/24 20:38, 4F
推
11/24 20:44, 6年前 , 5F
謝謝
11/24 20:44, 5F
推
11/24 20:53, 6年前 , 6F
樓主好人 一生平安
11/24 20:53, 6F
推
11/24 20:55, 6年前 , 7F
推
11/24 20:55, 7F
推
11/24 20:57, 6年前 , 8F
厲害
11/24 20:57, 8F
推
11/24 20:58, 6年前 , 9F
感謝
11/24 20:58, 9F
推
11/24 21:08, 6年前 , 10F
樓主好人
11/24 21:08, 10F
推
11/24 21:12, 6年前 , 11F
推一個
11/24 21:12, 11F
推
11/24 21:17, 6年前 , 12F
推
11/24 21:17, 12F
推
11/24 21:19, 6年前 , 13F
推
11/24 21:19, 13F
推
11/24 21:25, 6年前 , 14F
感動推
11/24 21:25, 14F
推
11/24 21:29, 6年前 , 15F
厲害
11/24 21:29, 15F
推
11/24 21:36, 6年前 , 16F
推推
11/24 21:36, 16F
推
11/24 21:41, 6年前 , 17F
神
11/24 21:41, 17F
推
11/24 21:42, 6年前 , 18F
先推
11/24 21:42, 18F
推
11/24 21:55, 6年前 , 19F
推
11/24 21:55, 19F
推
11/24 21:57, 6年前 , 20F
感動推
11/24 21:57, 20F
推
11/24 22:00, 6年前 , 21F
11/24 22:00, 21F
推
11/24 22:05, 6年前 , 22F
推
11/24 22:05, 22F
推
11/24 22:08, 6年前 , 23F
多謝好心人
11/24 22:08, 23F
推
11/24 22:14, 6年前 , 24F
推
11/24 22:14, 24F
推
11/24 22:35, 6年前 , 25F
推。謝謝你
11/24 22:35, 25F
推
11/24 22:37, 6年前 , 26F
大推.好人
11/24 22:37, 26F
推
11/24 22:41, 6年前 , 27F
推好心!
11/24 22:41, 27F
推
11/24 23:00, 6年前 , 28F
感謝分享
11/24 23:00, 28F
推
11/24 23:05, 6年前 , 29F
感謝分享~~
11/24 23:05, 29F
→
11/24 23:12, 6年前 , 30F
推謝謝～
11/24 23:12, 30F
推
11/24 23:15, 6年前 , 31F
推
11/24 23:15, 31F
推
11/24 23:16, 6年前 , 32F
好文必推
11/24 23:16, 32F
推
11/24 23:37, 6年前 , 33F
有神必推!!!
11/24 23:37, 33F
推
11/24 23:41, 6年前 , 34F
好強大 不過看不懂Q_Q
11/24 23:41, 34F
推
11/24 23:45, 6年前 , 35F
貪心的問一下請問現金股利跟殖利率怎麼讓他跑出來..
11/24 23:45, 35F
→
11/24 23:45, 6年前 , 36F
.輸入代碼之後後面沒顯示
11/24 23:45, 36F
這個有點誤會了，這邊要手動填的，後面沒自動抓數字的是我自己筆記常用的公式範本 這邊只是方便手動填入股利和時間後，可以拉公式算殖利率和注意到那些時間快到了
推
11/24 23:47, 6年前 , 37F
推
11/24 23:47, 37F
推
11/25 00:06, 6年前 , 38F
推
11/25 00:06, 38F
推
11/25 00:23, 6年前 , 39F
讚讚讚
11/25 00:23, 39F
推
11/25 00:29, 6年前 , 40F
感恩大大
11/25 00:29, 40F
※ 編輯: kslman (218.187.82.172), 11/25/2017 01:09:57
推
11/25 07:57, 6年前 , 41F
好用心
11/25 07:57, 41F
推
11/25 08:18, 6年前 , 42F
股海良心
11/25 08:18, 42F
推
11/25 08:55, 6年前 , 43F
推
11/25 08:55, 43F
推
11/25 11:37, 6年前 , 44F
推
11/25 11:37, 44F
推
11/25 12:35, 6年前 , 45F
讚
11/25 12:35, 45F
推
11/25 15:26, 6年前 , 46F
推一個
11/25 15:26, 46F
推
11/25 17:48, 6年前 , 47F
厲害
11/25 17:48, 47F
推
11/25 18:29, 6年前 , 48F
推
11/25 18:29, 48F
推
11/25 20:01, 6年前 , 49F
有下有推 感謝
11/25 20:01, 49F
推
11/25 20:29, 6年前 , 50F
太感謝了！
11/25 20:29, 50F
推
11/25 23:03, 6年前 , 51F
有下有推!! 謝謝~~
11/25 23:03, 51F
推
11/25 23:07, 6年前 , 52F
感謝大大
11/25 23:07, 52F
推
11/26 08:02, 6年前 , 53F
推
11/26 08:02, 53F
推
11/26 11:08, 6年前 , 54F
祝福好人大大一生平安~
11/26 11:08, 54F
推
11/26 14:54, 6年前 , 55F
感謝大大讚嘆大大
11/26 14:54, 55F
推
11/26 20:55, 6年前 , 56F
推謝
11/26 20:55, 56F
推
11/26 22:10, 6年前 , 57F
推分享！
11/26 22:10, 57F
推
11/27 01:43, 6年前 , 58F
推一個
11/27 01:43, 58F
推
11/27 14:51, 6年前 , 59F
謝謝分享~
11/27 14:51, 59F
推
11/28 23:24, 6年前 , 60F
大大太厲害了，想請問是不是興櫃才會更新日期呢??我
11/28 23:24, 60F
→
11/28 23:25, 6年前 , 61F
看興櫃的有()TODAY指令，但上市跟上櫃的則沒有
11/28 23:25, 61F
這個邏輯是這樣的。 因為我不知道怎麼判斷上一個交易日，但興櫃股票有即時資訊， 所以我用興櫃股票來當作交易日的判斷。（因為一定是交易日才會有資料） 16點之前只會更新興櫃股票，16點之後就會按興櫃交易日來更新上市上櫃的股票。 ※ 編輯: kslman (118.163.68.55), 11/30/2017 15:20:09 ※ 編輯: kslman (118.163.68.55), 12/01/2017 16:48:11
‣ 更多 kslman 的文章
文章代碼(AID): #1Q60w4oS (Stock)