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')
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')
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')