zjunlp SEC 10-K Company Analysis
Database Schema
The SQLite database has 5 tables with these exact column names (wrong column names are a common failure):
companies (primary key: cik)
Key columns: cik, name, sic, sic_description, entity_type, category, fiscal_year_end,
state_of_incorporation, phone, description, website, former_names, owner_org
company_addresses
Columns: cik, address_type ("business"/"mailing"), street1, city, state_or_country, zip_code
company_tickers
Columns: cik, ticker, exchange
filings — use column form (NOT form_type)
Key columns: cik, accession_number, filing_date, report_date, form, core_type, size, is_xbrl
financial_facts — use column fact_name (NOT tag), form_type (NOT form)
Key columns: cik, fact_name, fact_value, unit, fact_category, fiscal_year, fiscal_period,
end_date, accession_number, form_type, filed_date, dimension_segment, dimension_geography
fiscal_period values: FY (annual), Q1, Q2, Q3, Q4
fact_category values: us-gaap, dei, ifrs-full
Analysis Workflow
Step 1: Database Discovery
-- Always start by understanding structure
get_database_info()
describe_table("companies")
describe_table("financial_facts")
Step 2: Company Basics
SELECT * FROM companies WHERE cik = '<CIK>'
SELECT * FROM company_tickers WHERE cik = '<CIK>'
SELECT * FROM company_addresses WHERE cik = '<CIK>'
Step 3: Filing History
-- Note: column is `form`, not `form_type`
SELECT form, filing_date, report_date, accession_number
FROM filings WHERE cik = '<CIK>'
ORDER BY filing_date DESC LIMIT 20
-- Count by form type
SELECT form, COUNT(*) as count FROM filings
WHERE cik = '<CIK>' GROUP BY form ORDER BY count DESC
Step 4: Discover Available Financial Metrics
-- Get all fact names for this company (always LIMIT to avoid huge result)
SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>' ORDER BY fact_name LIMIT 100
Step 5: Core Financial Metrics (Annual Data)
-- Get key annual metrics — filter by form_type='10-K' for clean annual data
SELECT fact_name, fact_value, unit, fiscal_year, fiscal_period, end_date
FROM financial_facts
WHERE cik = '<CIK>'
AND form_type = '10-K'
AND fact_name IN (
'Assets', 'Liabilities', 'StockholdersEquity',
'NetIncomeLoss', 'OperatingIncomeLoss',
'CashAndCashEquivalentsAtCarryingValue',
'EarningsPerShareBasic', 'EarningsPerShareDiluted',
'EntityCommonStockSharesOutstanding'
)
ORDER BY end_date DESC LIMIT 50
Step 6: Revenue Discovery (Critical — many companies use non-standard names)
Start with these candidates; if empty, search with LIKE:
-- Try common revenue names
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN (
'Revenues',
'RevenueFromContractWithCustomerExcludingAssessedTax',
'RevenueFromContractWithCustomerIncludingAssessedTax',
'SalesRevenueNet',
'RevenuesNetOfInterestExpense'
)
ORDER BY end_date DESC LIMIT 20
-- If empty, discover the actual revenue metric name
SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>'
AND (fact_name LIKE '%Revenue%' OR fact_name LIKE '%Sales%')
ORDER BY fact_name LIMIT 20
Step 7: Cash Flow Analysis
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN (
'NetCashProvidedByUsedInOperatingActivities',
'NetCashProvidedByUsedInInvestingActivities',
'NetCashProvidedByUsedInFinancingActivities',
'CapitalExpendituresIncurredButNotYetPaid',
'PaymentsToAcquirePropertyPlantAndEquipment'
)
ORDER BY end_date DESC LIMIT 15
Step 8: Debt & Capital Structure
SELECT DISTINCT fact_name FROM financial_facts
WHERE cik = '<CIK>' AND fact_name LIKE '%Debt%' LIMIT 30
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts
WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN ('LongTermDebt', 'DebtCurrent', 'LongTermDebtCurrent',
'DebtInstrumentCarryingAmount', 'LongTermDebtNoncurrent')
ORDER BY end_date DESC LIMIT 20
Step 9: Industry-Specific Metrics
After identifying the company's SIC code, query relevant industry metrics:
REIT / Real Estate (SIC 6500-6799):
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts WHERE cik = '<CIK>'
AND (fact_name LIKE '%RealEstate%' OR fact_name LIKE '%NumberOfReal%'
OR fact_name LIKE '%NumberOfUnit%' OR fact_name LIKE '%FundsFrom%'
OR fact_name LIKE '%Rental%')
ORDER BY end_date DESC LIMIT 30
Pharmaceutical / Life Sciences (SIC 2830-2836):
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN ('ResearchAndDevelopmentExpense',
'SellingGeneralAndAdministrativeExpense',
'AllocatedShareBasedCompensationExpense',
'CommonStockDividendsPerShareDeclared')
ORDER BY end_date DESC LIMIT 20
Retail / Consumer (SIC 5200-5999):
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN ('CostOfGoodsSold', 'GrossProfit', 'InventoryNet',
'AdvertisingExpense', 'NumberOfStores')
ORDER BY end_date DESC LIMIT 20
Utilities (SIC 4900-4999):
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN ('PublicUtilitiesPropertyPlantAndEquipmentNet',
'RegulatoryAssetsCurrent', 'RegulatoryAssetsNoncurrent')
ORDER BY end_date DESC LIMIT 20
Financial Services / Banks (SIC 6000-6499):
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts WHERE cik = '<CIK>' AND form_type = '10-K'
AND (fact_name LIKE '%Interest%' OR fact_name LIKE '%Loan%'
OR fact_name LIKE '%Deposit%')
ORDER BY end_date DESC LIMIT 30
Technology / Manufacturing:
SELECT fact_name, fact_value, unit, fiscal_year, end_date
FROM financial_facts WHERE cik = '<CIK>' AND form_type = '10-K'
AND fact_name IN ('ResearchAndDevelopmentExpense', 'GoodwillAndIntangible',
'PropertyPlantAndEquipmentNet', 'InventoryNet')
ORDER BY end_date DESC LIMIT 20
Common Pitfalls
| Mistake | Correct Approach |
|---|---|
SELECT DISTINCT tag FROM financial_facts |
SELECT DISTINCT fact_name FROM financial_facts |
GROUP BY form_type FROM filings |
GROUP BY form FROM filings (filings uses form) |
SELECT * FROM filings (no LIMIT) |
Always add LIMIT to avoid huge results |
Assuming Revenues metric exists |
Try multiple revenue names; use LIKE fallback |
Missing parentheses: WHERE fact_name LIKE '%A%' OR fact_name LIKE '%B%' |
Use: WHERE (fact_name LIKE '%A%' OR fact_name LIKE '%B%') |
Only checking fiscal_period='FY' for revenue |
Some companies report differently; also check form_type='10-K' |
Output Structure
Always produce a comprehensive final report with a "FINISH:" prefix. Include:
FINISH:
## Company Overview
- Name, CIK, Ticker (Exchange), SIC code and description
- Entity type, Filer category, State of incorporation
- Fiscal year end, Address, Phone
- Former names (if any)
## Financial Performance (Most Recent FY + 3-year trend)
- Revenue: [values by year]
- Gross Profit / Operating Income
- Net Income
- EPS (Basic and Diluted)
## Balance Sheet (Most Recent)
- Total Assets
- Total Liabilities
- Stockholders' Equity
- Cash & Cash Equivalents
- Long-term Debt
## Cash Flow (Most Recent Annual)
- Operating / Investing / Financing
## Industry-Specific Metrics
[Metrics relevant to company's sector]
## SEC Filing Activity
- Total filings, key form types and counts
- Most recent 10-K date
## Key Observations
- Trends, notable changes, financial health indicators
Multi-Year Trend Query Pattern
To get clean multi-year comparisons from 10-K filings:
SELECT
end_date,
MAX(CASE WHEN fact_name = 'Assets' THEN fact_value END) AS Assets,
MAX(CASE WHEN fact_name = 'NetIncomeLoss' THEN fact_value END) AS NetIncome,
MAX(CASE WHEN fact_name = 'StockholdersEquity' THEN fact_value END) AS Equity
FROM financial_facts
WHERE cik = '<CIK>'
AND form_type = '10-K'
AND fiscal_period = 'FY'
AND fact_name IN ('Assets', 'NetIncomeLoss', 'StockholdersEquity')
GROUP BY end_date
ORDER BY end_date DESC
LIMIT 10