開發者文件

每個 MCP 工具背後打哪個 endpoint、跑什麼 SQL、碰哪些表,全部攤開。寫自由 SQL 前,表結構與關聯也在這裡。

49 個 MCP 工具7 個資料模塊唯讀 SQL 可查 27 張表

MCP

接上 MCP server 後,agent 透過工具查庫;每個工具等價於一個 REST endpoint 加一段 SQL。

REST API

同一批 endpoint 也能直接打:base URL 加 Authorization: Bearer 你的 API key。

唯讀 SQL(Pro)

execute_readonly_sql 直接下 SELECT;先用 describe_table 看表,關聯見下方各表卡片。

本頁模塊

公司與 SEC 申報三大財報與估值內部人與機構 13F 價格與期權公司行動、行事曆、逐字稿與新聞總經、ETF 與市場快照整合視圖與自由 SQL

公司與 SEC 申報

資料表

companies

被追蹤的美股與 ADR 公司登錄表,每個 ticker 一列,含 CIK、產業分類與下市狀態。

欄位	型別	說明
ticker	varchar(10) PK	主鍵,美股代號(大寫)。
cik	varchar(10)	SEC 中央索引碼(10 碼零填)。
cusip	varchar(9)	9 碼 CUSIP,常缺為 null。
name	text	公司名稱。
sector	text	產業別。
sic_code	varchar(4)	SEC SIC 產業代碼(4 碼)。
industry	text	細分產業。
exchange	text	掛牌交易所。
status	varchar(10)	'active' 或 'delisted',下市判讀以此為準(價格停更不等於下市)。
delisted_at	date	下市生效日,在市時為 null。

關聯

filings.ticker → companies.ticker (FK, SET NULL)

filings

SEC EDGAR 申報索引(10-K / 10-Q / 20-F / 8-K / Form 4 / 13F 等),每份申報一列,含表格類型與申報時間。

欄位	型別	說明
accession	varchar(20) PK	主鍵,SEC accession number(例 0000320193-24-000123)。
cik	varchar(10)	申報人 SEC CIK。
ticker	varchar(10) FK	外鍵到 companies.ticker(公司刪除時設 null)。
form_type	varchar(10)	表格類型,如 10-K / 10-Q / 8-K / 4 / 13F-HR。
filed_at	timestamptz	申報時間戳(帶時區),列表排序欄。
period_of_report	date	報告期末日,常缺為 null。
primary_doc_url	text	主文件 SEC 連結。
raw_storage_key	text	R2 原文存放鍵。
status	varchar(12)	處理狀態(discovered / parsed 等)。
parsed_at	timestamptz	解析完成時間,未解析為 null。

關聯

filings.ticker → companies.ticker (FK, SET NULL)
filing_sections.accession → filings.accession (FK, CASCADE)
income_statements.source_accession → filings.accession (FK, SET NULL)

filing_sections

已解析的申報章節內文,每筆對應一份申報的某個 item,含 item_code、標題與全文。

欄位	型別	說明
id	bigint PK	主鍵,自增。
accession	varchar(20) FK	外鍵到 filings.accession(申報刪除時連帶刪除)。
item_code	text	章節編號,如 'Item 1A'(風險因素)、'Item 7'(MD&A)。
title	text	章節標題。
content	text	章節完整內文,僅 get_filing_section 回傳(目錄查詢省略)。
char_start	integer	章節在原文的起始字元位置,目錄排序欄。
char_end	integer	章節在原文的結束字元位置。

關聯

filing_sections.accession → filings.accession (FK, CASCADE)

Functions

start_here()

任何新接入 agent 的入口地圖,回傳產品範圍、限制、選工具指引與工作流程。不打後端,永遠免費且安全。

REST endpoint:直連 DB(不經 REST)

背後 SQL

-- static orientation payload, no SQL

回傳範例

{
  "product": "investor-db",
  "scope": "US equities + ADRs only. EOD or slower, never real-time. ~3-5 years of history. No analyst ratings or revenue forecasts ...",
  "first_call": "Call get_data_coverage before quoting any exact figure ...",
  "how_to_pick_a_tool": [
    "Don't know the exact ticker -> search_companies",
    "Want a fast four-lens traffic-light conclusion -> get_analysis"
  ],
  "workflows": [
    {
      "name": "quick_answer_in_chat",
      "best_for": "a fast conclusion inside the conversation",
      "steps": ["get_company (or search_companies)", "get_analysis", "get_objective_report"]
    }
  ],
  "prompts": ["analyze_stock", "analyze_stock_full", "compare_stocks", "build_stock_report", "company_profile"],
  "resources": {
    "data://dictionary": "Field-level semantics, units, codes, and lag rules.",
    "data://analysis-playbook": "Step-by-step analysis methodology.",
    "data://report-template": "The HTML report design system used by build_stock_report."
  },
  "honesty_rules": [
    "Every figure needs an as-of date (from get_data_coverage or the row itself).",
    "null means missing, never zero.",
    "Do not invent price targets or forecasts."
  ]
}

get_data_coverage()

回報每個資料 domain 的新鮮度與覆蓋範圍,是引用任何確切數字前的信任基石。跨各表聚合最新資料點、最近成功 ingest 與估計筆數,結果做 10 分鐘 in-process 快取。

REST endpoint:GET /api/meta/coverage

觸及資料表:ingest_runscompaniesfilings

背後 SQL

-- last successful ingest per ETL stage
SELECT stage, max(finished_at) FROM ingest_runs WHERE status = 'ok' GROUP BY stage;
-- newest data point per domain (examples)
SELECT max(filed_at) FROM filings;
SELECT max(date) FROM prices_daily;
-- cheap row-count estimate (avoids count(*) on ~33M-row tables)
SELECT relname, reltuples::bigint FROM pg_class WHERE relkind = 'r';

回傳範例

{
  "as_of": "2026-05-01T12:00:00+00:00",
  "notes": "All data is end-of-day or slower; nothing is real-time. Coverage: US equities + ADRs.",
  "domains": [
    {
      "domain": "prices_daily",
      "description": "Daily OHLCV (Alpha Vantage TIME_SERIES_DAILY_ADJUSTED)",
      "last_data_point": "2026-04-30",
      "last_ingest_ok": "2026-05-01T10:22:35+00:00",
      "coverage": { "tickers": 18542, "rows_estimate": 15230000 },
      "date_range": { "from": "2021-05-03", "to": "2026-04-30" },
      "cadence": "Mon-Sat ~10:00 UTC scan; EOD T+1; 5-year rolling retention",
      "known_gaps": "~5% of symbols (funds/warrants/special classes) not quoted by the vendor"
    }
  ]
}

list_companies(limit?, offset?)

列出被追蹤的美股公司,依 ticker 字母排序,支援 limit / offset 分頁瀏覽(MCP 端預設回 200 筆)。

REST endpoint:GET /api/companies

觸及資料表:companies

背後 SQL

SELECT ticker, cik, name, sector, industry, exchange, status, delisted_at, first_seen, last_updated
FROM companies
ORDER BY ticker
LIMIT :limit OFFSET :offset

回傳範例

[
  {
    "ticker": "AAPL",
    "cik": "0000320193",
    "cusip": "037833100",
    "name": "Apple Inc.",
    "sector": "Technology",
    "sic_code": "3571",
    "industry": "Electronic Computers",
    "exchange": "NASDAQ",
    "reporting_currency": "USD",
    "country": null,
    "is_active": true,
    "status": "active",
    "delisted_at": null,
    "first_seen": "2021-01-04",
    "last_updated": "2026-05-01T06:12:44+00:00"
  }
]

search_companies(q, limit?)

以 ticker 或公司名稱關鍵字模糊搜尋,排序為完全命中優先、再 ticker 前綴、再 name 前綴、最後子字串,同級短 ticker 優先,typeahead 用。

REST endpoint:GET /api/companies/search

觸及資料表:companies

背後 SQL

SELECT ticker, name, exchange
FROM companies
WHERE ticker ILIKE :prefix OR ticker ILIKE :contains OR name ILIKE :contains
ORDER BY
  CASE WHEN ticker = :exact THEN 0 WHEN ticker ILIKE :prefix THEN 1 WHEN name ILIKE :prefix THEN 2 ELSE 3 END,
  length(ticker), ticker
LIMIT :limit

回傳範例

[
  { "ticker": "AAPL", "name": "Apple Inc.", "exchange": "NASDAQ" },
  { "ticker": "AAPD", "name": "Direxion Daily AAPL Bear 1X Shares", "exchange": "NASDAQ" }
]

get_company(ticker)

以精確 ticker 取得單一公司的完整基本資料,查無此代號則回 404。

REST endpoint:GET /api/companies/{ticker}

觸及資料表:companies

背後 SQL

SELECT ticker, cik, cusip, name, sector, sic_code, industry, exchange,
       reporting_currency, status, delisted_at, first_seen, last_updated
FROM companies
WHERE ticker = :ticker

回傳範例

{
  "ticker": "AAPL",
  "cik": "0000320193",
  "cusip": "037833100",
  "name": "Apple Inc.",
  "sector": "Technology",
  "sic_code": "3571",
  "industry": "Electronic Computers",
  "exchange": "NASDAQ",
  "reporting_currency": "USD",
  "country": null,
  "is_active": true,
  "status": "active",
  "delisted_at": null,
  "first_seen": "2021-01-04",
  "last_updated": "2026-05-01T06:12:44+00:00"
}

list_filings(ticker, form_type?, since?, until?, limit?)

列出某公司的 SEC 申報,依 filed_at 由新到舊,可用 form_type 與 since / until 日期區間(對 filed_at,含界)過濾。

REST endpoint:GET /api/filings

觸及資料表:filings

背後 SQL

SELECT accession, ticker, cik, form_type, filed_at, period_of_report,
       primary_doc_url, raw_storage_key
FROM filings
WHERE ticker = :ticker
  -- optional: AND form_type = :form_type AND filed_at >= :since AND filed_at <= :until
ORDER BY filed_at DESC
LIMIT :limit

回傳範例

[
  {
    "accession": "0000320193-26-000042",
    "ticker": "AAPL",
    "cik": "0000320193",
    "form_type": "10-Q",
    "filed_at": "2026-05-01T16:30:05+00:00",
    "period_of_report": "2026-03-28",
    "primary_doc_url": "https://www.sec.gov/Archives/edgar/data/320193/000032019326000042/aapl-20260328.htm",
    "raw_storage_key": "filings/AAPL/0000320193-26-000042/primary.htm"
  }
]

get_filing(accession)

以 SEC accession number 取得單一申報的後設資料,查無則回 404。

REST endpoint:GET /api/filings/{accession}

觸及資料表:filings

背後 SQL

