Aaron Seiter - Final Project

Final Project – Equity Option Datasheet Generator

2.1 Executive Summary

Summary

For my final project I created an equity option datasheet generator that gathers data from Bloomberg and spins off that data into individual excel workbooks then saves these files in a specified location.

Business need for the program

This program was built in order to solve a problem faced by BYU Professors Boyer and Carpenter. They are co-authoring a research paper on trade behavior of call and put equity options. In order to write the research paper they needed trade data on 8,530 US equities pulled in from a Bloomberg terminal. It would take an insurmountable amount of time to pull and manipulate this data by hand. It became necessary to write a VBA program to expedite the process.

Overview of program

The program performs five main tasks.

1. Retrieve one of the 8,530 tickers from the correct ticker tab

2. Create a formula using the retrieved ticker necessary to get the “option chain” (aka list of all options listed on Bloomberg for that ticker)

3. Filter the “option chain” to only include options that meet specified date criteria

4. Create formulas to pull the trade data from the filtered “option chain”

5. After the data is successful pulled into the three sheets dedicated to the retrieved ticker, the program will spin those three sheets into a separate workbook, rename the workbook, and save it in a specified location

Difficulties encountered

The most notable hurdles encountered while programming were:

1. Bloomberg’s formulas are unique to Bloomberg and difficult to recreate in VBA

2. The number of options in a ticker’s “option chain” is not constant between tickers. One might have 75 while another may have 475.

3. Bloomberg takes a few seconds to populate the data after you make a data call.

Excel File:

http://files.gove.net/shares/files/11w/aseiter/AaronSeiter_OptionsV11.xlsm

PDF Write Up:

http://files.gove.net/shares/files/11w/aseiter/Final_Project_Write_Up_Aaron_Seiter.pdf

2.2 Implementation Documentation

Creation of the VBA program

Step 1: Drawing Board

When I first started this program I got a blank sheet of paper and organized my thoughts. This program is the most complex program I have made to date. Before I even opened up a session of excel I outlined exactly what I wanted the program to do. Often times I didn’t have a clue of how to get Excel to do what I wanted, but I at least knew what I wanted out of the program. This saved me a lot of time since I didn’t have to build and delete unnecessary blocks of code.

Step 2: Insert Outline into VBA

I inserted the following outline I created from step 1 into two modules in VBA as comments

'This creates copies of the master sheets

'This part selects the right ticker from the sheet specified bellow

'This pastes it onto the recently copied sheets and creates the option chain formula

'This filters the option chain and creates subsequent option trade data formulas

'This part separates the sheets into its own workbook

'This part renames the sheets

'This part saves the newly minted sheet

Step 3: Fill the outline in with code that will complete the necessary tasks in the outline

Each item in the outline is essential to complete the task. The order is also very important since many steps rely on preceding steps. The following is a more detailed description of how I went about filling in the code into the outline.

1. 'This creates copies of the master sheets

a. Each of the three master sheets has pre set parameters (see screenshot below)

b. Each of the three master sheets has different parameters therefore it is necessary to copy and use all three for the spin offs

2. 'This part selects the right ticker from the sheet specified bellow

a. I broke up the 8,530 tickers into separate worksheets to make the list more manageable. I alphabetized the ticker list and then separated the tickers according to their first letter (see screenshot below)

b. The VBA activates one of the ticker sheets (the “A” sheet for example)

c. The code will then go through a “do until” loop and select the tickers one by one until there are no more tickers in the sheet

3. 'This pastes it onto the recently copied sheets and creates the option chain formula

a. The selected ticker will then be pasted into the three recently copied master worksheets

b. This newly pasted ticker will be used to create the option chain

c. It was a little tricky creating this formula since there are quotes, so it is necessary to have triple quotes where usually there is just a single quote. (see below for example)

*Note: Range “A2” is the selected ticker

*Note: “=BDS” is a formula in excel that performs a data call to Bloomberg when connected to a terminal

d. It takes Bloomberg a few seconds to bring in the requested data. To overcome this obstacle I put in a few wait times so that the sheet would have a few seconds to load the data.

4. 'This filters the option chain and creates subsequent option trade data formulas

a. When Bloomberg gives the option chain it gives all the options not just the ones needed. It is necessary to filter the options chain based on the parameters listed in the sheet. This is done with “Instr”

b. If the option in the option chain contains the correct date then a formula is created that requests the needed data from Bloomberg

5. 'This part separates the sheets into its own workbook

a. This was fairly straight forward. I simply recorded myself doing this with a macro

6. 'This part renames the sheets

a. This was fairly straightforward however it was necessary to splice in the ticker name into sheet name.

7. 'This part saves the newly minted sheet

a. Once again I needed to use the ticker in the saved name.

2.3 Difficulties Encountered

I ran into several roadblocks while filling code into the outline. Here are a few examples:

Problem #1 Bloomberg’s formulas are unique to Bloomberg and difficult to create in VBA

Bloomberg formulas a separate beast from what I was used to dealing with. It became necessary to not only learn the Bloomberg formulas, but to be able to recreate them into the code. I was initially thrown off since the Bloomberg formulas use a lot of quotation makes. In VBA this has to be addressed for the formula to be properly formatted. I overcame this obstacle by visiting with Pro. Allen who helped me get the right number of quotes in order for the VBA reader to properly format the formula

Problem #2 The number of options in a ticker’s “option chain” is not constant between tickers. One might have 75 while another may have 475.

This was a difficult task for me to work around. It would be really simple to work with different tickers if each ticker had the same number of available options. Since that was not the case it became necessary to make a robust code that could handle a variable number of options. Once again Prof. Allen helped me craft the following code to work around this problem.

Problem #3 Bloomberg takes a few seconds to populate the data after you make a data call.

When my subroutine was finished I ran it a few times and it worked beautifully except that none of the Bloomberg values populated! I realized that this was because I didn’t give Bloomberg enough time to populate the data. It takes a few seconds to fulfill a data request. My work around for this problem was to simply put in some wait periods.

What did I learn?

I learned several things while putting this VBA program together.

· I learned the nuances of Bloomberg data pulls

o How to put together a Bloomberg data pull formula

o How to put a pause into my code to ensure the data has time to populate

· I got some experience using loops. We learned these in class, but it was nice to be able to incorporate things that I learned in class into my program

· I learned how to create a program from start to finish

o I realize know just how important it is to spend the time to create a good outline then work on filling the outline with the code

· I learned how to spin off excel sheets.

o I initially thought this was going to be very very difficult, however it was as simple as me recording a macro of me doing it!

· Most importantly I gained a lot of confidence in taking this program from concept to workable product!

Things I wanted to include

· One thing I really wanted to include that I leaned about in class but ran out of time to include was a slick user interface. I really wanted to make a user-friendly user form like we did in the User form project. I created the userform but had a hard time getting the drop down lists to populate and work with the already created code

· I also am not super happy with how I handled the problem with Bloomberg taking so long to load the data. My solution works but it is not very robust. I put in an “Application.Wait (Now()+Timevalue(“00:00:03”) every time Bloomberg was populating some data. As I continue to tweek the sheet I will replace that with a loop that will check to see if the data is loaded. Once Bloomberg sends the data then the code will exit the loop and move on. I discussed this problem with Prof. Allen and he suggested I use the “doexecute” loop. I had a hard time getting it to work…