Value Divided by Exchange Rate From Different Sheet

[JSheppard98]

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.

[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?

[caltang]

In that case, then yes, making it using a Transpose tool will help. You will still need to join to your raw data where you want to apply your exchange rates to. 

If you're stuck, maybe provide more raw data and we can look at it together. 

[JSheppard98]

That's a relief at least, please see attached and let me know if this is fine for the raw data?

[caltang · Accepted solution]

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.

[JSheppard98]

Amazing, thank you so much for the help! This is perfect for what I need. This is also dynamic just to check? So if I was to change March's exchange rates when I am running this each day to get the prior day's balances etc, it will pick up from March's ones not Feb's?

[caltang]

Very happy to help you on that. If you have an example dataset of the changes, kindly provide them and let me make it dynamic for you. 

But if you wish to learn, you can try changing the data on your own and see what happens. I would suggest for you to use an analytic app so that you can play around with the process more fluidly. 

Regards,

Cal