SELECT accession, ticker, cik, form_type, filed_at, period_of_report,
       primary_doc_url, raw_storage_key
FROM filings
WHERE accession = :accession

回傳範例

{
  "accession": "0000320193-26-000042",
  "ticker": "AAPL",
  "cik": "0000320193",
  "form_type": "10-Q",
  "filed_at": "2026-05-01T16:30:05+00:00",
  "period_of_report": "2026-03-28",
  "primary_doc_url": "https://www.sec.gov/Archives/edgar/data/320193/000032019326000042/aapl-20260328.htm",
  "raw_storage_key": "filings/AAPL/0000320193-26-000042/primary.htm"
}

list_filing_sections(accession)

列出某申報已解析的章節目錄(id、item_code、標題、字元範圍),不含內文,依字元起點排序。

REST endpoint:GET /api/filings/{accession}/sections

觸及資料表:filing_sections

背後 SQL

SELECT id, accession, item_code, title, char_start, char_end
FROM filing_sections
WHERE accession = :accession
ORDER BY char_start NULLS LAST, id

回傳範例

[
  {
    "id": 918273,
    "accession": "0000320193-26-000042",
    "item_code": "Item 1A",
    "title": "Risk Factors",
    "char_start": 12045,
    "char_end": 48210
  },
  {
    "id": 918274,
    "accession": "0000320193-26-000042",
    "item_code": "Item 2",
    "title": "Management's Discussion and Analysis of Financial Condition and Results of Operations",
    "char_start": 48211,
    "char_end": 96530
  }
]

get_filing_section(accession, item_code)

以 (accession, item_code) 取得單一章節的完整內文,查無則回 404。

REST endpoint:GET /api/filings/{accession}/sections/{item_code}

觸及資料表:filing_sections

背後 SQL

SELECT id, accession, item_code, title, content, char_start, char_end
FROM filing_sections
WHERE accession = :accession AND item_code = :item_code
LIMIT 1

回傳範例

{
  "id": 918273,
  "accession": "0000320193-26-000042",
  "item_code": "Item 1A",
  "title": "Risk Factors",
  "content": "The Company's business, reputation, results of operations, financial condition and stock price can be affected by a number of factors, whether currently known or unknown, including those described below ...",
  "char_start": 12045,
  "char_end": 48210
}

三大財報與估值

資料表

income_statements

損益表逐期資料,來自 SEC XBRL filing 正規化為 USD。每列為某公司某會計期(年報 FY 或季報 Q1 至 Q3)的一張損益表。

欄位	型別	說明
ticker	varchar(10) PK	美股代號,複合主鍵之一。
period_end	date PK	會計期末日,複合主鍵之一,也是排序鍵。
fiscal_period	varchar(10) PK	期別代碼:FY 為年報,Q1 至 Q3 為季報。
fiscal_year	integer	會計年度(可空)。
revenue	bigint	營收(USD,可空)。
gross_profit	bigint	毛利(USD,可空)。
operating_income	bigint	營業利益(USD,可空)。
net_income	bigint	淨利(USD,可空)。
eps_diluted	numeric(12,4)	稀釋每股盈餘(可空)。
source_accession	varchar(20) FK	來源 filing 的 SEC accession;另有 cost_of_revenue、rd_expense、sga_expense、eps_basic、shares_basic、shares_diluted 等欄。

關聯

source_accession → filings.accession (FK, SET NULL)
ticker → companies.ticker (logical join, no DB FK)

balance_sheets

資產負債表逐期資料,來自 SEC XBRL filing 正規化為 USD。每列為某公司某會計期末的資產、負債與權益快照。

欄位	型別	說明
ticker	varchar(10) PK	美股代號,複合主鍵之一。
period_end	date PK	會計期末日,複合主鍵之一,也是排序鍵。
fiscal_period	varchar(10) PK	期別代碼:FY 為年報,Q1 至 Q3 為季報。
cash_and_equivalents	bigint	現金及約當現金(USD,可空)。
total_current_assets	bigint	流動資產合計(USD,可空)。
total_assets	bigint	資產總額(USD,可空)。
total_current_liabilities	bigint	流動負債合計(USD,可空)。
total_liabilities	bigint	負債總額(USD,可空)。
total_equity	bigint	權益總額(USD,可空)。
source_accession	varchar(20) FK	來源 filing 的 SEC accession;另有 goodwill、intangibles、long_term_debt、inventory 等欄。

關聯

source_accession → filings.accession (FK, SET NULL)
ticker → companies.ticker (logical join, no DB FK)

cash_flow_statements

現金流量表逐期資料,來自 SEC XBRL filing 正規化為 USD。每列含營業、投資、融資三大活動現金流與自由現金流。

欄位	型別	說明
ticker	varchar(10) PK	美股代號,複合主鍵之一。
period_end	date PK	會計期末日,複合主鍵之一,也是排序鍵。
fiscal_period	varchar(10) PK	期別代碼:FY 為年報,Q1 至 Q3 為季報。
operating_cash_flow	bigint	營業活動現金流(USD,可空)。
capex	bigint	資本支出(USD,通常為負,可空)。
free_cash_flow	bigint	自由現金流(USD,可空)。
dividends_paid	bigint	支付股利(USD,可空)。
share_buybacks	bigint	股票回購(USD,可空)。
net_change_in_cash	bigint	現金淨變動(USD,可空)。
source_accession	varchar(20) FK	來源 filing 的 SEC accession;另有 financing_cash_flow、investing_cash_flow 等欄。

關聯

source_accession → filings.accession (FK, SET NULL)
ticker → companies.ticker (logical join, no DB FK)

company_overview

每檔股票一列的估值快照,由 Alpha Vantage OVERVIEW 灌入,市值每日以最新收盤自算。含本益比家族、Beta、52 週高低、均線與分析師目標價。

欄位	型別	說明
ticker	varchar(10) PK	美股代號,主鍵。
market_cap	bigint	市值(USD,可空);查詢層以最新收盤乘股數自算。
pe_ratio	numeric(14,4)	本益比(可空)。
peg_ratio	numeric(14,4)	PEG 比(可空)。
price_to_book	numeric(14,4)	股價淨值比(可空)。
ev_to_ebitda	numeric(14,4)	EV/EBITDA 倍數(可空)。
dividend_yield	numeric(10,6)	股息殖利率(0 至 1 小數,非百分比,可空)。
return_on_equity_ttm	numeric(10,6)	ROE TTM(0 至 1 小數,可空)。
analyst_target_price	numeric(14,4)	分析師目標價(USD,可空)。
updated_at	timestamptz	刷新時間;另有 forward_pe、price_to_sales_ttm、beta、week_52_high/low、ma_50、ma_200 等數十欄。

關聯

ticker → companies.ticker (logical 1:1, intentionally no DB FK)

Functions

get_income_statements(ticker, period?, limit?)

取得某公司的損益表逐期列表,依期末由新到舊。period 傳 annual 取年報(FY)、quarterly 取季報(Q1 至 Q3),省略則兩者都回;limit 預設 20(1 至 200)。金額為 USD。

REST endpoint:GET /api/financials/income

觸及資料表:income_statements

背後 SQL

SELECT ticker, period_end, fiscal_period, fiscal_year,
       revenue, gross_profit, operating_income, net_income, eps_diluted
FROM income_statements
WHERE ticker = :ticker
  -- period='annual'    -> AND fiscal_period = 'FY'
  -- period='quarterly' -> AND fiscal_period IN ('Q1','Q2','Q3')
ORDER BY period_end DESC
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "fiscal_year": 2026,
    "revenue": 95359000000,
    "cost_of_revenue": 50492000000,
    "gross_profit": 44867000000,
    "operating_expenses": 15274000000,
    "rd_expense": 8550000000,
    "sga_expense": 6724000000,
    "operating_income": 29593000000,
    "interest_expense": null,
    "tax_expense": 4864000000,
    "net_income": 24780000000,
    "eps_basic": "1.66",
    "eps_diluted": "1.65",
    "shares_basic": 14928000000,
    "shares_diluted": 15022000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
]

get_balance_sheets(ticker, period?, limit?)

取得某公司的資產負債表逐期列表,依期末由新到舊。period 過濾同損益表(annual 為 FY、quarterly 為 Q1 至 Q3);limit 預設 20(1 至 200)。金額為 USD。

REST endpoint:GET /api/financials/balance

觸及資料表:balance_sheets

背後 SQL

SELECT ticker, period_end, fiscal_period,
       cash_and_equivalents, total_current_assets, total_assets,
       total_liabilities, total_equity
FROM balance_sheets
WHERE ticker = :ticker
  -- period='annual'    -> AND fiscal_period = 'FY'
  -- period='quarterly' -> AND fiscal_period IN ('Q1','Q2','Q3')
ORDER BY period_end DESC
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "cash_and_equivalents": 28160000000,
    "short_term_investments": 35230000000,
    "accounts_receivable": 21540000000,
    "inventory": 6100000000,
    "total_current_assets": 133210000000,
    "ppe_net": 45680000000,
    "goodwill": null,
    "intangibles": null,
    "total_assets": 344090000000,
    "accounts_payable": 54180000000,
    "short_term_debt": 14200000000,
    "total_current_liabilities": 145300000000,
    "long_term_debt": 85750000000,
    "total_liabilities": 277320000000,
    "total_equity": 66770000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
]

get_cash_flow_statements(ticker, period?, limit?)

取得某公司的現金流量表逐期列表,依期末由新到舊。含營業現金流、資本支出、自由現金流、股利與回購;limit 預設 20(1 至 200)。金額為 USD。

REST endpoint:GET /api/financials/cashflow

觸及資料表:cash_flow_statements

背後 SQL

SELECT ticker, period_end, fiscal_period,
       operating_cash_flow, capex, free_cash_flow,
       dividends_paid, share_buybacks, net_change_in_cash
FROM cash_flow_statements
WHERE ticker = :ticker
  -- same period filter as income statements
ORDER BY period_end DESC
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "operating_cash_flow": 28950000000,
    "capex": -2870000000,
    "free_cash_flow": 26080000000,
    "dividends_paid": -3820000000,
    "share_buybacks": -23500000000,
    "financing_cash_flow": -29400000000,
    "investing_cash_flow": -1200000000,
    "net_change_in_cash": -1650000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
]

get_latest_period(ticker, period?)

取得最近一期的三張財報合體(income、balance、cash_flow,同一個 period_end)。先以 income_statements 選出最新期,再抓相同 period_end 的另兩表;某表缺該期則為 null。查無財報回 404。

REST endpoint:GET /api/financials/latest

觸及資料表:income_statementsbalance_sheetscash_flow_statements

背後 SQL

