Road map to the xlf-ymd-selector file

1. YMD selector

The xlf-ymd-selector file described in this module contains both Data Validation and Form Control versions of the Year-Month-Day worksheet selector interface. The available selector dates, described as the CurrentTDlist, are geared to trading days on the Australian Stock Exchange (ASX). Assuming a 20 minute lag in available data, the list updates at 10:30 am each trading day. Features of the file are summarised in the following sections. A copy of the file is available for download.

1.1 Data Validation (DV) version

Fig 1: YMD data validation version - cell based drop-down lists

Data validation settings:

1.2 Form Control (FC) version

Fig 2: YMD form control version - Combo Box (Form Control) drop-down lists

Form Control settings:

2. YMD dates

Year-Month-Day values are derived from the CurrentTDlist (section 2.2).

2.1 YMD list constructor

Each list in figure 3 is returned by spilled formula in row 3.

Fig 3: YMD list range - with spill arrays YearList, MonthList, and DayList

Figure 3 WS formulas (row 3)

YearList ::  M3: = UNIQUE(YEAR(CurrentTDlist))
MonthList :: N3: = UNIQUE(MONTH(FILTER(CurrentTDlist,YEAR(CurrentTDlist) = Year)))
DayList ::   O3: = DAY((FILTER(CurrentTDlist,(YEAR(CurrentTDlist) = Year) * (MONTH(CurrentTDlist) = Month))))

Conditional formatting showing current selector settings:

2.2 Trading day lists

TDAnchor is the first trading day - cell J3.

Fig 4:Trading day lists - all trading days for span of Holiday range (column J), all trading days from current trading day and earlier (column K)

Figure 4 WS formulas

NAME          CELL
TDAnchor       J3: = LastBusDay
               J4: = IFERROR(IF(WORKDAY(J3,-1,Holidays) >= FirstBusDay,WORKDAY(J3,-1,Holidays)),".")
               K3: = OFFSET(TDAnchor,TDposn-1,0,TDminPosn-TDposn+1)

2.3 Trading day WorkArea

The first value in the CurrentTDlist is conditional on the computer time and the most recent holiday data (LastBusDay - section 2.3)

Fig 5: Trading day WorkArea - list attributes of TD... values

Figure 5 WS formulas

Now ::         N28: = MIN(TDAnchor,NOW())
Ten.30 ::      N29: = IF(Now - INT(Now) >= TIME(10,30,0),Now,Now - 1)
TradDay ::     N30: = WORKDAY(WORKDAY(Ten.30,1,Holidays),-1,Holidays)
TDposn ::      N31: = MATCH(TradDay,TradingDays,0)
TDminPosn ::   N32: = MATCH(MIN(TradingDays),TradingDays,0)
TDminVal ::    N33: = OFFSET(TDAnchor,COUNT(INDIRECT("C"&COLUMN(TDAnchor)&":"&"C"&COLUMN(TDAnchor),FALSE)) - 1,0,1,1)
TDlastFPos ::  N34: = ROW(OFFSET(TDAnchor,COUNTA(INDIRECT("C"&COLUMN(TDAnchor)&":"&"C"&COLUMN(TDAnchor),FALSE))-1,0,1,1))-ROW(TDAnchor) - 1
TDavailable :: N35: = TDlastFPos - TDminPosn

TradingDays range defined name

2.3 Holidays WorkArea

Fig 6: Holidays WorkArea - first and last trading from Holidays range

Figure 6 WS formulas

NAME          CELL
FirstYrHols    N38: =YEAR(INDEX(Holidays,1,1))
FirstBusDay    N39: =WORKDAY(WORKDAY(DATE(FirstYrHols,1,2),-1,Holidays),1,Holidays)
LastYrHols     N41: =YEAR(@INDEX(Holidays,ROWS(Holidays),8))
LastBusDay     N42: =WORKDAY(WORKDAY(DATE(LastYrHols,12,31),1,Holidays),-1,Holidays)

Holidays range defined name