  //----------------------------------------//
  //*** building Upstrreamness-fin ratio ***//
  //this ratio base method of Antras(2016) and created by YuanYe(HUST)//
  clear
  set more off
  global PATH1 "E:\Research\GVCs\data\financial_constraint\by_year_upstreamness2"
  cd $PATH1
  use workhouse.dta  // merged CUS and AIS data fpllowing Yu(2010)
  
  //  generate measure of fiancial constraint //
  // financial indicator of 1 //
  bysort year indu_code : egen inventory_sum=sum(inventory)
  bysort year indu_code : egen mainincome_sum=sum(main_income)
  gen fin_1=inventory_sum/mainincome_sum
  
  bysort year indu_code : egen tangble_sum=sum(tot_asset-intangble)
  bysort year indu_code : egen totasset_sum=sum(tot_asset)
  bysort year indu_code : gen fin2=totasset_sum/tangble_sum
  sort fin2
  bysort year indu_code:  egen fin_2 = median(fin2)
  
  gen fin3=rnd/mainincome
  sort fin3
  bysort year indu_code: egen fin_3 = median(fin3)   
  // after that we find Rnd is not avairiables for all frims
  // so in regressstion we drop it .

  gen cash_flow=main_income-main_cost-tax-inventory
  replace cash_flow=0 if cash_flow==.
  gen     fin4=(fix_asset_1+year_depreciation-fix_asset_2-cash_flow)/(fix_asset_1-fix_asset_2+year_depreciation)
  replace fin4=(fix_asset_j+year_depreciation-fix_inves-cash_flow)/(fix_asset_j-fix_inves+year_depreciation) if fin4==.
  sort fin4
  bysort year indu_code: egen fin_4=median(fin4)  
  
  
  keep fin_1 fin_2 fin_3 fin_4 indu_code IO  mainincome_sum year
  duplicates drop  IO year indu_code ,force
  
  bysort year IO : egen w=sum(mainincome_sum)
  replace fin_1=fin_1*mainincome_sum
  replace fin_2=fin_2*mainincome_sum
  replace fin_3=fin_3*mainincome_sum
  replace fin_4=fin_4*mainincome_sum
  
  bysort year IO :egen finacial_1=sum(fin_1)
  bysort year IO :egen finacial_2=sum(fin_2)
  bysort year IO :egen finacial_3=sum(fin_3)
  bysort year IO :egen finacial_4=sum(fin_4)
  
  novarabbrev replace fin_1=finacial_1/w
  novarabbrev replace fin_2=finacial_2/w
  novarabbrev replace fin_3=finacial_3/w
  novarabbrev replace fin_4=finacial_4/w

  drop finacial_1 finacial_2 finacial_3 finacial_4 w
  duplicates drop  IO year ,force
  save financial_industry.dta,replace

  clear
  forvalues t=2002(1)2007{
  use financial_industry.dta
  keep if year==`t'
  save financial_industry_`t'.dta,replace
  
  clear
  import excel "E:\Research\GVCs\data\Es_Sigma (feestran(1994)&Broda(2005))\sigma\td_90.xls", sheet("Sheet4") firstrow clear
  gen IO=_n
  
  merge 1:1 IO using "E:\Research\GVCs\data\financial_constraint\by_year_upstreamness2\financial_industry_`t'"
  drop _merge
  merge 1:1 IO using "E:\Research\GVCs\data\upstreamness\upstreamness_2"
  drop _merge
  
  keep IO IO_* fin_* up_median
  rename up_median upstreamness
  
  drop if IO>90
  drop if IO<11
  
  forvalues i=1(1)10{
   novarabbrev drop IO_`i' 
   }
   
  gen w=IO_11
  forvalues j=11(1)90{
  qui replace w=w+IO_`j'
  }
     
	 replace fin_1=0 if fin_1==.
	 replace fin_2=0 if fin_2==.
	 replace fin_3=0 if fin_3==.
	 replace fin_4=0 if fin_4==.
	 replace upstreamness=0 if upstreamness==.
	 
mkmat fin_1 ,mat(fin_1)
mkmat fin_2 ,mat(fin_2)
mkmat fin_3 ,mat(fin_3)
mkmat fin_4 ,mat(fin_4)
mkmat upstreamness ,mat(up)
mkmat IO_*,mat(Z)

mat mean_up=Z*up
mat mean_fin1=Z*fin_1
mat mean_fin2=Z*fin_2
mat mean_fin3=Z*fin_3
mat mean_fin4=Z*fin_4

svmat mean_up,names(mean_up)
svmat mean_fin1,names(mean_fin1)
svmat mean_fin2,names(mean_fin2)
svmat mean_fin3,names(mean_fin3)
svmat mean_fin4,names(mean_fin4)

replace mean_up=mean_up/w
replace mean_fin1=mean_fin1/w
replace mean_fin2=mean_fin2/w
replace mean_fin3=mean_fin3/w
replace mean_fin4=mean_fin4/w

replace mean_up=(upstreamness-mean_up)
replace mean_fin1=(fin_1-mean_fin1)
replace mean_fin2=(fin_2-mean_fin2)
replace mean_fin3=(fin_3-mean_fin3)
replace mean_fin4=(fin_4-mean_fin4)

gen fin1_up_d=mean_up*mean_fin1
gen fin2_up_d=mean_up*mean_fin2
gen fin3_up_d=mean_up*mean_fin3
gen fin4_up_d=mean_up*mean_fin4

mkmat fin1_up_d,mat(fin1_up_d)
mkmat fin2_up_d,mat(fin2_up_d)
mkmat fin3_up_d,mat(fin3_up_d)
mkmat fin4_up_d,mat(fin4_up_d)

mat up_fin1=Z*fin1_up_d
mat up_fin2=Z*fin2_up_d
mat up_fin3=Z*fin3_up_d
mat up_fin4=Z*fin4_up_d

svmat up_fin1,names(upstream_fin1)
svmat up_fin2,names(upstream_fin2)
svmat up_fin3,names(upstream_fin3)
svmat up_fin4,names(upstream_fin4)

replace upstream_fin1=upstream_fin1/w
replace upstream_fin2=upstream_fin2/w
replace upstream_fin3=upstream_fin3/w
replace upstream_fin4=upstream_fin4/w

drop IO_*
save up_mean_fin_`t'.dta,replace
}

forvalues i=2002(+1)2007{
  erase financial_industry_`i'.dta
}
  

