Bond Data Converter

U.S. Government Bonds and Notes quotes are not easily understood by excel. A quote’s fractional part is often separated by a colon or space and the denominator is assumed. For example 100:02 is the form given by the Wall Street Journal. From other sources it might appear as 100 02. In this quote the 02 is the numerator and the denominator (32) is assumed. To put back in general number form that excel can read the math is 100 + 02/32 which is 100.0625. The below code that I wrote is from
Bondconvert.xls. The code will convert all the data in columns B-E to number form if it has a space between the the two parts. The code can be adapted easily.
For example: to convert data with a colon instead of a space you would change the line y = Split(x," ") to y = Split(x,":")

In addition to the code based conversion, the function can also be used with live data. For example the function can be used in a formula like Bconver("120 01.5"). In that case "120 01.5" could be replaced with a dde link.

Option Explicit

Function Bconver(x As Variant)
Dim y As Variant
y = Split(x, " ")
If UBound(y) = 1 Then
Bconver = y(0) + y(1) / 32
Bconver = x
End If
End Function

Sub bondconvert()
Dim wsET As Worksheet
Dim lr As Long
Dim r As Range
Dim x As Variant
'Set wsET = ThisWorkbook.Worksheets("Data")
Set wsET = ThisWorkbook.ActiveSheet
With wsET
lr = .Cells(Rows.Count, "A").End(xlUp).Row
For Each r In .Range("B2:E" & lr)
r.Value = Bconver(r.Value)
Next r
End With
End Sub

', Copyright © 2011 Excel Trader - All Rights Reserved


  1. Hi ... I would like to know how to convert decimal prices back into fractal prices :

    like 120 04.5 into 120.1406 and back to 120 04.5.

    I'm not familiar with VB.



Speak Your Mind