Translating Values to Current Rates

Hi All

I'm trying to create a work flow that validates the FX rates of all periods by taking the underlying input currency and multiply them by the FX rate table per month. The recalculation (EURO Value in the Input 19A * Euro Rate) and it should calculate the same for all the input currencies. At the end all the calculations should equal to the highlighted row by period USD @ Forecast Rate. I'm trying to validate to make sure that the rates in the system are calculating correcting - see the two attached file

1) first file is the input file by period

2) is the FX Table by period

I started something, but it's easy to do in Excel but I want to add this in Alteryx

Thank you

FX Table Rates.xlsx

Input 18A (GBPQ).xlsx

Workflow

Accepted answers

T_Willins

Hi @Arkouda,

This should now work. Using the original file I was able to eliminate the data cleansing. Added a group by entity to the Summarize tool so the last Join can use entity and month.

FX Rate Conversion v2.yxmd

BenMoss

Hi!

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

Learn more about Accepted Solutions here.

Thank you!

All comments

T_Willins

HI @Arkouda,

We use similar workflows as part of our trial balance processes. Attached is an example using your data. The easiest way to to compare data with multiple months is to first Transpose the data values into a single column so a Join can be used to put the FX rates on the same row as the input values by month and currency. The rest of the workflow uses a Formula tool to calculate USD amounts, Sum USD by month, Join with the USD_Forecast data and finally a Formula tool to calculate the variance. There is a variance showing as Excel limits the amounts in a calculated cell to 15 significant figures, while Alteryx does not have this limitation*.

* There is a single record size limit of 2GB in a 64-bit environment, but the number would have to be very, very large!

FX Rate Conversion.yxmd

Arkouda

Hi T_Willins

Thank you for the quick response on this. Very helpful, but I have a wrench to throw this into - I have multiple entities that I need this to work on. See attached the actual work file - I attempted on my end to add this file, but running into issues

Thank you

Input 18A (GBPQ).xlsx

BenMoss

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703