麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 請問如何以VBA擷取網站上的資料？ [打印本頁]

作者: mnsmrtl 時間: 2011-4-21 11:32 標題: 請問如何以VBA擷取網站上的資料？

本帖最後由 mnsmrtl 於 2011-4-23 12:11 編輯

如題，我希望在EXCEL輸入股票代碼，然後可以從證交所或是奇摩等網站抓取當日收盤等數據
不過完全不知道從何下手，請各位先進不吝指教，謝謝

作者: freeffly 時間: 2011-4-21 12:44

奇摩網站的網路上有人提供
但是我跟櫃買中心的比對有點差異
不知道原因是什麼
不然那各檔真的是讓人抓的相當好用
上網找一下就有

作者: GBKEE 時間: 2011-4-21 16:57

回復 1# mnsmrtl
不一定要VBA
[attach]5676[/attach]

作者: mnsmrtl 時間: 2011-4-22 11:29

感謝兩位的回覆，不過我還是希望透過VBA每日擷取
至於網路上找到的檔案，我也有抓到過，不過無法開啟
似乎缺了很多元件，由於我VBA的功力不足，要修改也無從下手
所以想自己學著寫寫看，還請提供建議，謝謝

作者: GBKEE 時間: 2011-4-22 16:05

回復 4# mnsmrtl

Sub 新增查詢()
Range("A1") = "2610" '股票代號
Range("A3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1"), Destination:=Selection) '新增查詢
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "6"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Name = .ResultRange.Cells(3, 1)
End With
End Sub
'***** 已新增後查詢後可修改A1的股票代號查詢 *****
Sub 更新查詢()
With Range("A3").QueryTable
.Connection = "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1") '股票代號在[A1]
.Refresh BackgroundQuery:=False
.Name = .ResultRange.Cells(3, 1)
End With
End Sub

複製代碼

作者: mnsmrtl 時間: 2011-4-23 12:07

本帖最後由 mnsmrtl 於 2011-4-23 12:14 編輯

感謝版主，經測試後已經ok
另外想再請教，如何只回傳其中一個欄位？
如果該網站變更格式的話，.querytable的子項目是不是要重新修改？
而webtables="6"，要怎麼取得？

作者: GBKEE 時間: 2011-4-23 17:28

回復 6# mnsmrtl
另外想再請教，如何只回傳其中一個欄位？
請參考 http://forum.twbts.com/thread-3064-1-2.html

webtables="6"，要怎麼取得？

[attach]5745[/attach]

[attach]5746[/attach]

作者: mnsmrtl 時間: 2011-4-24 01:10

感謝版主指導~

作者: pig172 時間: 2011-10-16 16:28

感謝發文.真的可以用..是否還有同時能抓多檔的方法?

作者: dechiuan999 時間: 2011-10-17 09:32

版主大大你好：

小弟對透過EXCEL取得網頁資料，
一直深深感到興趣但是無法領會及突破困境。
現想借用此板及板主大大提供的語法。
並取得此網頁的指定位置內容如下：

一、
網址：http://tw.stock.yahoo.com/d/s/company_1102.html

二、
取得此網頁的資料表為

獲利能力 (100第2季) 最新四季每股盈餘  最近四年每股盈餘

三、
  小弟有試著直接將下列語法的網址更正
如上的網址但是無法成功。
能請版主大大指點小弟。

感恩大大：

Sub 新增查詢()
Range("A1") = "2610" '股票代號
Range("A3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1"), Destination:=Selection)  '新增查詢
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlSpecifiedTables
      .WebFormatting = xlWebFormattingNone
      .WebTables = "6"
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
      .Name = .ResultRange.Cells(3, 1)
End With
End Sub
'***** 已新增後查詢後可修改A1的股票代號查詢  *****
Sub 更新查詢()
With Range("A3").QueryTable
      .Connection = "URL;http://tw.stock.yahoo.com/q/q?s=" & Range("a1") '股票代號在[A1]
      .Refresh BackgroundQuery:=False
      .Name = .ResultRange.Cells(3, 1)
End With
End Sub

作者: oobird 時間: 2011-10-17 11:15

其實你錄製一下就可得到代碼，再把2610改成儲存格位址如[a1], 如此而已。

Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tw.stock.yahoo.com/d/s/company_" & [a1] & ".html", _
Destination:=Range("A3"))
.Name = "company_" & [a1]
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "9"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

複製代碼

作者: oobird 時間: 2011-10-17 11:32

這是excel的說明中的範例寫法，比錄製得的代碼簡潔多了

Sub qq()
Set shFirstQtr = ActiveSheet
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection:="URL;http://tw.stock.yahoo.com/d/s/company_" & [a1] & ".html", _
Destination:=shFirstQtr.Cells(3, 1))
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "9"
.Refresh
End With
End Sub

複製代碼

作者: dechiuan999 時間: 2011-10-17 20:16

