How to format date and time correctly with CSV file using python (Jupyter notebook & Anaconda)

Question

So I'm new to python and I'm trying to run this script with a CSV file I downloaded. I'm not sure what I need to fix or edit, but I've been stuck on this step for days now. I keep on getting the error "IndexError: list index out of range". I only put half the script and the CSV file in this post, but if needed I can attach the whole thing. The CSV file has almost 10,000 lines and I didn't want to make the post too cluttered (I posted the first 15 lines). If anyone could help it would be much appreciated!

The code:

import pandas as pd
import numpy as np
import scipy
from scipy.stats import norm
import matplotlib.pyplot as plt
from datetime import datetime, timedelta, date

pd.options.display.float_format = '{:,.4f}'.format

# Inputs and Parameters
filename = 'spx_quotedata.csv'

# Black-Scholes European-Options Gamma
def calcGammaEx(S, K, vol, T, r, q, optType, OI):
    if T == 0 or vol == 0:
        return 0

    dp = (np.log(S/K) + (r - q + 0.5*vol**2)*T) / (vol*np.sqrt(T))
    dm = dp - vol*np.sqrt(T) 

    if optType == 'call':
        gamma = np.exp(-q*T) * norm.pdf(dp) / (S * vol * np.sqrt(T))
        return OI * 100 * S * S * 0.01 * gamma 
    else: # Gamma is same for calls and puts. This is just to cross-check
        gamma = K * np.exp(-r*T) * norm.pdf(dm) / (S * S * vol * np.sqrt(T))
        return OI * 100 * S * S * 0.01 * gamma 

def isThirdFriday(d):
    return d.weekday() == 4 and 15 <= d.day <= 21

# This assumes the CBOE file format hasn't been edited, i.e. table beginds at line 4
optionsFile = open(filename)
optionsFileData = optionsFile.readlines()
optionsFile.close()

# Get SPX Spot
spotLine = optionsFileData[1]
spotPrice = float(spotLine.split(',')[1].split('Last:')[0])
fromStrike = 0.8 * spotPrice
toStrike = 1.2 * spotPrice

# Get Today's Date
dateLine = optionsFileData[2]
todayDate = dateLine.split(',')[1].split('Date: ')
monthDay = todayDate[0].split(' ')

# Handling of US/EU date formats
if len(monthDay) == 2:
    year = int(todayDate[1])
    month = monthDay[0]
    day = int(monthDay[1])
else:
    year = int(monthDay[2])
    month = monthDay[1]
    day = int(monthDay[0])

todayDate = datetime.strptime(month,'%B')
todayDate = todayDate.replace(day=day, year=year)

# Get SPX Options Data
df = pd.read_csv(filename, sep=",", header=None, skiprows=4)
df.columns = ['ExpirationDate','Calls','CallLastSale','CallNet','CallBid','CallAsk','CallVol',
              'CallIV','CallDelta','CallGamma','CallOpenInt','StrikePrice','Puts','PutLastSale',
              'PutNet','PutBid','PutAsk','PutVol','PutIV','PutDelta','PutGamma','PutOpenInt']

df['ExpirationDate'] = pd.to_datetime(df['ExpirationDate'], format='%a %b %d %Y')
df['ExpirationDate'] = df['ExpirationDate'] + timedelta(hours=16)
df['StrikePrice'] = df['StrikePrice'].astype(float)
df['CallIV'] = df['CallIV'].astype(float)
df['PutIV'] = df['PutIV'].astype(float)
df['CallGamma'] = df['CallGamma'].astype(float)
df['PutGamma'] = df['PutGamma'].astype(float)
df['CallOpenInt'] = df['CallOpenInt'].astype(float)
df['PutOpenInt'] = df['PutOpenInt'].astype(float)


# ---=== CALCULATE SPOT GAMMA ===---
# Gamma Exposure = Unit Gamma * Open Interest * Contract Size * Spot Price 
# To further convert into 'per 1% move' quantity, multiply by 1% of spotPrice
df['CallGEX'] = df['CallGamma'] * df['CallOpenInt'] * 100 * spotPrice * spotPrice * 0.01
df['PutGEX'] = df['PutGamma'] * df['PutOpenInt'] * 100 * spotPrice * spotPrice * 0.01 * -1

df['TotalGamma'] = (df.CallGEX + df.PutGEX) / 10**9
dfAgg = df.groupby(['StrikePrice']).sum()
strikes = dfAgg.index.values