-- 1) pick the newest (period_end, fiscal_period) from income_statements
SELECT period_end, fiscal_period
FROM income_statements
WHERE ticker = :ticker
  -- same optional period filter (FY or IN ('Q1','Q2','Q3'))
ORDER BY period_end DESC
LIMIT 1;

-- 2) fetch each statement at that fiscal_period, keep rows matching period_end
SELECT * FROM income_statements    WHERE ticker = :ticker AND fiscal_period = :fp ORDER BY period_end DESC LIMIT 1;
SELECT * FROM balance_sheets       WHERE ticker = :ticker AND fiscal_period = :fp ORDER BY period_end DESC LIMIT 1;
SELECT * FROM cash_flow_statements WHERE ticker = :ticker AND fiscal_period = :fp ORDER BY period_end DESC LIMIT 1;

回傳範例

{
  "ticker": "AAPL",
  "period_end": "2026-03-28",
  "fiscal_period": "Q2",
  "income": {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "fiscal_year": 2026,
    "revenue": 95359000000,
    "gross_profit": 44867000000,
    "operating_income": 29593000000,
    "net_income": 24780000000,
    "eps_basic": "1.66",
    "eps_diluted": "1.65",
    "shares_diluted": 15022000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  },
  "balance": {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "cash_and_equivalents": 28160000000,
    "total_current_assets": 133210000000,
    "total_assets": 344090000000,
    "total_current_liabilities": 145300000000,
    "total_liabilities": 277320000000,
    "total_equity": 66770000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  },
  "cash_flow": {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "operating_cash_flow": 28950000000,
    "capex": -2870000000,
    "free_cash_flow": 26080000000,
    "dividends_paid": -3820000000,
    "share_buybacks": -23500000000,
    "net_change_in_cash": -1650000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
}

get_overview(ticker)

取得某公司的估值快照原始數據:市值、本益比家族、Beta、52 週高低、均線與分析師目標價。資料源 Alpha Vantage OVERVIEW,每日刷新;尚未被覆蓋的 ticker 回 404。

REST endpoint:GET /api/overview/{ticker}

觸及資料表:company_overview

背後 SQL

SELECT ticker, market_cap, shares_outstanding, ebitda, revenue_ttm,
       pe_ratio, forward_pe, peg_ratio, price_to_book, price_to_sales_ttm,
       ev_to_ebitda, dividend_yield, return_on_equity_ttm,
       beta, week_52_high, week_52_low, ma_50, ma_200,
       analyst_target_price, latest_quarter, updated_at
FROM company_overview
WHERE ticker = :ticker

回傳範例

{
  "ticker": "AAPL",
  "market_cap": 3125000000000,
  "shares_outstanding": 14840000000,
  "ebitda": 138500000000,
  "revenue_ttm": 420700000000,
  "gross_profit_ttm": 190400000000,
  "pe_ratio": 32.4,
  "forward_pe": 29.1,
  "peg_ratio": 2.61,
  "price_to_book": 46.8,
  "price_to_sales_ttm": 7.43,
  "ev_to_ebitda": 23.1,
  "eps": 6.52,
  "dividend_per_share": 1.04,
  "dividend_yield": 0.0049,
  "profit_margin": 0.243,
  "return_on_equity_ttm": 1.48,
  "beta": 1.19,
  "week_52_high": 237.49,
  "week_52_low": 169.21,
  "ma_50": 211.34,
  "ma_200": 198.77,
  "analyst_target_price": 245.0,
  "latest_quarter": "2026-03-28",
  "updated_at": "2026-07-09T16:00:00Z"
}

內部人與機構 13F

資料表

insider_trades

SEC Form 4 內部人(高管/董事/大股東)交易明細,一份 Form 4 可含多筆交易,故用合成 id 當主鍵。內部人須在成交後 2 個營業日內申報,ETL 每日 ingest。

欄位	型別	說明
id	bigint PK	合成主鍵(自增)。
ticker	varchar(10)	股票代號,與 companies.ticker 邏輯對應(無 FK 約束)。
accession	varchar(20) FK	來源 SEC filing accession。
insider_name	text	內部人姓名(可空)。
insider_title	text	職稱,例 CEO / Director(可空)。
transaction_date	date	成交日(可空)。
transaction_code	varchar(2)	SEC Form 4 代碼,P=open-market 買、S=賣。
shares	bigint	本筆交易股數(可空)。
price_per_share	numeric(18,4)	每股價格(USD,可空)。
shares_owned_after	bigint	交易後持股數,用於自算內部人持股比例。

關聯

accession → filings.accession (FK, CASCADE)
ticker → companies.ticker (logical join, no FK)

institutional_holdings

SEC 13F-HR 機構持股明細,主鍵為 (filer_cik, cusip, quarter_end)。約 4100 萬列,查詢靠 partial index(WHERE ticker IS NOT NULL)。季度持倉,申報延遲約 45 天,每日以 1/7 filer 輪替刷新。

欄位	型別	說明
filer_cik	varchar(10) PK	申報機構 CIK。
cusip	varchar(9) PK	13F-HR 唯一保證有的證券識別碼。
quarter_end	date PK	持倉季底日。
ticker	varchar(10) FK	由 cusip 解析而來,對不到時為 null。
shares	bigint	持有股數(可空)。
market_value	bigint	持有市值(USD,可空)。
change_in_shares	bigint	相對前季股數變動(可正可負)。
change_type	varchar(10)	變動類別:new / increase / decrease / sold_out。
source_accession	varchar(20)	來源 13F accession(無跨表 FK)。

關聯

filer_cik → institutions.cik (FK)
ticker → companies.ticker (FK, SET NULL)

institutions

13F filer 登錄表,把 CIK 對到機構名稱,供 typeahead 與把持倉 CIK 解析成名稱用。

欄位	型別	說明
cik	varchar(10) PK	機構 CIK。
name	text	機構名稱。
first_seen	date	首次出現日期(可空)。
last_updated	timestamptz	資料列最後更新時間。

關聯

institutional_holdings.filer_cik → institutions.cik
institution_filings.filer_cik → institutions.cik

Functions

list_insider_trades(ticker, since?, until?, limit?)

列出單一公司內部人 Form 4 交易,依交易日新到舊。單 ticker 視角,要跨市場篩買進改用 screen_insider_buys。

REST endpoint:GET /api/insider

觸及資料表:insider_trades

背後 SQL

SELECT id, ticker, accession, insider_name, insider_title,
       transaction_date, transaction_code, shares, price_per_share,
       total_value, shares_owned_after, is_direct
FROM insider_trades
WHERE ticker = :ticker
  -- optional: AND transaction_date >= :since AND transaction_date <= :until
ORDER BY transaction_date DESC NULLS LAST, id DESC
LIMIT :limit

回傳範例

[
  {
    "id": 4821507,
    "ticker": "AAPL",
    "accession": "0000320193-26-000075",
    "insider_name": "COOK TIMOTHY D",
    "insider_title": "Chief Executive Officer",
    "transaction_date": "2026-04-01",
    "transaction_code": "S",
    "shares": 223986,
    "price_per_share": "169.32",
    "total_value": 37925069,
    "shares_owned_after": 3280150,
    "is_direct": true
  }
]

screen_insider_buys(transaction_code?, since_days?, ticker_contains?, insider_title_contains?, market_cap_min?, market_cap_max?, limit?)

跨整個 universe 篩內部人 open-market 交易(預設買進),依 transaction_date DESC、usd_value DESC 排序。market_cap 用 LATERAL 對 prices_daily 逐 ticker index seek 現算,缺價或缺股數時為 null。

REST endpoint:GET /api/screener/insider-buys

觸及資料表:insider_tradescompaniesprices_dailyincome_statements

背後 SQL

WITH base AS (
  SELECT it.transaction_date, it.ticker, c.name AS company_name,
         (pd.close * COALESCE(c.shares_outstanding, isd.shares_diluted))::float8 AS market_cap,
         it.insider_name, it.insider_title, it.shares, it.price_per_share,
         (it.shares * it.price_per_share)::float8 AS usd_value
  FROM insider_trades it
  LEFT JOIN companies c ON c.ticker = it.ticker
  LEFT JOIN LATERAL (
    SELECT p.close FROM prices_daily p
    WHERE p.ticker = it.ticker ORDER BY p.date DESC LIMIT 1
  ) pd ON true
  LEFT JOIN LATERAL (
    SELECT i.shares_diluted FROM income_statements i
    WHERE i.ticker = it.ticker AND i.shares_diluted IS NOT NULL
    ORDER BY i.period_end DESC LIMIT 1
  ) isd ON true
  WHERE it.transaction_code = :transaction_code
    AND it.transaction_date >= CURRENT_DATE - make_interval(days => :since_days)
)
SELECT * FROM base
WHERE (:market_cap_min IS NULL OR market_cap >= :market_cap_min)
  AND (:market_cap_max IS NULL OR market_cap <= :market_cap_max)
ORDER BY transaction_date DESC, usd_value DESC NULLS LAST
LIMIT :limit

回傳範例

{
  "items": [
    {
      "transaction_date": "2026-06-30",
      "ticker": "OXY",
      "company_name": "Occidental Petroleum Corp",
      "market_cap": 45600000000.0,
      "insider_name": "HOLLUB VICKI A",
      "insider_title": "President and Chief Executive Officer",
      "shares": 10000,
      "price_per_share": "48.50",
      "usd_value": 485000.0
    }
  ],
  "count": 1
}

list_13f_holders(ticker, quarter_end?, limit?)

列出某股票的所有 13F 機構持有人,依持有市值由大到小。quarter_end 省略時取「已過申報截止(季末後 45 天)」的最新季,避免季末剛過只算到零星早鳥造成大型股假性 0% 機構持有。

REST endpoint:GET /api/13f/holders

觸及資料表:institutional_holdingsinstitutions

背後 SQL

-- quarter_end omitted -> latest settled quarter for the ticker:
SELECT MAX(quarter_end) FILTER (
         WHERE quarter_end <= CURRENT_DATE - make_interval(days => 45)
       ) AS settled,
       MAX(quarter_end) AS newest
FROM institutional_holdings WHERE ticker = :ticker;  -- use settled, fall back to newest

SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end
ORDER BY ih.market_value DESC NULLS LAST
LIMIT :limit

回傳範例

[
  {
    "filer_cik": "0000102909",
    "filer_name": "VANGUARD GROUP INC",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 1370000000,
    "market_value": 231000000000,
    "change_in_shares": 16800000,
    "change_type": "increase"
  },
  {
    "filer_cik": "0001364742",
    "filer_name": "BLACKROCK INC",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 1050000000,
    "market_value": 177000000000,
    "change_in_shares": -4700000,
    "change_type": "decrease"
  }
]

list_13f_portfolio(cik, quarter_end?, limit?)

列出某機構(以 CIK 指定)整個 13F 持倉,依持有市值由大到小。quarter_end 省略時取該 filer 的 MAX(quarter_end)(單一 filer 一次申報整份持倉,有列即代表該季完整)。ticker 可能為 null。

REST endpoint:GET /api/13f/portfolio

觸及資料表:institutional_holdingsinstitutions

背後 SQL

-- quarter_end omitted -> MAX(quarter_end) WHERE filer_cik = :cik
SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.filer_cik = :cik AND ih.quarter_end = :quarter_end
ORDER BY ih.market_value DESC NULLS LAST
LIMIT :limit

回傳範例

[
  {
    "filer_cik": "0001067983",
    "filer_name": "BERKSHIRE HATHAWAY INC",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 300000000,
    "market_value": 50600000000,
    "change_in_shares": 0,
    "change_type": "no_change"
  },
  {
    "filer_cik": "0001067983",
    "filer_name": "BERKSHIRE HATHAWAY INC",
    "ticker": "BAC",
    "cusip": "060505104",
    "quarter_end": "2026-03-31",
    "shares": 631600000,
    "market_value": 25900000000,
    "change_in_shares": -48700000,
    "change_type": "decrease"
  }
]

list_13f_top_buyers(ticker, quarter_end?, limit?)

列出某股票本季新進或增持最多的機構,篩 change_type IN ('new','increase'),依 COALESCE(change_in_shares, shares) 由大到小。quarter_end 省略時走與 holders 相同的「已截止最新季」規則。

REST endpoint:GET /api/13f/top-buyers

觸及資料表:institutional_holdingsinstitutions

背後 SQL

SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end
  AND ih.change_type IN ('new', 'increase')
ORDER BY COALESCE(ih.change_in_shares, ih.shares) DESC NULLS LAST
LIMIT :limit

回傳範例

[
  {
    "filer_cik": "0000895421",
    "filer_name": "MORGAN STANLEY",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 12500000,
    "market_value": 2110000000,
    "change_in_shares": 12500000,
    "change_type": "new"
  }
]

list_13f_top_sellers(ticker, quarter_end?, limit?)

列出某股票本季減持或出清最多的機構,篩 change_type IN ('decrease','sold_out'),依 change_in_shares 遞增排序(最負、賣最多在前)。quarter_end 省略時走「已截止最新季」規則。

REST endpoint:GET /api/13f/top-sellers

觸及資料表:institutional_holdingsinstitutions

背後 SQL

SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end
  AND ih.change_type IN ('decrease', 'sold_out')
ORDER BY ih.change_in_shares ASC NULLS LAST
LIMIT :limit

回傳範例

[
  {
    "filer_cik": "0000093751",
    "filer_name": "STATE STREET CORP",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 583000000,
    "market_value": 98300000000,
    "change_in_shares": -9200000,
    "change_type": "decrease"
  }
]

list_institutional_holders(ticker)

取得某股票最新一季前幾大機構持有人的摘要視圖,依持股數由大到小。每筆附 pct_held = shares / shares_out 與相對前季的 pct_change。要完整名單與季度變動改用 list_13f_holders。

REST endpoint:GET /api/holdings/institutions

觸及資料表:institutional_holdingsinstitutionscompaniesincome_statements

背後 SQL

SELECT i.name AS holder, ih.shares, ih.market_value AS value,
       ih.change_in_shares, ih.change_type, ih.quarter_end
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end  -- latest settled quarter
ORDER BY ih.shares DESC NULLS LAST
LIMIT :limit
-- pct_held = shares / COALESCE(companies.shares_outstanding,
--                              latest income_statements.shares_diluted)

回傳範例

[
  {
    "holder": "VANGUARD GROUP INC",
    "date_reported": "2026-03-31",
    "shares": 1370000000,
    "value": 231000000000,
    "pct_held": 0.0902,
    "pct_change": 0.0123
  },
  {
    "holder": "BLACKROCK INC",
    "date_reported": "2026-03-31",
    "shares": 1050000000,
    "value": 177000000000,
    "pct_held": 0.0691,
    "pct_change": -0.0045
  }
]

get_holders_breakdown(ticker)

回傳內部人/機構/流通占比總覽(0 至 1 小數),全部第一手自算。機構 = LEAST(1, 最新已截止季 13F 總持股 / shares_out);內部人 = 每位內部人最新一筆 Form 4 shares_owned_after 加總 / shares_out;float = GREATEST(0, 1 - 機構 - 內部人)。shares_out 缺時三欄皆 null。

REST endpoint:GET /api/holdings/major

觸及資料表:institutional_holdingsinsider_tradescompaniesincome_statements

背後 SQL

-- shares_out = COALESCE(companies.shares_outstanding,
--                        latest income_statements.shares_diluted)

-- institutions (newest settled quarter):
SELECT COALESCE(SUM(shares), 0) AS inst_shares, COUNT(*) AS inst_count
FROM institutional_holdings
WHERE ticker = :ticker AND quarter_end = :quarter_end;

-- insiders (each insider's latest Form 4 shares_owned_after):
SELECT COALESCE(SUM(shares_owned_after), 0) AS insider_shares
FROM (
  SELECT DISTINCT ON (insider_name) shares_owned_after
  FROM insider_trades
  WHERE ticker = :ticker AND insider_name IS NOT NULL
  ORDER BY insider_name, transaction_date DESC
) s;

-- institutions_pct = LEAST(1, inst_shares / shares_out)
-- insiders_pct     = insider_shares / shares_out
-- float_pct        = GREATEST(0, 1 - institutions_pct - insiders_pct)

回傳範例

{
  "insiders_pct": 0.0007,
  "institutions_pct": 0.6123,
  "institutions_float_pct": 0.3870,
  "institutions_count": 5218,
  "source": "investor-db (SEC 13F + Form 4)"
}

search_institutions(q, limit?)

以機構名稱或 CIK 關鍵字模糊搜尋 13F filer(typeahead)。排序:CIK 完全相等優先、名稱前綴次之、其餘子字串命中最後,同級按名稱字母。每筆附 latest_quarter。

REST endpoint:GET /api/13f/institutions/search

觸及資料表:institutionsinstitutional_holdings

背後 SQL

SELECT i.cik, i.name, i.first_seen, lq.latest_quarter
FROM institutions i
LEFT JOIN (
  SELECT filer_cik, MAX(quarter_end) AS latest_quarter
  FROM institutional_holdings GROUP BY filer_cik
) lq ON lq.filer_cik = i.cik
WHERE i.cik = :exact_cik OR i.name ILIKE :contains
ORDER BY CASE
           WHEN i.cik = :exact_cik THEN 0
           WHEN i.name ILIKE :prefix THEN 1
           ELSE 2
         END, i.name
LIMIT :limit

回傳範例

[
  {
    "cik": "0001067983",
    "name": "BERKSHIRE HATHAWAY INC",
    "first_seen": "2013-05-15",
    "latest_quarter": "2026-03-31"
  },
  {
    "cik": "0001610520",
    "name": "BERKSHIRE ASSET MANAGEMENT LLC PA",
    "first_seen": "2014-02-14",
    "latest_quarter": "2026-03-31"
  }
]

get_institution(cik)

以精確 CIK 取得單一 13F filer 基本資料(名稱 / first_seen / 最新持倉季);查無回 404。latest_quarter 來自 institutional_holdings 的 MAX(quarter_end)。

REST endpoint:GET /api/13f/institutions/{cik}

觸及資料表:institutionsinstitutional_holdings

背後 SQL

SELECT i.cik, i.name, i.first_seen, lq.latest_quarter
FROM institutions i
LEFT JOIN (
  SELECT filer_cik, MAX(quarter_end) AS latest_quarter
  FROM institutional_holdings GROUP BY filer_cik
) lq ON lq.filer_cik = i.cik
WHERE i.cik = :cik

回傳範例

{
  "cik": "0001067983",
  "name": "BERKSHIRE HATHAWAY INC",
  "first_seen": "2013-05-15",
  "latest_quarter": "2026-03-31"
}

價格與期權

資料表

prices_daily

第一手每日 OHLCV 日 K(Alpha Vantage TIME_SERIES_DAILY_ADJUSTED),約 5 年滾動保留。PK 為 (ticker, date);表內雖有 adj_close 欄,但 /api/prices/daily 只回 OHLCV。

欄位	型別	說明
ticker	varchar(10) PK	美股代號,PK 之一。
date	date PK	交易日,PK 之一。
open	numeric(14,4)	開盤價(USD)。
high	numeric(14,4)	當日最高價。
low	numeric(14,4)	當日最低價。
close	numeric(14,4)	收盤價。
volume	bigint	成交股數。
adj_close	numeric(14,4)	分割股利還原後收盤;daily 端點不回傳此欄。

關聯

ticker → companies.ticker (soft join, no DB FK)

prices_hourly

每小時 OHLCV 日內 K(dt 為 timestamptz,以美東切日界),約 60 天滾動保留。PK 為 (ticker, dt),含 adj_close 欄且 hourly 端點會回傳。

欄位	型別	說明
ticker	varchar(10) PK	美股代號,PK 之一。
dt	timestamptz PK	含時區的小時時戳,PK 之一。
open	numeric(18,4)	該小時開盤價。
high	numeric(18,4)	該小時最高價。
low	numeric(18,4)	該小時最低價。
close	numeric(18,4)	該小時收盤價。
volume	bigint	該小時成交股數。
adj_close	numeric(18,4)	還原後收盤;hourly 端點會回傳。

關聯

ticker → companies.ticker (soft join, no DB FK)

options_eod

期權 EOD snapshot(Alpha Vantage HISTORICAL_OPTIONS),一列為一個 OCC 合約某交易日的報價加 IV 與 greeks。PK 為 (contract_id, date);另有 last、mark、bid、ask、bid_size、ask_size、volume、open_interest、rho 等欄。

欄位	型別	說明
contract_id	varchar(24) PK	OCC 合約代號,PK 之一。
underlying	varchar(10)	標的代號(AV symbol,不保證對齊 companies.ticker)。
expiration	date	合約到期日。
strike	numeric(14,4)	履約價。
option_type	varchar(4)	'call' 或 'put'。
implied_volatility	numeric(12,6)	隱含波動率,低流動性合約可能為 null。
delta	numeric(12,6)	greek delta,可為負。
gamma	numeric(12,6)	greek gamma。
theta	numeric(12,6)	greek theta,通常為負。
vega	numeric(12,6)	greek vega。

關聯

underlying → companies.ticker (soft join, AV symbol may differ)

Functions

list_daily_prices(ticker, start?, end?, limit?)

取得第一手每日 OHLC 加成交量(價格 USD),依日期升冪。約 5 年滾動歷史,不含還原價 adj_close,跨除權息或分割需自行以 list_dividends 與 list_splits 調整。

REST endpoint:GET /api/prices/daily

觸及資料表:prices_daily

背後 SQL

SELECT ticker, date, open, high, low, close, volume
FROM prices_daily
WHERE ticker = :ticker
  AND (CAST(:start AS date) IS NULL OR date >= :start)
  AND (CAST(:end AS date) IS NULL OR date <= :end)
ORDER BY date
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "date": "2026-07-08",
    "open": "210.5000",
    "high": "212.3400",
    "low": "209.8700",
    "close": "211.6200",
    "volume": 48213500
  },
  {
    "ticker": "AAPL",
    "date": "2026-07-09",
    "open": "211.8000",
    "high": "214.1500",
    "low": "211.2000",
    "close": "213.7700",
    "volume": 52901200
  }
]

