Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Categorizing credit rating into investment and non-investment grade

    Dear STATA user

    I need help to categorize variable “cur_rating” and "last_rating" based on the variable “agency” and 2 table of criteria below into new variable “grade” that consist of observation:
    --> “1” or “investment” if observation in variable “cur_rating” and "last_rating" is one of this letter. The observation (Aaa, AAA,etc) in variable “cur_rating” and "last_rating" that can be categorized into “investment” is depend on the variable “agency” who assign it, as described below:
    agency rating
    Moody's Aaa Aa1 Aa2 Aa3 A1 A2 A3 Baa1 Baa2 Baa3
    Standard & Poor's (S&P) AAA AA+ AA AA- A+ A A- BBB+ BBB BBB-
    Fitch AAA AA+ AA AA- A+ A A- BBB+ BBB BBB-
    PEFINDO idAAA idAA+ idAA idAA- idA+ idA idA- idBBB+ idBBB idBBB-
    --> “0” or “non-investment” if observation in variable “cur_rating” and "last_rating" is one of this letter. The observation (Ba1, idBB+, etc) in variable “cur_rating” and "last_rating" that can be categorized into “investment” is depend on the variable “agency” who assign it, as described below:
    agency rating
    Moody's Ba1 Ba2 Ba3 B1 B2 B3 Caa1 Caa2 Caa3 Ca C
    Standard & Poor's (S&P) BB+ BB BB- B+ B B- CCC+ CCC CCC- CC C D
    Fitch BB+ BB BB- B+ B B- CCC+ CCC CCC- CC C D
    PEFINDO idBB+ idBB idBB- idB+ idB idB- idCCC idD idSD
    "
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str9(cur_rating last_rating) str17 SecurityName long agency float event_date
"CC *-"   "CCC *-"  "DGSA IJ Equity"    4 14627
"idBB+ "  "idBB- "  "BSBR IJ Equity"    3 14641
"idA "    "idA- "   "TKIM IJ Equity"    3 14651
"idA "    "idA- "   "INKP IJ Equity"    3 14651
"idA "    "idA- "   "1014Z IJ Equity"   3 14651
"idA "    "idA- "   "1085Z IJ Equity"   3 14651
"idCCC "  "idD "    "BBTN IJ Equity"    3 14742
"idA+ "   "idA "    "1072Z IJ Equity"   3 14753
"CCC "    "CCC- *-" "HMSP IJ Equity"    4 14763
"D "      "SD "     "147674Z IN Equity" 4 14777
"WR "     "idCCC "  "JRPT IJ Equity"    3 14791
"CC "     "CCC- *-" "1046Z IJ Equity"   4 14811
"WR "     "idD "    "PWSI IJ Equity"    3 14812
"SD "     "CC *-"   "1050Z IJ Equity"   4 14812
"NR "     "CC "     "1046Z IJ Equity"   4 14812
"WR "     "idCCC "  "BFIN IJ Equity"    3 14812
"WR "     "idD "    "MLND IJ Equity"    3 14812
"CCC "    "CC *"    "MPPA IJ Equity"    4 14817
"idBBB- " "idBB+ "  "DUTI IJ Equity"    3 14823
"NR "     "CCC *-"  "1004Z IJ Equity"   4 14830
"idBB "   "idB+ "   "CMNP IJ Equity"    3 14850
"idA+ "   "idA "    "HMSP IJ Equity"    3 14853
"NR "     "CCC- *-" "BRPT IJ Equity"    4 14861
"WR "     "idBB "   "PAFI IJ Equity"    3 14868
"idB+ "   "idCCC "  "ASII IJ Equity"    3 14868
"CCC+ *+" "CCC+ "   "TKIM IJ Equity"    4 14872
"CCC+ *+" "CCC+ "   "1014Z IJ Equity"   4 14872
"CCC+ *+" "CCC+ "   "INKP IJ Equity"    4 14872
"CCC+ *+" "CCC+ "   "1085Z IJ Equity"   4 14872
"CCC+ *+" "CCC+ "   "BBNI IJ Equity"    4 14894
"WR "     "idCCC "  "BBLD IJ Equity"    3 14894
"SD "     "CC *-"   "DGSA IJ Equity"    4 14903
"WR "     "idD "    "MDBN IJ Equity"    3 14908
"WR "     "idD "    "PWON IJ Equity"    3 14913
"idBBB "  "idBBB- " "NISP IJ Equity"    3 14914
"idBBB- " "idBB+ "  "BSBR IJ Equity"    3 14914
"idBB "   "idB "    "1067Z IJ Equity"   3 14914
"idBBB "  "idBBB- " "DUTI IJ Equity"    3 14956
"D "      "SD "     "DGSA IJ Equity"    4 14958
"B- "     "CCC+ *+" "BBNI IJ Equity"    4 14962
"WR "     "Ca "     "POLY IJ Equity"    2 14963
"idA "    "idA- "   "1070Z IJ Equity"   3 14966
"idBBB+ " "idBBB "  "ULTJ IJ Equity"    3 14984
"NR "     "D "      "147674Z IN Equity" 4 14994
"CCC+ *"  "CCC+ *+" "1085Z IJ Equity"   4 14994
"CCC+ *"  "CCC+ *+" "TKIM IJ Equity"    4 14994
"CCC+ *"  "CCC+ *+" "INKP IJ Equity"    4 14994
"CCC+ *"  "CCC+ *+" "1014Z IJ Equity"   4 14994
"CCC+ *-" "CCC+ *"  "TKIM IJ Equity"    4 15006
"CCC+ *-" "CCC+ *"  "1085Z IJ Equity"   4 15006
"CCC+ *-" "CCC+ *"  "INKP IJ Equity"    4 15006
"CCC+ *-" "CCC+ *"  "1014Z IJ Equity"   4 15006
"CCC- *-" "CCC+ *-" "INKP IJ Equity"    4 15008
"D "      "CCC+ *-" "TKIM IJ Equity"    4 15008
"CCC- *-" "CCC+ *-" "1085Z IJ Equity"   4 15008
"CCC- *-" "CCC+ *-" "1014Z IJ Equity"   4 15008
"D "      "SD "     "1050Z IJ Equity"   4 15008
"B- *-"   "B- "     "BNII IJ Equity"    1 15013
"SD "     "D "      "TKIM IJ Equity"    4 15013
"CCCpi "  "R "      "BBCA IJ Equity"    4 15020
"idBB+ "  "idA "    "INKP IJ Equity"    3 15020
"idBB+ "  "idA "    "TKIM IJ Equity"    3 15020
"idBB+ "  "idA "    "1085Z IJ Equity"   3 15020
"idBB+ "  "idA "    "1014Z IJ Equity"   3 15020
"CCC- *-" "SD "     "TKIM IJ Equity"    4 15036
"idD "    "WR "     "BRPT IJ Equity"    3 15046
"idCCC "  "idBB+ "  "1014Z IJ Equity"   3 15047
"CC *-"   "CCC- *-" "1085Z IJ Equity"   4 15047
"CC *-"   "CCC- *-" "INKP IJ Equity"    4 15047
"idCCC "  "idBB+ "  "1085Z IJ Equity"   3 15047
"idCCC "  "idBB+ "  "TKIM IJ Equity"    3 15047
"CC *-"   "CCC- *-" "1014Z IJ Equity"   4 15047
"idCCC "  "idBB+ "  "INKP IJ Equity"    3 15047
"CC *-"   "CCC- *-" "TKIM IJ Equity"    4 15047
"idBBB "  "idBBB- " "BJBR IJ Equity"    3 15062
"D "      "CC *-"   "1014Z IJ Equity"   4 15067
"D "      "CC *-"   "TKIM IJ Equity"    4 15067
"D "      "CC *-"   "1085Z IJ Equity"   4 15067
"D "      "CC *-"   "INKP IJ Equity"    4 15067
"SD "     "D "      "DGSA IJ Equity"    4 15073
"NR "     "SD "     "DGSA IJ Equity"    4 15074
"idD "    "idCCC "  "INKP IJ Equity"    3 15079
"idD "    "idCCC "  "1085Z IJ Equity"   3 15081
"idD "    "idCCC "  "1014Z IJ Equity"   3 15091
"idD "    "idB "    "0781793DIJ Equity" 3 15103
"idD "    "idCCC "  "TKIM IJ Equity"    3 15103
"B- "     "B- *-"   "BNII IJ Equity"    1 15116
"idBB- "  "idCCC "  "BBTN IJ Equity"    3 15124
"NR "     "D "      "FASW IJ Equity"    4 15151
"NR "     "D "      "TPIA IJ Equity"    4 15151
"NR "     "D "      "INRU IJ Equity"    4 15151
"NR "     "D "      "POLY IJ Equity"    4 15151
"NR "     "D "      "1050Z IJ Equity"   4 15151
"WR "     "idD "    "CTRA IJ Equity"    3 15174
"B "      "CCC "    "HMSP IJ Equity"    4 15188
"idBB+ "  "idBB "   "1067Z IJ Equity"   3 15201
"idD "    "WR "     "BBLD IJ Equity"    3 15251
"CCC+ "   "CCC "    "MPPA IJ Equity"    4 15278
"WR "     "idCCC "  "DART IJ Equity"    3 15279
"WR "     "idD "    "PUDP IJ Equity"    3 15279
end
format %td event_date
label values agency agency
label def agency 1 "Fitch", modify
label def agency 2 "Moody's", modify
label def agency 3 "PEFIN", modify
label def agency 4 "S&P", modify
    "

    Thanks in advance for your help

    regards,
    Anzas
    Tags: None
  • #2
    While there are probably more elegant ways to do this, you can just do it with replace and a bunch of conditions.
    g invcat=.
    replace invcat=0 if agency=="Moody's" & (rating=="Ba1" | rating=="Ba2")

    Naturally, this will take a few lines and you'll want more ratings== conditions.

    Comment

    • #3
      This is the third post with respect to this problem. I worked hard to provide a complete solution here but anzas just replied that it did not work and requested that I apply my divination powers to figure out what's going on. He reposted the question a second time and I pointed out the problem here. Apparently, that was not sufficient, so here again is a complete solution, based on the observation that only the first word of each rating needs to be matched with the agency ratings.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
