Step 1 – Converting SPSS formatted data
Reading data from the Wiley Web Site
The data from the book is available in SPSS .sav format and available fror download from the Wiley site https://www.wiley.com//legacy/wileychi/delaurentis/
In this page, the left “Supplementary material” list element points to a page asking for the term located on Page 106 of the book as loading keyword Two data sets are proposed: SAS and SPSS-PASW. We have used the second one: https://www.wiley.com//legacy/wileychi/delaurentis/supp/SPSS-PASW.zip
The package “haven” has been loaded from the USA-CA berkeley CRAN Repository
The RPackage Haven enables to read the SPSS format https://cran.r-project.org/web/packages/haven/index.html
Specifying your project directory (Replace by the location of your own project directory)
projdir = “C:/RExercise/”
Reading the full dataset and saving it to .csv
library(haven)
wcs1org <- read_sav(sprintf(“%sW_CS_1_OriginalDataSet.sav”, projdir))
write.csv(wcs1org, file = sprintf(“%sW_CS_1_OriginalDataSet.csv”, projdir))
Reading the “training” dataset and saving it to .csv (page 116)
wcs1train <- read_sav(“C:/Projets_En_Cours/AI_MTPL/UCI_Internal_Ratings/SPSS-PASW/W_CS_1_AnalysisSampleDataSet.sav”)
write.csv(wcs1train, file = “C:/Projets_En_Cours/AI_MTPL/UCI_Internal_Ratings/SPSS-PASW/W_CS_1_AnalysisSampleDataSet.csv”)
Reading the “cleaned” training data set and saving it to .csv (page 129)
wcs2train <- read_sav(“C:/Projets_En_Cours/AI_MTPL/UCI_Internal_Ratings/SPSS-PASW/W_CS_1_AnalysisSampleDataSet_2.sav”)
write.csv(wcs2train, file = “C:/Projets_En_Cours/AI_MTPL/UCI_Internal_Ratings/SPSS-PASW/W_CS_1_AnalysisSampleDataSet_2.csv”)
The .csv file is modified using a standard text editor so that the first column named INDEX (generated by default by R) is removed, short names are assigned to the variables and “qualitative” variables values are turned into text by putting them between quotes:
- BADGOOD variable values are transformed and put into “quoted” text: 0 -> “Good” and 1 -> “Bad”
- STATUS variable values are transformed and put into “quoted” text: 3 -> “Performing”, 2 -> “Substandard”, 1 -> “Doubtful”, 0 -> “Unknown”
- SECTOR The file is then re-imported
NOTE : The double-quote ” being a reserved character in Excel Formulas, one should replce it by an “escape” expression of the form : =””””&E2&””””
Furthermore, the separator in the .csv file being “;” this should be explicitely specified in the read.csv R function
wcs2train <- read.csv(“C:/Projets_En_Cours/AI_MTPL/UCI_Internal_Ratings/SPSS-PASW/W_CS_1_AnalysisSampleDataSet_2B.csv”, header=TRUE, sep=”;”)
Finally, list the name and type of all variables for checking:
sapply(wcs2train, class)
Double check the content of the BADGOOD field (of type “factor” in R) in order to count the number of companies being Active (labelled “bad”) and those being Insolvent (labelled “bad”)
summary(wcs2train$BADGOOD)
Here, the R ouput is:
“Bad” “Good”
51 1221
NOTE: All resulting files are available for download at : https://github.com/MoiraCorp/DLMM-IRating-in-R/tree/main/steps/step1/assets
List of ratios used in the DLMM model
| Variable (Short) | Variable Name in Text | Description |
|---|---|---|
| ROE | ROE | Ratio Net Profit/Equity |
| EBITDAON | EBITDAonSALES | Ratio EBITDA/Sales [%] |
| ROI | ROI | Ratio EBIT/Operating Assets [%] |
| ROA | ROA | Ratio Current Income/Total Assets [%] |
| V89A | EBITDAonVP | Ratio EBITDA/Value of Production |
| ROS | ROS | Ratio EBIT/Sales [%] |
| ASSETSTU | ASSETS_TURNOVER | Ratio Total Assets/Turnover |
| INVENTOR | INVENTORY_TURNOVER | Ratio Inventory/Turnover |
| RECEIVAB | Receivables_Turnover | Ratio Trade Receivables at year end/Turnover |
| V94A | RECEIVABLES_PERIOD | Ratio Trade Receivables/Daily Sales |
| V95A | INVENTORY_PERIOD | Ratio Inventory/Daily Sales |
| PAYABLES | PAYABLES_PERIOD | Ratio Trade Payables/Daily Purchases |
| COMMERCI | COMMERCIAL_WC_PERIOD | Ratio (Trade Receivables + Inventory – Trade Payables)/Daily Sales |
| IEONEBIT | IEonEBITDA | Ratio Interest Expenses/EBITDA [%] |
| NIEONEBI | NIEonEBITDA | Ratio Net Interest Expenses/EBITDA [%] |
| IEONLIAB | IEonLIABLITIES | Ratio Interest Expenses/Liabilities [%] |
| IEONFINA | IEonFINANCIAL_DEBTS | Ratio Interest Expenses/Financial Debts [%] |
| EXTRIC | Extr_I_C_onCurrent_Income | Ratio Gross of Tax Profit/Income from ordinary (current) activities |
| TAXESONG | TaxesOnGross_Profit | Ratio Taxes/Gross of Tax Profit |
| INTANGIB | INTANGIBLESonTA | Ratio Intangibles/Total Assets [%] |
| TRADERE | TRADE_RECEIVABLESonTA | Ratio Trade Receivables/Total Assets [%] |
| V110A | INVENTORIESonTA | Ratio Inventories/Total Assets [%] |
| EQUITYON | EQUITYonPERMANENT_CAPITAL | Ratio Equity/Permanent Capital [%] |
| TRADEPA | TRADE_PAYABLESonTL | Ratio Trade Payables/Total Liabilities [%] |
| DEBTEQU | DEBT_EQUITY_RATIO | Ratio Debt/Equity [%] |
| CURRENT | CURRENT_RATIO | Ratio Current Assets/Current Liabilities |
| QUICKRA | QUICK_RATIO | Ratio (Current Assets – Inventories)/Current Liabilities |
| SALESONV | SALESonVP | Ratio Sales/Value of Production [%] |
| SALESMIN | SALESminusVConEBIT | Ratio (Sales –Variable Costs)/EBIT |
| ROAMINUS | ROAminusIEonTL | ROA minus Ratio Interest Expenses/Total Liabilities |
| EBITDAIE | EBITDAonIE | Ratio EBITDA/Interest Expenses |
| EQUILIABL | EQUITYonLIABILITIES | Ratio Equity/Liabilities |
| DEBTEQUTR | DebtEquityTr | Ratio Interest-bearing Financial Debt/Equity |
| ROETR | ROETr | Ratio Net Profit/Total Stockholder’s Equity |
List of financial accounting terms used in the DLMM data set
| Variable (Short) | Description |
|---|---|
| INDEX | Index in file |
| BORCODE | Borrower’s code (assigned by the bank) |
| STATUS | Borrower’s classification in regulatory grades (3 = performing, 2 = substandard, 1 = doubtful, 0 = unknown) |
| SUBSECTOR | Numerical code for business subs-sector |
| SECTOR | Numerical code for business sector |
| INTASSETS | Intangible assets, net |
| GRFIXASST | Tangible fixed assets, gross |
| ACCDEPREC | Accumulated depreciation and impairment |
| NETFIXASST | Tangible fixed assets, net |
| ADVTAINPRO | Advances and tangible assets in progress |
| NETINVEST | Investments, net |
| SECLOANS | Securities and loans |
| NCROC | Non-current receivables and other credits |
| NCFINASST | Non-current financial assets |
| TOTNCASST | Total non-current assets |
| ADVANCES | Advances |
| RMINVENT | Raw materials inventories, net |
| WIPINVENT | Work in progress and finished goods, net |
| NETINVENT | Iventories, net |
| TRRECEIV | Accounts and notes receivables |
| STLOANS | Short term loans |
| OSTCREDITS | Other short term credits |
| OCASSTS | Other current assets (prepaid expenses,…) |
| CURSEC | Current securities |
| CASH | Cash and cash equivalents |
| CASHSTINV | Cash and s. t. investments |
| TOTCURASST | Total current assets |
| TOTASST | Total assets |
| EQUITY | Total stockholder’s equity |
| PROVCHARG | Provisions for liabilities and charges |
| EMPLBENEF | Employee benefits |
| TOTPRVSNS | Provisions |
| BONDS | Bonds |
| BORFROMSH | Borrowings from shareholders falling due beyond 1 year |
| MLTBORBANK | Borrowings from banks falling due beyond 1 year |
| MLTBOROT | Borrowings from others falling due beyond 1 year |
| MLTTROTLIAB | Trade and other liabilities falling due beyond 1 year |
| MLTDEBTS | Medium and long term debts |
| TOTPERMCAP | Total permanent capital |
| STBORBANK | Bank loans payables |
| OTSTLOANS | Other loans payables |
| STFINDEBT | Short term financial debt |
| TRPAYABLES | Trade payables |
| ADPAYMENTS | Advance payments from customers |
| TOTSTPAYAB | Total trade payables (short term) |
| TAXPAYAB | Tax payables (short term) |
| OTSTPAYAB | Other payables (short term) |
| OTSTLIAB | Other liabalities (short term) |
| TOTCURLIAB | Total current liabilities |
| TOTEQU_LIAB | Total stockholder’s equity & liabilities |
| SALES | Net sales |
| CHFGWIPINV | Change in stocks of finished goods and w.i.p. |
| CAPITCOSTS | Capitalized product development costs |
| GRANTS | Grants for the financ.year & other sales (engineering) |
| VP | Value of production |
| PURCH | Purchase of raw materials and consumables |
| CHRMINV | Change in stocks of raw mat. & consumables |
| CSTSERV | Costs for services |
| AV | Added value |
| STAFFCOST | Staff costs |
| EBITDA | Gross operating income |
| DEPR | Depreciation of tangible fixed assets |
| WRDWWC | Write-downs on working capital |
| PROV | Provisions for liabilities and charges |
| EBIT | Net operating income |
| ONONFRC | Other (non-operating non-financial) revenues & charges |
| INTCAPITAL | Capitalization of intangible assets |
| DEPRCAPIT | Depreciation of intangible assets |
| EBFPL | Income on ord. act. before financial P&L |
| FININCOM | Financial income |
| FINLOSWD | Losses on and write-downs of financ.assets |
| EBI | Income of ord. act. before interest exp. |
| IE | Interest and other financial expenses |
| EOOA | Income on ordinary activities |
| GLNFA | Gains (losses) on disposal of non-financ.assets |
| RWDNFA | Reduction in write-downs & (W-D) on non-fin. assets |
| EXINCCH | Extraordinary income and (charges |
| GPROF | Gross profit before tax profit |
| CTAX | Corporate income taxes |
| OTAX | Other taxes |
| ADNPROF | Adjusted net (of tax) profit |
| ACCDEPR | Accelerated depreciation |
| OTAXITEM | Other items due to tax regulations (+ -) |
| PROUSRES | Provisions (-) to and (+) uses of reserves |
| NPROF | Profit (loss) for the financial year |