get_latest_price(ticker)

取得最新一個交易日的第一手日 K(OHLC 加成交量,USD),即 prices_daily 依日期倒序取第一筆。查無價格回 404。

REST endpoint:GET /api/prices/daily/latest

觸及資料表:prices_daily

背後 SQL

SELECT ticker, date, open, high, low, close, volume
FROM prices_daily
WHERE ticker = :ticker
ORDER BY date DESC
LIMIT 1

回傳範例

{
  "ticker": "AAPL",
  "date": "2026-07-09",
  "open": "211.8000",
  "high": "214.1500",
  "low": "211.2000",
  "close": "213.7700",
  "volume": 52901200
}

list_hourly_prices(ticker, start?, end?, limit?)

取得每小時 OHLC 加成交量原列(dt 為 timestamptz,依 dt 升冪),start 與 end 為 UTC ISO datetime。粒度比日 K 細,適合 intraday 分析,歷史僅約 60 天滾動保留。

REST endpoint:GET /api/prices/hourly

觸及資料表:prices_hourly

背後 SQL

SELECT ticker, dt, open, high, low, close, volume, adj_close
FROM prices_hourly
WHERE ticker = :ticker
  AND (CAST(:start AS timestamptz) IS NULL OR dt >= :start)
  AND (CAST(:end AS timestamptz) IS NULL OR dt <= :end)
