Exemplo n.º 1
0
def insert_historical_option_data(rows):
    account = ut.get_azure_account()
    table_service = None
    table_name = 'optionSandbox'  # ut.TABLE_NAME_OPTIONS
    try:
        if config.IS_EMULATED:
            table_service = TableService(is_emulated=True)
        else:
            table_service = TableService(
                account_name=config.STORAGE_ACCOUNT_NAME,
                account_key=config.STORAGE_ACCOUNT_KEY)

        if not table_service.exists(table_name):
            # create the table
            try:
                table_service.create_table(table_name)
            except Exception as err:
                print('Error creating table, ' + table_name +
                      'check if it already exists')
                lg.error(
                    'Tried and failed to create the table for the symbols.  Program terminating...'
                )
                exit()

        batch = TableBatch()
        batchCount = 0
        rowCount = 0

        for row in rows:
            option = Entity()
            callPut = str(row[5]).strip()
            optionDate = row[7]
            expiration = row[6]
            strike = float(row[8])
            option.PartitionKey = str(row[0]).strip()
            # rowkey comprises the concatination of symbols to ensure the key is unique for the symbol.
            #option.RowKey = callPut + optionDate.strftime('%Y%m%d') + expiration.strftime('%Y%m%d') + str(strike)
            optionDateYYYY = optionDate[-4:]
            optionDateMM = optionDate[:2]
            optionDateDD = optionDate[3:5]
            optionDateRowKey = optionDateYYYY + optionDateMM + optionDateDD
            expDateYYYY = expiration[-4:]
            expDateMM = expiration[:2]
            expDateDD = expiration[3:5]
            expDateRowKey = expDateYYYY + expDateMM + expDateDD

            option.RowKey = callPut + optionDateRowKey + expDateRowKey + str(
                strike)
            option.OptionDate = ut.historicalLoadDates[
                optionDate]  #  ut.date_for_azure(optionDate)
            option.Expiration = ut.historicalLoadDates[
                expiration]  #  ut.date_for_azure(expiration)
            option.CallPut = callPut
            option.Strike = strike
            option.Bid = float(row[10])
            option.Ask = float(row[11])
            option.LastPrice = float(row[9])
            option.Volume = float(row[12])
            option.OpenInterest = int(row[13])
            option.StockPrice = float(row[1])
            batch.insert_or_replace_entity(option)

        table_service.commit_batch(table_name, batch)

    except Exception as e:
        print('Error importing option ' + symbol + '. Error is: ', e)
        lg.error('Error importing rows to the options table')
Exemplo n.º 2
0
def insert_options_azure(optionChain):
    # receives the optionChain object containing all options for all expiration dates
    # for the selected symbol.  inserts rows into the database options table for
    # each option.  Performs a db INSERT statement.  If the row already exists,
    # the database will generate an invalid key error to prevent the row from
    # being duplicated in the table.  In this case, the error is ignored.
    #
    account = ut.get_azure_account()
    table_service = None
    table_name = ut.TABLE_NAME_OPTIONS
    try:
        if config.IS_EMULATED:
            table_service = TableService(is_emulated=True)
        else:
            table_service = TableService(
                account_name=config.STORAGE_ACCOUNT_NAME,
                account_key=config.STORAGE_ACCOUNT_KEY)

        if not table_service.exists(table_name):
            # create the table
            try:
                table_service.create_table(table_name)
            except Exception as err:
                print('Error creating table, ' + table_name +
                      'check if it already exists')
                lg.error(
                    'Tried and failed to create the table for the symbols.  Program terminating...'
                )
                exit()

        batch = TableBatch()
        batchCount = 0
        rowCount = 0
        for o in optionChain.options:
            rowCount += 1
            if rowCount > 100:
                # Azure restricts the batch size to a max of a hundred entries.  Since we're at our
                # limit, we'll commit these and start a new batch
                table_service.commit_batch(table_name, batch)
                batch = TableBatch()
                rowCount = 1
                batchCount += 1

            option = Entity()
            option.PartitionKey = o.PartitionKey
            # rowkey comprises the concatination of symbols to ensure the key is unique for the symbol.
            # we'll use the callPut, optionDate, expirationDate, and strike price.  Dates will be in format yyyymmdd
            option.RowKey = o.RowKey
            option.OptionDate = o.optionDate  # dates are already cast as Entity Property with an aware date value
            option.Expiration = o.expiration
            option.CallPut = o.callPut
            option.Strike = o.strike
            option.Bid = o.bid
            option.Ask = o.ask
            option.LastPrice = o.lastPrice
            option.Volume = o.volume
            option.OpenInterest = o.openInterest
            option.IV = o.impliedVolatility
            option.StockPrice = o.stockPrice

            batch.insert_entity(option)

        table_service.commit_batch(table_name, batch)

    except Exception as e:
        print('Error adding option ' + symbol + '. Error is: ', e)
        lg.error('Error adding rows to the options table')
