def get(self): # NOTE using prior quarter fundamentals for complete dataset
fp = f'{self.iodir}/all_fundamentals.xlsx'
# print(f'Modified: {pd.to_datetime(self.get_modified_time(fp))}')
print(f'Today: {cal.today()}')
if self.ticker:
df = pd.DataFrame(nasdaqdatalink.get_table(self.name, dimension="MRQ", ticker = self.ticker))
else:
df = nasdaqdatalink.get_table(self.name, dimension="MRQ", calendardate=[self.calendardate], paginate=True)
df['shequity'] = df['assets'] - (df['liabilities'] )
df['roe'] = df['netinc'] / (df['equity'] )
df['roc'] = df['netinc'] / (df['equity'] + df['debt'])
df['roa'] = df['netinc'] / df['assets']
df['ev/ebitda'] = df['ev'] / df['ebitda']
df['p/cf'] = df['marketcap'] / df['fcf']
df['opp margin'] = df['opinc'] / df['revenue']
df['interest coverage'] = df['ebit'] / df['intexp']
df['adj netinc'] = df['netinc'] + df['rnd'] # + lease expense
df['adj oppinc'] = df['ebit'] + df['rnd'] # + lease expense
df['net capex'] = df['capex'] + df['depamor']
df['non cash workingcapital'] = df['assetsc'] - df['cashneq'] - df['liabilitiesc']
df['change nc workingcapital'] = df['non cash workingcapital'] - df['non cash workingcapital'].shift(-1, axis=0)
df['total expense'] = df['revenue'] - df['netinc']
df['tax rate'] = round((df['taxexp'] / df['ebt']) *100, 2)
df['opp margin'] = round(df['opp margin'] *100, 2)
df['dps'] = (df['ncfdiv'] + df['prefdivis']) / df['sharesbas'] # dividends per share
df['payoutratio'] = df['dps'] / df['eps']
df['nopat'] = (df['opinc']) * (1-df['tax rate']/100)
df['equity reinvested'] = (df['capex'] - df['depamor']) + df['change nc workingcapital'] - df['ncfdebt']
df['retention ratio'] = df['retearn'] / df['netinc']
df['expected netinc growth'] = df['retention ratio'] * df['roe']
df['expected roe growth'] = df['ebit'] * (1 - df['tax rate']/100) / (df['equity'] + df['debt'])
df['marginal return on equity'] = (df['netinc'] - df['netinc'].shift(-1, axis=0)) / df['equity']
df['reinvestment rate'] = ((df['capex'] - df['net capex'] + df['change nc workingcapital']) / df['ebit']) * (1-df['tax rate']/100)
df['expected ebit growth'] = df['reinvestment rate'] * df['roc']
df['fcf firm'] = df['opinc'] * (1-df['tax rate']) + (df['capex'] - df['depamor']) - df['change nc workingcapital'] # FIXME
df['fcf equity'] = df['netinc'] - (df['capex'].abs() - df['depamor']) - df['change nc workingcapital'].abs() + df['ncfdebt']
df['increase in current assets'] = df['assetsc'] - df['assetsc'].shift(-1, axis=0)
df['increase in current liabilities'] = df['liabilitiesc'] - df['liabilitiesc'].shift(-1, axis=0)
df['_fcf'] = df['netinc'] + df['depamor'] + df['intexp'] + df['ncfinv'] + df['ncfbus'] + df['ncff'] - df['change nc workingcapital'].abs() - df['capex'].abs()
self.df = df
df = yf.download([self.ticker, 'SPY'], '2021-01-01')['Adj Close']
price_change = df.pct_change()
df = price_change.drop(price_change.index[0])
self.wacc(df)
return self.df
def get(self, date, institution=None):
# self.df = nasdaqdatalink.bulkdownload("SF3")
'''
TODO; pass multiple dates and specific institution - return df
'''
df = nasdaqdatalink.get_table(self.name, paginate=True, calendardate=date, investorname=institution)
print(f'[SUCCEESS] Retrieved {institution} data for {date}: {len(df)} rows')
return df
def get(self):
fp = f'{self.iodir}/all_tickers.xlsx'
if not os.path.exists(fp) or (pd.to_datetime(pd.to_datetime(self.get_modified_time(fp)).strftime('%Y-%m-%d')) < pd.to_datetime(pd.to_datetime(utc.localize(cal.today())).strftime('%Y-%m-%d')) ):
print('Tickers File does not exist or has not been updated today. Downloading full query results...')
print(f'Modified: {pd.to_datetime(self.get_modified_time(fp))}')
print(f'Today: {cal.today()}')
df = nasdaqdatalink.get_table(self.name, table="SF1", paginate=True ) #qopts={"columns":"compnumber"}, date = { 'gte': '2016-01-01', 'lte': '2016-12-31' })
df = df.loc[(df.isdelisted == 'N') & (df.lastpricedate == max(df.lastpricedate)) ]
df = df.drop_duplicates(subset=['ticker', 'name'])
df.to_excel(fp)
else:
print('Tickers data has been updated today - reading from file')
df = pd.read_excel(fp)
return df
def get_calendardate_history(self):
return nasdaqdatalink.get_table(self.name, dimension="MRQ", paginate=True).calendardate.unique()
def get(self): # NOTE using prior quarter fundamentals for complete dataset
df = nasdaqdatalink.get_table(self.name, date=[cal.previous_quarter_end()], paginate=True) ##qopts={"columns":"compnumber"}, date = { 'gte': '2016-01-01', 'lte': '2016-12-31' })
return df
def fundamentals(request):
ticker = request.POST.get("ticker")
selected_metric = request.POST.get("metric")
print(selected_metric)
if selected_metric in [None, '', '[None]']:
selected_metric = 'pe'
if ticker in [None, '', '[None]']:
ticker = 'AMZN'
print(ticker)
print(request.POST)
engine = Postgres().engine
ndq = Nasdaq()
ndq.authenticate()
# boxplots
ticker_data = nasdaqdatalink.get_table('SHARADAR/TICKERS', ticker=ticker)
industry = ticker_data['industry'].iloc[0]
sector = ticker_data['sector'].iloc[0]
view_fields = [
selected_metric,
'netinc',
'roe',
'roa',
'roc',
'pe',
'pb',
'ps',
'divyield',
'ev/ebitda',
'p/cf',
'opp margin',
'bvps',
'price',
'interest coverage',
'payoutratio',
]
data_of_selected_company = pd.DataFrame(
Fundamentals(ticker=ticker).get().iloc[0]).transpose()
data_of_selected_company.rename(columns={'calendardate': 'date'},
inplace=True)
calendardate = pd.to_datetime(
data_of_selected_company['date'].values[0]).strftime('%Y-%m-%d')
box_plot_values = []
company_values = []
for metric in [selected_metric]:
# Sector
sector_percentiles = pd.read_sql_table(
'Percentiles_Sector', engine
) # a list of values representing the min, max, median, 1st and 3rd quartile
sector_percentiles = sector_percentiles.loc[sector_percentiles.sector
== sector]
sector_percentiles_values = [
float(str(x).replace(',', '')) for x in sector_percentiles[metric]
]
# Industry
industry_percentiles = pd.read_sql_table(f'Percentiles_Industry',
engine)
industry_percentiles = industry_percentiles.loc[
industry_percentiles.industry == industry]
industry_percentiles_values = [
float(str(x).replace(',', ''))
for x in industry_percentiles[metric]
]
# Values
box_plot_values.append([sector_percentiles_values] +
[industry_percentiles_values])
company_values.append([data_of_selected_company[metric].values[0]] +
[data_of_selected_company[metric].values[0]])
print(box_plot_values)
print(company_values)
colnames = [
x for x in sector_percentiles.columns.tolist()
if x not in ['level_0', 'index', 'date', 'uid']
] # all datapoints
colnames = [
'bvps',
'pe',
'dps',
'eps',
'divyield',
'fcfps',
'grossmargin',
'pe',
'roa',
'roe',
'roc',
'ps',
'opp margin',
'p/cf',
'ev/ebitda',
] # selected datapoints
# industry percetiles v. metric over time
data = Fundamentals(ticker=ticker).get()
line_chart_json_list = []
for metric in [selected_metric]:
metric_data = data[['calendardate', metric]]
metric_data['calendardate'] = [
x.strftime('%Y%m%d') for x in metric_data['calendardate']
]
frames = []
for k, v in {
'Quartile_Values_Over_Time_Median_by_Industry': 'Median',
'Quartile_Values_Over_Time_Upper_by_Industry': 'Upper',
'Quartile_Values_Over_Time_Lower_by_Industry': "Lower"
}.items():
x = pd.read_sql_table(k, engine)
x = x[['date',
metric]].loc[x.industry == industry].rename(columns={
'date': 'calendardate',
metric: v
})
x['calendardate'] = [
pd.to_datetime(x).strftime('%Y%m%d') for x in x['calendardate']
]
frames.append(x)
cbind = metric_data
for df in frames:
cbind = cbind.merge(df, on='calendardate')
for c in cbind:
cbind[c] = cbind[c].apply(lambda x: float(str(x).replace(',', '')))
cbind.drop('calendardate', axis=1, inplace=True)
linechart_data = ndq.to_highcharts(cbind)
linechart_data = json.loads(linechart_data)
for i in range(len(linechart_data)):
if i > 0:
linechart_data[i]["color"] = '#D3D3D3'
line_chart_json_list.append(json.dumps(linechart_data))
data_of_selected_company = data_of_selected_company[['date'] + view_fields]
data_of_selected_company['Peer Group'] = 'N/A'
data_of_selected_company['Description'] = 'Values as reported'
for c in [
x for x in data_of_selected_company.columns
if x not in ['date', 'Peer Group', 'Description']
]:
try:
data_of_selected_company[c] = "{:,}".format(
float(data_of_selected_company[c]))
except:
pass
industry_ranks_of_selected_company = pd.read_sql_table(
'Ranks_Industry', engine)
industry_ranks_of_selected_company = industry_ranks_of_selected_company[
['date'] + view_fields].loc[industry_ranks_of_selected_company.ticker
== ticker.upper()]
industry_ranks_of_selected_company['Peer Group'] = 'Industry'
industry_ranks_of_selected_company[
'Description'] = 'Percentile Rank against Industry Peer Group'
sector_ranks_of_selected_company = pd.read_sql_table(
'Ranks_Sector', engine)
sector_ranks_of_selected_company = sector_ranks_of_selected_company[
['date'] + view_fields].loc[sector_ranks_of_selected_company.ticker ==
ticker.upper()]
sector_ranks_of_selected_company['Peer Group'] = 'Sector'
sector_ranks_of_selected_company[
'Description'] = 'Percentile Rank against Sector Peer Group'
percentile_values_of_industry = pd.read_sql_table('Percentiles_Industry',
engine)
percentile_values_of_industry = percentile_values_of_industry[
['date'] +
view_fields].loc[(percentile_values_of_industry.industry == industry) &
(percentile_values_of_industry['index'] == 'median')]
percentile_values_of_industry['Peer Group'] = 'Industry'
percentile_values_of_industry[
'Description'] = 'Median Percentile Value amoung Industry Peer Group'
percentile_values_of_sector = pd.read_sql_table('Percentiles_Sector',
engine)
percentile_values_of_sector = percentile_values_of_sector[
['date'] +
view_fields].loc[(percentile_values_of_sector.sector == sector)
& (percentile_values_of_sector['index'] == 'median')]
percentile_values_of_sector['Peer Group'] = 'Sector'
percentile_values_of_sector[
'Description'] = 'Median Percentile Value amoung Sector Peer Group'
company_fundamentals = pd.concat([
data_of_selected_company,
percentile_values_of_industry,
percentile_values_of_sector,
industry_ranks_of_selected_company,
sector_ranks_of_selected_company,
],
axis=0,
ignore_index=True)
company_fundamentals = company_fundamentals[
['Description', 'Peer Group', 'date'] + [
x for x in company_fundamentals.columns.tolist()
if x not in ['Description', 'Peer Group', 'date']
]]
context = {
'selected_ticker': ticker,
'sector': sector,
'industry': industry,
'calendardate': calendardate,
'colnames': colnames,
# boxplots
'box_plot_values_1': box_plot_values[0],
'selected_company_values_1': company_values[0],
# 'box_plot_values_2': box_plot_values[1],
# 'selected_company_values_2': company_values[1],
# industry percentiles over time
'line_chart_values_1': line_chart_json_list[0],
# 'line_chart_values_2':line_chart_json_list[1],
'company_fundamentals': company_fundamentals,
'values': company_fundamentals.values.tolist(),
}
return render(request, 'fundamentals.html', context)