EXCEL VBA從證交所(TWSE)取得即時資訊
說明
本篇是結合TWSE即時資訊API與VBA GET的應用,範例功能為每隔幾秒從證交所取得即時資訊,並儲存在EXCEL中。
這篇的GET是採同步傳輸,所以取得資料時,會有一點不順,之後會介紹非同步傳輸方式取得即時資訊。
如果想了解VBA GET/POST,參考這篇;想了解TWSE即時資訊API,參考這篇。
- 需特別留意,太頻繁從證交所抓資料,會被鎖IP。好像是每5秒超過3次,就會被鎖幾十分鐘(時間不確定),建議至少延遲2秒。
- 範例檔下載連結在最下面,想先嘗試的朋友可先到最下面下載喔~
撰寫程式
確認worksheet名稱
名稱需與程式碼內相同,若工作表名稱不是「工作表1」,請先找到下面這行程式碼,把「工作表1」改成跟你的工作表名稱
sheetName = "工作表1"
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYtuMGsaNZfRIHY-e3u-QXYheVszpKpkibYXuR_RIDtss3uw3SEfdtUKhxPZJuvoBVs82pGvFX_HIsSWdx5CNBt0V9QvCTK-aUe07ZJWvWZ8mlqoI99v6sVm5wO_rsT4KunwCGIJ4kQXAW/w400/01.jpg)
進入VB編輯器
如果你還沒開啟開發人員頁面,請先照這篇的說明設定,若已完成設定,請進行下一步驟
匯入Module
下載以下兩個Module
- https://drive.google.com/file/d/1aTQqZig64Ild7RGdnRtsMqKYdzy08hDV/view?usp=drive_link
- https://drive.google.com/file/d/1zV_3AVMkYysBH1KDOKjWshVLMiADu9h_/view?usp=drive_link
將「mControl.bas」、「mTextProc.bas」拖到專案總管
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhviGVaft5YjeUG3S7Rr7HBvBXN3IPhg8bMk5yUdyhV8kH20eh_hoYbGx3tP0Fqk4RvMea4p3rG0KKGWqW-X1WudaqQwUWDzzVm6Mex2MMo-jEwYdhPO6Suyh3ZhGTzEeZYJoBakBxF5ZWw/w400/02.jpg)
貼上程式碼
- 在ThisWorkbook處連點滑鼠左鍵兩次
- 在右側貼上程式碼
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgx3UkpLjQT3sULuf-Ksnm-yoxYT3mac3k-EyYg-ouoQEbD-VaJbKmB4iHeY96aGFwnDN9swNJUf0G2vSAnkP7IQprE-fBsOt0TAw8BdT0zn26pyFJMitCLSdFJidm3ULU0lbR0S-kvSE0s/w600/03.jpg)
程式碼如下
Option Explicit
Public Sub updateRtdTwse()
' Local variable declaration'
Dim arrTmp1() As String, arrTmp2() As String
Dim rowOffset As Long, colOffset As Long
Dim rowCnt As Long, colCnt As Long
Dim addrTmp As String
Dim varTmp As Variant
Dim sheetName As String
Dim updateAddr As String
Dim securityCode As String
Dim mode As Integer
' Constant declaration'
Const kPrt1StMask = 3
Const kNoHeader = 0 ' Data array does not contain header'
Const kNotPrtHeader = 1 ' Not print header(1st row/column)'
Const kPrtHeader = 2 ' Print header(1st row/column)'
Const kDirectionMask = 4
Const kTopToBottom = 0
Const kLiftToRight = 4
' Parameters'
sheetName = "工作表1"
updateAddr = "A1:F100"
securityCode = "tse_2330.tw|otc_6488.tw"
mode = kLiftToRight + kPrtHeader
' Get real-time data from TWSE (string array)'
arrTmp1 = ApiRtdTwse(securityCode)
' Variable initialization'
addrTmp = updateAddr
rowOffset = Range(updateAddr).Row
colOffset = Range(updateAddr).Column
rowCnt = 0
colCnt = Range(updateAddr).Columns.Count
For Each varTmp In arrTmp1
' Convert a subarray (string) to a 2D array'
arrTmp2 = Split2D(CStr(varTmp), ":", ",")
' Fill in missing headers'
FillNaKey arrTmp2
rowCnt = UBound(arrTmp2, 1) - LBound(arrTmp2, 1) + 1
addrTmp = Range(Cells(rowOffset, colOffset), Cells(rowOffset + rowCnt - 1, colOffset + colCnt - 1)).Address
' Write data (2D array) to Excel worksheet'
UpdateTable sheetName, addrTmp, arrTmp2, mode
' Set next row offset'
rowOffset = rowOffset + rowCnt
Next varTmp
End Sub
Private Sub Workbook_Open()
With Sheets("設定")
With .CommandButton1
.Caption = "開始"
.Enabled = True
End With
With .CommandButton2
.Caption = "停止"
.Enabled = False
End With
End With
End Sub
執行程式
- 將游標移到程式碼上
(是鍵盤的游標,不是滑鼠游標) - 按F5執行程式
執行結果
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjM0w9JjnwJyXGB5uV4LcCNkOUhrGkWK4jR4SS0oOJkjhUDwbeKHetlMZEzN_drDK2eV_7oQHHiqz5hmKHT6IGJKEosQTrc-a0-f2K6CFB_fmJ5fciHJi_si5cXsuwff4SVQQrOBQsCfN5s/s640/04.jpg)
範例下載
- 範例檔有把不需要的欄位隱藏了,參數名稱對照表參考這篇
- 範例連結 :
https://drive.google.com/file/d/10UQJJWJ9oh-ZejbKRWApPkgqfXJX1f4Y/view?usp=drive_link
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_oa7tSZBjQB67_fejYoAOjUqtMFLT8u1gSN4h5g_RkIAynikTQfMUvfM8npidwtXKYMp0vM3YemWtIOfYX8gqofKK0ktYapRdRVBVvgszIdikBMHWWbHtqRBYtT5jTAdqD6GGJd8gJc45/w600/05.jpg)
0留言