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

[µo°Ý] ¸ê®Æ±Æ§Ç¤ÏÂà

¦^´_ 9# c_c_lai


    ¤S¨Ó½Ð±Ð¤j¤j
±qºô¸ô¤U¸üªº¸ê®Æ¬O¤é´Á¤Ïªº¡A¦Ó¹Ïªí¤Ï¦V¡A
½Ð°Ý ¤j¤j­n­×§ï¨º­Ó¦a¤è
ÁÂÁÂ

test.rar (979.9 KB)

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-10-11 20:02 ½s¿è

¦^´_ 11# wufonna
  1. Sub MySort()
  2.      Dim EndKBarRow As Long
  3.         
  4.     With Sheets("data")
  5.         EndKBarRow = .Range("A" & Rows.Count).End(xlUp).Row
  6.    
  7.         With .Sort
  8.             .SortFields.clear
  9.             .SortFields.Add Key:=Range("A5"), SortOn:=xlSortOnValues, Order:=xlAscending
  10.             '  ¸ê®Æ¥Ñ¤p¦Ó¤j¨Ì§Ç±Æ§Ç
  11.             '  .SortFields.Add Key:=Range("A5"), SortOn:=xlSortOnValues, Order:=xlDescending
  12.             '  ¸ê®Æ¥Ñ¤j¦Ó¤p¨Ì§Ç±Æ§Ç
  13.             .SetRange Range("A5:R" & EndKBarRow)
  14.             ' .Header = xlNone
  15.             .Apply
  16.         End With
  17.     End With
  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 12# c_c_lai


    ·PÁ¤j¤j¡A¤w§@¥X¤j½L¡AÁÂÁ ^o^

