Many Times It Takes Multiple Tools to Get the Job Done
Just like a mechanic, a Quant needs tools to accomplish many programming tasks. In this post, I use a toolbox to construct an EasyLanguage function that will test a date and determine if it is considered a Holiday in the eyes of the NYSE.
Why a Holiday Function?
TradeStation will pump holiday data into a chart and then later go back and take it out of the database. Many times the data will only be removed from the daily database, but still persist in the intraday database. Many mechanical day traders don’t want to trade on a shortened holiday session or use the data for indicator/signal calculations. Here is an example of a gold chart reflecting President’s Day data in the intra-day data and not in the daily.
This affects many stock index day traders. Especially if automation is turned on. At the end of this post I provide a link to my youTube channel for a complete tutorial on the use of these tools to accomplish this task. It goes along with this post.
First Get The Data
I searched the web for a list of historical holiday dates and came across this:
You might be able to find this in a easier to use format, but this was perfect for this post.
Extract Data with Beautiful Soup
Here is where Python and the plethora of its libraries come in handy. I used pip to install the requests and the bs4 libraries. If this sounds like Latin to you drop me an email and I will shoot you some instructions on how to install these libraries. If you have Python, then you have the download/install tool known as pip.
Here is the Python code. Don’t worry it is quite short.
As you can see this is very simple code. First I set the variable url to the website where the holidays are located. I Googled on how to do this – another cool thing about Python – tons of users. I pulled the data from the website and stuffed it into the page object. The page object has several attributes (properties) and one of them is a text representation of the entire page. I pass this text to the BeautifulSoup library and inform it to parse it with the html.parser. In other words, prepare to extract certain values based on html tags. All_tables contains all of the tables that were parsed from the text file using Soup. Don’t worry how this works, as its not important, just use it as a tool. In my younger days as a programmer I would have delved into how this works, but it wouldn’t be worth the time because I just need the data to carry out my objective; this is one of the reasons classically trained programmers never pick up the object concept. Now that I have all the tables in a list I can loop through each row in each table. It looked liker there were 9 rows and 2 columns in the different sections of the website, but I didn’t know for sure so I just let the library figure this out for me. So I played around with the code and found out that the first two columns of the table contained the name of the holiday and the date of the holiday. So, I simply stuffed the text values of these columns in two lists: a and b. Finally I print out the contents of the two lists, separated by a hyphen, into the Interpreter window. At this point I could simply carry on with Python and create the EasyLanguage statements and fill in the data I need. But I wanted to play around with Excel in case readers didn’t want to go the Python route. I could have used a powerful editor such as NotePad++ to extract the data from the website in place of Python. GREP could have done this. GREP is an editor tool to find and replace expressions in a text file.
Use Excel to Create Actual EasyLanguage – Really!
I created a new spreadsheet. I used Excel, but you could use any spreadsheet software. I first created a prototype of the code I would need to encapsulate the data into array structures. Here is what I want the code to look like:
This is just the first few lines of the function prototype. But you can notice a repetitive pattern. The array names stay the same – the only values that change are the array elements and the array indices. Computers love repetitiveness. I can use this information a build a spreadsheet – take a look.
I haven’t copied the data that I got out of Python just yet. That will be step 2. Column A has the first array name holidayName (notice I put the left square [ bracket in the column as well). Column B will contain the array index and this is a formula. Column C contains ]=”. Column D will contain the actual holiday name and Column E contains the ; These columns will build the holidayName array. Columns G throuh K will build the holidayDates array. Notice column H equals column B. So whatever we do to column B (Index) will be reflected in Column H (Index). So we have basically put all the parts of the EasyLanguage into Columns A thru K.
Excel provides tools for manipulating strings and text. I will use the Concat function to build my EasyLanguage. But before I can use Concat all the stuff I want to string together must be in a string or text format. The only column in the first five that is not a string is Column B. So the first thing I have to do is convert it to text. First copy the column and paste special as values. Then go to your Data Tab and select Text To Columns.
It will ask if fixed width or delimited – I don’t think it matters which you pick. On step 3 select text.
The Text To Columns button will solve 90% of your formatting issues in Excel. Once you do this you will notice the numbers will be left justified – this signifies a text format. Now lets select another sheet in the workbook and past the holiday data.
Copy Holiday Data Into Another Spreadsheet
Text To Columns to the rescue. Here I will separate the data with the “-” as delimiter and tell Excel to import the second column in Date format as MDY.
Now once the data is split accordingly into two columns with the correct format – we need to convert the date column into a string.
Now the last couple of steps are really easy. Once you have converted the date to a string, copy Column A and past into Column D from the first spreadsheet. Since this is text, you can simply copy and then paste. Now go back to Sheet 2 and copy Column C and paste special [values] in Column J on Sheet 1. All we need to do now is concatenate the strings in Columns A thru E for the EasyLanguage for the holidayName array. Columns G thru K will be concatenated for the holidayDate array. Take a look.
Now create a function in the EasyLanguage editor and name it IsHoliday and have it return a boolean value. Then all you need to do is copy/paste Columns F and L and the data from the website will now be available for you use. Here is a portion of the function code. Notice I declare the holidayNameStr as a stringRef? I did this so I could change the variable in the function and pass it back to the calling routine.
This was a pretty long tutorial and might be difficult to follow along. If you want to watch my video, then go to this link.
I created this post to demonstrate the need to have several tools at your disposal if you really want to become a Quant programmer. How you use those tools is up to you. Also you will be able to take bits and pieces out of this post and use in other ways to get the data you really need. I could have skipped the entire Excel portion of the post and just did everything in Python. But I know a lot of Quants that just love spreadsheets. You have to continually hone your craft in this business. And you can’t let one software application limit your creativity. If you have a problem always be on the lookout for alternative platforms and/or languages to help you solve it.