Automatically calculate public holidays from 1900-2203

Hello Optuma scripting community.

Mathew, Matthew, Darren, the Optuma support team and the Optuma scripting community have always been very helpful to me, so I thought I might give something back.

This post is about a script that automatically calculates public holidays and therefore when markets are closed.

And you are welcomed to use it.

I found that in backtesting of my trading strategies it became important to know when public holidays were or will be, but it was quite a distraction to be constantly searching the internet to get answers to questions like ‘when was Good Friday in 2005?’, or ‘why is there no bar in an Optuma daily chart for Friday 22 April, 2011?’

So I developed a script that automatically calculates public holidays. The MS Excel community has been doing this for years, and I was able to ‘borrow’ the Excel formulas for calculating public holidays and apply them in an Optuma script. And, with Optuma 1.4, new essential script functions like MOD, and the new ‘Numbers’ feature of Show Bar made it possible.

The attached screen shot (US Public Holidays on SPX) shows the outcome of the script, applied to a daily SPX chart. Each number on the chart corresponds to one of the nine annual public holidays in the US. (You will also see a ‘10’ as well: that was an irregular holiday for the state funeral of George Bush Snr… the script handles those sorts of scenarios too).

While I have not tested the whole range, the Excel community reckons that Excel calculates all public holidays accurately from 1900- 2203, even the tricky ones like Good Friday. I use this script on my live strategies, and have found it to be accurate from 1980 - present so far.

Now the trick is that there is no data on a public holiday when markets are closed, so Optuma won’t show a daily bar for a public holiday. Fair enough. So to indicate a public holiday on a daily chart, you need to do something like indicating it the day or week before, which sort of makes sense because you really want to know in advance when the markets are closed. So this script gives you the option of indicating 1 day or 7 days before the public holiday.

The script is intended to be ‘plug-n-play’ so you should be able to just copy the code of this post and try it out for yourself. If you do use it and have some problems I will try to support you as best I can through this forum, but there is always the possibility of issues that I can’t resolve because it is highly likely our environment setups are different. What I also can’t do is warrant the script, so please be aware that you are using it at your own risk. You should understand it, test it, and be satisfied that it is accurate before applying it to your live trading strategies.

This script is possibly not for the faint-hearted. It’s almost 200 lines long but does have substantial explanatory commentary within it. You can copy and paste and follow the instructions of the Show Bar to get an output like the screen shot, but if that doesn’t work first up, you will need to roll up your sleeves and start debugging.

You might notice some strange looking things like ‘@alias’, ‘@constant’, ‘{integer}’, ‘{1|o}’ etc. in the comments. Don’t worry about them, they don’t affect the script. (They’re there because I am developing an auto-documentation generator for Optuma scripts, which is in an alpha-testing phase at the moment).

As a final note, the script has been developed for US public holidays only, but if there is sufficient interest from the Optuma scripting community, I will build an Australia public holiday equivalent. Just reply to this post if you are interested in an Aussie version.

Now, here it comes. It’s also attached in a file also for your convenience. Good luck, and I hope that some of you find it useful!

Cheers, Dean

 

 

 

 

 

US-Public-Holidays-Optuma-Script.txt (12.2 KB)

// Determines if the next trading day or if the trading day in one week's  time is a US public holiday, therefore all US exchanges are closed for trading.
// The script is based on calculations built and tested in MS Excel. The calculations are found to be accurate from 1900-2203. The script uses the Show Bar numbers feature released in Optuma 1.4 to assign a number to each public holiday. Then, a number corresponding to the public holiday appears on a daily chart just prior (either 1 day or 1 week) to the actual holiday.
// @alias M7R.Utility.USMarketHolidays
// @constant {integer} Alert notice period. Set to Abs(1) if to give an alert one day prior, meaning that the markets are closed on the next trading day. Set to Abs(7) to give an alert one week prior, meaning that the markets are closed on the trading day in 7 days' time. Why use Abs()? Optuma does not like variables being directly assigned to 1. The Abs() function is used as a workaround.
alertNoticePeriod = Abs(1);
// @constant {integer} A universal offset is applied to all calculations. This is applied to all dates after 01/01/1900 to align D/M/Y datetimes with equivalent integer values like Optuma's BARDATE() and Excel's datetime functions.
offsetUniversal = 2;

// CALCULATION FOR IRREGULAR NATIONAL US HOLIDAYS

