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

snare
個人積分：3127分
文章編號：70533005

3127分

樓主

2018-10-17 20:29

justinyutw wrote:
120樓，樓主的寫法非常的不同，差異如下圖
...(恕刪)

關於478樓的問題2
我重看了一次網頁原始碼
確實2個網址都會回傳相同的資料，但表格位置不同
ajax 那個也許是為了將來改成https用的
舊的那個還保留，也許是為了相容性

而我沒有保留當初的網頁原始碼，且過了1年5個月，也許是後來網頁改版加上去的
所以我沒辦法回答，為什麼我會選那個網址

justinyutw wrote:
善用將 Query Table 放入 SubFun 內，或甚至多加一句 Nothing (雙重保障)，
應該對記憶體不足，應該明顯有幫助吧？
...(恕刪)

只有一點點幫助
querytable效率本來就差
佔記憶體主要是背景連線的問題
(可參考113樓)

至於刪連線數的部份，可參考麻辣論譠，那邊很多querytable的範例(或搜尋其它的文章)
例如這篇剛好有刪連線數的寫法：http://forum.twbts.com/thread-21183-1-1.html

querytable是我不用的方法，所以不多加介紹

如果您用的是excel 2013(或新版)，也可以用
FILTERXML()這個函數來代替querytable，效率比較好

而且FILTERXML()，除了查資料外，還可以做很多神奇的事
(例如：連結網頁的功能，來對儲存格，做計算、翻譯…等等的處理)

justinyutw wrote:
已有解釋這三行的應用。不過對於這一行，是如何或基於什麼了解要設定這時間？
...(恕刪)

有些網站不一定要3行，也許其中一行就可以了
3行都加是省去測試的麻煩，多寫不會怎麼樣
但對於不需即時更新的網站，3行都加上去，會降低一些速度

關於時間，那是隨便設定的，什麼時間都可以，只是習慣上設比較遠一點的
避免查詢時不小心查到相同的時間(雖然不太可能)

(您可以參考看看這一篇文章，雖然不是介紹vba，但對HTTP Cache(Header)有非常詳細的說明)
https://blog.techbridge.cc/2017/06/17/cache-introduction/

justinyutw wrote:
第二次GET(POST)會引用到這些訊息，當然是要說明這些訊息是 Referer 哪個網頁取得的
...(恕刪)

正確，但不一定要加，有些網頁不會檢查

但2次查詢的目地，有時候是為了順便取得cookie值
雖然1次查詢也可以，但要另外寫程式做一個假cookie來讓網站檢查
用2次查詢的方法簡單多了

justinyutw wrote:
雖然之後的範例即使沒有二次的GET(POST), 依然會寫上這一行，如同標準配備一般。
...(恕刪)

這是因為我懶，有時候複製、貼上後，忘了刪

就如前面所說的，和查詢無關的參數，多寫不會怎麼樣，網站後端會處理

justinyutw

justinyutw
個人積分：1分
文章編號：70613864

1分

482樓

2018-10-24 0:35

snare wrote:
關於478樓的問題2...(恕刪)

感謝樓主的回覆

其實我也沒有在學習 Querytable，因能力有限，還是好好認真學樓主提供的招式即可。
而真正想知道的是物件在方法一與方法二是否有差別

請教#175這一範例，似乎是討論串內唯一的非同步下載
#175
(1)
請問是否任何下載資料的程式，如果原本是同步下載(False)，只要將 async 的設定改成非同步下載(True)，都是OK的？

因為似乎沒看過非同步的寫法，沒什麼經驗。

(2)
自動執行時滿非 stock_id，都會跳到 re_query 並寫入""網路忙線中，稍後自動重新下載"。
但在debug模式下，一行一行執行下，竟然非 stock_id，竟然都不會跳到 re_query，
似乎在debug模式下執行，並不會 error。

(有時候自動執行，好像也不會跳到 re_query 並寫入""網路忙線中，稍後自動重新下載"。
On error 和 re_query 移除掉，程式並不會出錯)

(3)
TimeSerial 和 TimeValue 是不是功能一樣？使用上看起來沒什麼差別。

https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime

https://docs.microsoft.com/zh-tw/office/vba/language/reference/TimeSerial Function

snare

snare
個人積分：3127分
文章編號：70614809

3127分

樓主

2018-10-24 5:29

