Menü
Use when pulling data from WRDS databases, merging financial datasets via linking tables, validating panel structure, or constructing financial variables for finance and real estate research.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Basierend auf der SOC-Berufsklassifikation
| name | wrds-data-pull |
| description | Use when pulling data from WRDS databases, merging financial datasets via linking tables, validating panel structure, or constructing financial variables for finance and real estate research. |
| triggers | ["WRDS","Compustat","CRSP","IBES","Thomson Reuters","BoardEx","ISS","CoreLogic","ZTRAX","CoStar","NCREIF","RavenPack","SEC EDGAR","CUSIP","GVKEY","PERMNO","CCM","ICLINK","financial data extraction","panel data"] |
| role | specialist |
| scope | implementation |
| output-format | code |
Automated WRDS data extraction with pre-built query templates, merge logic, validation, and variable documentation for finance and real estate research.
This skill packages 20+ years of WRDS research patterns into reusable components: parameterized SQL queries for major databases, step-by-step merge sequences with proper key handling, automated panel diagnostics, and standard financial variable construction with citations.
Saves 20-40 hours per project by eliminating repetitive query writing, merge debugging, and data validation setup.
Copy the production-tested pipeline from an existing project:
# Copy existing scripts to your project
import shutil
src = "path/to/---Overleaf_Template/Code/Python/1---wrds-direct-connection"
dst = "your_project/data_pipeline"
shutil.copytree(src, dst)
# Configure credentials
# Add to .env file: WRDS_USERNAME=your_username
# Run full pipeline
python data_pipeline/5---main_orchestrator.py
The pipeline includes:
1---config_wrds_data.py: Configuration with 100+ Compustat/CRSP variables2---pull_compustat_data.py: Annual and quarterly pulls with caching3---pull_crsp_data.py: Monthly data aggregated to quarterly4---merge_and_process_data.py: CUSIP-based linking (99% PERMNO coverage)5---main_orchestrator.py: Coordinated executionFor custom workflows, use the modular components:
from wrds_data_pull import query_templates, merge_utils
# 1. Pull data with template
comp_query = query_templates.compustat_quarterly(
variables=["gvkey", "datadate", "atq", "ltq"],
start_date="2020-01-01",
end_date="2023-12-31"
)
# 2. Execute query
import wrds
conn = wrds.Connection(wrds_username="username")
comp_df = conn.raw_sql(comp_query)
# 3. Validate panel
from wrds_data_pull.panel_diagnostics import validate_panel
diag = validate_panel(comp_df, unit_id="gvkey", time_id="datadate")
print(f"Panel type: {diag['panel_type']}, Duplicates: {diag['n_duplicates']}")
Compustat + CRSP (most common):
IBES + Thomson Reuters:
Real estate databases:
Alternative data:
All query templates are parameterized:
from wrds_data_pull.query_templates import compustat_quarterly
# Basic pull
query = compustat_quarterly(
variables=["gvkey", "datadate", "atq", "niq"],
start_date="2020-01-01",
end_date="2023-12-31"
)
# With filters
query = compustat_quarterly(
variables=["gvkey", "datadate", "atq", "niq"],
start_date="2020-01-01",
end_date="2023-12-31",
gvkey_list=["001045", "001078"], # Filter to specific firms
filters={"indfmt": "INDL", "datafmt": "STD"} # Standard filters
)
Compustat-CRSP via CCM linking table:
from wrds_data_pull.merge_utils import merge_compustat_crsp_via_ccm
merged_df = merge_compustat_crsp_via_ccm(
compustat_df=comp_df,
crsp_df=crsp_df,
link_type="LC" # Primary links only
)
CUSIP-based linking (alternative, higher coverage):
from wrds_data_pull.merge_utils import merge_via_cusip
merged_df = merge_via_cusip(
compustat_df=comp_df, # Must have 'cusip' column
crsp_df=crsp_df, # Must have 'cusip', 'year', 'quarter'
merge_on=["cusip", "year", "quarter"]
)
Run diagnostics after every pull:
python scripts/validate_panel.py \
--input merged_data.parquet \
--unit_id gvkey \
--time_id year_quarter
Checks:
Standard formulas with academic citations:
from wrds_data_pull.variable_builders import FinancialRatios
ratios = FinancialRatios(df)
df['tobins_q'] = ratios.tobins_q() # (ME + BV_debt) / BV_assets
df['leverage'] = ratios.leverage() # Total debt / Total assets
df['roa'] = ratios.roa() # Net income / Total assets
See variable-construction.md for complete formulas.
Configure WRDS connection, validate credentials, test database access.
python scripts/setup_wrds.py --username your_username
Panel diagnostics (balance, duplicates, coverage, known issues).
python scripts/validate_panel.py \
--input data.parquet \
--unit_id gvkey \
--time_id year_quarter \
--output diagnostics.txt
Variable-level missing data report with LaTeX output for papers.
python scripts/coverage_report.py \
--input data.parquet \
--output coverage_table.tex
Full pipeline from existing codebase (tested on 35K observations, 280+ variables):
# Use existing pipeline
import importlib.util
# Load orchestrator
spec = importlib.util.spec_from_file_location(
"orchestrator",
"Code/Python/1---wrds-direct-connection/5---main_orchestrator.py"
)
orch = importlib.util.module_from_spec(spec)
spec.loader.exec_module(orch)
# Run full pipeline
final_df = orch.main() # Returns merged Compustat + CRSP data
Pipeline includes:
# Pull specific variables
from wrds_data_pull.query_templates import compustat_quarterly
import wrds
conn = wrds.Connection(wrds_username="username")
query = compustat_quarterly(
variables=["gvkey", "datadate", "atq", "ltq", "seqq", "niq"],
start_date="2015-01-01",
end_date="2023-12-31"
)
df = conn.raw_sql(query)
# Construct ratios
from wrds_data_pull.variable_builders import construct_financial_ratios
df = construct_financial_ratios(df, ratios=["leverage", "roa"])
# Validate
from wrds_data_pull.panel_diagnostics import validate_panel
diag = validate_panel(df, unit_id="gvkey", time_id="datadate")
print(f"Coverage: {diag['coverage_summary']}")
# Pull NCREIF property-level returns
from wrds_data_pull.query_templates import ncreif_property_returns
query = ncreif_property_returns(
start_date="2010-01-01",
end_date="2023-12-31",
property_types=["Office", "Retail", "Apartment"]
)
# Execute and validate
import wrds
conn = wrds.Connection(wrds_username="username")
ncreif_df = conn.raw_sql(query)
# Merge with CoreLogic (requires separate access)
# See real-estate-databases.md for CoreLogic patterns
datafmt='STD' for unrestated (standardized) datadatacqtr for calendar quarters, datafqtr for fiscalseqq < 0 (common for distressed firms)msedelist table for complete return historyshrcd IN (10,11) for common stock onlydatadate BETWEEN LINKDT AND LINKENDDTLINKTYPE='LC')See known-data-issues.md for comprehensive documentation.
# Test WRDS access
import wrds
conn = wrds.Connection(wrds_username="username")
print(conn.list_libraries()) # Should show: comp, crsp, ibes, taqm, etc.
conn.close()
# Check if variable exists in table
conn = wrds.Connection(wrds_username="username")
fields = conn.list_table_fields("comp", "fundq")
print("atq" in [f.lower() for f in fields]) # True if exists
# Diagnose merge issues
from wrds_data_pull.merge_utils import diagnose_merge
diagnosis = diagnose_merge(
left_df=comp_df,
right_df=crsp_df,
merge_keys=["cusip", "year", "quarter"]
)
print(f"Match rate: {diagnosis['match_rate']:.1%}")
print(f"Left-only: {diagnosis['left_only_count']} observations")
print(f"Right-only: {diagnosis['right_only_count']} observations")
The assets/wrds_data_pull/ directory contains a standalone Python package with modular components:
query_templates.py: Parameterized SQL for all major WRDS databasesmerge_utils.py: Merge functions with date validation and diagnosticspanel_diagnostics.py: Balance checks, duplicate detection, coverage statsvariable_builders.py: Financial ratios with formulas and citations__init__.py: Convenient importspip install wrds pandas numpy scipy
Add to Python path:
import sys
sys.path.insert(0, "/path/to/wrds-data-pull/assets")
from wrds_data_pull import query_templates, merge_utils
Or copy assets/wrds_data_pull/ directly into your project.
Multi-phase, subagent-driven deep audit and remediation of codebase guidance (root agent instructions, docs, module context files, skills/agents references, task routing). Phase 1 subagents write verified findings only; Phase 2 subagents verify and fix guidance. Use when the user asks to audit, refresh, or fix agent context, CLAUDE.md or AGENTS.md drift, stale docs, contradictory rules, or run a context-guidance deep dive.
Use when the user asks to create, draft, save, revise, or improve a Codex Goal, GOAL.md, goal file, persistent objective, completion contract, or long-running Codex work objective. This skill must be used before inventing or saving any goal for Codex.
Use when the user wants to create, brainstorm, draft, or post social media content for X (Twitter), LinkedIn, or Instagram. This includes generating post ideas, writing captions or threads, adapting content across platforms, planning a posting cadence, or navigating to a platform's posting interface via browser tools. Trigger whenever the user mentions social media, posting, tweets, LinkedIn posts, Instagram captions, content calendars, thought leadership posts, or sharing research/business updates online — even if they just say something like 'I should post about this' or 'help me share this.'
Build Discounted Cash Flow (DCF) valuation models for commercial real estate (CRE). Calculate NOI-based cash flows, levered and unlevered IRR, equity multiple, DSCR, debt yield, exit cap rate reversion, and sensitivity analysis. Use for office, retail, industrial, multifamily, mixed-use, and hotel properties. Trigger on: CRE valuation, property DCF, NOI projection, cap rate analysis, IRR analysis, real estate investment return, acquisition underwriting, hold-period analysis, CRE sensitivity table.
Build discounted cash flow (DCF) valuation models in Excel specifically for commercial real estate (CRE). Use when creating CRE acquisition underwriting models, property-level DCF workbooks, NOI projections, rent roll buildouts, debt schedules, or IRR/equity-multiple return analyses in Excel or ExcelJS. Covers all CRE property types: office, retail, industrial, multifamily, mixed-use, hotel. Trigger on: 'CRE excel model', 'build underwriting model', 'property DCF excel', 'NOI projection spreadsheet', 'rent roll model', 'IRR excel', 'acquisition model', 'CRE waterfall', 'cap rate model'.
Create new skills, modify and improve existing skills, and measure skill performance. Use when users want to create a skill from scratch, update or optimize an existing skill, run evals to test a skill, benchmark skill performance with variance analysis, or optimize a skill's description for better triggering accuracy.