ORDER BY dt
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "dt": "2026-07-09T14:30:00Z",
    "open": "211.8000",
    "high": "212.9500",
    "low": "211.4000",
    "close": "212.7300",
    "volume": 8123400,
    "adj_close": "212.7300"
  }
]

get_options_chain(ticker, as_of?, expiration?, option_type?, limit?)

取得某標的某交易日的期權鏈(EOD 報價加 IV 與 greeks),依 (到期日, 履約價, call/put) 排序。只回未到期(expiration 大於等於今天)的合約;as_of 省略時自動取該標的最新交易日。數值欄可為 null,勿當 0 計算。

REST endpoint:GET /api/options/chain

觸及資料表:options_eod

背後 SQL

-- as_of omitted -> SELECT MAX(date) FROM options_eod WHERE underlying = :underlying
SELECT contract_id, date, underlying, expiration, strike, option_type,
       last, mark, bid, bid_size, ask, ask_size, volume, open_interest,
       implied_volatility, delta, gamma, theta, vega, rho
FROM options_eod
WHERE underlying = :underlying
  AND date = :as_of
  AND expiration >= CURRENT_DATE
  AND (CAST(:expiration AS date) IS NULL OR expiration = :expiration)
  AND (CAST(:option_type AS text) IS NULL OR option_type = :option_type)
ORDER BY expiration, strike, option_type
LIMIT :limit

回傳範例

[
  {
    "contract_id": "TSLA260717C00300000",
    "date": "2026-07-09",
    "underlying": "TSLA",
    "expiration": "2026-07-17",
    "strike": "300.0000",
    "option_type": "call",
    "last": "18.4500",
    "mark": "18.6000",
    "bid": "18.4000",
    "bid_size": 12,
    "ask": "18.8000",
    "ask_size": 8,
    "volume": 4521,
    "open_interest": 15230,
    "implied_volatility": "0.482100",
    "delta": "0.548300",
    "gamma": "0.011200",
    "theta": "-0.412600",
    "vega": "0.198400",
    "rho": "0.072300"
  },
  {
    "contract_id": "TSLA260717P00300000",
    "date": "2026-07-09",
    "underlying": "TSLA",
    "expiration": "2026-07-17",
    "strike": "300.0000",
    "option_type": "put",
    "last": null,
    "mark": "16.3500",
    "bid": "16.1000",
    "bid_size": 5,
    "ask": "16.6000",
    "ask_size": 3,
    "volume": 2870,
    "open_interest": 9840,
    "implied_volatility": "0.476500",
    "delta": "-0.451700",
    "gamma": "0.011000",
    "theta": "-0.398200",
    "vega": "0.196100",
    "rho": "-0.065900"
  }
]

get_option_expirations(ticker, as_of?)

列出某標的某交易日可選的到期日與各到期合約數,依到期日升冪。只列未到期的到期日,給挑 expiration 用;as_of 省略時取最新交易日。

REST endpoint:GET /api/options/expirations

觸及資料表:options_eod

背後 SQL

SELECT expiration, COUNT(*) AS contract_count
FROM options_eod
WHERE underlying = :underlying
  AND date = :as_of
  AND expiration >= CURRENT_DATE
GROUP BY expiration
ORDER BY expiration

回傳範例

[
  { "expiration": "2026-07-17", "contract_count": 184 },
  { "expiration": "2026-07-24", "contract_count": 162 }
]

get_option_contract_history(contract_id, start?, end?, limit?)

取得單一 OCC 合約的逐日 EOD 時間序列(報價、IV、greeks 隨時間變化),依日期升冪。contract_id 區分大小寫,用 OCC 原樣(例 AAPL260605C00200000)。

REST endpoint:GET /api/options/contract/{contract_id}

觸及資料表:options_eod

背後 SQL

SELECT contract_id, date, underlying, expiration, strike, option_type,
       last, mark, bid, bid_size, ask, ask_size, volume, open_interest,
       implied_volatility, delta, gamma, theta, vega, rho
FROM options_eod
WHERE contract_id = :contract_id
  AND (CAST(:start AS date) IS NULL OR date >= :start)
  AND (CAST(:end AS date) IS NULL OR date <= :end)
ORDER BY date
LIMIT :limit

回傳範例

[
  {
    "contract_id": "TSLA260717C00300000",
    "date": "2026-07-09",
    "underlying": "TSLA",
    "expiration": "2026-07-17",
    "strike": "300.0000",
    "option_type": "call",
    "last": "18.4500",
    "mark": "18.6000",
    "bid": "18.4000",
    "bid_size": 12,
    "ask": "18.8000",
    "ask_size": 8,
    "volume": 4521,
    "open_interest": 15230,
    "implied_volatility": "0.482100",
    "delta": "0.548300",
    "gamma": "0.011200",
    "theta": "-0.412600",
    "vega": "0.198400",
    "rho": "0.072300"
  }
]

公司行動、行事曆、逐字稿與新聞

資料表

dividends

Alpha Vantage 現金股息全史,一列一次配息事件,以除息日為錨點,每週日刷新。

欄位	型別	說明
ticker	varchar(10) PK	股票代號,FK 到 companies。
ex_dividend_date	date PK	除息日,事件錨點。
declaration_date	date	宣告日,資料源常缺(可空)。
record_date	date	登記日,常缺(可空)。
payment_date	date	發放日,常缺(可空)。
amount	numeric(18,6)	每股配息金額,通常 USD(可空)。

關聯

ticker → companies.ticker (FK)

splits

Alpha Vantage 股票分割全史,一列一次分割,供在分割點還原歷史價格,每週日刷新。

欄位	型別	說明
ticker	varchar(10) PK	股票代號,FK 到 companies。
effective_date	date PK	分割生效日。
split_factor	numeric(18,8)	新股數除以舊股數,2:1 為 2.0,1:10 反分割為 0.1(可空)。

關聯

ticker → companies.ticker (FK)

earnings_calendar

Alpha Vantage 財報行事曆,一列一檔股票一個未來財報日(最多未來 12 個月),每日 03:00 UTC 刷新。

欄位	型別	說明
ticker	varchar(10) PK	股票代號,FK 到 companies。
report_date	date PK	預定公布日,為 vendor 估計值。
fiscal_date_ending	date PK	對應財報期末。
estimate_eps	numeric(12,4)	共識 EPS 預估,中小型股常缺(可空)。
currency	varchar(3)	ISO 4217 幣別,常缺(可空)。
report_time	varchar(12)	pre-market、post-market 或 null。

關聯

ticker → companies.ticker (FK)

earnings_call_transcripts

法說會逐字稿一場一列(AV EARNINGS_CALL_TRANSCRIPT),segments=0 為問過無資料的 tombstone,對外只列 segments>0,約 T+1 到位。

欄位	型別	說明
ticker	varchar(10) PK	股票代號(無 FK)。
quarter	char(6) PK	AV 日曆季,如 2025Q4。
segments	integer	該場段數;0 為 tombstone(問過 AV 無逐字稿)。
raw_storage_key	text	R2 原始 JSON key,tombstone 為 null。
fetched_at	timestamptz	抓取時間。

關聯

earnings_call_segments.(ticker, quarter) → earnings_call_transcripts.(ticker, quarter)

earnings_call_segments

法說會逐字稿逐段內文,一列一段(0-based seq),含發言者與 AV 每段情緒分,複合 FK 指向 transcripts。

欄位	型別	說明
id	bigint PK	代理主鍵。
ticker	varchar(10) FK	複合 FK 之一。
quarter	char(6) FK	複合 FK 之一。
seq	integer	場內段序,0-based。
speaker	text	發言者(可空)。
speaker_title	text	發言者職稱(可空)。
content	text	段落內文。
sentiment	numeric(4,2)	AV 每段情緒分(vendor 模型分,非本平台計算,可空)。

關聯

(ticker, quarter) → earnings_call_transcripts.(ticker, quarter) (FK)

news_articles

Alpha Vantage NEWS_SENTIMENT 聚合新聞(vendor 源,非第一手 SEC),一篇一列,url 為去重鍵,留存 12 個月。

欄位	型別	說明
id	bigint PK	代理主鍵。
url	text UNIQUE	原文連結,去重鍵。
title	text	標題(可空)。
summary	text	AV 摘要,無全文(可空)。
source	text	來源名(可空)。
published_at	timestamptz	發布時間。
overall_sentiment	numeric(6,4)	AV 整篇情緒分(vendor)。
overall_label	varchar(20)	AV 情緒標籤,如 Bullish。
topics	jsonb	主題標註陣列 [{topic, relevance}]。

關聯