clear
input str23 agency str6 rating float investment
"Fitch"                   "B"      0
"Fitch"                   "B+"     0
"Fitch"                   "B-"     0
"Fitch"                   "BB"     0
"Fitch"                   "BB+"    0
"Fitch"                   "BB-"    0
"Fitch"                   "C"      0
"Fitch"                   "CC"     0
"Fitch"                   "CCC"    0
"Fitch"                   "CCC+"   0
"Fitch"                   "CCC-"   0
"Fitch"                   "D"      0
"Fitch"                   "A"      1
"Fitch"                   "A+"     1
"Fitch"                   "A-"     1
"Fitch"                   "AA"     1
"Fitch"                   "AA+"    1
"Fitch"                   "AA-"    1
"Fitch"                   "AAA"    1
"Fitch"                   "BBB"    1
"Fitch"                   "BBB+"   1
"Fitch"                   "BBB-"   1
"Moody's"                 ""       0
"Moody's"                 "B1"     0
"Moody's"                 "B2"     0
"Moody's"                 "B3"     0
"Moody's"                 "Ba1"    0
"Moody's"                 "Ba2"    0
"Moody's"                 "Ba3"    0
"Moody's"                 "C"      0
"Moody's"                 "Ca"     0
"Moody's"                 "Caa1"   0
"Moody's"                 "Caa2"   0
"Moody's"                 "Caa3"   0
"Moody's"                 "A1"     1
"Moody's"                 "A2"     1
"Moody's"                 "A3"     1
"Moody's"                 "Aa1"    1
"Moody's"                 "Aa2"    1
"Moody's"                 "Aa3"    1
"Moody's"                 "Aaa"    1
"Moody's"                 "Baa1"   1
"Moody's"                 "Baa2"   1
"Moody's"                 "Baa3"   1
"PEFINDO"                 ""       0
"PEFINDO"                 ""       0
"PEFINDO"                 ""       0
"PEFINDO"                 "idB"    0
"PEFINDO"                 "idB+"   0
"PEFINDO"                 "idB-"   0
"PEFINDO"                 "idBB"   0
"PEFINDO"                 "idBB+"  0
"PEFINDO"                 "idBB-"  0
"PEFINDO"                 "idCCC"  0
"PEFINDO"                 "idD"    0
"PEFINDO"                 "idSD"   0
"PEFINDO"                 "idA"    1
"PEFINDO"                 "idA+"   1
"PEFINDO"                 "idA-"   1
"PEFINDO"                 "idAA"   1
"PEFINDO"                 "idAA+"  1
"PEFINDO"                 "idAA-"  1
"PEFINDO"                 "idAAA"  1
"PEFINDO"                 "idBBB"  1
"PEFINDO"                 "idBBB+" 1
"PEFINDO"                 "idBBB-" 1
"Standard & Poor's (S&P)" "B"      0
"Standard & Poor's (S&P)" "B+"     0
"Standard & Poor's (S&P)" "B-"     0
"Standard & Poor's (S&P)" "BB"     0
"Standard & Poor's (S&P)" "BB+"    0
"Standard & Poor's (S&P)" "BB-"    0
"Standard & Poor's (S&P)" "C"      0
"Standard & Poor's (S&P)" "CC"     0
"Standard & Poor's (S&P)" "CCC"    0
"Standard & Poor's (S&P)" "CCC+"   0
"Standard & Poor's (S&P)" "CCC-"   0
"Standard & Poor's (S&P)" "D"      0
"Standard & Poor's (S&P)" "A"      1
"Standard & Poor's (S&P)" "A+"     1
"Standard & Poor's (S&P)" "A-"     1
"Standard & Poor's (S&P)" "AA"     1
"Standard & Poor's (S&P)" "AA+"    1
"Standard & Poor's (S&P)" "AA-"    1
"Standard & Poor's (S&P)" "AAA"    1
"Standard & Poor's (S&P)" "BBB"    1
"Standard & Poor's (S&P)" "BBB+"   1
"Standard & Poor's (S&P)" "BBB-"   1
end
drop if mi(rating)
replace agency = "PEFIN" if agency == "PEFINDO"
replace agency = "S&P" if agency == "Standard & Poor's (S&P)"
save "agency_ratings.dta", replace


