This page is no longer maintained – goto Online2 :: 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

 

 

Dynamic range | OFFSET /w COUNT

The ASX Holidays range (figure 1) name is dynamic – Holidays Refers to: =OFFSET(holidays!$B$3,0,0,COUNT(holidays!$B:$B)-1,8). 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 this example, 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 8 numbers (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 10 (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.

Because the count values start at 1, and the offset values start at 0, you need to subtract 1 from the COUNT return value to synchronize the two series. OFFSET width is set to 8 because this is the number of Australian national public holidays. With the anchor point set to $B$3, and years 2010 to 2018 inclusive, the Holidays range is 10 rows x 8 columns (ie. a 10 x 8 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.

 

 

Fig 1: Excel Web App #1: ASX Public holidays data fileFig 2: Excel Web App #2: NASDAQ trading holidays data file

---

• This example was developed in Excel 2013 Pro 64 bit.
• Last modified: , [Australian Eastern Time (AET)]