【量化投资】02.数据接口初探——excel读取新浪股票api数据

【目标】

           使用vba程序获取新浪股票api的实时行情数据、K线，以及macd等技术指标数据。

【新浪api介绍】

1.  获取实时行情数据接口，http://hq.sinajs.cn/list=sz002208,sh601318

2. 对于股票的K线图，日线图等的获取可以通过请求:
   http://image.sinajs.cn/…./…/.gif
   此URL获取，其中代表股票代码，详见如下：
   查看日K线图： http://image.sinajs.cn/newchart/daily/n/sh601318.gif
   分时线的查询： http://image.sinajs.cn/newchart/min/n/sh601318.gif
   日K线查询： http://image.sinajs.cn/newchart/daily/n/sh601318.gif
   周K线查询： http://image.sinajs.cn/newchart/weekly/n/sh601318.gif
   月K线查询： http://image.sinajs.cn/newchart/monthly/n/sh601318.gif

3. 对于股票的macd等技术指标图，可以通过请求:
   http://image.sinajs.cn/…./…/.gif
   此URL获取，其中代表股票代码，详见如下：
   macd： http://image.sinajs.cn/newchart/macd/sh601318.gif
   kdj： http://image.sinajs.cn/newchart/kdj/sh601318.gif
   rsi： http://image.sinajs.cn/newchart/rsi/sh601318.gif
   bias： http://image.sinajs.cn/newchart/bias/sh601318.gif
   等等，其他雷同

【示例代码1.查询实时行情】

  获取股票名称、现价、涨幅、涨跌额、昨收、今开、最高、最低、振幅、总手、总额等，编写vba程序，并使用按钮宏关联此vba。

Public indexType As String
Public caption_Name As String
Public stockCode As String
Public url As String
Public i, j As Integer
Public Const stockCodeColumn = 1                    '股票代码所在列
Public Const startRow = 3                           '开始行1表示是从第一行开始的数据，否则表示前面有注释类信息
Public Const arrSrartColumn = 3                     '排列开始的列
Public Const arrEndColumn = arrSrartColumn + 15     '排列结束的列
Public Const extSrartColumn = 17                    '扩展信息开始的列
Public Const url1 = "http://image.sinajs.cn/newchart/"
Public Const url2 = "http://image2.sinajs.cn/newchart/"Sub GetData_click()Dim originalData As String, arrAllData() As String, recordData() As String, recordHeader() As String
Dim stocks As StringEndRow = Range("A65536").End(xlUp).Row'关闭屏幕刷新
Application.ScreenUpdating = False'跳过错误
On Error Resume Next'合并股票代码
For i = startRow To EndRowIf i = startRow Thenstocks = LCase(Cells(startRow, 1))Elsestocks = stocks + "," + LCase(Cells(i, 1))End If
Next istocks = stocks + "," + "sh000001" + "," + "sz399001"'取得实时数据
originalData = GetHttp("http://hq.sinajs.cn/list=" + stocks)arrAllData = Split(originalData, ";")iCount = UBound(arrAllData)'排列所取得的数据
For i = startRow To iCount + startRow - 1 - 2KK = Cells(i, arrSrartColumn + 2).ValuerecordData = Split(arrAllData(i - startRow), ",")If UBound(recordData) <= 1 ThenCells(i, stockCodeColumn + 1) = "无此代码"ElserecordHeader = Split(recordData(0), Chr(34))Cells(i, stockCodeColumn + 1) = recordHeader(1)       '股票名称End IfCells(i, arrSrartColumn + 0) = recordData(3)     '现价Cells(i, arrSrartColumn + 1) = Round((recordData(3) - recordData(2)) / recordData(2) * 100, 2) & "%"    '涨跌幅Cells(i, arrSrartColumn + 2) = Round(recordData(3) - recordData(2), 3)    '涨跌Cells(i, arrSrartColumn + 3) = recordData(2)     '昨收Cells(i, arrSrartColumn + 4) = recordData(1)     '今开Cells(i, arrSrartColumn + 5) = recordData(4)     '最高Cells(i, arrSrartColumn + 6) = recordData(5)     '最低Cells(i, arrSrartColumn + 7) = Round((recordData(4) - recordData(5)) / recordData(2) * 100, 2) & "%"     '振幅Cells(i, arrSrartColumn + 8) = Round(recordData(8) / 100, 0)             '成交量由股变为手Cells(i, arrSrartColumn + 9) = Round(recordData(9) / 100000000, 2)       '成交额由元变亿元If recordData(3) = 0 ThenCells(i, arrSrartColumn + 1) = "0%"                                      '现价为0时涨跌幅Cells(i, arrSrartColumn + 2) = "0"                                       '现价为0时涨跌Cells(i, arrSrartColumn + 7) = "0%"                                      '现价为0时振幅End IfNext i'打开屏幕刷新
Application.ScreenUpdating = TrueEnd Sub'清除数据
Sub Clean_ArrArea()
On Error Resume Next
EndRow = Range("A65536").End(xlUp).RowIf startRow <= EndRow ThenRange(Cells(startRow, arrSrartColumn - 1), Cells(EndRow, arrEndColumn + 3)).Value = ""
ElseExit Sub
End IfEnd Sub

【运行效果1.】

【示例代码2.查询日K线】

 获取日K线的gif图片，其他5分钟、15分钟、macd、kdj等逻辑都是一样的，展示图片而已。

Sub A01_mink5()MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)url = url1caption_Name = "5分钟K线"indexType = "mink5"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub A02_mink15()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "15分钟K线"indexType = "mink15"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub A03_mink30()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "30分钟K线"indexType = "mink30"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub A04_daily()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "日K线"indexType = "daily"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub A05_weekly()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "周K线"indexType = "weekly"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub A06_monthly()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "月K线"indexType = "monthly"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub A07_min()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "分时线"indexType = "min"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B01_macd()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "MACD"indexType = "macd"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B02_trix()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "TRIX"indexType = "trix"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B03_dmi()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "DMI"indexType = "dmi"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B04_expma()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "EXPMA"indexType = "expma"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B05_brar()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "BRAR"indexType = "brar"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B06_cr()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "CR"indexType = "cr"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B07_vr()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "VR"indexType = "vr"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B08_bias()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "BIAS"indexType = "bias"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B09_psy()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "PSY"indexType = "psy"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B10_obv()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "OBV"indexType = "obv"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B11_boll()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "BOLL"indexType = "boll"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B12_mike()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "MIKE"indexType = "mike"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B13_asi()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "ASI"indexType = "asi"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B14_emv()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "EMV"indexType = "emv"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B15_cci()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "CCI"indexType = "cci"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B16_rsi()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "RSI"indexType = "rsi"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B17_wr()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "WR"indexType = "wr"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B18_kdj()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "KDJ"indexType = "kdj"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B19_roc()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "ROC"indexType = "roc"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd SubSub B20_dma()url = url1MyRow = ActiveCell.RowstockCode = LCase(Cells(MyRow, stockCodeColumn).Value)caption_Name = "DMA"indexType = "dma"If Len(stockCode) <> 8 ThenMsgBox "不是有效的股票代码。", 16, "错误信息"ElseGet_Photo.ShowEnd IfEnd Sub

【运行效果】

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！