news_ticker_sentiment.article_id → news_articles.id (FK, CASCADE)

news_ticker_sentiment

文章對某 ticker 的相關度與情緒,一列一組 (文章, ticker),relevance>=0.1 才入庫,published_at 去正規化供快查。

欄位	型別	說明
article_id	bigint PK FK	FK 到 news_articles(CASCADE)。
ticker	varchar(10) PK	股票代號。
published_at	timestamptz	去正規化自文章,供 ticker 排序查詢。
relevance	numeric(6,4)	對此 ticker 相關度 0 至 1,入庫門檻 >= 0.1(可空)。
sentiment	numeric(6,4)	對此 ticker 情緒分(vendor,可空)。
label	varchar(20)	對此 ticker 情緒標籤(可空)。

關聯

article_id → news_articles.id (FK, CASCADE)

Functions

list_dividends(ticker, limit?, offset?)

列出某股票的現金股息歷史,依除息日由新到舊分頁,供除息跳空判讀與自算還原價。

REST endpoint:GET /api/dividends/{ticker}

觸及資料表:dividends

背後 SQL

SELECT ticker, ex_dividend_date, declaration_date, record_date,
       payment_date, amount
FROM dividends
WHERE ticker = :ticker
ORDER BY ex_dividend_date DESC
LIMIT :limit OFFSET :offset

回傳範例

[
  {
    "ticker": "AAPL",
    "ex_dividend_date": "2025-05-12",
    "declaration_date": "2025-05-01",
    "record_date": "2025-05-12",
    "payment_date": "2025-05-15",
    "amount": "0.26"
  },
  {
    "ticker": "AAPL",
    "ex_dividend_date": "2025-02-10",
    "declaration_date": null,
    "record_date": null,
    "payment_date": null,
    "amount": "0.25"
  }
]

list_splits(ticker, limit?, offset?)

列出某股票的股票分割歷史,依生效日由新到舊分頁,供在分割點還原歷史價格序列。

REST endpoint:GET /api/splits/{ticker}

觸及資料表:splits

背後 SQL

SELECT ticker, effective_date, split_factor
FROM splits
WHERE ticker = :ticker
ORDER BY effective_date DESC
LIMIT :limit OFFSET :offset

回傳範例

[
  { "ticker": "NVDA", "effective_date": "2024-06-10", "split_factor": "10.0" },
  { "ticker": "NVDA", "effective_date": "2021-07-20", "split_factor": "4.0" }
]

list_earnings(ticker, start?, end?, limit?)

列出單一公司即將公布的財報日,依公布日由舊到新,可用 start 與 end 過濾,供避開 earnings gap。

REST endpoint:GET /api/earnings/{ticker}

觸及資料表:earnings_calendar

背後 SQL

SELECT ticker, report_date, fiscal_date_ending, estimate_eps,
       currency, report_time
FROM earnings_calendar
WHERE ticker = :ticker
  AND (CAST(:start AS date) IS NULL OR report_date >= :start)
  AND (CAST(:end AS date) IS NULL OR report_date <= :end)
ORDER BY report_date, fiscal_date_ending
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "report_date": "2025-07-31",
    "fiscal_date_ending": "2025-06-30",
    "estimate_eps": "1.42",
    "currency": "USD",
    "report_time": "post-market"
  }
]

get_earnings_calendar(start?, end?, limit?)

掃整個市場某區間內即將公布財報的公司,跨 ticker 依公布日排序,回答下週有誰公布財報。

REST endpoint:GET /api/earnings/calendar

觸及資料表:earnings_calendar

背後 SQL

SELECT ticker, report_date, fiscal_date_ending, estimate_eps,
       currency, report_time
FROM earnings_calendar
WHERE (CAST(:start AS date) IS NULL OR report_date >= :start)
  AND (CAST(:end AS date) IS NULL OR report_date <= :end)
ORDER BY report_date, ticker, fiscal_date_ending
LIMIT :limit

回傳範例

[
  {
    "ticker": "AAPL",
    "report_date": "2025-07-31",
    "fiscal_date_ending": "2025-06-30",
    "estimate_eps": "1.42",
    "currency": "USD",
    "report_time": "post-market"
  },
  {
    "ticker": "MSFT",
    "report_date": "2025-07-31",
    "fiscal_date_ending": "2025-06-30",
    "estimate_eps": "3.35",
    "currency": "USD",
    "report_time": "post-market"
  }
]

get_earnings_transcript(ticker, quarter?, offset?, limit?, speaker?)

取得某公司某季法說會逐字稿的分頁段落與 meta,quarter 省略時自動取最新季,可按 speaker 部分比對過濾,情緒分為 AV vendor 模型分。

REST endpoint:GET /api/companies/{ticker}/transcripts/{quarter}

觸及資料表:earnings_call_transcriptsearnings_call_segments

背後 SQL

-- 1) available quarters (latest first; segments > 0 excludes tombstones)
SELECT quarter, segments, fetched_at
FROM earnings_call_transcripts
WHERE ticker = :ticker AND segments > 0
ORDER BY quarter DESC;

-- 2) paged segments for that quarter (optional speaker ILIKE filter)
SELECT seq, speaker, speaker_title, content, sentiment
FROM earnings_call_segments
WHERE ticker = :ticker AND quarter = :quarter
  AND (CAST(:speaker AS text) IS NULL
       OR speaker ILIKE '%' || :speaker || '%')
ORDER BY seq ASC
LIMIT :limit OFFSET :offset

回傳範例

{
  "ticker": "AAPL",
  "quarter": "2025Q2",
  "segments_total": 72,
  "fetched_at": "2025-05-02T14:30:00+00:00",
  "segments": [
    {
      "seq": 0,
      "speaker": "Operator",
      "speaker_title": null,
      "content": "Good day, and welcome to the Apple Q2 Fiscal Year 2025 Earnings Conference Call...",
      "sentiment": "0.1"
    },
    {
      "seq": 1,
      "speaker": "Tim Cook",
      "speaker_title": "Chief Executive Officer",
      "content": "Thank you. Good afternoon, everyone. Today Apple is reporting revenue of 95.4 billion dollars for the March quarter, up 5 percent from a year ago...",
      "sentiment": "0.35"
    }
  ],
  "available_quarters": ["2025Q2", "2025Q1", "2024Q4", "2024Q3"]
}

get_company_news(ticker, days?, min_relevance?, limit?)

取得某公司近 N 天且 relevance 達標的新聞與情緒,依發布時間由新到舊,為 AV 聚合源(非第一手),每 4 小時刷新。

REST endpoint:GET /api/companies/{ticker}/news

觸及資料表:news_ticker_sentimentnews_articles

背後 SQL

SELECT a.title, a.url, a.source, a.source_domain, ts.published_at,
       a.summary, a.overall_sentiment, a.overall_label,
       ts.relevance, ts.sentiment AS ticker_sentiment, ts.label AS ticker_label
FROM news_ticker_sentiment ts
JOIN news_articles a ON a.id = ts.article_id
WHERE ts.ticker = :ticker
  AND ts.published_at >= (CURRENT_DATE - make_interval(days => :days))
  AND ts.relevance >= :min_relevance
ORDER BY ts.published_at DESC
LIMIT :limit

回傳範例

[
  {
    "title": "Apple Unveils On-Device AI Features at Developer Conference",
    "url": "https://www.reuters.com/technology/apple-ai-features-2025",
    "source": "Reuters",
    "source_domain": "reuters.com",
    "published_at": "2025-06-10T13:45:00Z",
    "summary": "Apple announced a suite of on-device AI capabilities aimed at boosting iPhone sales heading into the second half of the year.",
    "overall_sentiment": "0.284",
    "overall_label": "Somewhat-Bullish",
    "relevance": "0.812",
    "ticker_sentiment": "0.301",
    "ticker_label": "Somewhat-Bullish"
  }
]

get_market_news(topic?, limit?)

取得全市場最新新聞與情緒(不掛單一 ticker),可按 AV 主題過濾,為 AV 聚合源,每 4 小時刷新。

REST endpoint:GET /api/market/news

觸及資料表:news_articles

背後 SQL

SELECT title, url, source, source_domain, published_at, summary,
       overall_sentiment, overall_label, topics
FROM news_articles a
WHERE (
  CAST(:topic AS text) IS NULL
  OR EXISTS (
    SELECT 1
    FROM jsonb_array_elements(a.topics) elem
    WHERE elem ->> 'topic' = :topic
  )
)
ORDER BY published_at DESC
LIMIT :limit

回傳範例

[
  {
    "title": "Wall Street Rallies as Inflation Data Cools",
    "url": "https://www.bloomberg.com/news/markets-rally-inflation-2025",
    "source": "Bloomberg",
    "source_domain": "bloomberg.com",
    "published_at": "2025-06-11T21:05:00Z",
    "summary": "Major indices closed higher after the latest CPI print came in below economist expectations.",
    "overall_sentiment": "0.216",
    "overall_label": "Somewhat-Bullish",
    "topics": [
      { "topic": "Financial Markets", "relevance": "0.99" },
      { "topic": "Economy - Monetary", "relevance": "0.51" }
    ]
  }
]

總經、ETF 與市場快照

資料表

macro_series_meta

總經、商品、指數 series 的自我描述字典,每列一個 series_id,存名稱、單位、頻率、類別。查觀測值前先在這裡挑 series_id 並看它的 unit。

欄位	型別	說明
series_id	varchar(48) PK	穩定 series 代碼(如 CPI、WTI、INDEX_VIX),區分大小寫。
name	text	人類可讀名稱。
unit	text	觀測值單位(percent、USD、index 等),可空。
frequency	varchar(16)	頻率(monthly、quarterly、daily、annual 等)。
category	varchar(16)	分類:macro、commodity、index。
updated_at	timestamptz	最近刷新時間。

關聯

macro_series.series_id → macro_series_meta.series_id (logical link, no DB FK)

macro_series

總經、商品、指數的觀測值長表,一列是某 series 在某日期的數值。value 本身不帶單位,單位要回 macro_series_meta 查。

欄位	型別	說明
series_id	varchar(48) PK	對應 macro_series_meta 的 series_id。
date	date PK	觀測日期。
value	numeric(20,6)	觀測值,單位見 macro_series_meta.unit(可空)。

關聯

series_id → macro_series_meta.series_id (logical link, no DB FK)

etf_profile

ETF 層級 metadata,一列一檔 ETF,存淨資產、費用率、配息率、成立日、是否槓桿。固定約 25 檔大型 ETF,月更。

