(不定期更新)使用VBA解決 excel web 查詢無法匯入、匯入太慢的股市資料

樓主您好:
小弟先前查詢YAHOO股市資料,以WEB查詢的的程式碼放入VBA中作資料自動更新,但YAHOO改版後致無法查詢,請問除了以您提供的程式外,原WEB查詢程式碼,是否有機會修正後恢復正常查詢?
activer wrote:
請問除了以您提供的程式外,原WEB查詢程式碼,是否有機會修正後恢復正常查詢?...(恕刪)


可改用 powerquery 代替,請參考413樓

建議改用xmlhttp,速度快、穩定,可參考21樓

如果您堅持一定要用webquery(web匯入、查詢)



一、執行 regedit
二、到 HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings
三、右鍵新增dword 值(32位元),名稱是"BypassSSLNoCacheCheck"
四、點開BypassSSLNoCacheCheck,數值資料=>1
五、重新打開excel

缺點,會變亂碼,上網會有安全性問題,不建議使用







snare wrote:
可改用 powerquery...(恕刪)


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://tw.stock.yahoo.com/q/q?s=" & stockNo, Destination:=Range("V" & stockLine))
.Name = "q?s=" & stockNo
.
.WebTables = "2"
.Refresh BackgroundQuery:=False
End With

樓主您太厲害了,這麼快就回覆,謝謝,但小弟功力太差,還請多指教

1.以上是小弟VBA中有關yahoo股市web查詢的部分程碼,暫時未修改reg檔,僅安裝您在440樓提供的xp修正程式及將WebTables 改成 "2"後即可恢復查詢,您提供的程式修改table=2亦可正常更新

2. 有關修改"BypassSSLNoCacheCheck"是修改什麼,以致會造成"上網會有安全性問題,不建議使用"

activer wrote:
1.以上是小弟VBA中有關yahoo股市web查詢的部分程碼,暫時未修改reg檔,僅安裝您在440樓提供的xp修正程式及將WebTables 改成 "2"後即可恢復查詢,您提供的程式修改table=2亦可正常更新
...(恕刪)

您是用 xp 系統?
因為https的關係,xp不裝KB4019276 (TLS 1.1/1.2 support),就沒辦法下載
但這只能算是偏方,是給pos用的,不是微軟正式放出來的修正

新版的windows office,在一些https的網站使用web匯入
如果出現無法下載,才需使用562樓的方式改登錄檔
(json、ajax網站,不用試了,一樣不行,是web匯入的先天限制)



activer wrote:
2. 有關修改"BypassSSLNoCacheCheck"是修改什麼,以致會造成"上網會有安全性問題,不建議使用"
...(恕刪)

這是微軟官方文件,處理當excel web匯入(querytable),無法下載網頁(table)的修改方式



照字面看是跳過ssl的快取檢查,修改後,也確實有效
但既然有效,那為什麼不正式用更新檔修正,相必一定有一些未發現,安全、穩定上的問題
所以我才會說不建議使用


querytable,除了速度不穩定,還有連線數的問題,可參考113樓、481樓

以yahoo股市來比較,xml 約0.5秒,querytable 約2.5秒
如果拿xml,去下載json、csv格式的資料,時間甚至可以短到0.0X秒
而querytable不穩的時候可能會多到1分鐘以上
這也是我不寫querytable範例的原因


snare wrote:
您是用 xp 系統...(恕刪)


1.XP系統較親民,最主要是習慣了,其他作業系統慢慢適應中
2.querytable使用久了,執行速度真的不是普通慢,恰巧看到樓主不藏私分享這個vba程式,且協助解決問題,在時下許多論壇中,已不多見了
3.樓主您的程式與相關討論串,小弟還在消化中,真心感謝!!
在其它地方,看到一個沒人回答問題,如何用vba開啟其它程式,並輸入必要的按鍵
例如:帳號、密碼、tab、enter……等等

雖然正常情況下是用不到這種方式
除非用來開啟一定要和excel連動的程式,但也只能減少一些開啟的動作
不過還是寫個範例,給各位參考

其實很簡單,只要利用shell + appactivate + sendkeys ,就可輕易做到


'=========================================
'範例功能:使用vba開啟其它程式後,在程式的視窗上輸入按鍵

'sendkeys 按鍵代號,請參考
'https://docs.microsoft.com/zh-tw/office/vba/api/excel.application.sendkeys



Sub test()

Dim file_name As String
file_name = "c:\windows\system32\calc.exe"'小算盤

MsgBox "範例執行時,請勿動鍵盤、滑鼠"
Shell file_name, vbNormalFocus
Delay (3) ' => 需根據程式的啟動速度,修改適當的等待時間
AppActivate "小算盤" '=>要做輸入那個視窗上的標題
Delay (1)
SendKeys "123456", True
Delay (2)
SendKeys "{+}", True
Delay (2)
SendKeys "654321", True
Delay (2)
SendKeys "~", True
Delay (2)

'因為是範例,所以把sendkeys的文字,拆成多段,間隔時間也設的比較長
'請自己依需求修改

file_name = "C:\Windows\system32\notepad.exe"'記事本
Shell file_name, vbNormalFocus
Delay (3)
AppActivate "未命名 - 記事本"
Delay (1)
SendKeys "mobile01", True
Delay (1)
SendKeys "{TAB}", True
Delay (1)
SendKeys "~", True
Delay (1)
SendKeys "snare", True
Delay (1)
SendKeys "~", True
Delay (1)
SendKeys "台股上萬點", True


