Transaction Configuration Overview

Summary

The primary goal of the Transaction Analysis document is to display all of the transactions performed at physical branch locations. The document uses the information stored in the SHARE-HISTORY and LOAN-HISTORY datasets. Since there is usually more information in these sets than what is necessary, the document has multiple types of filters that need to be set up before first use. The three levels of filters are Branch Filtering, Teller Filtering, and Command Filtering. By configuring these options it is possible to tailor the document to display only the desired transactions.

Note: all of the underlying history is archived, even if it is filtered out of the final Vital Signs document. None of the filters discussed in this overview permanently removes archived information.

Types of Filters

Branch Filtering

Branch-level filtering is the primary way the Transaction Analysis document removes unwanted transactions. A list of the desired branches is specified in a spreadsheet and any transactions not performed at these branches are removed from the document.

The name of the configuration spreadsheet is brinfoYYYYMMDD.xls. It is stored in the ../cuname/data/config Vital Signs directory.

Note that if the option to use accounting centers as branches is set, the brinfo file should contain a list of the desired centers.

The brinfo file is also where the branch’s lobby and extended hours are specified. These hours are for reporting purposes only -- not for transaction filtering. Even if a transaction occurs outside of the specified open and close hours, it is still reported (provided it meets the other filtering conditions). The closing hours can affect how teller hours are calculated -- see the section ‘Calculating Time Worked’ later in this overview.

For more information on how to set up the brinfo file, see the ‘Configuring BRINFO Files’ section.

Teller Filtering

There are many system-generated teller codes used by Spectrum for its back office processing. There are also employees who may process transactions that you do not wish to see in the final document. The teller-level filtering enables any transactions associated with these teller codes to be removed.

By default, the Transaction Analysis document will attempt to exclude the system-generated tellers. Any teller initials that are greater than 3 characters, as well as teller initials that do not start or end with a letter, are excluded. For example, the teller code:

AB1

would be excluded, but the code:

C2D

would be allowed, since the first and last characters were alphabetic.

For back office employees with valid teller codes that you wish to exclude, use the TC_TELLERS.xls configuration file. It is also possible to override the default automatic exclusion here as well. If, for example, AB1 was really a valid teller, its code could be changed to not exclude it automatically. See the ‘Configuring the TC_CODES and TC_TELLER Files’ section for more information.

Finally, it is possibly to change the rules used to identify system-generated tellers. If your site has different parameters for this, let us know and we will customize the document’s code for you.

Command Filtering

The final way to filter the incoming history data is by the type and command of each record. The type of command is the Spectrum TYPE field. For example, types of 100, 101, and 102 are all monetary transactions. By default, all types are allowed in to the final Transaction Analysis document. It is possible to remove unwanted types by specifying them in the main ‘VitalSigns.xls’ configuration file.

The setting is on the ‘Transaction’ sheet in the xls file, and is called

vTR_Exclude_Type

Use a semi-colon separated list of value to specify which types to exclude. For example, types 499, 699 and 999 are all system-generated records used to store extra information about other commands in case an UNDO is performed. These records can be excluded by listing them as

499;699;999

for this setting.

Individual commands can be filtered as well. The command codes are the 4-letter Spectrum commands. Use the

vTR_Exclude_Cmnd

setting for this. An example is the cash dispenser command DISP. Many sites do not want occurrences of this command reported so it is filtered out. If there are other commands you wish filtered, use a semi-colon separated list.

Automatic Duplication Filtering

Some history transactions that are stored in the database are not unique. It is possible for the same record to be stored multiple times. This can affect the transaction counts reported by Vital Signs.

There is a setting in the VitalSigns.xls file that can affect how history records get filtered. This is the

vTR_Count_Dup_Trans

setting. There are three possible values: ALL, EXACT, KEY

The ‘ALL’ value allows all duplicate records into the document.

The ‘EXACT’ value will compare the entire history record of each entry and only allow distinct values into the document.

The ‘KEY’ value is similar to ‘EXACT’ but will only compare the non-binary portions of non-monetary transactions when filtering. This is important for a single datachange command that might be stored as multiple records. The binary information is different for each record, so ‘EXACT’ would not filter the duplicates, even though the multiple records were really one command.

    ‘KEY’ is the recommended setting for all new installs.  The other options are mainly to support existing sites that previously used the older settings.

Configuring BRINFO Files

The Transaction Analysis document uses a set of configuration files to decide which branches to include.

This is done with the files called brinfo########.xls, where the ####### is a CYMD date. There should already be a sample in your data/config directory on the VitalSigns Windows server.

The brinfo file(s) specify the branches and their hours. If you only put one instance of the file on the server, the branches/times will be used for the entire time frame (default is 12 months) tracked by the document. If your branch hours ever change, ie you need to add a new branch or modify drive-thru hours, you make another instance of the brinfo file with the date you want the new times to take effect. After a few years, it would be normal to have three or four different brinfo files on the system.

Here are the guidelines for setting up a brinfo file.