謝謝oobird版主大大。
感謝您幫小弟開啟這一扇大門，
同時也幫小弟搬開心中的一塊
大石頭。
一語點醒如此好用的EXCEL
錄製功能，小弟卻將它遺忘。

另小弟想宣告設定
DIM qtQtrResults AS ......
才能讓它在WITH qtQtrResults時
能顯示出其屬性及功能呢？
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "9"
.Refresh
End With

感恩大大！

作者: oobird 時間: 2011-10-17 20:35

Dim qtQtrResults As QueryTable

作者: dechiuan999 時間: 2011-10-18 06:34

謝謝oobird版主大大。
感謝您引領小弟進入
另一個新的領域。

感恩大大！

作者: p01005 時間: 2015-3-4 23:39

Sub qq()
Set shFirstQtr = ActiveSheet
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection:="URL;http://tw.stock.yahoo.com/d/s/company_" & [a1] & ".html", _
Destination:=shFirstQtr.Cells(3, 1))
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "9"
.Refresh
End With
End Sub
我想問問那麼在這段代碼中
.Refreshstyle 可以插在那個位置？？
因為我想這個是覆蓋資料而不是不斷插入

作者: p01005 時間: 2015-3-5 02:31

已找到.Refreshstyle的位置
但找不到怎樣只導入第一欄的資料＠＠

作者: ABK 時間: 2016-6-7 12:29

請問如果只想抓網站上的部分資料下來， VBA 要如何寫 ?

Sub 期貨交易口數()
Set shFirstQtr = ActiveSheet
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection:="URL;http://www.taifex.com.tw/chinese/3/7_12_3_tbl.asp", _
Destination:=shFirstQtr.Cells(3, 1))
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "2"
.Refresh
End With
End Sub

複製代碼

作者: ui123 時間: 2017-9-12 10:51

回復 5# GBKEE

大大請問一下，我用下面這個程式抓出的資料跟原始網站的資料比較下，下面有一段沒有抓不到，是不是有參數沒設好? 要wait嗎?因為下面有顯示Loading more data...但是我不知道怎樣讓他跑完

原始網站: https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d

Sub QueryTable()
Const xlURL As String = "https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d"
With ActiveSheet.QueryTables.Add("URL;" & xlURL, Destination:=Range("$A$1"))
      .WebFormatting = xlWebFormattingNone
      .TablesOnlyFromHTML = False
      .RefreshStyle = xlOverwriteCells
      .SaveData = True
      .Refresh 0
End With
End Sub

作者: ui123 時間: 2017-9-12 11:08

回復 5# GBKEE

GBKEE大大，我原本想用這幾天問你的，抓Crumb位置來下載csv檔案，但沒有成功，所以想換下面那個網址試試，但抓出的資料不完整，如果可以抓取完整就太好了
https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d
紅框下面是要抓的資料:

javascript:;

作者: ui123 時間: 2017-9-13 20:02

回復 11# oobird

來問一下 oobird 大大，
oobird大大問你喔，我用下面這個程式抓出的100筆資料，你知道怎樣讓他抓完整嗎？

原始網站: https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d

Sub QueryTable()
Const xlURL As String = "https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d"
With ActiveSheet.QueryTables.Add("URL;" & xlURL, Destination:=Range("$A$1"))
      .WebFormatting = xlWebFormattingNone
      .TablesOnlyFromHTML = False
      .RefreshStyle = xlOverwriteCells
      .SaveData = True
      .Refresh 0
End With
End Sub

作者: Scott090 時間: 2017-9-14 06:35

回復 21# ui123

請問這個數值文字如何轉換成日期格式? 謝謝
period1=1473638400

作者: ui123 時間: 2017-9-14 16:27

回復 22# Scott090

原始網站: https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2=1505174400&interval=1d&filter=history&frequency=1d
會自動會轉成，規則不清楚，目前用VBA抓只能抓到100筆

作者: Scott090 時間: 2017-11-11 19:51

本帖最後由 Scott090 於 2017-11-11 19:53 編輯

回復 23# ui123

原始網站: https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2 ...
ui123 發表於 2017-9-14 16:27

period1=1473638400, period2=1505174400 的數字是日期的秒數數列值
以VBA計算可得
例如日期是 "2017/1/20"，則 period = datevalue("2017/1/20") * 86400 - 2209190400
其中一日有86400 秒, 22091904005這個數字是這個網頁對日期演算的一個常數

可以驗算：
a = (1473638400 + 2209190400) / 86400 = 42625.33333
yyyy = year(a) = 2016
mm = month(a) = 9
dd = day(a) = 12

所以， 1473638400 這個數字代表日期 2016/9/12

以上請參考

作者: Scott090 時間: 2017-11-12 17:31

回復 Scott090

原始網站: https://finance.yahoo.com/quote/AAPL/history?period1=1473638400&period2 ...
ui123 發表於 2017-9-14 16:27

請參考：
http://forum.twbts.com/thread-20288-1-1.html

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)