Stock Price Updater

Overview

Update Excel files with latest stock market data from multiple sources. The script automatically detects stock markets, fetches real-time prices, and writes comprehensive trading data including open/high/low/close prices, volume, and price changes.

Quick Start

Basic usage:

python3 scripts/update_stock_prices.py <excel_file>

Save to a different file:

python3 scripts/update_stock_prices.py input.xlsx -o output.xlsx

Installation

Install required dependencies:

pip install pandas openpyxl yfinance akshare

Minimum requirements:

• pandas and openpyxl (required for Excel handling)
• At least one of: yfinance or akshare (for fetching stock data)

Excel File Requirements

The Excel file must contain a stock code column with one of these names (case-insensitive):

• 股票代码, 代码 (Chinese)
• code, symbol, ticker (English)

Optional stock name column:

• 股票名称, 名称 (Chinese)
• name, stock_name (English)

The script will add or update these columns:

• 开盘价 (Open), 最高价 (High), 最低价 (Low), 收盘价 (Close)
• 成交量 (Volume)
• 涨跌额 (Change), 涨跌幅(%) (Change %)
• 更新日期 (Update Date)

For detailed format specifications and examples, see references/excel-format.md.

Supported Stock Markets

The script automatically detects the market based on code format:

Chinese A-Shares:

• Shanghai: 600000, 601000, 688000 (or with sh prefix)
• Shenzhen: 000001, 300001 (or with sz prefix)
• Uses AkShare (preferred) or Yahoo Finance

Hong Kong Stocks:

• Format: 0700.HK, 9988.HK or just 0700, 9988
• Uses Yahoo Finance

US Stocks:

• Format: AAPL, TSLA, GOOGL
• Uses Yahoo Finance

Other Markets:

• Supported through Yahoo Finance with appropriate suffixes

For detailed information about data sources, see references/data-sources.md.

Workflow

When a user requests to update stock prices in an Excel file:

1. Verify file exists and is accessible
2. Check dependencies: Ensure pandas, openpyxl, and at least one data source library (yfinance or akshare) are installed
3. Run the script: Execute python3 scripts/update_stock_prices.py <file_path>
4. Review output: The script reports success/failure for each stock
5. Verify results: Check the updated Excel file for new data columns

Script Features

• Automatic market detection: Identifies Chinese, Hong Kong, US, and other markets by code format
• Multiple data sources: Tries AkShare for Chinese stocks, falls back to Yahoo Finance
• Flexible column matching: Finds stock code columns by multiple possible names
• Data caching: Avoids redundant API calls for duplicate stocks
• Comprehensive output: Adds 8 data columns with complete trading information
• Error handling: Reports which stocks succeeded or failed to update

Troubleshooting

"Cannot find stock code column" error:

• Ensure Excel has a column named: 股票代码, 代码, code, symbol, or ticker
• Column names are case-insensitive

Missing data for specific stocks:

• Stock may be suspended, delisted, or code format is incorrect
• Check the code format matches the market (e.g., US stocks need ticker symbols like AAPL, not numbers)

Import errors:

• Install missing libraries: pip install yfinance akshare pandas openpyxl
• At minimum, need pandas, openpyxl, and one data source library

Slow performance:

• Fetching many stocks takes time (each API call takes 1-3 seconds)
• Script includes caching to avoid duplicate requests
• Consider running during off-peak hours for large files

Network errors:

• AkShare requires stable connection to Chinese data sources
• Yahoo Finance may rate-limit excessive requests
• Try again after a few minutes if rate-limited

For detailed troubleshooting, see references/data-sources.md.