討論群組
小惡魔市集 悅遊日本 活動報導
我要回覆

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

  • 2016-03-14 20:04
  • 1348799
snare
snare
3127分
樓主
snare
snare
個人積分：3127分
文章編號：90373862
nijawang wrote:
發現網頁好像有改版，網址有變









[點擊下載]
2024-09-25 7:19 #1491
cji3cj6xu6
謝謝 Snare 大師
2024-10-01 11:02
Morten Hsu
Morten Hsu
0分
1492樓
Morten Hsu
Morten Hsu
個人積分：0分
文章編號：90380534
snare wrote:
.AsTable.ToExcel...(恕刪)

Snare大師 您好：
這一段時間我一直嘗試抓取正確的XPath，總算有意點眉目。不過想再請教，除了選擇股票號碼，可不可以再選擇起始日期和結束日期？還有，使用chrome.FindElementsByXPath抓取資料，好像過一段時間XPath就會做個小變動，用起來是有點不方便。目前的城市代碼如下：
Dim chrome As New Selenium.ChromeDriver, UrL As String
stock = InputBox("股票代號", , 2330)

UrL = "https://goodinfo.tw/tw/ShowMarginChart.asp?STOCK_ID=" & stock
chrome.Get UrL
chrome.Wait 6000

Sheets("資券變化").Cells.Clear
Sheets("資券餘額").Cells.Clear

chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/table[1]/tbody/tr/td[1]/section/table/tbody")(1).AsTable.ToExcel Sheets("資券變化").Range("a1") '當日成交資訊
chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/table[3]/tbody")(1).AsTable.ToExcel Sheets("資券變化").Range("a11") '融資融券變化統計 標題
chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/div[2]/div/table[1]/tbody")(1).AsTable.ToExcel Sheets("資券變化").Range("a12") '融資融券統計表格內容

chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/table[4]")(1).AsTable.ToExcel Sheets("資券變化").Range("a18") '資券變化累計統計 標題
chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/div[3]/div/table[1]/tbody")(1).AsTable.ToExcel Sheets("資券變化").Range("a19") '資券變化累統計表格內容

'/html/body/table[2]/tbody/tr[2]/td[3]/div[5]/table/tbody/tr/td/table/tbody/tr/td[1]/nobr[3]/input[1] 'edtSTART_DT "STARTdate"
'/html/body/table[2]/tbody/tr[2]/td[3]/div[5]/table/tbody/tr/td/table/tbody/tr/td[1]/nobr[3]/input[2] 'edtEND_DT "ENDdate"
chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/div[5]/table/tbody/tr/td/table/tbody")(1).AsTable.ToExcel Sheets("資券餘額").Range("a2") '融資融券餘額 標題
chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/div[5]/div/div/table[1]/tbody")(1).AsTable.ToExcel Sheets("資券餘額").Range("a3") '融資融券餘額表格內容
2024-09-26 9:14 #1492
snare
snare
3127分
樓主
snare
snare
個人積分：3127分
文章編號：90381895
Morten Hsu wrote:
可不可以再選擇起始日期和結束日期？還有，使用chrome.FindElementsByXPath抓取資料，好像過一段時間XPath就會做個小變動，用起來是有點不方便


Sub all_table()

    Dim chrome As New Selenium.ChromeDriver, UrL As String, stock As String, table, R As Integer, C As Integer, i As Integer, TempArray
    Dim keys As New Selenium.keys
    
    stock = InputBox("股票代號", , 2330)
    Sheets("工作表1").Cells.Clear
    R = 1: C = 1
    
    UrL = "https://goodinfo.tw/tw/ShowMarginChart.asp?STOCK_ID=" & stock
    chrome.Get UrL
    chrome.Wait 6000
    
    'day1
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").Clear
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys "2024"
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys (keys.Tab)
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys "08"
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys "08"
    
    'day2
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").Clear
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys "2024"
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys (keys.Tab)
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys "09"
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys "09"
    
    'close ad
    chrome.FindElementByXPath("/html/body/div[5]/button").Click
    
    'click Query button
    chrome.FindElementByXPath("//*[@id='divMarginDetailData']/table/tbody/tr/td/table/tbody/tr/td[1]/nobr[3]/input[3]").Click
    chrome.Wait 6000
    
    Set table = chrome.FindElementsByTag("table")
   
   
    For i = 1 To table.Count
    
        R = R + 1
        TempArray = table(i).AsTable.Data

        Sheets("工作表1").Cells(R, C).Value = "****** Table " & i & "******"
        If (UBound(TempArray) * UBound(TempArray, 2)) > 0 Then
             Sheets("工作表1").Cells(R + 1, C).Resize(UBound(TempArray), UBound(TempArray, 2)).Value = TempArray
        End If
        R = R + UBound(TempArray) + 1
        DoEvents
    Next i
            

    chrome.Quit
    Set chrome = Nothing

