json - 使用 Excel VBA 从 CSV 文件中提取数据

Question

我是 Yahoo Finance API 难民(他们停止了 API 服务)，试图切换到 Alpha Vantage。
我修改了之前用于 Yahoo Finance 的以下代码，但在 Excel 中出现#VALUE 错误。

下面的 URL 可以自行工作(如果您在 Web 浏览器中键入它，它会打开一个 CSV)，所以我想我真正的问题在于将正确的数据从 CSV 提取到我的 Excel 电子表格中。有人可以帮忙吗？

我正在尝试从 CSV 中提取第 2 行第 5 列(最后收盘价)中的数据。提前谢谢了!

Function StockClose(Ticker As String)

Dim URL As String, CSV As String, apikey As String, SCRows() As String, SCColumns() As String, pxClose As Double

apikey = "*censored*"

URL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & Ticker & "&outputsize=full&" & apikey & "&datatype=csv"

Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", URL, False
    xmlhttp.Send
    CSV = xmlhttp.responseText

    'split the CSV into rows
    SCRows() = Split(CSV, Chr(10))
    'split the relevant row into columns. 0 means 1st row, starting at index 0
    SCColumns() = Split(SCRows(1), ",")
    '6 means: 5th column; starting at index 0 - price close is in the 5th column
    pxClose = SCColumns(6)

    StockClose = pxClose

Set http = Nothing

End Function

如果我提取 json 而不是 csv，则返回的数据示例:

{ "Meta Data": { "1. Information": "Daily Prices (open, high, low, close) and Volumes", "2. Symbol": "SGD=X", "3. Last Refreshed": "2017-11-10", "4. Output Size": "Full size", "5. Time Zone": "US/Eastern" }, "Time Series (Daily)": { "2017-11-13": { "1. open": "1.3588", "2. high": "1.3612", "3. low": "1.3581", "4. close": "1.3587", "5. volume": "0" }, "2017-11-10": { "1. open": "1.3588", "2. high": "1.3612", "3. low": "1.3581", "4. close": "1.3587", "5. volume": "0" },

Answer 1

网站上的“CSV”选项是一个可下载的文件，而不是像这样解析的文本文档。一个更简单的解决方案是使用站点的 JSON 选项，它很容易加载到字符串中。

由于您只需要一个值，因此最简单的方法是搜索字符串“Close”，然后返回它后面的值。

这是一个快速解决方案，您可以根据需要进行调整:

Option Explicit

Function StockClose(Ticker As String) As Double

    Dim URL As String, json As String, apiKey As String, xmlHTTP As Object
    Dim posStart As Integer, posEnd As Integer, strClose As String

    apiKey = "demo"
    URL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & Ticker & "&outputsize=full&apikey=" & apikey 

    Set xmlHTTP = CreateObject("MSXML2.XMLHTTP")
    xmlHTTP.Open "GET", URL, False
    xmlHTTP.Send
    json = xmlHTTP.responseText
    Set xmlHTTP = Nothing

    posStart = InStr(json, "4. close") + 12
    posEnd = InStr(posStart, json, """") - 1
    strClose = Mid(json, posStart, posEnd - posStart)

    StockClose = Val(strClose)

End Function