justinyutw wrote:
想知道的是物件在方法一與方法二是否有差別
...(恕刪)

記憶體差不多，但速度差很多(如果資料量很大的話)
物件的建立、釋放，也是需要時間的

但query table方式是例外，因為它本身的查詢方式，會影響程式的速度，沒辦法做正確的測試

下面是用字典物件做出方法1、方法2的範例

產生一個隨機數放到a key => 拿出來放到 cells(1,1)=> 清空字典
最後釋放物件
test1 跑6萬次
test2 跑1萬次
您分別跑一次test1、test2比較看看

Sub test1()

ttt = Timer

Dim n As Object
Set n = CreateObject("Scripting.Dictionary")

For i = 1 To 60000

n.Add "a", Int(Rnd(Timer) * 1000)
Cells(1, 1) = n("a")
n.RemoveAll

Next i

Set n = Nothing

Debug.Print Timer - ttt

End Sub

Sub test2()

ttt = Timer
For i = 1 To 10000
Call test3
Next i
Debug.Print Timer - ttt

End Sub

Sub test3()

Dim n As Object
Set n = CreateObject("Scripting.Dictionary")
n.Add "a", Int(Rnd(Timer) * 1000)
Cells(1, 1) = n("a")
n.RemoveAll
Set n = Nothing

End Sub

justinyutw wrote:
請教#175這一範例，似乎是討論串內唯一的非同步下載
...(恕刪)

問題1
false 改true(非同步)，可以的，但有可能會漏資料
需另外增加程式碼處理沒資料的情況
因為本來就是要抓完整的資料，所以基本上不會用非同步來自找麻煩
可參考393樓的簡易說明

justinyutw wrote:
(有時候自動執行，好像也不會跳到 re_query 並寫入""網路忙線中，稍後自動重新下載"。
On error 和 re_query 移除掉，程式並不會出錯)
...(恕刪)

問題2
因為網頁的回應時間，有時很快，有時很慢，用非同步就會有這樣的問題
但網頁正常情況下，通常不會出錯，因為下載時間遠低於預設等待時間
所以on error不會啟動

至於debug 逐行執行不會出錯，是因為時間夠多，資料早就下載好了
所以程式不會出錯

justinyutw wrote:
TimeSerial 和 TimeValue 是不是功能一樣？使用上看起來沒什麼差別。
...(恕刪)

問題3
基本上相同，就像mid()可以代替left()、right()一樣
偶爾可試試不同的寫法，增加經驗

OMITASUM

OMITASUM
個人積分：0分
文章編號：70656461

0分

484樓

2018-10-26 23:00

感謝樓主提供範例
想請問這個程式可以改成將52週的資料全po出來, 在同一個SHEET裡面,表示嗎?
而不是單單某各區間呢?

謝謝樓主

justinyutw

justinyutw
個人積分：1分
文章編號：70663653

1分

485樓

2018-10-27 16:05

snare wrote:
記憶體差不多，但速...(恕刪)

感謝樓主的回覆
藉由樓主提供的測試範例，再重新排例作個組合，測試時間如下：
不管是僅一次宣告定義 set n=... 或使用 SubFun 釋放記憶體，
由測試時間結果來看，都是可以大幅縮短執行時間。

#175
(1)
非同步情況下，程式還在繼續執行下，將程式按Stop，還未執行結束的程式並不會因為按了Stop就停止。

看了#393的說明，請問
非同步情況下，又設定 On Error 如下。即使按了Stop，但程式並沒有下載到資料或網站掛了，是否依然會在背景下繼續執行，沒完沒了，除了將Excel關掉或關機？

re_query:

Sheets("stock").Cells(lastrow, 2) = "網路忙線中，稍後自動重新下載"
Application.OnTime Now + TimeSerial(0, 0, 15 + Int(Rnd(Timer) * 15 + 1)), "'GetYahoo " & """" & stock_id & """" & "," & lastrow & "," & True & "," & 30 & "'"

snare

snare
個人積分：3127分
文章編號：70665407

3127分

樓主

2018-10-27 19:33

OMITASUM wrote:
想請問這個程式可以改成將52週的資料全po出來, 在同一個SHEET裡面,表示嗎? ...(恕刪)

我不知道您在問那個範例?

如果網站有提供下載，用迴圈多跑幾次就可以了
如果是access那個範例，已下載的歷史資料，用sql語法，就可以一次列出來

