2022-01-21 16:12 根据新浪接口更新了代码,再修复一次
打开VBA编辑器WinHttp.XMLHTTP 替换 成 WinHttp.WinHttpRequest.5.1
在 .send 前面加 .setRequestHeader "Referer", "http://finance.sina.com.cn/"
例如我这个用的这个函数原先是:
With CreateObject("WinHttp.XMLHTTP")
.Open "GET", url, False
.Send
sTemp = .responseText
End With
改成下面的就正常了
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", url, False
.setRequestHeader "Referer", "http://finance.sina.com.cn/"
.Send
sTemp = .responseText
End With
==============
获取新浪行情的完整函数
Sub GetNetValueDetail(ByVal sheet As Worksheet, beginCol As String) '基金查询
Dim rowCount As Integer
Dim url As String
Dim sTemp As String
rowCount = sheet.Range("A65535").End(xlUp).Row '获取行数
url = "http://hq.sinajs.cn/list=" '新浪行情数据接口
For i = 2 To rowCount '从第二行开始,第一列为股票代码
code = sheet.Range("A" & i).Text
If Len(code) < 6 Then
code = "unknow"
Else
code = "of" & Right(code, 6) '基金代码前of(open fund)
End If
If i = 2 Then
url = url & code
Else
url = url & "," & code
End If
Next i
'获取新浪股票行情数据,放入sTemp变量
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", url, False
.setRequestHeader "Referer", "http://finance.sina.com.cn/"
.Send
sTemp = .responseText
End With
splits = Split(sTemp, ";")
For i = 0 To rowCount - 1
mystr = splits(i)
ss = InStr(mystr, ",")
If ss > 1 Then
startindex = InStr(1, mystr, """")
endindex = InStrRev(mystr, """")
substr = Mid(mystr, startindex + 1, endindex - startindex - 1) '引号中的有效数据
valuearray = Split(substr, ",")
begin = Asc(beginCol)
J = 0
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(0) '名称
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(1) '净值
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(2) '累计净值
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(3) '上日净值
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = Format(valuearray(4) / 100, "0.00%") '净值涨跌幅
sheet.Range(Chr(begin + J) & i + 2).Font.Color = GetFontColor(valuearray(1) - valuearray(3))
J = J + 1
sheet.Range(Chr(begin + J) & i + 2).Value = valuearray(5) '日期
End If
Next i
End Sub
0
@ccnuwater
sheet.Range(Chr(begin + j) & i + 2).Value = Format(valueArray(1) / valueArray(3) - 1, "0.00%") '净值涨跌幅
sheet.Range(Chr(begin + j) & i + 2).Font.Color = GetFontColor(valueArray(1) - valueArray(3))
我这样修改后,显示错误:运行时错误13,类型不匹配。j = j + 1
点击提示时,这段提示有问题: sheet.Range(Chr(begin + J) & i + 2).Value = Format(valuearray(4) / 100, "0.00%") '净值涨跌幅
不知道怎么解决?
sheet.Range(Chr(begin + j) & i + 2).Value = Format(valueArray(1) / valueArray(3) - 1, "0.00%") '净值涨跌幅
sheet.Range(Chr(begin + j) & i + 2).Font.Color = GetFontColor(valueArray(1) - valueArray(3))