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:

Verify file exists and is accessible
Check dependencies: Ensure pandas, openpyxl, and at least one data source library (yfinance or akshare) are installed
Run the script: Execute python3 scripts/update_stock_prices.py <file_path>
Review output: The script reports success/failure for each stock
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.

stock-price-updater