# xlf EandA series

## 0. Preliminary

### The QUESTION

Write a Private Function procedure that determines if a particular date is a trading day for the Australian Stock Exchange. The function is only operational for the calendar year 2016. Name the function ASXTD2016. It has only one argument, an Excel date reference. If the date is not in the year 2016, then the function will return a #VALUE! error. Note: the holiday array must be imbedded in the function. This point is usually missed unless specifically stated.

Details of ASX non trading days can be found at ASX non trading days

## 1. ASXTD2016(Dte) - the code

### Version 1

• Holidays array: static - type string - repopulate with each call of function
• Holiday Boolean: DateValue converts string to date. Loop through elements of holiday

Code 1: ASXTD2016 function with DateValue
Private Function ASXTD2016(Dte As Date) As Variant
Dim i As Integer
Dim H(1 To 8) As String, bHol As Boolean

H(1) = "1-Jan-2016"   ' New Year's day
H(2) = "26-Jan-2016"  ' Australia day
H(3) = "25-Mar-2016"  ' Good Friday
H(4) = "28-Mar-2016"  ' Easter Monday
H(5) = "25-Apr-2016"  ' ANZAC day
H(6) = "13-Jun-2016"  ' Queen's Birthday
H(7) = "26-Dec-2016"  ' Christmas day
H(8) = "27-Dec-2016"  ' Boxing day

If Year(Dte) <> 2016 Then GoTo ErrHandler

For i = LBound(H, 1) To UBound(H, 1)
If Dte = DateValue(H(i)) Then
bHol = True
Exit For
End If
Next i

If bHol Then                                    ' Holiday
ASXTD2016 = False
ElseIf Weekday(Dte) = vbSaturday Or Weekday(Dte) = vbSunday Then  ' Weekend
ASXTD2016 = False
Else                                            ' Not holiday or weekend
ASXTD2016 = True
End If
Exit Function

ErrHandler:
ASXTD2016 = VBA.CVErr(xlErrValue)
End Function



### Version 1 - H array variation 1

• Holidays array: static - type integer - repopulate with each call of function
• Holiday Boolean: DateSerial converts string to date. Loop through elements of holiday

Code 1: ASXTD2016 function with DateSerial

Dim H(1 To 8, 1 to 3) As Integer, bHol As Boolean

If Year(Dte) <> 2016 Then GoTo ErrHandler

H(1, 1) = 2016: H(1, 2) = 1: H(1, 3) = 1    ' New Year's day
H(2, 1) = 2016: H(2, 2) = 1: H(2, 3) = 26   ' Australia day
H(3, 1) = 2016: H(3, 2) = 3: H(3, 3) = 25   ' Good Friday
H(4, 1) = 2016: H(4, 2) = 3: H(4, 3) = 28   ' Easter Monday
H(5, 1) = 2016: H(5, 2) = 4: H(5, 3) = 25   ' ANZAC day
H(6, 1) = 2016: H(6, 2) = 6: H(6, 3) = 13   ' Queen's Birthday
H(7, 1) = 2016: H(7, 2) = 12: H(7, 3) = 26  ' Christmas day
H(8, 1) = 2016: H(8, 2) = 12: H(8, 3) = 27  ' Boxing day

For i = LBound(H, 1) To UBound(H, 1)
If Dte = DateSerial(H(i, 1), H(i, 2), H(i, 3)) Then
bHol = True
Exit For
End If
Next i



### Version 1 - H array variation 2

• Holidays array: static - type integer - repopulate with each call of function
• Holiday Boolean: >b>Split string to elements in D, DateSerial converts string elements to date. Loop through elements of holiday

Code 1: ASXTD2016 function with DateSerial

Dim H(1 To 8) As String, D() As String, bHol As Boolean

If Year(Dte) <> 2016 Then GoTo ErrHandler

H(1) = "2016,1,1"    ' New Year's day
H(2) = "2016,1,26"   ' Australia day
H(3) = "2016,3,25"   ' Good Friday
H(4) = "2016,3,28"   ' Easter Monday
H(5) = "2016,4,25"   ' ANZAC day
H(6) = "2016,6,13"   ' Queen's Birthday
H(7) = "2016,12,26"  ' Christmas day
H(8) = "2016,12,27"  ' Boxing day

For i = LBound(H, 1) To UBound(H, 1)
D = Split(H(i), ",")
If Dte = DateSerial(D(0), D(1), D(2)) Then
bHol = True: Exit For
End If
Next i



### Version 2

• Holidays array: static - type date with date literals - declared as static - only populates if first element 1 is empty

Code 2: ASXTD2016 function
Private Function ASXTD2016b(Dte As Date) As Variant
Dim i As Integer
Static H(1 To 8) As Date
Dim bHol As Boolean
' Date syntax #mm/dd/yyyy#
If Year(H(1)) <> 2016 Then
H(1) = #1/1/2016#       ' New Year's day
H(2) = #1/26/2016#      ' Australia day
H(3) = #3/25/2016#      ' Good Friday
H(4) = #3/28/2016#      ' Easter Monday
H(5) = #4/25/2016#      ' ANZAC day
H(6) = #6/13/2016#      ' Queen's Birthday
H(7) = #12/26/2016#     ' Christmas day
H(8) = #12/27/2016#     ' Boxing day
End If

If Year(Dte) <> 2016 Then GoTo ErrHandler

For i = LBound(H, 1) To UBound(H, 1)
If Dte = H(i) Then
bHol = True
Exit For
End If
Next i

If bHol Then
ASXTD2016b = False
ElseIf Weekday(Dte) = vbSaturday Then
ASXTD2016b = False
ElseIf Weekday(Dte) = vbSunday Then
ASXTD2016b = False
Else
ASXTD2016b = True
End If
Exit Function

ErrHandler:
ASXTD2016b = VBA.CVErr(xlErrValue)
End Function


## 2. Code testing

Code 3: RunASXTD2016 sub procedure with output to Immediate Window
Sub RunASXTD2016()
Dim Ans As Variant
Debug.Print vbNewLine & Time & " ====="
Ans = ASXTD2016(DateValue("1-Jan-2016"))    ' NY Holiday - Friday
Debug.Print Ans
Ans = ASXTD2016(DateValue("3-Jan-2016"))    ' Weekend - Sunday
Debug.Print Ans
Ans = ASXTD2016(DateValue("5 Jan 2016"))    ' Trading day - Tuesday
Debug.Print Ans
On Error Resume Next
Ans = ASXTD2016(DateValue("3 Jan 2015"))    ' Invalid year
If IsError(Ans) Then
Debug.Print "Undefined error"
End If
End Sub


• This example was developed in Excel 2016 Pro 64 bit.
• Revised: Tuesday 6th of February 2018 - 01:20 PM, Pacific Time (PT)