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


xlf-ymd-selector-dv
Fig 1: YMD data validation version - cell based drop-down lists

Data validation settings:


1.2 Form Control (FC) version


xlf-ymd-selector-fc
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.

xlf-ymd-selector-fc
Fig 3: YMD list range - with spill arrays YearList, MonthList, and DayList

Figure 3 WS formulas (row 3)

 
NAME       CELL
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.

xlf-ymd-trading-day
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)

xlf-ymd-tradingday-workarea
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


xlf-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