def stockpridownload(stockname):
ic = ((len(stockname)) - 1)
df_port = pd.DataFrame()
idx_sn = 1
pricex = quandl.get_table('WIKI/PRICES',
ticker=stockname[0],
qopts={'columns': ['date', 'adj_close']},
date={
'gte': '2014-1-1',
'lte': '2019-12-31'
},
paginate=True)
df_port = pd.concat([df_port, pricex], axis=1)
for idx_sn in range(ic):
pricex = quandl.get_table('WIKI/PRICES',
ticker=stockname[idx_sn + 1],
qopts={'columns': ['adj_close']},
date={
'gte': '2014-1-1',
'lte': '2019-12-31'
},
paginate=True)
pd.DataFrame(pricex)
pprix = pricex['adj_close']
df_port = pd.concat([df_port, pprix], axis=1)
idx_sn = idx_sn + 1
df_port = df_port.set_index('date')
df_port.columns = stockname
return df_port
def test_get_table_calls_connection_with_params_for_post_request(
self, mock):
RequestType.USE_GET_REQUEST = False
params = {
'ticker': ['AAPL', 'MSFT'],
'per_end_date': {
'gte': '2015-01-01'
},
'qopts': {
'columns': ['ticker', 'per_end_date']
},
'foo': 'bar',
'baz': 4
}
expected_params = {
'ticker': ['AAPL', 'MSFT'],
'per_end_date.gte': '2015-01-01',
'qopts.columns': ['ticker', 'per_end_date'],
'foo': 'bar',
'baz': 4
}
quandl.get_table('ZACKS/FC', **params)
expected = call('post', 'datatables/ZACKS/FC', json=expected_params)
self.assertEqual(mock.call_args, expected)
def test_get_table_calls_connection_with_no_params_for_post_request(
self, mock):
RequestType.USE_GET_REQUEST = False
quandl.get_table('ZACKS/FC')
expected = call('post', 'datatables/ZACKS/FC', json={})
self.assertEqual(mock.call_args, expected)
def get_prices(ticker,dfrom ='2015-1-1',dto='2018-1-10'):
data = quandl.get_table('WIKI/PRICES', ticker=ticker,
qopts={'columns': ['date', 'Open','ticker']},
date={'gte': dfrom, 'lte': dto}, paginate=True)
data2 = quandl.get_table('WIKI/PRICES', ticker=ticker[0],
qopts={'columns': ['date', 'Open', 'ticker']},
date={'gte': dfrom, 'lte': dto}, paginate=True)
dates= data2['date'].sort_values()
res=[]
clean = data.set_index('date')
table = clean.pivot(columns='ticker')
returns_daily = table
data = returns_daily
data = data.dropna(axis=0)
data.fillna(method='ffill', inplace=True)
data.sort_index(inplace=True)
res.append(data)
res.append(dates)
return res
def quick_screener_fg(revenue_growth=0.15, num_yrs=5, output=False):
#tickers_df = quandl.get_table('SHARADAR/TICKERS',paginate=True)
SF1_df = quandl.get_table('SHARADAR/SF1', paginate=True)
screened_stocks = []
count = 0
for t in tickers_df['ticker'].unique():
if count % 100 == 0:
print("Tickers at", count, ":", screened_stocks)
#high revenue growth
try:
SF1_df_t = quandl.get_table('SHARADAR/SF1',
ticker=t,
dimension='MRY')[::-1]
SF1_df_t['revenue_growth'] = SF1_df_t['revenue'].pct_change()
mean_rev_growth = SF1_df_t['revenue_growth'][:num_yrs].mean()
if mean_rev_growth > revenue_growth:
#low relative valuation
ticker_industry = tickers_df[tickers_df['ticker'] ==
t]['industry'].values
if ticker_industry != []:
competitors = tickers_df[
tickers_df['industry'] ==
ticker_industry[0]]['ticker'].values
if competitors != []:
mean_EBITDA_multiple = SF1_df[SF1_df['ticker'].isin(
competitors)]['evebitda'].mean()
mean_PS_multiple = SF1_df[SF1_df['ticker'].isin(
competitors)]['ps'].mean()
ticker_evebitda = SF1_df_t['evebitda'][0]
ticker_ps = SF1_df_t['ps'][0]
if output:
print("Ticker:", t)
print("\tEBITDA Multiple:", ticker_evebitda)
print("\tPS:", ticker_ps)
print("\tRevenue Growth:", mean_rev_growth)
if (ticker_evebitda < 0) or (mean_EBITDA_multiple < 0):
if ticker_ps < mean_PS_multiple:
screened_stocks.append(t)
print('BUY:', t)
else:
if ticker_evebitda < mean_EBITDA_multiple:
screened_stocks.append(t)
print('BUY:', t)
count += 1
except:
print('Unknown error. Please manually analyze.')
return screened_stocks
def quick_screener_bs(output=False):
screened_stocks = []
#tickers_df = quandl.get_table('SHARADAR/TICKERS',paginate=True)
for t in tickers_df['ticker'].unique():
if output:
print('Ticker', t)
try:
SF1_df = quandl.get_table('SHARADAR/SF1',
ticker=t,
dimension='MRY')[::-1]
cashneq = SF1_df['cashnequsd']
totaldebt = SF1_df['debtusd']
num_shares = SF1_df['shareswa']
tangible_assets = SF1_df['tangibles']
total_liabilities = SF1_df['liabilities']
marketcap = SF1_df['marketcap']
SF1_df = quandl.get_table('SHARADAR/SF1',
ticker=t,
dimension='MRY')[::-1]
#(cash - total debt) per share
SF1_df['net_debt_ps'] = (cashneq - totaldebt) / num_shares
#cash per share
SF1_df['cash_ps'] = cashneq / num_shares
#change in cash y/y
SF1_df['pct_change_cash'] = cashneq.pct_change()
#tangible book value per share
SF1_df['ptb'] = marketcap / (tangible_assets - total_liabilities)
#change in debt y/y
SF1_df['pct_change_debt'] = totaldebt.pct_change()
high_cash = (SF1_df['net_debt_ps'][0] > 0)
low_ptb = (SF1_df['ptb'][0] < 1)
five_yr_decreasing_debt = (SF1_df['pct_change_debt'][:5].mean() <
0)
five_yr_increasing_cash = (SF1_df['pct_change_cash'][:5].mean() >
.07)
if output:
print('\tHigh Cash:', high_cash)
print('\tLow PTB:', low_ptb)
print('\t5 YR Decreasing Debt:', five_yr_decreasing_debt)
print('\t5 YR Increasing Cash:', five_yr_increasing_cash)
if (high_cash + low_ptb + five_yr_decreasing_debt +
five_yr_increasing_cash) >= 3:
screened_stocks.append(t)
except:
print('Unknown error. Please analyze manually.')
continue
return screened_stocks
def get_demo_simple_dense_data():
# Return format: X, y
train_table = quandl.get_table('SHARADAR/SF1',
calendardate='2010-12-31',
ticker='ZZ')
train_X = train_table[['assets', 'sps']] / 100000000
train_y = train_table['workingcapital'] / 100000000
test_table = quandl.get_table('SHARADAR/SF1',
calendardate='2011-12-31',
ticker='ZZ')
test_X = test_table[['assets', 'sps']] / 100000000
test_y = test_table['workingcapital'] / 100000000
return (train_X, train_y, test_X, test_y)
def asset_data_collect(companies = None, begin_date = None, end_date = None, columns = None):
#pull data
r_data = quandl.get_table('WIKI/PRICES', ticker=companies, qopts={'columns': columns},
date={'gte':begin_date, 'lte':end_date}, paginate=True)
index_data = r_data.set_index('date')
df = index_data.pivot(columns='ticker')
return df
def get_stock_data(self, stock_name):
"""
Return a dataframe of that stock and normalize all the values.
(Optional: create moving average)
"""
logger.info("Loading Stock [%s]...", stock_name)
df = quandl.get_table('WIKI/PRICES', ticker=stock_name, paginate=True)
df.drop([
'ticker', 'open', 'high', 'low', 'close', 'ex-dividend', 'volume',
'split_ratio'
],
1,
inplace=True)
df.set_index('date', inplace=True)
# Renaming all the columns so that we can use the old version code
df.rename(columns={
'adj_open': 'Open',
'adj_high': 'High',
'adj_low': 'Low',
'adj_volume': 'Volume',
'adj_close': HeaderFactory.Price
},
inplace=True)
df.sort_index(ascending=True, inplace=True)
df.dropna(inplace=True)
return df
def get_data(self, tokens):
if not tokens:
return None
data = self._cache_service.get_data(tokens)
if data is None or data.empty:
quandl.ApiConfig.api_key = self._config['quandl']['api_key']
tickers = [tokens['dependent_variable']]
if 'independent_variables' in tokens and isinstance(
tokens['independent_variables'], list):
tickers = tickers + tokens['independent_variables']
data = quandl.get_table(
'WIKI/PRICES',
qopts={'columns': ['ticker', 'date', 'close']},
ticker=tickers,
date={
'gte': tokens['date_from'],
'lte': tokens['date_to']
})
if not isinstance(data, pd.DataFrame):
return None
self._cache_service.save_data(tokens, data)
return data
def stock():
ticker_name = request.form['ticker_name']
quandl.ApiConfig.api_key = 'Y9QvH7-wD-UDNHcL2J_m'
data = quandl.get_table(
'WIKI/PRICES',
ticker=ticker_name,
qopts={
'columns':
['ticker', 'date', 'adj_close', 'close', 'open', 'adj_open']
},
paginate=True)
source = ColumnDataSource(data.set_index(['ticker']))
#r = requests.get('https://www.quandl.com/api/v3/datasets/WIKI/' + ticker_name + '.json?api_key=Y9QvH7-wD-UDNHcL2J_m')
#json_object = r.json()
#data = json_object['dataset']['data']
#source = {"date": [], "open": [], "adj_open": [], "close": [], "adj_close": []}
#for arr in data:
#source['date'].append(arr[0])
#source['open'].append(arr[1])
#source['adj_open'].append(arr[8])
#source['close'].append(arr[4])
#source['adj_close'].append(arr[11])
#source=ColumnDataSource(source)
col_active0 = request.form.getlist('check')
plot = create_figure(ticker_name, source,
col_active0) #col_active1, col_active2, col_active3)
script, div = components(plot)
#print(col_active0)
return render_template(
'about.html', script=script, div=div
) #ticker_name=ticker_name, col_active0=col_active0) #col_active1=col_active1,
def download_sf1_data():
fname = os.path.join(os.getcwd(), 'data_defensive', 'sf1.csv')
def batch(iterable, n=1):
l = len(iterable)
for ndx in range(0, l, n):
yield iterable[ndx:min(ndx + n, l)]
list_sf1 = get_sf1_symbols()
list_dfs = []
i = 1
for x in batch(list_sf1, 200):
print(f'Batch {i}: from {x[0]} to {x[-1]}')
columns = [
'ticker', 'calendardate', 'datekey', 'revenueusd', 'currentratio',
'eps', 'dps', 'bvps', 'price'
]
df = quandl.get_table('SHARADAR/SF1',
dimension='ARY',
ticker=x,
qopts={"columns": columns},
paginate=True)
df.sort_values(['ticker', 'calendardate'], inplace=True)
list_dfs.append(df)
i += 1
final_df = pd.concat(list_dfs, axis=0)
final_df.to_csv(fname, index=False)
def plotStockPrice(symbol, plotOptions=[]):
# ['ticker', 'date', 'open', 'close', 'adj_open', 'adj_close']
cols = ['ticker', 'date'] + plotOptions
tmp=quandl.get_table('WIKI/PRICES',\
qopts = { 'columns': cols },\
ticker = symbol)
if (tmp.empty):
return ("", "")
p = figure(plot_width=800,\
plot_height=300,\
x_axis_type="datetime",\
title="{:s} stock price".format(symbol))
p.title.text_color = "black"
p.title.text_font = "arial"
for label, color in zip(plotOptions, Spectral4):
p.line(tmp['date'],\
tmp[label],\
color=color,\
legend=label,\
alpha=0.8,\
line_width=5)
p.xaxis.axis_label = "Date"
p.yaxis.axis_label = "Stock price"
p.legend.click_policy = "hide"
script, div = components(p)
return (script, div)
def create_dataframe(self):
"""
Uses Quandl to retrieve stock's financial data. Calculates 20 day mean of closing prices as well as
2 standard deviations above and below those prices (Bollinger Bands).
"""
try:
# Creates dataframe using user inputs and stock data provided by Quandl
data = quandl.get_table('WIKI/PRICES',
ticker=self.ticker,
qopts={'columns': ['date', 'close']},
date={
'gte': self.start_date,
'lte': self.end_date
})
stock_df = data.set_index('date')
except:
# If dataframe cannot be created (usually because of firewall settings), prints error message and proceeds to end of program
print("ERROR: COULD NOT CREATE DATAFRAME!")
else:
# Adds column to stock df for 20 day mean of closing prices
stock_df['close: 20 day mean'] = stock_df['close'].rolling(
20).mean()
# Adds column for 20 day mean of closing prices plus two standard deviations
stock_df['upper'] = stock_df['close: 20 day mean'] + 2 * (
stock_df['close'].rolling(20).std())
# Adds column for 20 day mean of closing prices minus two standard deviations
stock_df['lower'] = stock_df['close: 20 day mean'] - 2 * (
stock_df['close'].rolling(20).std())
return stock_df
def quandl_api(userticker,scraped_data):
tickers = [userticker];
# api call to quandl, gte and lte are data bounds
data = quandl.get_table('WIKI/PRICES', ticker = tickers,
qopts = { 'columns': ['ticker', 'date', 'adj_close'] },
date = { 'gte': '2018-01-19', 'lte': '2018-10-20' },
paginate=True)
print(data.head(1))
# get most recent entry
onedata=data.head(1)
onedata = onedata.reset_index()
lastdate = onedata[['date'][0]]
lastdatevalue = lastdate[0]
lastclosing = onedata[['adj_close'][0]]
lastclosingvalue = float(lastclosing[0])
#convert date to human readable format
readabledate = "{:%B %d, %Y}".format(lastdatevalue)
# extract last closing of stock and conver to number
stripcomma = scraped_data["Previous Close"]
stripcomma = stripcomma.replace(',' , '')
currentclose = float(stripcomma)
# find difference in closing value
diff = currentclose - lastclosingvalue
diff = round(diff,2)
print(str(userticker), ' has had a change of', str(diff), 'since', readabledate)
# add change to scraped data file
strwrite = str(diff) + ' from ' + readabledate
scraped_data["change"] = strwrite
def _get_history_from_table(
self, tickers_of_single_db: Sequence[QuandlTicker],
fields: Sequence[str], start_date: datetime,
end_date: datetime) -> Dict[QuandlTicker, pd.DataFrame]:
# Possibly this method is not generic enough, but I couldn't find another table db to test it.
field_options = {}
if fields is not None:
columns = ['ticker', 'date'] + list(fields)
field_options['qopts'] = {'columns': columns}
db_name = tickers_of_single_db[0].database_name
result_dict = {}
tickers_str = [t.as_string() for t in tickers_of_single_db]
df = quandl.get_table(db_name,
ticker=tickers_str,
paginate=True,
**field_options)
# at this point we have a large DataFrame with rows corresponding to different tickers
# we group it by ticker
ticker_grouping = df.groupby('ticker')
for ticker_str, ticker_df in ticker_grouping:
ticker = QuandlTicker(ticker=ticker_str,
database_name=db_name,
database_type=QuandlDBType.Table)
dates_fields_values_df = self._format_single_ticker_table(
ticker_df, start_date, end_date)
result_dict[ticker] = dates_fields_values_df
return result_dict
def populate_raw_data(tickers, fields, raw_path):
"""tickers is a dict with the ticker string as the key and the SID
as the value. """
quandl_tools.set_api_key()
# existing = listdir(RAW_FLDR)
for ticker, sid in tickers.items():
# if "%d.csv" % sid in existing:
# continue
try:
query_str = "%s %s" % (DS_NAME, ticker)
print("fetching data for: {}".format(query_str))
# df = quandl.get_table(query_str, start_date=START_DATE, end_date=END_DATE)
df = quandl.get_table(
DS_NAME,
calendardate={
'gte': START_DATE,
'lte': END_DATE
},
ticker=ticker,
qopts={'columns': ['dimension', 'datekey'] + fields})
df = df[df.dimension == "ARQ"] # only use As-Reported numbers
# Change column name to field
df = df.rename(columns={"datekey": "Date"})
df = df.drop(["dimension"], axis=1)
# write raw file: raw/
df.to_csv(os.path.join(raw_path, "{}.csv".format(sid)))
except quandl.errors.quandl_error.NotFoundError:
print("error with ticker: {}".format(ticker))
def create_splits_df(sharadar_metadata_df, related_tickers, existing_tickers,
start):
splits_df = quandl.get_table('SHARADAR/ACTIONS',
date={'gte': start},
action=['split'],
paginate=True)
# Remove splits_df entries, whose ticker doesn't exist
tickers_splits = splits_df['ticker'].unique()
tickers_intersect = set(existing_tickers).intersection(tickers_splits)
splits_df = splits_df.loc[splits_df['ticker'].isin(tickers_intersect)]
# The date dtype is already datetime64[ns]
splits_df['value'] = 1.0 / splits_df['value']
splits_df.rename(
columns={
'value': 'ratio',
'date': 'effective_date',
},
inplace=True,
copy=False,
)
splits_df['ratio'] = splits_df['ratio'].astype(float)
splits_df['sid'] = splits_df['ticker'].apply(
lambda x: lookup_sid(sharadar_metadata_df, related_tickers, x))
splits_df.drop(['action', 'name', 'contraticker', 'contraname', 'ticker'],
axis=1,
inplace=True)
return splits_df
def __init__(self, path=None, factors_to_keep=None):
#Initially, going to be CSV dependent -> eventually may use REST? / Other API stream
#Dataframe
if path is None:
self.DF = quandl.get_table('ETFG/INDP',
date='2018-01-02',
ticker='SPY,GLD,EEM,EFA,TLT,JNK,IWM')
else:
self.DF = pd.read_csv(path) #WILL USE THIS WHEN FULL DATA
#Columns / Factors
if factors_to_keep is None:
self.factors = self.def_factors
else:
self.factors = factors_to_keep #COL NAMES
try:
self.DF = self.DF.drop(columns=[
col for col in self.DF.columns if col not in self.factors
])
except:
try:
self.DF = self.DF[self.factors]
except:
print('ERROR -- confirm factors are in dataframe.')
def request(self, **kwargs):
"""Request table data from quandl
Args:
**kwargs: keyword arguments for quandl request.
query: table to get data from.
filter: dictionary of filters for data. Depends on table.
columns: columns to select.
Raises:
IOError: if api key is not set.
ValueError: if filters kwarg is not a dict.
"""
if not self.check():
raise IOError("Connection is not valid")
filters = kwargs.get("filters", {})
if not isinstance(filters, dict):
raise ValueError("'filters' keyword argument must be a \
dictionary of valid Qaudnl column filters")
# set up queue options
qopts = dict()
if "columns" in kwargs:
qopts["columns"] = kwargs["columns"]
return quandl.get_table(kwargs["query"],
ticker=kwargs.get("ticker", None),
paginate=True,
qopts=qopts,
**filters)
def get_stock_data_basic(self, stock_name):
"""
Return a dataframe of that stock and normalize all the values.
(Optional: create moving average)
"""
df = quandl.get_table('WIKI/PRICES', ticker=stock_name)
df.drop([
'ticker', 'open', 'high', 'low', 'close', 'ex-dividend', 'volume',
'split_ratio'
],
1,
inplace=True)
df.set_index('date', inplace=True)
# Renaming all the columns so that we can use the old version code
df.rename(columns={
'adj_open': 'Open',
'adj_high': 'High',
'adj_low': 'Low',
'adj_volume': 'Volume',
'adj_close': 'Adj Close'
},
inplace=True)
df.dropna(inplace=True)
return df
def get_historical_fundamentals(self, stock_code, start_date=None, end_date=None):
data = quandl.get_table(
'SHARADAR/SF1',
ticker=stock_code
)
data = data.set_index('reportperiod')
data.index.names = ['date']
if start_date is not None and end_date is not None:
data = data[(data.index >= start_date) & (data.index < end_date)]
elif start_date is not None:
data = data[data.index >= start_date]
elif end_date is not None:
data = data[data.index < end_date]
data.sort_index(inplace=True)
data = data.drop(
columns=[
'ticker',
'dimension',
'calendardate',
'datekey',
'lastupdated'
]
)
return data
def plt_graph(ticker_name=None,start_date=None,end_date=None):
ticker_name= request.form['ticker_name']
start_date = request.form['start_date']
end_date = request.form['end_date']
#print(start_date)
quandl.ApiConfig.api_key = insert a valid key
#ticker_name="AAPL"
#start_date="2015-12-31"
#end_date="2016-12-31"
data = quandl.get_table('WIKI/PRICES', ticker = ticker_name,
qopts = { 'columns': ['ticker', 'date', 'adj_close'] },
date = { 'gte': start_date, 'lte': end_date },
paginate=True)
if len(data) == 0:
return ('','','Did not find any data, <br>' +
'try changing ticker name')
# create a new dataframe with 'date' column as index
new = data.set_index('date')
# use pandas pivot function to sort adj_close by tickers
clean_data = new.pivot(columns='ticker')
#remove hirarchy from column lables
clean_data.columns=clean_data.columns.droplevel()
# output to static HTML file
output_file(ticker_name+".html")
source = ColumnDataSource(data=clean_data)
p = figure(title=ticker_name, x_axis_type="datetime")
p.toolbar.active_drag = None
p.line(x='date', y=ticker_name, source=source)
script, div = components(p)
#show(p)
return render_template('tickerGraph.html', ticker_name=ticker_name, \
start_date=start_date, end_date=end_date, script=script, div=div)
def __init__(self,
ticker,
rfa="US10Y",
mi="SP500",
cr="BAA",
do_stats=False):
"""Import necessary Quandl stock data and, set ticker and risk-free
asset names and optionally calculate and set key statistics
rfa: either a specific FRED code for an asset or an asset preset
available in <Stock>._rfa_presets
mi: either a specific Yahoo! Finance market index code or an index
preset available in <Stock>._mi_presets
cr: Moody's credit rating
-- Currently only "AAA" and "BAA" are supported
"""
self.pgr = self.ke = self.roe = self.beta = \
self.FCFE = self.DDM = self.REL = None
self.ticker = ticker
self.data = quandl.get_table("SHARADAR/SF1", ticker=ticker)
self.data.set_index(pd.DatetimeIndex(self.data["calendardate"]),
inplace=True)
self._rfa = self._rfa_presets.get(rfa, rfa)
self._mi = self._mi_presets.get(mi, mi)
self._cr = cr
if do_stats:
self.do_stats(do_all=True)
def get_data_from_yahoo(reload_sp500=False):
if reload_sp500:
tickers = save_sp500_tickers()
else:
with open('sp500tickers.pickle', 'rb') as f:
tickers = pickle.load(f)
if not os.path.exists('stock_dfs'):
os.makedirs('stock_dfs')
start = dt.datetime(2000, 1, 1)
end = dt.datetime(2016, 12, 31)
for ticker in tickers:
if not os.path.exists('stock_dfs/{}.csv'.format(ticker)):
with open('stock_dfs/{}.csv', 'rb') as f:
# I am trying to take the df data (quandl) and make a csv
df = quandl.get_table(
'WIKI/PRICES',
qopts={'columns': ['ticker', 'date', 'close']},
ticker,
date={
'gte': '2016-01-01',
'lte': '2016-12-31'
})
df = print(
ticker,
quandl.get('WIKI/YHOO',
start_date='2000-1-1',
end_date='2016-12-31'))
df.to_csv('stock_dfs/{}.csv'.format(ticker))
else:
print('Already have {}'.format(ticker))
def fetch_sf1_table_date(api_key, start, end=None):
log.info("Start loading Sharadar SF1 fundamentals data from %s to %s..." % (start, "today" if end is None else end))
quandl.ApiConfig.api_key=api_key
df = quandl.get_table('SHARADAR/SF1', dimension=['ARQ','ART'], \
lastupdated={'gte':start,'lte':end}, qopts={'latest':1}, paginate=True)
return df
def getTickers_Names(tradingDates):
"""Gets all SP500 Tickers that are also available on Quandl from startDate."""
print("\nRETRIEVING SP500 TICKERS...\n")
# All Quandl tickers available at startDate
startTickers = quandl.get_table(
'WIKI/PRICES',
date=tradingDates[0],
paginate=True
)['ticker'].as_matrix()
# Get all SP500 tickers from Wikipedia
html = rq.get('http://en.wikipedia.org/wiki/List_of_S%26P_500_companies')
soup = bs.BeautifulSoup(html.text, 'lxml')
table = soup.find('table', {'class': 'wikitable sortable'})
# Store intersection of SP500 & Quandl tickers
tickers_dict = {}
print("Tickers mising from Quandl on start date:")
missing, total = 0, 0
for row in table.findAll('tr')[1:]:
total += 1
ticker = row.findAll('td')[0].text
name = row.findAll('td')[1].text
if ticker in startTickers:
tickers_dict[ticker] = name
else:
print(ticker)
missing += 1
print("\n{} -> {}".format(total, total - missing))
return tickers_dict
def download_tkr(tkr):
record_db_t_2017Q4 = quandl.get_table('SHARADAR/SF1',
calendardate='2017-12-31',
ticker=tkr,
dimension='MRY')
list_all = [record_db_t_2017Q4]
return list_all
def main():
# setting data frame
quandl.ApiConfig.api_key = 'AQ_X5HmEN_Wn1LNWCWfz'
df = quandl.get_table('WIKI/PRICES')
print(df.head())
df = df[['adj_open','adj_high','adj_low','adj_close','adj_volume']]
df['HL_PCT'] = (df['adj_high'] - df['adj_close']) / df['adj_close'] * 100.0
df['PCT_change'] = (df['adj_close'] - df['adj_open']) / df['adj_open'] * 100.0
exit()
df = df[['adj_close', 'HL_PCT', 'PCT_change', 'adj_volume']]
forecast_col = 'adj_close'
df.fillna(-9999, inplace=True)
forecast_out = int(math.ceil(0.01*len(df)))
df['label'] = df[forecast_col].shift(-forecast_out)
df.dropna(inplace=True)
# features
X = numpy.array(df.drop('label', 1))
# label
y = np.array(df['label'])
X = preprocessing.scale(X)
X = X[:-forecast_out+1]
df.dropna(inplace=True)
y = np.array(df['label'])
print(df.tail())
def generate_earnings_day_reports(ticker):
ten_q_urls = get_sec_ten_q_urls(ticker)
ten_q_soups = get_soups(ten_q_urls)
earnings_day_reports = []
max_date = ""
min_date = ""
for soup in ten_q_soups:
ten_q_info = {"date": get_date(soup)}
if max_date == "":
max_date = ten_q_info["date"]
min_date = ten_q_info["date"]
ten_q_info["sentiment"] = get_text_sentiment(get_text_from_filing_detail_soup(soup))
earnings_day_reports.append(ten_q_info)
stock_data = quandl.get_table('WIKI/PRICES', qopts={'columns': ['date', 'open', 'close']}, ticker=[ticker],
date={'gte': min_date, 'lte': max_date})
stock_data = stock_data.set_index("date")
for report in earnings_day_reports:
date = report["date"]
date = get_nearest_date(date, stock_data)
report["open"] = float(stock_data.loc[date, "open"])
report["close"] = float(stock_data.loc[date, "close"])
with open(ticker + '_info.json', 'w') as outfile:
json.dump(earnings_day_reports, outfile)
write_stock_history_json(stock_data, ticker)
def google_stocks(myTicker):
quandl.ApiConfig.api_key = "M5HWLF9jXDTrXrcWnpEe"
data = quandl.get_table(
'WIKI/PRICES',
qopts={
'columns':
['ticker', 'date', 'close', 'open', 'high', 'low', 'Volume']
},
ticker=[myTicker],
date={
'gte': '2017-06-01',
'lte': '2017-12-31'
})
for index in data['date']:
intDate = to_integer(index)
date1.append(
datetime.datetime.fromtimestamp(intDate).strftime('%d-%b-%Y'))
for index in data['close']:
closing_prices_list.append(float(index))
for index in data['open']:
openning_prices_list.append(float(index))
for index in data['high']:
High_prices_list.append(float(index))
for index in data['low']:
Low_prices_list.append(float(index))
i = 0
for row in date1:
final_date_list.append(i)
i = i + 1
def test_get_table_calls_connection_with_params_for_get_request(self, mock):
params = {'ticker': ['AAPL', 'MSFT'],
'per_end_date': {'gte': '2015-01-01'},
'qopts': {'columns': ['ticker', 'per_end_date']},
'foo': 'bar',
'baz': 4
}
expected_params = {'ticker[]': ['AAPL', 'MSFT'],
'per_end_date.gte': '2015-01-01',
'qopts.columns[]': ['ticker', 'per_end_date'],
'foo': 'bar',
'baz': 4
}
quandl.get_table('ZACKS/FC', **params)
expected = call('get', 'datatables/ZACKS/FC', params=expected_params)
self.assertEqual(mock.call_args, expected)
def test_datatable_with_code_returns_datatable_object(self, mock):
df = quandl.get_table('AR/MWCF', code="ICEP_WAC_Z2017_S")
self.assertIsInstance(df, pandas.core.frame.DataFrame)
database='ZACKS/FR'
quandl.ApiConfig.api_key='GWy95bbJS_AQmxhanhZP'
stockprice = quandl.Dataset('WIKI/AAPL').data()
print(str(stockprice))
print(type(stockprice))
print(stockprice[0].date)
print(stockprice[0].close)
dataset_data = quandl.Dataset('WIKI/AAPL').data(params={ 'start_date':'2001-01-01', 'end_date':'2010-01-01', 'collapse':'annual', 'transformation':'rdiff', 'rows':4 })
print(str(dataset_data))
data = quandl.get_table('WIKI/PRICES', qopts = { 'columns': ['ticker', 'date', 'close'] }, ticker = ['AAPL', 'MSFT'], date = { 'gte': '2016-01-01', 'lte': '2016-01-05' })
print(type(data))
print(data[:20])
'''
get_table into Python as a Pandas DataFrame
<class 'pandas.core.frame.DataFrame'>
pandas_dataframe=quandl.get_table(database)
print("Retrieved data for Zacks Fundamental Ratios - - - - ")
#use returns="pandas" to force to a dataframe but looks like it defaults.
# Show last xxx number of ROWS of the data
print("Printing FIRST 5 ROWS")
def getStockPrices():
initDate = date(2015,11,25)
endDate = date(2016,12,31)
ticker_list = []
all_stocks = quandl.get_table('WIKI/PRICES', qopts = {'columns': ['ticker', 'adj_open']}, date = {'gte': initDate.strftime('%Y-%m-%d'), 'lte': initDate.strftime('%Y-%m-%d')})
for index, row in all_stocks.iterrows():
if row.adj_open <= 5:
ticker_list.append(str(row.ticker))
feature_data = quandl.get_table('WIKI/PRICES', ticker = ticker_list, qopts = { 'columns': ['ticker', 'adj_close', 'adj_volume', 'adj_high', 'adj_low', 'adj_open', 'date'] }, date = { 'gte': initDate.strftime('%Y-%m-%d'), 'lte': endDate.strftime('%Y-%m-%d') }, paginate=True)
data_array = np.array([0,0,0,0,0])
ticker_id = np.array([0])
name = ''
j = 0
i = 0
remove = set()
print ("Set: {}".format(remove))
ticker_count = 0
max_ticker_count = 0
max_tick_index = set()
for index, row in feature_data.iterrows():
if row.ticker != name:
ticker_count = i
if ticker_count > max_ticker_count:
for _ in range(len(max_tick_index)):
remove.add(max_tick_index.pop())
#remove.add(max_tick_index)
max_ticker_count = ticker_count
max_tick_index.add(j)
elif ticker_count < max_ticker_count:
remove.add(j)
else:
max_tick_index.add(j)
name = row.ticker
j += 1
i = 0
adj_volume = row.adj_volume
#If the volume is zero, add the ticker id to the remove set
if (adj_volume == 0):
remove.add(j)
adj_close = row.adj_close
adj_high = row.adj_high
adj_low = row.adj_low
adj_open = row.adj_open
newRow = np.array([adj_close, adj_volume, adj_high, adj_low, adj_open])
data_array = np.vstack((data_array, newRow))
ticker_id = np.vstack((ticker_id, j))
i +=1
print (max_ticker_count)
ticker_id = ticker_id.astype(np.float64)
data_array = np.append(data_array, ticker_id, 1)
#loop through the array. If a row's ticker id is in the set, delete that row.
i = 0
z = 1
y = 0
print (len(remove))
while i < len(data_array):
if int(data_array[i,-1]) in remove:
print ("index: {} - tick: {}".format(i,data_array[i,-1]))
data_array = np.delete(data_array, (i), axis=0)
else:
i+=1
# #print(data_array.shape[0])
# print (z)
np.savetxt("foo.txt", data_array)
def test_get_table_calls_connection_with_no_params_for_post_request(self, mock):
RequestType.USE_GET_REQUEST = False
quandl.get_table('ZACKS/FC')
expected = call('post', 'datatables/ZACKS/FC', json={})
self.assertEqual(mock.call_args, expected)
def test_get_table_calls_connection_with_no_params_for_get_request(self, mock):
quandl.get_table('ZACKS/FC')
expected = call('get', 'datatables/ZACKS/FC', params={})
self.assertEqual(mock.call_args, expected)
def test_datatable_returns_datatable_object(self, mock):
df = quandl.get_table('ZACKS/FC', params={})
self.assertIsInstance(df, pandas.core.frame.DataFrame)