欄位	型別	說明
ticker	varchar(10) PK	ETF 代號。
net_assets	bigint	淨資產(USD,可空)。
net_expense_ratio	numeric(10,6)	淨費用率,0 至 1 小數(可空)。
portfolio_turnover	numeric(10,6)	投組週轉率,0 至 1 小數(可空)。
dividend_yield	numeric(10,6)	配息率,0 至 1 小數(可空)。
inception_date	date	成立日(可空)。
leveraged	boolean	是否槓桿型(可空)。
updated_at	timestamptz	最近刷新時間(月更)。

關聯

etf_holdings.etf_ticker → etf_profile.ticker (FK)
etf_sector_weights.etf_ticker → etf_profile.ticker (FK)

etf_holdings

ETF 成分股權重明細,一列是某 ETF 持有某成分標的的權重。weight 是 0 至 1 小數。holding_symbol 有索引,可反查哪些 ETF 持有某標的。

欄位	型別	說明
etf_ticker	varchar(10) PK FK	FK 到 etf_profile.ticker。
holding_symbol	varchar(20) PK	成分標的代號,無 FK(可能是現金、海外、未收錄標的)。
description	text	成分名稱,常缺(可空)。
weight	numeric(12,8)	占該 ETF 淨值權重,0 至 1 小數(可空)。

關聯

etf_ticker → etf_profile.ticker (FK)

etf_sector_weights

ETF 的 GICS sector 曝險權重,一列是某 ETF 在某類股的權重。weight 是 0 至 1 小數,每檔約 11 個 sector。

欄位	型別	說明
etf_ticker	varchar(10) PK FK	FK 到 etf_profile.ticker。
sector	varchar(64) PK	GICS 類股名。
weight	numeric(12,8)	占該 ETF 淨值權重,0 至 1 小數(可空)。

關聯

etf_ticker → etf_profile.ticker (FK)

market_movers

每交易日收盤後的全市場漲跌與成交熱榜,一列是某日某榜第 N 名。category 為 gainer、loser、most_active,各取前 20。ticker 不設 FK,常含宇宙外標的。

欄位	型別	說明
date	date PK	榜單所屬交易日。
category	varchar(12) PK	榜別:gainer、loser、most_active。
rank	integer PK	名次 1 至 20。
ticker	varchar(10)	標的代號,無 FK,常含宇宙外標的。
price	numeric(14,4)	當日價格(USD,可空)。
change_amount	numeric(14,4)	相對前一交易日的價格變動(USD,可空)。
change_pct	numeric(10,4)	漲跌幅百分比數值(12.34 代表 12.34%,可空)。
volume	bigint	當日成交量(可空)。

ipo_calendar

近期與即將上市新股的行事曆,一列一檔。price_range 的 0 已在入庫前轉為 null(尚未定價)。symbol 不設 FK,尚未掛牌不在個股宇宙。

欄位	型別	說明
symbol	varchar(10) PK	標的代號,無 FK。
ipo_date	date PK	預定掛牌日(vendor 估計,可能變動)。
name	text	公司名(可空)。
price_range_low	numeric(14,4)	定價區間下界(USD),null 代表尚未定價。
price_range_high	numeric(14,4)	定價區間上界(USD),null 代表尚未定價。
exchange	text	掛牌交易所(可空)。

Functions

list_macro_series(category?)

列出可查的總經、商品、指數 series 目錄,依 category 與 series_id 排序。先在這裡挑 series_id 並看 unit,再帶去 get_macro_series 取時間序列。

REST endpoint:GET /api/macro/series

觸及資料表:macro_series_meta

背後 SQL

SELECT series_id, name, unit, frequency, category, updated_at
FROM macro_series_meta
WHERE (CAST(:category AS text) IS NULL OR category = :category)
ORDER BY category NULLS LAST, series_id

回傳範例

[
  { "series_id": "CPI", "name": "Consumer Price Index for All Urban Consumers", "unit": "index", "frequency": "monthly", "category": "macro", "updated_at": "2026-07-01T04:12:33+00:00" },
  { "series_id": "INDEX_VIX", "name": "CBOE Volatility Index", "unit": "index", "frequency": "daily", "category": "index", "updated_at": "2026-07-09T22:05:11+00:00" }
]

get_macro_series(series_id, start?, end?, limit?, order?)

取某 series 的觀測時間序列,邊界含端點。MCP 端預設 order 為 desc(最新在前),limit=1 即拿最新值;畫圖或算移動平均改 order=asc。value 的單位不在回應裡,要回目錄查。

REST endpoint:GET /api/macro/series/{series_id}

觸及資料表:macro_series

背後 SQL

SELECT series_id, date, value
FROM macro_series
WHERE series_id = :series_id
  AND (CAST(:start AS date) IS NULL OR date >= :start)
  AND (CAST(:end AS date) IS NULL OR date <= :end)
ORDER BY date DESC
LIMIT :limit

回傳範例

[
  { "series_id": "INDEX_VIX", "date": "2026-07-09", "value": "14.62" },
  { "series_id": "INDEX_VIX", "date": "2026-07-08", "value": "15.10" }
]

get_etf_profile(ticker)

取單一 ETF 的層級 metadata(淨資產、費用率、配息率、是否槓桿等)。費用率與配息率是 0 至 1 小數。查無此 ETF 回 404。

REST endpoint:GET /api/etf/{ticker}/profile

觸及資料表:etf_profile

背後 SQL

SELECT ticker, net_assets, net_expense_ratio, portfolio_turnover,
       dividend_yield, inception_date, leveraged, updated_at
FROM etf_profile
WHERE ticker = :ticker

回傳範例

{
  "ticker": "SPY",
  "net_assets": 612000000000,
  "net_expense_ratio": "0.00095000",
  "portfolio_turnover": "0.02000000",
  "dividend_yield": "0.01230000",
  "inception_date": "1993-01-22",
  "leveraged": false,
  "updated_at": "2026-07-01T03:10:22+00:00"
}

list_etf_holdings(ticker, limit?, offset?)

列出某 ETF 的成分股與權重,依 weight 由大到小(top holdings 先),可分頁。weight 是 0 至 1 小數,null 排最後。

REST endpoint:GET /api/etf/{ticker}/holdings

觸及資料表:etf_holdings

背後 SQL

SELECT etf_ticker, holding_symbol, description, weight
FROM etf_holdings
WHERE etf_ticker = :etf_ticker
ORDER BY weight DESC NULLS LAST, holding_symbol
LIMIT :limit OFFSET :offset

回傳範例

[
  { "etf_ticker": "SPY", "holding_symbol": "AAPL", "description": "Apple Inc", "weight": "0.07120000" },
  { "etf_ticker": "SPY", "holding_symbol": "MSFT", "description": "Microsoft Corp", "weight": "0.06850000" }
]

list_etfs_holding_ticker(ticker, limit?, offset?)

反查:哪些 ETF 持有某個 symbol,依該 symbol 在各 ETF 的 weight 由大到小,可分頁。方向與 list_etf_holdings 相反,靠 etf_holdings 的 holding_symbol 過濾(非 etf_ticker),給被動資金流判讀。

REST endpoint:GET /api/etf/holders/{ticker}

觸及資料表:etf_holdings

背後 SQL

SELECT etf_ticker, holding_symbol, description, weight
FROM etf_holdings
WHERE holding_symbol = :holding_symbol
ORDER BY weight DESC NULLS LAST, etf_ticker
LIMIT :limit OFFSET :offset

回傳範例

[
  { "etf_ticker": "QQQ", "holding_symbol": "AAPL", "description": "Apple Inc", "weight": "0.08960000" },
  { "etf_ticker": "SPY", "holding_symbol": "AAPL", "description": "Apple Inc", "weight": "0.07120000" }
]

list_etf_sectors(ticker)

取某 ETF 的 GICS sector 權重,依權重由大到小,不分頁。weight 是 0 至 1 小數,約 11 個 sector。

REST endpoint:GET /api/etf/{ticker}/sectors

觸及資料表:etf_sector_weights

背後 SQL

SELECT etf_ticker, sector, weight
FROM etf_sector_weights
WHERE etf_ticker = :etf_ticker
ORDER BY weight DESC NULLS LAST, sector

回傳範例

[
  { "etf_ticker": "SPY", "sector": "Information Technology", "weight": "0.37600000" },
  { "etf_ticker": "SPY", "sector": "Financials", "weight": "0.13100000" }
]

get_market_movers(date?)

取某交易日的全市場漲幅榜、跌幅榜、成交最熱榜(各前 20)。不帶 date 自動取最新一天(先查 MAX(date) 再撈該日)。change_pct 是百分比數值(5.23 代表 +5.23%),非 0 至 1 小數。

REST endpoint:GET /api/market/movers

觸及資料表:market_movers

背後 SQL

-- 1) resolve latest day when date omitted
SELECT MAX(date) AS d FROM market_movers;

-- 2) fetch that day's three boards
SELECT category, rank, ticker, price, change_amount, change_pct,
       volume, updated_at
FROM market_movers
WHERE date = :d
ORDER BY category, rank

回傳範例

{
  "date": "2026-07-09",
  "last_updated": "2026-07-09T20:30:05+00:00",
  "gainers": [
    { "rank": 1, "ticker": "XYZ", "price": "12.44", "change_amount": "4.11", "change_pct": "49.34", "volume": 8123456 }
  ],
  "losers": [
    { "rank": 1, "ticker": "LMNO", "price": "6.71", "change_amount": "-3.28", "change_pct": "-32.83", "volume": 4200110 }
  ],
  "most_active": [
    { "rank": 1, "ticker": "TSLA", "price": "402.11", "change_amount": "6.20", "change_pct": "1.57", "volume": 128443090 }
  ]
}

get_ipo_calendar(from_date?, to_date?)

取 IPO 行事曆(近期與即將上市新股),依掛牌日由舊到新。兩個日期都省略時套今天起 90 天視窗;只帶一邊就只約束那一邊。price_range 為 null 代表尚未定價。

REST endpoint:GET /api/market/ipo-calendar

觸及資料表:ipo_calendar

背後 SQL

SELECT symbol, ipo_date, name, price_range_low, price_range_high,
       currency, exchange
FROM ipo_calendar
WHERE (CAST(:from_date AS date) IS NULL OR ipo_date >= :from_date)
  AND (CAST(:to_date AS date) IS NULL OR ipo_date <= :to_date)
ORDER BY ipo_date, symbol
LIMIT :limit

回傳範例

[
  { "symbol": "ACME", "ipo_date": "2026-07-15", "name": "Acme Robotics Inc", "price_range_low": "18.00", "price_range_high": "20.00", "currency": "USD", "exchange": "NASDAQ" },
  { "symbol": "BETA", "ipo_date": "2026-07-22", "name": "Beta Health Corp", "price_range_low": null, "price_range_high": null, "currency": "USD", "exchange": "NYSE" }
]

整合視圖與自由 SQL

Functions

get_analysis(ticker)

