For your VBA, CUT AND PASTE the Function out of this VB.Net code.>>
Hi! I have a list with prices of US Treasury bonds. They are quoted in a particular way that I would like to convert to decimal form. They are quoted like this eg: 99-02. Now this means that the price is 99 and some decimal. The two first numbers are the number of 32:s. Thus in this case the price is 99 and 2/32. However if there are three decimals eg 99-021 the last digit refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256. Sometimes There are only two decimals followed by a plus or minus sign eg 99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
Keep in mind that the non decimal numbers may be one, two or three digits. Thus, it could be eg. 6-02 87-22- or 105-21+. Is there any way that I can convert these price quotes into decimal form by creating a user defined function in vba? I have trouble coming up with code that searches for the dividing sign between non decimal numbers and decimals numbers (ie “-“) and then work it from there. Any help very much appreciated! Thanks a lot in advance!
Hi,
99-021 = 99 + 2/32 + 1/124 ' ? seems to me that this one makes the most
'sense as each part is almost double the previous part.
Is it not 128 'ths?
or 99-021 = 99 + 2/32 + 1/256 ' ? which is it please as your question isn't clear.
99-30+ would be 99 + 3/32 + 1/64 then?
Regards S_DS
Private Function ConvertBondPrice(bondPrice as String) As Decimal
Dim priceElementArray as String()
' May want to validate bondPrice here to ensure correct formatting
priceElementArray = bondPrice.Split("-")
Dim fractionalElement as Decimal
fractionalElement = Integer.Parse(priceElementArray(1)) / 32
Dim outputValue as Decimal
outputValue = Decimal.Parse(priceElementArray(0)) + fractionalElement
Return outputValue
End Function
Hi,
I done this.>>
Just CUT & PASTE THE FUNCTION out of the following VB.Net CLASS code if you want to use them in VBA.
Public Class Bond
Private Cost As String
Public Sub New()End SubFunction ToDecimal(ByVal Price As String) As DecimalDim currPrice As StringcurrPrice = Price
Dim len As Integer = currPrice.LengthDim finalCost As Decimal = 0'Has price got 64's?If currPrice.Substring(len - 1, 1) = "+" ThenfinalCost = finalCost + (1 / 64)
'currPrice = currPrice.Substring(0, len - 1)finalCost = finalCost +
CDec((currPrice.Substring(len - 3, 2)) / 32)'Get Integer part.Dim index2 As Integer = InStr(currPrice, " ") - 1Dim mySub2 As String = currPrice.Substring(0, index2)finalCost =
CDec(mySub2) + finalCostReturn finalCostElseIf currPrice.Substring(len - 1, 1) = "-" ThenfinalCost = finalCost - (1 / 64)
'currPrice = currPrice.Substring(0, len - 1)finalCost = finalCost +
CDec((currPrice.Substring(len - 3, 2)) / 32)'Get Integer part.Dim index3 As Integer = InStr(currPrice, " ") - 1Dim mySub3 As String = currPrice.Substring(0, index3)finalCost =
CDec(mySub3) + finalCostReturn finalCostEnd If'Check for 3 final numbers.Dim mySub As StringmySub = currPrice.Substring(len - 3, 1)
If IsNumeric(mySub) = True Then'Number has three last digits.finalCost = finalCost +
CDec((currPrice.Substring(len - 3, 2)) / 32) + _CDec
((currPrice.Substring(len - 1, 1)) / 124)Else'Number only has two last digits.finalCost = finalCost +
CDec((currPrice.Substring(len - 2, 2)) / 32)End If'Get Integer part.Dim index1 As Integer = InStr(currPrice, " ") - 1Dim mySub1 As String = currPrice.Substring(0, index1)finalCost =
CDec(mySub1) + finalCostReturn finalCostEnd FunctionPublic Property value() As StringGetReturn CostEnd GetSet(ByVal value As String)Cost = value
End SetEnd PropertyEnd
Class
' Then my FORM code is just.>>
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ClickDim myBond As New BondmyBond.value = TextBox1.Text
Dim myDec As DecimalmyDec = myBond.ToDecimal(myBond.value)
TextBox2.Text =
CStr(myDec)End Sub Instructions>> Type BOND price in any of these FORMATS into textbox1
99 17+
99 17-
99 17
Clicking button1 gives the following output to textbox2>>
99.546875
99.515625
99.53125
Is this what you are after?
Regards,
S_DS
P.S. Would you need reverse conversion?
