Home PageWindows Mockup

File Discussion

All user data and parameters reside in a one Access file. A second file, info-helps.mdb, must reside in the same folder as the Excel workbook. It will get replaced with updates (it also contains the program's version history).

Updating the user's data file: Excel cannot add fields to an Access file, so with updates an Access macro will be included with ALTER TABLE statements and ADD COLUMN clauses. There will also be a macro that completely clears the database and restores it to its defaults.

Grid input will reside in Access Memo fields in a delimited format (commas for numbers, and stiles when text).

The file system has these forms (shown below):

The table of file information is shown first below. The VBA code gets everything it needs from this table. The file system could easily be used for another application.

Some tables have companion tables. The names are mutually exclusive between the tables, so one input field can work for both a single record and a group of records.

The Recs flag signifies that the table is record oriented. The only one here is the Equity information table. One row per ticker.

The NewDate flag adds a button to the Record Picker form that creates a new using for its name the results of Excel's Date function.

The Type column adds a column to the Record Picker form with the record's Type (for securities: Bond, PIK, MBS, MM, CF, Perp) and (for prepayments: SMM, CPR, PSA, AB,S HEP, MHP). Yield curves optionally has a Type column, if users are using the different input screens. Number is width of Type column on form in points.

The LockFlag signifies whether that table has BondCalc's internal record locking system turned on or not.

On the Parameter page there are checkboxes that control the user's interface with the file system (stored in the user's Registry):

When a form has sort buttons, the current sort order is stored in the user's Registry. Locations of moved forms are remembered, as are any forms that have had their height changed.

When record locking is turned on for the table, the user can only open a locked file read-only. There is a form on the Utilities menu to monitor the locks and unlock them.

Click on a row to see that UserForm.

AccessTableList Go to Bond Solver Go to Compares Go to Portfolio Report Writer Go to Report Format Groups Go to Securities Report Writer Go to Lists of Securities Report Formats Go to Equity Information Go to Floating Indexes Go to Foreign Exchange Rates Go to Import Securities Go to Indexes Go to IRR Calculator Go to Record Locking Go to Market Sector Labels Go to Multiple Imports Go to Parameters Go to Portfolios Go to Default Ratings Labels & Values Go to Ratings Go to Securities Go to Security Price Comparisons Go to Security Prices Go to SLG Table Go to Default Speed Table Coupons Go to Forecasted Prepayment Speeds Go to Spread Matrixes Go to Spread Sector Differentials Go to Update Securities Go to User Defined Labels Go to Version History Go to Yield Curves

There is a second Access database with information for BondCalc. It is expected to be in the same folder as the BondCalc WorkBook. An update of this database will be included with many program updates. It needs a holiday list.

Click on a row to see that UserForm.

AccessTableList2 Go to Country Code List Go to Currency Table Go to Help Files Go to State List Go to Version History

File System UserForms

Folder Picker

The major file systems, and some of the auxiliary files, have a Folder Picker form. These, in a one level system, give some of the functionality of Windows folders.

Folder Picker

File Number

Generates the file number needed by the following form. Only these listed databases have folders or records and can use this facility.

Folder ListNum

Folder Transfer

Lots of flexibility here. Selected folders and sorts are saved in the Registry.

Folder Transfer

Record Picker

The Record Picker can either edit the file, or transfer the name back to the calling form. When the file system has folders, the current folder is saved in the registry.

A checkbox on the Record Picker form gives users the choice of after editing or creating something, either returning to the picker form to select another (like before), or simply closing. When editing something, this picker form is always closed first. This allows the form to be opened for a different file system, and also in a shared environment it will get a refresh when returning to this form.

record-picker record-picker 2

Record Block Picker

Only the Equity database has blocks of records, instead of folders. The difference is when folders the user edits one record at a time. With the Record Block Picker, all records in the folder are edited simultaneously, one per row.

Folder Transfer

Managing Record Locking

BondCalc has a complete record locking system for all tables that have a number assigned (see table above). If a user tries to open a file that is locked, the user is told who is using the file, when locked, and then is allowed to open read-only. Or go to the Utilities menu and use this form to unlock. The second form listing the lockable files is dynamically built from the tables.


Price Information Database

There is a global menu, and a menu inside the security. The one inside the security also links to the global one. The global menu can view inside a security. The retrievals from Access are displayed in ListBoxes, so one can sort on any column. To see the reports, click the four info buttons (the three on the first form, and the first on the second).

Database Contents Overview button Number of Records Reports button Graphs button Security Database Contents