[µo°Ý] ²{¦b­n¦p¦ó¨ìÃÒ¥æ©Ò¤U¸ü¥~¸ê¦¨¥æ¸ê°T

vanguarx

¤§«e¬O¥Î¤U¦Cªºµ{¦¡½X ¨ì http://www.twse.com.tw/ch/trading/fund/TWT38U/TWT38U.php ³oÃä¥h¤U¸ü¨C¤é¥~¸ê¶R½æ¶W¸ê°T¡C
­ì¥»³£¨S¦³°ÝÃD¡A¦ý¤@¤ë¥÷ ÃÒ¥æ©Ò§ïºô­¶¡A­ì¥»¥i¥H¬Ý¨ì¤@­Ó¤U¸ü¸ô®|¡A¦ý²{¦b¨S¿ìªk¬Ý¨ì¡A¾É­P¤£ª¾¹D¸Ó¦p¦ó¤U¸ü¨C¤éªº CSV ÀÉ...

¦³¨S¦³¤Hª¾¹D¸Ó¦p¦ó¤U¸ü¡H

Dim st
Dim myURL As String
Dim oStream As Object           'ADODB.Stream
Dim WinHttpReq As Object            'XMLHTTP
Dim fileidx As String
Dim seldate As String

Worksheets("Config").Select
endline = Range("A65536").End(xlUp).Row


    seldate = Range("A" & i).Value    ''¦¨¥æ¤é´Á
    fileidx = seldate
   
    ''http://www.twse.com.tw/ch/trading/fund/TWT38U/TWT38U_print.php?edition=ch&filename=genpage/A20141202.dat&type=csv
   
    myURL = "http://www.twse.com.tw/ch/trading/fund/TWT38U/TWT38U_print.php?edition=ch&filename=genpage/A" & _
            fileidx & ".dat&type=csv"
         
    ''¥~¸ê¶R½æ¸ê°T
    Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
    With WinHttpReq
        .Open "GET", myURL, False
        .Send
        myURL = .responseText
    End With
    Set oStream = CreateObject("ADODB.Stream")
    With oStream
        .Open
        .Type = 1
        .Write WinHttpReq.responseBody
        fileidx = Sheets("Config").Range("G2") & "\A" & fileidx & ".csv"          ''³]©w¤U¸ü¸ô®|¤ÎÀɦW
        On Error Resume Next
        Kill fileidx
        On Error GoTo 0
        .SaveToFile fileidx
        .Close
    End With
    Set WinHttpReq = Nothing
    Set oStream = Nothing

End Sub

stillfish00

¦^´_ 1# vanguarx
§ï¥ÎPost , ¤j·§¦p¤U¡A¿é¤J¤é´Á©MÀx¦s¸ô®|¦Û¤v§ï¦¨§A­nªº§a~

1. Sub TestWeb()
   
2.     Dim myURL As String
   
3.     Dim oStream As Object           'ADODB.Stream
   
4.     Dim WinHttpReq As Object            'XMLHTTP
   
5.     Dim fileidx As String
   
6.     Dim sPost As String
   
7.     Dim dteQueryDate As Date
   
8.    
   
9.     dteQueryDate = #3/11/2015#
   
10.    
    
11.     myURL = "http://www.twse.com.tw/ch/trading/fund/TWT38U/TWT38U.php"
    
12.     sPost = "download=csv&qdate=" & Format(dteQueryDate, "e/mm/dd") & "&sorting=by_issue"
    
13.     sPost = Replace(sPost, "/", "%2F")  'or urlencode
    
14.    
    
15.     ''¥~¸ê¶R½æ¸ê°T
    
16.     Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
    
17.     With WinHttpReq
    
18.         .Open "POST", myURL, False
    
19.         .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
20.         .setRequestHeader "Content-Length", Len(sPost)
    
21.         .Send (sPost)
    
22.     End With
    
23.    
    
24.     Set oStream = CreateObject("ADODB.Stream")
    
25.     With oStream
    
26.         .Open
    
27.         .Type = 1
    
28.         .Write WinHttpReq.responseBody
    
29.         fileidx = ThisWorkbook.Path & "\" & Format(dteQueryDate, "yyyymmdd") & ".csv"          ''³]©w¤U¸ü¸ô®|¤ÎÀɦW
    
30.         On Error Resume Next
    
31.         Kill fileidx
    
32.         On Error GoTo 0
    
33.         .SaveToFile fileidx
    
34.         .Close
    
35.     End With
    
36.    
    
37.     Set WinHttpReq = Nothing
    
38.     Set oStream = Nothing
    
39. End Sub

½Æ»s¥N½X

vanguarx

OK ¤F.... ÁÂÁ«ü¾É

adranimal

½Ð°Ý§Ú¬O·Q±NÃÒ¥æ©Òªº¨C¤é¦¬½L¦æ±¡(¤£§tÅvÃÒ¤ûºµÃÒ)¡A¶K¤Wexcel¡A²{¦b¥Îpostªk¸Ó«ç»ò¼gªº
­ì¥»getªk¬O³o¼Ë¼gªº
Sub getSIIPrice()
    urlStr = "URL;http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/genpage/Report" & Format(Sheets("UI").Range("A21").Value, "YYYYMM") & "/A112" & Format(Sheets("UI").Range("A21").Value, "YYYYMMDD") & "ALLBUT0999_1.php?select2=ALLBUT0999&chk_date=" & Year(Sheets("UI").Range("A21").Value) - 1911 & "/" & Format(Sheets("UI").Range("A21").Value, "MM/DD")
    Sheets("SIIPrice").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    With ActiveSheet.QueryTables.Add(Connection:= _
        urlStr _
        , Destination:=Range("A1"))
        .Name = "19"
        .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 = "10"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        .Delete
    End With
End Sub

«á¨Ó§Ú±Nºô§}§ï¬°http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php
ActiveSheet.QueryTables.Add¼W¥[¤@­ÓPostText°Ñ¼Æ«o¤£¦æ
½Ð°Ý¸Ó¦p¦ó§ó§ï©O?

stillfish00

¦^´_ 4# adranimal
urlstr = "URL;http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php"
.WebTables = "2"
.PostText = "download=&qdate=104%2F03%2F12&selectType=ALLBUT0999"

§ï³o¤T­Ó¦a¤è

adranimal

·PÁÂS¤j¦^ÂÐ
¥Ø«e§ï¦¨³o¼Ë
Sub getSIIPrice()

    urlStr = "URL;http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php"
    Sheets("SIIPrice").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
  With ActiveSheet.QueryTables.Add(Connection:= _
    urlStr _
     , Destination:=Range("A1"))
      .WebTables = "2"
      .PostText = "downlod=&qdate=104%2F03%2F12&selectType=ALLBUT0999"
      .Name = "19"
      .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 = "10"
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
      .Delete
   End With
End Sub

¦ý¬O¨S¦³¦^¶Ç¸ê®Æ¡A¤£ª¾°ÝÃD¥X¦b­þ?

stillfish00

¦^´_ 6# adranimal
¥J²ÓÂI°Ú¡A§A.WebTables Äݩʧï¤F¨â¦¸

adranimal

¦^´_ 7# stillfish00
·PÁÂS¤jªº«ü±Ð
¤w¸g¥i¥H¨Ï¥Î¤F
¦ý¬O¦pªG§Ú·Q­nÅý§ìÀɪº¤é´Áµ¥©óSheets("UI").Range("A22").Value À³¸Ó«ç»ò½s¼g©O?

stillfish00

¦^´_ 8# adranimal
¤@¤G¼Ó³£¦³½u¯Á¡A¦Û¤v°µ°µ¬Ý

go2hk

ÁÂÁÂstillfish00¤j¤jªºµ{¦¡½X
¶¶§Q¤U¸üÃÒ¥æ©Òªº¥~¸ê¶R½æ¶W¸ê°T
¥t¥~¤]¦Û¦æ§ï¦¨¤U¸ü§ë«H¶R½æ¶W¸ê°T
³£¯à¥¿±`¨Ï¥Î«D±`·PÁÂ

¦ý¬O·Q¥t¥~½Ð°Ý
¥H¤U¨â­Óºô­¶¬OÂd¶R¤¤¤ßªººô­¶
1.¤T¤jªk¤H¶R½æ©ú²Ó¸ê°T :
http://www.tpex.org.tw/web/stock/3insti/daily_trade/3itrade_hedge.php?l=zh-tw
2.¤WÂdªÑ²¼¨C¤é¦¬½L¦æ±¡(¤£§t©w»ù)
§Ú»Ý­nªº¬O©Ò¦³ÃÒ¨é(¤£§tÅvÃÒ.¤ûºµÃÒ)
http://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430.php?l=zh-tw

§Ú¦Û¦æ­×§ï¤Fºô§}ªº³¡¤À
µ²ªG§ì¦^¨ÓªºcsvÀÉ
¤º®e«o¬Oºô­¶ªº­ì©l½X
½Ð°Ý¬O¤£¬O¤U¦C³o¬q»Ý­n°µ¦ó½Õ¾ã

Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
    With WinHttpReq
        .Open "POST", myURL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .setRequestHeader "Content-Length", Len(sPost)
        .send (sPost)
    End With

©Î¬O¸Ó¦p¦ó½Õ¾ã
¨Ï±o¯à°÷¤U¸ü¦^csvÀÉ
·PÁÂ