// {1|0} Extraordinary market close dates. Format is [BARDATE() == xxxxx - yy] where [xxxxx] is extraordinary holiday date,and [yy] is a constant to set the equation to evaluate to the bar BEFORE the date. Note that [yy] must be at least one but could be as many as 4 depending on weekends and public holdiays, and has to be determined indpendently for each extraordinary holiday.
isExtraordinaryCloses =
// BARDATE() == xxxxx - yy 
   BARDATE() == 38149 - 01 //11 Jun 2004, Ronald Regan State Funeral
or BARDATE() == 43439 - 01 //05 Dec 2018, George Bush Snr State Funeral
;

// {1|0} 1 (true) if the year of the current bar is a leapyear.
offsetLeapYear =  IF(MOD(VALUE=4,YEARNUM())==0, 1, 0);

// {integer} Find the BARDATE that is the first day of this year. If a Leap Year, Subtract 1 day because that day only gets added after February 28. Also there is a universal offset of 2 applied after 01/01/1900 to align D/M/Y datetimes with equivalent integer values like BARDATE().
offsetThisYear =
offsetUniversal 
+ (offsetLeapYear*-1)
+ (YEARNUM()-1900)*365 
+ (YEARNUM()-MOD(VALUE=4,YEARNUM())-1900)/4;

// CALCULATION FOR MARTIN LUTHER KING JR DAY HOLIDAY

// {integer} For Martin Luther King Jr Day holiday, simulare Excel's Date(YEARNUM,1,1) function using a fixed offset of 0.
offsetMartinLutherKingJrDay = offsetThisYear + 0;

// {integer} Calculate the date of Martin Luther King Jr. Day holiday for the current year. Rule: The 3rd Monday in January. Excel equivalent formula: =DATE(YEARNUM,1,1)+14+CHOOSE(WEEKDAY(DATE(YEARNUM,1,1)),1,0,6,5,4,3,2). 
bardateMartinLutherKingJrDay = 
offsetMartinLutherKingJrDay + 14
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 1, 1, 0)
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 2, 0, 0)
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 3, 6, 0)
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 4, 5, 0)
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 5, 4, 0)
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 6, 3, 0)
+ IF(MOD(VALUE=7, offsetMartinLutherKingJrDay) == 0, 2, 0);

// CALCULATION FOR PRESIDENTS' DAY HOLIDAY

// {integer} For Presidents' Day, simulate Excel's Date(YEARNUM, 1, 1) function using a fixed offset of 31.
offsetPresidentsDay = offsetThisYear + 31;

// {integer} Calculate the date of Presidents' Day holiday for the current year. Rule: The 3rd Monday in February. Excel equivalent formula:  =DATE(YEARNUM,2,1)+14+CHOOSE(WEEKDAY(DATE(YEARNUM,2,1)),1,0,6,5,4,3,2).
bardatePresidentsDay = 
offsetPresidentsDay + 14
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 1, 1, 0)
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 2, 0, 0)
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 3, 6, 0)
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 4, 5, 0)
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 5, 4, 0)
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 6, 3, 0)
+ IF(MOD(VALUE=7, offsetPresidentsDay) == 0, 2, 0);

// CALCULATION FOR GOOD FRIDAY HOLIDAY

// {integer} For Easter Sunday, use a fixed offset of 90 to simulate Excel's Date(YEARNUM,4,1) function. Leapyear included because date is after February 28. 
offsetEasterSunday = offsetThisYear + 90 + offsetLeapYear;

// {integer} Calculate the date of Easter Sunday for the current year. Rule: as described by Excel formula. Excel equivalent formula:  =ROUND(DATE(YEARNUM,4,1)/7+MOD(19*MOD(YEARNUM,19)-7,30)*14%,0)*7-6-2. The main formula evaluates to Easter Sunday, so 2 is subracted at end of formula to go back to Good Friday.
EasterSunday = 
(offsetEasterSunday) / 7
+ MOD(VALUE=30,19*MOD(VALUE=19,YEARNUM())-7) * 0.14;

// {integer} Calculate the date of Good Friday holiday for the current year Excel equivalent formula.  = ROUND(EasterSunday, DECIMALTYPE=0) * 7 - 6 - 2. Unfortunately ROUND() does not fully work in Optuma, so use an alternative method to round Easter Sunday.
bardateGoodFriday = 
(FLOOR(EasterSunday) + IF(MOD(VALUE=10, EasterSunday*10)/10 > 0.5, 1, 0)) * 7
- 6 - 2;

// CALCULATION FOR MEMORIAL DAY HOLIDAY

// {integer} For Memorial Day, use fixed offset of 151 to simulate Excel's Date(Year,6,1) function. Leap year included because date is after February 28. 
offsetMemorialDay = offsetThisYear + 151 + offsetLeapYear;

