091105

VBA代码如下:

Sub GetData()

    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Set DataSheet = ActiveSheet
        StartDate = DataSheet.Range("B1").Value
        EndDate = DataSheet.Range("B2").Value
        Symbol = DataSheet.Range("B3").Value
        Range("C7").CurrentRegion.ClearContents
        qurl = "https://ichart.yahoo.com/table.csv?s=" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) – 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) – 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("E3") & "&q=q&y=0&z=" & _
            Symbol & "&x=.csv"
        Range("b5") = qurl
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            Range("C7").CurrentRegion.TextToColumns Destination:=Range("C7"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
            Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"
            Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
            Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"

End Sub

下载地址(未来可能失效):

https://down.qiannao.com/space/file/uu1001/share/2009/11/5/-80a1-7968-6570-636e.xlsm/.page

使用说明:

我文件里写了上海和深证所有股票的命名规则。有朋友下载了,问我能不能取外盘数据,也是可以的。数据源其实就是finance.yahoo.com,数据质量是有保证的,外盘品种代码可以自己去搜,外汇期货都有,就是没有内盘期货数据(内盘期货数据目前觉得文华的质量最好,可以全部从那里导出)
下面列几个常用的:
道琼斯工业指数:^DJI
纳斯达克:^IXIC
恒生指数:^HSI
日经指数: ^N225
英国富时:^FTSE
CMX黄金:GCX09.CMX
NYM石油:CLZ09.NYM
谷歌:GOOG
IBM:IBM
代码输入到B3格子,点按钮。
如果想取所有数据,但不知道开始的时间,就直接填1900年开始也行。

说点什么