Excel 股票行情数据
18
10 月

Excel 股票行情数据

新浪的数据接口:http://hq.sinajs.cn/list=sz000002,其中sz000002是股票代码,一定要加上sh或sz的标识,输入浏览器,返回结果如下:

var hq_str_sz000002="万 科A,25.900,26.000,26.040,26.100,25.510,26.030,26.050,46121388,1189370537.360,2800,26.030,9600,26.020,418300,26.010,19020,26.000,300,25.990,23900,26.050,123777,26.060,29000,26.070,73200,26.080,53500,26.090,2016-10-18,14:13:36,00";

以上的数据分别对应:

0:"万 科A",股票;
1:"25.900",今开;
2:"26.000",昨收;
3:"26.040",当前价格;
4:"26.100",今日最高价;
5:"25.510",今日最低价;
6:"26.030",竞价买一报价;
7:"26.050",竞价卖一报价;
8:"46121388",成交量;
9:"1189370537.360",成交额;
10~29为五档信息
30:"2016-10-18",日期;
31:"14:13:36",时间;

不明白的地方可以对着股票行情看一下就知道

excel加载行情要用到VBA代码,如下:

Sub a新浪股票()
    Dim xmlobject As Object
    Dim strReturn As String
    Dim strUrl As String
    Dim intLen As Long
    Dim intLenA As Long
    Dim arry As Variant

    Set xmlobject = CreateObject("microsoft.xmlhttp")
   
    For i = 1 To 5 '遍历,5等于当前股票行数
   
    strUrl = "http://hq.sinajs.cn/list=" & Cells(1 + i, 2) '起始股票代码单元格,格式要求前缀sz或sh
    xmlobject.Open "GET", strUrl, False
    xmlobject.send
    If xmlobject.readystate = 4 Then
        strReturn = xmlobject.responsetext
        intLen = Len(strReturn) - 25 '剔除无关数据
        strReturn = Mid(strReturn, 22, intLen)
        arry = Split(strReturn, ",") '按逗号分隔数据,放入数组arry
        intLenA = UBound(arry) - LBound(arry) + 1 '数组长度,此处未使用,可结合For遍历arry
       
       '获取目标数据
       Cells(1 + i, 3) = arry(0)
       Cells(1 + i, 4) = Round((arry(3) - arry(2)) / arry(2), 4) * 100
       Cells(1 + i, 5) = arry(3)
       Cells(1 + i, 6) = arry(1)
       Cells(1 + i, 7) = arry(2)
       Cells(1 + i, 8) = arry(4)
       Cells(1 + i, 9) = arry(5)
       Cells(1 + i, 10) = arry(8) / 1000000
       Cells(1 + i, 11) = arry(9) / 10000
       
    End If
    Next i
End Sub

文件下载:VBA_股票行情.rar

结果如下:

sinastock

2 条评论

  1. Z2017 年 03 月 11 日下午 11:20 回复

    你好,请问为什么代码复制到其他excel表格宏就无法运行了啊?谢谢!
    “运行时错误5,无效的过程调用或参数”

    • snowtraces*2017 年 03 月 13 日上午 12:31 回复

      代码中指定了行号,你要确认下,还有自己看报错代码位置