1. You should list each branch (by branch number) that you want tracked. These should be any branch where transactions are done by physical tellers (whether it is an actual branch or the call center.) Do not include any back-office or automated branches.
2. Each branch should have seven lines, one for each day of the week. The sample xls file can be used as a model. Both the day names and numbers are listed, so there would be no confusion on whether Sunday or Monday was the first day of the week.
3. The next two columns, XLS_OPEN and XLS_CLOSE are the lobby hours of the branch. These should be in a 24-hour clock format, rounded to the nearest half-hour period. Note that all transactions, regardless of time, are included in the document, so you don't have to worry about listing "closed" as 5:00 and losing transactions that occur at 5:01. The lobby hours are only there to help identify transactions that happen outside of the normal open hours.
4. The XLS_FTE is an obsolete field and has been deprecated. It is there only for compatability with the older Teller Activity document.
5. The XLS_EXT_OPEN and XLS_EXT_CLOSE columns are if you want to track drive-thru (or any special early/late hours) separately from the main lobby hours. The EXT_OPEN time should be earlier than the XLS_OPEN time, and the EXT_CLOSE time should be later than the XLS_CLOSE time. If there are no extended times, just put 0. There is a special case supported if you want a day to be considered all extended times, ie a Saturday w/ only drive-thru hours. In this case, set the XLS_OPEN and XLS_CLOSE to 0, and set XLS_EXT_OPEN and XLS_EXT_CLOSE to the branch hours for that day.
6. For Sundays (and possibly Saturdays) when the branches are regularly closed, put 0's for all 4 open/close hours columns. Note that closed days due to holidays or special circumstances are handled elsewhere. You should only create a new brinfo file when a branch's regular schedule changes, or a new branch is added.
7. The EXT_FTE is an obsolete field and has been deprecated. It is there only for compatability with the older Teller Activity document.

Date the new brinfo file with today's date and copy it to the data/config directory. If you know occurrences in the last two years where branch hours were changed, you may want to create a second brinfo file to reflect the old times. Be sure to rename/remove the sample brinfo20080101.xls file if it exists.

Configuring the TC_CODES and TC_TELLER Files

Vital Signs gives you the ability to group sets of tellers together, enabling quick selection. It also uses these codes to filter unwanted tellers (back office or automated tellers, for example) from the document. Two spreadsheet configuration files are used for this: TC_CODES.xls and TC_TELLERS.xls.

Both of these spreadsheets are different from the brinfo files in that there is always only a single version of each on the system. The brinfo files are date-stamped, ie brinfo20090301.xls, and multiple dates are allowed to document the changes in branch hours over time. Although there is always only one TC_TELLERS.xls file, it allows you to specify date ranges inside the document to mark teller code changes that occur on specific dates.

     You should always back up the old TC_CODES and TC_TELLERS files before overwriting them with a new version.

The first file (TC_CODES) should already be on your system -- you can modify it to contain the different codes you want to use for classifying the people who perform transactions.

Each code must be assigned to a group, either "T" for the teller group, or "O" for the others. The Teller Group should include all employees whose MAIN function is to perform transactions. The Other Group is for people who perform transactions when needed but it is not their main function (Ie Managers or Loan Officers).

You can have multiple codes within the same group, ie "Full Time Teller", "Floating Teller" etc. Each of these codes will be selectable in the Vital Signs document. You do not want to make the codes too specific though, or they can become too difficult to maintain.

Once the codes are set up, you can create the TC_TELLERS.xls sheet. The best way to do this is to go to the Maintenance tab and export the teller list to create an Excel file. Vital Signs will give you a table with all the tellers in your system who have performed transaction in the past 12 months. You can right click this table and choose "export" and create an xls file from it.

The xls file will have all of your current tellers, with either an <X> or an <M> as the teller code. An <X> means that this teller code should be excluded and no transaction counts for the teller will be imported. We have some rules to automatically mark 4 character teller codes, as well as three char codes that start or end w/ a number (ie "AT1") as excluded by default. If there is a teller code you want to track that is marked as excluded, you can change it to a valid code and all of the old transaction will be included on the next refresh. (All excluded transactions are archived -- they are just not presented in the final document.)

An <M> code means the teller does not yet have a valid code defined. You will need to change the code to one of the new codes you set up in TC_CODES, ie "TLR" or "FLT". There are columns to add a date range in case a teller's status changes over time. (This is meant more for a permanent change, like a promotion from CSR to Asst. Manager -- you do not want to create roles so specific that a single teller is constantly moving between them.)

As new employees start performing transactions, they will be added to the Vital Signs document as <M>. The document will remind you on the "Details" tabs that there are unassigned transactions in the current date range. This means you should re-export the teller table from the maintenance page and update the codes.

    Updating the TC_TELLERS file is necessary to maintain the teller codes, otherwise new tellers will keep the <M> (missing) status.

Vital Signs will never overwrite any entry you place (with one exception --see below) so it is possible for you to be proactive and add new tellers to the system before they have any transactions. In this case, you would need to add the entire row of information directly to the TC_TELLERS.xls file instead of re-exporting the table from inside the document.

There is one other system status, the unassigned <U> status. This is a special marker to alert you of a date range problem with a teller, ie an overlapping date range where the teller might have two (or no) roles assigned. It shouldn't come up for any teller where you haven't modified the effective dates. In the case of a date conflict however, the system will create a new entry for the employee for the conflicting date range and add a <U> status. Any part of the date ranges where there was no conflict will be preserved with the original status.

Finally, you might notice some of the automatically generated teller entries contain teller names, while some only have their initials repeated in the name column. If the name is missing, it means the teller no longer exists in the TELLER-INFO dataset and their name could not be identified. Remember that ALL transactions in the last 12 months are used, so it is common to have transactions performed by former employees. If you know the names of these tellers, you can add them to the spreadsheet. This is also true if you want to change the name of an existing teller for reporting purposes. The new name will take precedence over the TELLER-INFO name. Note -- this is for names column only, not the initials column. You cannot change someone's initials since it is the key value used to identify the transactions -- they would just show as <M> with their original initials the next time you refreshed the document.

For questions please contact support@vital-soft.com or 1-800-848-2576 ext 21