Exemplo n.º 1
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']

        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()
Exemplo n.º 2
0
    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']
Exemplo n.º 3
0
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')
Exemplo n.º 4
0
    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'])
Exemplo n.º 5
0
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')
Exemplo n.º 6
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.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
Exemplo n.º 7
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()
Exemplo n.º 8
0
    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
Exemplo n.º 9
0
        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']
Exemplo n.º 10
0
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))
Exemplo n.º 11
0
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))