Value Divided by Exchange Rate From Different Sheet

Hi there,

Wondering if this can be possible in Alteryx, as I have a macro in excel to just vlookup formula based on the dated column with the dated exchange rates to give me GBP equivalents.

I've attached the workflow, this is run and gives the previous day date already, but what I'm looking for is a way to convert/change the regular H2/I2 cell values etc etc by the corresponding exchange rate and the date to give me GBP equivalents, instead of the original currency value in the cells.

This is the formula I've used in excel vba pretty much if this helps at all.

=H2/VLOOKUP('Balance Summary'!$G2,'Exchange Rates'!$A:C,12,0) - this would convert the H2 value to gbp equiv, and vba macro then pulls this down the column for other accounts in the row. Then the next day it would shift along to the next column.

Value Converted Sample.xlsx

Tips and Tricks

Custom Tools

Join

Accepted answers

caltang

Please find attached:

If this suits your needs, then you can make it even more dynamic using an analytic app. Hope this gives you some ideas / pointers.

For JSheppard98.yxmd

All comments

caltang

Correct me if I am wrong, but what you're essentially asking is if Alteryx can update the exchange rates given the currency and date, correct?

If yes, then it can be done in Alteryx though not as straight forward as a VLOOKUP.

First, you'll need to have a file (xlsx) that focuses on currencies and exchange rates. Like what you have:

Then, it will be helpful to have a Transpose tool help you make the dates into 1 column, and you have values of each currency exchange per date and per currency.

Then, use a Join Tool to connect to your original dataset via Currency as the primary key.

Each time the date changes in your main data set or even if the currency you want changes, it will join and reference accordingly.

caltang

As a bonus, you can do a quick change using an analytic app to get what you desire without much interference in the Designer workflow itself.

Kindly provide more dummy data and I can give you a sample.

JSheppard98

Thanks for the reply, not update them no (as they'd be the same for 1 month basis until updating the next months), I have them from a different file that I've filled out the whole year, and then brought into my workflow, which outputs to a sheet on my desired output file. Each month would manually update said months with the values and leave it at that.

More so looking for the values in the 1st screenshot/balance summary tab, to be divided by the exchange rate. I don't necessarily need to have each days exchange rate there in all honesty, as like I said, we keep them the same for a month period. So only 1 column per month for the exchange rate values could work in this case. If this helps?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335