如何从打开的Excel调用Python实现股票数据实时更新？

你的需求完全可行，通过xlwings直接操作打开的Excel工作簿，即可绕开文件锁定问题，实现不关闭Excel的情况下实时更新股票数据。

修改后的完整代码 #

import yfinance as yf
import xlwings as xw
import pandas as pd
from concurrent.futures import ThreadPoolExecutor  

def get_stats(ticker):
    try:
        info = yf.Tickers(ticker).tickers[ticker].info
        cols = ['sector', 'industry', 'currentPrice', 'marketCap', 'forwardPE', 'sharesOutstanding','totalRevenue', 'netIncomeToCommon','fullTimeEmployees','totalCash','totalDebt','bookValue','fiftyTwoWeekHigh','fiftyTwoWeekLow','grossMargins', 'operatingMargins', 'profitMargins', 'freeCashflow',
                'ebitda','grossProfits','priceToBook','fiftyDayAverage','twoHundredDayAverage','exchange','forwardEps','trailingEps','volume','averageVolume10days','heldPercentInstitutions','targetMeanPrice','returnOnEquity','shortName']
        # 处理API返回缺失字段，避免KeyError
        return {'ticker': ticker, **{c: info.get(c, None) for c in cols}}
    except Exception as e:
        print(f"获取{ticker}数据失败: {str(e)}")
        # 返回空结构保证DataFrame列一致性
        return {'ticker': ticker, **{c: None for c in cols}}

# 连接目标Excel工作簿（如果已打开，也可直接用xw.Book()）
wb = xw.Book(r'C:\Users\jkru0\OneDrive\Desktop\yahoo.xlsx')
ws = wb.sheets['Sheet1']

# 读取Ticker列（假设标题在A1，数据从A2开始）
tickers = ws.range('A2').expand('down').value
# 过滤空值，避免无效请求
tickers = [t for t in tickers if t is not None]

if not tickers:
    print("未找到有效股票代码")
    exit()

with ThreadPoolExecutor() as executor:
    result_iter = executor.map(get_stats, tickers)

df = pd.DataFrame(result_iter)

# 重命名列名
df.columns = ['Ticker', 'Sector', 'Industry', 'Price', 'MarketCap', 'ForwardPE', 'SharesOutstanding', 'Total Revenue', 'NetIncometoCommon', 'FullTimeEmployees', 'Total Cash', 'Total Debt', 'Book Value', '52Wk High', '52Wk Low', 'Gross Margins', 'Operating Margins', 'Profit Margins', 'FreeCashFlow',
              'Ebitda', 'Gross Profits', 'PricetoBook', '50DayAvg', '200DayAvg', 'Exchange', 'ForwardEPS', 'TrailingEPS','Volume','averageVolume10days','heldPercentInstitutions','targetMeanPrice','ROE', 'Comp Name']

# 清空原有数据区域
if ws.used_range:
    ws.used_range.clear_contents()

# 从A1开始写入新数据
ws.range('A1').value = df

print("数据更新完成")

关键优化点 #

• 直接操作打开的Excel：通过xlwings.Book()连接内存中的Excel实例，无需读写本地文件，彻底解决文件锁定问题
• 异常容错：添加异常捕获处理单个股票数据获取失败的情况，避免代码中途终止
• 空值过滤：自动过滤Ticker列的空值，避免无效API请求
• 数据覆盖逻辑：先清空原有数据再写入新结果，确保表格内容完全同步

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