xlf EandA series


ASXTD2016(Dte)


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



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



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



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



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