[µo°Ý] ¹B¥ÎVBA§ì¨úYahoo Finance APIsªÑ²¼¸ê®Æ

§ö¤¯ªG¤l

¦bºô¸ô¤W§ä¨ì¦¹§ì¨úªÑ»ùªº¤è¦¡¡A¦ýµLªk¶¶§Q¼g¤J¥¨¶°¤¤¡AÅý¸Õºâªí¤@¶}±Ò§Y¥i§ó·sªÑ»ù¡I
¬O­þ­Ó¦a¤è¥X¿ù¤F¡H


'«Å§iÅܼÆ
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer
   
'§i¶DExcel¤£­n¨C§ó·s¤@®æ´N­«·s­pºâ
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
   
'±N²{¦bªº¤u§@ªí³]¬°¸ê®Æªí
    Set DataSheet = ActiveSheet
  
'±q²Ä¤C¦C¶}©l¡A¥u­n²Ä¤@Äæ¡]AÄæ¡^¤º®e¤£¬OªÅªº¡A´N§â¥N½X¥[¨ìYahoo Finance APIªººô§}¤¤
    Range("C7").CurrentRegion.ClearContents
    i = 7
    qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
    i = i + 1
    While Cells(i, 1) <> ""
        qurl = qurl + "+" + Cells(i, 1)
        i = i + 1
    Wend
    qurl = qurl + "&f=" + Range("C2")
'±Nºô§}©ñ¨ìC1ªºÄæ¦ì¤¤
    Range("c1") = qurl
'§ì¨ú¸ê®Æ
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            
'±NCSVªº®æ¦¡¶ë¨ìExcelªºªí®æ·í¤¤
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
                        
   
'ÅýExcel­«·s¬¡¦^¨Ó¡AÅý¸ê®Æ¯à°÷Åã¥Ü
'turn calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'    Range("C7:H2000").Select
'    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
'        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Columns("C:C").ColumnWidth = 12
    Columns("J:J").ColumnWidth = 25.43
    Range("h2").Select