import pandas as pd  
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

plt.style.use('fivethirtyeight')

%matplotlib inline
%config InlineBackend.figure_format = 'retina'

!pip install wrds

import wrds

# Now I am connecting to WRDS site - you must have a WRDS account user id and password
conn=wrds.Connection()

help(wrds)

#listing all dtabases or libraries in WRDS
conn.list_libraries()

help(conn.list_libraries)

help(conn.list_tables)

conn.list_tables(library='comp')

help(conn.describe_table)

compustat_variables=conn.describe_table('comp', 'funda')
print(45 * "-")
print(type(compustat_variables))
print(45 * "-")
print(compustat_variables)
print(45 * "-")
print(compustat_variables.shape)
print(45 * "-")
print(compustat_variables.info())
print(45 * "-")      
compustat_variables.head(20)

help(conn.get_row_count)

conn.get_row_count('comp', 'funda')

help(conn.get_table)

conn.get_table(library='comp', table='funda', 
               columns = ['conm', 'gvkey', 'cusip','cik', 'fyear', 'consol', 'tic', 'at', 'ni', 'prcc_f'], obs=20)

subset = conn.get_table(library='comp', table='funda', 
               columns = ['conm', 'gvkey', 'cusip','cik', 'fyear', 'consol', 'tic', 'at', 'ni', 'prcc_f', 'che', 'ebit'])
subset2=subset[subset["tic"] == 'TSLA']
subset2

print(conn.describe_table('comp', 'company'))

print(type(conn.get_table(library='comp', table='company', 
               columns = ['conm', 'gvkey', 'cik'], obs=10)))
print(45 * "-")
conn.get_table(library='comp', table='company', 
               columns = ['conm', 'gvkey', 'cik', 'busdesc', 'dlrsn', 'ipodate'], obs=10)

help(conn.raw_sql)

apple = conn.raw_sql("""select permno, date, prc, ret, shrout 
                        from crsp.msf 
                        where permno = 14593
                        and date>='01/01/2020'""", 
                     date_cols=['date'])
apple

apple_fund = conn.raw_sql("""select a.gvkey, a.iid, a.datadate, a.tic, a.conm,
                            a.at, b.prccm, b.cshoq 
                            
                            from comp.funda a 
                            inner join comp.secm b 
                            
                            on a.gvkey = b.gvkey
                            and a.iid = b.iid
                            and a.datadate = b.datadate 
                            
                            where a.tic = 'AAPL' 
                            and a.datadate>='01/01/1960'
                            and a.datafmt = 'STD' 
                            and a.consol = 'C' 
                            and a.indfmt = 'INDL'
                            """, date_cols=['datadate'])
print(apple_fund)
print(apple_fund.info())

data_directory = '/content/drive/My Drive/Colab Notebooks/data/'
!pwd

apple_fund.to_pickle(data_directory + "apple_fund.pkl")