AgentSkillsCN

multi-market-portfolio-allocator

当用户需要在多个市场（美国、香港、A股）构建投资组合，设定具体的资产配置比例、总资金约束以及各市场的交易规则时，该技能会从Excel文件中读取股票清单，从金融API中获取当前或最新股价与汇率，按市场类型对股票进行分类，依据配置比例计算各仓位规模，落实各市场特有的约束条件（美国/香港股票以整手为单位，A股以100股为最小交易单位），完成货币兑换，并生成包含详细计算结果的完整建仓方案。同时，该技能还会更新Excel文件，将计算得出的股票代码与仓位规模写入其中。触发条件包括“投资组合配置”“仓位构建”“按资金配置比例规划股票买入”以及“基于Excel的股票清单处理”等需求。

SKILL.md
--- frontmatter
name: multi-market-portfolio-allocator
description: When the user needs to build an investment portfolio across multiple markets (US, Hong Kong, A-shares) with specific allocation ratios, total capital constraints, and market-specific trading rules. This skill reads stock lists from Excel files, fetches current/latest stock prices and exchange rates from financial APIs, categorizes stocks by market type, calculates position sizes based on allocation percentages, applies market-specific constraints (whole shares for US/HK stocks, 100-share lots for A-shares), performs currency conversions, and generates a complete position-building plan with detailed calculations. It also updates Excel files with calculated stock codes and position sizes. Triggers include requests for portfolio allocation, position building, stock purchase planning with capital allocation ratios, and Excel-based stock list processing.

Instructions

1. Understand the User Request

The user will provide:

  • •A total capital amount (e.g., $1,000,000 USD).
  • •An allocation ratio for US stocks, Hong Kong stocks, and A-shares (e.g., 4:3:3).
  • •An Excel file (stock.xlsx by default) containing a list of stock names. The file has columns: Stock_name, Stock_code (initially empty), and Initial_position_size (initially empty).
  • •Specific instructions on which stocks belong to which market (e.g., "Alibaba as a Hong Kong stock, BYD, Ping An Insurance and Wuxi AppTec as A-shares").
  • •Trading rules: Purchases in whole shares for US/HK stocks, and in multiples of 100 shares (lots) for A-shares.
  • •The request to calculate based on "today's" opening prices, or the latest trading day's data if today is not a trading day.

Your goal is to produce a detailed position-building plan and fill the user's Excel file.

2. Core Workflow

Phase 1: Data Extraction & Categorization

  1. •Read the Excel File: Use the excel-read_data_from_excel tool to load the stock list. Identify the stock names.
  2. •Categorize Stocks: Based on the user's instructions and common knowledge, map each stock name to its market (US, Hong Kong, A-share) and determine its standard ticker symbol.
    • •US Stocks: Typically trade on US exchanges (NASDAQ, NYSE). Use standard tickers (e.g., MSFT, AAPL).
    • •Hong Kong Stocks: Use the .HK suffix (e.g., 9988.HK for Alibaba).
    • •A-Shares: Use the .SS (Shanghai) or .SZ (Shenzhen) suffix (e.g., 601318.SS for Ping An Insurance).
    • •If a stock could belong to multiple markets (like Alibaba), strictly follow the user's explicit instruction.

Phase 2: Data Fetching

  1. •Fetch Stock Prices: For each categorized stock, use the yahoo-finance-get_historical_stock_prices tool to get the last 5 days of daily data (period="5d", interval="1d"). Extract the Open price from the most recent data point (index 0). This represents "today's" or the latest trading day's opening price.
  2. •Fetch Exchange Rates: Use the same tool to get exchange rates for USDCNY=X (USD to CNY) and USDHKD=X (USD to HKD). Extract the latest Open rate.

Phase 3: Calculation & Allocation

  1. •Calculate Market Allocations: Apply the user's ratio (e.g., 4:3:3) to the total capital to determine the USD amount allocated to each market.
  2. •Convert to Local Currency: Convert the HK allocation to HKD and the A-share allocation to CNY using the fetched exchange rates.
  3. •Calculate Per-Stock Allocation: Divide each market's local currency allocation equally among the stocks in that market.
  4. •Calculate Share Quantities:
    • •US/HK Stocks: floor(per_stock_allocation_local / stock_price). Result must be a whole integer.
    • •A-Shares: floor(per_stock_allocation_local / (stock_price * 100)) * 100. Result must be a multiple of 100.
  5. •Calculate Costs & Summary: Compute the cost in local currency and USD for each position. Sum up totals for each market and overall. Calculate remaining cash.

Phase 4: Output & File Update

  1. •Generate Position Plan: Present a clear summary including:
    • •Allocation breakdown and exchange rates used.
    • •A table for each market showing stock, ticker, shares, price, and cost.
    • •A final investment summary (totals per market, total invested, remaining cash).
  2. •Update Excel File: Use the excel-write_data_to_excel tool to write the calculated ticker symbols and share quantities (numbers only) back into the Stock_code and Initial_position_size columns of the original Excel file, starting at cell B2.
  3. •Verify Update: Optionally re-read the file to confirm the data was written correctly.

3. Key Rules & Constraints

  • •Accuracy: Double-check ticker symbol mappings and currency conversions.
  • •Precision: Share calculations must strictly adhere to floor for US/HK and floor to the nearest 100 for A-shares. Do not round up.
  • •Clarity: The final plan should be easy for the user to review and execute.
  • •Stop: Once the plan is delivered and the Excel file is updated, claim the task as done. Do not perform any additional, unrequested actions.

4. Handling Ambiguity

  • •If a stock name is ambiguous and the user didn't specify, use your best judgment based on the most common/liquid listing and state your assumption.
  • •If financial data is unavailable for a ticker, note it in the plan and exclude it from calculations, adjusting the per-stock allocation for the remaining stocks in that market accordingly.
  • •If the user's Excel file has a different structure, adapt the read/write cell ranges as needed.

For detailed ticker mappings, calculation logic, and error handling examples, refer to the bundled scripts and references.