excel - 创建Excel函数以获取雅虎财经股票报价

Question

我遇到了以下 VBA 代码，它非常适合我在 Excel 中创建一个函数，该函数可以在某个单元格中获取 Yahoo Finance 股票报价。

代码的唯一缺点是它让我得到了最后一个报价，而我也希望对股票价值有类似的功能，例如 7 天前、1 个月和 6 个月前。（即 =today()-7 天或 =today()-30 天。

有没有办法修改它，以便我可以创建所需的新功能，例如 StockPrice1week 、 StockPrice6M 等。

我不需要 1 周或 6 个月的范围，而只需要该日期的单个收盘价。我不是在寻找任何其他宏或替代方案，而只是对这个 VBA vode 的修改。

Function StockQuote(ticker As String)
    ' Get near real-time stock quote from Yahoo via JSON query

    Dim URL As String, response As String, stripped As String, inbits() As String, i As Long
    Dim request As WinHttp.WinHttpRequest                               ' needs Tools|References|WinHTTP Services
    On Error GoTo Err

    URL = "https://query2.finance.yahoo.com/v7/finance/quote?symbols=" & Trim(ticker)
    Set request = New WinHttp.WinHttpRequest
    With request
        .Open "GET", URL, True
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .Send
        .WaitForResponse
        response = .responseText
    End With

    If InStr(response, """result"":[]") <> 0 Then GoTo Err              ' ticker not found

    'kludge parse: strip JSON delimiters and quotes
    stripped = Replace(Replace(Replace(Replace(Replace(response, "[", ""), "]", ""), "{", ""), "}", ""), """", "")

    stripped = Replace(stripped, ":", ":,")                             ' keep colons for readability, but make them delimit
    inbits = split(stripped, ",")                                       ' split

    i = LBound(inbits)
    Do While inbits(i) <> "regularMarketPrice:" And i <= UBound(inbits) ' find "regularMarketPrice:" tag
        i = i + 1
    Loop

    If i > UBound(inbits) Or Not IsNumeric(inbits(i + 1)) Then          ' not found; look for previous close
        i = LBound(inbits)
        Do While inbits(i) <> "regularMarketPreviousClose:" _
                                            And i <= UBound(inbits)
            i = i + 1
        Loop
        If i > UBound(inbits) Or Not IsNumeric(inbits(i + 1)) Then GoTo Err
    End If

    StockQuote = Val(inbits(i + 1))                                     ' price is next element
    Exit Function

Err:
    StockQuote = CVErr(xlErrNA)

End Function

我相信 ti 会特别与这个片段有关，但我不是 100% 确定？！

Do While inbits(i) <> "regularMarketPrice:" And i <= UBound(inbits) ' find "regularMarketPrice:" tag
    i = i + 1
Loop

If i > UBound(inbits) Or Not IsNumeric(inbits(i + 1)) Then          ' not found; look for previous close
    i = LBound(inbits)
    Do While inbits(i) <> "regularMarketPreviousClose:" _
                                        And i <= UBound(inbits)
        i = i + 1
    Loop
    If i > UBound(inbits) Or Not IsNumeric(inbits(i + 1)) Then GoTo Err
End If

谢谢你的帮助！

标签： excelvbaexcel-formulayahoo-finance