[Python] Python scraper not returning value to Excel

I have a Python webscraper that pulls a specific value every 1 second. The target website it AJAXed, so I'm not hitting it with too many requests.

This is the Python code:

import time
import logging
import sys
from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC

# Suppress all unnecessary logs
logging.basicConfig(level=logging.CRITICAL) # Suppress logs lower than CRITICAL
logging.getLogger("selenium").setLevel(logging.CRITICAL) # Suppress Selenium logs

# Specify the path to your chromedriver
chromedriver_path = 'C:/GoldScraper/chromedriver.exe'

# Set up Chrome options
options = Options()
options.add_argument("--headless") # Run in headless mode
options.add_argument("--no-sandbox")
options.add_argument("--disable-dev-shm-usage")
options.add_argument("--disable-gpu")
options.add_argument("start-maximized")
options.add_argument("--log-level=3") # Suppress logs

# Suppress WebGL and other errors
options.add_argument("--disable-software-rasterizer")
options.add_argument("--disable-extensions")
options.add_argument("--disable-logging")

# Create a Service object
service = Service(chromedriver_path)
driver = webdriver.Chrome(service=service, options=options)

# Navigate to the website
driver.get('https://www.cnbc.com/quotes/XAU=')

# Print waiting message once
print("Waiting for price element...")

try:
# Wait for the gold price element to be visible
gold_price_element = WebDriverWait(driver, 10).until(
EC.visibility_of_element_located((By.CLASS_NAME, "QuoteStrip-lastPrice"))
)

print("Price element found. Fetching prices...")

while True:
# Extract and strip commas, convert to float, and format to 2 decimal places
gold_price = round(float(gold_price_element.text.replace(",", "")), 2) # Remove commas, convert to float, and round to 2 decimal places
print(f"{gold_price:.2f}") # Print with 2 decimal places
sys.stdout.flush() # Ensure output is immediately available to VBA

# Wait for 1 second before next fetch
time.sleep(1)


except KeyboardInterrupt:
print("\nScript stopped by user.")

except Exception as e:
print(f"Error extracting gold price: {e}")
sys.stdout.flush() # Ensure error messages are flushed too

finally:
# Ensure the browser closes
driver.quit()
print("Driver closed.")


I need to have the VBA macro capture the gold_price variable each second and display the value in a named cell called "Live_AU". This is the VBA code I have come up with:

Sub StartScraping()
Dim objShell As Object
Dim pythonScriptPath As String
Dim pythonExePath As String
Dim price As String

' Define the path to your Python executable and script
pythonExePath = "C:\python313\python.exe" ' Adjust this if needed
pythonScriptPath = "C:\GoldScraper\goldscraper.py" ' Adjust this if needed

' Run the Python script and capture the output
Set objShell = CreateObject("WScript.Shell")

' Capture the output from the Python script (output redirected to text)
Dim output As String
output = objShell.Exec(pythonExePath & " " & pythonScriptPath).StdOut.ReadAll ' Use ReadAll to capture everything

' Debug: Show the price output from Python
MsgBox "Python Output: " & output ' Display the output for debugging

' Check if price is valid and update the Live_AU cell
If IsNumeric(output) Then
Range("Live_AU").Value = output
Else
MsgBox "No valid output from Python. Output: " & output ' Show the captured output if invalid
End If
End Sub


This returns nothing or an empty string, I can't tell. Can anyone help debug this for me so that the correct value is displayed in the required Excel cell?

Continue reading...