This python notebook generates the stock dataset. This requires data from the Financial Modelling Prep resource which requires an account, which under circumstances can be free of charge.
once you have a key, you may either store it on the fmp_key
file, or simply assign it as a string value to the fmp_api_key
variable.
The resulting dataset is stored in the sqlite3 format at the path pointed to by the file_output
variable. The dataset consists of financial information for the companies, stocks of which are listed in the New York Stack Exchange. With these, we also store daily price time-series since the beginning of 2019.
All downloaded data are cached in the output file (and only the erroneous/pending entries are retried each time).
file_output = 'cache/stock.sqlite3'
with open('fmp.key', 'r') as fid:
fmp_api_key = fid.read().strip()
from colito.logging import getModuleLogger
getModuleLogger('').setLevel(0)
getModuleLogger('').add_stderr(0)
<StreamHandler (NOTSET)>
from collections import deque
import aiohttp
import asyncio
import re
import pandas as pd
from tqdm.notebook import tqdm
log = getModuleLogger('')
log.setLevel(20)
log.add_stderr()
<StreamHandler (NOTSET)>
This class parses the online data from the FMP resource.
log = getModuleLogger('FMP')
import datetime
import json
import requests
import inspect
import sqlite3
from collections import namedtuple
def command(command, v=3, parts = [], prep=None, args=[], kwargs={}):
base_url = f'https://financialmodelingprep.com/api/v{v}/{command}'
def decorator(fn):
def mk_par(arg, val=inspect.Parameter.empty):
return inspect.Parameter(arg, kind=inspect._ParameterKind.POSITIONAL_OR_KEYWORD, default=val)
pars = [mk_par('self'), *[mk_par(a) for a in parts], *[mk_par(a) for a in args], *[mk_par(a,v) for a,v in kwargs.items()]]
sig = inspect.Signature(pars)
def wrapper_raw(self, *args, **kwargs):
pars = sig.bind(self, *args, **kwargs)
pars.apply_defaults()
params = dict(pars.arguments)
part_vals = [params.pop(p) for p in parts]
url = '/'.join([base_url, *part_vals])
params.pop('self')
resp = self.query(url, params=params)
return resp
def wrapper(self, *args, **kwargs):
resp = wrapper_raw(self, *args, **kwargs)
text = resp.text
data = json.loads(text)
if prep is not None:
data = prep(data)
return fn(self, data)
wrapper.__raw__ = wrapper_raw
wrapper.__signature__ = sig
wrapper.__name__ = fn.__name__
return wrapper
return decorator
class ConvertError(Exception): pass
AttrInfo = namedtuple('AttrInfo', ('name', 'kind', 'selected','conv', 'pp')) # pp is what must happen before using in sergio (post-process)
class FMP:
def __init__(self, apikey, db):
self._apikey = apikey
self._db = db
self._session = requests.Session()
def fetch_single(self, sql, params=[]):
rows = self._db.execute(sql, params).fetchall()
return [row[0] for row in rows]
def __del__(self):
self._db.close()
@classmethod
def profile_attr_info(cls):
attr_infos = []
def empty_or(fn):
def wrapped(x):
if isinstance(x, str) and len(x) == 0:
return None
else:
return fn(x)
return wrapped
def addinf(names, kind, conv=None, selected=True, pp=None):
nonlocal attr_infos
attr_infos += [AttrInfo(name=name, kind=kind, selected=selected, conv=conv, pp=pp) for name in names]
addinf(['price', 'beta', 'lastDiv', 'changes', 'dcfDiff', 'dcf'], 'NUMERIC', empty_or(float))
addinf(['volAvg', 'mktCap', 'fullTimeEmployees'], 'NUMERIC', empty_or(int))
addinf(['ipoDate'], 'NUMERIC', empty_or(cls.parse_date), pp=datetime.date.toordinal)
addinf(['cik', 'cusip'], 'INDEX', str)
addinf(['sector', 'country'], 'CATEGORICAL')
addinf(['isEtf', 'isActivelyTrading'], 'BOOLEAN', empty_or(bool))
addinf(['zip', 'website', 'description', 'ceo', 'phone', 'address', 'image', 'defaultImage', 'range'], None)
return attr_infos
__attr_infos__ = None
@classmethod
def _apply_attr_info(cls, which, row):
attr_infos = cls.__attr_infos__[which]
for ai in attr_infos:
if ai.conv is not None:
val = row[ai.name]
if val is not None:
try:
row[ai.name] = ai.conv(val)
except Exception as e:
raise ConvertError(f'While converting {ai} with value {val!r}')
@classmethod
def parse_date(cls, what):
if isinstance(what, datetime.date):
return what
elif isinstance(what, str):
return datetime.date.fromisoformat(what)
else:
raise TypeError(f'Could not parse date from {what} of type {type(what).__name__}.')
@property
def stocks(self): return pd.read_sql_query('select * from stocks', self._db)
@property
def company_profiles(self): return pd.read_sql_query('select * from company_profile', self._db)
@property
def tables(self):
return self.fetch_single('select name from SQLITE_MASTER where type="table"')
EXCHANGES = 'ETF|MUTUAL_FUND|COMMODITY|INDEX|CRYPTO|FOREX|TSX|AMEX|NASDAQ|NYSE|EURONEXT|XETRA|NSE|LSE'.split('|')
def query(self, url, params):
params = {'apikey':self._apikey, **params}
with self._session.get(url, params=params) as resp:
return resp
@command('dowjones_constituent', v=3, prep=pd.DataFrame)
def dowjones_constituent(self, result):
return result
@command('actives', v=3, prep=pd.DataFrame)
def most_active(self, result):
return result
@command('search-ticker', v=3, prep=pd.DataFrame, args=['query','exchange'])
def search_ticker(self, result):
return result
@command('profile', parts=['symbol'])
def company_profile(self, result):
if result:
row = result[0]
self._apply_attr_info('company_profile', row)
return row
@command('historical-price-full', parts=['symbol'])
def historical_price_full(self, result):
if result:
symbol = result['symbol']
df = pd.DataFrame(result['historical'])
df = df.astype({'date':'datetime64'}).drop('label',1).assign(symbol=symbol)
return df
else:
return None
@command('historical-price-full', parts=['symbol'])
def historical_price_full_raw(self, result):
if result:
symbol = result['symbol']
return [{'symbol':symbol,**row} for row in result['historical']]
else:
return None
@command('key-executives', parts=['symbol'])
def key_executives(self, result):
if result:
df = pd.DataFrame(result)
return df
else:
return None
def store_into(self, args, key, table, loader, batch_size=10, single=True):
'''Store a loaded value into a db
:param single: The result is a single entry or a df
'''
from itertools import chain
tables = self.tables
if table in tables:
keys_stored = self.fetch_single(f'select distinct `{key}` from `{table}`;')
args_left = [a for a in args if a not in keys_stored]
else:
args_left = args
it = iter(enumerate(tqdm(args_left)))
def get_batch():
results = []
while len(results) < batch_size:
try:
idx, arg = next(it)
except StopIteration:
break
try:
result = loader(arg)
if result is None or single and not result:
log.error(f'EMPTY: While loading index {idx} with arguments: {arg}')
else:
results.append(result)
except Exception as e:
log.error(f'ERROR: While loading index {idx} with arguments: {arg}: {e}', exc_info=True)
if not results:
return None
else:
return results if single else list(chain(*results))
if table not in tables:
rows = get_batch()
df = pd.DataFrame(rows)
df.to_sql(table, self._db, index=False)
def db_insert(rows):
sparam = ','.join(['?']*len(rows[0]))
sql = f'insert into `{table}` values ({sparam})'
self._db.executemany(sql, [list(row.values()) for row in rows])
self._db.commit()
while True:
df = get_batch()
if df is None:
break
db_insert(df)
FMP.__attr_infos__ = {'company_profile': FMP.profile_attr_info()}
db = sqlite3.connect(file_output)
fmp = FMP(fmp_api_key, db)
This selects the stocks listed in the New York Stack Exchange (NYSE).
if 'stocks' in fmp.tables:
df_stocks = fmp.stocks
log.info(f'Loaded {df_stocks.shape[0]} stocks from database')
else:
log.info(f'Fetching NYSE company stocks...')
df_stocks = fmp.search_ticker('','NYSE')
df_stocks.to_sql('stocks', fmp._db)
log.info(f'Fetched and stored {df_stocks.shape[0]} stocks into the database')
df_stocks.head()
Loaded 4727 stocks from database
index | symbol | name | currency | stockExchange | exchangeShortName | |
---|---|---|---|---|---|---|
0 | 0 | A | Agilent Technologies Inc | USD | New York Stock Exchange | NYSE |
1 | 1 | W | Wayfair Inc | USD | New York Stock Exchange | NYSE |
2 | 2 | R | Ryder System Inc | USD | New York Stock Exchange | NYSE |
3 | 3 | K | Kellogg Co | USD | New York Stock Exchange | NYSE |
4 | 4 | U | Unity Software Inc. | USD | New York Stock Exchange | NYSE |
Fetch and store the company profile entries for each stock in the df_stocks
table.
fmp.store_into(args=df_stocks.symbol, key='symbol',table='company_profile',single=True, batch_size=20,loader=fmp.company_profile)
EMPTY: While loading index 0 with arguments: OG EMPTY: While loading index 1 with arguments: PV EMPTY: While loading index 2 with arguments: NE EMPTY: While loading index 3 with arguments: GB EMPTY: While loading index 4 with arguments: UP EMPTY: While loading index 5 with arguments: JRE EMPTY: While loading index 6 with arguments: ATA EMPTY: While loading index 7 with arguments: UPH EMPTY: While loading index 8 with arguments: JBI EMPTY: While loading index 9 with arguments: WDI EMPTY: While loading index 10 with arguments: PAB EMPTY: While loading index 11 with arguments: CBX EMPTY: While loading index 12 with arguments: OSI EMPTY: While loading index 13 with arguments: YOU EMPTY: While loading index 14 with arguments: FZT EMPTY: While loading index 15 with arguments: NXU EMPTY: While loading index 16 with arguments: MCG EMPTY: While loading index 17 with arguments: PSY EMPTY: While loading index 18 with arguments: NGC EMPTY: While loading index 19 with arguments: LGV EMPTY: While loading index 20 with arguments: FOA EMPTY: While loading index 21 with arguments: ZVV EMPTY: While loading index 25 with arguments: GSX EMPTY: While loading index 32 with arguments: GIK EMPTY: While loading index 125 with arguments: ZIM EMPTY: While loading index 139 with arguments: ESM EMPTY: While loading index 241 with arguments: PDO EMPTY: While loading index 243 with arguments: JKE EMPTY: While loading index 255 with arguments: DEH EMPTY: While loading index 278 with arguments: TBA EMPTY: While loading index 451 with arguments: MDH EMPTY: While loading index 466 with arguments: ASZ EMPTY: While loading index 529 with arguments: OCA EMPTY: While loading index 530 with arguments: PTA EMPTY: While loading index 549 with arguments: ROT EMPTY: While loading index 565 with arguments: UMI EMPTY: While loading index 590 with arguments: CND EMPTY: While loading index 608 with arguments: DNZ EMPTY: While loading index 622 with arguments: JKD EMPTY: While loading index 623 with arguments: JKF EMPTY: While loading index 633 with arguments: JKH EMPTY: While loading index 645 with arguments: VAL EMPTY: While loading index 648 with arguments: OPA EMPTY: While loading index 666 with arguments: WDH EMPTY: While loading index 670 with arguments: UTZ EMPTY: While loading index 680 with arguments: RCC EMPTY: While loading index 684 with arguments: MVP EMPTY: While loading index 723 with arguments: GFX EMPTY: While loading index 725 with arguments: BVH EMPTY: While loading index 795 with arguments: PLT EMPTY: While loading index 856 with arguments: CRU EMPTY: While loading index 860 with arguments: CAP EMPTY: While loading index 944 with arguments: RLX EMPTY: While loading index 965 with arguments: FVT EMPTY: While loading index 1022 with arguments: DTB EMPTY: While loading index 1108 with arguments: NMG EMPTY: While loading index 1234 with arguments: JKG EMPTY: While loading index 1269 with arguments: GIC EMPTY: While loading index 1276 with arguments: CCV EMPTY: While loading index 1283 with arguments: BTC EMPTY: While loading index 1308 with arguments: AUS EMPTY: While loading index 1332 with arguments: JKL EMPTY: While loading index 1413 with arguments: LFT EMPTY: While loading index 1453 with arguments: MIT EMPTY: While loading index 1467 with arguments: JKK EMPTY: While loading index 1480 with arguments: PZD EMPTY: While loading index 1623 with arguments: CAS EMPTY: While loading index 1662 with arguments: UZD EMPTY: While loading index 1728 with arguments: ADF EMPTY: While loading index 1734 with arguments: ETM EMPTY: While loading index 1750 with arguments: BRW EMPTY: While loading index 1751 with arguments: TTE EMPTY: While loading index 1791 with arguments: DTM EMPTY: While loading index 1835 with arguments: UZE EMPTY: While loading index 1853 with arguments: UZF EMPTY: While loading index 1944 with arguments: JKJ EMPTY: While loading index 1966 with arguments: NSTC EMPTY: While loading index 1971 with arguments: FACT EMPTY: While loading index 1972 with arguments: FLME EMPTY: While loading index 1976 with arguments: UGCE EMPTY: While loading index 1978 with arguments: CLVT EMPTY: While loading index 1979 with arguments: WBAI EMPTY: While loading index 1982 with arguments: KCAC EMPTY: While loading index 1984 with arguments: ATMR EMPTY: While loading index 1989 with arguments: FSNB EMPTY: While loading index 1991 with arguments: LIII EMPTY: While loading index 1999 with arguments: BEPH EMPTY: While loading index 2003 with arguments: ANAC EMPTY: While loading index 2005 with arguments: BAMI EMPTY: While loading index 2014 with arguments: GENI EMPTY: While loading index 2016 with arguments: BWSN EMPTY: While loading index 2018 with arguments: IBER EMPTY: While loading index 2021 with arguments: PICC EMPTY: While loading index 2025 with arguments: EMHC EMPTY: While loading index 2027 with arguments: EATZ EMPTY: While loading index 2028 with arguments: KKRS EMPTY: While loading index 2029 with arguments: BEDZ EMPTY: While loading index 2035 with arguments: RAAS EMPTY: While loading index 2039 with arguments: MGRB EMPTY: While loading index 2085 with arguments: HJEN EMPTY: While loading index 2086 with arguments: ADIV EMPTY: While loading index 2089 with arguments: DMCY EMPTY: While loading index 2090 with arguments: DIVS EMPTY: While loading index 2092 with arguments: SYUS EMPTY: While loading index 2093 with arguments: INMU EMPTY: While loading index 2094 with arguments: IAPR EMPTY: While loading index 2095 with arguments: EAPR EMPTY: While loading index 2096 with arguments: JHCB EMPTY: While loading index 2097 with arguments: HLGE EMPTY: While loading index 2100 with arguments: TWIO EMPTY: While loading index 2105 with arguments: STNC EMPTY: While loading index 2148 with arguments: SPRQ EMPTY: While loading index 2163 with arguments: BLUA EMPTY: While loading index 2168 with arguments: CBAH EMPTY: While loading index 2171 with arguments: CGAU EMPTY: While loading index 2173 with arguments: KWAC EMPTY: While loading index 2175 with arguments: SPGS EMPTY: While loading index 2181 with arguments: LCTD EMPTY: While loading index 2183 with arguments: LCTU EMPTY: While loading index 2192 with arguments: BALY EMPTY: While loading index 2200 with arguments: PFIX EMPTY: While loading index 2204 with arguments: LOKM EMPTY: While loading index 2205 with arguments: SBII EMPTY: While loading index 2206 with arguments: AGOX EMPTY: While loading index 2207 with arguments: FRXB EMPTY: While loading index 2210 with arguments: MBOX EMPTY: While loading index 2211 with arguments: BITQ EMPTY: While loading index 2213 with arguments: SXQG EMPTY: While loading index 2214 with arguments: FORH EMPTY: While loading index 2215 with arguments: SVOL EMPTY: While loading index 2216 with arguments: BIGZ EMPTY: While loading index 2219 with arguments: ISOS EMPTY: While loading index 2220 with arguments: GPOR EMPTY: While loading index 2221 with arguments: TWNT EMPTY: While loading index 2222 with arguments: VSLU EMPTY: While loading index 2228 with arguments: GBLD EMPTY: While loading index 2229 with arguments: TRYP EMPTY: While loading index 2230 with arguments: AMTR EMPTY: While loading index 2233 with arguments: IPVF EMPTY: While loading index 2234 with arguments: BAMH EMPTY: While loading index 2235 with arguments: NIMC EMPTY: While loading index 2236 with arguments: NTSI EMPTY: While loading index 2237 with arguments: NTSE EMPTY: While loading index 2238 with arguments: ATAQ EMPTY: While loading index 2239 with arguments: RKTA EMPTY: While loading index 2240 with arguments: DMYQ EMPTY: While loading index 2241 with arguments: SCHY EMPTY: While loading index 2242 with arguments: WKLY EMPTY: While loading index 2244 with arguments: NSTD EMPTY: While loading index 2245 with arguments: MBAC EMPTY: While loading index 2246 with arguments: CLAA EMPTY: While loading index 2247 with arguments: MACC EMPTY: While loading index 2248 with arguments: TFSA EMPTY: While loading index 2249 with arguments: IACC EMPTY: While loading index 2250 with arguments: FVIV EMPTY: While loading index 2251 with arguments: ATFV EMPTY: While loading index 2252 with arguments: MJUS EMPTY: While loading index 2253 with arguments: WPCA EMPTY: While loading index 2254 with arguments: LQDB EMPTY: While loading index 2255 with arguments: DSPC EMPTY: While loading index 2259 with arguments: TSPQ EMPTY: While loading index 2261 with arguments: SOGU EMPTY: While loading index 2262 with arguments: FMNY EMPTY: While loading index 2263 with arguments: XVOL EMPTY: While loading index 2267 with arguments: CLAS EMPTY: While loading index 2268 with arguments: LNFA EMPTY: While loading index 2274 with arguments: SJIV EMPTY: While loading index 2283 with arguments: NPCT EMPTY: While loading index 2291 with arguments: SNII EMPTY: While loading index 2292 with arguments: AAQC EMPTY: While loading index 2293 with arguments: IPVA EMPTY: While loading index 2311 with arguments: VPCC EMPTY: While loading index 2314 with arguments: VGII EMPTY: While loading index 2324 with arguments: AMPI EMPTY: While loading index 2326 with arguments: WPCB EMPTY: While loading index 2327 with arguments: FTEV EMPTY: While loading index 2328 with arguments: ATHN EMPTY: While loading index 2329 with arguments: GAPA EMPTY: While loading index 2330 with arguments: KAHC EMPTY: While loading index 2331 with arguments: STRE EMPTY: While loading index 2332 with arguments: BGSX EMPTY: While loading index 2334 with arguments: ROSS EMPTY: While loading index 2338 with arguments: TRCA EMPTY: While loading index 2340 with arguments: PDOT EMPTY: While loading index 2386 with arguments: FACA EMPTY: While loading index 2413 with arguments: IACB EMPTY: While loading index 2425 with arguments: TMAC EMPTY: While loading index 2429 with arguments: NGAB EMPTY: While loading index 2431 with arguments: AAIC EMPTY: While loading index 2456 with arguments: IVAN EMPTY: While loading index 2458 with arguments: QFTA EMPTY: While loading index 2461 with arguments: CCVI EMPTY: While loading index 2462 with arguments: CHAA EMPTY: While loading index 2480 with arguments: CANO EMPTY: While loading index 2481 with arguments: KBND EMPTY: While loading index 2490 with arguments: ASAI EMPTY: While loading index 2491 with arguments: FATT EMPTY: While loading index 2492 with arguments: WARR EMPTY: While loading index 2493 with arguments: HUGS EMPTY: While loading index 2499 with arguments: TACA EMPTY: While loading index 2501 with arguments: GSQD EMPTY: While loading index 2504 with arguments: ILDR EMPTY: While loading index 2506 with arguments: PVAL EMPTY: While loading index 2507 with arguments: EPRE EMPTY: While loading index 2509 with arguments: BIPH EMPTY: While loading index 2511 with arguments: NBXG EMPTY: While loading index 2513 with arguments: PGRO EMPTY: While loading index 2514 with arguments: PLDR EMPTY: While loading index 2515 with arguments: HHLA EMPTY: While loading index 2519 with arguments: PFUT EMPTY: While loading index 2525 with arguments: ATAC EMPTY: While loading index 2528 with arguments: FIGS EMPTY: While loading index 2552 with arguments: FCRX EMPTY: While loading index 2585 with arguments: HTPA EMPTY: While loading index 2591 with arguments: FCAX EMPTY: While loading index 2598 with arguments: UGIC EMPTY: While loading index 2608 with arguments: FERG EMPTY: While loading index 2705 with arguments: GNPK EMPTY: While loading index 2767 with arguments: SKIL EMPTY: While loading index 2809 with arguments: YTPG EMPTY: While loading index 2901 with arguments: DBRG EMPTY: While loading index 2966 with arguments: NDMO EMPTY: While loading index 3085 with arguments: TLGA EMPTY: While loading index 3127 with arguments: BRSP EMPTY: While loading index 3131 with arguments: BODY EMPTY: While loading index 3158 with arguments: ESGY EMPTY: While loading index 3160 with arguments: RERE EMPTY: While loading index 3161 with arguments: SUNL EMPTY: While loading index 3162 with arguments: XPND EMPTY: While loading index 3163 with arguments: ALIT EMPTY: While loading index 3164 with arguments: LEXI EMPTY: While loading index 3165 with arguments: IAUM EMPTY: While loading index 3166 with arguments: SHUS EMPTY: While loading index 3167 with arguments: TSPA EMPTY: While loading index 3168 with arguments: ZHDG EMPTY: While loading index 3169 with arguments: META EMPTY: While loading index 3170 with arguments: PBUG EMPTY: While loading index 3175 with arguments: QVML EMPTY: While loading index 3176 with arguments: QVMM EMPTY: While loading index 3177 with arguments: AEMB EMPTY: While loading index 3178 with arguments: ESGB EMPTY: While loading index 3179 with arguments: DFAT EMPTY: While loading index 3180 with arguments: DFAS EMPTY: While loading index 3181 with arguments: IDAT EMPTY: While loading index 3182 with arguments: QVMS EMPTY: While loading index 3183 with arguments: FDWM EMPTY: While loading index 3191 with arguments: SINV EMPTY: While loading index 3192 with arguments: ITAN EMPTY: While loading index 3193 with arguments: DFAC EMPTY: While loading index 3194 with arguments: DFUS EMPTY: While loading index 3195 with arguments: SILX EMPTY: While loading index 3196 with arguments: AMAM EMPTY: While loading index 3211 with arguments: AWYX EMPTY: While loading index 3212 with arguments: CRUZ EMPTY: While loading index 3213 with arguments: TENG EMPTY: While loading index 3214 with arguments: IBDW EMPTY: While loading index 3216 with arguments: FSST EMPTY: While loading index 3254 with arguments: DTOX EMPTY: While loading index 3257 with arguments: MUSI EMPTY: While loading index 3258 with arguments: CUBS EMPTY: While loading index 3261 with arguments: TRPL EMPTY: While loading index 3272 with arguments: JIDA EMPTY: While loading index 3274 with arguments: KTEC EMPTY: While loading index 3278 with arguments: CLSC EMPTY: While loading index 3280 with arguments: DYLD EMPTY: While loading index 3284 with arguments: CPUH EMPTY: While loading index 3285 with arguments: SPAX EMPTY: While loading index 3288 with arguments: MJXL EMPTY: While loading index 3298 with arguments: JUSA EMPTY: While loading index 3301 with arguments: OOTO EMPTY: While loading index 3323 with arguments: FREY EMPTY: While loading index 3326 with arguments: GFOR EMPTY: While loading index 3327 with arguments: CURV EMPTY: While loading index 3328 with arguments: BAMR EMPTY: While loading index 3329 with arguments: AOMR EMPTY: While loading index 3330 with arguments: TPTA EMPTY: While loading index 3331 with arguments: POND EMPTY: While loading index 3336 with arguments: ALCC EMPTY: While loading index 3337 with arguments: ECCC EMPTY: While loading index 3338 with arguments: CLBR EMPTY: While loading index 3339 with arguments: AQNU EMPTY: While loading index 3340 with arguments: CNVY EMPTY: While loading index 3341 with arguments: ZETA EMPTY: While loading index 3370 with arguments: QDPL EMPTY: While loading index 3371 with arguments: KUKE EMPTY: While loading index 3372 with arguments: JFWD EMPTY: While loading index 3373 with arguments: CLSA EMPTY: While loading index 3374 with arguments: CLSM EMPTY: While loading index 3375 with arguments: KOCG EMPTY: While loading index 3379 with arguments: BIGY EMPTY: While loading index 3380 with arguments: GSFP EMPTY: While loading index 3381 with arguments: OWLT EMPTY: While loading index 3383 with arguments: STVN EMPTY: While loading index 3384 with arguments: BLND EMPTY: While loading index 3385 with arguments: HVAL EMPTY: While loading index 3386 with arguments: EGGF EMPTY: While loading index 3388 with arguments: JOJO EMPTY: While loading index 3389 with arguments: PSPC EMPTY: While loading index 3390 with arguments: MKFG EMPTY: While loading index 3393 with arguments: NABL EMPTY: While loading index 3394 with arguments: RIGZ EMPTY: While loading index 3395 with arguments: VSPY EMPTY: While loading index 3396 with arguments: OPFI EMPTY: While loading index 3398 with arguments: RYAN EMPTY: While loading index 3400 with arguments: KONG EMPTY: While loading index 3403 with arguments: AAIN EMPTY: While loading index 3405 with arguments: LOPX EMPTY: While loading index 3406 with arguments: OWNS EMPTY: While loading index 3408 with arguments: MVPS EMPTY: While loading index 3409 with arguments: AGOV EMPTY: While loading index 3410 with arguments: MGRD EMPTY: While loading index 3411 with arguments: EERN EMPTY: While loading index 3412 with arguments: STBL EMPTY: While loading index 3413 with arguments: CLMA EMPTY: While loading index 3414 with arguments: SHFT EMPTY: While loading index 3415 with arguments: PWSC EMPTY: While loading index 3416 with arguments: MLNK EMPTY: While loading index 3418 with arguments: HLLY EMPTY: While loading index 3422 with arguments: NSTB EMPTY: While loading index 3423 with arguments: BBSC EMPTY: While loading index 3424 with arguments: SPCX EMPTY: While loading index 3432 with arguments: STPK EMPTY: While loading index 3441 with arguments: JCTR EMPTY: While loading index 3468 with arguments: LOPP EMPTY: While loading index 3469 with arguments: LOKB EMPTY: While loading index 3474 with arguments: TPGS EMPTY: While loading index 3479 with arguments: TWNI EMPTY: While loading index 3489 with arguments: HTFB EMPTY: While loading index 3499 with arguments: AIZN EMPTY: While loading index 3541 with arguments: CVII EMPTY: While loading index 3556 with arguments: DNMR EMPTY: While loading index 3584 with arguments: BITE EMPTY: While loading index 3589 with arguments: BOAS EMPTY: While loading index 3591 with arguments: STIC EMPTY: While loading index 3602 with arguments: TWOA EMPTY: While loading index 3608 with arguments: MOTV EMPTY: While loading index 3613 with arguments: EQHA EMPTY: While loading index 3617 with arguments: APGB EMPTY: While loading index 3619 with arguments: RTPZ EMPTY: While loading index 3625 with arguments: AGAC EMPTY: While loading index 3628 with arguments: ACII EMPTY: While loading index 3630 with arguments: PRPC EMPTY: While loading index 3631 with arguments: CSTA EMPTY: While loading index 3632 with arguments: PNTM EMPTY: While loading index 3644 with arguments: ADEX EMPTY: While loading index 3647 with arguments: ECCW EMPTY: While loading index 3649 with arguments: CSAN EMPTY: While loading index 3652 with arguments: JWSM EMPTY: While loading index 3660 with arguments: CPTK EMPTY: While loading index 3661 with arguments: SLAC EMPTY: While loading index 3662 with arguments: IIAC EMPTY: While loading index 3668 with arguments: CLIM EMPTY: While loading index 3689 with arguments: GGRW EMPTY: While loading index 3690 with arguments: OPPX EMPTY: While loading index 3695 with arguments: SUBZ EMPTY: While loading index 3708 with arguments: AESC EMPTY: While loading index 3780 with arguments: EPWR EMPTY: While loading index 3781 with arguments: STPC EMPTY: While loading index 3794 with arguments: TIXT EMPTY: While loading index 3801 with arguments: RFMZ EMPTY: While loading index 3819 with arguments: DMYI EMPTY: While loading index 3835 with arguments: SOJE EMPTY: While loading index 3842 with arguments: PRPB EMPTY: While loading index 3854 with arguments: PIPP EMPTY: While loading index 3858 with arguments: ACND EMPTY: While loading index 3866 with arguments: PCPC EMPTY: While loading index 3871 with arguments: ONTF EMPTY: While loading index 3897 with arguments: IPOE EMPTY: While loading index 3920 with arguments: BCAT EMPTY: While loading index 3928 with arguments: NSCO EMPTY: While loading index 3949 with arguments: SPFR EMPTY: While loading index 3952 with arguments: AENZ EMPTY: While loading index 3981 with arguments: GAB-R EMPTY: While loading index 3985 with arguments: CTEST EMPTY: While loading index 3986 with arguments: GRP-U EMPTY: While loading index 3994 with arguments: WSO-B EMPTY: While loading index 4060 with arguments: NBA-UN EMPTY: While loading index 4077 with arguments: RTP-UN EMPTY: While loading index 4085 with arguments: SUNL-W EMPTY: While loading index 4115 with arguments: FVIV-UN EMPTY: While loading index 4126 with arguments: PDAC-UN EMPTY: While loading index 4130 with arguments: PMVC-UN EMPTY: While loading index 4148 with arguments: FTEV-UN EMPTY: While loading index 4160 with arguments: SNPR-UN
df_profiles = pd.read_sql_query('select * from company_profile', db)
log.info(f'Stored: {df_profiles.shape[0]} company profiles.')
df_profiles.head()
Stored: 4343 company profiles.
symbol | price | beta | volAvg | mktCap | lastDiv | range | changes | companyName | currency | ... | city | state | zip | dcfDiff | dcf | image | ipoDate | defaultImage | isEtf | isActivelyTrading | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | A | 150.93 | 1.005434 | 1671660 | 4.579865e+10 | 0.762 | 94.53-152.41 | 0.98 | Agilent Technologies, Inc. | USD | ... | Santa Clara | CALIFORNIA | 95051 | -53.50 | 154.5830 | https://financialmodelingprep.com/image-stock/... | 1999-11-18 | 0 | 0 | 1 |
1 | W | 275.63 | 3.135326 | 1344066 | 2.872037e+10 | 0.000 | 221.09-369.0 | 0.99 | Wayfair Inc. | USD | ... | Boston | MASSACHUSETTS | 02116 | NaN | 296.0770 | https://financialmodelingprep.com/image-stock/... | 2014-10-02 | 0 | 0 | 1 |
2 | R | 71.21 | 2.000543 | 608953 | 3.836111e+09 | 2.240 | 35.39-89.65 | -2.69 | Ryder System, Inc. | USD | ... | Medley | FLORIDA | 33178 | 752.25 | 76.7075 | https://financialmodelingprep.com/image-stock/... | 1960-09-19 | 0 | 0 | 1 |
3 | K | 63.39 | 0.645108 | 2352255 | 2.158404e+10 | 2.290 | 56.61-72.88 | -0.32 | Kellogg Company | USD | ... | Battle Creek | MICHIGAN | 49016-3599 | 79.97 | 64.9620 | https://financialmodelingprep.com/image-stock/... | 1952-01-09 | 0 | 0 | 1 |
4 | U | 105.12 | 0.000000 | 3338061 | 2.937232e+10 | 0.000 | 65.11-174.94 | 4.34 | Unity Software Inc. | USD | ... | San Francisco | CA | 94103 | NaN | 0.0000 | https://financialmodelingprep.com/image-stock/... | 2020-09-18 | 0 | 0 | 1 |
5 rows × 34 columns
Fetch and store the daily prices for each stock since the beginning of 2019.
def parse_datetime(what):
if isinstance(what, datetime.datetime):
return what
elif isinstance(what, str):
return datetime.datetime.fromisoformat(what)
else:
raise TypeError(f'Could not parse date from {what} of type {type(what).__name__}.')
def get_price(symbol, since=None, until=None):
df = fmp.historical_price_full(symbol)
if df is not None:
df = df.drop(['changePercent','change'],1)
if since is not None:
since = parse_datetime(since)
df = df.loc[df.date > since]
if until is not None:
until = parse_datetime(until)
df = df.loc[df.date < until]
df.date = [e.date() for e in df.date]
cols = df.columns
rows = [dict(zip(cols,row)) for row in list(df.to_records(index=False))]
else:
rows = None
return rows
fmp.store_into(args=df_stocks.symbol, key='symbol',table='prices_daily',single=False, batch_size=20,loader=lambda s: get_price(s, since='2019-01-01'))
EMPTY: While loading index 417 with arguments: MCG EMPTY: While loading index 423 with arguments: FNG EMPTY: While loading index 709 with arguments: SIZ EMPTY: While loading index 2629 with arguments: GDXS EMPTY: While loading index 2630 with arguments: GLDW EMPTY: While loading index 2631 with arguments: USOD EMPTY: While loading index 2632 with arguments: USOU EMPTY: While loading index 2642 with arguments: PPLN EMPTY: While loading index 3821 with arguments: TRPL EMPTY: While loading index 3838 with arguments: CLSC EMPTY: While loading index 3886 with arguments: GFOR EMPTY: While loading index 3943 with arguments: STVN EMPTY: While loading index 3944 with arguments: BLND EMPTY: While loading index 3946 with arguments: EGGF EMPTY: While loading index 3960 with arguments: KONG EMPTY: While loading index 3975 with arguments: PWSC EMPTY: While loading index 4541 with arguments: GAB-R EMPTY: While loading index 4645 with arguments: SUNL-W EMPTY: While loading index 4726 with arguments: PHGE-UN
df_prices = pd.read_sql_query('select * from prices_daily', db)
log.info(f'Stored: {df_prices.shape[0]} price entries for {len(df_prices.symbol.unique())} stocks (avg: {df_prices.groupby("symbol").apply(len).mean()} prices per symbol)')
df_prices.head()
Stored: 2585050 price entries for 4708 stocks (avg: 549.0760407816482 prices per symbol)
date | open | high | low | close | adjClose | volume | unadjustedVolume | vwap | changeOverTime | symbol | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2021-07-27 | 149.29 | 150.00 | 148.86 | 149.95 | 149.95 | 2019312.0 | 2019312.0 | 149.60333 | 0.00442 | A |
1 | 2021-07-26 | 151.73 | 152.17 | 149.77 | 150.25 | 150.25 | 1771760.0 | 1771760.0 | 150.73000 | -0.00975 | A |
2 | 2021-07-23 | 150.59 | 152.41 | 150.43 | 152.31 | 152.31 | 2099551.0 | 2099551.0 | 151.71667 | 0.01142 | A |
3 | 2021-07-22 | 149.71 | 150.50 | 148.67 | 150.30 | 150.30 | 1920853.0 | 1920853.0 | 149.82333 | 0.00394 | A |
4 | 2021-07-21 | 149.49 | 149.91 | 147.61 | 148.95 | 148.95 | 1708302.0 | 1708302.0 | 148.82333 | -0.00361 | A |
Fetch and store general information about the most important executive people of each company, for which the stock is listed.
def get_executives(symbol):
df = fmp.key_executives(symbol)
if df is not None:
cols = df.columns
df = df.astype({'yearBorn':float,'pay':float})
rows = [{'symbol':symbol, **dict(zip(cols,row))} for row in list(df.to_records(index=False))]
return rows
fmp.store_into(args=df_stocks.symbol, key='symbol',table='key_executives',single=False, batch_size=10,loader=get_executives)
EMPTY: While loading index 5 with arguments: S EMPTY: While loading index 30 with arguments: OG EMPTY: While loading index 33 with arguments: IS EMPTY: While loading index 42 with arguments: GK EMPTY: While loading index 71 with arguments: PV EMPTY: While loading index 75 with arguments: NE EMPTY: While loading index 90 with arguments: GB EMPTY: While loading index 99 with arguments: FM EMPTY: While loading index 128 with arguments: SH EMPTY: While loading index 133 with arguments: IG EMPTY: While loading index 137 with arguments: GD EMPTY: While loading index 154 with arguments: JE EMPTY: While loading index 162 with arguments: UP EMPTY: While loading index 175 with arguments: VT EMPTY: While loading index 184 with arguments: QS EMPTY: While loading index 189 with arguments: VB EMPTY: While loading index 190 with arguments: JO EMPTY: While loading index 192 with arguments: DV EMPTY: While loading index 197 with arguments: VO EMPTY: While loading index 199 with arguments: VV EMPTY: While loading index 200 with arguments: CN EMPTY: While loading index 201 with arguments: LD EMPTY: While loading index 217 with arguments: MJ EMPTY: While loading index 220 with arguments: CCZ EMPTY: While loading index 224 with arguments: VDE EMPTY: While loading index 228 with arguments: WFH EMPTY: While loading index 233 with arguments: XLU EMPTY: While loading index 236 with arguments: XLY EMPTY: While loading index 238 with arguments: JRE EMPTY: While loading index 254 with arguments: ATA EMPTY: While loading index 263 with arguments: BCI EMPTY: While loading index 279 with arguments: SAK EMPTY: While loading index 282 with arguments: IJK EMPTY: While loading index 290 with arguments: BCM EMPTY: While loading index 292 with arguments: NLR EMPTY: While loading index 293 with arguments: RSI EMPTY: While loading index 295 with arguments: UPH EMPTY: While loading index 296 with arguments: JBI EMPTY: While loading index 297 with arguments: WDI EMPTY: While loading index 298 with arguments: SPD EMPTY: While loading index 304 with arguments: AEB EMPTY: While loading index 306 with arguments: PAB EMPTY: While loading index 309 with arguments: TOK EMPTY: While loading index 310 with arguments: ERX EMPTY: While loading index 312 with arguments: GWX EMPTY: While loading index 313 with arguments: GXC EMPTY: While loading index 314 with arguments: NSS EMPTY: While loading index 316 with arguments: CBX EMPTY: While loading index 317 with arguments: CBH EMPTY: While loading index 319 with arguments: AGL EMPTY: While loading index 320 with arguments: OSI EMPTY: While loading index 321 with arguments: MID EMPTY: While loading index 323 with arguments: QSY EMPTY: While loading index 325 with arguments: YOU EMPTY: While loading index 328 with arguments: QLV EMPTY: While loading index 329 with arguments: RFG EMPTY: While loading index 330 with arguments: FXA EMPTY: While loading index 331 with arguments: FXC EMPTY: While loading index 332 with arguments: FXB EMPTY: While loading index 333 with arguments: FXE EMPTY: While loading index 334 with arguments: FXD EMPTY: While loading index 336 with arguments: PHB EMPTY: While loading index 338 with arguments: JIG EMPTY: While loading index 341 with arguments: FXF EMPTY: While loading index 342 with arguments: FXI EMPTY: While loading index 343 with arguments: FXL EMPTY: While loading index 345 with arguments: FXN EMPTY: While loading index 346 with arguments: FEZ EMPTY: While loading index 347 with arguments: DBA EMPTY: While loading index 348 with arguments: DBB EMPTY: While loading index 349 with arguments: DBE EMPTY: While loading index 351 with arguments: PZT EMPTY: While loading index 352 with arguments: YLD EMPTY: While loading index 354 with arguments: DDM EMPTY: While loading index 355 with arguments: GLL EMPTY: While loading index 356 with arguments: BAB EMPTY: While loading index 357 with arguments: FZT EMPTY: While loading index 359 with arguments: FXY EMPTY: While loading index 376 with arguments: NXU EMPTY: While loading index 380 with arguments: NIM EMPTY: While loading index 385 with arguments: DUG EMPTY: While loading index 390 with arguments: JBK EMPTY: While loading index 395 with arguments: BPT EMPTY: While loading index 398 with arguments: SMM EMPTY: While loading index 407 with arguments: PYT EMPTY: While loading index 408 with arguments: PYS EMPTY: While loading index 410 with arguments: XHS EMPTY: While loading index 417 with arguments: MCG EMPTY: While loading index 423 with arguments: FNG EMPTY: While loading index 427 with arguments: EWV EMPTY: While loading index 428 with arguments: PSY EMPTY: While loading index 431 with arguments: LAW EMPTY: While loading index 432 with arguments: EHT EMPTY: While loading index 436 with arguments: CNM EMPTY: While loading index 437 with arguments: NGC EMPTY: While loading index 441 with arguments: IPO EMPTY: While loading index 446 with arguments: PBC EMPTY: While loading index 449 with arguments: BLV EMPTY: While loading index 451 with arguments: ADZ EMPTY: While loading index 455 with arguments: BOM EMPTY: While loading index 457 with arguments: BOS EMPTY: While loading index 458 with arguments: DDP EMPTY: While loading index 460 with arguments: DEE EMPTY: While loading index 461 with arguments: QLD EMPTY: While loading index 467 with arguments: SYG EMPTY: While loading index 468 with arguments: BIL EMPTY: While loading index 471 with arguments: IAT EMPTY: While loading index 472 with arguments: DOL EMPTY: While loading index 473 with arguments: QPX EMPTY: While loading index 474 with arguments: QPT EMPTY: While loading index 475 with arguments: NVQ EMPTY: While loading index 476 with arguments: DFJ EMPTY: While loading index 477 with arguments: BAL EMPTY: While loading index 485 with arguments: SZO EMPTY: While loading index 486 with arguments: BNE EMPTY: While loading index 487 with arguments: IBD EMPTY: While loading index 495 with arguments: UJB EMPTY: While loading index 500 with arguments: LGV EMPTY: While loading index 501 with arguments: BAR EMPTY: While loading index 503 with arguments: JPT EMPTY: While loading index 508 with arguments: FOA EMPTY: While loading index 510 with arguments: AAA EMPTY: While loading index 513 with arguments: FIV EMPTY: While loading index 514 with arguments: SZK EMPTY: While loading index 517 with arguments: NCZ EMPTY: While loading index 524 with arguments: ZVV EMPTY: While loading index 525 with arguments: XPP EMPTY: While loading index 526 with arguments: KTN EMPTY: While loading index 528 with arguments: VPC EMPTY: While loading index 529 with arguments: VOX EMPTY: While loading index 530 with arguments: CEF EMPTY: While loading index 532 with arguments: BBK EMPTY: While loading index 533 with arguments: TLH EMPTY: While loading index 534 with arguments: VPL EMPTY: While loading index 535 with arguments: TMF EMPTY: While loading index 539 with arguments: MDY EMPTY: While loading index 545 with arguments: AUD EMPTY: While loading index 546 with arguments: RCB EMPTY: While loading index 548 with arguments: CHN EMPTY: While loading index 549 with arguments: ELC EMPTY: While loading index 550 with arguments: BWX EMPTY: While loading index 551 with arguments: MOM EMPTY: While loading index 552 with arguments: GAL EMPTY: While loading index 553 with arguments: IEV EMPTY: While loading index 558 with arguments: UPW EMPTY: While loading index 564 with arguments: YYY EMPTY: While loading index 577 with arguments: GAZ EMPTY: While loading index 578 with arguments: DAG EMPTY: While loading index 579 with arguments: USI EMPTY: While loading index 590 with arguments: UST EMPTY: While loading index 592 with arguments: GIK EMPTY: While loading index 596 with arguments: ZIG EMPTY: While loading index 597 with arguments: ELP EMPTY: While loading index 599 with arguments: RMI EMPTY: While loading index 601 with arguments: UUP EMPTY: While loading index 603 with arguments: SRS EMPTY: While loading index 609 with arguments: GCC EMPTY: While loading index 612 with arguments: PVI EMPTY: While loading index 613 with arguments: UYM EMPTY: While loading index 623 with arguments: FAN EMPTY: While loading index 626 with arguments: QMJ EMPTY: While loading index 645 with arguments: IHE EMPTY: While loading index 648 with arguments: PBR EMPTY: While loading index 654 with arguments: PBE EMPTY: While loading index 665 with arguments: VEU EMPTY: While loading index 672 with arguments: FPL EMPTY: While loading index 674 with arguments: DGS EMPTY: While loading index 679 with arguments: TBB EMPTY: While loading index 680 with arguments: DIA EMPTY: While loading index 681 with arguments: MTR EMPTY: While loading index 684 with arguments: PUK EMPTY: While loading index 687 with arguments: DYY EMPTY: While loading index 688 with arguments: JWS EMPTY: While loading index 689 with arguments: DIM EMPTY: While loading index 691 with arguments: DJD EMPTY: While loading index 696 with arguments: QDF EMPTY: While loading index 699 with arguments: ESM EMPTY: While loading index 703 with arguments: OVT EMPTY: While loading index 704 with arguments: QED EMPTY: While loading index 705 with arguments: DTY EMPTY: While loading index 709 with arguments: SIZ EMPTY: While loading index 718 with arguments: AMR EMPTY: While loading index 721 with arguments: SPY EMPTY: While loading index 739 with arguments: VOO EMPTY: While loading index 743 with arguments: MUB EMPTY: While loading index 751 with arguments: VGT EMPTY: While loading index 754 with arguments: CBO EMPTY: While loading index 756 with arguments: XLK EMPTY: While loading index 759 with arguments: GIX EMPTY: While loading index 761 with arguments: VHT EMPTY: While loading index 764 with arguments: AMX EMPTY: While loading index 770 with arguments: XLV EMPTY: While loading index 789 with arguments: LCG EMPTY: While loading index 792 with arguments: NUV EMPTY: While loading index 801 with arguments: PDO EMPTY: While loading index 802 with arguments: XTN EMPTY: While loading index 803 with arguments: JKE EMPTY: While loading index 811 with arguments: DEN EMPTY: While loading index 813 with arguments: DWM EMPTY: While loading index 815 with arguments: DEH EMPTY: While loading index 826 with arguments: IAU EMPTY: While loading index 830 with arguments: XAR EMPTY: While loading index 837 with arguments: IDU EMPTY: While loading index 845 with arguments: XBI EMPTY: While loading index 855 with arguments: VDC EMPTY: While loading index 858 with arguments: BSX EMPTY: While loading index 868 with arguments: XME EMPTY: While loading index 869 with arguments: VIG EMPTY: While loading index 870 with arguments: MXI EMPTY: While loading index 871 with arguments: SFB EMPTY: While loading index 885 with arguments: AFK EMPTY: While loading index 891 with arguments: VBK EMPTY: While loading index 892 with arguments: PBY EMPTY: While loading index 895 with arguments: IVV EMPTY: While loading index 897 with arguments: PDN EMPTY: While loading index 900 with arguments: RHS EMPTY: While loading index 903 with arguments: AGG EMPTY: While loading index 910 with arguments: AGD EMPTY: While loading index 917 with arguments: ACV EMPTY: While loading index 922 with arguments: IWV EMPTY: While loading index 923 with arguments: UZC EMPTY: While loading index 928 with arguments: ZEV EMPTY: While loading index 931 with arguments: UWM EMPTY: While loading index 934 with arguments: TNA EMPTY: While loading index 935 with arguments: OVM EMPTY: While loading index 936 with arguments: VRP EMPTY: While loading index 970 with arguments: RFM EMPTY: While loading index 974 with arguments: RFV EMPTY: While loading index 976 with arguments: HGH EMPTY: While loading index 977 with arguments: LEV EMPTY: While loading index 978 with arguments: PHK EMPTY: While loading index 982 with arguments: PKB EMPTY: While loading index 989 with arguments: IYM EMPTY: While loading index 1003 with arguments: VSL EMPTY: While loading index 1011 with arguments: MDH EMPTY: While loading index 1013 with arguments: BGH EMPTY: While loading index 1026 with arguments: ASZ EMPTY: While loading index 1028 with arguments: VSS EMPTY: While loading index 1034 with arguments: VVR EMPTY: While loading index 1037 with arguments: VWO EMPTY: While loading index 1041 with arguments: TTT EMPTY: While loading index 1044 with arguments: PGZ EMPTY: While loading index 1045 with arguments: AOK EMPTY: While loading index 1046 with arguments: AOM EMPTY: While loading index 1047 with arguments: VOC EMPTY: While loading index 1048 with arguments: AOR EMPTY: While loading index 1049 with arguments: NPN EMPTY: While loading index 1050 with arguments: TYD EMPTY: While loading index 1057 with arguments: TYO EMPTY: While loading index 1061 with arguments: PRF EMPTY: While loading index 1064 with arguments: MUS EMPTY: While loading index 1071 with arguments: TZA EMPTY: While loading index 1078 with arguments: YCL EMPTY: While loading index 1082 with arguments: YCS EMPTY: While loading index 1089 with arguments: OCA EMPTY: While loading index 1090 with arguments: PTA EMPTY: While loading index 1092 with arguments: EDC EMPTY: While loading index 1094 with arguments: IJJ EMPTY: While loading index 1096 with arguments: RWM EMPTY: While loading index 1097 with arguments: RJA EMPTY: While loading index 1098 with arguments: MNA EMPTY: While loading index 1100 with arguments: RWO EMPTY: While loading index 1105 with arguments: PSP EMPTY: While loading index 1108 with arguments: RWR EMPTY: While loading index 1112 with arguments: CIG EMPTY: While loading index 1119 with arguments: PSQ EMPTY: While loading index 1123 with arguments: GJT EMPTY: While loading index 1125 with arguments: UMI EMPTY: While loading index 1126 with arguments: PST EMPTY: While loading index 1128 with arguments: DBC EMPTY: While loading index 1129 with arguments: WIP EMPTY: While loading index 1130 with arguments: RWX EMPTY: While loading index 1131 with arguments: RXD EMPTY: While loading index 1137 with arguments: RMO EMPTY: While loading index 1139 with arguments: NAZ EMPTY: While loading index 1141 with arguments: RXL EMPTY: While loading index 1150 with arguments: CND EMPTY: While loading index 1155 with arguments: ERY EMPTY: While loading index 1158 with arguments: ING EMPTY: While loading index 1159 with arguments: BBC EMPTY: While loading index 1163 with arguments: BBP EMPTY: While loading index 1168 with arguments: DNZ EMPTY: While loading index 1169 with arguments: OVS EMPTY: While loading index 1171 with arguments: TVE EMPTY: While loading index 1175 with arguments: FEI EMPTY: While loading index 1182 with arguments: JKD EMPTY: While loading index 1183 with arguments: JKF EMPTY: While loading index 1188 with arguments: XHB EMPTY: While loading index 1193 with arguments: JKH EMPTY: While loading index 1197 with arguments: DBO EMPTY: While loading index 1204 with arguments: SHI EMPTY: While loading index 1205 with arguments: VAL EMPTY: While loading index 1206 with arguments: JRS EMPTY: While loading index 1208 with arguments: OPA EMPTY: While loading index 1211 with arguments: PWB EMPTY: While loading index 1212 with arguments: VBR EMPTY: While loading index 1213 with arguments: PXH EMPTY: While loading index 1214 with arguments: PXJ EMPTY: While loading index 1217 with arguments: PXQ EMPTY: While loading index 1218 with arguments: DBV EMPTY: While loading index 1223 with arguments: DTW EMPTY: While loading index 1226 with arguments: WDH EMPTY: While loading index 1230 with arguments: UTZ EMPTY: While loading index 1231 with arguments: AAC EMPTY: While loading index 1232 with arguments: EDI EMPTY: While loading index 1240 with arguments: RCC EMPTY: While loading index 1244 with arguments: MVP EMPTY: While loading index 1247 with arguments: PPX EMPTY: While loading index 1252 with arguments: EEH EMPTY: While loading index 1257 with arguments: RWK EMPTY: While loading index 1262 with arguments: OLO EMPTY: While loading index 1269 with arguments: PXE EMPTY: While loading index 1270 with arguments: DTJ EMPTY: While loading index 1281 with arguments: NCB EMPTY: While loading index 1283 with arguments: GFX EMPTY: While loading index 1285 with arguments: BVH EMPTY: While loading index 1291 with arguments: AIC EMPTY: While loading index 1292 with arguments: ENJ EMPTY: While loading index 1297 with arguments: LQD EMPTY: While loading index 1317 with arguments: UCC EMPTY: While loading index 1329 with arguments: RSF EMPTY: While loading index 1334 with arguments: JMM EMPTY: While loading index 1339 with arguments: RZB EMPTY: While loading index 1342 with arguments: ACR EMPTY: While loading index 1346 with arguments: UCO EMPTY: While loading index 1348 with arguments: PRS EMPTY: While loading index 1350 with arguments: ARB EMPTY: While loading index 1352 with arguments: OWL EMPTY: While loading index 1369 with arguments: UBT EMPTY: While loading index 1375 with arguments: TDI EMPTY: While loading index 1386 with arguments: JNK EMPTY: While loading index 1390 with arguments: FSR EMPTY: While loading index 1400 with arguments: GJH EMPTY: While loading index 1402 with arguments: GTN EMPTY: While loading index 1405 with arguments: UDN EMPTY: While loading index 1416 with arguments: CRU EMPTY: While loading index 1420 with arguments: CAP EMPTY: While loading index 1432 with arguments: PFH EMPTY: While loading index 1444 with arguments: WIZ EMPTY: While loading index 1451 with arguments: SBB EMPTY: While loading index 1455 with arguments: NYF EMPTY: While loading index 1456 with arguments: TWN EMPTY: While loading index 1457 with arguments: TBC EMPTY: While loading index 1458 with arguments: PZA EMPTY: While loading index 1463 with arguments: FRX EMPTY: While loading index 1465 with arguments: UGE EMPTY: While loading index 1471 with arguments: FIW EMPTY: While loading index 1478 with arguments: TMV EMPTY: While loading index 1482 with arguments: UGL EMPTY: While loading index 1485 with arguments: SCO EMPTY: While loading index 1487 with arguments: DFE EMPTY: While loading index 1488 with arguments: HPX EMPTY: While loading index 1491 with arguments: EFL EMPTY: While loading index 1509 with arguments: FPE EMPTY: While loading index 1511 with arguments: TAN EMPTY: While loading index 1520 with arguments: IVW EMPTY: While loading index 1525 with arguments: FVT EMPTY: While loading index 1543 with arguments: FLM EMPTY: While loading index 1546 with arguments: NCV EMPTY: While loading index 1557 with arguments: IWL EMPTY: While loading index 1559 with arguments: DHS EMPTY: While loading index 1560 with arguments: DIG EMPTY: While loading index 1571 with arguments: TBF EMPTY: While loading index 1577 with arguments: DIV EMPTY: While loading index 1582 with arguments: DTB EMPTY: While loading index 1583 with arguments: IXC EMPTY: While loading index 1588 with arguments: GTO EMPTY: While loading index 1597 with arguments: JCO EMPTY: While loading index 1602 with arguments: TBT EMPTY: While loading index 1604 with arguments: TBX EMPTY: While loading index 1610 with arguments: FNI EMPTY: While loading index 1618 with arguments: SHE EMPTY: While loading index 1627 with arguments: ENO EMPTY: While loading index 1634 with arguments: DOG EMPTY: While loading index 1637 with arguments: DOO EMPTY: While loading index 1639 with arguments: UZA EMPTY: While loading index 1643 with arguments: FXU EMPTY: While loading index 1651 with arguments: FXZ EMPTY: While loading index 1653 with arguments: DTP EMPTY: While loading index 1654 with arguments: EWD EMPTY: While loading index 1655 with arguments: EWH EMPTY: While loading index 1659 with arguments: AIO EMPTY: While loading index 1660 with arguments: EWM EMPTY: While loading index 1662 with arguments: RCA EMPTY: While loading index 1667 with arguments: TVC EMPTY: While loading index 1668 with arguments: NMG EMPTY: While loading index 1679 with arguments: VGK EMPTY: While loading index 1680 with arguments: MGC EMPTY: While loading index 1681 with arguments: MGV EMPTY: While loading index 1684 with arguments: LTL EMPTY: While loading index 1686 with arguments: XHE EMPTY: While loading index 1688 with arguments: EBR EMPTY: While loading index 1690 with arguments: JPN EMPTY: While loading index 1694 with arguments: KRE EMPTY: While loading index 1696 with arguments: EWC EMPTY: While loading index 1698 with arguments: XLE EMPTY: While loading index 1700 with arguments: DLY EMPTY: While loading index 1701 with arguments: XLG EMPTY: While loading index 1709 with arguments: TDJ EMPTY: While loading index 1716 with arguments: CEW EMPTY: While loading index 1717 with arguments: GNR EMPTY: While loading index 1719 with arguments: RZA EMPTY: While loading index 1760 with arguments: IVE EMPTY: While loading index 1761 with arguments: MVV EMPTY: While loading index 1764 with arguments: IWC EMPTY: While loading index 1765 with arguments: XLC EMPTY: While loading index 1766 with arguments: IXG EMPTY: While loading index 1767 with arguments: IYG EMPTY: While loading index 1769 with arguments: GMF EMPTY: While loading index 1770 with arguments: DGL EMPTY: While loading index 1773 with arguments: EIS EMPTY: While loading index 1774 with arguments: IJH EMPTY: While loading index 1775 with arguments: FDM EMPTY: While loading index 1776 with arguments: FDL EMPTY: While loading index 1777 with arguments: IJR EMPTY: While loading index 1779 with arguments: IYF EMPTY: While loading index 1780 with arguments: DGT EMPTY: While loading index 1781 with arguments: FDN EMPTY: While loading index 1782 with arguments: IJS EMPTY: While loading index 1786 with arguments: OPP EMPTY: While loading index 1787 with arguments: IYH EMPTY: While loading index 1788 with arguments: IYK EMPTY: While loading index 1789 with arguments: CMF EMPTY: While loading index 1794 with arguments: JKG EMPTY: While loading index 1800 with arguments: MGR EMPTY: While loading index 1803 with arguments: ILF EMPTY: While loading index 1804 with arguments: FFR EMPTY: While loading index 1805 with arguments: EDV EMPTY: While loading index 1807 with arguments: FGD EMPTY: While loading index 1808 with arguments: EES EMPTY: While loading index 1811 with arguments: DGP EMPTY: While loading index 1813 with arguments: DGZ EMPTY: While loading index 1814 with arguments: DZZ EMPTY: While loading index 1817 with arguments: XLF
df_execs = pd.read_sql_query('select * from key_executives', db)
log.info(f'Stored: {df_execs.shape[0]} executable entries for {len(df_execs.symbol.unique())} stocks (avg: {df_execs.groupby("symbol").apply(len).mean()} entries per symbol)')
df_execs.head()