home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Shareware Overload
/
ShartewareOverload.cdr
/
busi
/
sectrk.zip
/
SECTRAK.DOC
< prev
next >
Wrap
Text File
|
1990-09-02
|
55KB
|
1,385 lines
╔════════════════════════════════╗
║ ║▒
║ SECURITY TRAK ║▒
║ ┌────────────────────────────┐ ║▒
║ │ UNLICENSED EVALUATION COPY │ ║▒
║ └────────────────────────────┘ ║▒
║ (c) 1990 by E G Collins ║▒
║ All Rights Reserved ║▒
║ ║▒
╚════════════════════════════════╝▒
▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
OVERVIEW
SECURITY TRAK is PC-based system for tracking an investment
portfolio consisting of US Treasury Bills, Notes, & Bonds;
Municipals; Government Agency issues (FNMA, GNMA, FHLMC); and
pools of Mortgage Backed Securities. The program will
automatically accrue interest and accrete discount or
amortize premium on a monthly or actual-days basis, as well
as compute principal payments due on Mortgage Backed
Securities (MBS) based on current pool factors. Market
values along with Gain/(Loss) will be computed by entering
the current market price. Total accrued interest due (and
unpaid) is accumulated in the individual security files, and
actual cash flow from payments received is reported for each
quarter and year-to-date.
THIS PROGRAM IS DISTRIBUTED AS SHAREWARE, AND THE USER IS
GRANTED A LIMITED LICENSE TO EVALUATE THE PROGRAM. IF YOU
FIND THIS PROGRAM IS USEFUL AND YOU WISH TO CONTINUE USING
IT, PLEASE REGISTER BY COMPLETING THE FORM AT THE END OF THIS
DOCUMENTATION AND SENDING IT WITH $39.00 TO:
BANQUETTE SOFTWARE
E G COLLINS
2300 EDENBORN #147 II
METAIRIE, LA. 70001
Registration will entitle you to a licensed copy of the
latest version and notice of all future upgrades describing the
most recent enhancements to the program. Governmental
and business entities MAY NOT use this program without
remitting the full registration fee (site licences
available).
The user is granted permission to make unlimited copies of
this program and to distribute those copies as long as all
the files are copied intact and no fee is charged for the
program. A duplication charge not to exceed $5.00 may be
collected.
This program is provided AS IS without any warranty, express
or implied, including but not limited to fitness for a
particular purpose.
SECURITY TRAK - 1
PROGRAM BACKGROUND
The program was developed for a community Commercial Bank
with a small, but fairly active, investment portfolio. While
"book" totals were maintained on their mainframe computer,
monthly accruals, accretion, premium amortization, and
principal payments due were being handled in three different
ways: US Treasury and Municipal issues were maintained by an
"upstream" Correspondent bank's Service Center (at a nice
monthly fee); fixed-rate Mortgage Backed Security pools were
maintained on a PC via a Basic program furnished by a local
friendly broker; and adjustable-rate MBS pools were
"estimated" by hand!
SECURITY TRAK brings this "hodge-podge" system together where
one person can easily control the entire portfolio on a
single PC. The number of securities and their corresponding
transactions is limited only by your available disk space.
Several reports are available at any time, and End-of-Period
processing is automated (if you choose) on a monthly,
quarterly, or annual basis.
ABOUT THE AUTHOR
Everett G. Collins has been a career banker since 1969,
having handled all phases of Lending, Operations, and
Administration. He has developed and implemented custom
programs for his bank since 1982, when he discovered that PC
software for small to medium sized financial institutions was
simply NOT available ....... the software that WAS available
could not have easily been implemented by a community bank or
would have been "overkill" for the task at hand.
Other programs currently available through Banquette Software
written by this author are:
LOAN TRAK A stand-alone PC program to track loan principal
and interest accruals, payments, and legal &
other costs, charges, or credits. Most useful in
maintaining and reporting non-accrual accounts
and/or charged off accounts. (Most mainframes
will continue to accrue interest, but cannot
handle the associated "costs" incurred.) Reports
include Trial Balances and Transaction Histories.
OREO TRAK A stand-alone PC program to track Other Real
Estate Owned (foreclosed properties). In
addition to maintaining book value and market
value, the program tracks ALL income and expense
attributable to each property. "Breakeven" and
"Total Recovery" amounts are computed and always
available for 999 properties with up to 99 units
for EACH property. Reports include Property
Status (summary), Net Income/Expense for each
property, Rent Report, and OREO Expense Report
(for your Board of Directors).
SECURITY TRAK - 2
GETTING STARTED
SECURITY TRAK requires an IBM XT/AT or compatible with 512K
of RAM and at least two floppy disk drives or a hard disk.
An IBM/Epson (or compatible) printer is needed for reports.
MAKE A BACKUP COPY OF YOUR PROGRAM/DATA DISK AND STORE IT IN
A SAFE PLACE!
HARD DISK: Log onto the directory where you installed the
program and data files during the extraction
process (eg: CD\SECTRAK), and type the following
at the DOS prompt:
SECTRAK
FLOPPY DISK: Copy the data (and index) files to a blank
formatted disk in Drive B. (With your 'extracted'
program/data file disk in Drive A, type COPY *.DBF
B: , then type COPY *.NTX B:) With your program
disk in Drive A and your data file disk in Drive
B, type
SECTRAK B
at the DOS prompt. This will tell the program to
look for the data files on Drive B.
The program will load itself, display a title screen, open
all related files, and then display the MAIN MENU bar at the
top of the screen. All menus may be navigated by using the
arrow keys, or simply press the first letter of the menu
selection you want. From the top level of the Main Menu,
drop-down windows are activated for specific actions, and
pressing ESC will always take you back one level in the menu
system. (CAREFUL ... pressing ESC at the TOP level will
close all files and exit the program.) Pressing F10 will
return you to the TOP level, no matter what level of the Menu
you're currently in.
SECURITY TRAK - 3
MAIN MENU - TOP LEVEL
The Top Level of the Main Menu offers you the following six
choices:
[] SECURITIES This is your MASTER security
information file, which contains the
security type, description, coupon,
maturity date, certificate amount,
original & current face value, current
market value, original and current
book value, purchase & sale dates,
original & remaining discount/
(premium), and current gain/(loss)
based on book value. From this
selection, you can:
ADD new securities
EDIT/VIEW existing securities
FIND a specific security
RE-INDEX the security file
QUIT the program
[] TRANSACTIONS This is your TRANSACTION file for the
securities contained in your MASTER
file (above). NOTE: A security must
be added to the MASTER file before you
will be allowed to enter any
transactions for that security. From
this selection you can:
ADD new transactions
EDIT/VIEW existing transactions
FIND a transaction for a specific
security
RE-INDEX the transaction file
[] LIST RECORDS This selection will list all of the
Securities presently on file, along
with their Pool/Key No. and maturity
date to either the screen or printer.
(You can also list the records to the
screen at any time you are EDITing
records.)
[] REPORTS This selection will allow you to
generate the following reports:
SECURITY TRANSACTION history
PORTFOLIO RECAP (summary)
CASH FLOW REPORT (Quarterly/YTD)
PLEDGED SECURITIES report
END OF MONTH ENTRIES report
[] UTILITIES This selection will allow you to:
DELETE RECORDS
UNDELETE RECORDS
PACK (compress) THE FILES
REBUILD ALL INDEXES
SHOW (display) DELETED RECORDS
SECURITY TRAK - 4
HIDE DELETED RECORDS
EDIT 'HIDDEN' FIELDS
[] EOP This selection performs End-of-Period
processing for the end of a month,
quarter, or year and prints reports
associated with the specific closing.
-------------------------------------------------------------
SECURITIES
-------------------------------------------------------------
SECURITIES - ADD/EDIT RECORDS
Shown below is the data entry screen for ADDing or EDITing
your MASTER security information file:
╔═══════════════════════════════════════════════════════════╗
║ SECURITIES INFORMATION FILE ║
╚═══════════════════════════════════════════════════════════╝
Type: Pool/Key No: 0 CUSIP:
Description: Maturity Date:
Certificate Amt: Rating: Purchase Date:
Purch Par/Face: Sale Date:
Current Face Amt: Last Update:
Current Mkt Val:
Orig Book Value: Original Disc/(Prem):
Curr Book Value: Remaining Disc/(Prem):
Interest Basis: Held at:
Coupon Rate: SK Receipt:
Accrued Interest: Pledged to:
Prin Rec'd - YTD: Pledged Amt:
Int Rec'd - YTD: ┌───────────┐
════════════════════════════════════════════════╡Gain/(Loss)│
* Type: TBILL∙TBOND∙TNOTE∙TSTRP∙MUNI∙MBS∙AGNCY│ │
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀└───────────┘
(If you got this screen and didn't want to be here, press
CTRL-W or ESC to escape back to the Main Menu.)
The "acceptable" TYPEs of securities are displayed at the
bottom of the screen. If one of these TYPEs are entered in
the TYPE field, the program will be able to automatically
compute your monthly or actual-days accrued interest, as well
as accretion of discount or amortization of premium. You MAY
enter a TYPE other than that shown, but you will have to
manually compute and enter your accruals/accretion on the
Transaction screen. (See Appendix "A" for a short primer on
the different types of securities)
The POOL/KEY NO. is VERY important! Once this "key" is
assigned to a security it CANNOT be changed! All future
FINDs and transactions will be linked to this security by
this number. (This field will accept both alpha and numeric
SECURITY TRAK - 5
input, so you can assign any combination of letters/numbers
that make sense to you.) SUGGESTION: Use the pool number
for mortgage backed securities (such as the one shown), and
the coupon rate for notes, bills, and bonds (as long as
another security doesn't bear the same rate). Once this
"key" is assigned to a security, the program will NOT let you
reassign it later to another security.
The security DESCRIPTION can contain anything you want (up to
25 characters) but please be consistent. For example, ALL US
Treasury issues should begin with "US TREAS" followed by
NOTE, BILL, etc. and anything else you would always want to
appear in the description field of your reports. Likewise,
MBS securities should always begin with "FNMA", "FHLMC", or
"GNMA". The important thing to remember is that the program
uses the first four characters of this DESCRIPTION field to
group categories together for subtotals in certain reports.
Inconsistency in describing your securities won't crash your
program, but it may produce undesirable groupings in your
reports.
The CERTIFICATE AMT is the amount of the original issue.
The PURCH PAR/FACE is the amount of principal still due and
unpaid at the time the security is purchased. These amounts
may differ on seasoned MBS pools and other issues that have
principal payments due PRIOR to the stated maturity date.
(See Appendix "A" for the different types of securities) This
amount is NOT current market value, nor should it include any
discount or premium involved in your total purchase price.
The RATING is the rating assigned to this particular issue.
All US Treasuries and government guaranteed issues are rated
AAA. Call your broker if you're unsure about this. This is
an "information" field only, and may be left blank.
The CURRENT FACE AMT will be the same as the PURCH PAR/FACE
for a new security just purchased, but MAY differ if you are
adding an existing security which you have held. For MBS
issues contact your broker and obtain the current "factor"
for your pool; then multiply the CERTIFICATE AMT (eg:
$10000000.00) by this factor (eg: .9692876) to obtain the
CURRENT FACE AMT (eg: $969287.60). For most other issues,
this amount will be the same as the CERTIFICATE AMT.
The CURRENT MKT VAL (Current Market Value) is obtained by
multiplying the CURRENT FACE AMT by the current market price.
(Remember, a market price of 99.125 is actually .99125 in the
calculation. "PAR" is 100, or 1.00000; "Par + 1/2" is 1.0050,
etc.)
ORIG BOOK VALUE is the amount you initially "booked" this
security for, INCLUDING any discount or premium. (eg: If you
purchased a $1000000.00 UST Note @ 99.00, your original book
amount would be $990,000.00)
CURRENT BOOK VALUE is the amount at which you are currently
carrying an existing security on your books. (I assume you've
been accreting any discount or amortizing any premium.)
If this amount is the same as your ORIG BOOK VALUE you can
SECURITY TRAK - 6
accept the 0 default and the program will fill this field in
when data entry is complete.
NOTE: CURRENT FACE AMT, CURRENT MKT VAL, and CURRENT BOOK
VALUE will be automatically updated by the program as
you enter transactions. More on this later.
INTEREST BASIS is the yearly "days" on which interest is
accrued. If you have entered one of the recognizable TYPEs
at the top of the screen, this field will be automatically
filled in when you complete all data entry. If your TYPE is
NOT one that is shown, you will have to fill this field in
manually.
COUPON RATE is the rate of interest being paid on the issue.
If you have an adjustable rate security (such as a FNMA ARM)
it should be the CURRENT rate (call your broker!). If a
different rate is posted on a Transaction entry, this field
will automatically be updated to reflect that rate.
ACCRUED INTEREST (due), PRIN REC'D - YTD, and INT REC'D - YTD
are "calculated" fields, but you must enter these amounts for
any existing securities you presently hold if you expect your
accruals to balance to your books and your Cash Flow reports
to be accurate. Remember, ACCRUED INT is interest due but
unpaid, while PRIN REC'D - YTD and INT REC'D - YTD are actual
monies received (not due) DURING THE CURRENT CALENDAR YEAR.
These last two fields will be zeroed out when you process
your year-end closing (EOP on the main menu).
MATURITY DATE and PURCHASE DATE are self-explanatory.
SALE DATE should be left blank until such time as you sell
the security. Then enter it by EDITing the record.
LAST UPDATE is the date to which accrued interest has been
computed. All future interest accruals and
accretion/amortization will be based on this date. For a new
purchase it will be the same as your purchase (or settlement)
date. For existing securities it will be the last date
interest was accrued THROUGH. This field will be
automatically updated as you enter Transactions containing an
interest ACCRUAL (but not for payments received or
adjustments).
ORIGINAL DISC/(PREM) will be computed at the end of data
entry when ADDing a security based on the amounts entered for
PURCH PAR/FACE and ORIG BOOK VALUE. If this amount is not
correct, check the amounts entered in those fields. If those
entries are correct, and this amount still doesn't match your
records, don't despair! Save the record as is, Quit to the
MAIN MENU, and immediately EDIT this field to contain the
amount you want.
REMAINING DISC/(PREM) should be the same as ORIGINAL
DISC/(PREM) for a new security just purchased (leave this
amount as 0 and the program will fill it in), but will be
different for an existing security you have held for more
SECURITY TRAK - 7
than a month and you should enter your remaining discount/
(premium) as reflected on your books. This field is updated
automatically by the program whenever interest accruals and
accretion/amortization is computed or entered in the
Transaction file. The remaining discount/(premium) SHOULD be
an amount equal to CURRENT FACE AMT - CURR BOOK VALUE.
REMEMBER: Premiums are entered (and displayed) as NEGATIVE
amounts, while discounts are positive numbers.
HELD AT, SK (Safe-Keeping) RECEIPT, PLEDGED TO, AND PLEDGED
AMT are self explanatory.
GAIN/(LOSS) is a calculated field (CURRENT MKT VAL - CURR
BOOK VALUE), and cannot be accessed by the user.
After data entry is complete (by completing all fields or
pressing the PgDn key anywhere on the screen) you will be
asked if you want to SAVE THIS RECORD? (Y/N) Any response
other than "Y" will discard all data entered and return you
to the MAIN MENU. You MUST press ENTER to record your
response.
When EDITing existing records, you will be presented with the
same data entry screen but your cursor will be positioned on
a menu bar at the bottom of the screen:
┌───────────────────────────────────────────────────────────┐
│Next Prev Top Bot Quit Edit Set List Find Help Del 2/10│
└───────────────────────────────────────────────────────────┘
Use the right/left arrow keys to select (followed by enter),
or just press the first letter of the menu choice to execute
the command. The numbers on the right side of this line
indicate the record number being displayed followed by the
total number of records in this file (eg: The number above
indicates that we are looking at record number 2 of 10
records in this file). The menu choices are:
N - Go to the next record
P - Go to the previous record
T - Go to the first record
B - Go to the last record
Q - Return to the main menu
E - Change the data in an existing record
S - Set a filter. This will only display records
which meet some criteria you specified.
L - List records to screen by Security Desc and Pool/Key No
F - Find a record by using the active index
H - Help screen (this listing)
D - Delete/Recall records (NOT FUNCTIONAL in this program)
To EDIT an existing record, locate that record by using Next,
Prev, Top, Bot, or Find commands to display it on your
screen. After the record you want is displayed, press E (or
cursor to the Edit choice). All accessible fields will be
shown in inverse, and you will notice that you CANNOT access
SECURITY TRAK - 8
the POOL/KEY NO field. (Remember .... this field cannot be
changed once the key is assigned when adding a new record.)
After EDITing the record (you can press PgDn to bypass
remaining fields that will not change), you will be asked if
you want to SAVE THIS RECORD? (Y/N). Any response but "Y"
(the default) will discard your changes and return you to the
"display" mode.
PLEASE EXERCISE CAUTION IN EDITING YOUR FILES ..... any
adjustments to "calculated" fields may (and probably WILL)
create an out-of-balance condition with your Transaction
file. It may be better to enter a new Transaction for this
security using the "Due" fields or "Adj" (adjustment) fields
(See section on TRANSACTIONS, below).
SECURITIES - FIND an Existing Record
This selection on the Main Menu will "jump" you right to the
FIND mode on your EDIT screen. Enter the POOL/KEY NO of the
security you're seeking, and the program will attempt to
locate it. If it can't, an error message will appear at the
bottom of the screen.
SECURITIES - RE-INDEX the file
This selection will "rebuild" the indexes (there are 3)
associated with the Master Security file. While the indexes
are automatically maintained and updated by the program,
there may be occasions where they will be "damaged" (such as
in a power outage). At any time you feel your indexes are
incorrect (such as being unable to FIND a record that you
KNOW is in your file) use this selection. If you want to
rebuild ALL the indexes for ALL the files used by this
program, use the UTILITIES selection on the top level of the
Main Menu.
SECURITY TRAK - 9
-------------------------------------------------------------
TRANSACTIONS
-------------------------------------------------------------
TRANSACTIONS - ADD or EDIT/VIEW
Show below is the data entry screen for ADDing or EDITing
security TRANSACTIONS:
╔═══════════════════════════════╗
║ SECURITIES TRANSACTION FILE ║ Pool / Key No: 273778
╚═══════════════════════════════╝ Entry Date: 06/19/1990
┌────────────────────────────────┬──────────────────────────┐
│ Transaction Descr: │ Transaction Date: │
├────────────────────────────────┤ Current factor: │
│ Security: FHLMC 273778 │ Current/New rate: │
│ │ Current Mkt Price: │
│ Coupon:8.5000 Maturity:06/01/16├──────────┬───────────────┤
│ │ Principal Due: 0.00│
│ Prev Prin: Book: │ Interest Due: 0.00│
│ │ Accretion/(Amort): 0.00│
│ Accr Int : ├──────────────────────────┤
│ │ Principal Rec'd: 0.00│
│ Remaining Disc/(Prem): │ Interest Rec'd: 0.00│
│ │ Principal Adj: 0.00│
│ Last Update: 05/31/1990 │ Interest Adj: 0.00│
├────────────────────────────────┴──────────────────────────┤
│ Ending Prin: 0.00 Ending Book: 0.00 Mkt Value: 0.00│
└───────────────────────────────────────────────────────────┘
(Screen compressed to fit within margins)
The left side middle-section of this screen contains
information contained in the associated Master Security
record. (This will be saved with your TRANSACTION record so
you will always have an "audit trail" of the old AND new
information.) The ENTRY DATE is automatically filled in with
the current system date and cannot be accessed by the user.
The fields on the bottom line are "calculated" fields and
cannot be accessed by the user.
When ADDing a new record you must first enter a POOL/KEY NO,
which the program will then validate as an existing security
in the Master file. If no such POOL/KEY NO is found you will
receive an error message at the bottom of the screen and you
will return to the Main Menu.
If the POOL/KEY NO entered is valid, you will be allowed to
enter data in the fields shown in inverse.
Any description you prefer may be entered in the TRANSACTION
DESCR field, but in order for the program to recognize and
print your end-of-month entries for you (accruals/accretion/
amortization) the first three characters for this type of
transaction MUST be "EOM." (If you use EOP for your monthly
accruals/accretion this field will automatically be filled in
SECURITY TRAK - 10
with "EOM ACCRUALS.") For your convenience, the following
function keys have been programed as "speed keys" to type
this entry for you:
F2 - EOM ACCRUALS
F3 - PAYMENT REC'D
F4 - ADJUSTMENT TO BALANCE
Press ENTER to accept the "speed key" description, or type
anything else you'd like after it. (CTRL-Y will always blank
the field from the cursor position to the end of line.)
The TRANSACTION DATE should be the effective date of this
transaction. If you have asked the program to compute your
monthly accruals/accretion (by using EOP or answering
"Monthly" to the accrual question (see below)) the program
will change this date to the last day of the month. YOU MUST
ENTER A VALID DATE FOR THE MONTH WITH WHICH YOU ARE WORKING!
Anytime interest is accrued the TRANSACTION DATE will also be
written to the associated Master security record as the LAST
UPDATE.
The CURRENT FACTOR for all issues which have no principal
repayment until maturity will ALWAYS be 1.00000000. Issues
with principal repayment schedules require that the current
factor be entered in this field (call your broker for the
current factor and market price). (The program will retrieve
the last factor recorded for this issue and enter it in this
field as a default.)
The CURRENT/NEW RATE will never change on a fixed-rate issue,
and the program will retrieve the last recorded rate as the
default for this field. If you are dealing with an
adjustable rate issue, ask your broker for the current rate
and enter it in this field. Please realize that you will
always be "behind" on posting adjustable rates, and your
accruals will probably NEVER be exactly right. Just do an
INTEREST ADJustment when the payment is received, and adjust
your books accordingly.
The CURRENT MKT PRICE is the "dollar" price currently being
offered for the particular issue (eg: 99.125 or 101.25). If
this field is 0.0000 the program will try to compute the
market price from the last market value recorded in the
associated Master Security record.
PRINCIPAL DUE, INTEREST DUE, and ACCRETION/(AMORT) will be
automatically computed for you based on the CURRENT FACTOR
entered above and your positive response to the Accrual
question (see below). If you are trying to adjust your
accrued interest or principal balance, you should use the
PRINCIPAL ADJ and/or INTEREST ADJ fields below. If you
should need to adjust your REMAINING DISC/(PREM) in your
Master Security record, enter your adjusting figure in the
ACCRETION/(AMORT) field (pay attention to positive/negative
numbers) or EDIT the Master Security record directly.
REMEMBER ... any data entered in these fields will be
overwritten if you ask the program to automatically compute
your accruals/accretion!
SECURITY TRAK - 11
PLEASE NOTE: It IS possible to have a "negative accretion."
In the case of a graduated payment mortgage pool, for
example, you will have negative principal due (during the
first five years) on a monthly basis. If this pool was
purchased at a discount, you will also have negative
accretion during this period. (This would also hold true for
a pool purchased at a premium - you would experience
"positive" amortization.) The program will recognize these
GPMs, and allocate the negative accretion or positive
amortization properly. (This action will "reverse" itself
once the pool reaches a positive principal reduction status.)
PRINCIPAL REC'D and INTEREST REC'D are self explanatory and
should be used only to record payments ACTUALLY received
(paid).
PRINCIPAL ADJ and INTEREST ADJ fields should be used to
record (or adjust) any differences between Principal/Interest
due and the actual payment received. (Such as those pennies
that are off because of "rounding" errors). For example,
suppose that last month you had an interest accrual of
$1739.57. When you receive your payment this month the
interest paid is only $1739.56. You would then post 1739.56
in the INTEREST REC'D field, and post -0.01 in the INTEREST
ADJ field. This "adjustment" will straighten out your
ACCRUED INTEREST field in the associated Master Security
record.
THE LAST SEVEN FIELDS DISCUSSED ABOVE ARE USED TO UPDATE THE
FOLLOWING ASSOCIATED MASTER SECURITY RECORD FIELDS:
CURRENT FACE AMT
CURRENT MKT VAL
CURR BOOK VALUE
ACCRUED INTEREST
PRIN REC'D - YTD
INT REC'D - YTD
REMAINING DISC/(PREM)
Please be careful when entering data, and "eyeball" the
calculated fields on the bottom line for accuracy prior to
saving the transaction.
"UN-EDITABLE" TRANSACTION FIELDS:
When EDITing an existing Transaction record you will be
presented with the same Menu Bar on the bottom line of the
screen as discussed in the SECURITIES file above. "Find" the
record to be edited in the same way, and choose EDIT (or
press E). You will notice that the POOL/KEY NO and
TRANSACTION DATE fields are NOT in inverse and user access is
not permitted. The POOL/KEY NO "shutout" should now be
obvious, but the TRANSACTION DATE may not be.
Remember that ALL accruals/accretion/amortization are
computed using the (previous) LAST UPDATE and the (effective)
TRANSACTION DATE? If this field were EDITed from 5/31/90 to
SECURITY TRAK - 12
read 5/30/90, your file could end up with a "hole" in the
accruals/accretion, unless it was the LAST transaction
posted.
For example:
Transaction 1 accrued interest thru 5/31/90
Transaction 2 accrued interest from 6/1/90 thru 6/30/90
Transaction 3 accrued interest from 7/1/90 thru 7/31/90
On 8/3/90, you decide to EDIT transaction 1 and inadvertently
change the transaction date to read 5/30/90. If you re-
compute the interest what happens to the ONE DAY (May 31st)?
Oops!!!!!
For this very reason I have also built another "safety-valve"
into the EDITing process. UNLESS the Transaction being
edited bears the SAME date as the LAST UPDATE (recorded in
the associated Master Security record) no updates will be
made to the Master Security record for the CURRENT FACE AMT,
CURR BOOK VALUE, ACCRUED INTEREST, LAST UPDATE, or COUPON
RATE. You will receive a WARNING whenever this situation is
imminent in an EDIT mode.
TRANSACTIONS - FIND an Existing Record
This selection on the Main Menu will "jump" you right to the
FIND mode on your EDIT screen. Enter the POOL/KEY NO of the
security you're seeking, and the program will attempt to
locate it. If it can't, an error message will appear at the
bottom of the screen.
TRANSACTIONS - RE-INDEX the file
This selection will "rebuild" the indexes (there are 2)
associated with the Transaction file. While the indexes are
automatically maintained and updated by the program, there
may be occasions where they will be "damaged" (such as in a
power outage). At any time you feel your indexes are
incorrect (such as being unable to FIND a record that you
KNOW is in your file) use this selection. If you want to
rebuild ALL the indexes for ALL the files used by this
program, use the UTILITIES selection on the top level of the
Main Menu.
SECURITY TRAK - 13
S E T T I N G F I L T E R S
A menu choice on both EDIT screens for Securities and
Transactions is to SET a filter. A "filter" is nothing more
than a mask that hides all the records that do not meet a
specified criteria. In other words, you only see what you
WANT to see.
For both files you can set a filter for the POOL/KEY NO or a
number of POOL/KEY NOs. (This is useful for viewing all the
transactions for ONE security, and no others.)
In addition, you can set a filter for the security
DESCRIPTION (SECURITIES file) or the TRANSACTION DATE
(TRANSACTIONS file). Partial DESCRIPTIONs should still find
a match in the Securities file (good for finding all your
GNMAs, for example).
Follow the prompts ..... you will see that the program will
allow you to set multiple filter conditions, but let's not go
crazy with this!
If the filter fails (no match found) you will be told.
When a filter is "in effect" you will always see "FILT"
blinking on the bottom line of the EDIT screen as a reminder.
To erase a filter that has been set (and return to displaying
ALL your records), just choose SET again and immediately
press ESC. You will return to the EDIT screen and the
blinking "FILT" will be gone.
-------------------------------------------------------------
REPORTS
-------------------------------------------------------------
REPORTS - SECURITY TRANSACTIONS
This selection will allow you to print or view a Transaction
History Report for the Security whose POOL/KEY NO you enter
at the prompt. The history will list ALL transactions from
the date the security was first added to the file thru the
last transaction posted. A "header" will precede the
transactions summarizing the most important data contained in
the Master Security record.
SECURITY TRAK - 14
REPORTS - PORTFOLIO RECAP
This report can only be printed, since it is 136 columns
wide. The report "summarizes" your entire portfolio (with
totals) as of the date printed . It contains (left to
right):
Issue description
Coupon rate
Maturity date
Current par (face)
Current book value
Market value
Gain/(Loss)
Accrued interest
Remaining discount/(premium)
Held at
Pledged to
REPORTS - CASH FLOW REPORT
This report is generated to simplify the requirements of your
CPAs in preparing your audited statements (Your
Cashier/Treasurer will love you also!) The report lists each
security in your portfolio along with the principal &
interest payment ACTUALLY RECEIVED during the first, second,
third, and fourth quarters, as well as year-to-date.
Naturally, totals are included for all.
REPORTS - PLEDGED SECURITIES
All examiners are the same .... they want all your
information as of the date THEY walked in! NOT from last
month's Board Report! Sooo .... no problem! Just hit the
right key and you'll have a printed report to hand them
showing what securities are pledged, to whom, and in what
amounts. (Hope you've kept this info up to date! See
SECURITIES on Main Menu.)
REPORTS - END OF MONTH ENTRIES
This report will print a summary of principal due, interest
accrued, discount accretion, and premium amortization for the
month requested. Subtotals will be generated by security
"groups" (the first four characters of the security
DESCRIPTION), along with totals for the entire portfolio.
In searching for the applicable TRANSACTION records, the
program will first look for the month requested and then look
for "EOM" as the first three characters of the TRANSACTION
DESCR.
I find this report most helpful in preparing my end-of-month
"book" entries.
SECURITY TRAK - 15
-------------------------------------------------------------
UTILITIES
-------------------------------------------------------------
UTILITIES - DELETE RECORDS
While you may have noticed that you have a DELete selection
on your EDIT screen menu, you may not realize that it is not
functional in the EDIT mode. The reasoning for this is
basically the same as discussed in "un-editable fields"
above. Deleting a single Transaction record could leave a
"hole" in your accruals/accretion, and create an out-of-
balance condition. On the other hand, deleting a Master
Security file could leave a number of Transaction records
hanging out there without a "parent." Therefore, you MUST
use this selection to delete ANY of your records.
NOTE: If you intend to produce CASH FLOW REPORTS you should
NOT delete your records until you have closed out the year.
Forewarned is forearmed!
Here's the "kicker" - this selection will prompt you for a
POOL/KEY NO to be deleted, verify that the key exits, ask
you to verify this action, and then proceed to delete ALL
records in BOTH files bearing the POOL/KEY NO entered. In
this way the Master Security record (parent) and ALL it's
children (Transactions) are deleted in one step.
Whoops! What if you made a mistake and entered the wrong
POOL/KEY NO? No sweat .... these records are only MARKED as
deleted (until you PACK the file - more on that later). You
can restore them ..... read on.
UTILITIES - UNDELETE MARKED RECORDS
This selection will restore those records marked as "Deleted"
by mistake with a slip of the hand or a temporary blackout.
Like the preceding selection, you must enter the POOL/KEY NO
and confirm that you REALLY DO want to restore these records
to their "living" state. Voila! .... they're back (like
Poltergeists). BUT ..... if you have PACKed your files since
marking these records as deleted you will have to rush out to
the closest "attitude adjustment hour" and drown your sorrows
- they're gone!
UTILITIES - PACK THE FILES
Exercise care when using this option - ALL records currently
"marked" as deleted will be erased forever! I would suggest
that you use this option ONLY after you have closed out the
year or when you KNOW you have really messed up a file and
intend to reenter your data correctly.
SECURITY TRAK - 16
On the "good" side, this will clear all unwanted and unneeded
files and reduce the disk space required to store your files.
UTILITIES - REBUILD ALL INDEXES
This selection performs the same action that the RE-INDEX
option does on other menu choices, except that it operates on
ALL your files at once.
UTILITIES - SHOW or HIDE DELETED RECORDS
As previously mentioned, "deleted" records are only marked as
deleted and still exist in your files (until the file is
PACKed). Unless you choose to HIDE these deleted records,
they will still appear on your EDIT screens (with a blinking
DEL on the bottom line to remind you) and in ALL your
reports. If you choose to HIDE these records, the program
will treat them as if they don't exist at all.
The default at startup is SHOW DELETED RECORDS. The
currently ACTIVE choice will be indicated by an asterisk(*)
following the option.
UTILITIES - EDIT 'HIDDEN' FIELDS
In order to produce the Cash Flow Reports, there several
fields that are part of your Securities file but never appear
on any of your screens. These fields are all used by the
program, but require no user input since they are all
"calculated" and updated automatically in the ADD and EDIT
modes. This menu option is included ONLY to give you the
ability to "repair" your mistakes or any damage to the file.
Otherwise, you should NOT tamper with this data.
-------------------------------------------------------------
EOP (End of Period processing)
-------------------------------------------------------------
EOP - END OF MONTH
From this option you will be asked to enter the month-end
date. If it is not a correct date (such as 05/30/90 instead
of 05/31/90) your entry will not be accepted. Then the
program will automatically accrue your monthly interest,
accretion, and amortization for each security in your file -
you need only enter the current factor, current rate, and
current market price.
When the file update is complete, a PORTFOLIO RECAP report
SECURITY TRAK - 17
and END OF MONTH ENTRIES report will be automatically
printed.
EOP - END OF QUARTER
From this option you will be asked to enter the month-end
date. If it is not a correct date (such as 05/30/90 instead
of 05/31/90) your entry will not be accepted. Then the
program will automatically accrue your monthly interest,
accretion, and amortization for each security in your file -
you need only enter the current factor, current rate, and
current market price.
When the file update is complete, a CASH FLOW report,
PLEDGED SECURITIES report, PORTFOLIO RECAP report, and END OF
MONTH ENTRIES report will be automatically printed.
EOP - END OF YEAR
From this option you will be asked to enter the year-end
date. The program will validate the date and then
automatically accrue your monthly interest, accretion, and
amortization for each security in your file - you need only
enter the current factor, current rate, and current market
price.
When the file update is complete, a CASH FLOW report,
PLEDGED SECURITIES report, PORTFOLIO RECAP report, and END OF
MONTH ENTRIES report will be automatically printed.
Finally, ALL the Interest Paid YTD/QTD and Principal Paid
YTD/QTD fields will be ZEROED out for the start of the New
Year. YOU MUST CLOSE OUT EACH YEAR USING THIS OPTION OR YOUR
CASH FLOW REPORTS FOR THE FOLLOWING YEAR WILL BE INACCURATE!
************************************************************
Please note: IF the program "detects" that the period from
the LAST UPDATE to the current month-end or year-end date is
less or greater than one complete month, it will
automatically compute that accrual/accretion on an "actual-
days-elapsed" basis. You will be notified when this is
happening.
SECURITY TRAK - 18
********** APPENDIX A **********
INTEREST INTEREST
ISSUE MATURITY FORM PAID COMPUTED
------------------- -------------- ------- -------- --------
US TREASURY SECURITIES
US Treas Bills 3 mos - 1 year Disc Maturity Act/360
US Treas Notes 2 yrs - 10 yrs IntBear Semi-Ann Act/365
US Treas Bonds > 10 yrs IntBear Semi-Ann Act/365
US Treas Strips 3 mos - 30 yrs Disc Maturity Act/365
FEDERAL AGENCY SECURITIES
FFCB 5M/5M 3/6/12 months IntBear Varies 30/360
FFCB 1M/1M 13 mos -10 yrs IntBear Semi-Ann 30/360
FHLB 3 mos - 10 yrs IntBear Varies 30/360
FNMA 6 mos - 30 yrs IntBear Semi-Ann 30/360
MORTGAGE-BACKED SECURITIES
GNMA 15 & 30 yrs IntBear Monthly 30/360
FNMA 15 & 30 yrs IntBear Monthly 30/360
FHLMC 15 & 30 yrs IntBear Monthly 30/360
CMO/REMIC Varies IntBear Mth/Qtr 30/360
MUNICIPAL SECURITIES
BAN, RAN, TAN 3 mos - 1 yr IntBear Maturity 30/360
Tax Exempt Bonds 1 to 30 yrs IntBear Semi-Ann 30/360
Taxable Bonds 1 to 30 yrs IntBear Semi-Ann 30/360
-------------------------------------------------------------
APPENDIX B
CAN I DIRECTLY EDIT MY FILES?
-----------------------------
Yes! If you really need to and know what you're doing. All
you need is access to dBASE III (or a "clone"), since your
SECMAST.DBF (Master Securities) and SECTRAN.DBF (Transaction)
files are dBASE compatible (although the index files are
not). Just remember, any manual changes you make to your
files could create an "out-of-balance" condition. Plan your
editing session well if you choose to do this, and make
backups of your files as a precaution.
You should always RE-INDEX your files AFTER any manual
editing .... just use UTILITIES on your Main Menu.
SECURITY TRAK - 19
REGISTRATION FORM
-----------------
PROGRAM: Security Trak Version 1.0
BANQUETTE SOFTWARE
2300 EDENBORN #147 II
METAIRIE, LA 70001
Name: _______________________________________________
Company: _______________________________________________
Address: _______________________________________________
_______________________________________________
City, State, Zip: ______________________________________
Payment enclosed: $______________ ($39.00 for full
registration,
(No cash, please) current version,
and user support.)
Where did you hear about this program? _____________________
Comments/Questions: ________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
(Please write to the above address for pricing on Site
licenses.)
T H A N K Y O U F O R R E G I S T E R I N G !
SECURITY TRAK - 20