Currency data fetch from ECB's API with macro

[Nfftrol]

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

[patrick_digan]

@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.