xlf EandA series


M Data dynamic range


Code development version. This document provides some assignment solution ideas, presented here as an exercise.


0. Preliminary


Some FACTS from the assignment




1a. Worksheet names

  • M data - is the missing data interpolation worksheet (named M Data dev in this version)
  • Summary - and the case of no data, and/or missing data (not included in this version)

1b. Data naming conventions

  • No data - is the case where an initial listing or delisting occurs in the window period (as set by the selector panel). This case will activate the Out of Data alert on the selector panel
  • Missing data - occurs then the data is missing because of events such as trading halts, data errors, or thin trading. In this case the missing observations are interpolated. These values must include the formulas used in the interpolation process. Apply a Yellow fill colour to the interpolated cells

2. Missing data - identification

The GetDataSS defined name returns a #N/A. Remember the dot "You will need temporary name holders to suppress errors".

Identification of missing observations is done on a stock by stock basis.

  1. Using the selector panel, select the first stock for the primary stock panel. This will load the data in the Stk Summary worksheet
  2. Identify any #N/A errors caused by missing observations
  3. Write an Advanced Filter criteria to filter all contiguous #N/A rows plus the preceding and succeeding rows with valid data. Name each of the criteria ASX_NA where ASX is the stock code
  4. Copy the visible data for the filter, columns - Dates to Volume -, and paste as values to the range starting in column B of the M data worksheet. Leave a blank row between each block of company missing dates
    5. 5. Repeat steps 1 to 4 for the remaining stock codes.

3. Missing data - interpolation and identification

  1. Use an interpolation technique such as LKV to replace the copied #N/A errors
  2. Using the example for ZZZ, and ZZZ_, enter an index vector ASX_ (in column A), that will be used to form a dynamic range for the missing data block, including any of the preceding and succeeding rows
  3. The analyst is required to create a series of dynamic ranges based on the MissingData column A vector, ie. the ASX_ vector.
    The range names are applied to each of the interpolated blocks. Note: the INDIRECT function is not compatible with a dynamic range based on OFFSET. Think about the specification of the INDIRECT function!
  4. Use helper cells on the M data sheet (seed areas appear in column L). Name the area Mdynamic
  5. If there is no missing data, complete this section for the ZZZ example (and retain the ZZZ.AX worksheet)
  6. This is a challenging question. If the analyst is unable to solve the challenge, then apply manual naming (for less marks). A similar solution applies to the #N/A filter, use manual copy and paste (for less marks)

 

The EXERCISE - implement the following


This exercise picks up from point 3.3 in the assignment FACTS list. (The analyst is required to create a series of dynamic ranges …)


About the module (see figure 1):


1. The development workbook


Fig 1: Excel Web App #1 - the completed M Data development worksheet

2. Discussion


No discussion (about the WS) at this stage! - except for noting the use of a double nested INDIRECT function in the statistics area =summary_statistic(INDIRECT(INDIRECT(Select)). This is required to remove the double quotes on the text string created by the link to a Data Validation selector pass through in the Test Area 1. When the Data Validation selector is by-passed in Test Area 2, only a single INDIRECT is needed.