// {integer} For Memorial Day, use a second fixed offset of 156 to simulate Excel's Date(Year,6,6) function. Leap year included because date is after February 28. 
offset2MemorialDay = offsetThisYear + 156 + offsetLeapYear;

// {integer} Calculate the date of Memorial Day holiday for the current year. Rule: The last Monday in May
//Excel equivalent formula: =DATE(YEARNUM,6,1)-WEEKDAY(DATE(YEARNUM,6,6)). 
bardateMemorialDay = 
offsetMemorialDay
- IF(MOD(VALUE=7, offset2MemorialDay) == 0, 7, MOD(VALUE=7, offset2MemorialDay));

// CALCULATION FOR INDEPENDENCE DAY HOLIDAY

// {number} For Independence Day, use fixed offset of 184 to simulate Excel's Date(YEARNUM,7,4) function. Leapyear included because date is after February 28.
offsetIndependenceDay = offsetThisYear + 184 + offsetLeapYear;

// {integer} Calculate the date of Independence Day holiday for the current year. Rule: Nearest weekday to July 4. Excel equivalent formula: =IF(WEEKDAY(DATE(YEARNUM,7,4))=7,DATE(YEARNUM,7,4)-1, IF(WEEKDAY(DATE(YEARNUM,7,4))=1,DATE(YEARNUM,7,4)+1, DATE(YEARNUM,7,4))). 
bardateIndependenceDay = 
offsetIndependenceDay
+ IF(MOD(VALUE=7,offsetIndependenceDay)==0, -1, IF(MOD(VALUE=7,offsetIndependenceDay)==1, 1));

// CALCULATION FOR LABOR DAY HOLIDAY

// {integer} For Labor Day, Use fixed offset of 243 to simulate Excel's Date(YEARNUM,9,1) function. Leapyear included because date is after February 28.
offsetLaborDay = offsetThisYear + 243 + offsetLeapYear;

// {integer} Calculate the date of Labor Day holiday for the current year.  Rule: The 1st Monday in September. Excel equivalent formula: =DATE(YEARNUM,9,1)+0+CHOOSE(WEEKDAY(DATE(YEARNUM,9,1)),1,0,6,5,4,3,2). 
bardateLaborDay = 
offsetLaborDay + 0
+ IF(MOD(VALUE=7,offsetLaborDay) == 1, 1, 0)
+ IF(MOD(VALUE=7,offsetLaborDay) == 2, 0, 0)
+ IF(MOD(VALUE=7,offsetLaborDay) == 3, 6, 0)
+ IF(MOD(VALUE=7,offsetLaborDay) == 4, 5, 0)
+ IF(MOD(VALUE=7,offsetLaborDay) == 5, 4, 0)
+ IF(MOD(VALUE=7,offsetLaborDay) == 6, 3, 0)
+ IF(MOD(VALUE=7,offsetLaborDay) == 0, 2, 0);

// CALCULATION FOR THANKSGIVING DAY HOLIDAY

// {integer} For Thanksgiving Day, use fixed offset of 304 to simulate Excel's Date(YEARNUM,11,1) function. Leapyear included because date is after February 28.
offsetThanksgivingDay = offsetThisYear + 304 + offsetLeapYear;

// {integer} Calculate the date of Thanksgiving Day holiday for the current year. Rule: The 4th Thursday in November. Excel equivalent formula: =DATE(YEARNUM,11,1)+21+CHOOSE(WEEKDAY(DATE(YEARNUM,11,1)),4,3,2,1,0,6,5). 
bardateThanksgivingDay = 
offsetThanksgivingDay + 21
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 1, 4, 0)
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 2, 3, 0)
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 3, 2, 0)
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 4, 1, 0)
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 5, 0, 0)
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 6, 6, 0)
+ IF(MOD(VALUE=7, offsetThanksgivingDay) == 0, 5, 0);

// CALCULATION FOR CHRISTMAS DAY HOLIDAY

// {integer} For Christmas Day, simulate Excel's Date(YEARNUM, 12, 25) function, and include leapyear because date is after February 28.
offsetChristmasDay = 
offsetThisYear + 358 + offsetLeapYear;

// {integer} Calculate the date of Christmas Day holiday for the current year. Rule: Nearest weekday to December 25. Excel equivalent formula: =IF(WEEKDAY(DATE(YEARNUM,12,25))=7,DATE(YEARNUM,12,25)-1,IF(WEEKDAY(DATE(YEARNUM,12,25))=1,DATE(YEARNUM,12,25)+1,DATE(YEARNUM,12,25))).
bardateChristmasDay = 
offsetChristmasDay
+ IF(MOD(VALUE=7, offsetChristmasDay) == 0, -1, IF(MOD(VALUE=7, offsetChristmasDay) == 1, 1));