# Chart 1: Absolute Gamma Exposure
plt.grid()
plt.bar(strikes, dfAgg['TotalGamma'].to_numpy(), width=6, linewidth=0.1, edgecolor='k', label="Gamma Exposure")
plt.xlim([fromStrike, toStrike])
chartTitle = "Total Gamma: $" + str("{:.2f}".format(df['TotalGamma'].sum())) + " Bn per 1% SPX Move"
plt.title(chartTitle, fontweight="bold", fontsize=20)
plt.xlabel('Strike', fontweight="bold")
plt.ylabel('Spot Gamma Exposure ($ billions/1% move)', fontweight="bold")
plt.axvline(x=spotPrice, color='r', lw=1, label="SPX Spot: " + str("{:,.0f}".format(spotPrice)))
plt.legend()
plt.show()

# Chart 2: Absolute Gamma Exposure by Calls and Puts
plt.grid()
plt.bar(strikes, dfAgg['CallGEX'].to_numpy() / 10**9, width=6, linewidth=0.1, edgecolor='k', label="Call Gamma")
plt.bar(strikes, dfAgg['PutGEX'].to_numpy() / 10**9, width=6, linewidth=0.1, edgecolor='k', label="Put Gamma")
plt.xlim([fromStrike, toStrike])
chartTitle = "Total Gamma: $" + str("{:.2f}".format(df['TotalGamma'].sum())) + " Bn per 1% SPX Move"
plt.title(chartTitle, fontweight="bold", fontsize=20)
plt.xlabel('Strike', fontweight="bold")
plt.ylabel('Spot Gamma Exposure ($ billions/1% move)', fontweight="bold")
plt.axvline(x=spotPrice, color='r', lw=1, label="SPX Spot:" + str("{:,.0f}".format(spotPrice)))
plt.legend()
plt.show()


# ---=== CALCULATE GAMMA PROFILE ===---
levels = np.linspace(fromStrike, toStrike, 60)

# For 0DTE options, I'm setting DTE = 1 day, otherwise they get excluded
df['daysTillExp'] = [1/262 if (np.busday_count(todayDate.date(), x.date())) == 0 \
                           else np.busday_count(todayDate.date(), x.date())/262 for x in df.ExpirationDate]

nextExpiry = df['ExpirationDate'].min()

df['IsThirdFriday'] = [isThirdFriday(x) for x in df.ExpirationDate]
thirdFridays = df.loc[df['IsThirdFriday'] == True]
nextMonthlyExp = thirdFridays['ExpirationDate'].min()

totalGamma = []
totalGammaExNext = []
totalGammaExFri = []

# For each spot level, calc gamma exposure at that point
for level in levels:
    df['callGammaEx'] = df.apply(lambda row : calcGammaEx(level, row['StrikePrice'], row['CallIV'], 
                                                          row['daysTillExp'], 0, 0, "call", row['CallOpenInt']), axis = 1)

    df['putGammaEx'] = df.apply(lambda row : calcGammaEx(level, row['StrikePrice'], row['PutIV'], 
                                                         row['daysTillExp'], 0, 0, "put", row['PutOpenInt']), axis = 1)    

    totalGamma.append(df['callGammaEx'].sum() - df['putGammaEx'].sum())

    exNxt = df.loc[df['ExpirationDate'] != nextExpiry]
    totalGammaExNext.append(exNxt['callGammaEx'].sum() - exNxt['putGammaEx'].sum())

    exFri = df.loc[df['ExpirationDate'] != nextMonthlyExp]
    totalGammaExFri.append(exFri['callGammaEx'].sum() - exFri['putGammaEx'].sum())

totalGamma = np.array(totalGamma) / 10**9
totalGammaExNext = np.array(totalGammaExNext) / 10**9
totalGammaExFri = np.array(totalGammaExFri) / 10**9

# Find Gamma Flip Point
zeroCrossIdx = np.where(np.diff(np.sign(totalGamma)))[0]

negGamma = totalGamma[zeroCrossIdx]
posGamma = totalGamma[zeroCrossIdx+1]
negStrike = levels[zeroCrossIdx]
posStrike = levels[zeroCrossIdx+1]

# Writing and sharing this code is only possible with your support! 
# If you find it useful, consider supporting us at perfiliev.com/support :)
zeroGamma = posStrike - ((posStrike - negStrike) * posGamma/(posGamma-negGamma))
zeroGamma = zeroGamma[0]