四面向紅綠燈分析入口:後端把倉儲原始數據整合成基本面、籌碼面、技術面、期權面各一句結論加 verdict 與綜合總評。資料缺的面向標 na 並從綜合分母剔除,永遠回 200。

REST endpoint:GET /api/analysis/{ticker}

觸及資料表:income_statementscompany_overviewinsider_tradesinstitutional_holdingsprices_dailyoptions_eod

背後 SQL

-- composite: the four-lens engine runs independent queries per lens, then merges verdicts
-- fundamental -> income_statements (revenue/EPS YoY) + company_overview (valuation/ROE)
-- ownership   -> insider_trades (6-month net buys/sells) + institutional_holdings (13F changes)
-- technical   -> prices_daily (self-computed MA50/MA200 + 52-week position)
-- options     -> options_eod (latest chain put/call volume ratio)
-- representative sub-query (technical lens moving averages):
WITH r AS (
  SELECT date, close,
    AVG(close) OVER (ORDER BY date ROWS 49 PRECEDING)  AS ma50,
    AVG(close) OVER (ORDER BY date ROWS 199 PRECEDING) AS ma200
  FROM prices_daily
  WHERE ticker = :t AND date >= CURRENT_DATE - INTERVAL '400 days'
)
SELECT date, close, ma50, ma200 FROM r ORDER BY date DESC LIMIT 1;

回傳範例

{
  "ticker": "AAPL",
  "overall_verdict": "bullish",
  "overall_summary": "AAPL bullish. Bullish lenses: Fundamentals, Ownership/Flows, Technical. (based on 4/4 lenses)",
  "lenses_scored": 4,
  "lenses": [
    {
      "key": "fundamental",
      "name": "Fundamentals",
      "verdict": "bullish",
      "summary": "Latest quarter revenue grew +39% YoY (strong growth); forward P/E 24.5x reflects market-priced growth.",
      "as_of": "2026-03-31",
      "signals": [
        { "label": "Revenue growth (YoY)", "value": "+39%", "verdict": "bullish" },
        { "label": "Valuation (forward P/E)", "value": "24.5x", "verdict": "neutral" }
      ]
    },
    {
      "key": "chips",
      "name": "Ownership/Flows",
      "verdict": "bullish",
      "summary": "Insiders buying, institutions net adding (13F lags ~90 days).",
      "as_of": "2026-05-20",
      "signals": [
        { "label": "Insider net buy/sell (6mo)", "value": "$12.3M (buy 5 / sell 2)", "verdict": "bullish" }
      ]
    },
    {
      "key": "technical",
      "name": "Technical",
      "verdict": "bullish",
      "summary": "Moving averages in bullish alignment; price at 70% of its 52-week range.",
      "as_of": "2026-07-09",
      "signals": [
        { "label": "Moving-average alignment", "value": "bullish alignment (close 189 / MA50 182 / MA200 178)", "verdict": "bullish" }
      ]
    },
    {
      "key": "options",
      "name": "Options",
      "verdict": "neutral",
      "summary": "Put/call volume ratio 0.85, balanced positioning.",
      "as_of": "2026-07-09",
      "signals": [
        { "label": "Put/call volume ratio", "value": "0.85", "verdict": "neutral" }
      ]
    }
  ]
}

get_objective_report(ticker, sections?, statements_limit?, insider_limit?, holders_limit?, filings_limit?, recent_price_bars?)

單一公司客觀數據包:一次打包估值、近 N 期三表、內部人、13F、價格摘要、期權摘要與近期 filing 章節目錄,純數據無任何解讀,供 agent 自行分析。可用 sections 只挑需要的塊省 token。

REST endpoint:GET /api/report/{ticker}

觸及資料表:companiescompany_overviewincome_statementsbalance_sheetscash_flow_statementsinsider_tradesinstitutional_holdingsinstitutionsprices_dailyoptions_eodfilingsfiling_sections

背後 SQL

-- composite: per-ticker objective data pack; each requested section calls its own
-- service and returns a {source, as_of, ...} envelope. Raw data, no interpretation.
-- company -> companies; overview -> company_overview
-- financials -> income_statements + balance_sheets + cash_flow_statements
-- insider -> insider_trades; institutional_13f -> institutional_holdings JOIN institutions
-- price_summary -> prices_daily (self-computed MA / 52-week); options_summary -> options_eod
-- filings -> filings + filing_sections (section titles only, no body text)
-- representative sub-query (latest-quarter 13F flow counts):
SELECT COUNT(DISTINCT filer_cik) AS total_holders,
       COUNT(*) FILTER (WHERE change_type = 'new')      AS new_pos,
       COUNT(*) FILTER (WHERE change_type = 'increase') AS increased,
       COALESCE(SUM(change_in_shares), 0)               AS net_share_change
FROM institutional_holdings
WHERE ticker = :t AND quarter_end = :q;

回傳範例

{
  "ticker": "AAPL",
  "generated_at": "2026-07-10T02:14:55+00:00",
  "company": { "ticker": "AAPL", "name": "Apple Inc.", "sector": "Technology", "exchange": "NASDAQ", "is_active": true },
  "overview": {
    "source": "company_overview",
    "as_of": "2026-07-09",
    "data": { "market_cap": 3450000000000, "pe_ratio": 32.1, "forward_pe": 24.5, "beta": 1.28, "week_52_high": 199.62, "week_52_low": 164.08, "analyst_target_price": 210.0 }
  },
  "financials": {
    "source": "sec_xbrl",
    "as_of": "2026-03-31",
    "income": [ { "period_end": "2026-03-31", "fiscal_period": "Q2", "revenue": 95400000000, "net_income": 25100000000 } ],
    "balance": [ { "period_end": "2026-03-31", "total_assets": 352000000000, "total_equity": 65000000000 } ],
    "cash_flow": [ { "period_end": "2026-03-31", "operating_cash_flow": 28600000000, "free_cash_flow": 26300000000 } ]
  },
  "insider": {
    "source": "sec_form4",
    "as_of": "2026-05-20",
    "trades": [ { "insider_name": "Cook Timothy D", "transaction_date": "2026-05-20", "transaction_code": "S", "shares": 511000, "total_value": 96803000 } ]
  },
  "institutional_13f": {
    "source": "sec_13f",
    "as_of": "2026-03-31",
    "counts": { "total_holders": 4820, "new_positions": 310, "increased": 1980, "decreased": 1750, "sold_out": 240, "net_share_change": 84000000 },
    "top_holders": [ { "filer_name": "Vanguard Group Inc", "shares": 1310000000, "market_value": 248900000000, "change_type": "increase" } ]
  },
  "price_summary": {
    "source": "prices_daily",
    "as_of": "2026-07-09",
    "summary": { "latest_close": 188.72, "change_pct": 1.29, "ma_50": 182.4, "ma_200": 178.1, "range_position_pct": 69.6 },
    "recent_daily": [ { "date": "2026-07-09", "open": 186.9, "high": 189.4, "low": 186.5, "close": 188.72, "volume": 48200000 } ]
  },
  "options_summary": {
    "source": "options_eod",
    "as_of": "2026-07-09",
    "summary": { "put_call_volume_ratio": 0.66, "put_call_oi_ratio": 0.73, "contract_count": 3120 },
    "expirations": [ { "expiration": "2026-07-18", "contract_count": 412 } ]
  },
  "filings": {
    "source": "sec_filing_sections",
    "as_of": "2026-05-02",
    "recent": [ { "accession": "0000320193-26-000042", "form_type": "10-Q", "filed_at": "2026-05-02T20:31:00+00:00", "section_count": 2 } ]
  }
}

execute_readonly_sql(query)Pro

跑一段唯讀 SELECT 並回 JSON(rows 加 meta),給需要彈性查詢的 agent 或分析用。三層防護:SELECT-only 解析、唯讀 DB role、5 秒 statement timeout,並自動補外層 LIMIT。需 Pro tier。

REST endpoint:直連 DB(不經 REST)

觸及資料表:companiesinstitutionsinstitution_filingsfilingsfiling_sectionsincome_statementsbalance_sheetscash_flow_statementsinsider_tradesinstitutional_holdingsprices_dailyprices_hourlycompany_overviewoptions_eoddividendssplitsearnings_calendaretf_profileetf_holdingsmacro_seriesmacro_series_metamarket_moversipo_calendarearnings_call_transcriptsearnings_call_segmentsnews_articlesnews_ticker_sentiment

背後 SQL

-- three guardrails:
-- 1) SQL parsing: sqlparse admits a single SELECT / WITH ... SELECT only,
--    rejecting multi-statement input and sensitive functions (pg_sleep, copy, lo_import).
-- 2) DB role: the connection uses a SELECT-only readonly role, so DML/DDL fails
--    even if parsing were bypassed.
-- 3) statement_timeout = 5000ms per query.
-- auto LIMIT: your SQL is wrapped as SELECT * FROM (<your SQL>) _capped LIMIT n;
-- a missing top-level LIMIT gets 1000, an explicit N is capped at 10000;
-- output larger than 100KB is truncated.
-- queryable tables: see the table list on this card (also via describe_table()).

回傳範例

{
  "row_count": 2,
  "executed_query": "SELECT * FROM (\nSELECT ticker, name FROM companies WHERE sector = 'Technology'\n) AS _capped\nLIMIT 1000",
  "rows": [
    { "ticker": "AAPL", "name": "Apple Inc." },
    { "ticker": "MSFT", "name": "Microsoft Corporation" }
  ]
}

describe_table(table_name?)Pro

自省 readonly SQL 的 schema:不帶參數列出所有可查 table,帶 table 名列出該表欄位、型別與是否可空,讓你在下 execute_readonly_sql 前先確認欄位名。需 Pro tier。

REST endpoint:直連 DB(不經 REST)

背後 SQL

-- direct readonly-DB introspection (bind params, public schema only):
-- no argument -> list all BASE TABLEs in the public schema:
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name;
-- with table_name -> list that table's columns, types and nullability:
SELECT column_name, data_type, is_nullable FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position;

回傳範例

// mode A: describe_table() -> list all queryable tables
{
  "tables": [
    "balance_sheets",
    "cash_flow_statements",
    "companies",
    "company_overview",
    "income_statements",
    "insider_trades",
    "institutional_holdings",
    "options_eod",
    "prices_daily"
  ]
}

// mode B: describe_table("companies") -> that table's columns
{
  "table": "companies",
  "columns": [
    { "column_name": "ticker", "data_type": "text", "is_nullable": "NO" },
    { "column_name": "cik", "data_type": "text", "is_nullable": "NO" },
    { "column_name": "name", "data_type": "text", "is_nullable": "YES" },
    { "column_name": "sector", "data_type": "text", "is_nullable": "YES" }
  ]
}