[µo°Ý] ­ÓªÑ¾ú¥v»ù®æªí

Scott090

¹d¦ë¾ú¥v»ù®æªíªººô§}­ì¨Ó¬O¡G
       "https://www.cnyes.com/twstock/ps_historyprice/2330.htm"
       ¥B­ì¨Óªº°Ñ¼Æ¬° "&ctl00$ContentPlaceHolder1$startText=" & StartDate ªº®æ¦¡

  ²{¦b¤w§ïÅܦ¨¬°¡G
       "https://invest.cnyes.com/twstock/tws/2330/history"
½Ð°Ý¦p¦ó§ó¥¿¯à¨ú±o¸ê®Æ?

   Sub ¹d¦ë¾ú¥vK_Test()
    Dim sh As Worksheet
    Dim oXmlhttp As Object, oHtmldoc As Object
    Dim URL As String, E As Variant
    Dim StartDate$, EndDate$, submitBTN$, ttt#, tt#
    Dim a As Variant, Table As Object, Ar_Code()
    Dim oDOC As Object, Req$
    Dim Re%, Ce%, k%, n%, i%, dataLen%
    Dim stockno$
    Set sh = Sheets("¸ÕÅç­¶"): sh.Select
   
    stockno = "2330"
      StartDate = Format("2010/01/01", "yyyy-mm-dd")    '°_©l¤é´Á
      EndDate = Format(Date, "yyyy-mm-dd")
      StartDate = "jsx-197276814 date_start=" & StartDate   '(??)
      EndDate = "& jsx-197276814 date_end=" & EndDate   '(??)
      submitBTN = "& jsx-197276814 action_submit=®M¥Î"   '(??)
      Req = StartDate & EndDate & submitBTN                         '(??)
    i = 0: k = 0
    ttt = timer
   
    Set oXmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set oHtmldoc = CreateObject("htmlfile")

    Application.DisplayStatusBar = True
    DoEvents
     
    With oXmlhttp
            URL = "https://invest.cnyes.com/twstock/tws/" & stockno & "/history"
            .Open "POST", URL, False
            .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
            .setRequestHeader "Referer", URL
            .setRequestHeader "Cache-Control", "no-cache"
            .setRequestHeader "Pragma", "no-cache"
            .setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
            .send Req
            
             tt = timer
            Do While .Status <> 200 And timer - tt < 3
                DoEvents
            Loop
''''           DoEvents
           
           'ºô­¶¥¼·Ç³Æ¦n¡AÃö³¬­«±Ò
            If .Status <> 200 Then
                Set oXmlhttp = Nothing
               
                Exit Sub
            End If

           oHtmldoc.write .responsetext

            Set E = oHtmldoc.all.tags("TABLE")(0)
            If E Is Nothing Then
                Debug.Print stockno & " E.Table = Null"
                Exit Sub
            End If
            
            dataLen = E.Rows.Length
            ReDim price(dataLen, 8)
                           
            For Re = 1 To dataLen - 1
                  price(Re, 1) = E.Rows(Re).Cells(0).innertext          '¤é´Á
                  For Ce = 1 To 7
                        If Ce = 6 Then
                        
                            '¥h°£ %
                            price(Re, Ce + 1) = Val(Replace(E.Rows(Re).Cells(Ce).innertext, "%", ""))
                        Else
                        
                            '¥h°£¤d¤À¸¹¡A¶}¡B°ª¡B§C¡B¦¬¡Bº¦¶^  º¦% ¦¨¥æ¶q ' ¦¨¥æª÷ÃB
                            price(Re, Ce + 1) = Val(Replace(E.Rows(Re).Cells(Ce).innertext, ",", ""))
                        End If
                        
                  Next
            Next
            
'''           Debug.Print timer - ttt

        
    End With
    Application.DisplayStatusBar = False
    Set oXmlhttp = Nothing
    Set oHtmldoc = Nothing
    Set E = Nothing
   
    [A1].Resize(dataLen, 8) = price
    Exit Sub   
   
End Sub

   ÁÂÁÂ

joey0415

¦^´_ 1# Scott090

https://ws.api.cnyes.com/charting/api/v1/history?resolution=D&symbol=TWS:2330:STOCK&from=1586390400&to=1428364800

¤W­±ªº®æ¦¡¬°JSON
from=1586390400&to=1428364800

¬õ¦r¬°timestamp
Âà´«½d¨Òºô¯¸
https://www.cadch.com/article/timestamp/index.php

VBA°Ñ¦Ò
http://white5168.blogspot.com/2017/03/unix-timestamp-excel-vba.html

Scott090

¦^´_ 2# joey0415

ÁÂÁÂjoey04152¤j ªº«ü¾É
¸ÕµÛ¥h°µ¡A¦³°ÝÃD¦A½Ð±Ð¡C

Scott090

¦^´_ 2# joey0415

  ¸Õ§@¦p¤U¡A½Ð joey0415 ¤j°Ñ¦Ò©ò¥¿¡AÁÂÁ¡G

  Option Base 1  
'myArr() ªºÄæ¦ì¡G ¤é´Á¡B¶}¡B°ª¡B§C¡B¦¬¡Bº¦¡Bº¦%¡B±i¼Æ
'¹d¦ë¸ê®Æ¦C ¡G¤é´Á¡B¶}¡B°ª¡B§C¡B¦¬¡B±i¼Æ
Sub ¹d¦ë¾ú¥vK_Test1()
      Dim stockno$, myTEXT, myText1
      Dim sh As Worksheet
      Dim T!, recCAT$, i%, j%, k%, n%, Trade%, BarCntReq%
      Dim myXML As Object, URL$, myArr
      Dim StartDate&, EndDate&
      Const Dat% = 1, Op% = 2, Hi% = 3, Low% = 4, Klose% = 5, CHG% = 6, CHGpercent% = 7, Vol% = 8    'for myArr
      
      StartDate = DateToUnixTime("2020/01/02")
      EndDate = DateToUnixTime(Format(Date, "yyyy/mm/dd"))
      Application.DisplayStatusBar = True
      Application.StatusBar = stockno & " ³sºô¤¤... "
      Set myXML = CreateObject("WinHttp.WinHttpRequest.5.1")
      recCAT = "D"          '¤é½u¹Ï
      URL = "https://ws.api.cnyes.com/charting/api/v1/history?resolution=" & recCAT & "&symbol=TWS:2330:STOCK&from=" & EndDate & "&to=" & StartDate
      T = timer
      With myXML
          .Open "GET", URL, False
          .send
          Do While .Status <> 200
             DoEvents
             If timer - T > 3 Then Exit Do
          Loop
         
          myTEXT = .responsetext                '¤å¦r¦ê .txt
      End With
      
      Set myXML = Nothing
      If myTEXT = "" Then GoTo Exit_Sub
      myTEXT = Split(myTEXT, ":")
      
      myText1 = Split(Replace(Replace(myTEXT(5), "[", ""), "]", ""), ",")
      n = UBound(myText1): ReDim myArr(n + 1, 8)
      For i = 0 To UBound(myText1) - 1
          myArr(i + 1, 1) = UnixTime2Date(myText1(i))
      Next

          k = 4
      For j = 6 To 10         '¶}¡B°ª¡B§C¡B¦¬¡B±i¼Æ
          myText1 = Split(myTEXT(j), ",")
          If j = 10 Then k = 2               '±±¨î myArr ªº¦æ¼Æ(column), ²Ä8¦æ¬O±i¼Æ
          For i = 0 To n - 1
               myArr(i + 1, j - k) = myText1(i)
          Next
          myArr(1, j - k) = Replace(myArr(1, j - k), "[", ""): myArr(n, j - k) = Replace(myArr(n, j - k), "]", "")
      Next j
      For i = 1 To n - 1
          myArr(i, 6) = myArr(i, 5) - myArr(i + 1, 5): myArr(i, 7) = Format(myArr(i, 6) / myArr(i + 1, 5) * 100, "#0.00")    'º¦¶^¡Bº¦¶^%
      Next
      
Exit_Sub:
      Set myXML = Nothing
      Application.StatusBar = ""
      Application.DisplayStatusBar = False

End Sub

Function DateToUnixTime(dstring) As Long
      DateToUnixTime = (DateValue(dstring) - #1/1/1970 8:00:00 AM#) * 86400
End Function

Function UnixTime2Date(UnixT) As Date
      UnixTime2Date = Format(UnixT / 86400 + #1/1/1970 8:00:00 AM#, "yyyy/mm/dd")
End Function

Scott090

¦^´_ 1# Scott090

¹d¦ë¾ú¥v»ù®æªíªººô§}­ì¨Ó¬O¡G
       "https://www.cnyes.com/twstock/ps_historyprice/2330.htm"
   ¤w§ï¬°¡G "https://www.cnyes.com/archive/twstock/ps_historyprice/2330.htm"
       ¥B­ì¨Óªº°Ñ¼Æ¬° "&ctl00$ContentPlaceHolder1$startText=" & StartDate ªº®æ¦¡¤@¼Ë¥i¥Î

  ·sª©ªººô§}¦b¡G
       "https://invest.cnyes.com/twstock/tws/2330/history"
     ¥Lªº°Ñ¼Æ´N¤£ª¾¦p¦ó°µ¤F?????

GBKEE

¦^´_ 5# Scott090
½Ð¤F¸Ñ ºô­¶±±¨î¶µ¤º®e
--https://invest.cnyes.com/twstock/tws/2330/history

1. '****¿ï¾Ü®É¶¡°Ï¶¡ªº¦U«ö¶s   [ ºô­¶±±¨î¶µ¤º®e]  ********
   
2. '<button class="jsx-197276814 period_btn" data-period="1d">1¤é</button>
   
3. '<button class="jsx-197276814 period_btn" data-period="5d">5¤é</button>
   
4. '<button class="jsx-197276814 period_btn" data-period="3m">3¤ë</button>
   
5. '<button class="jsx-197276814 period_btn" data-period="6m">6¤ë</button>
   
6. '<button class="jsx-197276814 period_btn" data-period="thisYear">¤µ¦~¥H¨Ó</button>
   
7. '<button class="jsx-197276814 period_btn" data-period="1y">1¦~</button>
   
8. '<button class="jsx-197276814 period_btn" data-period="5y">5¦~</button>
   
9. '<button class="jsx-197276814 period_btn" data-period="10y">³Ì¤j</button>
   
10. '<button class="jsx-197276814 period_btn period_btn--active" data-period="3m">3¤ë</button>
    
11. '****¿ï¾Ü®É¶¡°Ï¶¡«á«ö¤U (®M¥Î) ªº«ö¶s   [ ºô­¶±±¨î¶µ¤º®e]  ********
    
12. '<button class="jsx-197276814 action_submit">®M¥Î</button>
    
13. ''****¿ï¾Ü®É¶¡°Ï¶¡«á«ö¤U(®M¥Î)«ö¶s©ÒÅã¥Üªº¤é´Á      [ ºô­¶±±¨î¶µ¤º®e]  ********
    
14. '<span class=    "jsx-197276814 btn_txt">2020/04/09 - 2020/04/14</span></button>
    
15. ''****ºô­¶©ÒÅã¥ÜªÑ²¼ªº¦WºÙ      [ ºô­¶±±¨î¶µ¤º®e]  ********
    
16. '<h2 class="jsx-969407034 jsx-1444699802 sub_title">¥x¿n¹q</h2>

½Æ»s¥N½X

Ū¨úºô­¶¸ê®Æªºµ{¦¡½X ½Æ»s¦b¦P¤@¼Ò²Õ¤¤

1. Option Explicit
   
2. Sub Ex_¹d¦ëºô_¾ú¥v»ù®æ()
   
3.     Dim e As Object, R As Double, C As Integer
   
4.      Àˬd
   
5.     Application.ScreenUpdating = False
   
6.     With CreateObject("InternetExplorer.Application")
   
7.       '  .Visible = True
   
8.         .Navigate "https://invest.cnyes.com/twstock/tws/" & [B1] & "/history"
   
9.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
   
10.         If InStr(.Document.BODY.INNERTEXT, "±z·Q¬Ýªº­¶­±¤£¦s¦b") Then
    
11.             .Quit    ''**   ±z·Q¬Ýªº­¶­±¤£¦s¦b
    
12.             MsgBox [B1] & "  §ä¤£¨ì ??? ":             End
    
13.         End If
    
14.         For Each e In .Document.ALL.TAGS("BUTTON")               '**«ü©w®É¶¡°Ï¶¡
    
15.             If e.CLASSNAME = "jsx-197276814 period_btn" And e.INNERTEXT = [D1].Text Then e.Click:  Exit For
    
16.         Next
    
17.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
18.         For Each e In .Document.ALL.TAGS("BUTTON")               '**«ü©w®É¶¡°Ï¶¡«á«ö¤U(®M¥Î)Áä
    
19.             If e.CLASSNAME = "jsx-197276814 action_submit" And e.INNERTEXT = "®M¥Î" Then e.Click: Exit For
    
20.         Next
    
21.         Do While .Busy Or .readyState <> 4: DoEvents: Loop
    
22.         For Each e In .Document.ALL.TAGS("H2")                            '**Åã¥ÜªÑ²¼ªº¦WºÙ
    
23.             If e.CLASSNAME = "jsx-969407034 jsx-1444699802 sub_title" Then Range("B2") = e.INNERTEXT:  Exit For
    
24.         Next
    
25.         For Each e In .Document.ALL.TAGS("SPAN")                       '**Åã¥Ü©Ò«ü©wªº®É¶¡°Ï¶¡
    
26.             If e.CLASSNAME = "jsx-197276814 btn_txt" Then Range("D2") = e.INNERTEXT:  Exit For
    
27.         Next
    
28.         '*********************µ¥­Ô¸ê®Æ¤U¸ü§¹¦¨***********************************************
    
29.         Do While .Busy Or .readyState <> 4 And .Document.ALL.TAGS("table")(0) Is Nothing: DoEvents: Loop
    
30.         '********************¸ê®Æ¤º®e¾É¤J¤u§@ªí***********
    
31.         With .Document.ALL.TAGS("table")(0)
    
32.             For R = 0 To .Rows.Length - 1
    
33.                 For C = 0 To .Rows(R).Cells.Length - 1
    
34.                     Cells(R + 3, C + 1) = .Rows(R).Cells(C).INNERTEXT
    
35.                 Next
    
36.             Next
    
37.         End With
    
38.         .Quit     '*********Ãö³¬ºô­¶
    
39.     End With
    
40.     [f1] = "***¸ê®Æ¤U¸ü §¹¦¨***"
    
41.    Exit Sub
    
42. End Sub
    
43. Private Sub Àˬd()
    
44.          ''**¿ï¾Ü®É¶¡°Ï¶¡-> Range("B2") ªº³B¦s®æ ¾É¤J[ÅçÃÒ¸ê®Æ]
    
45.         With Range("D1").Validation
    
46.             .Delete
    
47.             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    
48.             xlBetween, Formula1:="1¤é,5¤é,3¤ë,6¤ë,¤µ¦~¥H¨Ó,1¦~,5¦~,³Ì¤j"   '
    
49.         End With
    
50.         If [B1] = "" Or [D1] = "" Then    '** Range("B2")  »Ý¬OªÑ²¼¥N¸¹
    
51.             MsgBox "ªÑ²¼¸¹½X??  Or ®É¶¡°Ï¶¡ >>"
    
52.              End         '***µ²§ôµ{¦¡
    
53.          End If
    
54.         UsedRange.Offset(1).Clear
    
55.       [f1] = "***¸ê®Æ¤U¸ü¤¤....***"
    
56.       Application.ScreenUpdating = True
    
57.       Application.Wait (Time + #12:00:01 AM#)
    
58. End Sub

½Æ»s¥N½X

Scott090

¦^´_ 6# GBKEE


    ·P®¦ GBKEE ¤j
     §Ú¬ã¨s¤j§@¬Ý¬Ý

quickfixer

¦^´_ 7# Scott090

01¨ºÃä¤W¬P´Á´N¦³cnyesªºXml json code
¥i¥H¥h¬Ý¬Ý

Scott090

¦^´_ 8# quickfixer


    mobile01 ½×¾Â¶Ü?
ÁÂÁ quickfixer ¤jªº°T®§

Scott090

¦^´_ 8# quickfixer


    quickfixer ¤j:
    https://www.mobile01.com/topicde ... ;t=4737630&p=77
   «Ü¥i±¤¡A§Ú¨S§ä¨ì¡C
    ¥i§_©ú¥Ü¬O²Ä´X¼Ó©Î¥Lªº Xml json code

   ÁÂÁÂ