End Sub

Sub one_table()

    Dim chrome As New Selenium.ChromeDriver, UrL As String, stock As String, table, TempArray
    Dim keys As New Selenium.keys
    
    stock = InputBox("股票代號", , 2330)
    Sheets("工作表1").Cells.Clear
    
    UrL = "https://goodinfo.tw/tw/ShowMarginChart.asp?STOCK_ID=" & stock
    chrome.Get UrL
    chrome.Wait 6000
    
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").Clear
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys "2024"
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys (keys.Tab)
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys "08"
    chrome.FindElementByXPath("//*[@id='edtSTART_DT']").SendKeys "08"

    chrome.FindElementByXPath("//*[@id='edtEND_DT']").Clear
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys "2024"
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys (keys.Tab)
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys "09"
    chrome.FindElementByXPath("//*[@id='edtEND_DT']").SendKeys "09"
    
    chrome.FindElementByXPath("/html/body/div[5]/button").Click
    chrome.FindElementByXPath("//*[@id='divMarginDetailData']/table/tbody/tr/td/table/tbody/tr/td[1]/nobr[3]/input[3]").Click
    chrome.Wait 6000
    
    
    Set table = chrome.FindElementsByTag("table")
    
    TempArray = table(22).AsTable.Data
    
    Sheets("工作表1").Cells(1, 1).Resize(UBound(TempArray), UBound(TempArray, 2)).Value = TempArray

    chrome.Quit
    Set chrome = Nothing

End Sub
2024-09-26 12:05 #1493
Morten Hsu
Morten Hsu
0分
1494樓
Morten Hsu
Morten Hsu
個人積分：0分
文章編號：90383708
snare wrote:
Sub all_table...(恕刪)

非常感謝Snare大師的快速回覆，這個進階作法對我來說，跨步大了一點，我得仔細研究一番。
2024-09-26 16:49 #1494
nijawang
nijawang
1231分
1495樓
nijawang
nijawang
個人積分：1231分
文章編號：90384432
snare wrote:[點擊下載]...(恕刪)
感謝snare的指導與範例提供！
後來自己用了array()來抓Json資料，不然History資料很多會很慢。

不過有疑問是：
snare大是從網頁的哪邊知道GET的UrL要用這個網址？

UrL = "https://query1.finance.yahoo.com/v8/finance/chart/" & stock & "?events=capitalGain%7Cdiv%7Csplit&formatted=true&includeAdjustedClose=true&interval=1d&period1=" & D1 & "&period2=" & D2 & "&symbol=" & stock & "&userYfid=true&lang=en-US®ion=US"
2024-09-26 18:57 #1495
snare
snare
3127分
樓主
snare
snare
個人積分：3127分
文章編號：90385460
nijawang wrote:
從網頁的哪邊知道GET的UrL要用這個網址？



一、先開網頁
二、F12 (開發者工具)
三、隨便改個日期
四、看看那個網址有資料回傳



2024-09-26 22:42 #1496
nijawang
感謝snare，又學了一招！
2024-09-27 18:02
Yaude Huang
Yaude Huang
5289分
1497樓
Yaude Huang
Yaude Huang
個人積分：5289分
文章編號：90393250
nijawang wrote:
後來自己用了array()來抓Json資料，不然History資料很多會很慢。


你有365嗎？
你可以嘗試用插入Python來處理

我試著用你的URL嘗試一下2330

先抓開始結束時間



再組合URL

="https://query1.finance.yahoo.com/v8/finance/chart/2330.TW?events=capitalGain%7Cdiv%7Csplit&formatted=true&includeAdjustedClose=true&interval=1d&period1="&C73&"&period2="&C74&"&symbol=2330.TW&userYfid=true&lang=en-US®ion=US"



