This module illustrates the construction of a dynamic range named DateV. This dynamic date vector is a core component in the moving data windows that drive the descriptive statistics, and dashboard charts in the xlf Analyser Project.
Note: this demonstration is not a cut and paste source for the project. You need to understand the techniques included here, and then apply the selected techniques to the project. Most of this material is best used in the Workarea worksheet.
Some Excel functions used in this module.
|EDATE(start_date,months)||Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date)|
|FIND(find_text,within_text,start_num)||Locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. FIND is case sensitive. Use FINDB if default language includes Japanese, Chinese (Simplified), Chinese (Traditional), or Korean.|
|LEFT(text,num_chars)||Returns the first character or characters in a text string, based on the number of characters you specify. Use LEFTB if default language includes Japanese, Chinese (Simplified), Chinese (Traditional), or Korean.|
|SEARCH(find_text,within_text,[start_num])||Locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. SEARCH is not case sensitive. Use SEARCHB if default language includes Japanese, Chinese (Simplified), Chinese (Traditional), or Korean.|
|WORKDAY(start_date,days,holidays)||Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays|
Date and time conventions
First, some important points about the time and the data sort order – as shown by the horizontal blue and red arrows in figure 1. Calendar time runs from left to right – the blue time arrow – earlier dates (start dates) are on the left, and later dates (end dates) are on the right.
In contrast, the stock price and volume data is sorted in descending order, and dates run in the the opposite direction as shown by the red arrows. The start_date, is on the right, and date values decrease until the earlier last_date on the left. This concept is used later in the EDATE function, where a negative month value is used to return a date in the past, named last_date in figure 1. The green Date and DateV objects in figure 1, are equivalent to the transpose of the Date range, and the DateV range in the Analyser.
The DateV range
Initialize the selector panel by setting values for the StartDate and DataWindow length.
We’ll use –
- StartDate: 25-Mar-13
- DataWindow: 3 Months
The Date range is the master list of trading days, and acts as the primary key.
- Find the StartDate: in the Date range by using the MATCH function.
=MATCH(StartDate,Date,FALSE). Assign the value to the range named First.
- Extract the month value: from the string in the DataWindow selector. If the maximum value is a single digit, ie. less than 10, then the LEFT function can be used. If months has a variable number of digits, then use the SEARCH or FIND function to return the number of characters for the LEFT function.
Like most naming conventions in Excel, SEARCH is not case sensitive. Thus SEARCH is preferable to using FIND.
The formula is:
=LEFT(DataWindow,SEARCH(" ",DataWindow)-1). Assign the return value to a range named Months.
- Calculate the date -3 months: use the EDATE function. The formula is:
=EDATE(First,-Months). In this case the return value is 25 Dec 2012.
- If a holiday, calculate the previous trading day: as StartDate minus 3 months is a public holiday, use the WORKDAY function with Holiday argument to find the previous trading day. Remember, we are counting backwards in time, so if its a holiday, keep going backwards until a trading day is found. The formula is:
=WORKDAY(WORKDAY(DATEVALUE("25Dec2012"),1,holiday),-1,holiday). This returns 24 Dec 2012. Assign the value to a range named Last.
- Find the position of Last in Date vector: this is the same technique as used in point 1 for the First
- Construct the DateV range: You now have the dimension and location values, so these can applied to the OFFSET function, and then assigned to the range name DateV.
Set up a WorkArea and experiment with the concepts shown in the list, to evaluate the return values under different scenarios.
- This example was developed in Excel 2013 Pro 64 bit.
- Last modified: , [Australian Eastern Standard Time (AEST)]
- Thanks to Yang for pointing out an error in the point 4 WORKDAY formula – corrected 5 September 2015