* Example generated by -dataex-. To install: ssc install dataex
clear
input str9(cur_rating last_rating) str17 SecurityName long agency float event_date
"CC *-"   "CCC *-"  "DGSA IJ Equity"    4 14627
"idBB+ "  "idBB- "  "BSBR IJ Equity"    3 14641
"idA "    "idA- "   "TKIM IJ Equity"    3 14651
"idA "    "idA- "   "INKP IJ Equity"    3 14651
"idA "    "idA- "   "1014Z IJ Equity"   3 14651
"idA "    "idA- "   "1085Z IJ Equity"   3 14651
"idCCC "  "idD "    "BBTN IJ Equity"    3 14742
"idA+ "   "idA "    "1072Z IJ Equity"   3 14753
"CCC "    "CCC- *-" "HMSP IJ Equity"    4 14763
"D "      "SD "     "147674Z IN Equity" 4 14777
"WR "     "idCCC "  "JRPT IJ Equity"    3 14791
"CC "     "CCC- *-" "1046Z IJ Equity"   4 14811
"WR "     "idD "    "PWSI IJ Equity"    3 14812
"SD "     "CC *-"   "1050Z IJ Equity"   4 14812
"NR "     "CC "     "1046Z IJ Equity"   4 14812
"WR "     "idCCC "  "BFIN IJ Equity"    3 14812
"WR "     "idD "    "MLND IJ Equity"    3 14812
"CCC "    "CC *"    "MPPA IJ Equity"    4 14817
"idBBB- " "idBB+ "  "DUTI IJ Equity"    3 14823
"NR "     "CCC *-"  "1004Z IJ Equity"   4 14830
"idBB "   "idB+ "   "CMNP IJ Equity"    3 14850
"idA+ "   "idA "    "HMSP IJ Equity"    3 14853
"NR "     "CCC- *-" "BRPT IJ Equity"    4 14861
"WR "     "idBB "   "PAFI IJ Equity"    3 14868
"idB+ "   "idCCC "  "ASII IJ Equity"    3 14868
"CCC+ *+" "CCC+ "   "TKIM IJ Equity"    4 14872
"CCC+ *+" "CCC+ "   "1014Z IJ Equity"   4 14872
"CCC+ *+" "CCC+ "   "INKP IJ Equity"    4 14872
"CCC+ *+" "CCC+ "   "1085Z IJ Equity"   4 14872
"CCC+ *+" "CCC+ "   "BBNI IJ Equity"    4 14894
"WR "     "idCCC "  "BBLD IJ Equity"    3 14894
"SD "     "CC *-"   "DGSA IJ Equity"    4 14903
"WR "     "idD "    "MDBN IJ Equity"    3 14908
"WR "     "idD "    "PWON IJ Equity"    3 14913
"idBBB "  "idBBB- " "NISP IJ Equity"    3 14914
"idBBB- " "idBB+ "  "BSBR IJ Equity"    3 14914
"idBB "   "idB "    "1067Z IJ Equity"   3 14914
"idBBB "  "idBBB- " "DUTI IJ Equity"    3 14956
"D "      "SD "     "DGSA IJ Equity"    4 14958
"B- "     "CCC+ *+" "BBNI IJ Equity"    4 14962
"WR "     "Ca "     "POLY IJ Equity"    2 14963
"idA "    "idA- "   "1070Z IJ Equity"   3 14966
"idBBB+ " "idBBB "  "ULTJ IJ Equity"    3 14984
"NR "     "D "      "147674Z IN Equity" 4 14994
"CCC+ *"  "CCC+ *+" "1085Z IJ Equity"   4 14994
"CCC+ *"  "CCC+ *+" "TKIM IJ Equity"    4 14994
"CCC+ *"  "CCC+ *+" "INKP IJ Equity"    4 14994
"CCC+ *"  "CCC+ *+" "1014Z IJ Equity"   4 14994
"CCC+ *-" "CCC+ *"  "TKIM IJ Equity"    4 15006
"CCC+ *-" "CCC+ *"  "1085Z IJ Equity"   4 15006
"CCC+ *-" "CCC+ *"  "INKP IJ Equity"    4 15006
"CCC+ *-" "CCC+ *"  "1014Z IJ Equity"   4 15006
"CCC- *-" "CCC+ *-" "INKP IJ Equity"    4 15008
"D "      "CCC+ *-" "TKIM IJ Equity"    4 15008
"CCC- *-" "CCC+ *-" "1085Z IJ Equity"   4 15008
"CCC- *-" "CCC+ *-" "1014Z IJ Equity"   4 15008
"D "      "SD "     "1050Z IJ Equity"   4 15008
"B- *-"   "B- "     "BNII IJ Equity"    1 15013
"SD "     "D "      "TKIM IJ Equity"    4 15013
"CCCpi "  "R "      "BBCA IJ Equity"    4 15020
"idBB+ "  "idA "    "INKP IJ Equity"    3 15020
"idBB+ "  "idA "    "TKIM IJ Equity"    3 15020
"idBB+ "  "idA "    "1085Z IJ Equity"   3 15020
"idBB+ "  "idA "    "1014Z IJ Equity"   3 15020
"CCC- *-" "SD "     "TKIM IJ Equity"    4 15036
"idD "    "WR "     "BRPT IJ Equity"    3 15046
"idCCC "  "idBB+ "  "1014Z IJ Equity"   3 15047
"CC *-"   "CCC- *-" "1085Z IJ Equity"   4 15047
"CC *-"   "CCC- *-" "INKP IJ Equity"    4 15047
"idCCC "  "idBB+ "  "1085Z IJ Equity"   3 15047
"idCCC "  "idBB+ "  "TKIM IJ Equity"    3 15047
"CC *-"   "CCC- *-" "1014Z IJ Equity"   4 15047
"idCCC "  "idBB+ "  "INKP IJ Equity"    3 15047
"CC *-"   "CCC- *-" "TKIM IJ Equity"    4 15047
"idBBB "  "idBBB- " "BJBR IJ Equity"    3 15062
"D "      "CC *-"   "1014Z IJ Equity"   4 15067
"D "      "CC *-"   "TKIM IJ Equity"    4 15067
"D "      "CC *-"   "1085Z IJ Equity"   4 15067
"D "      "CC *-"   "INKP IJ Equity"    4 15067
"SD "     "D "      "DGSA IJ Equity"    4 15073
"NR "     "SD "     "DGSA IJ Equity"    4 15074
"idD "    "idCCC "  "INKP IJ Equity"    3 15079
"idD "    "idCCC "  "1085Z IJ Equity"   3 15081
"idD "    "idCCC "  "1014Z IJ Equity"   3 15091
"idD "    "idB "    "0781793DIJ Equity" 3 15103
"idD "    "idCCC "  "TKIM IJ Equity"    3 15103
"B- "     "B- *-"   "BNII IJ Equity"    1 15116
"idBB- "  "idCCC "  "BBTN IJ Equity"    3 15124
"NR "     "D "      "FASW IJ Equity"    4 15151
"NR "     "D "      "TPIA IJ Equity"    4 15151
"NR "     "D "      "INRU IJ Equity"    4 15151
"NR "     "D "      "POLY IJ Equity"    4 15151
"NR "     "D "      "1050Z IJ Equity"   4 15151
"WR "     "idD "    "CTRA IJ Equity"    3 15174
"B "      "CCC "    "HMSP IJ Equity"    4 15188
"idBB+ "  "idBB "   "1067Z IJ Equity"   3 15201
"idD "    "WR "     "BBLD IJ Equity"    3 15251
"CCC+ "   "CCC "    "MPPA IJ Equity"    4 15278
"WR "     "idCCC "  "DART IJ Equity"    3 15279
"WR "     "idD "    "PUDP IJ Equity"    3 15279
end
format %td event_date
label values agency agency
label def agency 1 "Fitch", modify
label def agency 2 "Moody's", modify
label def agency 3 "PEFIN", modify
label def agency 4 "S&P", modify

