Currency data fetch from ECB's API with macro

Question

Hi community,

I'm reaching out regarding currency data retrievable through the European Central Bank's (ECB) API. I would like to be able to fetch exchange rate data for each currency within specific date ranges (from the MaxDate in the example data up to the current day) using a workflow and a macro. I have been trying to create a macro using a sample currency (USD) so that an API command, or URL, can be generated based on the information in each row.

For the macro, I would like it to create a unique ID for each currency (using the RecordID tool) and then use a Formula to generate the API command, resulting in a URL.

In the workflow, I want the macro to be able to update the ID number for each currency, incrementing it by one based on the previous row's currency ID. Then, the macro would update the URL for the API command to fetch the currency data. Finally, I would like all this information to be provided as output, so that the exchange rate data for each currency can be written to a database one currency at a time. In other words, the workflow and macro would run in a loop, starting with the smallest ID number. At the moment, there will come API headers such as "TIME_PERIOD", "CURRENCY_DENOM" etc. to the output data, after each currency is looped.

Both the workflow and macro are currently a bit confusing as I have been attempting to modify them. At the moment, the macro changes each currency to USD for some reason, which I am not able to understand. I apologize for this, as I am new to using Alteryx and my skills are still limited.

Any advice or explanation what should be done would be highly appreciated! Thanks! Happy to provide more detail if needed!

Please see the attached workflows to better understand what I am trying to do.

See the link to ECB's web page to read about the APIs: https://data.ecb.europa.eu/help/api/data

ECB_BatchMacro_F.yxmc

ECB_Workflow_F.yxmc

volkerlebens · Answer

Thanks for the short practical workflow!

Nfftrol · Answer

Hi Patrick,

Thanks for your previous reply. After a long break, I've started working on this project again. The solution you provided helped me get the workflow running without errors in Designer. Thanks for that!

Lately, I've been trying to upload the workflow to Alteryx Gallery to schedule it to run automatically daily, after new exchange rates have been published by the ECB. However, I've encountered some issues indicating that connecting to the database is not possible. I believe this is related to my company's trusted network connections, which will eventually need some IP addresses to be whitelisted.

The reason I'm posting again is that I’ve started to think about how the workflow would function in Gallery, given that the Download tool saves output data to a temporary file located on my local drive (C disk). The Dynamic Input tool then reads data from this temporary file if I understand the workflow correctly.

Would it be better to change this setup to avoid using local storage? How should I go about it? Could I, for example, add a temporary file to Gallery that the workflow could use? Or would it be better to modify the actual workflow and tools used in it?

Have you encountered similar issues before?

patrick_digan · Answer

@Nfftrol Hopefully the attached will get you started. I made several changes:

1) I don't know if it mattered, but I changed the DateTimeToday to only the left 10 characters (ie 2024-06-14)

2) I don't think a batch macro is needed. I added the formula and download tool back to the main workflow. You mentioned writing the data to a DB one currency at a time. If the one currency at a time is truly a requirement, then I would batch macro just that part, not the rest of the workflow which can be done without a batch macro.

3) For the download tool, I changed it to save the csv's as a file. Since the website returns csv data, this made the most sense to me:

3a) to read the csv data, I now used a dynamic input:

4) In case you wanted the record ID or max date for something, I joined that data back based on the filename.

ECB_Workflow_F.yxmd