求Python代码：在打开的Excel Sheet1中实时更新NSE股票数据

需求说明 #

原代码可循环爬取印度国家证券交易所Nifty50成分股的实时行情并在终端打印，现需修改为将数据实时导出到已打开的Excel文件的Sheet1工作表，每次循环自动更新数据，直到关闭Python程序或Excel文件。

修改后的完整代码 #

import pandas as pd
import sys
import requests
from bs4 import BeautifulSoup
import re
import os
import win32com.client as win32
import time

def fetch_NSE_stock_price(stock_code):
    stock_url = 'https://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=' + str(
        stock_code)
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36'}
    response = requests.get(stock_url, headers=headers)
    soup = BeautifulSoup(response.text, 'html.parser')
    data_array = soup.find(id='responseDiv').getText().strip().split(":")

    op = lp = dh = dl = cp = 0.0
    for item in data_array:
        if 'lastPrice' in item:
            index = data_array.index(item) + 1
            latestPrice = data_array[index].split('\"')[1]
            lp = float(latestPrice.replace(',', ''))
        elif 'closePrice' in item:
            index = data_array.index(item) + 1
            closePrice = data_array[index].split('\"')[1]
            cp = float(closePrice.replace(',', ''))
        elif 'open' in item:
            index = data_array.index(item) + 1
            openPrice = data_array[index].split('\"')[1]
            op = float(openPrice.replace(',', ''))
        elif 'dayLow' in item:
            index = data_array.index(item) + 1
            dayLow = data_array[index].split('\"')[1]
            dl = float(dayLow.replace(',', ''))
        elif 'dayHigh' in item:
            index = data_array.index(item) + 1
            dayHigh = data_array[index].split('\"')[1]
            dh = float(dayHigh.replace(',', ''))
    return op, lp, dh, dl, cp

# 获取Nifty50成分股列表
nifty50_url = 'https://www1.nseindia.com/content/indices/ind_nifty50list.csv'
df_n50 = pd.read_csv(nifty50_url)
regexp = re.compile('&')

# 连接已打开的Excel实例
try:
    excel = win32.GetActiveObject("Excel.Application")
    workbook = excel.ActiveWorkbook
    sheet = workbook.Sheets("Sheet1")
except Exception as e:
    print("未找到已打开的Excel文件，请先打开目标Excel再运行程序")
    sys.exit()

while True:
    try:
        # 每次循环清空数据列表，避免累积
        OP = []
        LP = []
        DHP = []
        DLP = []
        CP = []

        # 爬取所有成分股数据
        for index, row in df_n50.iterrows():
            stock_code = row['Symbol']
            if regexp.search(stock_code):
                stock_code = stock_code.replace('&', '%26')

            oPrice, lPrice, dhPrice, dlPrice, cPrice = fetch_NSE_stock_price(stock_code)
            OP.append(oPrice)
            LP.append(lPrice)
            DHP.append(dhPrice)
            DLP.append(dlPrice)
            CP.append(cPrice)

        # 更新终端显示
        os.system('cls')
        print("--------------------------------------------------------------------------------------------------------------------------------------------")
        print("|{:50s} | {:20s} | {:10s} | {:10s} | {:10s} | {:10s} | {:10s}|".format('Company Name', 'Symbol',
                                                                                  'openPrice', 'lastPrice',
                                                                                  'dayHigh', 'dayLow',
                                                                                  'closePrice'))
        print("--------------------------------------------------------------------------------------------------------------------------------------------")

        for index, row in df_n50.iterrows():
            print("|{:50s} | {:20s} | {:10.2f} | {:10.2f} | {:10.2f} | {:10.2f} | {:10.2f} |".format(str(row['Company Name']),
                                                                                                      row['Symbol'],
                                                                                                      OP[index],
                                                                                                      LP[index],
                                                                                                      DHP[index],
                                                                                                      DLP[index],
                                                                                                      CP[index]))
        print("--------------------------------------------------------------------------------------------------------------------------------------------")

        # 更新Excel数据
        # 写入表头
        sheet.Range("A1").Value = "Company Name"
        sheet.Range("B1").Value = "Symbol"
        sheet.Range("C1").Value = "openPrice"
        sheet.Range("D1").Value = "lastPrice"
        sheet.Range("E1").Value = "dayHigh"
        sheet.Range("F1").Value = "dayLow"
        sheet.Range("G1").Value = "closePrice"

        # 写入数据行
        for idx, row in df_n50.iterrows():
            row_num = idx + 2
            sheet.Range(f"A{row_num}").Value = str(row['Company Name'])
            sheet.Range(f"B{row_num}").Value = row['Symbol']
            sheet.Range(f"C{row_num}").Value = OP[idx]
            sheet.Range(f"D{row_num}").Value = LP[idx]
            sheet.Range(f"E{row_num}").Value = DHP[idx]
            sheet.Range(f"F{row_num}").Value = DLP[idx]
            sheet.Range(f"G{row_num}").Value = CP[idx]

        # 刷新Excel显示
        excel.ScreenUpdating = True
        # 设置更新间隔（可根据需求调整，单位：秒）
        time.sleep(10)

    except KeyboardInterrupt:
        print("\n程序已终止")
        sys.exit()
    except Exception as e:
        print(f"异常：{str(e)}，可能Excel已关闭，程序终止")
        sys.exit()

关键修改点 #

• 引入win32com.client库，用于连接系统中已打开的Excel实例，直接操作工作表
• 每次循环前清空数据列表，避免重复数据堆积
• 添加Excel连接异常处理，确保程序启动时已打开目标Excel文件
• 实现Excel表头和数据的实时写入，每次循环覆盖原有数据实现更新
• 增加time.sleep()控制更新频率（默认10秒，可自行调整）
• 捕获Excel关闭等异常，避免程序崩溃

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