Training: VISION:Excel

Administrative Information Services

Data Administration

VISION:Excel

A. Statistical Functions

VISION:Excel provides the VISION:Results programmer with the ability to create sophisticated statistical reports with a minimum of programming effort. By adding just a few statements to your VISION:Results program code, you can perform advanced statistical functions such as Aging Analysis, Frequency Distributions, Random Sampling, Linear Regressions, Trend Line Analysis, and Scatter Diagrams. These functions automatically generate their own standardized reports so the programmer doesn't need to fuss with report layouts, etc.

B. Special Routines

The VISION:Excel facility also equips the VISION:Results programmer with special audit and conversion routines which enable the programmer to write more powerful VISION:Results programs with less effort. The most commonly used categories of these routines are Date Conversion, Date Age Analysis, Differences Between Dates, and Random Number Generation. Rather that having to code these functions from scratch, they can just be copied into VISION:Results code using a special COPY statement.

A. OPTION EXCEL

1. Before the system can run a VISION:Results program that invokes VISION:Excel functions, we need to tell the system that we'll be using these functions. We do that by supplying VISION:Results with an additional OPTION statement at the beginning of our program. The statement

OPTION EXCEL

tells the VISION:Results system that we will be using VISION:Excel in this program. The OPTION EXCEL statement must be placed at the top of your VISION:Results program with the other OPTION statements.

B. OPTION STRUCTURED2

1. In the past, you've always used the OPTION STRUCTURED statement at the beginning of your program to indicate to VISION:Results that you would be using the programmer controlled cycle and structured code. This statement disallowed certain unstructured VISION:Results statements. However, VISION:Excel calls in pre-written code that may sometimes contain the unstructured code that we are specifically disqualifying with the OPTION STRUCTURED statement. In order to allow both structured code and unstructured code in the same program, we need to change the OPTION STRUCTURED statement to the following:

OPTION STRUCTURED2

This will allow your structured VISION:Results code to call modules that may not necessarily contain structured code. The OPTION STRUCTURED2 statement will replace the OPTION STRUCTURED statement and should be placed at the top of your VISION:Results program with the other OPTION statements.

C. Defining Parameters

1. What is a parameter?

A parameter is a special variable in a program which is used to pass information to and from different programs or routines. For example, if you're performing a Gregorian to Julian date conversion and wish to use the VISION:Excel date conversion routine, you need to tell the routine what date field you want converted, it's current format, and the format that you want returned to your program. The variables we use to tell it these things are called parameters. We pass parameters into another program to give it information needed to perform it's task and then we receive information back from that program via the same parameters, giving us the information we asked for. We can then use those same variables in the course of our program logic to achieve our desired programming results.

C. Defining Parameters (Continued)

2. How to identify parameters to VISION:Excel

Whenever we use a function of VISION:Excel, we have to provide the VISION:Excel function with the information it needs to use for it's processing. We do this by placing parameters after the function or routine name. The exact syntax is dependent upon the specific VISION:Excel function you wish to use. The following are typical types of statements you will use to specify parameters:

Function Parameter1 Parameter2 Parameter3...

Call Routine USING Parameter1 Parameter2 Parameter3...

Copy Routine #1=aaaaaaaa #2=bbbbbbb #3=cccccc...

D. VISION:Excel Statements

There are four main statements which may be included in your VISION:Results programs to invoke the VISION:Excel functions. They correspond with the four main audit functions that are available to us through the VISION:Excel facility. These statements must be coded within the main logic portion of your program. For each audit function statement, there are corresponding function set-up statements that should be coded just after the file definition section of your program. These set-up statements define the parameters that the specified VISION:Excel function will use to perform it's work.

1. AGING

The Aging function of VISION:Excel is used to determine the number of days a particular date is prior to or beyond a relative date. Aging is most commonly used in the accounting environment to determine what receivables are past due and the number of days they are past due. Although aging analysis is most often used in accounting systems, it can also be used for many other business tasks such as identifying correspondence frequencies, identifying employees eligible for various benefits, etc.

2. FREQUENCY

The Frequency function of VISION:Excel allows you to report on data by ranges or intervals based on the number of values to be included in the distribution. For example, you could run a frequency distribution to identify the number of individuals on your receivables file who owe less than $100, $101 to $500, $501 to $1000, etc. The frequency distribution will provide you with the following information for each range or interval: count of the items, percent of the count in relation to the entire file, total dollar amount, dollar amount percent in relation to the entire file, mean, and standard deviation. You may specify Equal intervals, Logarithmic intervals, or Alphanumeric intervals. The frequency function will also produce a count and/or value histogram according to your specifications.

D. VISION:Excel Statements (Continued)

3. REGRESSION

VISION:Excel's Regression function will allow you to measure and study the relationship between two variables in three different ways. Linear Regression, Scatter Diagrams and Trend Line Analysis are all options of the regression function.

A. Linear Regression

In using linear regression, a theoretical 'straight line' that ties a dependent variable to an independent variable is defined. The dependent variable is then plotted along the straight line according to it's relationship to the independent variable. For example, you might use a linear regression to analyze the relationship between Sales (the independent variable) and Profit (the dependent variable).

B. Scatter Diagrams