不過這部份請自行加油
=====================================

justinyutw wrote:
非同步情況下，程式還在繼續執行下，將程式按Stop，還未執行結束的程式並不會因為按了Stop就停止。
...(恕刪)

跟非同步無關
是Application.OnTime Now的關係(詳細功能請google)

justinyutw wrote:
非同步情況下，又設定 On Error 如下。即使按了Stop，但程式並沒有下載到資料或網站掛了，是否依然會在背景下繼續執行，沒完沒了，除了將Excel關掉或關機？
...(恕刪)

大型網站要掛掉機會不高，但如您所說的，不幸碰到網站掛掉
因為 Application.OnTime Now 會一直重覆排程
有機會遇到沒完沒了的情況，要關掉excel或關機，才能正常結束
也可在程式中如入重試次數，例如超過10次後就不排程(175樓範例中沒這個功能)

非同步要注意，有資料、資料不完整、沒資料、網路斷線、網站掛掉、網路lag

同步方式，只需要考慮，有資料、沒資料2種狀況而己，很好處理
所以我才說，沒事不會用非同步方式自找麻煩

OMITASUM

OMITASUM
個人積分：0分
文章編號：70670853

0分

487樓

2018-10-28 10:42

感謝樓主的回覆.

我是用328樓的範例,的確可以大幅縮減查詢時間
我再試試看用迴圈,將資料變成52周跑出來在同一張sheet,
然後在畫出趨勢走勢圖...

justinyutw

justinyutw
個人積分：1分
文章編號：70692442

1分

488樓

2018-10-30 0:39

snare wrote:
跟非同步無關
是Application.OnTime Now的關係(詳細功能請google)...(恕刪)

感謝樓主的回覆，提供這麼多的解釋供學習

