variable “gvkey”: the unique identifier in the Compustat.
Therefore, the next step merges the first two link tables, which will link LPC DealScan data and Compustat. > i.e. “Borrower_Id” (DealScan) \rightarrow “LoanConnector Company ID” (LinkTable) \Leftrightarrow “LPC_COMPANY_ID” \rightarrow “borrowercompanyid” \longrightarrow gvkey (Compustat).
S3. Merged Link Table (Compustat)
Code
list.files(pattern =".xlsx")Roberts_link <-read_excel("Dealscan-Compustat_Linking_Database012024.xlsx", sheet =2)names(Roberts_link)LPC_link <-read_excel("LPC_Loanconnector_Company_ID_Mappings.xlsx", sheet =1)names(LPC_link)Link_table <- Roberts_link %>%left_join(LPC_link, by =c("borrowercompanyid"="LPC_COMPANY_ID")) dim(Link_table); names(Link_table)save(Link_table, file ="Link_table_2024.RData")Link_table2 <- Roberts_link %>%inner_join(LPC_link, by =c("borrowercompanyid"="LPC_COMPANY_ID"))dim(Link_table2); names(Link_table2)Link_table %>%filter(is.na(`LoanConnector Company ID`))
Link_table2 follows the same logic but only keeps the borrowing companies identified both by the variable “borrowercompanyid” in Roberts link table (i.e. “Dealscan-Compustat_Linking_Database012024.xlsx”) and “LPC_COMPANY_ID” in the new-and-legacy LPC link table (i.e. “LPC_Loanconnector_Company_ID_Mappings.xlsx”). The number of observations after this merge is 203285.
Heads up!The next step is to match more observations in the LPC link table.
Definitions
(Guessed) Definition of Variables:
Updated time: 2017-08-15; 2018-06-18; 2023-10-31.
“ds…” means that variable is from Datastream / LPC DealScan.
“cs…” means that variable is from Compustat.
“gvkey”: the unique identifier in Compustat.
Link Table Preview
An example for company Aeroflex Inc. (previously known as ARX Inc.), which was acquired by Cobham Plc (LoanConnector Company ID = 22552).
Code
Link_table %>%filter(`LoanConnector Company ID`%in%c(31224, 8532)) %>%arrange(facilitystartdate) %>%gt() %>%tab_header(title ="Link table for Aeroflex Inc. (previously known as ARX Inc.)") %>%tab_options(table.font.size =10, heading.align ='left' ) %>%cols_hide(columns =matches("score|match") ) # hide columns
Link table for Aeroflex Inc. (previously known as ARX Inc.)
facilityid
facilitystartdate
gvkey
borrowercompanyid
ds_company
cs_company
ds_ticker
cs_ticker
Company Name
LoanConnector Company ID
5171
1989-04-24
1056
2463
ARX Inc
ARX INC
NA
ARX
ARX Inc
31224
10226
1991-10-10
1056
2463
ARX Inc
ARX INC
NA
ARX
ARX Inc
31224
10227
1991-10-10
1056
2463
ARX Inc
ARX INC
NA
ARX
ARX Inc
31224
27968
1994-04-11
1056
2463
ARX Inc
ARX INC
NA
ARX
ARX Inc
31224
27969
1994-04-11
1056
2463
ARX Inc
ARX INC
NA
ARX
ARX Inc
31224
62943
1996-03-15
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARX
Aeroflex Inc
8532
62944
1996-03-15
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARX
Aeroflex Inc
8532
67177
1999-02-25
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARX
Aeroflex Inc
8532
67178
1999-02-25
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARX
Aeroflex Inc
8532
67179
1999-02-25
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARX
Aeroflex Inc
8532
145204
2003-02-14
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
145320
2003-02-14
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
194507
2006-03-21
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
214917
2007-04-13
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
214918
2007-04-13
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
214919
2007-04-13
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
219384
2007-08-15
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
221951
2007-08-15
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
223289
2007-08-15
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
269533
2011-05-09
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
269534
2011-05-09
1056
30274
Aeroflex Inc
AEROFLEX INC
NA
ARXX
Aeroflex Inc
8532
303619
2013-05-28
184639
30274
Aeroflex Inc
AEROFLEX HOLDING CORP
NA
ARX
Aeroflex Inc
8532
304052
2013-05-28
184639
30274
Aeroflex Inc
AEROFLEX HOLDING CORP
NA
ARX
Aeroflex Inc
8532
Code
Link_table %>%filter(grepl(pattern ="Cobham", x = ds_company, ignore.case = T)) %>%gt() %>%tab_header(title ="Link table for Cobham Plc (`LoanConnector Company ID = 22552`)") %>%tab_options(table.font.size =10, heading.align ='left' ) %>%cols_hide(columns =matches("score|match") ) # hide columns
Link table for Cobham Plc (`LoanConnector Company ID = 22552`)
facilityid
facilitystartdate
gvkey
borrowercompanyid
ds_company
cs_company
ds_ticker
cs_ticker
Company Name
LoanConnector Company ID
33791
1995-07-28
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
74419
1999-12-03
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
74420
1999-12-03
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
129861
2001-02-12
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
143185
2003-01-09
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
143186
2003-01-09
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
183355
2005-07-26
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
229489
2008-01-18
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
243812
2009-03-05
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
243813
2009-03-05
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
278758
2011-11-08
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
322715
2014-05-20
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
342066
2015-05-12
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
342067
2015-05-12
100794
19896
Cobham Plc
COBHAM PLC
COB
NA
Cobham Plc
22552
379111
2011-12-31
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
379112
2012-12-31
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
379113
2014-12-31
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
379114
2012-12-31
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
397120
2017-12-01
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
397122
2017-12-01
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
397123
2017-12-01
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
437247
2020-01-20
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
437252
2020-01-20
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
438535
2020-01-20
100794
19896
Cobham Plc
COBHAM PLC
COB
None
Cobham Plc
22552
S4. Future Notes
Use the “Link_table_2024.RData” data file for variable Link_table to link the DealScan data with Compustat data in the next step.
The next step will be to potentially expand the link table to a wider stock universe, other than the North American sample covered in Compustat.
---title: "DealScan Link Table (Compustat)"author: "Hongyi Xu"date: today format: html: theme: flatly toc: true code-fold: true code-tools: true code-summary: "Show the code" html-math-method: katex toc-location: left editor: visual---[> Back to research](https://hongyileoxu.github.io/research/)```{=html}<style type="text/css">body, td { font-size: 16px;}code.r{ font-size: 12px;}pre { font-size: 12px}</style>``````{r setup, include=FALSE}knitr::opts_chunk$set(echo =TRUE) setwd("~/Library/CloudStorage/OneDrive-HandelshögskolaniStockholm/Projects_2024/Dealscan_LinkTable/DealScan_Link_Table_Compustat_Jun2024")list.files(pattern =".xlsx")library(tidyverse)library(readxl)library(gt)```## S1. Objective {#sec-s1.-objective}File location: `r getwd()`.The object of this document is to create a new link table for the LPC LoanConnector DealScan database with the Compustat from WRDS.[WRDS-Reuters DealScan](https://wrds-www.wharton.upenn.edu/pages/get-data/thomson-reuters/wrds-reuters-dealscan/) has an [updated instruction](https://wrds-www.wharton.upenn.edu/pages/support/manuals-and-overviews/thomson-reuters/wrds-reuters-dealscan/wrds-overview-on-dealscan-loanconnector/) on the new and legacy DealScan data.## S2. Data sources {#sec-s2.-data-sources}- [LPC_Loanconnector_Company_ID_Mappings.xlsx](https://wrds-www.wharton.upenn.edu/pages/support/manuals-and-overviews/thomson-reuters/wrds-reuters-dealscan/): Translation between DealScan (Legacy) and LoanConnector DealScan and here is the translation table: - variable "LoanConnector Company ID": a unique identifier for the firm and matches "Borrower_Id" in the [LPC DealScan data](https://wrds-www.wharton.upenn.edu/pages/get-data/thomson-reuters/wrds-reuters-dealscan/dealscan/). - variable "LPC_COMPANY_ID": a unique identifier matches to the Legacy DealScan data.| | Table | Legacy ID variables | New ID Variables ||----------------------|-----------------|-----------------|-----------------|| Deal/Tranche to Package/Facility | WRDS_LOANCONNECTOR_IDS | WRDS_Package_ID, WRDS_Facility_ID | LoanConnector_Deal_ID, LoanConnector_Tranche_ID || New Lender_ID, Lender_Parent_ID, and Borrower_ID to legacy Company ID | LPC_LOANCONNECTOR_COMPANY_ID_MAP | LPC_Company_ID | LoanConnector_Company_ID |- [Dealscan-Compustat_Linking_Database012024.xlsx](https://wrds-www.wharton.upenn.edu/pages/get-data/macro-finance-society/dealscan-compustat-linking-database/): This linking database connects Legacy DealScan identifiers to Compustat identifiers from Chava and Roberts (2009). - variable ["borrowercompanyid"]{style="color: red;"}: this is the identifier that consistent with variable "borrowercompanyid" in the [WRDS-Reuters DealScan-Package-Legacy](https://wrds-www.wharton.upenn.edu/pages/get-data/thomson-reuters/wrds-reuters-dealscan/package/). - variable ["borrowercompanyid"]{style="color: red;"} also links to the variable "LPC_COMPANY_ID" in the [LPC_Loanconnector_Company_ID_Mappings.xlsx](https://wrds-www.wharton.upenn.edu/pages/support/manuals-and-overviews/thomson-reuters/wrds-reuters-dealscan/) (the first link table). - variable "gvkey": the unique identifier in the Compustat.- Therefore, the next step merges the first two link tables, which will link [LPC DealScan data](https://wrds-www.wharton.upenn.edu/pages/get-data/thomson-reuters/wrds-reuters-dealscan/dealscan/) and Compustat. \> i.e. "Borrower_Id" (DealScan) $\rightarrow$ <mark>"LoanConnector Company ID"</mark> (LinkTable) $\Leftrightarrow$ "LPC_COMPANY_ID" $\rightarrow$ "[borrowercompanyid]{style="color: red;"}" $\longrightarrow$ gvkey (Compustat).## S3. Merged Link Table (Compustat) {#sec-s3.-merged-link-table}```{r linktables, echo=TRUE, results='hide'}list.files(pattern =".xlsx")Roberts_link <-read_excel("Dealscan-Compustat_Linking_Database012024.xlsx", sheet =2)names(Roberts_link)LPC_link <-read_excel("LPC_Loanconnector_Company_ID_Mappings.xlsx", sheet =1)names(LPC_link)Link_table <- Roberts_link %>%left_join(LPC_link, by =c("borrowercompanyid"="LPC_COMPANY_ID")) dim(Link_table); names(Link_table)save(Link_table, file ="Link_table_2024.RData")Link_table2 <- Roberts_link %>%inner_join(LPC_link, by =c("borrowercompanyid"="LPC_COMPANY_ID"))dim(Link_table2); names(Link_table2)Link_table %>%filter(is.na(`LoanConnector Company ID`))````Link_table` bases on the [Dealscan-Compustat_Linking_Database012024.xlsx](https://wrds-www.wharton.upenn.edu/pages/get-data/macro-finance-society/dealscan-compustat-linking-database/), which has `r dim(Link_table)[1]` observations.`Link_table2` follows the same logic but only keeps the borrowing companies identified both by the variable "borrowercompanyid" in Roberts link table (i.e. "Dealscan-Compustat_Linking_Database012024.xlsx") and "LPC_COMPANY_ID" in the new-and-legacy LPC link table (i.e. "LPC_Loanconnector_Company_ID_Mappings.xlsx"). The number of observations after this merge is `r dim(Link_table2)[1]`.::: {.alert .alert-dismissible .alert-info}<buttontype="button"class="btn-close"data-bs-dismiss="alert"></button><strong>Heads up!</strong><ahref="#sec-s3.-merged-link-table"class="alert-link">The next step</a> is to match more observations in the LPC link table.:::### Definitions(Guessed) Definition of Variables:- Updated time: `r paste(sort(unique(Roberts_link$vintage_match)), collapse = "; ")`.- "ds..." means that variable is from Datastream / LPC DealScan.- "cs..." means that variable is from Compustat.- "gvkey": the unique identifier in Compustat.### Link Table PreviewAn example for company Aeroflex Inc. (previously known as ARX Inc.), which [was acquired](https://www.businesswire.com/news/home/20140519006808/en/Aeroflex-To-Be-Acquired-by-Cobham-plc-For-Approximately-1.46-Billion) by Cobham Plc (`LoanConnector Company ID = 22552`).```{r preview, echo=TRUE}#| column: pageLink_table %>%filter(`LoanConnector Company ID`%in%c(31224, 8532)) %>%arrange(facilitystartdate) %>%gt() %>%tab_header(title ="Link table for Aeroflex Inc. (previously known as ARX Inc.)") %>%tab_options(table.font.size =10, heading.align ='left' ) %>%cols_hide(columns =matches("score|match") ) # hide columns Link_table %>%filter(grepl(pattern ="Cobham", x = ds_company, ignore.case = T)) %>%gt() %>%tab_header(title ="Link table for Cobham Plc (`LoanConnector Company ID = 22552`)") %>%tab_options(table.font.size =10, heading.align ='left' ) %>%cols_hide(columns =matches("score|match") ) # hide columns ```## S4. Future Notes {#sec-s4.-future-notes}Use the [["Link_table_2024.RData"]{.underline}]{style="color: red;"} data file for variable `Link_table` to link the DealScan data with Compustat data in the next step.The next step will be to potentially expand the link table to a wider stock universe, other than the North American sample covered in Compustat.## Reference {#sec-reference}- Chava, S., Roberts, M.R., 2008. How Does Financing Impact Investment? The Role of Debt Covenants. The Journal of Finance 63, 2085--2121. <https://doi.org/10.1111/j.1540-6261.2008.01391.x>- [HTML Themes](https://quarto.org/docs/output-formats/html-themes.html#dark-mode)<!-- ## Notes --><!-- color: <span style="color: red;"> text </span> // {style="color: red;"} --><!-- ## Quarto --><!-- Quarto enables you to weave together content and executable code into a finished document. To learn more about Quarto see <https://quarto.org>. --><!-- ## Running Code --><!-- When you click the **Render** button a document will be generated that includes both content and the output of embedded code. You can embed code like this: --><!-- ```{r} --><!-- 1 + 1 --><!-- ``` --><!-- You can add options to executable code like this --><!-- ```{r} --><!-- #| echo: false --><!-- 2 * 2 --><!-- ``` --><!-- The `echo: false` option disables the printing of code (only output is displayed). -->