Excel VBA调用Python获取Yahoo Finance现货价格失败求助

我编写了一段通过yfinance库获取日内现货价格的Python代码，同时配套了Excel VBA代码用于调用该脚本，传入ric、datetime、tick interval三个参数获取对应价格。目前遇到的问题：单独调用Python函数能得到正确价格，但终端执行Python脚本无返回结果也无报错，VBA调用同样无法获取任何数据。

原代码参考 #

Python代码 #

import yfinance as yf
from datetime import datetime, timedelta, timezone

def get_closest_stock_price(ric, target_datetime_str, tick_interval):
    # Parse the target datetime string
    target_datetime = datetime.strptime(target_datetime_str, "%Y-%m-%d %H:%M:%S")

    # Create a new instance of yfinance Ticker for the specified Reuters ticker
    ticker = yf.Ticker(ric)

    # Download historical data
    stock_data = ticker.history(start=target_datetime - timedelta(days=1), end=target_datetime, interval=tick_interval)

    # Check if data is available
    if stock_data.empty:
        return None 

    # Make target_datetime timezone-aware using the timezone of stock_data.index
    target_datetime = target_datetime.replace(tzinfo=stock_data.index.tz)

    # Find the closest timestamp
    closest_timestamp = min(stock_data.index, key=lambda x: abs(x - target_datetime))

    # Get the stock price at the closest timestamp
    closest_price = stock_data.loc[closest_timestamp, 'Close']

    return closest_price

VBA代码 #

Function GetClosestStockPrice(ric As String, target_datetime As Date, tick_interval As String) As Variant
    Dim pythonScriptPath As String
    Dim pythonExePath As String
    Dim script As String
    Dim result As String

    ' Set the path to your Python script
    pythonScriptPath = "C:\xxxxx\YahooEQSpotApp\yfinance_eqspot.py"

    ' Set the path to your Python executable
    pythonExePath = "C:\Program Files\Python311\python.exe"

    ' Build the Python script command
    script = pythonExePath & " " & pythonScriptPath & " " & ric & " " & Format(target_datetime, "yyyy-mm-dd hh:mm:ss") & " " & tick_interval

    ' Run the Python script and capture the result
    result = CreateObject("WScript.Shell").Exec(script).StdOut.ReadAll

    ' Check if the result is empty
    If result = "" Then
        GetClosestStockPrice = "No data available."
    Else
        GetClosestStockPrice = CDbl(result)
    End If
End Function

测试参数与命令 #

• 示例输入：ric = ^SPX，datetime = 17/01/2024 15:15:00，intervals = 15m
• 终端执行命令：python3 yfinance_eqspot.py "^SPX" "2024-01-17 14:13:00" "15m"

核心问题与解决方案 #

1. 根本问题：Python脚本未处理命令行参数与输出 #

原Python代码仅定义了函数，但没有读取终端传入的参数、调用函数并打印结果，导致执行后无任何输出，VBA自然无法获取数据。

2. 其他潜在问题 #

• 特殊字符参数（如^SPX）未加引号，终端会解析错误
• yfinance的end参数是排他性的，目标时间刚好在边界时会漏数据
• VBA中含空格的路径未加引号，会导致命令执行失败

修正后的代码 #

修正版Python代码 #

import yfinance as yf
from datetime import datetime, timedelta, timezone
import sys

def get_closest_stock_price(ric, target_datetime_str, tick_interval):
    # Parse the target datetime string
    target_datetime = datetime.strptime(target_datetime_str, "%Y-%m-%d %H:%M:%S")

    # Create a new instance of yfinance Ticker
    ticker = yf.Ticker(ric)

    # 调整时间范围：end加1分钟，避免目标时间刚好在边界导致数据遗漏
    stock_data = ticker.history(
        start=target_datetime - timedelta(days=1), 
        end=target_datetime + timedelta(minutes=1),
        interval=tick_interval
    )

    # Check if data is available
    if stock_data.empty:
        return None 

    # Make target_datetime timezone-aware
    target_datetime = target_datetime.replace(tzinfo=stock_data.index.tz)

    # Find the closest timestamp
    closest_timestamp = min(stock_data.index, key=lambda x: abs(x - target_datetime))

    # Get the stock price
    closest_price = stock_data.loc[closest_timestamp, 'Close']

    return closest_price

# 新增：处理命令行参数并输出结果
if __name__ == "__main__":
    if len(sys.argv) != 4:
        print("Error: 需传入3个参数：ric、target_datetime_str、tick_interval")
        sys.exit(1)
    
    ric = sys.argv[1]
    target_datetime_str = sys.argv[2]
    tick_interval = sys.argv[3]
    
    price = get_closest_stock_price(ric, target_datetime_str, tick_interval)
    print(price if price is not None else "")

修正版VBA代码 #

Function GetClosestStockPrice(ric As String, target_datetime As Date, tick_interval As String) As Variant
    Dim pythonScriptPath As String
    Dim pythonExePath As String
    Dim script As String
    Dim result As String
    Dim wsh As Object
    
    ' 配置路径
    pythonScriptPath = "C:\xxxxx\YahooEQSpotApp\yfinance_eqspot.py"
    pythonExePath = "C:\Program Files\Python311\python.exe"
    
    ' 为含空格的路径和特殊字符参数添加双引号，避免解析错误
    pythonExePath = """" & pythonExePath & """"
    pythonScriptPath = """" & pythonScriptPath & """"
    ric = """" & ric & """"
    target_datetime_str = """" & Format(target_datetime, "yyyy-mm-dd hh:mm:ss") & """"
    tick_interval = """" & tick_interval & """"
    
    ' 构建执行命令
    script = pythonExePath & " " & pythonScriptPath & " " & ric & " " & target_datetime_str & " " & tick_interval
    
    ' 执行脚本并捕获结果
    Set wsh = CreateObject("WScript.Shell")
    result = wsh.Exec(script).StdOut.ReadAll
    
    ' 【调试用】捕获错误输出，排查问题（调试完成后可注释）
    ' Dim errorMsg As String
    ' errorMsg = wsh.Exec(script).StdErr.ReadAll
    ' If errorMsg <> "" Then MsgBox "Python执行错误：" & errorMsg
    
    ' 处理返回结果
    If Trim(result) = "" Then
        GetClosestStockPrice = "No data available."
    Else
        GetClosestStockPrice = CDbl(result)
    End If
End Function

测试步骤 #

1. 先在终端执行修正后的Python脚本，确认能输出价格：

python "C:\xxxxx\YahooEQSpotApp\yfinance_eqspot.py" "^SPX" "2024-01-17 14:13:00" "15m"

2. 在Excel中输入公式测试：

=GetClosestStockPrice("^SPX", DATE(2024,1,17)+TIME(15,15,0), "15m")

内容的提问来源于stack exchange，提问作者spongey