xlf EandA series

ASXTD2016(Dte)

0. Preliminary

The EXERCISE

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: 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: Friday 24th of February 2023 - 03:12 PM, Pacific Time (PT)