Apple Example

Data - Compustat

~/data/compustat/

Load Compustat Using PostgreSQL

wrds <- dbConnect(Postgres(), 
                  host='wrds-pgdata.wharton.upenn.edu',
                  port=9737,
                  user=username,
                  password=password,
                  sslmode='require',
                  dbname='wrds')

List all datasets

Available here: https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm

cusip:

• in Compustat, 9-digit CUSIP (last digit is checksum)
• in CRSP, 8-digit CUSIP

List of libraries

res <- dbSendQuery(wrds, "select distinct table_schema
                   from information_schema.tables
                   where table_type ='VIEW'
                   or table_type ='FOREIGN TABLE'
                   order by table_schema")
list_libraries <- dbFetch(res, n=-1)
dbClearResult(res)
list_libraries %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

List datasets

list_datasets_crsp <- dbSendQuery(wrds, "select distinct table_name
                   from information_schema.columns
                   where table_schema='crsp'
                   order by table_name") %>%
  dbFetch

list_datasets_crsp %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

list_datasets_comp <- dbSendQuery(wrds, "select distinct table_name
                   from information_schema.columns
                   where table_schema='comp'
                   order by table_name") %>%
  dbFetch

list_datasets_comp %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

List variables

list_variables_crsp_msf <- dbSendQuery(wrds, "select column_name
                   from information_schema.columns
                   where table_schema='crsp'
                   and table_name='msf'
                   order by column_name") %>%
  dbFetch
list_variables_crsp_msf %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

list_variables_comp_funda <- dbSendQuery(wrds, "select column_name
                   from information_schema.columns
                   where table_schema='comp'
                   and table_name='funda'
                   order by column_name") %>%
  dbFetch
list_variables_comp_funda %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

list_variables_comp_funda_fncd <- dbSendQuery(wrds, "select column_name
                   from information_schema.columns
                   where table_schema='comp'
                   and table_name='funda_fncd'
                   order by column_name") %>%
  dbFetch
list_variables_comp_funda_fncd  %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

List of datasets

compustat_AAPL <- dbSendQuery(wrds, "select *
                        from comp.funda
                        where tic = 'AAPL' and DATAFMT='STD' and POPSRC='D' and CONSOL='C'
                        and FIC='USA'") %>%
  dbFetch


crsp_AAPL <- dbSendQuery(wrds, "select cusip, permno, permco, date, prc, vol
                                from crsp.msf
                                where cusip='03783310' and date between '1975-01-01' and '2018-12-31'") %>%
  dbFetch

Aggregates

compustat <- dbSendQuery(wrds, "select gvkey, conm, datadate, fyear, tic, emp, at, revt, act, xsga, cogs, ppegt, ppent, xrd, ebitda, ebit
                        from comp.funda
                        where FYEAR='2017' and DATAFMT='STD' and POPSRC='D' and CONSOL='C'
                        and FIC='USA'") %>%
  dbFetch

compustat %>%
  select(emp, at, revt, act, xsga, cogs, ppegt, ppent, xrd, ebitda, ebit) %>%
  summarise_all(funs(sum(., na.rm = TRUE))) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

Therefore:

• emp: 45 Million employees in compustat
• at: 85 Trillion ASSETS
• revt: 19 Trillion
• act: 5.8 Trillion
• xsga: 2.45 Trillion
• cogs: 10.56 Trillion
• ppegt: Property, Plant and Equipment – Total (Gross) (ppegt): 13.5 Trillion
• ppent: Property, Plant and Equipment – Total (Net): 8.2 Trillion
• xrd: Research and Development: 374 billion
• ebitda: 3.15 Trillion
• ebit: Earnings Before Interest and Taxes (ebit): 2.3 Trillion

Loading Relevant Datasets

List of variables for Apple:

Apple has approximately 5 billion shares

$ 156 dollar per shares. Therefore it’s 5*156 = 780 Billion:

• emp: 132 – 132,000 Employees
• at: 365725 – 365 Billion Total Assets
• revt: 265359 – 265 Billion Total Revenue
• act: 131339 – in Millions
• xsga: 30941 – Selling General and Administrative (xsga): 30 Billion
• cogs: Cost of goods sold (cogs): 152 Billion
• ppegt: Property, Plant and Equipment – Total (Gross) (ppegt): 41 billion
• ppent: Property, Plant and Equipment – Total (Net): 41 billion
• xrd: Research and Development: 14 billion
• ebitda: Earnings Before Interest (ebitda): 81 billion
• ebit: Earnings Before Interest and Taxes (ebit): 70 billion
• cshfd: 5000.109 – Common Shares Used to Calc Earnings Per Share - Fully Diluted
• cshi: 5000.109 – 5 Billion shares? – Common Shares Used to Calc Earnings Per Share - Fully Diluted (Millions?)
• epspi: Earnings Per Share (Basic) - Including Extraordinary Items

list.variables <- c("fyear", "emp", "at", "revt", "act", "xsga", "cogs", 
                    "ppegt", "ppent", "xrd", "ebitda", "ebit", "cshfd", 
                    "epspi", "epspx", "mkvalt", "cshi", "csho")
compustat_AAPL %>%
  select(list.variables) %>% 
  filter(fyear >= 2016) %>%
  t()

#              [,1]       [,2]        [,3]       [,4]       [,5]
# fyear    2016.000   2017.000    2018.000   2019.000    2020.00
# emp       116.000    123.000     132.000    137.000     147.00
# at     321686.000 375319.000  365725.000 338516.000  323888.00
# revt   215091.000 229234.000  265359.000 260174.000  274515.00
# act    106869.000 128645.000  131339.000 162819.000  143713.00
# xsga    24239.000  26842.000   30941.000  34462.000   38668.00
# cogs   121576.000 131648.000  152853.000 149235.000  158503.00
# ppegt   61245.000  75076.000   90403.000  95957.000  112096.00
# ppent   27010.000  33783.000   41304.000  37378.000   45336.00
# xrd     10045.000  11581.000   14236.000  16217.000   18752.00
# ebitda  69276.000  70744.000   81565.000  76477.000   77344.00
# ebit    59476.000  61344.000   70662.000  63930.000   66288.00
# cshfd    5500.281   5251.692    5000.109   4648.913   17528.21
# epspi       8.350      9.270      12.010     11.970       3.31
# epspx       8.350      9.270      12.010     11.970       3.31
# mkvalt 603253.566 790050.098 1073390.540 995151.567 1966078.92
# cshi     5336.166   5126.201    4754.986   4443.236   16976.76
# csho     5336.166   5126.201    4754.986   4443.236   16976.76

compustat_variable_names %>%
  filter(V1 %in% list.variables) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

Compustat Variables

compustat_variable_names %>%
  filter(grepl("Share", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

Property, Plant, and Equipment

compustat_variable_names %>%
  filter(grepl("Property, Plant, and Equipment", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

Profit

compustat_variable_names %>%
  filter(grepl("Profit", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

Revenue

compustat_variable_names %>%
  filter(grepl("Revenue", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

compustat_variable_names %>%
  filter(grepl("Market", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

compustat_variable_names %>%
  filter(grepl("Shares", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}

Sales

compustat_variable_names %>%
  filter(grepl("Sales", V2)) %>%
  arrange(V2) %>%
  {if (is_html_output()) datatable(., filter = 'top', rownames = F) else .}