使用WEBSERVICE把json 內容取出



透過python 來處理 json字串




這個欄位 python輸出選擇excel 值，按右鍵處理

之後就是修改timestamp的計算方式與顯示格式




最後一欄我是秀收盤價




當然我的範例很陽春，我只是要測試功能
如果要實用，當然會簡化一些沒必要的畫面

我之前有稍微修改過一個，使用證交所的資料，覺得效率蠻快的
但後來沒用是收盤時，因為證交所在更新資料，那段時間我的檔案卡卡的
而且我的檔案數據太多，相互關聯下，太頻繁更新數據，暫時先不用

這樣的方法我覺得很簡單，連程式碼幾乎都不用寫，要寫的也不多

最近研究一下，是微軟的EXCEL本來有支援股票相關的功能，對我來說已經夠用
但四月以後新的ETF都不能用

00943 00944 00946 00947 全部沒資料可以用
都上市超過半年了，打算找其他方式替代，但又不想寫巨集

我是認為只要是URL可以直接顯示json內容的，都可以用這樣的方式處理
簡單又快速

先把URL組合出來
接著就是webservice取網頁內容
接著寫一段超簡單的pyshon程式碼
接著就是類似EXCEL的處理方式了
2024-09-28 10:21 #1497
nijawang
感謝分享！看起來用Python+Excel也是一個很棒的方式。
2024-09-28 10:46
Yaude Huang
Yaude Huang
5289分
1498樓
Yaude Huang
Yaude Huang
個人積分：5289分
文章編號：90428736
Yaude Huang wrote:
query1.finance.yahoo.com



yahoo finance 的歷史數據，
這幾天我開始拿來用，取代微軟365 excel 股票暫時找不到較新的股票代號部分

但這幾天有發現，台灣放假這幾天，數據怪怪的
放假這幾天，有的是內容直接寫None，有的是填寫最後一天的數據
也就是10/2 10/3 直接秀10/1的數據

最後我是再搭一層過濾，用成交量來過濾，當成交量是0時，自動濾掉
不過濾掉，我的一些技術指標的數據計算會錯誤

要使用yahoo finance當數據來源的要注意這部分
2024-10-04 23:59 #1498
Morten Hsu
Morten Hsu
0分
1499樓
Morten Hsu
Morten Hsu
個人積分：0分
文章編號：90434100
Morten Hsu wrote:
非常感謝Snare大...(恕刪)

Snare大師您好：
經過兩個星期的練習日期期間的輸入和查詢按鈕的寫法，略有所得。現在想再學進一步的項目選擇，該項目預設為融資融券餘額("//*[@id="selSheet"]/option[1]")，點入之後才可選擇借券資訊("//*[@id="selSheet"]/option[2]")。
我用chrome.FindElementsByXPath("/html/body/table[2]/tbody/tr[2]/td[3]/div[5]/div/div/table[1]/tbody")(1).AsTable.ToExcel Sheets("借券資訊").Range("a2")，卻得到融資融券餘額的資料，不是借券資訊。
嘗試用TempArray=table(23).ASTable.Data 也只得到A欄日期列表的空白資料。
請教大師該如何進入下一層取得借券資訊？
2024-10-06 13:03 #1499
snare
snare
3127分
樓主
snare
snare
個人積分：3127分
文章編號：90437350
Morten Hsu wrote:
點入之後才可選擇借券資訊("//*[@id="selSheet"]/option[2]")。


   chrome.FindElementByXPath("//*[@id='selSheet']/option[2]").Click
   chrome.Wait 3000



Morten Hsu wrote:
嘗試用TempArray=table(23)


table 22


2024-10-07 9:35 #1500
我要回覆

小惡魔廣編特輯

YAMAHA AUGUR 將科技智能融入日常騎乘!
比特幣 ETF 單週淨流出 6 億美元 技術面現死亡交叉警訊
MSI Katana 15 / Katana 17 揮舞效能之刃，開啟電競新戰場！
文章分享
加入好友名單
取消 確定
評分
取消 確定
評分
取消 確定
複製連結
複製
請輸入您要前往的頁數(1 ~ 157)
確定
留言回報
取消 確定

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