Sign in or start a free trial to avail of this feature.
7. Preparing Data for a Basic Macro
You will learn how to build your first macro by designing a tool that uses an API to download foreign exchange information. This first lesson sets up the workflow.
- In this tool we make use of an API from the European Central Bank to get foreign exchange rates – you can find the link here: http://api.fixer.io/
- API stands for “Application Programming Interface”
- APIs are allow for communication between various software, and are generally used to access web-based data
In the previous five lessons, we looked at how to create analytical apps in Alteryx, in the next several lessons, we'll look at a similar file type, the macro, what is a macro? A macro takes input data, performs a set of calculations, that then produces an output, another way to think of a macro is as a user-designed Alteryx tool, typically these are labor-saving shortcuts, that perform repeated tasks, that are required throughout your organization, let's take a look at an example. In the analytical app Workflow, you may remember that we had daily sales data, which we cross-referenced with some foreign exchange data to come up with sales in our home currency. Wouldn't it be fantastic, if we could instead add a macro to our Workflow, that looked up historic foreign exchange data and returned the relevant numbers to us? Our goal in this lesson is to create a Workflow to use as a framework for this macro. To accomplish this goal, we'll follow three key steps, first we'll convert our Workflow to the standard macro file type, next we'll import API data containing foreign exchange information, finally we'll parse the API data to return only the usable information. The first step is to change the file type of our Workflow, we'll go to the Configuration window of the Workflow, navigate to the Workflow tab and select Macro, we'll stick with standard macro for now. We're now ready to move on to step two and import our API data.
An API is a piece of software, that delivers specific, internet-based data to users, in this case, we'll make use of a free API of daily foreign exchange rates published by the European Central Bank, you can find a link in the Lesson Notes. This API will return a list of exchange rates, against the base currency for a date specified. Next, we'll start our Workflow by creating a simple date record, we'll bring a Text Input tool onto the canvas and give it a field heading of Date with one record, March 1st, 2016, this is simply a placeholder for now.
We now need to create a formula, that references the API, we'll connect a Formula tool and name the field URL, in the Formula canvas, we'll enter our API address in quotes and add the Date field, this Formula references the webpage with the exchange rate information for the specified date, to get that information on the Workflow, we need to use the Download tool, we'll navigate to the Connectors tab and connect a Download tool to the Workflow, we'll point the URL field to the URL column we just created and run the Workflow. We can see that two new fields have been created, DownloadData and DownloadHeaders, the DownloadData field contains the foreign exchange data, expressing various currencies in terms of Euro. Now that we've imported data from the API, we can move on to step three, in this step, we'll parse that data to get it into a format we can use. First, we'll add a Text To Columns tool, select the DownloadData field, choose to Split to Rows and run the Workflow.
Now each of our currencies has been split into 33 records, however the information in the DownloadData column still isn't in a usable format, we'll need to connect another Formula tool to parse this data, we'll select the DownloadData column and write a Formula to replace the rates with a blank space, we'll create another Formula canvas, this time replacing the brackets with a blank space, we'll then create one last Formula canvas to replace the text base colon Euro with Euro colon one, next we need to separate the currency codes from the foreign exchange rates by using another Text To Columns tool. In the Configuration window, we'll select the DownloadData column, select colon as a delimiter, split into two columns and run the Workflow again.
You may have noticed that there's a row, that contains date information and not currency information, we can remove this row with a Filter tool, in the Configuration window, we'll choose the field DownloadData1 and filter for columns, that do not equal date, finally, we'll bring down a Select tool, change the name of DownloadData1 to Currency and DownloadData2 to Fx Rate, we'll also change the Type of the Fx Rate field to fixed decimal, size 19.6, we'll run the Workflow one last time and see that we now have a list of 32 different currencies all expressed in terms of Euro for the date, March 1st, 2016.
We've now created our base Workflow, so we'll stop here. To recap this lesson, we first converted our Workflow to the standard Macro file type, we then imported API data, containing foreign exchange information, finally we parsed the API data to return only the usable information. In the next lesson, we'll complete this Workflow by converting it to a functioning Macro.