# Chart 3: Gamma Exposure Profile
fig, ax = plt.subplots()
plt.grid()
plt.plot(levels, totalGamma, label="All Expiries")
plt.plot(levels, totalGammaExNext, label="Ex-Next Expiry")
plt.plot(levels, totalGammaExFri, label="Ex-Next Monthly Expiry")
chartTitle = "Gamma Exposure Profile, SPX, " + todayDate.strftime('%d %b %Y')
plt.title(chartTitle, fontweight="bold", fontsize=20)
plt.xlabel('Index Price', fontweight="bold")
plt.ylabel('Gamma Exposure ($ billions/1% move)', fontweight="bold")
plt.axvline(x=spotPrice, color='r', lw=1, label="SPX Spot: " + str("{:,.0f}".format(spotPrice)))
plt.axvline(x=zeroGamma, color='g', lw=1, label="Gamma Flip: " + str("{:,.0f}".format(zeroGamma)))
plt.axhline(y=0, color='grey', lw=1)
plt.xlim([fromStrike, toStrike])
trans = ax.get_xaxis_transform()
plt.fill_between([fromStrike, zeroGamma], min(totalGamma), max(totalGamma), facecolor='red', alpha=0.1, transform=trans)
plt.fill_between([zeroGamma, toStrike], min(totalGamma), max(totalGamma), facecolor='green', alpha=0.1, transform=trans)
plt.legend()
plt.show()

The error I receive:

---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
/var/folders/1k/tfcmhfhx6pv06tc6c1x177gh0000gn/T/ipykernel_89066/3582543864.py in <module>
     51     day = int(monthDay[1])
     52 else:
---> 53     year = int(monthDay[2])
     54     month = monthDay[1]
     55     day = int(monthDay[0])

IndexError: list index out of range

In [8]:

todayDate = dateLine.split(',')[1].split('Date: ')
In [9]:

todayDate
Out[9]:
['Calls']

The CSV file:

Standard & Poors 500 Index,Last: 4363.4902,Change:  -23.0498,,,,,,,,,,,,,,,,,,,
"Date: March 4,2022, 7:50 PM EST",Bid: 4310.3501,Ask: 4429.4399,Size: 1*1,Volume: 0,,,,,,,,,,,,,,,,,
Expiration Date,Calls,Last Sale,Net,Bid,Ask,Volume,IV,Delta,Gamma,Open Interest,Strike,Puts,Last Sale,Net,Bid,Ask,Volume,IV,Delta,Gamma,Open Interest
Fri Mar 04 2022,SPXW220304C00800000,0,0,3559.5,3575.7,0,0.02,1,0,0,800,SPXW220304P00800000,0.05,0,0,0.05,0,8.7133,0,0,5
Fri Mar 04 2022,SPXW220304C00900000,0,0,3459.5,3475,0,0.02,1,0,0,900,SPXW220304P00900000,0.05,0,0,0.05,0,8.0839,0,0,2
Fri Mar 04 2022,SPXW220304C01000000,0,0,3359.5,3375,0,0.02,1,0,0,1000,SPXW220304P01000000,0.05,0,0,0.05,0,7.5902,0,0,12
Fri Mar 04 2022,SPXW220304C01200000,0,0,3159.5,3175.2,0,0.02,1,0,0,1200,SPXW220304P01200000,0.05,0,0,0.05,0,6.6726,0,0,2
Fri Mar 04 2022,SPXW220304C01400000,0,0,2959.5,2975.7,0,0.02,1,0,0,1400,SPXW220304P01400000,0.05,0,0,0.05,0,5.876,0,0,5
Fri Mar 04 2022,SPXW220304C01600000,0,0,2759.5,2775.5,0,0.02,1,0,0,1600,SPXW220304P01600000,0.05,0,0,0.05,0,5.1885,0,0,110
Fri Mar 04 2022,SPXW220304C01800000,0,0,2559.5,2575.5,0,0.02,1,0,0,1800,SPXW220304P01800000,0.05,0,0,0.05,0,4.6031,0,0,338
Fri Mar 04 2022,SPXW220304C02000000,0,0,2359.5,2375.5,0,0.02,1,0,0,2000,SPXW220304P02000000,0.05,0,0,0.05,0,4.0642,0,0,118
Fri Mar 04 2022,SPXW220304C02200000,0,0,2159.5,2175.5,0,0.02,1,0,0,2200,SPXW220304P02200000,0.05,0,0,0.05,0,3.6171,0,0,396
Fri Mar 04 2022,SPXW220304C02400000,0,0,1959.6,1975.5,0,0.02,1,0,0,2400,SPXW220304P02400000,0.05,0,0,0.05,0,3.1643,0,0,3054
Fri Mar 04 2022,SPXW220304C02600000,0,0,1759.6,1775.7,0,0.02,0.9999,0,0,2600,SPXW220304P02600000,0.05,0,0,0.05,0,2.765,0,0,4258
Fri Mar 04

Answer 1

I didn't really have to go through your code but it seems the issue is coming from a data point that is not up to 3 values ([0], [1], [2]). The code errors out at year = int(monthDay[2]) so there is a monthDay data that is not up to 3. You should try to put your code in a try/except block to confirm this or write another else statement to check if it is less than 2, note the index of the data point from there and maybe delete it or pass.