// CALCULATION FOR NEW YEAR'S DAY HOLIDAY

// {integer} Calculate the date of New Year's Day holiday. Rule: Nearest weekday after or on January 1. Excel equivalent formula: =IF(WEEKDAY(DATE(YEARNUM+1,1,1))=7,"No holiday",IF(WEEKDAY(DATE(YEARNUM+1,1,1))=1,DATE(YEARNUM+1,1,1)+1,DATE(YEARNUM+1,1,1)))
offsetNextYear =
IF(MOD(VALUE=4,YEARNUM()+1)==0, -1, 0)
+ (YEARNUM()+1-1900)*365 + (YEARNUM()+1-MOD(VALUE=4,YEARNUM()+1)-1900)/4 + offsetUniversal;

// {integer} RULE: If New Year's Day falls on a Saturday, no holiday. If it falls on a Sunday, then Monday is a public holiday. 
bardateNewYearsDay = 
offsetNextYear
+ IF(MOD(VALUE=7,offsetNextYear)==0, 0, IF(MOD(VALUE=7,offsetNextYear)==1, 1));

// CALCULATION FOR ALERT: TOMORROW OR IN ONE WEEK

// {integer} Calculate the date of next trading day (note if Friday or Saturday, skip to Monday), so that the previous bar on a daily chart is used to indicate that the next trading day is a holiday. In Optuma, holidays do not appear on a chart because they have no data (e.g. if a holiday is on a Tuesday, there will be a Monday bar, a Wednesday bar, but no Tuesday bar.) Therefore, to indicate a holiday on a chart it must be on a different bar. It makes sense to be forewarned of the holiday, so choose to either be alerted one day or one week prior.
bardateAlertDay = 
IF(alertNoticePeriod==1, 
  BARDATE() + IF(MOD(VALUE=7,BARDATE())==6, 3, IF(MOD(VALUE=7,BARDATE())==0, 2, 1)), 
  BARDATE() + 7
);

// ASSIGNMENT OF NUMBERS 1-10 TO EACH OF THE US ANNUAL HOLIDAYS

// {1} If current bar is 1 day/1week prior to New Year's Day, show with numerical value of 1 on a chart
V1 = IF(bardateAlertDay==bardateNewYearsDay , 1, 0);

// {2} If current bar is 1 day/1week prior to Martin Luther King Jr. Day, show with numerical value of 2 on a chart
V2 = IF(bardateAlertDay==bardateMartinLutherKingJrDay, 2, 0);

// {3} If current bar is 1 day/1week prior to Presidents' Day, show with numerical value of 3 on a chart
V3 = IF(bardateAlertDay==bardatePresidentsDay, 3, 0);

// {4} If current bar is 1 day/1week prior to Good Friday, show with numerical value of 4 on a chart
V4 = IF(bardateAlertDay==bardateGoodFriday, 4, 0);

// {5} If current bar is 1 day/1week prior to Memorial Day, show with numerical value of 5 on a chart
V5 = IF(bardateAlertDay==bardateMemorialDay, 5, 0);

// {6} If current bar is 1 day/1week prior to Independence Day, show with numerical value of 6 on a chart
V6 = IF(bardateAlertDay==bardateIndependenceDay, 6, 0);

// {7} If current bar is 1 day/1week prior to Labor Day, show with numerical value of 7 on a chart
V7 = IF(bardateAlertDay==bardateLaborDay, 7, 0);

// {8} If current bar is 1 day/1week prior to Thanksgiving Day, show with numerical value of 8 on a chart
V8 = IF(bardateAlertDay==bardateThanksgivingDay, 8, 0);

// {9} If current bar is 1 day/1week prior to Christmas Day, show with numerical value of 9 on a chart
V9 = IF(bardateAlertDay==bardateChristmasDay, 9, 0);

// {10} if current bar is 1 day/1week prior to an extraordinary market close show with numerical value of 10 on a chart
V10 = IF(isExtraordinaryCloses, 10, 0);

// {number} Output a number from 1-10 corresponding to a US public holiday.
V1 + V2 + V3 + V4 + V5 + V6 + V7 + V8 + V9 + V10

Hi Dean

Absolutely fantastic!! Many thanks for sharing.

Australian Public Holidays would also be very useful for this Aussie.

Then there are the holidays in UK, Europe (especially France and Germany), Singapore, Hong Kong, Japan, India and all the other markets that Optuma users watch.

You could take a real whipping meeting world wide demand.

Cheers

Trevor

That’s Awesome Dean, thanks so much for sharing.

I guess we’ll see this in an Optuma update :slight_smile: