def setUp(self):
userdir = user_dir()
self.database = userdir['postgresql']['pysecmaster_db']
self.user = userdir['postgresql']['pysecmaster_user']
self.password = userdir['postgresql']['pysecmaster_password']
self.host = userdir['postgresql']['pysecmaster_host']
self.port = userdir['postgresql']['pysecmaster_port']
cur_posix_time = str(datetime.now().timestamp())
cur_posix_time = cur_posix_time[:cur_posix_time.find('.')]
self.yahoo_fin_url = {
'root': 'https://query1.finance.yahoo.com/v7/finance/download/',
'start_date': 'period1=0', # First POSIX time (whole hist)
'end_date': 'period2=' + cur_posix_time, # Cur POSIX time
'interval': 'interval=', # 1d, 1w, 1mo: (daily, wkly, mthly)
'events': 'events=', # history, div, split
'cookie': 'crumb=', # Cookie value
}
self.csv_wo_data = 'test_yahoo_daily_wo_data.csv'
with open(self.csv_wo_data, 'w') as csv_file:
writer = csv.writer(csv_file)
writer.writerow(['tsid', 'date_tried'])
self.exchanges_df = self.query_exchanges()
def setUp(self):
self.userdir = user_dir()['postgresql']
self.db_name = self.userdir['pysecmaster_test_db']
self.user = self.userdir['pysecmaster_test_user']
self.password = self.userdir['pysecmaster_test_password']
self.host = self.userdir['pysecmaster_test_host']
self.port = self.userdir['pysecmaster_test_port']
def create_database(admin_user='******',
admin_password='******',
database='pysecmaster',
user='******'):
""" Determine if the provided database exists within the postgres server.
If the database doesn't exist, create it using the provided user as the
owner. This requires connecting to the default database before psycopg2 is
able to send an execute command.
NOTE: The provided user must have a valid login role within postgres before
they are able to log into the server and create databases.
:param admin_user: String of the database admin user
:param admin_password: String of the database admin password
:param database: String of the database to create
:param user: String of the user who should own the database
"""
userdir = user_dir()['postgresql']
conn = psycopg2.connect(database=userdir['main_db'],
user=admin_user,
password=admin_password,
host=userdir['main_host'],
port=userdir['main_port'])
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
try:
with conn:
cur = conn.cursor()
cur.execute("""SELECT datname FROM pg_catalog.pg_database
WHERE lower(datname)=lower('%s')""" % database)
database_exist = cur.fetchone()
if not database_exist:
cur.execute("""CREATE DATABASE %s OWNER %s""" %
(database, user))
else:
print('The %s database already exists.' % database)
cur.close()
except psycopg2.Error as e:
conn.rollback()
print('Failed to create the %s database' % database)
print(e)
return
except conn.OperationalError:
print('Unable to connect to the SQL Database in create_database. Make '
'sure the database address/name are correct.')
return
except Exception as e:
print(e)
raise SystemError(
'Error: An unknown issue occurred in create_database')
def setUp(self):
userdir = user_dir()
quandl_token = userdir['quandl']['quandl_token']
db_url = ['https://www.quandl.com/api/v1/datasets/', '.csv']
self.qd = QuandlDownload(quandl_token=quandl_token, db_url=db_url)
self.csv_wo_data = 'test_quandl_codes_wo_data.csv'
with open(self.csv_wo_data, 'w') as csv_file:
writer = csv.writer(csv_file)
writer.writerow(['q_code', 'date_tried'])
def create_database(admin_user='******', admin_password='******',
database='pysecmaster', user='******'):
""" Determine if the provided database exists within the postgres server.
If the database doesn't exist, create it using the provided user as the
owner. This requires connecting to the default database before psycopg2 is
able to send an execute command.
NOTE: The provided user must have a valid login role within postgres before
they are able to log into the server and create databases.
:param admin_user: String of the database admin user
:param admin_password: String of the database admin password
:param database: String of the database to create
:param user: String of the user who should own the database
"""
userdir = user_dir()['postgresql']
conn = psycopg2.connect(database=userdir['main_db'],
user=admin_user,
password=admin_password,
host=userdir['main_host'],
port=userdir['main_port'])
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
try:
with conn:
cur = conn.cursor()
cur.execute("""SELECT datname FROM pg_catalog.pg_database
WHERE lower(datname)=lower('%s')""" % database)
database_exist = cur.fetchone()
if not database_exist:
cur.execute("""CREATE DATABASE %s OWNER %s""" %
(database, user))
else:
print('The %s database already exists.' % database)
cur.close()
except psycopg2.Error as e:
conn.rollback()
print('Failed to create the %s database' % database)
print(e)
return
except conn.OperationalError:
print('Unable to connect to the SQL Database in create_database. Make '
'sure the database address/name are correct.')
return
except Exception as e:
print(e)
raise SystemError('Error: An unknown issue occurred in create_database')
def setUp(self):
userdir = user_dir()
self.database = userdir['postgresql']['pysecmaster_db']
self.user = userdir['postgresql']['pysecmaster_user']
self.password = userdir['postgresql']['pysecmaster_password']
self.host = userdir['postgresql']['pysecmaster_host']
self.port = userdir['postgresql']['pysecmaster_port']
self.nasdaq_sector_industry_url = 'http://www.nasdaq.com/screening/' \
'companies-by-industry.aspx?'
self.nasdaq_sector_industry_extractor_exchanges = [
'NASDAQ', 'NYSE', 'AMEX'
]
self.nasdaq_sector_industry_redownload_time = 0
def setUp(self):
userdir = user_dir()
self.database = userdir['postgresql']['pysecmaster_db']
self.user = userdir['postgresql']['pysecmaster_user']
self.password = userdir['postgresql']['pysecmaster_password']
self.host = userdir['postgresql']['pysecmaster_host']
self.port = userdir['postgresql']['pysecmaster_port']
self.google_fin_url = {
'root': 'http://www.google.com/finance/getprices?',
'ticker': 'q=',
'exchange': 'x=',
'interval': 'i=', # 60; 60 seconds is the shortest interval
# 'sessions': 'sessions=ext_hours',
'period': 'p=', # 20d; 15d is the longest period for min
'fields': 'f=d,c,v,o,h,l',
} # order doesn't change anything
self.exchanges_df = self.query_exchanges()
except conn.OperationalError:
raise SystemError(
"Unable to connect to the %s database in "
"pull_minute_prices. Make sure the database "
"address/name are correct." % database
)
except Exception as e:
print(e)
raise SystemError("Error: Unknown issue occurred in pull_minute_prices")
if __name__ == "__main__":
from utilities.user_dir import user_dir
userdir = user_dir()
test_database = userdir["postgresql"]["pysecmaster_db"]
test_user = userdir["postgresql"]["pysecmaster_user"]
test_password = userdir["postgresql"]["pysecmaster_password"]
test_host = userdir["postgresql"]["pysecmaster_host"]
test_port = userdir["postgresql"]["pysecmaster_port"]
test_query_type = "ticker" # index, ticker
test_tsid = "AAPL.Q.0"
test_data_vendor_id = 15 # pySecMaster_Consensus
# test_data_vendor_id = 12 # Google_Finance
test_beg_date = "1950-01-01 00:00:00"
test_end_date = "2016-12-30 00:00:00"
frequency = "daily" # daily, minute
item in the database, along with translating different source's codes
Database data download tasks:
Downloads Quandl data based on the download selection criteria using either
the official Quandl Codes or implied codes from CSI Data.
Downloads Google Finance minute stock data.
Can either append only the new data, or replace part of the existing data.
Future expansions:
Implement daily option chain data (from Google or Yahoo).
'''
###############################################################################
# Database maintenance options:
userdir = user_dir()
csidata_type = 'stock' # stock, commodity
# Don't change these unless you know what you are doing
database_url = ['https://www.quandl.com/api/v2/datasets.csv?query=*&'
'source_code=', '&per_page=300&page=']
# http://www.csidata.com/factsheets.php?type=stock&format=html
csidata_url = 'http://www.csidata.com/factsheets.php?'
tables_to_load = ['data_vendor', 'exchanges']
symbology_sources = ['csi_data', 'tsid', 'quandl_wiki', 'quandl_goog',
'seeking_alpha', 'yahoo']
nasdaq_sector_industry_url = 'http://www.nasdaq.com/screening/' \
'companies-by-industry.aspx?'
nasdaq_sector_industry_extractor_exchanges = ['NASDAQ', 'NYSE', 'AMEX']
def main(verbose=False):
""" Move all values from the minute_prices table of the sqlite_database
to the postgre database.
:param verbose: Boolean of whether to print debugging statements
"""
sqlite_database = 'C:/Users/joshs/Programming/Databases/pySecMaster/' \
'pySecMaster_m.db'
from utilities.user_dir import user_dir
userdir = user_dir()
postgre_database = userdir['postgresql']['pysecmaster_db']
postgre_user = userdir['postgresql']['pysecmaster_user']
postgre_password = userdir['postgresql']['pysecmaster_password']
postgre_host = userdir['postgresql']['pysecmaster_host']
postgre_port = userdir['postgresql']['pysecmaster_port']
table = 'minute_prices'
# Get a list of unique tsids from the sqlite database's table
tsid_list = query_all_tsids_from_table(database=sqlite_database,
table=table)
# Iterate through each tsid from the unique list
for tsid in tsid_list:
tsid_start = time.time()
# Query all of the tsid's table prices from the sqlite database
existing_price_df = query_all_tsid_prices(database=sqlite_database,
table=table,
tsid=tsid)
if len(existing_price_df) > 0:
# Add the source column using 'tsid' between data_vendor_id and tsid
existing_price_df.insert(1, 'source', 'tsid')
# Change the tsid column name to 'source_id'
existing_price_df.rename(columns={'tsid': 'source_id'},
inplace=True)
# existing_price_df['date'].tz_localize('UTC')
existing_price_df['date'] = \
pd.to_datetime(existing_price_df['date'], utc=True)
# print(existing_price_df.head())
# existing_price_df.to_csv('%s_min_prices.csv' % tsid)
# Insert all the prices into the postgre database
insert_df_to_db(database=postgre_database,
user=postgre_user,
password=postgre_password,
host=postgre_host,
port=postgre_port,
price_df=existing_price_df,
table=table,
verbose=verbose)
else:
# Should never happen, since the tsid wouldn't have been used anyway
if verbose:
print('No existing data found in the Sqlite3 database for %s' %
tsid)
if verbose:
print('Verifying the %s times for %s took %0.2f seconds' %
(table, tsid, time.time() - tsid_start))
def main(verbose=False):
""" Move all values from the minute_prices table of the sqlite_database
to the postgre database.
:param verbose: Boolean of whether to print debugging statements
"""
sqlite_database = 'C:/Users/joshs/Programming/Databases/pySecMaster/' \
'pySecMaster_m.db'
from utilities.user_dir import user_dir
userdir = user_dir()
postgre_database = userdir['postgresql']['pysecmaster_db']
postgre_user = userdir['postgresql']['pysecmaster_user']
postgre_password = userdir['postgresql']['pysecmaster_password']
postgre_host = userdir['postgresql']['pysecmaster_host']
postgre_port = userdir['postgresql']['pysecmaster_port']
table = 'minute_prices'
# Get a list of unique tsids from the sqlite database's table
tsid_list = query_all_tsids_from_table(database=sqlite_database,
table=table)
# Iterate through each tsid from the unique list
for tsid in tsid_list:
tsid_start = time.time()
# Query all of the tsid's table prices from the sqlite database
existing_price_df = query_all_tsid_prices(database=sqlite_database,
table=table, tsid=tsid)
if len(existing_price_df) > 0:
# Add the source column using 'tsid' between data_vendor_id and tsid
existing_price_df.insert(1, 'source', 'tsid')
# Change the tsid column name to 'source_id'
existing_price_df.rename(columns={'tsid': 'source_id'},
inplace=True)
# existing_price_df['date'].tz_localize('UTC')
existing_price_df['date'] = \
pd.to_datetime(existing_price_df['date'], utc=True)
# print(existing_price_df.head())
# existing_price_df.to_csv('%s_min_prices.csv' % tsid)
# Insert all the prices into the postgre database
insert_df_to_db(database=postgre_database, user=postgre_user,
password=postgre_password, host=postgre_host,
port=postgre_port, price_df=existing_price_df,
table=table, verbose=verbose)
else:
# Should never happen, since the tsid wouldn't have been used anyway
if verbose:
print('No existing data found in the Sqlite3 database for %s' %
tsid)
if verbose:
print('Verifying the %s times for %s took %0.2f seconds' %
(table, tsid, time.time() - tsid_start))
