# Alteryx Designer Knowledge Base

Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

# Everything You Need to Know About Currency

Alteryx
Created

Topics discussed below:

Currency Field Formatting

Currency Conversion

Currency Field Formatting - Strings to Doubles

We often get a lot of questions from new users about how to convert fields with currency formats to doubles and vice versa. If you have currency fields in your data that come in to Alteryx with the currency format (ex: \$1,354.00) and you want to perform any kind of numeric calculations, you must convert these fields to numeric fields (i.e.: Double, FixedDecimal, Int 32 etc…). For more information on data types click here.

There are a couple ways to convert this string format to a numeric format. Below I will demonstrate two ways: one is using the formula tool, the other is using the multi-field formula tool. Both are similar but one is a bit more flexible than the other.

Using the Formula Tool:

If you’re using the formula tool you must make a new field for the converted string. Make sure the type is numeric, I usually go with Double. Then use the formula shown below. The first part of the expression will replace the \$ and comma with empty strings. While ToNumber() will change it to a number.

Using Multi-Field Formula:

Using the multi-field formula is very similar to the formula tool. In this tool we can specify multiple fields if we so choose. Also we don’t have to make a new field. We can actually change the ones we already have. In the configuration, make sure you have “Text” fields pop up in the selection window and select the fields you want to change. The formula in this case is exactly the same except you use [_CurrentField_] as your field variable. This variable will run through the expression for all fields that you selected at the top.

Currency Field Formatting - Doubles to Strings

After you have done your calculations and you want this back into currency format you can simply use this expression in your formula tool:

'\$'+ToString([FieldName], 2, 1)

Now on to a fun topic: Currency Conversion

For those who gather data that comes in as a different currency than what they want in their reports could always look at conversion rates manually and do the math themselves (using Alteryx of course). The only problem with that is it becomes tedious and currency conversion rates change all the time. The best way to get this real time data is to do an API call to a website which offers this data in real time.

The workflow I have attached has a macro that I have built (version 10.6), which allows a user to choose the currency their field is in with a dropdown interface and convert it to a different currency. This macro uses the xe.com free API to get currency conversion rates in real time.

The base URL we make for this request is http://www.xe.com/currencyconverter/convert/?Amount=1&From=”FROM”&To=”TO”

The “FROM” and “TO” will change when the user chooses the currencies. After that happens, these will get replaced with the currency’s ISO 4217 code and the download tool will gather all information pertaining to that URL. After some parsing, we obtain the currency conversion rate and place it into its own field, from which we use to calculate our new currency.

To learn about APIs and how to connect to them using Alteryx. I would check out this article.

8 - Asteroid

@Ozzie

So if I want \$99.66 in numeric format, is it possible?

Alteryx

@marlline

Unfortunately no it would not because the "\$" is a string character. You must take the dollar sign out when converting to a numeric format.

8 - Asteroid

@Ozzie

Alright. Thank you!

Also can you show me how to @ me? When I @ you, there is no hyperlink showing up. I am newbie here. Thank you!

5 - Atom

Hi,

Using some of the above advice, I've tried to change currency e.g. £300,000.50  (I'm from the UK!), from text to a numeric using ...

but I'm left with 300.0.

So, although I'm losing the '£', which is good, I'm losing any numbers after comma's. and also limiting results to 1 decimal place (I need 2).

Also, for any negative values, I'm losing the minus sign '-'.

Any suggestions?

Sorry for the long winded explanation, I'm new to this.

Thanks,

Gareth

6 - Meteoroid

Hello,

When you convert number to a string to get \$ in front of it, in the summarize tool i cannot sum the values because they are no longer values, they are strings.

I multiplied my qty*Price (both fixed decimals) to get my Sales col. But I would like my sales to have a currency format, and also be able to sum them up on the summarize tool.

Is there a work around for this?

Thank you,

Lilly M.

Alteryx

Hello Lilly,

Unfortunately, you can't do that right now. You currently have to do all of your summarizations first before converting the fields to string in order to display the monetary format. Something that would be cool in the future is to be able to show monetary format on a numeric field if a user configures it. I would highly suggest posting the idea up to our Designer Ideas page. https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas.

-Ozzie

5 - Atom

Ozzie - do you know if your macro is still working with xe? I'm trying to run it but keep getting 0 for the converted amounts

Alteryx Partner

@Ozzie I'm using PostGres Greenplum and I'm trying to write to a money(255) data type. I'm getting all sorts of conversion errors when attempting to do this and am wondering if my convert-from-a-string-and-then-round-to-two-decimal-places-and-consider-it-a-double isn't good enough?