Exchange non trading days - ASX & NASDAQ
This module contains a worksheet with a list of non trading days for two stock exchanges; the Australian Stock Exchange (ASX) and the USA NASDAQ Stock Market (NASDAQ). The Excel date references are assigned to the dynamic range named: Holidays.
For further details of ASX trading days, see: Calendars - ASX, and for NASDAQ Nasdaq - U.S. Equity and Options Markets Holiday Schedule
TIP: Download a list of ASX non trading dates from the public holidays list in the figure 1: Excel Web App # 1 below
Dynamic range | OFFSET /w COUNT
The ASX Holidays range (figure 1) name is dynamic - Holidays Refers to: =OFFSET('ASX NTD'!$B$3,0,0,COUNT('ASX NTD'!B:$B),9)
. This allows the name to expand when the user adds new dates for years 2010 and later.
The OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
- SYNTAX: OFFSET(reference, rows, cols, [height], [width])
The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments.
- SYNTAX: COUNT(value1, [value2], ...)
In the ASX file, the OFFSET reference is cell $B$3
which contains the upper left date - Fri 1 Jan 2010 - this is the anchor point - so the OFFSET rows and cols are set to 0. The value for the OFFSET height is returned by the COUNT function - there are 14 numbers, 2012, 2013, ... 2023 (Windows Excel dates are represented by the number of days since 1 January 1900; 1 Jan 2010 = 40,179) in the left date column so the height is 14 (rows). To avoid a false value, the entire column B should not contain any other cells with numbers. The column must be an absolute reference $B:$B
.
OFFSET width is set to 9 because this is the number of Australian national public holidays (9 unique holidays labels). With the anchor point set to $B$3, and years 2010 to 2023 inclusive, the Holidays range is 14 rows x 9 columns (ie. a 14 x 9 array).
The choice of worksheet name: holidays, and range name Holidays is intentional. Including the three character day "ddd" item assists with verification of the Holiday values. For example, the Good Friday date reference should always return Fri.
Download the ASX data file by using the Microsoft Excel Web App link in #1, and NASDAQ data file using the Microsoft Excel Web App link in #2.
TIP: Copy the date array - if you copy the dates in columns B to I, they will paste as text strings. Instead, scroll to the right in the holidays worksheet, then copy the date serial values in columns K to R
The NASDAQ Holidays range (figure 2) name is dynamic - Holidays Refers to: =OFFSET(holidays!$A$3,0,0,COUNT(holidays!$A:$A),11)
. This allows the name to expand when the user adds new dates for years 2010 and later. Here, the reference was changed to column A due to the blank date for New Year's day in 2022.
- This example was developed in Excel 2013 Pro 64 bit and updated in Office 365 :: Excel.
- First Published: 15 August 2014
- Revised: Wednesday 6th of September 2023 - 02:05 PM, [Australian Eastern Standard Time (EST)]