Convert into decimal form

[nada]

Hi! I have a list with prices of US Treasury bonds. They are quoted in a partcular 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. Is there any way that I can convert these price quotes into decimal form using worksheet functions? Any help very much appreciated! Thanks a lot in advance!

 

[Scott Huish]

How does 99-021 equal 99 and 2/32 and 1/256? Based on your description, it seems like it should equal 99 and 2/32 and 1/124. What am I missing here?

 

[the magician]

Or do you mean 128:s, not 124:s?

Would the price ever be a single digit (9-02) or triple (199-02)?

 

[nada]

No you are not missing anything. However that is how they are quoted. The last digit refers to the number of eights of 32:s. 1/8/32=1/256. The plus/minus refers to adding/subtracting 1/64. Please see the follwoing link page 10 (top of the page):

http://www.newyorkfed.org/cfcbsweb/Treasuries_and_agencies.pdf

 

[nada]

Hi the magician! Well that could happen, although it is very unlikely. However the price could very well be eg 105-214. Ie there could be three non decimal numbers.

 

[Andrew Poulsom]

How about?

=LEFT(A1,FIND("-",A1)-1)+(MID(A1,FIND("-",A1)+1,2)/32)+IF(ISNUMBER(MID(A1,FIND("-",A1)+3,1)+0),MID(A1,FIND("-",A1)+3,1)/256)+IF(MID(A1,FIND("-",A1)+3,1)="+",1/64)-IF(MID(A1,FIND("-",A1)+3,1)="-",1/64)

 

[the magician]

How about?

=LEFT(A1,FIND("-",A1)-1)+(MID(A1,FIND("-",A1)+1,2)/32)+IF(ISNUMBER(MID(A1,FIND("-",A1)+3,1)+0),MID(A1,FIND("-",A1)+3,1)/256)+IF(MID(A1,FIND("-",A1)+3,1)="+",1/64)-IF(MID(A1,FIND("-",A1)+3,1)="-",1/64)

Yeah, that's what I was thinking....
Fine work, Andrew.

 

[nada]

Thanks!

Thank you very much Andrew Poulsom and the magician! You really helped me out! I also wrote a user defined function in vba (if anyone is interested(?)). I posted the code to that one also. But again thank you very much for your help!!!

Function convert(Indata As Variant) As Double
' konverterar "priset" på US T-notes/bonds till decimalform.

Dim lngPos As Long 'position i sträng
Dim length As Long 'prisets längd
Dim lngStrPos As Long 'position för "-"

Dim fractionPrice As String 'okonverterat pris
Dim firstDigits As String 'antal 32 delar

Dim thirdNumber As Double 'värdet på 3:dje decimaltalet
Dim firstNumbers As Double 'värdet av 32:andra delarna

Dim thridDigit As Variant 'sista tecknet
Dim nonDecimal As Variant 'värdet av heltalen

fractionPrice = CStr(Indata) 'typkonvertera till String
lngStrPos = InStr(fractionPrice, "-") 'anger position för första -
length = Len(fractionPrice) 'längd på sträng

nonDecimal = Mid(fractionPrice, 1, (lngStrPos - 1)) 'heltal

firstDigits = Mid(fractionPrice, (lngStrPos + 1), 2) 'de två 32:delarna
firstNumbers = (CLng(firstDigits) / 32) 'decimalvärde

'avgör om det finns mer än 2 decimala tal
If (length - lngStrPos) > 2 Then 'mer än två decimaltal
thirdDigit = Mid(fractionPrice, (lngStrPos + 3)) 'undersöker sista tecknet

If thirdDigit = "+" Then 'om +
thridNumber = (1 / 64)
ElseIf thirdDigit = "-" Then 'om -
thridNumber = (-1 / 64)
ElseIf CLng(thirdDigit) Then 'om siffra
thridNumber = (thirdDigit / 256)
End If

End If '**** mer än två decimala tal

convert = nonDecimal + firstNumbers + thridNumber 'totalt decimalpris

End Function