Case Study: VBA Implementation of a Bond Ladder

This is a short post showcasing some of the VBA procedures, classes, and coding techniques in last month’s bond ladder financial model. The model relies heavily on VBA, using it to manage displays, calculations, and data storage.

Overview

Last month, I posted a new financial model presenting a bond ladder portfolio. The model calculates a portfolio’s average weighted yield, trends, and income.

This post will look at the various VBA techniques used to create a user-friendly interface and handle complex calculations.

Financial Model File

You can download the financial model directly through this link. Macros must be enabled upon opening.

VBA Design Elements

While the most common elements of design do not have much to do with VBA—colors, fonts, spacing, etc.—VBA can play a major role in handling dynamic design, responsiveness, and the overall user experience.

Navigation

The financial model uses a VBA-based navigation feature, eliminating the need to use spreadsheet tabs. Users often feel intimidated viewing multiple tab workbooks and may not necessarily know where to start. By using a custom tab feature, users will be more familiar with navigation. Likewise, the approach implemented in this financial model provides intuitive navigation and placement cues (the tab format changes for the active page) for the user.

VBA based navigation.

This VBA approach to navigation permits full customization. In the bond ladder model, the bottom border line will disappear for the active tab, mimicking a file-folder appearance of the active tab being in front of the other tabs. Within the model, multiple procedures manage the navigation, starting with a Worksheet_SelectionChange event procedure to call a subroutine unique to the tab pressed. For example, the View_Dashboard_Detail subroutine in the Interface_Operations module will be called when a user clicks on the “Dashboard” tab.

You can read more about VBA navigation techniques here.

Custom Listboxes

Excel does offer listbox ActiveX and Form Controls, but these are feature-limited and contain numerous glitches. One way around this is to build a custom listbox using a scrollbar ActiveX Control. The custom VBA listbox solution allows full customization of the listbox. In addition to its core functionality (scrolling through data), the listbox assigns alternating colors to rows, contains buttons, and can contain conditional formatting. Additionally, we do not run into any inconsistent size and alignment issues when viewing the financial model across many different monitor resolutions (this is a common issue with the ActiveX listbox).

VBA custom listbox solution.

The custom VBA listbox prints a section of a dataset on a spreadsheet. The section printed is reliant on the values returned from the scrollbar when it was clicked. This gives the appearance of a listbox window. The bulk of custom listbox operations are managed within the class module: Scrolltable.

You can read more about custom VBA listboxes here.

Modifying Chart Properties

The first chart on the dashboard (Portfolio Average YTM Over Time) presents a year-over-year comparison of the average YTM for the bond ladder. Given that the spread between years can be a very small amount, it might be difficult to see the magnitude of difference. The financial model uses VBA to change the properties of the charts Y axis, acting as a zoom-in feature.

A snapshot of the zoomed-out chart:

Snapshot of the zoomed-out chart.

And a snapshot of the zoomed-in chart (using the exact same data):

Snapshot of the zoomed-in chart.

While the first chart shows the 2017 and 2016 YTM compared, it’s difficult to see the difference between the two year as the large scale narrows the visual distance. The second chart shrinks the scale to give the viewer a clearer view of the comparison over the course of the year. The second image shows a narrowed y-axis, where it is adjusted to be 10% from the maximum and minimum values in the chart. The following procedure accomplishes both the zoom-in and zoom-out functions whenever a user clicks the “Zoom In” or “Zoom Out” radio buttons at the top-right of the chart.

VBA Classes and Calculations

In most cases, spreadsheet-level Excel functions provide everything required to support calculations in financial models. This includes basic arithmetic, calculating simple probabilities, and calculating aggregate data. However, as financial models grow more complex, you may begin to rely on a large number of volatile formulas (formulas which recalculate after an unrelated change in the spreadsheet) and massive datasets. The constant recalculations can make the model operate extremely slowly as more data is added.

One way around time-consuming formula recalculations is to have VBA to handle calculations. This has two main advantages:

  1. VBA can calculate data much more efficiently than spreadsheet-level calculations.
  2. VBA gives you full control over when calculations will process.

Storing Arrays in Classes

Given that we include a filter to calculate cash flow and yield over specific years, we need to store this data as an array within a class. Within the RungCollection collection class, this data is stored under the array properties: rungCollectionMonthlyIncome, rungCollectionMonthlyYield, rungCollectionPriorMonthlyIncome, and rungCollectionPriorMonthlyYield.

These arrays store monthly cumulative calculations derived from transaction data. Whenever a user switches the comparison years on the dashboard (using the form pictured below this paragraph), the RungCollection collection class cycles through all transaction data and runs the required calculation if a transaction meets various criteria.

Buttons in the bond ladder financial model can select primary and comparison years.

One alternative to this would be storing aggregate data for all years. However, given that the model can store an unlimited number of years worth of transaction data, this could potentially add a processing burden whenever a transaction occurs (new transactions impact aggregate data and require recalculations). In short, the arrays within the collection ensure us that only two years of data are recalculated, as opposed to 30 or 40.

You can read more about storing arrays as class properties here.

Collection Classes

The financial model relies heavily on collection classes to store transaction and rung data. Transactions are held within TransCollection for easy access to methods which sort, calculate totals, manage printing, and to support saving. Rungs are held within RungCollection for calculating aggregate bond and portfolio data.

You can read more about collection classes here.

Storing Properties in Collection Classes

Collection classes are known to have a generic set of properties and methods to add, remove, and count values. It’s also possible to encapsulate unique properties within collection classes. This may seem counter-intuitive, as a properties are known to store a specific detail/attribute of a single element (not multiple elements), but they can be used within collection classes to present aggregate data about the entire collection.

Within the financial model, properties are used within collection classes to calculate and present totals, averages, and present arrays about the entire portfolio or individual rungs.

You can read more about storing properties within collection classes here.

Making Object Data Persist After Spreadsheet Close

One of the major drawbacks of relying heavily on VBA classes is that the objects will terminate whenever a process is interrupted, or a spreadsheet is closed. To get around this, class data must be saved somewhere on a spreadsheet.

Within the Bond Ladder Financial Model, all custom object data is saved (printed) within the “Rungs” and “Transactions” sheets. Within the RungCollection and TransCollection collection classes, the Class_Initialize subroutines work to load all saved data into their respective collection classes when a worksheet is opened. Data is automatically saved to these pages whenever a transaction is created or a major change takes place.

You can read more about saving custom object data here.

Leave a Reply