Clean & Merge Wind, CSMAR, and CNKI Exports into an Empirical Panel

A practitioner guide to resolving spreadsheet truncation, multi-row unit headers, point-in-time timestamp alignment, and sample attrition when assembling econometric panels from Chinese financial databases.

HumanPen Team

· 7 min read

Cleaning and Merging Wind, CSMAR, and CNKI Exports into an Empirical Panel

To clean and merge raw Excel or CSV files exported from Wind, CSMAR, or CNKI into an econometric panel, export unformatted tables from institutional terminals first, force entity identifiers to string format to preserve leading zeros, strip multi-row unit headers, and merge financial statements using backward point-in-time matching. Validate key uniqueness with one-to-one integrity checks before estimating models or trimming ratio outliers.

Financial and economic researchers frequently encounter corrupted panel structures when combining accounting statements, market pricing series, and corporate governance metrics. Discrepancies between calendar fiscal year-ends and actual release dates, altered numeric identifiers, and merged consolidated rows create silent sample attrition or artificial statistical significance if unaddressed during pre-processing.

Systematic data preparation requires converting manual point-and-click spreadsheet routines into reproducible Python or R pipeline scripts. When handling multilingual tables or complex workbooks, researchers often reference how to translate Excel spreadsheets while maintaining formula references to audit multi-sheet accounting architectures without accidental calculation errors.

Five Classic Spreadsheet Traps in Chinese Financial Databases

Raw exports from commercial financial databases such as Wind, CSMAR (GTA), and CNKI statistical yearbooks are designed for human viewing rather than direct machine ingestion. Opening these files directly in spreadsheet software triggers silent type coercions and parsing failures across five recurring dimensions.

Trap CategoryTypical SymptomUnderlying Root CauseRobust Scripted Fix
Dropped leading zerosTicker `000001` becomes integer `1`Automated numeric coercion on text digitsPass `dtype={'Stkcd': str}` to parser
Multi-row unit headersHeader rows pollute numeric columnsHuman-oriented units like `in RMB millions`Set `header=[0, 1]` or skip title rows
Consolidated vs parent mixDouble-counted annual revenue rowsBoth statement tiers exported togetherFilter explicitly on statement code `A`
Unfiltered distressed firmsDistorted capital structure ratiosInadvertent inclusion of `ST` or banksExclude CSRC sector `J` and special treatment
Duplicate firm-year keysRow count multiplies exponentiallyMerging across unmatched reporting periodsEnforce `validate='one_to_one'` during join
  • Force string datatypes on all security codes, administrative division codes, and industry identifiers using `pandas.read_excel` or `read_csv` with an explicit `dtype` mapping dictionary.
  • Strip preamble title lines, metadata banners, and unit notes by specifying `skiprows` or multi-level headers so that data frames contain only clean numeric arrays and variable names.
  • Separate consolidated financial statements from parent-company accounts by filtering on standard database flags such as CSMAR statement type `A` rather than pooling them.
  • Screen out special treatment firms (`ST`, `*ST`, `PT`) and financial intermediaries (CSRC finance classification code `J`) in accordance with your empirical design specification.
  • Validate merge cardinality using `DataFrame.merge` with `validate='one_to_one'` or `validate='many_to_one'` to halt script execution instantly whenever key duplication occurs.

Among these issues, ticker truncation causes the most catastrophic downstream failures. When Shenzhen ticker `000001` is coerced into integer `1` and merged against another table formatted as a six-digit string, rows are either silently dropped during inner joins or incorrectly matched against index benchmarks, distorting cross-sectional regressions.

Point-in-Time Alignment and Outlier Winsorization

A frequent methodological flaw in empirical corporate finance is pairing annual accounting metrics with market equity prices measured on December 31 of the same fiscal year. Because annual reports in China are filed between January and April of the following calendar year, matching balance sheet items on fiscal-year-end dates introduces look-ahead bias, conditioning market variables on information not yet accessible to investors.

Point-in-time matching with `pandas.merge_asof(..., direction='backward')` aligns each market observation with the most recent financial statement published on or before the trade date. Researchers can audit this temporal consistency using the empirical methods and results revision checklist to ensure that disclosure timeline assumptions withstand methodological referee scrutiny.

Beyond temporal alignment, raw financial ratios calculated from merged tables often contain extreme distributional distortions caused by near-zero denominators, distressed leverage figures, or transitional accounting adjustments. Trimming or bounding these variables at the 1st and 99th percentiles using `scipy.stats.mstats.winsorize` prevents isolated data anomalies from exerting undue statistical influence on ordinary least squares coefficients.

  • Calculate accounting ratios and interaction terms prior to distribution trimming, rather than winsorizing raw numerator and denominator components independently.
  • Group observations by calendar year or two-digit industry classification prior to winsorization when industry-specific capital structures deviate substantially.
  • Verify that market price data such as daily stock returns and trading volume are adjusted for cash dividends, rights offerings, and stock splits using reinvested total return series.
  • Inspect distribution moments including skewness, kurtosis, and minimum-maximum values before and after trimming to document exact cutoff thresholds in your methodology appendix.

