How to convert Bloomberg pricing and round up to 4 decimal places

[MarkPitcock]

Let me start by providing the excel formula below. I'm trying to convert this into Alteryx in the best possible way. I download pricing from Bloomberg which has various conventions. Treasures are priced as fractions 99-03 = 99,09375 (99+(3/32)) or 99-03+ = 99,109375 (99+(3.5/32)). Other bond prices have 14 digits after the comma which I need to be rounded up to the 4th decimal place  

=IF($I8="","",IF(ISNUMBER($I8),$I8,IF(ISERROR(LEFT($I8,FIND("-",$I8)-1)),LEFT($I8,FIND(".",$I8)-1),LEFT($I8,FIND("-",$I8)-1))+IF(ISNUMBER(FIND("+",IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8))))),LEFT(IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8))),LEN(IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8))))-1)/32+1/64,IF(ISNUMBER(FIND("-",IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8))))),LEFT(IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8))),LEN(IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8))))-1)/32-1/64,IF(ISERROR(MID($I8,FIND("-",$I8)+1,LEN($I8))),$I8,MID($I8,FIND("-",$I8)+1,LEN($I8)))/32))))

Any help would be much appreciated

[jarrod · Accepted solution]

to me, this looks like a series of filters would work best. Maybe start by filtering for contains([DataField],'-') to find the 99-03 and 99-03+ cases, then add another filter after the "true" output for contains[DataField],'+') so you can break those down into two streams. after you have the data streams filtered by format, it's just a matter of breaking it down through parsing. you can apply a single parsing rule for each stream instead of trying to use a single formula to catch all of the cases. Then you can union all the streams when you've cleaned them up. 

[MarkPitcock]

Thanks Jarrod - good advice

[danilang · Accepted solution]

Hi @MarkPitcock 

@jarrod gave some good advice, but you're still left with converting that Excel formula.  Here's a regex based solution that does that for you.

 

After splitting out the regular prices, the treasure prices are parsed using the following regex

The regex formula is this

(\d*)-(\d\d)(\+*)

(\d*) looks for zero or more number of digits representing the integer part

- looks for the "-"

 (\d\d) looks for the 2 digit 32nds part

(\+*) looks for zero or more "+"

These groups are output to the various fields listed in the config

The formula used to calculate the final number is 

[IntegerPart]+[32s]*(1/32) + iif(isempty([64s]),0,1/64)

The iif() checks if [64s] is empty and returns 0 or 1/64 (= 0.5 * 1/32).  Note that the formula outputs to a fixed decimal (19.4) which rounds to 4 decimals.  You didn't specify whether the treasure prices are rounded.  If you don't want them rounded change the field to a double 

Dan

[MarkPitcock]

Wow Dan , this is amazing. I'm definitely going to use this . Thank you for your help everyone