Using Quandl Data





Kevin Crotty

Overview of Datasets

Sharadar Core US Equities Bundle

Core US Fundamentals Data (SHARADAR/SF1)

7 tables (preview of each)

  • SF1: main table of fundamentals
  • DAILY: daily metrics
  • TICKERS: header file with firm information
  • INDICATORS: list of fields & codes for each table
  • ACTIONS: M&A, dividends, bankruptcy, delisting, etc
  • SP500: index constitutents, additions, deletions
  • EVENTS: variety of corp. events (13D/G, Reg FD)

Core US Insiders Data (SHARADAR/SF2)

Insider holdings and transactions (preview)

  • SEC form 3 (initial statement of ownership)
  • SEC form 4 (changes in ownership)
  • SEC form 5 (deferred reporting of changes in ownership)

Core US Institutional Investors Data (SHARADAR/SF3)

3 tables of quarterly data from Form 13F (preview of each)

  • SF3: single line item for each combination of ticker, investor, quarter, security type
  • SF3A: summary stats aggregated by ticker and quarter
  • SF3B: summary stats aggregated by investor and quarter

Sharadar Equity Prices (SHARADAR/SEP)

5 tables (preview of each)

  • SEP: main table of equity prices
  • ACTIONS: M&A, dividends, bankruptcy, delisting, etc
  • TICKERS: header file with firm information
  • METRICS: betas, div yield, moving avgs, volume
  • INDICATORS: list of fields & codes for each table

Sharadar Fund Prices (SHARADAR/SFP)

5 tables (preview of each)

  • SFP: main table of ETF/ETN/CEF/ETD prices
  • ACTIONS: dividends, listing, ticker changes, splits, etc
  • TICKERS: header file with firm information
  • METRICS: betas, div yield, moving avgs, volume
  • INDICATORS: list of fields & codes for each table

Connecting to quandl

!pip install quandl
import quandl
quandl.ApiConfig.api_key = "YOUR-KEY-HERE"

Pulling data: quandl.get_table()

Pulling subsets of data

We can pull data from Quandl using quandl.get_table().

By default, pulls are limited to 10,000 records.

# Pull indicators file
df = quandl.get_table('SHARADAR/INDICATORS')

# Pull insider trades for single ticker
df = quandl.get_table('SHARADAR/SF2', ticker='AAPL')

# Pull prices for single ticker
df = quandl.get_table('SHARADAR/SEP', ticker='AAPL')
df.sort_values('date')

# Multiple tickers
df=quandl.get_table('SHARADAR/SF2',ticker=['IBM','AAPL'])

Fundamentals (SF1) filter options

  • ticker
  • calendardate (normalized report period)
  • lastupdated (when database entry updated)
  • dimension
    • AR=as reported vs. MR=most recent
    • ARY=annual; ARQ=Quarterly, ART=Trailing 12 months
  • datekey
    • SEC filing date for AR dimensions
    • Report period for MR dimensions

Insiders (SF2) filter options

  • ticker
  • filingdate
  • ownername
  • securityadcode
    • security acquired/disposed code
    • e.g., DA=derivative acquisition; ND=non-derivative disposition
  • transactionvalue

Institutional Investors (SF3) filter options

  • ticker
  • investorname
  • securitytype
    • [SHR] Common Shares
    • [CLL] Call Options
    • [PUT] Put Options
    • [DBT] Debt
  • calendardate (last day of calendar quarter)

Equity and fund prices (SEP/SFP) filter options

  • ticker
  • date
  • lastupdated

Subsetting on dates

# As of a date
df = quandl.get_table('SHARADAR/SF1', ticker='AAPL',
    calendardate='2015-12-31')

# After a date
df = quandl.get_table('SHARADAR/SF1', ticker='AAPL',
    calendardate={'gte':'2015-12-31'})

# Between dates
df = quandl.get_table('SHARADAR/SF1', ticker='AAPL',
    calendardate={'gte':'2015-12-31', 'lte':'2017-12-31'})

Dimensions of the data

df = quandl.get_table('SHARADAR/SF1', 
    dimension='ARY', 
    ticker='AAPL')

Subsetting columns

col_list = ['ticker', 'filingdate', 'transactiondate', 'formtype', 'issuername', 'ownername','officertitle', 'securityadcode', 'transactionshares', 'transactionpricepershare']
df = quandl.get_table('SHARADAR/SF2', 
    qopts={'columns':col_list},
    filingdate={'gte': '2015-12-31', 'lte': '2017-12-31'}, 
    ticker='AAPL')

Corporate actions

# Pull actions for set of tickers
df = quandl.get_table('SHARADAR/ACTIONS', 
    ticker=['IBM','AAPL','TSLA'])
df.action.value_counts()

# List of actions
df=quandl.get_table('SHARADAR/INDICATORS', table='ACTIONTYPES')

Corporate events

# events by ticker
df = quandl.get_table('SHARADAR/EVENTS', ticker=['IBM','AAPL','TSLA'])

# events by date
df = quandl.get_table('SHARADAR/EVENTS', 
    date={'gte':'2022-01-01'}, 
    ticker=['IBM','AAPL','TSLA'])

# List of events
df=quandl.get_table('SHARADAR/INDICATORS', table='EVENTCODES')

# 13D events
df = quandl.get_table('SHARADAR/EVENTS', ticker=['IBM','GME','TSLA','BBBY'])
df['activist']=(df.eventcodes.str.find('35')>=0)
df[df.activist==True]

Pulling full datasets

Using quandl.get_table()

By default, get_table is limited to 10,000 rows

# Pull >10,000 records
df = quandl.get_table('SHARADAR/SP500', paginate=True)

Pulling a zipped full dataset

# Downloading zipped tables
for table in ['INDICATORS','TICKERS','SF1','DAILY','SEP','SF2','SF3','SFP'] :
    quandl.export_table('SHARADAR/'+table)
    print("finished", table)

Unzipping and loading full dataset

from zipfile import ZipFile
table = "SF2"
with ZipFile('./SHARADAR_'+table+'.zip', 'r') as zipObj:
    name = zipObj.namelist()[0]
    zipObj.extractall()
df = pd.read_csv(name, low_memory=False)
df = df.rename(columns={"table": "table_"})