End Sub

'使用348樓的時間延遲範例,彈性較大
'如果需要更長的等待時間,也可以改用application.wait代替這個副程式
Sub Delay(setdelay As Single)

Dim StartTime As Double, NowTime As Double
StartTime = Timer * 100
setdelay = setdelay * 100
Do
NowTime = Timer * 100
DoEvents
Loop Until NowTime - StartTime > setdelay

End Sub


'=========================================
樓主您好,
1.以21樓的程式碼,取代原webquery的查詢,速度快很多且不會有亂碼的問題

2.目前僅看到175樓,但仍弄不清楚有關網頁是以"GET"與"POST"的方式取得資料,不知樓主有簡易的判斷方式可供參考?

3.小弟有個網址,欲截取"USD美金匯率資料查詢─近30日","https://invest.fubonlife.com.tw/content.html?sUrl=$W$WA$FUBONEXCHANGECHART]DJHTM{A}USD!B}7!E}2019-1-25",以您的程式嚐試讀取資料:

在xp系統office2007中執行,出現"執行階段錯誤



在windows10系統office2016中執行,TABLE由0-20均出現"執行階錯誤91",但以web查詢可以順利取得資料,由程式碼發現無"webtables=??"的參數,反而以POWERQUERY方式卻無法執行



樓主可否提供處理方向?
activer wrote:
2.目前僅看到175樓,但仍弄不清楚有關網頁是以"GET"與"POST"的方式取得資料,不知樓主有簡易的判斷方式可供參考?
...(恕刪)


一、執行chrome(或其它),進入要查詢的網頁
二、使用開發者工具(f12)
三、重新整理網頁一次(f5)
四、開發者工具畫面中=>選network=>all(或其它)
看method那一欄就可以知道是get or post

(點下可看大圖)


如果method沒出來,在標題列上按右鍵,把method打勾


或使用較多人用的fiddler(請自行google教學,不多做介紹)



activer wrote:
3.小弟有個網址,欲截取"USD美金匯率資料查詢─近30日","https://invest.fubonlife.com.tw/content.html?sUrl=$W$WA$FUBONEXCHANGECHART]DJHTM{A}USD!B}7!E}2019-1-25",以您的程式嚐試讀取資料:

在xp系統office2007中執行,出現"執行階段錯誤

...(恕刪)


因為資料網址不對


activer wrote:
在windows10系統office2016中執行,TABLE由0-20均出現"執行階錯誤91",但以web查詢可以順利取得資料,由程式碼發現無"webtables=??"的參數,反而以POWERQUERY方式卻無法執行
...(恕刪)


同上,網址不對

使用開發者工具,可知,資料是由這2個網址回傳的

(點下可看大圖)




https://invest.fubonlife.com.tw/w/bcd/fubonexchangeBCD.djbcd?A=USD&B=7&C=0&D=2018-02-25&E=2019-01-25
這個網址傳回字串(非表格),不能用table方式
需改用剪貼薄或使用split、mid…等等字串函數處理,但資料少,會比較快





https://invest.fubonlife.com.tw/w/wa/fubonexchangechart.DJHTM?A=usd&B=7&C=0&D=2018-02-25&E=2019-01-25
這個網址傳回表格,可使用table方式(21樓範例)



網址中的日期區間,請自行在程式碼中用變數或其它日期代替

activer wrote:
一、執行chrome(或其它),進入要查詢的網頁 ...(恕刪)



謝謝樓主快速回覆
1.在xp系統,office2007中,以您提供的資料網址執行21樓程式,仍發生"執行階錯誤-系統錯誤,且該網址亦無法作webquery,猜想該公司網址可能有檢核作業系統及office版本

2.在win10系統office2016中執行21樓程式,以table(0),即可順利取得資料

3.一、執行chrome(或其它),進入要查詢的網頁
二、使用開發者工具(f12)
三、重新整理網頁一次(f5)
四、開發者工具畫面中=>選network=>all(或其它)

依樓主教授的方式,試著取得相關網址,但無法與樓主一樣取得完整的網址,如圖一,無參數"D"及日期資料(2018-2-25) ;圖二,有參數"D",但無日期資料(2018-2-25),請問樓主,小弟的操作少了什麼?

圖一




圖二



activer wrote:
1.在xp系統,office2007中,以您提供的資料網址執行21樓程式,仍發生"執行階錯誤-系統錯誤
...(恕刪)

此網站用沒有用舊版的https,所以xp不能用,就算裝了pos修正,也不夠新
使用xmlhttp vba 在xp中無法正常下載,是正常的

activer wrote:
依樓主教授的方式,試著取得相關網址,但無法與樓主一樣取得完整的網址,如圖一,無參數"D"及日期資料(2018-2-25) ;圖二,有參數"D",但無日期資料(2018-2-25),請問樓主,小弟的操作少了什麼?
...(恕刪)


使用日期選擇的正常
(點下可看大圖)



使用月份選擇的,因為是改用第2個網址的資料來產生表格,所以第一個網址無日期資料
(點下可看大圖)



使用月份選擇的,第2個網址正常
(點下可看大圖)


請使用dateadd()或其它日期函數,來產生需要的日期(請參考)
https://docs.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/dateadd-function
關閉廣告

今日熱門文章 網友點擊推薦!

文章分享
評分
複製連結
請輸入您要前往的頁數(1 ~ 124)