def getOptionVolume(aSymbol, aPrice, startDay):
"""
this routine will provide option volume for one Stock Symbol around one price.
Will retrun a list of strike prices
Parameters
----------
aSymbol : Stock
aPrice : the Max or Min move for a closing price
startDay : Earnings week
Returns
-------
Tuples: ([0] strikePlus, [1] strikeMinus, [2] aOccVolumeDFNextWeek, [3] aOccVolumeDFNextMontlyExpiry)
strikePlus/strikeMinus: Strike bounds Plus/Minus from aPrice - to id Min/Max parameters
aOccVolumeDFNextWeek: Option Volume for next week around strike price,
aOccVolumeDFNextMontlyExpiry: Option Volume for next Monthly Expiry around strike pric
"""
# todo - if next week equals next expiry push out next expiry
# get OCC Volume for aSymbol
aOccVolumeDF = getOccVolume(aSymbol)
# if aOccVolumeDF is empty send back 0 and empty DF
if aOccVolumeDF.empty:
return (0, 0, pd.DataFrame(), pd.DataFrame())
strikePlus, strikeMinus = getStrikes(aPrice, aOccVolumeDF, startDay)
# print('strikePlus: ', strikePlus)
# print(('strikeMinus: ', strikeMinus))
# get list of OCC strikes between <= strikePlus and >= StrikeMinus
strikes = [
strike for strike in aOccVolumeDF.Strike
if (strike >= strikeMinus and strike <= strikePlus)
]
# print('strikes: \n', strikes)
# strikes = list(set(strikes))
# print('unique strikes: ', strikes)
# get the Volume for Strikes
aOccVolumeDF = aOccVolumeDF[aOccVolumeDF.Strike.isin(strikes)]
# print('aOccVolumeDF:\n', aOccVolumeDF)
# Get the Volume for Next Friday Option Strikes
aOccVolumeDFNextWeek = aOccVolumeDF.loc[(
aOccVolumeDF.expiry == dateUtils.nextFridayOrgFormat(
dateUtils.getDateFromISO8601(startDay)))]
# Get the Volume for Next Friday Monthly Option Strikes
aOccVolumeDFNextMontlyExpiry = aOccVolumeDF.loc[
aOccVolumeDF.expiry == dateUtils.getNextThirdFridayFromDate(
dateUtils.getDateFromISO8601(startDay))]
# print('aOccVolumeDFNextWeek:\n', aOccVolumeDFNextWeek)
# print('aOccVolumeDFNextMontlyExpiry: \n', aOccVolumeDFNextMontlyExpiry)
return (strikePlus, strikeMinus, aOccVolumeDFNextWeek,
aOccVolumeDFNextMontlyExpiry)
def getEarningsForWeek(startday):
"""
This is the function that is called to start the Yahoo page scraping.
Parameters
----------
startday : date on which to start earnings week
Returns
-------
anEarningsDF: a DF of companies for the earnings week
"""
anEarningsDF = pd.DataFrame(
columns=['Symbol', 'Earnings_Date', 'Company', 'Earnings Call Time'])
# Week start date
aStartDay = dateUtils.getDateFromISO8601(startday)
#Start Monday go to Friday
for x in range(5):
aDay = aStartDay + datetime.timedelta(days=x)
aNewEarningsDF = getEarningsOnDate(
dateUtils.getDateStringDashSeprtors(aDay))
try:
anEarningsDF = anEarningsDF.append(aNewEarningsDF)
except TypeError:
print("No Earnings on: ", aDay)
continue
print('Working Day: ', aDay)
return anEarningsDF.reset_index(drop=True)
def checkStrikePrices(strikePlus, strikeMinus, aOccVolumeDF, startDay):
aOccVolumeDFpd = pd.DataFrame(aOccVolumeDF)
nextThrdFri = dateUtils.getNextThirdFridayFromDate(dateUtils.getDateFromISO8601(startDay))
nexFriday = dateUtils.nextFridayOrgFormat(dateUtils.getDateFromISO8601(startDay))
listOfExpiryNextThrdFriday = aOccVolumeDFpd.loc[aOccVolumeDFpd['expiry'] == nextThrdFri]
listOfExpiryNextFriday = aOccVolumeDFpd.loc[aOccVolumeDFpd['expiry'] == nexFriday]
#todo make sure the +2 amnd -2 are good adjustments
if listOfExpiryNextFriday.Strike.min() > strikeMinus:
strikeMinus=listOfExpiryNextFriday.Strike.min() +2
if listOfExpiryNextFriday.Strike.max() < strikePlus:
strikePlus=listOfExpiryNextFriday.Strike.max() -2
return strikePlus, strikeMinus
def getOptionVolumeNextFriExpiryCount(aSymbol, startDay, lenDF):
"""
Calculate the next Friday total Call / Put Open Interest
If there are no options for Friday goto Monthly else return O
Parameters
----------
aSymbol : Stock
startDay : Earnings week
Returns
-------
Volume of Call / Put open interest
"""
# get OCC Volume for aSymbol
aOccVolumeDF = getOccVolume(aSymbol)
# if aOccVolumeDF is empty send back 0 and empty DF
if aOccVolumeDF.empty:
print(lenDF, '- ' + aSymbol + ': No OCC Option Volume')
return (0, 0)
# else:
# print(aSymbol + ': OCC Option Volumes')
# Get the Volume for Next Friday Option Strikes
aOccVolumeDFNextWeek = aOccVolumeDF.loc[(
aOccVolumeDF.expiry == dateUtils.nextFridayOrgFormat(
dateUtils.getDateFromISO8601(startDay)))]
if aOccVolumeDFNextWeek.empty: #nothing for Next Friday?
# Get the Volume for Next Friday Monthly Option Strikes
aOccVolumeDFNextMontlyExpiry = aOccVolumeDF.loc[
aOccVolumeDF.expiry == dateUtils.getNextThirdFridayFromDate(
dateUtils.getDateFromISO8601(startDay))]
theCallsOpenInterest = aOccVolumeDFNextMontlyExpiry.Call.sum()
thePutsOpenInterest = aOccVolumeDFNextMontlyExpiry.Put.sum()
else: #use puts/calls for next Friday
theCallsOpenInterest = aOccVolumeDFNextWeek.Call.sum()
thePutsOpenInterest = aOccVolumeDFNextWeek.Put.sum()
return theCallsOpenInterest, thePutsOpenInterest
def getCloseStrikePrice(ib, qualityContracts, aStockSymbol, price, startDate, right, exchange = 'SMART' ):
"""
Get closes prices that is five points above, five points below
:Keyword arguments
ib -- ib_insync instance
qualityContracts -- a qualified Stock Symbol
aStockSymbol -- the stock
price -- price to find Option
:returns
Strike Prices
"""
# print(qualityContracts.symbol)
# [ticker] = ib.reqTickers(qualityContracts)
chains = ib.reqSecDefOptParams(qualityContracts.symbol, '', qualityContracts.secType, qualityContracts.conId)
chain = next(c for c in chains if c.tradingClass == aStockSymbol and c.exchange == exchange)
# if price >= then $40 Get to the next round at +/- 5
# if price < $40 get to round at the +/-2
if price >= 40 :
strikePlus = (5 * round(price / 5)) + 5
strikeMinus = (5 * round(price / 5)) - 5
# print('strikePlus', strikePlus)
# print('strikeMinus', strikeMinus)
else:
strikePlus = (2 * round(price / 2)) + 3
strikeMinus = (2 * round(price / 2)) - 3
# print('strikePlus', strikePlus)
# print('strikeMinus', strikeMinus)
# get strikes at strikePlus
strikes = [strike for strike in chain.strikes
if strike >= strikeMinus and strike <= strikePlus ]
# print('strikes: ', strikes)
# get experation date in proper format
dateFromISOFromat = dateUtils.getDateFromISO8601(startDate)
nextFridayDateFormat = dateUtils.nextFriday(dateFromISOFromat)
expiration = dateUtils.nextFridayOrgFormat(nextFridayDateFormat) # = sorted(exp for exp in chain.expirations)
print('expiration: ', expiration)
contracts = [Option(qualityContracts.symbol, expiration, strike, right, exchange)
for strike in strikes]
ib.qualifyContracts(*contracts)
# print('contracts: ', contracts, '\n\n\n')
# Todo: should this be ib.reqSecDefOptParams instead of ib.reqContractDetails???
optionContractsDetails = [ib.reqContractDetails(cd)
for cd in contracts]
return strikes, contracts
def saveSummaryToExcel(yahooEarningsDF, startday ):
"""
Parameters
----------
yahooEarningsDF: the Yahoo earnings DF
startday : Earnings week startday
Returns
-------
"""
# Setup Excel output file
companyEarningsWeek = theBaseCompaniesDirectory + startday + '/'
outExcelFile = companyEarningsWeek + 'SummaryWeekOf-' + startday + excelSuffix
# Create a Pandas Excel writer using XlsxWriter as the engine
# assuming Path is setup
writer = pd.ExcelWriter(outExcelFile, engine='xlsxwriter')
summaryWorkbook = writer.book
fmt = summaryWorkbook.add_format()
# Summary Sheet Name
yahooEarningsDF.to_excel(writer, sheet_name='Summary Earnings')
for i in range(0, len(yahooEarningsDF)):
# get the symbol and transpose data to fit Horizontally, creat sheet
aSymbol = yahooEarningsDF.loc[i,].Symbol
theHeader = yahooEarningsDF.loc[i,].to_frame()
theHeaderTransposed = theHeader.T
theHeaderTransposed.to_excel(writer, sheet_name= aSymbol)
inCsvFile_aSymbol = companyEarningsWeek + aSymbol + csvSuffix
yahooEarningsDf_aSymbol = pd.read_csv(inCsvFile_aSymbol, index_col=0)
startRow = 3
yahooEarningsDF.at[i, "Earnings_Date"] = dateUtils.getDateFromISO8601(yahooEarningsDF.loc[i,].Earnings_Date)
yahooEarningsDf_aSymbol.to_excel(writer, sheet_name= aSymbol, startrow=startRow)
# Convert the dataframe to an XlsxWriter Excel object.
# yahooEarningDf.to_excel(writer, sheet_name='Week of ' + startday)
# format Earnings_Date Column C Row to len(yahooEarningsDF)+1
worksheet = writer.sheets['Summary Earnings']
cellRowFormat = summaryWorkbook.add_format({'bold': True, 'bg_color': 'green'})
cellColFormat = summaryWorkbook.add_format()
cellColFormat.set_shrink()
worksheet.set_row(0, 15, cellRowFormat)
worksheet.set_column(0, 20, 15 ,cellColFormat)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
return
def saveSummaryToExcel(yahooEarningsDF, startday):
"""
Parameters
----------
yahooEarningsDF: the Yahoo earnings DF
startday : Earnings week startday
Returns
-------
"""
# Setup Excel output file
companyEarningsWeek = theBaseCompaniesDirectory + startday + '/rawData/'
outExcelFile = theBaseCompaniesDirectory + startday + '/' + 'SummaryWeekOf-' + startday + excelSuffix
# Create a Pandas Excel writer using XlsxWriter as the engine
# assuming Path is setup to outExcelFile
writer = pd.ExcelWriter(outExcelFile, engine='xlsxwriter')
summaryWorkbook = writer.book
fmt = summaryWorkbook.add_format()
yahooEarningsDF.sort_values(by=['Symbol'], inplace=True)
yahooEarningsDF = yahooEarningsDF.reset_index(drop=True)
# create first tab as Summary Earnings
# turn off the header so it can be formatted
yahooEarningsDF.to_excel(writer, sheet_name='Summary Earnings')
worksheet = writer.sheets['Summary Earnings']
# number of Stocks / i.e. Rows in Summary Earnings Tab
len_yahooEarningsDF = len(yahooEarningsDF)
# write out each Stock Symbol info from the stock CSV file into an excel tab
for i in range(0, len_yahooEarningsDF):
# from yahooEarningsDF get the symbol and then transpose data to fit Horizontally, creat sheet
# this is the Stock Tab top 3 rows that include Current Earnings info as well as other info like
# options volume, expected price changes, and delta price IV - etc
aSymbol = yahooEarningsDF.loc[i, ].Symbol
theHeader = yahooEarningsDF.loc[i, ].to_frame()
theHeaderTransposed = theHeader.T
theHeaderTransposed.to_excel(writer, sheet_name=aSymbol)
inCsvFile_aSymbol = companyEarningsWeek + aSymbol + csvSuffix
yahooEarningsDf_aSymbol = pd.read_csv(inCsvFile_aSymbol, index_col=0)
# start adding to data to tab after the Current Earnings info - see above
startRow = 3
# get date
yahooEarningsDF.at[i, "Earnings_Date"] = dateUtils.getDateFromISO8601(
yahooEarningsDF.loc[i, ].Earnings_Date)
# add the data
yahooEarningsDf_aSymbol.to_excel(writer,
sheet_name=aSymbol,
startrow=startRow)
aSymboWorksheet = writer.sheets[aSymbol]
aSymboWorksheet.set_column('A:V', 15)
aSymboWorksheet.set_column('N:U', 40)
# cellRowFormat = summaryWorkbook.add_format({'bold': True, 'bg_color': 'red'}) #hex ccffcc / R:204 G: 255 B: 204
# cellLtGreenFormat = summaryWorkbook.add_format({'bold': True, 'bg_color': '#CCFFCC'})
# cellColFormat.set_shrink()
#
percentFormat = summaryWorkbook.add_format({'num_format': '0.0%'})
currencyFormat = summaryWorkbook.add_format({'num_format': '$#,##0.00'})
worksheet.set_column('A:A', 5, percentFormat)
worksheet.set_column('B:B', 10, percentFormat)
worksheet.set_column('C:C', 35, percentFormat)
worksheet.set_column('D:D', 15, percentFormat)
worksheet.set_column('E:E', 10, percentFormat)
worksheet.set_column('F:F', 15)
worksheet.set_column('G:I', 15, percentFormat)
worksheet.set_column('J:L', 18)
worksheet.set_column('M:M', 15, currencyFormat)
worksheet.set_column('N:O', 15, percentFormat)
worksheet.set_column('P:P', 15, currencyFormat)
worksheet.set_column('Q:T', 23, currencyFormat)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
return