version 15.0
clear all
set more off
set type double
set linesize 160
capture log close _all
* Edit only this working-directory line if the folder is moved.
cd "C:/Users/cattom/Documents/econometrics/Week8"
log using "week8_stata.log", text replace
display "Week 8: residual degrees of freedom and RESSET-CSMAR merge"
display "All financial values are preserved in their original source scale."

tempname audit dict
postfile `audit' str60 metric double value using "processing_audit.dta", replace
postfile `dict' str32 variable str140 description str32 source_unit str18 source using "variable_dictionary.dta", replace

* 1. RESSET: read data below its Chinese header, retaining numeric precision.
import excel using "data/FININDRATIO_OLD_5938AD12016_(锐思).xls", sheet("FININDRATIO_OLD") cellrange(A2) clear
rename (A B C D E F G H I J) (Comcd Lcomnm A_Stkcd B_Stkcd H_Stkcd Lstflg Enddt Reporttype ROA ROE)
post `audit' ("resset_input_rows") (_N)
generate long resset_excel_row = _n + 1
generate str6 stkcd = strtrim(A_Stkcd)
assert regexm(stkcd,"^[0-9]+$") & strlen(stkcd) <= 6
generate byte code_format_exception = strlen(stkcd) != 6
assert stkcd == "0991" & inlist(year(Enddt),1998,1999) if code_format_exception
quietly count if code_format_exception
post `audit' ("resset_code_format_exceptions") (r(N))
assert Lstflg == "A"
assert missing(B_Stkcd) & missing(H_Stkcd)
assert Reporttype == "Q4"
assert month(Enddt) == 12 & day(Enddt) == 31
generate int year = year(Enddt)
assert !missing(stkcd,year)
isid stkcd year
post `audit' ("resset_duplicate_key_excess") (0)
quietly summarize year
post `audit' ("resset_min_year") (r(min))
post `audit' ("resset_max_year") (r(max))
egen byte firm_tag = tag(stkcd)
quietly count if firm_tag
post `audit' ("resset_distinct_codes") (r(N))
drop firm_tag
foreach v in ROA ROE {
    quietly count if missing(`v')
    post `audit' ("resset_missing_`v'") (r(N))
}
format Enddt %tdCCYY-NN-DD
format ROA ROE %24.17g
label variable stkcd "Source security code as text; normally six digits"
label variable code_format_exception "Source code has nonstandard length; preserved without guessing"
label variable year "Report year, not announcement year"
label variable ROA "资产净利率；原表标注(%)；原值未换算"
label variable ROE "净资产收益率(摊薄)；原表标注(%)；原值未换算"
label variable Enddt "RESSET报告截止日期"
label variable resset_excel_row "Row number in original RESSET worksheet"
label variable Lcomnm "源文件中的最新公司全称，非逐年历史名称"
post `dict' ("stkcd") ("证券代码文本；通常六位；原始0991的两条记录保留原样") ("文本标识") ("derived")
post `dict' ("code_format_exception") ("源代码非六位标记；0991两条记录未推测补零") ("0/1") ("derived")
post `dict' ("year") ("报告截止日期的年份，不是公告年份") ("年") ("derived")
post `dict' ("ROA") ("资产净利率；保留原值，不乘除100，经济尺度需原库数据字典确认") ("源表标注%；原值") ("RESSET")
post `dict' ("ROE") ("净资产收益率(摊薄)；保留原值，不乘除100，经济尺度待核") ("源表标注%；原值") ("RESSET")
post `dict' ("Comcd") ("上市公司代码") ("文本标识") ("RESSET")
post `dict' ("Lcomnm") ("最新公司全称，不能直接视为当年公司名称") ("文本") ("RESSET")
post `dict' ("A_Stkcd") ("A股股票代码；原始列") ("文本标识") ("RESSET")
post `dict' ("B_Stkcd") ("B股股票代码；本文件全空") ("文本标识") ("RESSET")
post `dict' ("H_Stkcd") ("H股股票代码；本文件全空") ("文本标识") ("RESSET")
post `dict' ("Lstflg") ("源文件上市标识；全为A，不额外推断历史上市状态") ("原始编码") ("RESSET")
post `dict' ("Enddt") ("RESSET报告截止日期") ("日期") ("RESSET")
post `dict' ("Reporttype") ("报表类型；本文件全为Q4且日期均为12月31日") ("原始编码") ("RESSET")
post `dict' ("resset_excel_row") ("RESSET原始工作表行号") ("行") ("provenance")
compress
sort stkcd year
save "resset_financial_all_years.dta", replace

* 2. CSMAR: retain every original governance field; append the two periods.
tempfile gov_first
foreach period in "1999-2012" "2013-2014" {
    import excel using "data/CG_Ybasic`period'.xls", sheet("CG_Ybasic") firstrow allstring clear
    assert Stkcd[1] == "证券代码"
    assert Stkcd[2] == "没有单位"
    foreach v of varlist _all {
        local description = `v'[1]
        local unit = `v'[2]
        label variable `v' "`description'"
        if "`period'" == "1999-2012" & "`v'" != "Stkcd" {
            post `dict' ("`v'") ("`description'") ("`unit'") ("CSMAR")
        }
    }
    drop in 1/2
    generate long csmar_excel_row = _n + 3
    post `audit' ("csmar_`period'_input_rows") (_N)
    rename Stkcd stkcd
    replace stkcd = strtrim(stkcd)
    assert regexm(stkcd,"^[0-9][0-9][0-9][0-9][0-9][0-9]$")
    generate double csmar_date = daily(Reptdt,"YMD")
    generate double csmar_announcement = daily(Annodt,"YMD")
    assert !missing(csmar_date)
    assert !missing(csmar_announcement) if strtrim(Annodt) != ""
    assert month(csmar_date) == 12 & day(csmar_date) == 31
    generate int year = year(csmar_date)
    assert inrange(year,1999,2012) if "`period'" == "1999-2012"
    assert inrange(year,2013,2014) if "`period'" == "2013-2014"
    destring Y0301b Y0401b Y0501b Y0601b Y0701b Y1001b Y1101a Y1101b Y1201a Y1301b Bddihldn Bsuphldn Excuhldn Mngmhldn Y1401b Y1501a Y1501b Y1501c Y1501d Y1501e Y1601a Y1601b Y1601c Y1701a Y1701b Y1701c Y1801b, replace
    generate str28 csmar_source = "CG_Ybasic`period'.xls"
    format csmar_date csmar_announcement %tdCCYY-NN-DD
    isid stkcd year
    if "`period'" == "1999-2012" save `gov_first', replace
}
append using `gov_first'
isid stkcd year
post `audit' ("csmar_total_rows") (_N)
post `audit' ("csmar_duplicate_key_excess") (0)
egen byte firm_tag = tag(stkcd)
quietly count if firm_tag
post `audit' ("csmar_distinct_codes") (r(N))
drop firm_tag
label variable csmar_date "CSMAR统计截止日期；由Reptdt解析"
label variable csmar_announcement "CSMAR公告日期；由Annodt解析"
label variable csmar_source "Original CSMAR filename"
label variable csmar_excel_row "Row number in original CSMAR worksheet"
post `dict' ("csmar_date") ("由Reptdt解析的统计截止日期") ("日期") ("derived")
post `dict' ("csmar_announcement") ("由Annodt解析的公告日期；不用于当期年份匹配") ("日期") ("derived")
post `dict' ("csmar_source") ("原CSMAR文件名") ("文本") ("provenance")
post `dict' ("csmar_excel_row") ("CSMAR原始工作表行号") ("行") ("provenance")
sort stkcd year
compress
save "csmar_governance_1999_2014.dta", replace

* 3. Full outer 1:1 merge: retain all years and explicitly mark unmatched rows.
use "resset_financial_all_years.dta", clear
merge 1:1 stkcd year using "csmar_governance_1999_2014.dta"
assert _merge == 1 if code_format_exception == 1
replace code_format_exception = 0 if _merge == 2
assert Enddt == csmar_date if _merge == 3
isid stkcd year
generate byte in_resset = inlist(_merge,1,3)
generate byte in_csmar = inlist(_merge,2,3)
generate byte matched = _merge == 3
generate byte complete_roa_roe = matched & !missing(ROA,ROE)
label define merge_source 1 "RESSET only" 2 "CSMAR only" 3 "Matched", replace
label values _merge merge_source
label variable complete_roa_roe "Matched record with both ROA and ROE observed"
foreach v in in_resset in_csmar matched complete_roa_roe {
    label variable `v' "`v': 1=yes, 0=no"
}
post `dict' ("_merge") ("合并状态：1仅RESSET，2仅CSMAR，3两库匹配") ("编码1/2/3") ("derived")
post `dict' ("in_resset") ("是否在RESSET文件出现；1是0否") ("0/1") ("derived")
post `dict' ("in_csmar") ("是否在CSMAR文件出现；1是0否") ("0/1") ("derived")
post `dict' ("matched") ("是否两库匹配；1是0否") ("0/1") ("derived")
post `dict' ("complete_roa_roe") ("两库匹配且ROA/ROE均非缺失；1是0否") ("0/1") ("derived")
post `audit' ("merged_total_rows") (_N)
forvalues s = 1/3 {
    quietly count if _merge == `s'
    post `audit' ("merge_status_`s'") (r(N))
}
quietly count if complete_roa_roe
post `audit' ("matched_complete_roa_roe") (r(N))
egen byte firm_tag = tag(stkcd) if matched
quietly count if firm_tag == 1
post `audit' ("matched_distinct_codes") (r(N))
drop firm_tag
quietly count if _merge == 1 & year < 1999
post `audit' ("resset_only_before_1999") (r(N))
quietly count if _merge == 1 & year >= 1999
post `audit' ("resset_only_from_1999") (r(N))
foreach v in ROA ROE {
    quietly count if matched & missing(`v')
    post `audit' ("matched_missing_`v'") (r(N))
}
order stkcd year ROA ROE _merge in_resset in_csmar matched complete_roa_roe
sort stkcd year
format ROA ROE %24.17g
char _dta[scope] "All observations supplied by user; not a certified census of listed firms."
char _dta[units] "ROA/ROE raw source values retained; headers say percent; economic scale not independently verified."
char _dta[merge] "1:1 stkcd year; financial master, governance using; full outer merge."
save "financial_governance_all_years.dta", replace
preserve
    * Stata display formats can round large holdings/pay values in CSV.
    * Export all non-date numeric columns with full double precision.
    quietly ds, has(type numeric)
    local numeric_vars `r(varlist)'
    local date_vars Enddt csmar_date csmar_announcement
    local precise_vars : list numeric_vars - date_vars
    format `precise_vars' %24.17g
    export delimited using "financial_governance_all_years.csv", datafmt nolabel quote replace
restore
preserve
    keep if matched
    save "financial_governance_matched.dta", replace
restore
preserve
    keep if !matched
    keep stkcd year _merge ROA ROE Comcd Lcomnm Enddt Reptdt csmar_source resset_excel_row csmar_excel_row
    export delimited using "unmatched_records.csv", datafmt nolabel quote replace
restore

* 4. Year-by-year coverage and missingness; structural absence is separate.
preserve
    generate byte resset_only = _merge == 1
    generate byte csmar_only = _merge == 2
    generate byte missing_roa = in_resset & missing(ROA)
    generate byte missing_roe = in_resset & missing(ROE)
    collapse (sum) resset_n=in_resset csmar_n=in_csmar matched_n=matched complete_n=complete_roa_roe resset_only csmar_only missing_roa missing_roe, by(year)
    generate double pct_resset_matched = 100*matched_n/resset_n
    generate double pct_csmar_matched = 100*matched_n/csmar_n
    format pct* %12.6f
    save "merge_by_year.dta", replace
    export delimited using "merge_by_year.csv", datafmt replace
    list, noobs separator(0)
    set scheme s2color
    twoway (line resset_n year, lcolor(navy) lwidth(medthick)) (line csmar_n year, lcolor(teal) lwidth(medthick)) (line matched_n year, lcolor(orange) lwidth(medthick)), title("Annual coverage in the supplied files") subtitle("Observations by report year; not a market census") xtitle("Report year") ytitle("Company-year records") xlabel(1990(2)2014, angle(45)) legend(order(1 "RESSET" 2 "CSMAR" 3 "Matched") rows(1)) graphregion(color(white)) name(coverage, replace)
    graph save "annual_coverage.gph", replace
    graph export "annual_coverage.png", width(1800) replace
restore

* 5. Descriptive statistics on the unchanged source scale.
tempname desc
postfile `desc' str20 sample str8 variable long observations long missing_count double mean double sd double min double p1 double p25 double p50 double p75 double p99 double max using "descriptive_statistics.dta", replace
foreach sample in resset_all matched matched_complete {
    local condition "in_resset == 1"
    if "`sample'" == "matched" local condition "matched == 1"
    if "`sample'" == "matched_complete" local condition "complete_roa_roe == 1"
    foreach v in ROA ROE {
        quietly count if (`condition') & missing(`v')
        local nm = r(N)
        quietly summarize `v' if `condition', detail
        post `desc' ("`sample'") ("`v'") (r(N)) (`nm') (r(mean)) (r(sd)) (r(min)) (r(p1)) (r(p25)) (r(p50)) (r(p75)) (r(p99)) (r(max))
    }
}
postclose `desc'
preserve
    use "descriptive_statistics.dta", clear
    format mean-max %24.17g
    export delimited using "descriptive_statistics.csv", datafmt replace
    list sample variable observations missing_count mean p50 min max, noobs
restore

* Preserve a short list of source extremes for transparent follow-up.
preserve
    keep if in_resset
    keep stkcd year Comcd Lcomnm ROA ROE resset_excel_row
    generate byte extreme = 0
    foreach v in ROA ROE {
        quietly summarize `v', detail
        replace extreme = 1 if inlist(`v',r(min),r(max)) & !missing(`v')
    }
    keep if extreme
    drop extreme
    export delimited using "source_extremes.csv", datafmt quote replace
restore

* 6. Ordinary OLS example to verify residual df; no causal interpretation.
* Using 2013 (a single cross-section) avoids implying panel-error independence.
generate double board_size = Y1101a
generate double independent_share = Y1101b/Y1101a if Y1101a > 0 & !missing(Y1101a,Y1101b)
generate byte valid_board = board_size > 0 & !missing(board_size,independent_share) & inrange(independent_share,0,1)
quietly count if matched & year == 2013 & !valid_board
post `audit' ("ols_2013_excluded_board_records") (r(N))
regress ROA board_size independent_share if matched & year == 2013 & valid_board
estimates save "df_example_ols.ster", replace
scalar n_ols = e(N)
scalar k_ols = e(df_m)
scalar rank_ols = e(rank)
scalar df_ols = e(df_r)
scalar sse_ols = e(rss)
scalar s2_ols = e(rss)/e(df_r)
scalar rmse2_ols = e(rmse)^2
scalar naive_s2 = e(rss)/e(N)
assert scalar(k_ols) == 2
assert scalar(rank_ols) == 3
assert scalar(df_ols) == scalar(n_ols)-3
assert reldif(scalar(s2_ols),scalar(rmse2_ols)) < 1e-12
predict double residual if e(sample), residuals
generate double residual_sq = residual^2
quietly summarize residual_sq, meanonly
scalar residual_sse = r(sum)
assert reldif(scalar(residual_sse),scalar(sse_ols)) < 1e-10
quietly summarize residual, meanonly
scalar residual_sum = r(sum)
generate double board_residual = board_size*residual
quietly summarize board_residual, meanonly
scalar board_residual_sum = r(sum)
generate double independent_residual = independent_share*residual
quietly summarize independent_residual, meanonly
scalar independent_residual_sum = r(sum)
assert abs(scalar(residual_sum)) < 1e-7
assert abs(scalar(board_residual_sum)) < 1e-6
assert abs(scalar(independent_residual_sum)) < 1e-7
tempname dfout
postfile `dfout' str36 metric double value using "df_example_metrics.dta", replace
foreach s in n_ols k_ols rank_ols df_ols sse_ols s2_ols rmse2_ols naive_s2 residual_sse residual_sum board_residual_sum independent_residual_sum {
    post `dfout' ("`s'") (scalar(`s'))
}
postclose `dfout'
preserve
    keep if e(sample)
    keep stkcd year ROA board_size independent_share residual residual_sq
    save "df_example_sample.dta", replace
restore
postclose `audit'
postclose `dict'

foreach f in processing_audit variable_dictionary df_example_metrics {
    use "`f'.dta", clear
    capture format value %24.17g
    export delimited using "`f'.csv", datafmt quote replace
}
use "financial_governance_all_years.dta", clear
isid stkcd year
tabulate _merge
display "WEEK8_ANALYSIS_COMPLETED_SUCCESSFULLY"
log close
exit, clear