* Please stop encoding data like this!!!!!
rename agency nagency
decode nagency, gen(agency)
drop nagency

* match the first word of cur_rating with the agency rating map
gen rating = word(cur_rating,1)
merge m:1 agency rating using "agency_ratings.dta", keep(master match) nogen
rename investment cur_invest
tab rating if mi(cur_invest)

* match the first word of last_rating with the agency rating map
replace rating = word(last_rating,1)
merge m:1 agency rating using "agency_ratings.dta", keep(master match) nogen
rename investment last_invest
tab rating if mi(last_invest)
drop rating

sort event_date SecurityName agency

list in 1/10
      and the results
      Code:
      . list in 1/10

     +------------------------------------------------------------------------------------+
     | cur_ra~g   last_r~g        SecurityName   event_d~e   agency   cur_in~t   last_i~t |
     |------------------------------------------------------------------------------------|
  1. |    CC *-     CCC *-      DGSA IJ Equity   18jan2000      S&P          0          0 |
  2. |   idBB+      idBB-       BSBR IJ Equity   01feb2000    PEFIN          0          0 |
  3. |     idA       idA-      1014Z IJ Equity   11feb2000    PEFIN          1          1 |
  4. |     idA       idA-      1085Z IJ Equity   11feb2000    PEFIN          1          1 |
  5. |     idA       idA-       INKP IJ Equity   11feb2000    PEFIN          1          1 |
     |------------------------------------------------------------------------------------|
  6. |     idA       idA-       TKIM IJ Equity   11feb2000    PEFIN          1          1 |
  7. |   idCCC        idD       BBTN IJ Equity   12may2000    PEFIN          0          0 |
  8. |    idA+        idA      1072Z IJ Equity   23may2000    PEFIN          1          1 |
  9. |     CCC     CCC- *-      HMSP IJ Equity   02jun2000      S&P          0          0 |
 10. |       D         SD    147674Z IN Equity   16jun2000      S&P          0          . |
     +------------------------------------------------------------------------------------+

      Comment

      • #4
        Thank you Robert Picard and Phil Bromiley
        much appreciated

        Comment

        Previous Next
        Working...
        X