Home Page → Windows 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):
- Folder Picker. To segregate securities, portfolios and compares, folders are simulated within the Access Tables, though they are only one level deep.
- Folder Number Selector. A menu to allow the user to select which file system the Folder Transfer is to work on.
- Folder Transfer. To move and copy files between or within folders.
- Record Picker. This will popup if the table of file information has a ListNum and this is called. It has different personalities. Sometimes it is only an editor. Other times it is a picker, where you can also edit. Or a picker only.
- Record Block Picker. An alternative only used by the Equity database. It has a field that selects out a block of records in a record oriented database.
- Record Locking Management. Complete system of record locking.
- Price Information Database. This database is paired with the securities database. Like before, Price Sweep contains: Settlement Date, Security Name, Worst End Date, Price, Yield to Maturity, Yield to Worst, and Spread to Worst. Any changes to the name or location of a security, is also changed in this database.
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):
- Check to ask for name upfront when creating new file
- Confirm before deleting files
- Include Type column on Yield Curve picker form
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.
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.
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.
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 Transfer
Lots of flexibility here. Selected folders and sorts are saved in the Registry.
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 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.
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).
Global
Security