¥xÆW¥[Åv«ü¼Æ-³ø¹S²v°Ï¶¡¹ÏII.rar (956.47 KB)

TOP

¦^´_ 13# wufonna
§A¦b drawCharts() ³Ì«e¡B¤Î³Ì«á¥[¤W¤@¦æ (¦p¤U) ø¹Ï§¹¦¨«áµe­±·|§ó²M²n¡C
  1. Sub drawCharts()
  2.     Dim toindexRows As Single, totalRows As Single, totalRows2 As Single
  3.     Dim VIMax As Single, VIMin As Single
  4.     Dim xRow, yCol, cHeight, cWidth As Integer
  5.     Dim text As String
  6.     Dim chartname As String
  7.     Dim sRowHeight As Single

  8.     '  xRow = 3
  9.     xRow = 1
  10.     yCol = 1
  11.     '  cHeight = 450      'CHeight ©w¸q¬°¹Ïªí©Ò¦ûªº¦C°ª
  12.     '  cWidth = 700
  13.     cHeight = 360         ' CHeight ©w¸q¬°¹Ïªí©Ò¦ûªº¦C°ª
  14.     cWidth = 720
½Æ»s¥N½X
  1.     Sheets("chart").[A1].Select
  2. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-10-12 07:43 ½s¿è

¦^´_ 13# wufonna
§Ú¦b drawCharts() ¥[¤F¤@¤p¬qµ{¦¡¡A¥¦±N¼ÐÃD²¾¥Xø¹Ï°Ï¡A
¦p¦¹¡A¤ñ¸û¤£·|À£¨ì K ½u¹ÏªºÃ¸»s (ªþ¤W¡G «e¡B«áªº¹Ïªí´£¨Ñ¤ñ¸û)¡G
  1.         .ChartArea.Height = cHeight                     ' ¹Ïªí°ª«×
  2.         .ChartArea.Width = cWidth                       ' ¹Ïªí¼e«×
  3.          '
  4.         With .PlotArea                          ' ¹ÏªíªºÃ¸¹Ï°Ï
  5.             .Top = 20                           ' ¹Ïªíªº¹ê»Úø¹Ï°Ï°_©l¦ì¸m
  6.             .Left = 1
  7.             .Width = .Parent.ChartArea.Width
  8.             .Height = .Parent.ChartArea.Height
  9.             .Interior.ColorIndex = xlNone
  10.             .InsideHeight = cHeight - 70        ' ½Õ¾ã¹ÏªíªºÃ¸¹Ï°Ï¤º³¡°ª«× (¬°°t¦X.Legend.Position = xlBottom )
  11.         End With
½Æ»s¥N½X

TOP

¦^´_ 15# c_c_lai


    ÁÂÁÂ ¤j¤j
   §ä¤F¦n¤[¨Ó¨ì¤F³oª©ª©¡A¤S¬Ý¨ì¤F¤j¤jªº K ½uªÑ²¼¹Ï¦p¦ó¯à»P¥D¤O¡B´²¤á¡B¤Î¦¨¥æ¶q½u¦@¦s¡H ½u¹Ï¦@¦s¡A¾Ç²ß°µ¬yªe¹Ï¡AÁÂÁ ¤j¤j µL¨pªº±Ð¾É¡C

¥xÆW¥[Åv«ü¼Æ-³ø¹S²v°Ï¶¡¹ÏIII.rar (959.32 KB)

TOP

¦^´_ 16# wufonna
§Ú±N "¥xÆW¥[Åv«ü¼Æ-³ø¹S²v°Ï¶¡¹ÏIII"  µyµy¾ã²z¤F¤@¤U¡A
¨Ã¥[¥Hµ{¦¡½X¹ï¦ì¡A§Æ±æ¹ï§A¯à¦³©Ò§U¯q¡C
drawCharts2() ¬O§Ú±`À³¥ÎªºÃ¸¹Ï¤è¦¡¡A¨Ñ§A°Ñ¦Ò¡I
¥xÆW¥[Åv«ü¼Æ-³ø¹S²v°Ï¶¡¹ÏIII.rar (921.68 KB)

TOP

¦^´_ 17# c_c_lai


    ÁÂÁÂ ¤j¤j  ^0^

TOP

ªe¬y¹Ïºô§}µL®Ä
°Ñ¦Ò¤@¨Ç¸ê®Æ
https://finance.yahoo.com/quote/2330.TW/history?period1=1575244800&period2=1631404800&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true


Private Const Unix1970 As Long = 25569 'CDbl(DateSerial(1970, 1, 1))

'Dim s As String

Public Function Date2Unix(ByVal vDate As Date) As Long

Date2Unix = DateDiff("s", Unix1970, vDate)


End Function

Sub test()
Debug.Print s

Debug.Print DateDiff("s", Unix1970, vDate)
Debug.Print Date2Unix("2019/6/7")
Debug.Print Date2Unix(Date - 365 * 3)  '¨ú¤T¦~
Debug.Print Now()
Debug.Print Date
Debug.Print Date2Unix(Date)
Debug.Print "https://finance.yahoo.com/quote/2330.TW/history?period1=" & Date2Unix("2019/6/7") & "&period2=" & Date2Unix(Date) & "&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true"

End Sub



'debug
'

'-2209161600
' 1559865600
' 1536796800
'2021/9/12 ¤U¤È 09:45:15
'2021/9/12
' 1631404800
'https://finance.yahoo.com/quote/2330.TW/history?period1=1559865600&period2=1631404800&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true





'https://white5168.blogspot.com/2017/03/unix-timestamp-excel-vba.html?fbclid=IwAR1FQDyY3A1utM3vaT24SKcoybW02PUycFBWuW8JXncdCfzk5wTEDZgi3vw#.YT33950zbcs
'https://www.cadch.com/article/timestamp/index.php
'https://finance.yahoo.com/quote/2330.TW/history?period1=1575244800&period2=1631404800&interval=1d&filter=history&frequency=1d&includeAdjustedClose=true
'http://forum.twbts.com/thread-22471-1-2.html?fbclid=IwAR0V3t8wojYYInLQXamUi6ZJqPeSRQGHhSR9RBia2xRh5YJw2ZIUvxMDWSw
'°Ñ¦Ò¸ê®Æ

'debug.print https://finance.yahoo.com/quote/2330.TW/history?period1=" & Date2Unix("2019/6

TOP

¦^´_ 1# wufonna

https://www.mobile01.com/topicdetail.php?f=511&t=4737630&p=28

°Ñ¦Ò 274¼Ó ­×§ï ªe¬y¹Ï
  1. Sub Getyahoofinance_Jsondata()

  2.     Dim Xmlhttp As Object, FileName As String, Url As String, urla As String, Crumbkey As String, stock As String, startday As String, endday As String
  3.     Dim Jsondata As Object, DecodeJson, temp
  4.     Dim TimeStamp, adjclose, quote_Open, quote_High, quote_Low, quote_Close, quote_Volume
  5.     Dim ttt, I
  6.     Set Jsondata = CreateObject("HtmlFile")
  7.     Jsondata.write "<script>document.JsonParse=function (s) {return eval('(' + s + ')');}</script>"

  8. '    Sheets("¤u§@ªí1").Cells.Clear
  9. '    Sheets("¤u§@ªí1").Range("a1:g1") = Array("Date", "Open", "HIgh", "Low", "Close", "Adj Close", "Volume")
  10.     With ¤u§@ªí1
  11.           Dim AR
  12.            AR = .Range("A1:G4")
  13.            .Range("A:G") = ""
  14.            .Range("A1:G4") = AR
  15.     End With
  16.    
  17.     stock = InputBox("ªÑ²¼¥N¸¹", , "^TWII")
  18.     startday = Format(InputBox("¶}©l¤é´Á(8½X¼Æ¦r)", , "20170101"), "####/##/##")
  19.     endday = Format(InputBox("µ²§ô¤é´Á(8½X¼Æ¦r)", , Format(Date, "yyyymmdd")), "####/##/##")
  20.    
  21.     If startday > endday Or stock = "" Or startday = "" Or endday = "" Then
  22.         MsgBox "¸ê®Æ¿é¤J¿ù»~", vbOKOnly, "½Ð­«·s¿é¤J"
  23.         Exit Sub
  24.     End If
  25.    
  26.   
  27.     ttt = Timer
  28.    
  29.     Url = "https://finance.yahoo.com/quote/" & stock & "/history?period1=" & DataToUnixTime(startday) & "&period2=" & DataToUnixTime(endday) & "&interval=1d&filter=history&frequency=1d"
  30.    
  31.     Set Xmlhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
  32.     With Xmlhttp
  33.    
  34.        .Open "GET", Url, False
  35.        .send
  36.       
  37.         Crumbkey = Left(Split(.responsetext, """CrumbStore"":{""crumb"":""")(1), 11)
  38.         urla = "https://query2.finance.yahoo.com/v8/finance/chart/" & stock & "?formatted=true&crumb=" & Crumbkey & "&lang=en-US&region=US&period1=" & DataToUnixTime(startday) & "&period2=" & DataToUnixTime(endday) & "&interval=1d&events=div%7Csplit&corsDomain=finance.yahoo.com"

  39.        .Open "GET", urla, False
  40.        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
  41.        .setRequestHeader "Referer", Url
  42.        .send
  43.         
  44.         Set DecodeJson = Jsondata.JsonParse(.responsetext)
  45.         Set temp = CallByName(CallByName(CallByName(DecodeJson, "chart", VbGet), "result", VbGet), "0", VbGet)
  46.         
  47.         TimeStamp = Split(CallByName(temp, "timestamp", VbGet), ",")
  48.         adjclose = Split(CallByName(CallByName(CallByName(CallByName(temp, "indicators", VbGet), "adjclose", VbGet), "0", VbGet), "adjclose", VbGet), ",")
  49.         quote_Open = Split(CallByName(CallByName(CallByName(CallByName(temp, "indicators", VbGet), "quote", VbGet), "0", VbGet), "open", VbGet), ",")
  50.         quote_High = Split(CallByName(CallByName(CallByName(CallByName(temp, "indicators", VbGet), "quote", VbGet), "0", VbGet), "high", VbGet), ",")
  51.         quote_Low = Split(CallByName(CallByName(CallByName(CallByName(temp, "indicators", VbGet), "quote", VbGet), "0", VbGet), "low", VbGet), ",")
  52.         quote_Close = Split(CallByName(CallByName(CallByName(CallByName(temp, "indicators", VbGet), "quote", VbGet), "0", VbGet), "close", VbGet), ",")
  53.         quote_Volume = Split(CallByName(CallByName(CallByName(CallByName(temp, "indicators", VbGet), "quote", VbGet), "0", VbGet), "volume", VbGet), ",")
  54.         
  55.       
  56.     End With
  57.    
  58.    
  59.     With ¤u§@ªí1
  60.         Application.ScreenUpdating = False
  61.         For I = 0 To UBound(TimeStamp)
  62.             .Cells(I + 5, 1) = Format(TimeStamp(I) / 86400 + #1/1/1970 8:00:00 AM#, "yyyy/mm/dd")
  63.             .Cells(I + 5, 2) = quote_Open(I)
  64.             .Cells(I + 5, 3) = quote_High(I)
  65.             .Cells(I + 5, 4) = quote_Low(I)
  66.             .Cells(I + 5, 5) = quote_Close(I)
  67.             .Cells(I + 5, 6) = adjclose(I)
  68.             .Cells(I + 5, 7) = quote_Volume(I)
  69.             .Cells.EntireColumn.AutoFit
  70.        '     .Cells(1, 1).Select
  71.         Next I
  72. '        Application.ScreenUpdating = True
  73. '        MsgBox "¶}©l¤é´Á" & startday & vbNewLine & "µ²§ô¤é´Á" & endday & vbNewLine & _
  74. '        "ªÑ²¼¥N¸¹" & stock & vbNewLine & "¸ê®Æµ§¼Æ" & .Range("a1").CurrentRegion.Rows.Count - 1 & "µ§" & vbNewLine & _
  75. '        "¨Ï¥Î®É¶¡" & Round(Timer - ttt, 2) & "¬í", vbOKOnly, "¤U¸ü§¹¦¨"
  76.     End With
  77.    
  78.    
  79.    
  80.     Set Xmlhttp = Nothing
  81.     Set DecodeJson = Nothing
  82.     Set temp = Nothing
  83.    
  84. End Sub

  85. Sub DeleteEmptyRows()
  86. Dim LastRow As Long, r As Long

  87.    
  88. With ¤u§@ªí1
  89. LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
  90. LastRow = LastRow + .Range("A" & .Rows.Count).End(xlUp).Row

  91. For r = LastRow To 5 Step -1
  92.     If .Cells(r, 2) = "" Then
  93.     Debug.Print .Cells(r, 1).Value
  94.    
  95.     .Rows(r).Delete
  96.     End If
  97.     Next r

  98. End With

  99.         Application.ScreenUpdating = True

  100. End Sub

  101. Function DataToUnixTime(dstring) As Long
  102.     DataToUnixTime = (DateValue(dstring) - #1/1/1970 8:00:00 AM#) * 86400
  103. End Function
½Æ»s¥N½X

¥xÆW¥[Åv«ü¼Æ-³ø¹S²v°Ï¶¡¹ÏIII-20022xlsm.rar (596.46 KB)

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD