Follow the instructions below in order to generate and sort Bloomberg Data

Note: To work correctly, these functions need to run on a machine with the Bloomberg Excel Add-in. The code in each cell should only be run once

Hit Shift enter to run each cell



In [ ]:

    
#necessary imports
import os
import sys
import add_bloomberg_excel_functions as abxl
import generate_company_workbooks as gcw
import generate_sorted_options_workbooks as gsow
import update_excel_workbooks as uxlw
import iv_calculation as ivc
import create_atm_vol_series as cavs
from CONSTANTS import ACQUIRER_DIR, TARGET_DIR, ACQUIRER_ATM_VOL_DIR, TARGET_ATM_VOL_DIR

Step 1)

Initalize a Create_Company_Workbooks() object and run the create_company_workbooks() method from gcw



In [ ]:

    
####################  DONT RUN MORE THAN ONCE  ############################

#creates an instance of the Create_Company_Workbooks class
workbook_generator = gcw.Create_Company_Workbooks(source_sheet_name='Filtered Sample Set')
                                            
#calls the create_company_workbooks method on workbook_generator
workbook_generator.create_company_workbooks()

Step 2)

Create a variable to store the list of names for each newly created file



In [ ]:

    
target_path = TARGET_DIR
acquirer_path = ACQUIRER_DIR

#A list of each file in the target folder
target_file_list = os.listdir(target_path)

#A list of each file in the acquirer folder
acquirer_file_list = os.listdir(acquirer_path)

Step 3)

Open each workbook and save it after the Bloomberg data has populated. Then run the update_read_data_only() function from uxlw on each workbook to remove the function link to Bloomberg, while maintaining all the loaded data



In [ ]:

    
#loop through every target company and run the update_read_data_only() function
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    uxlw.update_read_data_only(file_path= company_full_path)



In [ ]:

    
#loop through every acquirer company and run the update_read_data_only() function
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    uxlw.update_read_data_only(file_path= company_full_path)

Step 4)

Run the update_sheet_with_BDP_description() function from uxlw



In [ ]:

    
#loop through every target company and run the update_sheet_with_BDP_description() function
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    uxlw.update_sheet_with_BDP_description(workbook_path=company_full_path, sheet_name='Options Chain',
                                           starting_col=1, starting_row=10)



In [ ]:

    
#loop through every acquirer company and run the update_sheet_with_BDP_description() function
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    uxlw.update_sheet_with_BDP_description(workbook_path=company_full_path, sheet_name='Options Chain',
                                           starting_col=1, starting_row=10)

Step 5)

Run the update_stock_price_sheet() function from uxlw to add a stock prick sheet to each workbook



In [ ]:

    
#loop through evert target and run the function to add the stock sheet to the workbook
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        uxlw.update_stock_price_sheet(workbook_path =company_full_path,
                                        sheet_name='Options Chain',
                                        stock_sheet_index = 1,
                                        sheet_start_date_cell='B7',
                                        sheet_announce_date_cell='B8', 
                                        sheet_end_date_cell='B9',  
                                        data_header_row=8, 
                                        data_table_index=['INDEX','DATE'], 
                                        data_table_header=['PX_LAST'],
                                        BDH_optional_arg=['Days', 'Fill'],
                                        BDH_optional_val=['W','0'])
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through every acquirer and run the function to add the stock sheet to the workbook
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        uxlw.update_stock_price_sheet(workbook_path =company_full_path,
                                        sheet_name='Options Chain',
                                        stock_sheet_index = 1,
                                        sheet_start_date_cell='B7',
                                        sheet_announce_date_cell='B8', 
                                        sheet_end_date_cell='B9',  
                                        data_header_row=8, 
                                        data_table_index=['INDEX','DATE'], 
                                        data_table_header=['PX_LAST'],
                                        BDH_optional_arg=['Days', 'Fill'],
                                        BDH_optional_val=['W','0'])
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 6)

Open each workbook and save it after the Bloomberg data has populated. Then run the update_read_data_only() function from uxlw on each workbook to remove the function link to Bloomberg, while maintaining all the loaded data



In [ ]:

    
#loop through every target company and run the update_read_data_only() function
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    uxlw.update_read_data_only(file_path= company_full_path)



In [ ]:

    
#loop through every acquirer company and run the update_read_data_only() function
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    uxlw.update_read_data_only(file_path= company_full_path)

Step 7)

Run the update_option_contract_sheets() function from uxlw to add option contract sheets

If any issues are found hit the square button to skip over the company. Consider Getting data for that company by hand



