ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¤§«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

¦^´_ 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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

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

TOP

½Ð°Ý§Ú¬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?

TOP

¦^´_ 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¤è
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

·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­þ?

TOP

¦^´_ 6# adranimal
¥J²ÓÂI°Ú¡A§A.WebTables Äݩʧï¤F¨â¦¸
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

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

TOP

¦^´_ 8# adranimal
¤@¤G¼Ó³£¦³½u¯Á¡A¦Û¤v°µ°µ¬Ý
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

ÁÂÁÂ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ÁÂ

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD