Calling Python script which grabs spot price via Yahoo Finance via Excel VBA

Question

I have Python code which can call intraday spot price using yfinance. Separately, I also have Excel VBA code which calls the Python script to get prices based on input parameters: ticker, datetime and tick interval.

Excel VBA code doesn't seem to grab anything.

I know the Python code works because if I call the function in the Python code itself, it grabs the correct price.

Could someone highlight where I'm going wrong here?

Python code:

    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

Excel VBA code:

    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

These are the input parameters, for example:

ric = ^SPX, datetime = 17/01/2024 15:15:00, intervals = 15m

I've also tried calling the Python script inside the terminal:

`python3 yfinance_eqspot.py "^SPX" "2024-01-17 14:13:00" "15m"`

But nothing is returned and there is no error message.