在Excel中刷新股票数据

发表于 分类于 ，

在Excel中刷新股票数据

基本功能

点击刷新按钮以后，读取excel中某一列保存的股票代码，然后通过股票数据API获取实时数据，并保存到excel中。

如下图，点击刷新按钮，读取D列的股票代码，获取实时数据，并将对应的数据写入到E - L列。
image

基本步骤

step-1 添加按钮

通过Developer -> Insert -> Button添加刷新按钮。

image

然后分配宏脚本，这里新定义一个RefreshBtnClicked()。

1
2
3
4
5
6
7
Option Explicit

Sub RefreshBtnClicked()
    '
    Debug.Print "Start Refresh..."
End Sub

step-2 定义股票数据结构类

定义CStock类(class)，主要目的是方便数据的结构化处理和存储。可以根据需要和使用的股票API接口返回的数据添加、删除对应字段。
image

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Option Explicit

Public code As String '股票代码
Public name As String '股票名称
Public lastClosePrice As Double '昨收
Public openPrice As Double '今开
Public currPrice As Double '最新价格
Public maxPrice As Double '最高
Public minPrice As Double '最低

Public volume As Double '
Public turnover As Double
Public lastUpdateDate As String
Public lastUpdateTime As String

' Get current increase (%) - 获取今日涨幅
Public Property Get increaseRate() As Double
    increaseRate = Round((currPrice - lastClosePrice) / lastClosePrice * 100, 2)
End Property

step-3 检查股票代码

接下来为了排除非法股票代码，通过代码首两个字母做一个简单的筛选，比如沪市(sh开头)、深市(sz开头)和港股(hk开头)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Public Enum STOCK_MKT
    MKT_HK
    MKT_SH
    MKT_SZ
    MKT_INVALID
End Enum

'Get Market type by stock code.
Function GetMarketType(stockCode As String) As STOCK_MKT
    Dim code As String
    code = LCase(stockCode)
    
    GetMarketType = MKT_INVALID
    
    If InStr(code, "hk") = 1 Then
        GetMarketType = MKT_HK
        GoTo Done
    End If
    
    If InStr(code, "sh") = 1 Then
        GetMarketType = MKT_SH
        GoTo Done
    End If
    
    If InStr(code, "sz") = 1 Then
        GetMarketType = MKT_SZ
        GoTo Done
    End If
    
Done:
    'xxx
    
End Function

step-4 API获取数据

通过API获取实时数据。这里以sina的股票数据为例，接口支持同时获取多个股票的数据（以逗号分开）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
Function GetStockDataByCode(stockCode As String) As Collection
    On Error GoTo Oops

    Dim url As String
    url = "http://hq.sinajs.cn/list=" + stockCode
    
    ' define stock
    Dim stocks As Collection
    Set stocks = New Collection
    
    Dim stock As CStock
        
    ' request stock data
    Dim http As Object
    Set http = CreateObject("Microsoft.XMLHTTP")
    http.Open "POST", url, False
    http.send ""
    If http.Status = 200 Then
        ' split by ";"
        Dim stockList As Variant
        stockList = Split(http.responseText, ";")
        
        Dim item As Variant
        Dim stockDataStr As String
        
        For Each item In stockList
            stockDataStr = item
            If Len(stockDataStr) > 6 Then
                Set stock = ParseSingleLine(stockDataStr)
                stocks.Add stock
            End If
            
            'release control to OS
            DoEvents
        Next
        
    End If
    
    Set GetStockDataByCode = stocks
Oops:
    '
    Debug.Print "[GetStockDataByCode] Error when handling stock: " & stockCode
    
    ' 释放内存
    Set stock = Nothing
    Set stocks = Nothing
    
End Function