A scatter diagram is a graphic representation of the plotting of a dependent variable against an independent variable. Each combination of X (independent variable) and Y (dependent variable) represent a position on the graph. You might use a scatter diagram to represent monthly sales trends. The independent variable would be the Date and the dependent variable would be Sales.

C. Trend Line Analysis

Trend Line Analysis is a sub-function of the Linear Regression function. For Trend Line Regressions, the dependent variable will always be time oriented. (i.e., Month, Year, etc.) A very most common use for a Trend Line analysis would be to track annual sales by year.

4. SAMPLE

Sampling is a way of gathering information about a whole collection of items by examining only a portion of the population. The sample is expected to be more or less representative of the entire population. VISION:Excel provides you several different sampling techniques, both statistical (every item in the population has an equal change of being selected) and non-statistical (simple). The statistical sampling options offer both attribute oriented and value oriented techniques. The following list represents the available VISION:Excel sampling options:

A. Statistical Sampling Techniques

1. Attribute Oriented Sampling
<

a. Attribute Estimation Sampling

b. Discovery Sampling

c. Acceptance Sampling

2. Value Oriented Sampling

a. Estimation Sampling of Values

b. Proportional Sampling

c. Dollar Unit Sampling

d. Stratified Mean Estimation Sampling

B. Non-Statistical Sampling Techniques

1. Simple Interval Sampling

2. Simple Random Sampling

3. Stratified Simple Random Sampling

4. Stratified Simple Percentage Sampling

E. Using Special VISION:Excel Routines

1. Copying a Routine VS. Calling a Routine

You will notice as you use the miscellaneous VISION:Excel subroutines, that some of them must be "Called" into your program and some must be "Copied" into your program. The difference between the two methods of using these special modules should be transparent to you with the exception that you must used the method specified by VISION:Excel when using that specific subroutine.

When a subroutine is copied into a program, an actual copy of the VISION:Results code for that subroutine is inserted into the program in the position where the COPY statement occurs.

If a subroutine is called, the system actually leaves your program temporarily and runs a different program, returning to your program when the called program is finished running.

Again these differences should be transparent to you as you use these miscellaneous subroutines.

2. Special Routines Available

a. Date Routines

1. Julian/Gregorian Conversions

Quite often, the date on a file that you are reading is not in the format that you need it in for processing. VISION:Excel provides us with a module that will take a date in either Julian format (YYDDD) or Gregorian format (MMDDYY or YYMMDD) and reformat it. You should be aware that this module will only allow you to convert from Julian to Gregorian and from Gregorian to Julian. This module will not reformat a Gregorian date in one format to a Gregorian date in another format (i.e.; MMDDYY to YYMMDD). This subroutine is called DYLDATE.

2. Age Analysis

In addition to full aging analysis discussed in Section III, VISION:Excel allows you to compute the age in days of a particular transaction date based upon a specified audit date. This subroutine is called DYLAGEDT.

3. Difference Between Dates

If you should need to know the difference between two dates, VISION:Excel provides you with a module called DYLDATDF. For example if you have a billing date and a last payment date on file, you could run the comparison on the file to determine the number of days between billing and payments for each record on the file. The dates used by this module must be in MMDDYY format. The subroutine allows you to request an optional preformatted report of the dates and their differences.

E. Using Special VISION:Excel Routines (Continued)

4. Day of Week Determination

VISION:Excel provides the ability to determine the day of week for any specified date between January 1, 1700 and January 1, 2400. This module is called DYLDAYWK.

b. Random Number Routines

1. Random Number Generation

a) Random Number Generation - Called

One type of Random Number Generator provided by VISION:Excel generates a random number in 4-byte binary format. The number can range between 0 and 2 to the 31st power. A limit can be placed on the range by placing a restriction in the calling area. To use this subroutine, you must CALL DYLRAND.

b) Random Number Generation - Copied

A second type of Random Number Generator provided by VISION:Excel also generates a random number in 4-byte binary format. The number can range between 1 and 2,147,483,643. A limit can be placed on the range by coding a different maximum value on the copy statement. You may also specify a random seed to be used in the random function. If no seed is coded in the COPY statement, VISION:Excel automatically calculates it's own seed based on the system clock's time-of-day. To use this subroutine, you must COPY DYLRAND.

2. Random Number Sequences

VISION:Excel will allow you to create a sequence of random numbers. Numbers will not be repeated until the limiting cycle is completed. To use this subroutine simply specify the limiting value, an optional seed value, a 20-byte hold area field, and the name of the field to contain the random number. The name of this subroutine is DYLRNSEQ.

c. File Audit Routines

1. File Sequence/Duplicate Checks

VISION:Excel provides you with a subroutine that will check a file's sequence, report missing sequence numbers, and report duplicate numbers. This subroutine automatically generates a report indicating duplicates and/or missing sequence numbers on the file. You can chose not to receive this report and create your own report in your program if you wish. You may also choose to sort the file you want sequence checked before the sequence checking begins. This subroutine is called DYLSEQCK.

2. Negative Value Analysis

You may use VISION:Excel to analyze an amount field for negative amounts. For example, if you want to examine your receivables file for credits, you could run the negative value analysis on the amount field to determine if there are any individuals on the file who have a negative amount in the amount field. This example is based upon the assumption that a negative value in the account amount field infers that the account is in a credit status.

If you have comments or suggestions about this webpage Email us. (Please do not change the subject line)