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!

1) I will assume there are always 2 digits before the first hyphen
2) You state:"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." I
will assume this should be 1/124
With the coded value (and Oh what a code!) in A1
In B1 we get the dollar amount with =--LEFT(A1,2)
In C1 we get the 32-th bit with =MID(A1,4,2)/32
In D1 we get the 1/124 bit or the 1/64 correction with
=IF(ISNUMBER(--MID(A1,6,1)),RIGHT(A1)/124,IF(RIGHT(A1)="+",1/64,IF(RIGHT(A1)="-",-1/64,0)))
And in E1 we get the total with =SUM(B1:D1)
I recommend you test this with more examples
I will leave it to use to combine all of this into one simple formula if so
desired
And I expect others to give a us nice VBA solution
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arne Hegefors" wrote in message
...
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!

What a lovely typo
I will leave it to YOU ......
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
1) I will assume there are always 2 digits before the first hyphen
2) You state:"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." I
will assume this should be 1/124
With the coded value (and Oh what a code!) in A1
In B1 we get the dollar amount with =--LEFT(A1,2)
In C1 we get the 32-th bit with =MID(A1,4,2)/32
In D1 we get the 1/124 bit or the 1/64 correction with
=IF(ISNUMBER(--MID(A1,6,1)),RIGHT(A1)/124,IF(RIGHT(A1)="+",1/64,IF(RIGHT(A1)="-",-1/64,0)))
And in E1 we get the total with =SUM(B1:D1)
I recommend you test this with more examples
I will leave it to use to combine all of this into one simple formula if
so desired
And I expect others to give a us nice VBA solution
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arne Hegefors" wrote in message
...
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!

Unless you want to create a User Defined Function....
Try something like this:

For a quoted price in A1

This formula converts the quote to a decimal value
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/124,1/64,-1/64,LEFT(RIGHT(A1,2),1)/124+1/64,LEFT(RIGHT(A1,2),1)/124-1/64)

(yeah....I know....it's not very pretty)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Arne Hegefors" wrote:

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!

Thanks very much bernard. However the dollar amount (ie the non decimal
numbers) might be of one, two or three digits. Also the third decimal should
be 1/256. Please see top of page page 10:

http://www.newyorkfed.org/cfcbsweb/T...d_agencies.pdf

Thanks again Bernard!

"Bernard Liengme" skrev:

1) I will assume there are always 2 digits before the first hyphen
2) You state:"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." I
will assume this should be 1/124
With the coded value (and Oh what a code!) in A1
In B1 we get the dollar amount with =--LEFT(A1,2)
In C1 we get the 32-th bit with =MID(A1,4,2)/32
In D1 we get the 1/124 bit or the 1/64 correction with
=IF(ISNUMBER(--MID(A1,6,1)),RIGHT(A1)/124,IF(RIGHT(A1)="+",1/64,IF(RIGHT(A1)="-",-1/64,0)))
And in E1 we get the total with =SUM(B1:D1)
I recommend you test this with more examples
I will leave it to use to combine all of this into one simple formula if so
desired
And I expect others to give a us nice VBA solution
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arne Hegefors" wrote in message
...
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!

Arne,

For a string in cell A2:

=VALUE(LEFT(A2,FIND("-",A2)-1))+VALUE(MID(A2,FIND("-",A2)+1,2))/32+IF((LEN(A2)-FIND("-",A2))=3,IF(MID(A2,FIND("-",A2)+3,1)="-",-1/64,IF(MID(A2,FIND("-",A2)+3,1)
= "+", 1/64,MID(A2,FIND("-",A2)+3,1)/256)))

I just wasn't sure how to interpret this:

"refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256."

Should it actually be divided by 124, or by 256 - I used 256 in the formula.

HTH,
Bernie
MS Excel MVP


"Arne Hegefors" wrote in message
...
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!

Per your reference to the treasury PDF file....

Try this:

A1: (a quoted price)

The decimal value would be
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/256,1/64,-1/64,LEFT(RIGHT(A1,2),1)/256+1/64,LEFT(RIGHT(A1,2),1)/256-1/64)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Unless you want to create a User Defined Function....
Try something like this:

For a quoted price in A1

This formula converts the quote to a decimal value
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/124,1/64,-1/64,LEFT(RIGHT(A1,2),1)/124+1/64,LEFT(RIGHT(A1,2),1)/124-1/64)

(yeah....I know....it's not very pretty)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Arne Hegefors" wrote:

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!

Ok...here it is....the best I could come up with:

B1:
=LEFT(A1,FIND("-",A1)-1)+SUM(LOOKUP(MID(A1,FIND("-",A1)+{1,2,3,4},1),{"","-","+","0","1","2","3","4","5","6","7","8","9"} ,{0,-4,4,0,1,2,3,4,5,6,7,8,9})/{3.2,32,256,256})

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Per your reference to the treasury PDF file....

Try this:

A1: (a quoted price)

The decimal value would be
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/256,1/64,-1/64,LEFT(RIGHT(A1,2),1)/256+1/64,LEFT(RIGHT(A1,2),1)/256-1/64)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Unless you want to create a User Defined Function....
Try something like this:

For a quoted price in A1

This formula converts the quote to a decimal value
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/124,1/64,-1/64,LEFT(RIGHT(A1,2),1)/124+1/64,LEFT(RIGHT(A1,2),1)/124-1/64)

(yeah....I know....it's not very pretty)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Arne Hegefors" wrote:

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!

Arne Hegefors wrote...
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.
....

Yet another alternative.

=LEFT(x,FIND("-",x)-1)+MID(x,FIND("-",x)+1,2)/32+IF(COUNTIF(x,"*-???"),
CHOOSE(FIND(RIGHT(x,1),"+-"&RIGHT(x,1)),4,-4,RIGHT(x,1))/256)