Function ParseSingleLine(stockData As String) As CStock
    Dim Data As Variant
    Data = Split(stockData, ",")
    
    'check data length
    Dim dataLength As Integer
    dataLength = UBound(Data) - LBound(Data)
    If dataLength < 3 Then
        '如果数据长度小于3，直接返回
        Set ParseSingleLine = Nothing
        GoTo Oops
    End If
    
    Dim stock As CStock
    Dim mkt As STOCK_MKT
    Dim stockCode As String
    
    ' get stockCode
    stockCode = Split(Split(Data(0), "=")(0), "_")(2)
    
    Set stock = New CStock
    
    'check whether it's hk code
    mkt = GetMarketType(stockCode)
    If mkt = MKT_HK Then
        stock.code = stockCode
        stock.name = Data(1)
        
        stock.openPrice = Data(2)
        stock.lastClosePrice = Data(3)
        stock.maxPrice = Data(4)
        stock.minPrice = Data(5)
        stock.currPrice = Data(6)

        stock.lastUpdateDate = Data(17)
        stock.lastUpdateTime = Data(18)
    ElseIf mkt = MKT_SH Or mkt = MKT_SZ Then
        stock.code = stockCode
        
        Dim nameArray As Variant
        nameArray = Split(Data(0), """")
        stock.name = nameArray(1)
        
        stock.openPrice = Data(1)
        stock.lastClosePrice = Data(2)
        stock.currPrice = Data(3)
        stock.maxPrice = Data(4)
        stock.minPrice = Data(5)
        stock.volume = Data(8)
        stock.turnover = Data(9)
        stock.lastUpdateDate = Data(30)
        stock.lastUpdateTime = Data(31)
    End If
    
    Set ParseSingleLine = stock
    
Oops:
    'MsgBox "[ParseSingleLine] Error when handling stockData: " & stockData
    Debug.Print "Error when parsing stockData " & stockData
End Function

Step-5 将CStock数据写入到excel指定单元格

更新数据到Excel的基本逻辑很简单，就是直接在股票代码的右侧按顺序写入数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
'Write stock data to the right columns of the code column
Function WriteSingleStockDataToTable(stock As CStock, codeRow As Integer, codeCol As Integer, Optional IncludeDetails As Boolean = False)

    Dim sngStart As Single
    sngStart = Timer
    
    ' update current price and increase rate
    Cells(codeRow, codeCol + 1) = stock.increaseRate
    Cells(codeRow, codeCol + 2) = stock.currPrice
    
    Debug.Print "WriteSingleStockDataToTable() - 基础耗时: " & Timer - sngStart
    
    'check if need update more details
    If IncludeDetails Then
        Cells(codeRow, codeCol + 3) = stock.lastClosePrice
        Cells(codeRow, codeCol + 4) = stock.openPrice
        Cells(codeRow, codeCol + 5) = stock.maxPrice
        Cells(codeRow, codeCol + 6) = stock.minPrice
        Cells(codeRow, codeCol + 7) = stock.volume
        Cells(codeRow, codeCol + 8) = stock.turnover
        
        Debug.Print "WriteSingleStockDataToTable() - 完整耗时: " & Timer - sngStart
    End If
    
End Function

'Write multiple stock data to the right columns of code
Function WriteStockListDataToTable(codeCol As Integer, codeDict As Object, stocks As Collection, Optional IncludeDetails As Boolean = False)
    Dim item As CStock
    Dim codeRow As Integer
    
    For Each item In stocks
        codeRow = codeDict.item(item.code)
        Call WriteSingleStockDataToTable(item, codeRow, codeCol, IncludeDetails)
    Next
End Function

Step-6 刷新整个列表

考虑到API支持的参数，使用了分批刷新的方式，每次获取的股票数量为batchSize. 这里发现一个问题，向excel单元格写入数据的耗时会随着写入数据的数量成比例增加，为了优化每次更新的时间，可以通过getDetails选择是否写入详细数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
'
Sub RefreshBtnClicked()
    '
    Debug.Print "Start Refresh()..."
    Dim sngStart As Single
    sngStart = Timer
    
    Dim codeColumn As Integer
    Dim startRow As Integer
    Dim endRow As Integer
    Dim batchSize As Integer
    Dim getDetails As Boolean
    
    ' update settings
    codeColumn = 4 '股票代码列
    startRow = 5 '起始行
    endRow = 100 '结束行
    batchSize = 5 '一次获取股票个数
    getDetails = True '是否更新详情，否则只更新当前涨幅（注意：更新的字段越多，耗时越长）
    
    Call RefreshStockListByBatch(codeColumn, startRow, endRow, batchSize, getDetails)
    
    MsgBox "Refresh done, " & Timer - sngStart & "s used."

End Sub

Sub RefreshStockListByBatch(codeColumn As Integer, startRow As Integer, endRow As Integer, batchSize As Integer, Optional IncludeDetails As Boolean = False)
    'refresh my list using one single request
    Dim row As Integer
    Dim longCode As String
    Dim mkt As STOCK_MKT
    Dim stockCount As Integer
    Dim stockListData As Collection
    Dim columnCode As String
    Dim code As String
    
    'crete dict to record stock position
    Dim codeDict As Object
    Set codeDict = CreateObject("Scripting.Dictionary")
    
    'initialize
    longCode = ""
    stockCount = 0
    Set stockListData = New Collection
    
    For row = startRow To endRow
        'convert to lower case
        code = LCase(Cells(row, codeColumn).Value)
        
        'construct the long code by ",", e.g. sh000001,sz300012,sh...
        mkt = GetMarketType(code)
        If mkt <> MKT_INVALID Then
            If Len(longCode) > 0 Then
                longCode = longCode & ","
            End If
            longCode = longCode & code
            
            'record stock position
            If Not codeDict.exists(code) Then
                codeDict.Add code, row
            End If
            
            '
            stockCount = stockCount + 1
        End If
        
        'check and request stock data by longCode
        If stockCount + 1 > batchSize Then
            '
            Set stockListData = GetStockDataByCode(longCode)
            Call WriteStockListDataToTable(codeColumn, codeDict, stockListData, IncludeDetails)
            
            ' reset
            Set stockListData = Nothing
            Set stockListData = New Collection
            stockCount = 0
            longCode = ""
            
            'release control to OS
            DoEvents
        End If
        
    Next row
    
    '
    Set stockListData = GetStockDataByCode(longCode)
    Call WriteStockListDataToTable(codeColumn, codeDict, stockListData, IncludeDetails)
    
    Set codeDict = Nothing
    Set stockListData = Nothing
End Sub

原始文件

原始文件可以从这里下载，代码比较粗糙，欢迎在此基础上加工改造！