Exemplo n.º 3
0
def insert_options(optionChain):
    # receives the optionChain object containing all options for all expiration dates
    # for the selected symbol.  inserts rows into the database options table for
    # each option.  Performs a db INSERT statement.  If the row already exists,
    # the database will generate an invalid key error to prevent the row from
    # being duplicated in the table.  In this case, the error is ignored.
    #
    # changes to use an entity instead of a parameter list so we can cast the date objects for dates in Azure
    ###with pypyodbc.connect('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') as connection:
    ###    cursor = connection.cursor()
    ###    stockPrice = optionChain.stockPrice
    ###    for o in optionChain.options:
    ### original code:
    ###param_list = [o.symbol, o.optionDate, o.expiration, o.callPut, o.strike, o.bid, o.ask, o.lastPrice, o.volume, o.openInterest, o.impliedVolatility, stockPrice]
    ###try:
    ###    updateString = ('insert into Options (Symbol, OptionDate, Expiration, CallPut, Strike, Bid, Ask, LastPrice, Volume, OpenInterest, IV, StockPrice)'
    ###               ' values (?,?,?,?,?,?,?,?,?,?,?,?)')
    ###    cursor.execute(updateString, param_list)
    ###except Exception as err:
    ###    lg.error('Insert failed for symbol ' + o.symbol + ' exp: ' + o.expiration)
    ###connection.commit()
    ###
    ### replacement code to use entity objects and Azure table storage:
    account = ut.get_azure_account()  # CloudStorageAccount(is_emulated=True)
    table_service = None
    try:
        table_service = account.create_table_service()

        if not table_service.exists(ut.TABLE_NAME_OPTIONS):
            # create the table
            try:
                table_service.create_table(ut.TABLE_NAME_OPTIONS)
            except Exception as err:
                print('Error creating table, ' + ut.TABLE_NAME_OPTIONS +
                      'check if it already exists')
                lg.error(
                    'Tried and failed to create the table for the symbols.  Program terminating...'
                )
                exit()

        batch = TableBatch()
        batchCount = 0
        rowCount = 0
        print('Number entries to handle is ' + str(len(optionChain.options)))
        for o in optionChain.options:
            rowCount += 1
            if rowCount > 100:
                # Azure restricts the batch size to a max of a hundred entries.  Since we're at our
                # limit, we'll commit these and start a new batch
                table_service.commit_batch(ut.TABLE_NAME_OPTIONS, batch)
                batch = TableBatch()
                rowCount = 1
                batchCount += 1

            option = Entity()
            option.PartitionKey = o.symbol
            # rowkey comprises the concatination of symbols to ensure the key is unique for the symbol.
            # we'll use the callPut, optionDate, expirationDate, and strike price.  Dates will be in format yyyymmdd
            option.RowKey = o.callPut + o.optionDate.strftime(
                '%Y%m%d') + o.expiration.strftime('%Y%m%d') + str(o.strike)
            option.OptionDate = ut.date_for_azure(o.optionDate)
            option.Expiration = ut.date_for_azure(o.expiration)
            option.CallPut = o.callPut
            option.Strike = o.strike
            option.Bid = o.bid
            option.Ask = o.ask
            option.LastPrice = o.lastPrice
            option.Volume = o.volume
            option.OpenInterest = o.openInterest
            option.IV = o.impliedVolatility
            option.StockPrice = o.stockPrice

            batch.insert_entity(option)

        table_service.commit_batch(ut.TABLE_NAME_OPTIONS, batch)

    except Exception as e:
        print(
            'Error occurred in the sample. If you are using the emulator, please make sure the emulator is running.',
            e)
        lg.error('Error adding rows to the options table')