|
Treasury Bond Pricing conversion
As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
Just a hint to go forward
Suppose A1 = 111-222 The below formula will give you the numbers before the hyphen no matter what the length is =VALUE(LEFT(A1,FIND("-",A1)-1)) And the below formula will give you the numbers after the hyphen to a maximum of 10..which is more than enough =VALUE(MID(A1,FIND("-",A1)+1,10)) If this post helps click Yes --------------- Jacob Skaria "pakeez" wrote: As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
Assuming your decimal value is in C1, this will convert it back to the
format you showed... =INT(C1)&"-"&320*MOD(C1,1) -- Rick (MVP - Excel) "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
"pakeez" wrote:
110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 However, initially you said "110-16 which is equivalent to 110 16/32". Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xx. If C1 is =A1-B1, then: int(C1)&"-"&text(mod(C1,1)*320,"000") ----- original message ---- "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
"Rick Rothstein" wrote:
=INT(C1)&"-"&320*MOD(C1,1) Test with 110-160 minus 99-159. I believe the desired result is 1-001. Ostensibly, your formula will result in 1-1. But it actually results in 11-0.999999999999091. ----- original message ----- "Rick Rothstein" wrote in message ... Assuming your decimal value is in C1, this will convert it back to the format you showed... =INT(C1)&"-"&320*MOD(C1,1) -- Rick (MVP - Excel) "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
Errata....
I wrote: If C1 is =A1-B1, then: int(C1)&"-"&text(mod(C1,1)*320,"000") I meant that A1 and B1 are the decimal equivalents. I probably should have use A2, B2 and C2 in my example to avoid confusion. ----- original message ----- "JoeU2004" wrote in message ... "pakeez" wrote: 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 However, initially you said "110-16 which is equivalent to 110 16/32". Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xx. If C1 is =A1-B1, then: int(C1)&"-"&text(mod(C1,1)*320,"000") ----- original message ---- "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
Good point. Your TEXT function call to limit the entry to 3 digits is the
way to go. Thanks for noting the problem with my formula. -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "Rick Rothstein" wrote: =INT(C1)&"-"&320*MOD(C1,1) Test with 110-160 minus 99-159. I believe the desired result is 1-001. Ostensibly, your formula will result in 1-1. But it actually results in 11-0.999999999999091. ----- original message ----- "Rick Rothstein" wrote in message ... Assuming your decimal value is in C1, this will convert it back to the format you showed... =INT(C1)&"-"&320*MOD(C1,1) -- Rick (MVP - Excel) "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
Yes. But according to your example, the left part is not always 3 digits.
Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 Just been thinking about this; perhaps this, with only one function call, would be the better way to calculate the decimal value of the OP's formatted numbers... =--(SUBSTITUTE(A1,"-"," ")&"/320") -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "pakeez" wrote: 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 However, initially you said "110-16 which is equivalent to 110 16/32". Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xx. If C1 is =A1-B1, then: int(C1)&"-"&text(mod(C1,1)*320,"000") ----- original message ---- "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
However, initially you said "110-16 which is equivalent to 110 16/32".
Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. And this variation will distinguish between 2 and 3 digit values after the dash... =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0")) so that 110-16 and 110-160 will evaluate to the same value. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 Just been thinking about this; perhaps this, with only one function call, would be the better way to calculate the decimal value of the OP's formatted numbers... =--(SUBSTITUTE(A1,"-"," ")&"/320") -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "pakeez" wrote: 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 However, initially you said "110-16 which is equivalent to 110 16/32". Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xx. If C1 is =A1-B1, then: int(C1)&"-"&text(mod(C1,1)*320,"000") ----- original message ---- "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
"Rick Rothstein" wrote:
=--(SUBSTITUTE(A1,"-"," ")&"/320") Nifty! And this variation will distinguish between 2 and 3 digit values after the dash... =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0")) I had a similar thought after seeing your first follow-up. But for defensive-programming purposes, I would suggest: =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,MAX(1,LEN(A1)-3),1)<"-","","0")) That allows for the right part to be 1 digit as well as 2 or 3. I don't know if that's necessary; I'm not familiar with the format of T-bond prices, other than what Pakeez wrote. But the flexibility comes with no cost, even if it is unnecessary. (Well, almost "no cost". I put MAX(1,...) in at the last minute to handle cases like 1-1.) Fun stuff! :-) ----- original message ----- "Rick Rothstein" wrote in message ... However, initially you said "110-16 which is equivalent to 110 16/32". Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. And this variation will distinguish between 2 and 3 digit values after the dash... =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0")) so that 110-16 and 110-160 will evaluate to the same value. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 Just been thinking about this; perhaps this, with only one function call, would be the better way to calculate the decimal value of the OP's formatted numbers... =--(SUBSTITUTE(A1,"-"," ")&"/320") -- Rick (MVP - Excel) "JoeU2004" wrote in message ... "pakeez" wrote: 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Yes. But according to your example, the left part is not always 3 digits. Ostbensibly, the more versatile conversion to decimal is: left(A1,find("-",A1)-1)+right(A1,3)/320 However, initially you said "110-16 which is equivalent to 110 16/32". Did you really mean 110-160, as you use later? That is, is the right part always 3 digits? I hope so. It would be difficult, but not insurmountable, to differentiate 2-digit and 3-digit right parts. Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xx. If C1 is =A1-B1, then: int(C1)&"-"&text(mod(C1,1)*320,"000") ----- original message ---- "pakeez" wrote in message ... As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which is equivalent to 110 16/32 or 99-245 which is equivalent to 99 24.5/32) Now I need to do some calaculaltions while trading Bonds and I want to do in excel 03. I was given a hint that I need to convert the above price format first in decimal value and then format the result in xxx-xxx. e.g 110-16 is in cell A1 then formula for converting to decimal would be: =Left(A1,3)+Right(A1,3)/320 Since I have been out of touch with excel for long time I am having hard time how to format back in xxx-xxx. So I am giving an example below to do calculations to do price difference: say cell A1 has value of 110-160 say cell B1 has value of 99-245 The answer is 10-235. Now how can I get this calculations done in excel. Detail formulas would be appreciated. Thank YOu -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
Treasury Bond Pricing conversion
See inline comments...
=--(SUBSTITUTE(A1,"-"," ")&"/320") Nifty! Thanks And this variation will distinguish between 2 and 3 digit values after the dash... =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0")) I had a similar thought after seeing your first follow-up. But for defensive-programming purposes, I would suggest: =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,MAX(1,LEN(A1)-3),1)<"-","","0")) That allows for the right part to be 1 digit as well as 2 or 3. I don't know if that's necessary; I'm not familiar with the format of T-bond prices, other than what Pakeez wrote. But the flexibility comes with no cost, even if it is unnecessary. (Well, almost "no cost". I put MAX(1,...) in at the last minute to handle cases like 1-1.) I think this would work as well (and it avoids the need for the MAX function call)... =--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(LEN(A1)-FIND("-",A1)=3,"0","")) Fun stuff! :-) Yep! It sure is. ;-) -- Rick (MVP - Excel) |
Treasury Bond Pricing conversion
I like to Thank You all of you nice guys, who took time to help with my problem. Incorporating everybody's suggestion my basic spreadsheet looks per attachment. Please look to comment in cell E5. Thank You +-------------------------------------------------------------------+ |Filename: Bons price calculation example.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=119| +-------------------------------------------------------------------+ -- pakeez ------------------------------------------------------------------------ pakeez's Profile: http://www.thecodecage.com/forumz/member.php?userid=278 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92522 |
| All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com