API tools faq
paste
Guest User

Untitled

a guest
Jul 31st, 2018
103
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.30 KB | None | 0 0
raw download clone embed print report
  1. ## AUTHOR: Reginald Edwards
  2. ## CREATED: 31 July 2018
  3. ## DESCRIPTION: Query Compustat data from WRDS
  4.  
  5. library(dplyr)
  6. library(RPostgres)
  7. source('../0_datasets/wrds_login.R')
  8. wrds <- dbConnect(Postgres(),
  9. host='wrds-pgdata.wharton.upenn.edu',
  10. port=9737,
  11. user=wrds_user,
  12. password=wrds_pw,
  13. sslmode='require',
  14. dbname='wrds')
  15.  
  16. res <- dbSendQuery(wrds,
  17. "SELECT * from comp.funda where indfmt = 'INDL'
  18. and datafmt = 'STD'
  19. and popsrc = 'D'
  20. and consol = 'C'
  21. and final = 'Y'")
  22. comp.funda <- dbFetch(res)
  23. dbClearResult(res)
  24. save(comp.funda, file = '0_datasets/comp_funda.RData')
  25.  
  26. ###############################################################################
  27. ## Compute Annual Financial Ratios
  28. ###############################################################################
  29.  
  30. keep.vars <- c("gvkey", "datadate", "fyear", "fyr", "tic", "prcc_f", "seq",
  31. "ceq", "txditc", "txdb", "itcb", "pstkrv", "pstkl", "pstk",
  32. "prcc_f", "csho",
  33. "epsfx", "epsfi", "oprepsx", "opeps", "ajex", "ebit", "spi",
  34. "nopi", "sale",
  35. "ibadj", "dvc", "dvp", "ib", "oibdp", "dp", "oiadp", "gp",
  36. "revt", "cogs",
  37. "pi", "ibc", "dpc", "at", "ni", "ibcom", "icapt", "mib",
  38. "ebitda", "xsga",
  39. "xido", "xint", "mii", "ppent", "act", "lct", "dltt", "dlc",
  40. "che", "invt",
  41. "lt", "rect", "xopr", "oancf", "txp", "txt", "ap", "xrd",
  42. "xad", "xlr", "capx")
  43.  
  44. comp.funda <- comp.funda[keep.vars]
  45. comp.funda <- comp.funda[order(comp.funda$gvkey, comp.funda$fyear), ]
  46.  
  47. ## Data filters
  48. comp.funda <- comp.funda[comp.funda$at > 10, ]
  49. comp.funda <- comp.funda[comp.funda$seq > 0, ]
  50. comp.funda <- comp.funda[which(comp.funda$sale > 0), ]
  51. comp.funda <- comp.funda[which(comp.funda$prcc_f > 2), ]
  52.  
  53. ## label first year/observation for each firm (gvkey)
  54. first.fyear <- aggregate(comp.funda$fyear, by = list(comp.funda$gvkey), FUN = min)
  55. names(first.fyear) <- c('gvkey', 'fyear')
  56. first.fyear$first <- 1
  57. comp.funda <- merge(comp.funda, first.fyear, all.x = TRUE)
  58. comp.funda$first <- ifelse(is.na(comp.funda$first), 0, comp.funda$first)
  59.  
  60. # year gap between consecutive records
  61. comp.funda$fyear.lag1 <- lag(comp.funda$fyear)
  62. comp.funda$fyear.lag1 <- ifelse(comp.funda$first == 1, NA, comp.funda$fyear.lag1)
  63. comp.funda$gap <- comp.funda$fyear - comp.funda$fyear.lag1
  64.  
  65. ## create market value variable
  66. comp.funda$mktval <- comp.funda$prcc_f*comp.funda$csho
  67.  
  68. # set assets (at) and revenue (sale) to missing if they are negative
  69. comp.funda$at <- ifelse(comp.funda$at <= 0, NA, comp.funda$at)
  70. comp.funda$sale <- ifelse(comp.funda$sale <= 0, NA, comp.funda$sale)
  71.  
  72. # preferred stock
  73. comp.funda$pstk_new <- dplyr::coalesce(comp.funda$pstkrv, comp.funda$pstkl, comp.funda$pstk)
  74.  
  75. # Shareholder's Equity
  76. x <- coalesce(comp.funda$txditc, rowSums(comp.funda[, c("txdb", "itcb")], na.rm = TRUE))
  77. comp.funda$be = rowSums( data.frame(comp.funda$seq, x, -comp.funda$pstk_new), na.rm = TRUE);
  78. comp.funda$be <- ifelse(comp.funda$be <= 0, NA, comp.funda$be)
  79.  
  80. # book-to-market
  81. comp.funda$bm <- comp.funda$be/(comp.funda$mktval)
  82. comp.funda$bm <- ifelse(comp.funda$bm <= 0, NA, comp.funda$bm)
  83.  
  84. # invested capital
  85. comp.funda$icapt <- coalesce(comp.funda$icapt, rowSums(comp.funda[ , c("dltt", "pstk", "mib", "ceq")], na.rm = TRUE))
  86.  
  87. # operating cash-flow
  88. comp.funda$ch_act <- c(NA, diff(comp.funda$act))
  89. comp.funda$ch_che <- c(NA, diff(comp.funda$che))
  90. comp.funda$ch_lct <- c(NA, diff(comp.funda$lct))
  91. comp.funda$ch_dlc <- c(NA, diff(comp.funda$dlc))
  92. comp.funda$ch_txp <- c(NA, diff(comp.funda$txp))
  93. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "ch_act"] <- NA
  94. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "ch_che"] <- NA
  95. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "ch_lct"] <- NA
  96. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "ch_dlc"] <- NA
  97. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "ch_txp"] <- NA
  98.  
  99. comp.funda$ocf <- coalesce(comp.funda$oancf,
  100. comp.funda$ib - (comp.funda$ch_act - comp.funda$ch_che - comp.funda$ch_lct +
  101. comp.funda$ch_dlc + comp.funda$ch_txp),
  102. -comp.funda$dp)
  103. ## remove lagged generated variable if it is the first year/observation
  104. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "ocf"] <- NA
  105.  
  106.  
  107. #### Annual Valuation Ratios
  108. # Enterprise Value Multiple:
  109. # (market value of common stock + market value of preferred equity + market
  110. # value of debt + minority interest - cash and investments) divided by EBITDA
  111. comp.funda$evm <- rowSums(comp.funda[, c("dltt", "dlc", "mib", "pstk_new",
  112. "mktval")], na.rm = TRUE)/coalesce(comp.funda$ebitda, comp.funda$oibdp, comp.funda$sale -
  113. comp.funda$cogs - comp.funda$xsga)
  114.  
  115. # price-to-operating EPS, excl. EI (basic)
  116. comp.funda$pe_op_basic <- comp.funda$prcc_f/(comp.funda$opeps/comp.funda$ajex)
  117.  
  118. # price-to-operating EPS, excl. EI (diluted)
  119. comp.funda$pe_op_dil <- comp.funda$prcc_f/(comp.funda$oprepsx/comp.funda$ajex)
  120.  
  121. # price-to-earnings, excl. EI (diluted)
  122. comp.funda$pe_exi <- comp.funda$prcc_f/(comp.funda$epsfx/comp.funda$ajex)
  123.  
  124. # price-to-earnings, incl. EI (diluted)
  125. comp.funda$pe_inc <- comp.funda$prcc_f/(comp.funda$epsfi/comp.funda$ajex)
  126.  
  127. # price-to-sales ratio
  128. comp.funda$ps <- comp.funda$prcc_f/comp.funda$sale
  129.  
  130. # price-to-cash flow
  131. comp.funda$pcf <-comp.funda$prcc_f/comp.funda$ocf
  132.  
  133. # dividend payout ratio
  134. comp.funda$dpr <- ifelse(comp.funda$ibadj > 0, comp.funda$dvc/comp.funda$ibadj, 0)
  135.  
  136. ###############################################################################
  137. ## Profitability Ratios and Rates of Return
  138.  
  139. # net profit margin
  140. comp.funda$npm <- comp.funda$ib/comp.funda$sale
  141.  
  142. # operating profit margin before depreciation
  143. comp.funda$opmbd <- coalesce(comp.funda$oibdp, comp.funda$sale -
  144. comp.funda$xopr, comp.funda$revt - comp.funda$xopr)/comp.funda$sale
  145.  
  146. # operating profit margin after depreciation
  147. comp.funda$opmad <- coalesce(comp.funda$oiadp, comp.funda$oibdp - comp.funda$dp,
  148. comp.funda$sale - comp.funda$xopr - comp.funda$dp, comp.funda$revt -
  149. comp.funda$xopr - comp.funda$dp)/comp.funda$sale;
  150.  
  151. # gross profit margin
  152. comp.funda$gpm <- coalesce(comp.funda$gp, comp.funda$revt - comp.funda$cogs,
  153. comp.funda$sale - comp.funda$cogs)/comp.funda$sale
  154.  
  155. # pretax profit margin
  156. comp.funda$ptpm <- coalesce(comp.funda$pi, comp.funda$oiadp - comp.funda$xint +
  157. comp.funda$spi + comp.funda$nopi)/comp.funda$sale
  158.  
  159. # cash flow margin
  160. comp.funda$cfm <- coalesce(comp.funda$ibc + comp.funda$dpc, comp.funda$ib +
  161. comp.funda$dp)/comp.funda$sale
  162.  
  163. # Return on Assets
  164. #comp.funda$roa <- coalesce(comp.funda$oibdp, comp.funda$sale - comp.funda$xopr,
  165. # comp.funda$revt - comp.funda$xopr)/((comp.funda$at+lag(comp.funda$at))/2)
  166. comp.funda$roa <- comp.funda$ib/((comp.funda$at+lag(comp.funda$at))/2)
  167. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "roa"] <- NA
  168.  
  169. # Return on Equity
  170. comp.funda$roe <- comp.funda$ib/((comp.funda$be+lag(comp.funda$be))/2)
  171. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "roe"] <- NA
  172.  
  173. # Return on Capital Employed
  174. comp.funda$roce <- coalesce(comp.funda$ebit, comp.funda$sale -
  175. comp.funda$cogs - comp.funda$xsga-comp.funda$dp)/((comp.funda$dltt +
  176. lag(comp.funda$dltt) + comp.funda$dlc + lag(comp.funda$dlc) +
  177. comp.funda$ceq + lag(comp.funda$ceq))/2)
  178. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "roce"] <- NA
  179.  
  180. # effective tax rate
  181. comp.funda$efftax <- pmax(0, comp.funda$txt/coalesce(comp.funda$pi,
  182. comp.funda$oiadp - comp.funda$xint + comp.funda$spi + comp.funda$nopi))
  183.  
  184. # after tax return on average common equity
  185. comp.funda$aftret_eq <- coalesce(comp.funda$ibcom,comp.funda$ib -
  186. comp.funda$dvp)/((comp.funda$ceq+lag(comp.funda$ceq))/2)
  187. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1),
  188. "aftret_eq"] <- NA
  189.  
  190. # after tax return on invested capital
  191. comp.funda$aftret_invcapx <- rowSums(comp.funda[, c("ib", "xint", "mii")], na.rm = TRUE)/
  192. lag((comp.funda$icapt + comp.funda$txditc - comp.funda$mib))
  193. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1),
  194. "aftret_invcapx"] <- NA
  195.  
  196. # after tax return on total stock holder's equity
  197. comp.funda$aftret_equity <- comp.funda$ib/((comp.funda$seq +
  198. lag(comp.funda$seq))/2)
  199. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "aftret_equity"] <- NA
  200.  
  201. # pretax return on net operating assets
  202. comp.funda$pretret_noa <- coalesce(comp.funda$oiadp, comp.funda$oibdp -
  203. comp.funda$dp, comp.funda$sale - comp.funda$xopr - comp.funda$dp,
  204. comp.funda$revt - comp.funda$xopr - comp.funda$dp)/((lag(comp.funda$ppent +
  205. comp.funda$act - comp.funda$lct) + (comp.funda$ppent + comp.funda$act -
  206. comp.funda$lct))/2)
  207. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "pretret_noa"] <- NA
  208.  
  209. # pretax return on total earning assets
  210. comp.funda$pretret_earnat <- coalesce(comp.funda$oiadp, comp.funda$oibdp -
  211. comp.funda$dp, comp.funda$sale - comp.funda$xopr - comp.funda$dp,
  212. comp.funda$revt - comp.funda$xopr - comp.funda$dp)/((lag(comp.funda$ppent +
  213. comp.funda$act) + (comp.funda$ppent + comp.funda$act))/2)
  214. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1),
  215. "pretret_earnat"] <- NA
  216.  
  217. # gross profitability as % of total assets
  218. comp.funda$gprof <- coalesce(comp.funda$gp, comp.funda$revt - comp.funda$cogs,
  219. comp.funda$sale - comp.funda$cogs)/comp.funda$at
  220.  
  221. ## Capitalization ratios
  222. # Common Equity as % of invested capital
  223. comp.funda$equity_invcap <- comp.funda$ceq/comp.funda$icapt
  224.  
  225. # Long-term debt as % of invested capital
  226. comp.funda$debt_invcap <- comp.funda$dltt/comp.funda$icapt
  227.  
  228. # Total Debt as % of invested capital
  229. comp.funda$totdebt_invcap <- (comp.funda$dltt + comp.funda$dlc)/comp.funda$icapt
  230.  
  231. # capitaliz comp.funda$ation ratio
  232. comp.funda$capital_ratio <- comp.funda$dltt/(comp.funda$dltt +
  233. rowSums(comp.funda[, c("ceq", "pstk_new")], na.rm = TRUE))
  234.  
  235. ## Financial Soundness ratios
  236. # interest as % of average long-term debt
  237. comp.funda$int_debt <- comp.funda$xint/((comp.funda$dltt + lag(comp.funda$dltt))/2)
  238. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "int_debt"] <- NA
  239.  
  240. # interest as % of average total debt
  241. comp.funda$int_totdebt <- comp.funda$xint/((comp.funda$dltt +
  242. lag(comp.funda$dltt) + comp.funda$dlc + lag(comp.funda$dlc))/2)
  243. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "int_totdebt"] <- NA
  244.  
  245. # Cash balance to Total Liabilities
  246. comp.funda$cash_lt <- comp.funda$che/comp.funda$lt
  247.  
  248. # inventory as % of current assets
  249. comp.funda$invt_act <- comp.funda$invt/comp.funda$act
  250.  
  251. # receivables as % of current assets
  252. comp.funda$rect_act <- comp.funda$rect/comp.funda$act
  253.  
  254. # total debt as % of total assets
  255. comp.funda$debt_at <- (comp.funda$dltt + comp.funda$dlc)/comp.funda$at
  256.  
  257. # gross debt to ebitda
  258. comp.funda$debt_ebitda <- (comp.funda$dltt + comp.funda$dlc)/
  259. coalesce(comp.funda$ebitda, comp.funda$oibdp, comp.funda$sale -
  260. comp.funda$cogs - comp.funda$xsga)
  261.  
  262. # short term term as % of total debt
  263. comp.funda$short_debt <- comp.funda$dlc/(comp.funda$dltt + comp.funda$dlc)
  264.  
  265. # current liabilities as % of total liabilities
  266. comp.funda$curr_debt <- comp.funda$lct/comp.funda$lt
  267.  
  268. # long-term debt as % of total liabilities
  269. comp.funda$lt_debt <- comp.funda$dltt/comp.funda$lt
  270.  
  271. # profit before D&A to current liabilities
  272. comp.funda$profit_lct <- coalesce(comp.funda$oibdp, comp.funda$sale - comp.funda$xopr)/comp.funda$lct
  273.  
  274. # operating cash flow to current liabilities
  275. comp.funda$ocf_lct <- comp.funda$ocf/comp.funda$lct
  276.  
  277. # operating cash flow to total debt
  278. comp.funda$cash_debt <- comp.funda$ocf/coalesce(comp.funda$lt, comp.funda$dltt + comp.funda$dlc)
  279.  
  280. # Free Cash Flow/Operating Cash Flow
  281. comp.funda$fcf_ocf <- pmax(0, (comp.funda$ocf - comp.funda$capx)/comp.funda$ocf)
  282.  
  283. # total liabilities to total tangible assets
  284. comp.funda$lt_ppent <- comp.funda$lt/comp.funda$ppent
  285. comp.funda$dltt_be <- comp.funda$dltt/comp.funda$be # long-term debt to book equity
  286.  
  287. ## Solvency ratios
  288. # Debt-to-assets
  289. comp.funda$liabs_assets <- comp.funda$lt/comp.funda$at
  290.  
  291. # debt-to-capital
  292. comp.funda$debt_capital <- (comp.funda$ap + (comp.funda$dlc +
  293. comp.funda$dltt))/(comp.funda$ap + (comp.funda$dlc + comp.funda$dltt) +
  294. (comp.funda$ceq + comp.funda$pstk_new))
  295.  
  296. # debt to shareholders' equity ratio
  297. comp.funda$de_ratio <- comp.funda$lt/(comp.funda$ceq + comp.funda$pstk_new)
  298.  
  299. # after tax interest coverage
  300. comp.funda$intcov <- (comp.funda$xint + comp.funda$ib)/comp.funda$xint
  301.  
  302. # interest coverage ratio
  303. comp.funda$intcov_ratio <- coalesce(comp.funda$ebit, comp.funda$oiadp,
  304. comp.funda$sale - comp.funda$cogs - comp.funda$xsga - comp.funda$dp)/
  305. comp.funda$xint
  306.  
  307. ## Liquidity ratios
  308. # cash ratio
  309. comp.funda$cash_ratio <- comp.funda$che/comp.funda$lct
  310. comp.funda$cash_ratio <- ifelse(comp.funda$lct > 0, comp.funda$cash_ratio, NA)
  311.  
  312. # quick ratio (acid test)
  313. comp.funda$quick_ratio <- coalesce(comp.funda$act - comp.funda$invt,
  314. comp.funda$che + comp.funda$rect)/comp.funda$lct
  315. comp.funda$quick_ratio <- ifelse(comp.funda$lct > 0, comp.funda$quick_ratio, NA)
  316.  
  317. # current ratio
  318. comp.funda$curr_ratio <- coalesce(comp.funda$act, comp.funda$che +
  319. comp.funda$rect + comp.funda$invt)/comp.funda$lct
  320. comp.funda$curr_ratio <- ifelse(comp.funda$lct > 0, comp.funda$curr_ratio, NA)
  321.  
  322. # cash conversion cycle
  323. comp.funda$cash_conversion <- ((comp.funda$invt + lag(comp.funda$invt))/2)/
  324. (comp.funda$cogs/365) + ((comp.funda$rect + lag(comp.funda$rect))/2)/
  325. (comp.funda$sale/365) - ((comp.funda$ap + lag(comp.funda$ap))/2)/
  326. (comp.funda$cogs/365)
  327. comp.funda$cash_conversion <- ifelse(comp.funda$cash_conversion < 0, NA,
  328. comp.funda$cash_conversion)
  329. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "cash_conversion"] <- NA
  330.  
  331. ## Activity/Efficiency ratios
  332. # inventory turnover
  333. comp.funda$inv_turn <- comp.funda$cogs/((comp.funda$invt + lag(comp.funda$invt))/2)
  334. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "inv_turn"] <- NA
  335.  
  336. # asset turnover
  337. comp.funda$at_turn <- comp.funda$sale/((comp.funda$at + lag(comp.funda$at))/2)
  338. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "at_turn"] <- NA
  339.  
  340. # receivables turnover
  341. comp.funda$rect_turn <- comp.funda$sale/((comp.funda$rect + lag(comp.funda$rect))/2)
  342. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "rect_turn"] <- NA
  343.  
  344. # payables turnover
  345. comp.funda$pay_turn <- (comp.funda$cogs + c(NA, diff(comp.funda$invt)))/((comp.funda$ap + lag(comp.funda$ap))/2)
  346. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "pay_turn"] <- NA
  347.  
  348. # Miscallenous ratios
  349. # sale per $ invested capital
  350. comp.funda$sale_invcap <- pmax(0, comp.funda$sale/comp.funda$icapt)
  351.  
  352. # sales per $ total stockholders' equity
  353. comp.funda$sale_equity <- comp.funda$sale/comp.funda$seq
  354.  
  355. # sales per $ working capital
  356. comp.funda$sale_nwc <- pmax(0, comp.funda$sale/(comp.funda$act - comp.funda$lct))
  357.  
  358. # R&D as % of comp.funda$sales
  359. comp.funda$xrd <- ifelse(is.na(comp.funda$xrd), 0, comp.funda$xrd)
  360. comp.funda$rd_sale <- comp.funda$xrd/comp.funda$sale
  361.  
  362. # advertising as % of comp.funda$sales
  363. comp.funda$xad <- ifelse(is.na(comp.funda$xad), 0, comp.funda$xad)
  364. comp.funda$adv_sale <- comp.funda$xad/comp.funda$sale
  365.  
  366. # labor expense as % of comp.funda$sales
  367. comp.funda$staff_sale <- pmax(comp.funda$xlr, 0)/comp.funda$sale
  368.  
  369. ## Accruals
  370. comp.funda$avg_at <- (comp.funda$at + lag(comp.funda$at))/2
  371. x <- -(comp.funda$ch_act - comp.funda$ch_che - comp.funda$ch_lct + comp.funda$ch_dlc +
  372. comp.funda$ch_txp - comp.funda$dp)
  373. comp.funda$accruals <- coalesce(comp.funda$oancf - comp.funda$ib, x)/(comp.funda$avg_at)
  374. comp.funda[which(comp.funda$first == 1 | comp.funda$gap != 1), "accruals"] <- NA
  375.  
  376. ## Schiller's capei
  377. comp.funda$capei <- ((comp.funda$ib + lag(comp.funda$ib, 2) + lag(comp.funda$ib, 3) + lag(comp.funda$ib, 4) + lag(comp.funda$ib, 5))/5)/comp.funda$prcc_f
  378.  
  379. o <- order(comp.funda$gvkey, comp.funda$datadate, comp.funda$fyr)
  380. comp.funda <- comp.funda[o, ]
  381. rm(o)
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!