In [ ]:

    
#loop through evert target company and run the function to add the options sheets to the workbook
#potential other data_table_header's include: 'PX_BID','PX_ASK','PX_VOLUME','OPEN_INT'
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    
    try:
        uxlw.update_option_contract_sheets(workbook_path= company_full_path,
                                           sheet_name='Options Chain', 
                                           starting_col =1,
                                           starting_row=10,
                                           sheet_start_date_cell='B7',
                                           sheet_announce_date_cell='B8',
                                           sheet_end_date_cell='B9',
                                           data_header_row=8,
                                           data_table_index=['INDEX', 'DATE'],
                                           data_table_header=['PX_LAST'],
                                           BDH_optional_arg=['Days','Fill'],
                                           BDH_optional_val=['W', '0'])
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through evert acquirer company and run the function to add the options sheets to the workbook
#potential other data_table_header's include: 'PX_BID','PX_ASK','PX_VOLUME','OPEN_INT'
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    
    try:
        uxlw.update_option_contract_sheets(workbook_path= company_full_path,
                                           sheet_name='Options Chain', 
                                           starting_col =1,
                                           starting_row=10,
                                           sheet_start_date_cell='B7',
                                           sheet_announce_date_cell='B8',
                                           sheet_end_date_cell='B9',
                                           data_header_row=8,
                                           data_table_index=['INDEX', 'DATE'],
                                           data_table_header=['PX_LAST'],
                                           BDH_optional_arg=['Days','Fill'],
                                           BDH_optional_val=['W', '0'])
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 8)

Open each workbook and save it after the Bloomberg data has populated. Then run the update_read_data_only() function from uxlw on each workbook to remove the function link to Bloomberg, while maintaining all the loaded data



In [ ]:

    
#loop through every target company and run the update_read_data_only() function
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        uxlw.update_read_data_only(file_path= company_full_path)
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through every acquirer company and run the update_read_data_only() function
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        uxlw.update_read_data_only(file_path= company_full_path)
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 9)

Run the update_workbook_data_index() function from uxlw to populate the index column for each sheet in each workbook



In [ ]:

    
#loop through every target company and run the update_workbook_data_index() function
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        uxlw.update_workbook_data_index(workbook_path =company_full_path, data_start_row=9, index_column='A')
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through every acquirer company and run the update_workbook_data_index() function
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        uxlw.update_workbook_data_index(workbook_path= company_full_path, data_start_row= 9, index_column='A')
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 10)

Run the update_workbook_days_till_expiration() function from uxlw to add a days till expiration column



In [ ]:

    
#loop through ever target company and run the update_workbook_days_till_expiration() function
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        uxlw.update_workbook_days_till_expiration(reference_wb_path=company_full_path, 
                                                  data_start_row=9, 
                                                  date_col='B', 
                                                  calculation_col='D')
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through ever acquirer and company and run the update_workbook_days_till_expiration() function
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        uxlw.update_workbook_days_till_expiration(reference_wb_path=company_full_path, 
                                                  data_start_row=9, 
                                                  date_col='B', 
                                                  calculation_col='D')
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 11)

Run the calculate_workbook_iv() function from ivc in order to calculate the implied volatility for each option through expiration. Set six_month and twelve_month to True if you would like to calculate implied volatilities using six-month and 12-month treasury rates



In [ ]:

    
#iterate through every target and calculate the implied volatility for each contract on every day that it traded
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        ivc.calculate_workbook_iv(workbook_path=company_full_path, sheet_date_column=2, sheet_price_column=3, data_start_row=9, 
                                  three_month_data_col=5, six_month_data_col=6, twelve_month_data_col=7, 
                                  three_month=True, six_month=True, twelve_month=True)
    except Exception as e:
        print('ISSUE WITH{}: {}'.format(e,company_file))



In [ ]:

    
#iterate through every acquirer and calculate the implied volatility for each contract on every day that it traded
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        ivc.calculate_workbook_iv(workbook_path=company_full_path, sheet_date_column=2, sheet_price_column=3, data_start_row=9, 
                                  three_month_data_col=5, six_month_data_col=6, twelve_month_data_col=7, 
                                  three_month=True, six_month=True, twelve_month=True)
        
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 12)

Run the fill_option_wb_empty_cells() function from uxlw to fill empty cells with a stated fill_value



In [ ]:

    
#loop through every target company and fill empty cells in each sheet with the fill_value
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        uxlw.fill_option_wb_empty_cells(reference_wb_path=company_full_path,
                                    column_start=3, 
                                    row_start=9, 
                                    fill_value=0)
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through every acquirer company and fill empty cells in each sheet with the fill_value
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        uxlw.fill_option_wb_empty_cells(reference_wb_path=company_full_path,
                                    column_start=3, 
                                    row_start=9, 
                                    fill_value=0)
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 13)

Run the create_atm_vol_workbook() function from cavs to consolidate ATM implied volatility calculations for both calls and puts



In [ ]:

    
#loop through every acquirer and and create a new workbook that contains ATM implied volatility throughout the event window
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        cavs.create_atm_vol_workbook(company_full_path)
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through every target and and create a new workbook that contains ATM implied volatility throughout the event window
for (index, company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        cavs.create_atm_vol_workbook(company_full_path)
    except Exception as e:
        print('ISSUE WITH: {}'.format(company_file))

Step 14)