Constructing an Explicit Sample Selection Waterfall

High-impact peer-reviewed journals require complete transparency regarding how an initial pool of raw database downloads is reduced to the final regression estimation sample. Omitting intermediate attrition steps obscures whether statistical significance arises from genuine economic phenomena or survivorship bias induced by selective filtering.

Filtering StageApplied Exclusion CriteriaRemaining ObservationsObservations Dropped
Step 1Initial raw CSMAR/Wind download (A-share listed firms 2012–2024)48,2500
Step 2Exclude financial sector firms (CSRC 2012 sector code `J`)46,9101,340
Step 3Exclude firm-years with special treatment status (`ST`, `*ST`, `PT`)44,1202,790
Step 4Exclude firm-years with missing key covariates (R&D, Leverage, Size)36,4807,640
Step 5Exclude firms with leverage ratio > 1 or negative net equity35,920560
Step 6Final unbalanced panel estimation sample35,9200

Embedding automated row-count assertions into your data pipeline ensures that every filtering stage outputs exact observation tallies to a log file. If database vendors update retrospective restatements or alter underlying classifications, researchers can immediately pinpoint where discrepancies emerge without re-running manual spreadsheet filters.

End-to-End Panel Processing with HumanPen Start Project

Processing large empirical archives spanning millions of observations across disparate database exports often strains local spreadsheet tools, leading to application crashes or corrupted encoding. Researchers who export raw tables from institutional Wind, CSMAR, or CNKI terminals can upload their compressed archives directly to HumanPen Start Project for reproducible scripted cleaning and baseline econometric estimation.

Attach your data and papers, describe what you need, approve the plan before work starts, and get the reviewed report, charts, and code ready to preview online or download. The platform supports Excel, CSV, Word, PDF, and zip archives up to 2 GB, executing all data transformation and econometric routines in an isolated Python and R sandbox environment. Whether your project requires translating legacy Stata and SPSS estimation do-files into Python pipelines or running two-way fixed effects models, each execution is billed in real time by actual usage and retained securely for six months with full anytime deletion controls.

In accordance with academic integrity standards across universities and peer-reviewed journals, generated codebases, waterfall attrition tables, and baseline regression deliverables serve as a transparent methodological reference scaffold. Researchers and empirical coauthors must verify the underlying variable definitions, inspect the diagnostic summary logs against institutional terminal documentation, and disclose analytical assistance in compliance with their target journal policies. Similar reproducible panel workflows, complete with full code artifacts and diagnostic summaries, can be examined on the public example projects hosted on HumanPen Start Project.

Frequently Asked Questions

How should researchers resolve Chinese character encoding errors like `UnicodeDecodeError` when loading exported CSV files? Chinese financial exports from Wind and CSMAR frequently use `GBK` or `GB18030` character encoding rather than standard `UTF-8`. When reading these files with `pandas.read_csv`, pass `encoding='gb18030'` explicitly. This handles rare Chinese corporate name characters and prevents parsing crashes without altering numeric entries or column structures.

Why does `DataFrame.merge` raise a `MergeError` when validating a one-to-one join on firm-year identifiers? A `MergeError` indicates that at least one of your tables contains duplicate values for your merging keys (`Stkcd` and `Year`). In Chinese databases, duplicates typically stem from unseparated semi-annual and annual interim reports, multiple currency classes (A-shares and B-shares), or combined parent-company and consolidated statements. Inspect duplicate rows using `df[df.duplicated(subset=['Stkcd', 'Year'], keep=False)]` and apply explicit statement filters before re-merging.

Can analytical tools connect directly to institutional Wind or CNKI database accounts? No. In compliance with commercial licensing agreements and institutional network gateways, third-party platforms do not access terminal login credentials. Researchers must query and export the relevant raw tables from their licensed university or institutional terminals first, and then upload the resulting Excel, CSV, or compressed zip files (up to 2 GB) for reproducible automated processing.

Should accounting variables be winsorized across the full pooled sample or by industry and year? Standard corporate finance literature predominantly winsorizes continuous financial ratios at the pooled sample level across all firm-years, ensuring consistent percentile thresholds across the panel. However, when examining structural ratios that exhibit pronounced industry divergence (such as capital intensity in manufacturing versus software services), researchers often report industry-by-year winsorized specifications as an essential robustness check.

Sources

KEEP READING