Basic

What the Vancouver Stock Exchange Can Teach Us About Rounding Numbers in VBA

So you think you know how to round decimals. Do you, though? There may be more to it than you think.

Mike Wolfe

Aug 18, 2021 • 4 min read

DISCLAIMER: I have never run my own stock exchange (though it might be an interesting challenge). I do not work for the SEC. Heck, I don't even watch the SEC. Nothing in this article constitutes legal advice for any particular purpose, including launching and running one's own stock exchange. Also, VBA may not be the best language choice for such an endeavor.

Before we get to the Vancouver Stock Exchange, let's provide a bit of context.

Bankers Rounding in VBA

The built-in VBA Round function uses what's known as bankers rounding (a.k.a., "round half to even"). Here's a note from the help page:

This VBA function returns something commonly referred to as bankers rounding. So be careful before using this function. For more predictable results, use Worksheet Round functions in Excel VBA.

Not to be too pedantic, but I take issue with the word choice above. The results of bankers rounding are very much predictable. The output of the function is perfectly reproducible given the same input. A better phrase might be, "For more familiar results..."

Arithmetic Rounding - Something More Familiar

Arithmetic rounding, which most of us learned in school, says that we round any decimal equal to or greater than 0.5 up to the next whole number (or down if the number is negative). The problem is that this approach skews our rounded numbers high for positive numbers and low for negative numbers.

Bankers rounding addresses this skew by "taking turns" rounding up or down if the decimal falls exactly in the middle at 0.5. If the digit being rounded is a 5, you look to the digit to its left to decide how to handle the rounding.

If the digit to the left is an odd number, you round up; if the digit to the right is an even number, you round down.

The far-right column makes it clear why bankers rounding is often referred to as "**round half to even**."

It's important to note that no particular rounding approach is objectively "better" than another. The important thing is knowing that such differences exist and being aware of how they might affect your calculations. Especially if you run your own stock exchange.

The Most Important Thing to Know Before Starting Your Own Stock Exchange

Get the rounding right:

In January 1982 the [Vancouver Stock Exchange] index was initialized at 1000 and subsequently updated and truncated to three decimal places on each trade. Such a thing was done about 3000 times each day. The accumulated truncations led to an erroneous loss of around 25 points per month. Over the weekend of November 25–28, 1983, the error was corrected, raising the value of the index from its Friday closing figure of 524.811 to 1098.892

^{NOTE: There are probably other details to work out when starting one's own stock exchange.}