Create a list to store all the files in the newly created atm_vol folders in both the target and acquirer directory



In [ ]:

    
#list of all the atm_acquirer files
acquirer_atm_files = os.listdir(ACQUIRER_ATM_VOL_DIR)

#list of all the atm_target files
target_atm_files = os.listdir(TARGET_ATM_VOL_DIR)

Step 15)

Iterate over each file and run the create_average_vol_sheet() function to average together the put and call implied volatility for each company



In [ ]:

    
#loop through every acquirer ATM sheet and run the create_average_vol_sheet() function from cavs
for (index, company_file) in enumerate(target_atm_files):
    company_full_path='{}/{}'.format(TARGET_ATM_VOL_DIR, company_file)
    try:
        cavs.create_average_vol_sheet(company_full_path)
    except Exception as e:
        print('{} ISSUE WITH: {}'.format(e,company_file))



In [ ]:

    
#loop through every target ATM sheet and run the create_average_vol_sheet() function from cavs
for (index, company_file) in enumerate(acquirer_atm_files):
    company_full_path='{}/{}'.format(ACQUIRER_ATM_VOL_DIR, company_file)
    try:
        cavs.create_average_vol_sheet(company_full_path)
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 16)

Open each workbook and inspect the newly created sheet. In the event that any errors occured substitute those cell values for zero's. Then save each file. Finally run the add_mean_and_market_model() function from cavs to set up sheets for final analysis



In [ ]:

    
#loop through every acquirer ATM sheet and add a Mean Model and Market Model sheet
for (index, company_file) in enumerate(target_atm_files):
    company_full_path='{}/{}'.format(TARGET_ATM_VOL_DIR, company_file)
    try:
        cavs.add_mean_and_market_model(company_full_path)
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#loop through every target ATM sheet and add a Mean Model and Market Model sheet
for (index, company_file) in enumerate(acquirer_atm_files):
    company_full_path='{}/{}'.format(ACQUIRER_ATM_VOL_DIR, company_file)
    try:
        cavs.add_mean_and_market_model(company_full_path)
    except:
        print('ISSUE WITH: {}'.format(company_file))

Step 17)

Go through each sheet and determin which target and acquirers have enough data to perform event study analysis. All future work is done directly in excel.

OPTIONAL

Run the create_sorted_workbooks() function from gsow to generate options workbooks sorted by type(call or put), and either by strike, or by expiration date.

Sort By Strike



In [ ]:

    
#iterate through every target and create a new workbook with options sorted by type (call or put), and by strike
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        gsow.create_sorted_workbooks(reference_wb_path= company_full_path, header_start_row=8, 
                                data_column=['C','D','E','F','G'], index_column=['A'], 
                                sort_by_strike=True, sort_by_expiration=False)
        
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#iterate through every acquirer and create a new workbook with options sorted by type (call or put), and by strike
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        gsow.create_sorted_workbooks(reference_wb_path= company_full_path, header_start_row=8, 
                                data_column=['C','D','E','F','G'], index_column=['A'], 
                                sort_by_strike=True, sort_by_expiration=False)
        
    except Exception as e:
        print('ISSUE WITH: {}'.format(company_file))

Sort by Expiration Date



In [ ]:

    
#iterate through every target and create a new workbook with options sorted by type (call or put), 
#and by expiration date
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        gsow.create_sorted_workbooks(reference_wb_path= company_full_path, header_start_row=8, 
                                data_column=['C','D','E','F','G'], index_column=['A'], 
                                sort_by_strike=False, sort_by_expiration=True)
        
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#iterate through every acquirer and create a new workbook with options sorted by type (call or put), 
#and by expiration date
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        gsow.create_sorted_workbooks(reference_wb_path= company_full_path, header_start_row=8, 
                                data_column=['C','D','E','F','G'], index_column=['A'], 
                                sort_by_strike=False, sort_by_expiration=True)
        
    except:
        print('ISSUE WITH: {}'.format(company_file))

OPTIONAL

If anything goes wrong and sheets need to be deleted, call the delet_workbook_option_sheets() function to remove all the loaded option sheets

NOTE: This will delete all options workbooks from the target and acquirer folder respectively



In [ ]:

    
#iterate through every target and create a new workbook with options sorted by type (call or put), 
#and by expiration date
for (index, company_file) in enumerate(target_file_list):
    company_full_path='{}/{}'.format(target_path, company_file)
    try:
        uxlw.delet_workbook_option_sheets(workbook_path=company_full_path)
        
    except:
        print('ISSUE WITH: {}'.format(company_file))



In [ ]:

    
#iterate through every acquirer and create a new workbook with options sorted by type (call or put), 
#and by expiration date
for (index,company_file) in enumerate(acquirer_file_list):
    company_full_path='{}/{}'.format(acquirer_path, company_file)
    try:
        uxlw.delet_workbook_option_sheets(workbook_path=company_full_path)
        
    except Exception as e:
        print('ISSUE WITH: {}, \n{}'.format(company_file, e))