請問一下關於Arraylist (#210, #214) 的用法

snare wrote:
arraylist 受限於記憶體大小，基本上沒限制

受限制的是Application.Transpose
能轉換的陣列大小是 65536...(恕刪)

請問如果 TempArray 真的超過陣列大小的限制，請問有其它的方式可以轉置嗎？

如下紅字，嘗試使用其他方法解決，但msdn有看沒有懂吧，會有錯誤產生，還請樓主協助麻煩幫忙看一下，謝謝。

Sub test()

Cells.Clear
Application.ScreenUpdating = False
Dim TempArray As Object, DataArray As Object
Dim myArray As Variant

Set TempArray = CreateObject("System.Collections.ArrayList")
Set DataArray = CreateObject("System.Collections.ArrayList")

ttt = Timer

For i = 1 To 10000
TempArray.Add Int(Rnd * 1000)
Next i

For ii = 1 To 10000 Step 2
If TempArray(ii - 1) > 500 Then
DataArray.Add TempArray(ii - 1) + TempArray(ii)
End If
Next ii

Range("c1") = Timer - ttt
Range(Cells(1, 1), Cells(10000, 1)) = Application.Transpose(TempArray.toarray())
Range(Cells(1, 2), Cells(10000, 2)) = Application.Transpose(DataArray.toarray())

'1
TempArray.CopyTo(0, myArray, 0, 4)
TempArray.RemoveRange(0,4)
Range(Cells(1, 4), Cells(10000, 4)) = Application.Transpose(myArray)

'2
TempArray.CopyTo(0, myArray, 0, 4)
TempArray.RemoveRange(0,4)
Range(Cells(1, 4), Cells(10000, 4)) = Application.Transpose(myArray)

'3
TempArray.CopyTo(0, myArray, 0, 4)
TempArray.RemoveRange(0,4)
Range(Cells(1, 4), Cells(10000, 4)) = Application.Transpose(myArray)

Set TempArray = Nothing
Set DataArray = Nothing

Application.ScreenUpdating = False

End Sub

附加壓縮檔: 201810/mobile01-5e1a426bc4399e66d50901c7bf83ee17.zip

snare

snare
個人積分：3127分
文章編號：70704355

3127分

樓主

2018-10-30 22:03

justinyutw wrote:
請問如果 TempArray 真的超過陣列大小的限制，請問有其它的方式可以轉置嗎？
...(恕刪)

arraylist 基本上沒有陣列大小限制
受限制的是Application.Transpose，陣列大小超過 65536，就會出錯
不是陣列有限制，是Transpose有上限

arraylist轉置的用意是為了讓陣列，從横向，轉成直向，方便填入表格
只是用來計算，不填入表格，不轉也可以的
簡單的用法就是Application.Transpose，一行搞定，但有65536的限制

超過65536可改用for next來轉置
或是利用本樓其它範例中的部份特殊技巧來代替也可以

自行試看看以下四個範例有什麼不同
'=================================
Sub test1()

Cells.Clear
Application.ScreenUpdating = False
Dim DataArray As Object, lastrow As Double

Set DataArray = CreateObject("System.Collections.ArrayList")

lastrow = 30
ttt = Timer

For i = 1 To lastrow
DataArray.Add i
Next i

Range(Cells(1, 1), Cells(1, lastrow)) = DataArray.toarray()

Set DataArray = Nothing
Application.ScreenUpdating = true

Debug.Print Timer - ttt

End Sub

Sub test2()

Cells.Clear
Application.ScreenUpdating = False
Dim DataArray As Object, lastrow As Double

Set DataArray = CreateObject("System.Collections.ArrayList")

lastrow = 30
ttt = Timer

For i = 1 To lastrow
DataArray.Add i
Next i

Range(Cells(1, 1), Cells(lastrow, 1)) = Application.Transpose(DataArray.toarray())

Set DataArray = Nothing
Application.ScreenUpdating = true

Debug.Print Timer - ttt

End Sub

Sub test3()

Cells.Clear
Application.ScreenUpdating = False
Dim DataArray As Object, lastrow As Double

Set DataArray = CreateObject("System.Collections.ArrayList")

lastrow = 90000
ttt = Timer

For i = 1 To lastrow
DataArray.Add i
Next i

For i = 1 To lastrow
Cells(i, 1) = DataArray(i - 1)
Next i

Set DataArray = Nothing

Application.ScreenUpdating = true

Debug.Print Timer - ttt

End Sub

Sub test4()

Cells.Clear
Application.ScreenUpdating = False
Dim DataArray As Object, Clipboard As Object, lastrow As Double

Set DataArray = CreateObject("System.Collections.ArrayList")
Set Clipboard = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

lastrow = 90000
ttt = Timer

For i = 1 To lastrow
DataArray.Add i
Next i

With Clipboard
.SetText Join(DataArray.toarray(), vbNewLine)
.PutInClipboard
End With

With Sheets("sheet1")
.Select
.Cells(1, 1).Select
.PasteSpecial NoHTMLFormatting:=True
.Columns.AutoFit
End With

Set DataArray = Nothing
Set Clipboard = Nothing

Application.ScreenUpdating = true

Debug.Print Timer - ttt

End Sub

'=================================

justinyutw

justinyutw
個人積分：1分
文章編號：70734638

1分

490樓

2018-11-02 1:16

snare wrote:
arraylist 基本上沒有陣列大小限制
受限制的是Application.Transpose，陣列大小超過 65536，就會出錯...(恕刪)

Sorry, 敘述太快沒注意到。
其實想表達的是因Application.Transpose 陣列大小超過 65536，就會出錯。
所以 TempArray 如果需使用 Application.Transpose，就必需被限制在 65536 以下使用。

(1)
感謝樓主的回覆，整理一下測試時間如下：

test4 結合了之前剪貼薄的學習，在整理數據這一部份，還真是很好使用

另外test4 第一天測試時，沒什麼問題。
但第二天測試時，會 Error 在 .PasteSpecial NoHTMLFormatting:=True，但繼續執行，竟然還是可以跑完。
如果是使用 Debug 模式執行，則完全不會有 Error產生，並執行完畢。請問是和網路品質有關嗎或是哪個指令執行太慢了造成貼不過去？

(2)
雖然自己的方法不是很好，就是想要不超過65536下，一部份一部份的轉置。
照著 ArrayList 的 msdn 說明，嘗試著新的指令，卻試不出來。
與其說要試出小量 Transpose，不如說是想知道遇到一個新指令，照著說明輸入指令，但還是Error，要如何才能改到好。
google只有 msdn 的說明，卻沒有人使用後的心得可供參考。

TempArray.CopyTo(0, myArray, 0, 4)
TempArray.RemoveRange(0,4)
Range(Cells(1, 4), Cells(10000, 4)) = Application.Transpose(myArray)