Optuma Forums › Optuma Scripting › Automatically calculate public holidays from 1900-2203 › US Public Holiday Script

July 16, 2019 at 12:38 pm
#54053

Dean

- Topics: 13
- Replies: 25
- Posts: 38

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 |
// 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 |