Alteryx Essentials

1. 0.0 Introduction: Welcome to Al Tricks Essentials. My name's Andrew Poon and I'm from Educate. I've been an I T consultant for 10 years working on data and analytics projects in Australia, and I've developed this course to help new starters of L tricks get working and confident with this application as quickly as possible. At the end of this course deliverance, fundamental tools and knowledge required to develop intelligent and automated work flows that you can use in the workplace. This course has been structured into three main components. The first is walking you through the interface and explaining what each of the different buttons do and what the different windowpanes on the second, which is broken up into six chapters, will walk through the most commonly used tools. And at the end of it, you understand how to import and export falls between different file formats. Clean and organized your daughter for analysis combined enjoying different datasets together. Visualize your data to derive insights from pause your data into structured tables and create repeatable tasks. Use from at first. The third and final component of this course will put everything we've learned into practice by creating three unique work flows ranging from novice, intermediate and advanced. These work flows involved creating an FTE forecast, automating a cells report and creating a Cryptocurrency arbitrage for a but that takes advantage of price differences between exchanges. So at the end of this course, you will be a highly capable al tricks user with artifacts to back it up with. If you have any questions on any of the content in this course, please leave them in the video and all right back to you as quickly as possible. I look forward to working with you in al tricks essentials. 2. 0.1 Interface: Let's start with a quick overview off the ultras interface, going through the different menus, buttons and window panes, starting with the toolbar at the top, we have our typical file management tools, like creating, opening and saving. A work for the workflow is out Our tricks file that contains all our data and processes that we create here in the workflow pain and is typically saved with a dot y X MD extension . Edit lets us cut copy and paste daughter or a tool and unto and redo out. Last action view lets us hide or unhygienic different pains in our screen and search for a specific tool. However, I prefer to use the search by here at the top. Right options allows us to run our work floor scheduled to run it specific intervals export out workflow manager licenses as well as additional configurations. Finally, in help. There is additional information and examples on using out tricks just underneath. We have our tool ribbon categorized into different tabs, and these contained the tools that will be dragging in to our work for pain. Here, a tool refers to these different colored gadgets at the top of the screen If we actually dragging a tool into our work for pain, you'll notice the configuration. Pain here on the left is updated with some options and configurations for that particular tool. Finally, at the bottom, we have our preview or results pain, which, when we run, how work for outputs the execution status and results off our work for. 3. 1.0 In / Out Introduction: the first chapter. We're covering our tools from the in out tab, which allow you to input daughter into your workflow an output to a file or database. As mentioned at the start, I'll be covering the most commonly used tools instead of all tools to save time and accelerate your learning. As I don't believe they will be much benefit in going over a tool you won't use much. However, by the end of this course, you will definitely have the skills to explore and try the different tools we didn't cover . So with that in mind in this chapter will be going over. Browse daytime now input, data output data and text input. 4. 1.1 Input Data: the input Darda tool that you bring data into your workflow by connecting to a file or database. This will most likely be the starting tool for your work flows. In this lesson, we're going to import an Excel sheet, which I've illustrated here on the right. The first picture shows us going to the in APP tab and dragging an input data tool into our workflow. The second picture shows us selecting our workbook and worksheet, and the third picture has us running out work for with the results out. Put it in the preview pain. Let's stop by going to the in out pain and dragging in an input data tool into our workflow . If we look at the configuration menu on the left under, connect a file or database, we have the option off selecting a file such as CSP, Excel or Zip. And several types of databases, like Microsoft are Call her dupe and many others. Let's open our Excel file by clicking on file and navigating. To wear our course Falls are and selecting 1.1 input daughter. A list of sheet names in the workbook will appear on will select example Hey Char as this is the only shade in the workbook you noticed when we import a file, our configuration pain gives us a preview off our data. Set up to 100 records. Press the run, work for a button or control, are on the keyboard, and the contents of our worksheet have been imported and can be viewed in the preview pane blotter. 5. 1.2 Text Input: the text import tool lets you manually insert text into a table to create small datasets for import. This is most useful for creating look up tables, and if you want to share your workflow with others without having to provide a separate input data file, let's start by going to the in out pain and dragging in the text input tool into our workflow in the configuration menu on the left, we have the option off importing a file, which is similar to our input data tool or weaken manually type in text here. Please note the import option only imports the 1st $1000 off your data set. Let's try manually typing in daughter and copying and pasting daughter from a worksheet. So if we use the manual text input here, we can type in some column names and some records, and let's delete all of them. And let's try copying and pasting from worksheet. We'll select everything here copy, and this interface here can be a bit finicky. Sir, I suggest clicking on any of these cells and shift having until you get this thick border around the first cell. Once you've done that, press paste and everything will be nicely pasted in. Press the run, work for a button or control are on the keyboard, and we have manually inserted daughter into our work for. 6. 1.3 Browse: the browse tool lets you view your daughter up to the point of where it's connected to in the workflow. This tool is most useful for debugging and validating that your dollar is flowing through the workflow, as you intended it to. For those wondering what the differences are between using a browse tool and viewing the data in the results. Pain without brows the Results window only shows you the 1st 1 megabyte of data. Whilst the browse tool shows you everything, let's build upon our input data workflow by opening work for 1.1 will go to file open workflow, browse and select input. Dato 1.1 Old tricks wet floor will go to the tub and dragon Browse tool, and you'll notice that if we drag in a tour that's close enough to another one, al tricks will automatically connect them together. But if they're too far apart, they obviously won't be connected. Let's run our workflow by pressing the run button here or control R on your keyboard, and we can now view out Dada relative to where it's connected to in the work for you'll notice on the left that the configuration pain also provides us some graphs and charts at that out data set. But because this is mapping the employee I d. And what number it is, this isn't exactly relevant for us. 7. 1.4 Output Data: the output data. Tal writes the results off your workflow into a file or table in a database. Let's build upon our brows workflow by opening workflow. 1.3 will go to file. We've been workflow, browse and choose 1.3. Browse l Tricks work. Flu will navigates to the in out pain and drag in the output dot It'll in 12 work for in the configuration menu on the left. We have the option off riding to various file formats. Let's select CSP and give it a phone name off. Hey, cha file and change that to see his feet down here and we'll save it. Press the run workflow button or control R and navigate to the output folder off your file and open it to check that it was written successfully. There it is at the bottom there, and if we open it up, we have now output the results of our work flow into a file 8. 1.5 Date Time Now: the date time now tool allows us to insert the current date and time into our workflow. Let's start a new workflow it by clicking on the in out tab and dragging in a date. Time Now tool into our work for in the Configuration menu on the left. We have the option off selecting one of the many date and time formats. Note that these values are stored as a string and not an actual date type. However, this can easily be converted back to a day type, which will discuss in video 2.2. Press the run, work for a button or control R on your keyboard, and we now have the current data in time inserted into our workflow. 9. 2.0 Data Preparation Introduction: Now that you've completed the first chapter and understand how to input and output data to and from our workflow, we're now going to discuss how to prepare and format your data before you output it. As mentioned in the beginning, I'll be covering the most commonly used tools to save time and accelerate your learning. However, at the end of this course, you'll be familiar enough with the preparation tab to explore the other tools we didn't cover. So with that in mind in this chapter will be going over data cleansing, filter, formula sample select, sort and unique. 10. 2.1 Data Cleansing: the daughter cleansing tool replaces and removes inconsistent or improperly formatted daughter in your imports. I know that sounds a bit abstract, so let's go through it with an example. As you can see with the illustration on the right, the first picture shows in red boxes. All the anomalies we have without data set Employee I. D has random white spaces between the numbers. First name has punctuation issues. Age has a couple of random tabs, and favorite coffee has to know values. What we're going to do in this exercises import our example. Hate our sheets dragon, a daughter cleansing tool to clean it and then run out work for you to view the results of our cleaned, Daughter said. Let's start a new workflow by importing spreadsheet 2.1. We'll go to the import dot at all and connect to our spreadsheet. In here we have our example. Hatch are far from Chapter one, but this time it needs to be cleansed to get a better view off. Our daughter, who had a browse tour to our import dot a tool by using the following keyboard shortcut control shift B will run out work for you with control are and we can see at the bottom in our preview pain that with employee I d. There's a couple of white spaces. First name has punctuation issues. Age has a couple off random tabs and under favorite coffee, there are two no values. A quick way twice identify issues with our data set is to check the color of each column. If it's anything but green, there's potentially something wrong with it. Under age, Al Tricks tells us that 40% off our records and age aren't okay. And under favorite coffee, there are 20% now values. Having these anomalies in your data set might not seem like a big deal, but it could easily throw your data set off from an e, t o or dider ingestion. Point of view. Not cleaning the diver before ingesting it can corrupt the data set and as a user of it can skew the results off your queries. So cleaning, the doctor said, is very important before using it. Let's go to the preparation tub and dragon, a daughter cleansing tool into our work for in the configuration pain on the left. We have several options for selecting which fields we want to clean and how to clean them. The fields and skirt for this exercise were employee I D. First name, age and favorite coffee under replaced Mulls. Let's replace Nol string values with an empty pace of string and replace no numeric fields with zero just to quickly cover off nose and blank values are two different things. No means that nothing is stored in that field. Whilst blank means a blank value is stored in that field under remove unwanted characters, we can UnTech leading and trailing Whitespace is. Since we didn't have any, we can remove tabs, line breaks and duplicate white spaces to fix values that we had in age under or white spaces will take that to fix employee I D, which had white spaces between the numbers. We can leave letters and numbers alone, since we actually want those in our data set, and we can take punctuation to remove exclamation marks in first name. Let's run out work for with control off, and we can see that how Doucet has been cleansed. All al columns show a green color, and we can see that the white space has been removed from this record first name has the punctuation is removed, the tabs in age are gone and how no values and favorite coffee have been removed. 11. 2.2 Select: The select tool is useful for modifying and rearranging your data set. It allows you to include exclude, reorder, rename or resized columns in your data set. Also, if you remember Chapter 1.5 date time now, you'll recall that the current date and time is actually stored is text by default, and the selected tool allows us to set it back toward date format. Let's start a new work for by importing spreadsheet 2.2 into our work. For her, we'll go to the in out tub Dragon Out Input Data tool and select 2.2 Select spreadsheet Press. OK, and again we have our example. Hate Jar file here will go to the preparation Tub and Dragon, a select tool, and in the configuration menu on the left, we have the metadata for our fields, and this is where we can play around with it and maybe renamed last name to surname. Date of birth to DRB Date of joining toe join date. Let's smooth salary to the end of the file, and finally, we can see that out Tricks has picked state of birth and date of joining as date formats. Let's add a browse tool to the end of this by pressing control Shift B, and we'll run out work. For As you can see, the columns have been renamed here, surnamed dear be enjoined date and salary has been rearranged. 12. 2.3 Sort: The sort tour rearranges a set of records in a table based on a certain field, usually by ascending or descending order from A to zed that said a or 0 to 99 to zero. As illustrated in the slide here, the first picture has our example. Hey, Jar file. The second picture shows us dragging in the sort tool and sorting salary by ascending order , and the third picture shows us the result off running our workflow. Let's start a new workflow by opening spreadsheet 2.3 will go to the In Out Tab Dragon, our input data tool and connect to 2.3 sort. We have our example. Hey, Char fall ready to go and note the order of records in salary. Let's go to the preparation top and Dragon out sort tool and in the configuration, pain on the left. We have our list of fields toe sort by and will select salary, and we can also set the order. We want to see them in. Let's choose ascending, press the run, work for button or use control R on your keyboard and we can see that our salary has been sorted by ascending order 13. 2.4 Sample: the sample tool allows you to select a subset of rose in your data. This tool is useful if you need to. Just see, for example, the first record in your data set the 1st 10 records, 10% off all records or every 10th record in your daughter set as illustrated in the slide here, the first picture is out. Example. Hate our data set. The second is where we've included our sample tool, and I've selected one out of every five records and the last picture shows Thea output off that sample. Let's start a new workflow it by importing spreadsheet 2.4 will go to the in out tab, dragging the input data tal and connect to 2.4 sample. We again have our HR file with 30 employees. So now we'll go to the preparation Tub and Dragon, the sample tools in the configuration menu on the left. We have several sampling options. If we go to the middle and update n equals 102 n equals 10 we can now choose to sample the 1st 10 rows in our daughter set the last 10 rows, skip the 1st 10 rows and sure everything else share. One out of every 10 records have a one in 10 or 10% chance to include a troare or only share the 1st 10% off. All records in the data set, select each of these one by one and pressed the run workflow button or control R to familiarize yourself with the outputs of each option. I've selected the 1st 10 rows here. Therefore, the 1st 10 records have appeared in the preview pain. 14. 2.5 Filter: the filter tool allows you to query or data by using one or more expressions or conditions in your columns. The data set is then split into two. True and false. True is where the daughter meets the criteria you've set, and false is where it doesn't. This tool is useful if you need to return only a specific set of records for your data. For example, if you have a list of employees information, but you only want to look up employees whose favorite coffee is along black. Well, that's what I've illustrated in the slide here. Again, our first picture has our employees. HR datasets. The second picture is where I've included the filter tool and put a criteria off. Favorite coffee equals long black. There are two Browse Toshio, one connected to a tea output for truth and one connected to an F output for false. When I run the workflow, I get an output of five employees that have a favorite coffee off long black. Let's start a new workflow by importing spreadsheet 2.5, or go to the End Out Tab Dragon. Our input data tall and connect to the 2.5 filter worksheet. We have our example. Hey, HR file again with 30 employees in it. And let's just note the number of black coffees in there. We've got one, too 345 So let's go to the preparation Tub and Dragon, our filter tool and in the configuration menu on the left, we have two main options. A basic filter or a custom filter. Basic is what most people will select if they have one condition. In our case, we want to select how favorite coffee where equals a long black, the middle boxes out operator and the right box is the value we want to filter by. You'll notice. Under custom filter out tricks has populated the values and syntax based on our basic filter values wrapped around the square brackets. Our variables and the double quotes is the valley that, with filtering by, let's add a browse tool to the true and false outputs of the filter. Tal. We couldn't do this without keyboard shortcut of control shift B, and we'll run out. Work for our daughter set has been filtered by one condition where an employee's favorite coffee is long black, as seen in this slide previously and manually counting. Just then, let's now try a custom filter, which is used for more complex expressions. Mainly, if you have two or more conditions, we'll click on Filter, and we have the choice of typing directly into the expression editor or using these buttons here on the side. The FX shows the list of functions available and ex shows the variables in our data set. Let's practice riding a custom filter so we'll delay it. What's already in here? We'll start by clicking variables on adding favorite coffee. Let's put in equals and in double quotes, typing long black with space. Well, then add another filter within our five employees and select where age is less than 30 so or type in and and in square brackets. Type in age and used the less than operator and typing. 30 30 doesn't need double quotes because it's a number, not string. Let's press the run workflow button or control R and we can see our data set has been filtered by two conditions where an employee's favorite coffee is along black and their age is less than 30 15. 2.6 Formula: the formula tool allows you to write either text numbers, dates, calculations or functions in a column off data. This tool is useful for a wide range of things such as reformatting dates, extracting certain text within a string, performing finance and math calculations, and even writing if statements, the output of thes calculations can be written to a new column or an existing one. Let's start a new workflow by importing spreadsheet 2.6 will go to the in house tub, drag in the input data tool and connect to the 2.6 spreadsheet. We have our example. Hey, HR file again with 30 employees and I've got the date of birth column here that I'd like to reformat. I'd also like to extract the year from the date of joining, and I also want to write some logic to determine if an employee is entitled to long service leave. In other words, have been with the company for 10 or more years. Let's go to the preparation tub and dragging the formula tool in tow out work for in the configuration. Pain on the left were created with the option off assigning the output column so one of the existing ones or a new column and also a list of functions that we can perform for. This video will be using the date time almost function, the left function and the if function. Let's start by clicking in the formula tool and going to the functions icon and typing in dates time format. We'll click on that. And within daytime format, there are two parameters. DT and F Duty is date. F is format, so for DT will replace that with date of birth in square brackets and for F format. We actually need to refer to the online AL tricks documentation. We need to use one of the following formats in this page and because I want day, first month, second and year last, we're going to use this format here, sir. I'll copy this end and replace if with double quotes and our new date format selecting you column will call this dear be and we'll run out work for we see how a new date of birth column formatted as date month and year. Now we're going to repeat the same steps to extract the year only from joined date and calculate long service leave, So the last thing we want to calculate is whether or not an employee is entitled to long service leave. So let's click on the add I Carden selected and you, Colin and call this long service leave. The function we want is the if conditions, and we have three parameters here. See toe check the condition T for the action off. What happens when the condition is true and f for false. If we start with see the conditionally we want to check for Is it? The year joined is less than or equal to 2000 and nine. And if that's true, then they are entitled full service leave. It's not true, then they are not in titled, So because you're joined is currently formatted as a string here. We need to convert the year joint here into a number, so let's pretty fixed this with two number and excess, just the number we want to convert, so we'll leave it as you joined open bracket and close bracket around he joined. If we don't do this, it'll result in era. Let's run out workflow, and surely enough we can see which employees is now entitled to long service leave 16. 2.7 Unique: The unique tool allows you to remove duplicate records in a data set or removed duplicate values in the column. Let's start a new workflow. An import spreadsheet 2.7 will go to the in out tub, dragging the import data tool and select 2.7 unique. Here we have our hate our file again. But instead of the 30 employees, we have 123456 employees that have been duplicated to make sure we have a unique set of employees. Let's go to the preparation Tub and Dragon, the unique tool into our work. For you'll notice that the unique tool has two outputs, one for you or unique and one for D called duplicate, the U receives or the unique records and D receives or the duplicate records in the configuration. Pain on the left. We want unique records only, so we have to select all fields in this list here. However, if we wanted only unique values and a common, then you would just select that unique field and run the work for you. But we want everything in this instance, so let's run our workflow or press control are, and you can see that with you we have our six unique employees and D receives the duplicates 17. 3.0 Join Introduction: Now that you've completed the 1st 2 chapters and understand how to input and output data turned from our workflow and clean and organized data for analysis, we're now going to discuss how to combine datasets together from two or more imports. At the end of this chapter, you'll learn how to combine datasets horizontally with the upend fields tool and vertically using the union tool. We'll also be covering the fine replace and join tool. 18. 3.1 Join: The joint tool allows you to combine two datasets linked by a common field, usually by a unique identifying where, such as a customer i D email address or phone number. AL Trucks also lets you join to datasets based on record position, which means if you had 30 records in one set and five records in the other, the 1st 5 records in birth sets will join HORIZONTALLY, even though there's nothing common between them. Referring to the illustration here, the first picture shows out to datasets. The first is employee information, consisting off employee I. D. First and last name, age, position and department I D, the second daughter said. Contains department information in which includes department I D department name ends, apartment email. We essentially want to find the department name for each employee, as just showing the I D at the moment isn't too helpful. So the steps required here are dragging in to input data tools, a joint all and then joining by a common field and then out putting the results in the third picture to see the department I D. Department name and department email joined to the employee data set. Let's start a new workflow by adding our datasets will go to the in out Tab Dragon to input Dr Tools on, and the 1st 1 will be 3.1 point one employees, and the 2nd 1 will be department. Well, then go to the joint tub and dragon joined and you'll notice that there are two inputs in this one. A left and a right. Make sure that the employee is joined to the left and that the right important is joined to department looking in the joint tool. There are three types of joins. We can perform a left in Ah, and rights join. Let's go through what each of these mean. I'll start with the inner join because I think that's easiest. To explain in a joint means that the joint only returns records where it found matching I DS in both datasets. Therefore, looking at the department file, we have department ideas of 102 103 106 107 100. If we go to our employees fall, we have department ideas of 1234 and 500. Therefore, the ideas that match are only 102 103 100 we should only get back 17 records left. Join means that it will return the left data set, which is employees when it doesn't find a match between the two datasets. Because we know that there are 17 records that matched the data set from our inner join, we should see 13 records that don't match. In other words, the 405 100 department ideas, right? Join beans that it will return the right data set, which is department when it also doesn't find a match between the Dallas it We know that department I d 102 103 100 exists on six and 700 don't. So we should get back thes through records. If we add a browse to to the outputs off our joint tool using control shift B and let's update our joint all Teoh joined by a specific fields. And the common identify between the two datasets is department I d. So on the left imports, we have our employee file and will select department I D. And on the right, we have our department file and we're gonna link department I d with it. Let's run out work for and starting without inner join output in the middle. We should see 17 records that matched between the two files. So what was common between the two was department ideas 103 100. If we go to our left joint, we should see 13 records that didn't match. But it'll return the data from the employee file and 13 records here, and 405 100 worthy ideas that didn't match the department file and lost Lee with the right join. It will return the right data set, which was department information, and because department I D. 607 100 couldn't be found in employee. We have our two results here. Let's start a new workflow and import spreadsheets 3.1 point two. So we'll delete these ones here, Dragon to new import data tools and connect to 3.1 point two so have 2018 sales. Andi 2019 sales. Our first artis. It contains 2018 accounting data per department, so that's department I D. Revenue expenses, profit and the year off. The file the second daughter set contains the same daughter, but for 2019 because the two datasets are identical in schemer. In other words, they have the same columns, have the same departments. For record, we can use the joined by record position to combine the two datasets. So let's drag in the joint tool connecting birth. And we're joined by record position and out of browse tool to the output of our joint tool and run the work for you. Both sales figures are combined together into one table. We're now joined out Silas data by record position to make it a bit easier to read. We can add a select tool to the output of how joined Andi. Maybe give the's field names something more meaningful, sir. 2018 Revenue 2018 Expenses 2018 profit. We can probably remove year from it as well on and the right department i d. So we can only keep the 1st 1 poor at 2019 that the new 2013 expenses on 2019 Let's three run it and you can see here that per department idea. We have the 2018 and 2019 figures looking a lot more legible than it was before 19. 3.2 Union: the union tool allows you to upend multiple datasets under each other or vertically, either by field, name or record position. This tool is most useful if you need to consolidate or stitch multiple files together that have the same column name or composition referring to the illustration on the slide. If we start with the first picture, we have our 2018 cells on the left and 2019 cells on the right. They contain the same columns, but this time the columns are ordered differently in the 2019 file. Please keep note of this. What we're going to do in this exercise is dragging to input files. Neck them to the union to run the workflow, and we'll see that our datasets have been stuck under each other in the third picture. Let's start a new workflow by dragging in to input data tools and connecting to our 3.2 spreadsheets will connect to our 2018 cells at the top and 2019 cells at the bottom. So we have our datasets in here that contained the same combs. But remember this time the columns are ordered differently in 2018 its revenue expenses, profit year and in 2019 it's year profit revenue expenses. Please keep note of this. Let's go to the Joint up and Dragon, a union tool and connected to birth out datasets in the configuration Pain on the left. We have three main options off consolidating our datasets, either by a common name, common position or manually configuring the fields. Auto configuration by name will be the most relevant for us because remember how each of our datasets contained the same columns, but in different order. Well, using this option means it will automatically match the column names between our two sales datasets and consolidate our daughter. However, if we choose auto conflict by position, we have to make sure our two datasets have the same position. Otherwise, the output will be wrong. Lastly, there's the option to manually map the column names to each other, but we would need this for this video. Under properties, we can tell al tricks what to do If it can't find a column name to match against, we can either select era, which will terminate the process. Select warning, which will throw a warning message in the results window but still continue processing records or select Ignore, which will just process records without any warning. Let's select order conflict by name and leave properties as warning as we still would like to see the results, but at a browse tool by pressing control Shift B and will run out workflow with control are and checking the output of the union tool. We see our two sales started sets consolidated into one. If we were to rerun the workflow but have 14 conflict by position, we can see that our dad assert, has become mismatched through using the union tool. You now know how to easily consolidate multiple datasets. 20. 3.3 Append Fields: the Upend Fields tool allows you to take all the fields from a typically smaller data set and depend all its columns and records into another larger datasets, otherwise known as a Cartesian joint, referring to the illustration on the right. The first picture consists off an employee file and office location file. We want to combine both datasets to show that each employee works out of these two work locations. Therefore, the final data set after using the Upend Fields tool would be nine columns, six from employees and three from office, location and 10 records. Five employees, multiplied by two office locations gives you 10. Let's go through this in al Tricks. Let's start a new workflow by importing our 3.3 up in fields spreadsheets. Let's go to the in out Tab and dragon to input data tools and connect them to Howl 3.3 Pin Fields Employees Foe and ah, 3.3 a pin fields officers file. Once we've done that, will go to the joint tub and dragging the pin fields tool, and you'll notice that there are two import anchors here. The top anchor is for our main data set or target and the bottom anchor is for our smaller or source starts. It so will connect our officers data set to that one in the configuration pain on the left . We have the option to select D select rename and re size outfields. Note that he source fields are prefixed with source at the bottom. We have the option off how to handle large datasets. We can select for an error to appear a warning or just have it all processed. We'll leave it as error for an AL. But this is important in the future because if you had 1000 employees in your first start, is it and 10 office locations, you'll all of a sudden end up with 10,000 records, which is a lot to process. Finally, will add a browse tool by pressing control shift to be, and then we'll run out. Work for it with control are, and we now have a full data set that shows Theo office locations for each employee 21. 3.4 Find Replace: the find replace tool is similar. Toe of a look up in Excel, The tool takes a value, looks it up in a reference table and returns and associated value referring to the illustration on the right. We take the department i d. From the employee file. Look it up in the department file on Return The Department name, which you can see in the third picture. Let's start a new workflow by importing our 3.4 find. Replace spreadsheets so we'll go to the in out tab dragging our to import daughter tools. Andi connect to the 3.4 employees Fall and the 3.4 department file Well, then go to the joint Tub and Dragon. Our fine replace tool and the top anchor refers to the fines, data set or employees, and the bottom hanker is theory. Replaced starter, set or department. So let's drag department into the replace and in the configuration pain on the left. We have our find and replace section in find. We have the options to find Thebe ginning of the field. Any part of the field or entire field beginning means that it will find matching department ideas at the start of the field so the entire field doesn't have to contain what's being searched for. Any part of the field will look through the entire value for department I D. And if it finds a match at the left, middle or right part of the value, it will do a find and replace and entire field, which is what will be using means the department I d must match entirely for find within field will select from our find or employee data set department I D. However, it's not appearing in our list of values to choose from because department idea is listed as a number in our data set whilst finding replace only works with text. So what we'll do first is go to preparation and drag in a select tool here and a select tool here. And let's put out department I D. As a string and our department. I d here as a string. If we go back here, we can then now select department I D from our employee file and department I D within the fine value. So what's happening here is that we're looking up the department I d. Within three employee file finding that department I d in the department file. And once it's found that we're going to replace the department I d. With the department name. Let's add a browse tool to the end of our workflow with control shift B and run out work for with control are. And here we have our department I D column replaced with department name. If we go back to the fine, replace tool and select append and tick department name and run out workflow. Here we have our department name, which has been included at the end off our employee data set. 22. 4.0 Reporting Introduction: now that you've completed the 1st 3 chapters and understand how to input in output data clean and organized data for analysis and combine enjoying datasets together, we're now going to discuss representing and visualising data so that interpreting results and coming up with insights is easier. At the end of this chapter, you'll learn how to create interactive graphs, dashboards and customize their layout. The tours will need for this are interactive charts, layout, render report, footer and report head off. 23. 4.1 Interactive Chart: the interactive chart tool lets you visualize your data by being able to create bar charts , line graphs, scatter plots and pie charts you can interact with. Examples of these interactions include offer text appearing when your mouse hovers over a graph and zooming in and out off elements in the graph in the illustration. On the right, we have an extract of median house prices in Melbourne. Victoria Thes second picture shows our interactive chattel. On the third picture is Thiebaud. Hot Output. Let's start a new workflow so we can build. Our interactive chart will go to the in out tab and drag in the input data tool and then connect to our 4.0 reporting spreadsheet. For this chapter, there's only going to be one file as we're going to build upon this work for in each video . Well, then go to the Reporting Tub and Dragon, our interactive chart tool and let's run out work flor immediately with control all. Sometimes fields don't get detected or the graph would get output properly without running the work for once. The reason for this is that sometimes fields don't get detected properly or the graph foreign output properly without running the work for once. Well, then, at a browse tool using control Shift B Well, then go back to our interactive chart tool and click on configure chart on the Left, as you can see, were brought to a new menu to configure our chart. We'll start by adding a new layer and giving our layer names such as median house prices Q 1 2018 and the type of graph we want is a bar chart. So select that along the X axis would like to see the suburb name and along the why we'd like to see Q 1 2018 prices next in templates. We can choose to save this template if we want to reuse it, and it saves us having to recreate the chart. As you can see on the right, we have a preview of what our chart looks like, and if we hover over a certain elements, a piece of Ha Vertex pops up telling us the median house price for each suburb. We also have the option to zoom pan total spark lines and save. As a PNG under style, we can perform further customization, such as changing front properties, chart, orientation, bar colors, sizing and spacing, and several other customization is which I'd encourage you to play around with to get familiar with what you can customize just to quickly point out, you can't create a second chart using another layer here. Doing this just adds an additional layer onto your existing charge. If you want to add an additional charge, you need to drag in a second interactive chart tool to your workflow. If I were to update our layer here and probably add another bar chart and sure key 1 2017 you say that the 2017 daughter has been appended to the existing chart. So let's add a second chart. Let's do a scatter chart with the median house price against distance from the city, so it will drag in our interactive chart tool here and link it to our input daughter. And again, we'll just quickly run out work for and let's go to you can figure out chart. Let's give it and you lay Ah, median house price ah, against distance from city, Also like scatter on on the X axis, will show a suburb and on the Y kilometers from the CBD or Central Business District. Let's close that, and to join these interactive charts together, we need to add a joint all to our workflow. So we'll go to the joint tub on, let's move out, browse to away a bit and add a joint multiple tools and then at the second chart as an import to our joint multiple will join by record position and run out workflow. Looking at browse, we now have an easy way to visualize out daughter and also combine it into one page. 24. 4.2 Report Header & Footer: the reports header and report footer. A tool allows us to add a header and footer into our report to give it a professional look and feel. If you saved updates from work for a 4.1, you can open that up or feel free to open it from our course files. So picking up where we left off from work for 4.1 will now go to reporting and Dragon the report footer and header tool. I just moved this browse to out of the way. We'll start without report head on and then our report footer for report head. Oh, let's give this the name off House Price report. We can leave the date as it is, and I'll leave it up to you whether or not you'd like to use a local. I'll just use the default l tricks. One four Report footer. We can add copyright text and information text and let's update information text with our name. We've got out. Browse to at the end so we can run our work for and you'll see that our header and footer have been added all the way to the right, which I know doesn't look correct at the moment. But don't worry. We'll fix this in the next video with the layout tool 25. 4.3 Layout: the layout tool allows you to rearrange the graphs and charts in your report. If you followed workflow 4.1 and 4.2, you'll note that at the moment we have two charts, a report, header and footer on the wrong order, and the layout looks really messy. If you've saved workflow 4.2, you can open that up or feel free to open it from our course files. So picking up where we left off in workflow 4.2, Let's go to the reporting tab and dragon layout tool. We'll just move this browse tool out of the way a bit. Drag that in and in the configuration, pain on the left will start with layout. Murder the top, and we'll leave this as each individual records so we can see or charts or records under layout configuration. Let's change the orientation from horizontal to vertical, so it looks a bit more presentable in layout with, I'll leave that as 100% lout. Height we can leave is automatic, and I'll leave it up to you whether or not you'd like to see a border in separator between the graphs. But I'll leave it out in this example, and under Perot configuration, which is the important part here that's rearranged the order off our report elements by shuffling header to the top, having our first child underneath the second chart. Underneath that and finally the footer Let's run our workflow with control all. And as you can see here in the preview pain, we've got our header at the top. The first graph here, the second underneath it and finally the footer at the bottom. 26. 4.4 Render: the render tool allows you to export your reports to several file formats such as HTML, PNG, pdf or even Power point. If you've saved the updates from workflow 4.3, please open that up or feel free to open it from the course files. At the moment, we have two charts. Header and footer. Arrange properly in our report, and now it's time to export it. Toe a html page, picking up where we left off from Let's go to the reporting tab and dragging the render tool. You'll notice that there's no output for this tool, only an import anchor in the configuration. Pain on the left. We have rendered configuration and under output murdered. Let's choose a specific output file underneath it for output File will click the Save Icon here and select HTML files. Feel free to play around with the different types of files you can export to. To get an understanding of what each file looks like. Let's call this median house reports, and I'll put mine in a new folder. 40. We can leave everything else here alone and let's run out work for now. If we navigate while folder where we saved our HTML page on. Open it up. We can see here that the render tool has output it our report toe a html file which we can share with others. 27. 5.0 Transform Introduction: this is going to be a quick chapter in transform its the tab you'll use when you want to summarize or rearrange or data. If, for example, you want to know the total count or some off unique values in a column in Chapter 10 where we do a workshop together and string together, these tools will go through how to use transform to create a basic and advanced pivot table . 28. 5.1 Summarize: the summarized tool allows you to perform a range of summary calculations such as grouping , summing, count and string. Concatenation, in this example will calculate the total number of employees per department. As per the illustration on our slide, the first picture shows out employee data set. The second shows our workflow when we add Thesis. Um, Arise Tool on the third shows the total count off each employee per department. Let's start a new work for an import. Our 5.1 spreadsheet will go to the in out Tab Dragon, the import dot at all, and connect to 5.1 Summarize in here again, we have our employees details and which department they belong to. So we will now go to the Transform Tab and dragging the summarized tool and, in the configuration, pain on the left. Starting from the top. We have the option here of adding fields to summarize. So since we want to count the employees by department, let's start by grouping them by department I D. So we'll select department I d. Here at the bottom click on group by in the middle and next we want to count the number off employees for this, we can select any column to count, since all our fields have been populated but logically would use employee I d. So again we'll click on that click on the button in the middle and select count. Let's go back to our workflow and out of browse tool with control shift to be and then run out workflow with control. Our as you can see in the results pain, we have now summarize out data, which shows the total number if employees by department i. D. 29. 5.2 Cross Tab: the cross tab tool allows you to pivot and summarized data similar to a pivot table in Excel to provide some context. In the previous table, we only calculated one group of data on the vertical axis, which was department I. D. And we did one calculation, which was to count employees to take it one step further. In this example, we're going to calculate the average age of H role in each department, so we essentially have two groups of data now I e. The department I d on the vertical axis and the position off the employees on the horizontal axis. Let's start a new workflow by importing. Our 5.2 spreadsheet will go to the in out tab, dragging the input data tal and select 5.2 cross tab. We again have our employees hate jar file, but this time I've added a new column called Position to Determine. If an employee is a manager or analyst, will now go to the Transform Tab and Dragon, the cross tab tool in the configuration pain on the left. We have the option at the top to group our data along the vertical axis, so the field we want to group by is department I D. So we'll select that. Next. We have to choose the column headers that we'd like to see at the top and because we want to see it by position, will select position to be the column and the values we want to see our age so we'll select that, and the method for aggregating values will be average. So let's had a browse tool to the end with control shift B and run out work for with control. Aw, and as you can see, we now have a pivot table, which shows us the average age of each position by department. 30. 6.0 Parse Introduction: now that you've completed the 1st 4 chapters and understand how to inputs and output data clean and organized data for analysis, combining joined datasets together and visualize daughter to derive insights, we're now going to discuss parsing data or splitting chunks of data into individual fields . Some common examples will be covering are splitting A, C, S V and Jason two columns. The tools will be using in this chapter our text to columns Jason Pars and download. 31. 6.1 Text To Columns: the text to columns tool that's you split the string from one column into separate and multiple columns based on a delimit er. I find this tour to be most useful if I'm looking at a set of records in a table that haven't been split out. So if I have a data set with five attributes in it, I expect to see five columns in it. But if I'm seeing each record as one huge column, it means my daughter hasn't been split out, and I can use text to columns to do that for me. A good example of this, which are illustrated in the slide here, is the address field. We know that an address contains a street suburb, postcode and state. So why not split that up into four columns, using the text to columns who and using a comma as outduel emitter, we can see in the third picture pain that we can split our address into four different fields. Let's start a new workflow and import our 6.1 text to columns worksheet. We'll go to the in out Tab Dragon Out Input Data tool and select 6.1 Texaco homes. What we're going to do here is split out the address into four different columns, so the first column will be the address line. One second is the city, third postcard and forth country, and seeing that each value can be separated by a comma, we're going to use that as out delimit er to split the data into multiple columns. So let's go to the pause Tub and dragon. A text to columns tool. If you're unable to see the past tab, you can click on the add remove button and check pause here in the configuration pain on the left. We have the option to select the column. We want to split by which in our case will be address. We then have the delimit er that we'd like to split the column by which will be a comma. If you have multiple delimit er's you want to use, then you can just add them one after the other. Hell. If you want to split by tab, you have to use back slash T. If you want to split by new line, it's backslash in space is back slash s and space or tad is backslash backslash t underneath we have the option to either split by columns or split by rose, which will either split the data across multiple columns or multiple rows. Let's choose split by columns. Next, we need to define how many new columns were expecting to see and because we know that there are four line items in our address or split them into four columns. Next, we need to set how al tricks will react. If it finds more than four columns, we can either leave all extra text in the loss column, drop or remove extra text without warning, or just terminate and error out or select era as we only want thes defined number off columns. Lastly, in advanced options, you can choose to ignore delimit er's in quotes, single quotes, parentheses or brackets. For example, If you have a maybe product description field, which naturally contains a lot of commerce, you don't want to split that into a new column. Otherwise, you'd have an inconsistent number of columns, so let's add a browse tool to the end of our workflow with control shift B, and then we'll run out workflow with control. Ah, and here we have our columns split into four l tricks is giving us an area here because it's saying that there are leading spaces at the end off each of these values. What we can do is go to the preparation tub and at a doctor cleansing tool and move out. Brows over here run out workflow again and everything's green. The address field in our daughter has now been split into multiple columns. 32. 6.2 JSON Parse: the Jason Pars tool structures. JavaScript object notation text into a table made up off its key pair values. So it will essentially take a Jason object like we have in our first picture pain here and structure it into a table like our third picture pain. The key pair values are the Jason name and Jason Value String Records. In the third picture Pain, let's start a new workflow so we can pars adjacent object. We'll go to the in out Todd, and instead of using the input data it'll like we normally use will go forward Text input this time, and drag that into our workflow will also go to our course files and open up the Jason Powers file, copy the contents in the and paste them into our text input. Once we've done that will go to the developer tub, which is that the far right here and select the Jason Powers tour here. If you're unable to see it in your version of al tricks, you can go to the ad removed tools I can hear scroll to the bottom, go to developer and make sure you've got Jason Powers ticked. We will drag it into our work play and in the configuration pain on the left. We have the option to select which column from our input data. We want structure because we only have one field in our input data. It's going to be field one to the right of it. We have the option to include in output, which also includes the field name chunk of text in our output. But we don't want that in the scenario. And finally, we have the option to output values into a single string field, which means it will only show the Jason attribute, name and value. Or we can output it into data type specific fields, which will show us the attributes, name value and whether or not it's an inter foot or bullion who, at a browse tool to the end of our workflow with control shift B and run it with control, are and with single field selected, our daughter has been output into two columns 33. 6.3 Download: the download tool allows you to retrieve daughter from a U. R O, which can be used in our work, for I normally use this to download extracts from the Internet and use it for my input daughter. Before we hop into al tricks, please visit that you are l, as it contains, the daughter will need for this workflow. The girl has also been provided in the course files under 6.3. Once you've got the URL, let's visit the website to see what data it contains. Though this is our Jason follow that appears to keep either employee names or customer details. There are approximately 10 entries or 10 employees in here, and we're going to download this into our al tricks. What floor? Let's start a new workflow by dragging in the text input tool. Well, then go through our course files 6.3. Download URL. Open that up and copy the euro from here and paste it into our text. Input keeps. Note off the column name Coldfield one. Once we've done this will go to the connectors tab and drag inthe e download tool in the configuration pain on the left. We have the basic tab, which contains Thean put to put our your URL into referring to our text input tool. The URL field is fueled one, so let's leave that there. We'll leave in code you are, oh, text ticked as this will convert unsafe asking characters into a format that can be sent over the Internet. An example of this is Percentage 20 which represents a space in the output. We can choose to output to a string or a blob, and if you're only dealing with text, then we can select string. Otherwise, if you need to download images or binary large objects or blobs, then you can select blob. Lastly, if you want to export your output to a file, you have the option to export it to a temporary directory or to a specific file. Let's add a browse tool to the end of our workflow with control shift B and run it with control All and we've downloaded out Datta, which appears in this download. DATTA column Here. If we double click on it, a new window appears and we can say the contents off it again. Here 34. 7.0 Macro Introduction: this chapter is only going to be a quick lecture, as will cover the macro exercise in Chapter 11. The macro tools are created and added in tow work flows where a set of tasks need to be performed and repeated for a set of inputs. The four types of macros are standard batch it curative and location. Optimizer standard will be applicable for most use cases, and this is where it lumps up a set of tasks or tools into one tool, which is great for making your work. For more readable. Bachir builds on top of standard by requiring you to include a control parameter in your input. It's narrative also builds on standard to but will only keep functioning until a condition is met in Chapter 11 will be using the standard macro to find the exchange with the cheapest Cryptocurrency price. This is the perfect use case for macaroons because let's say I wanted to buy two cryptocurrencies and have the option off buying them from five exchanges. That gives me a total of 10 buying options manually. Getting those 10 prices will be tedious, so I want to use out tricks to find the cheapest price for those cryptocurrencies without having to create 10 parallel sets of work flows. Even though we're using the standard macro, this will give you enough information to easily work and expand onto the batch and iterated Mac Rose if you need. 35. 8.0 Workshops Introduction: in this section of the course. We're going to put everything we've learned into practice by creating three really cool work flows that will help you use al tricks independently and give you the confidence to start creating your own work flows to make these workshops relatable and easier to understand. Our structure them as business problems so you can see the bigger picture of what we're doing up until this point, except maybe for the reporting part, we've only been dragging in three tools per workflow. Thean put fart all the tour. That's the topic of the video and a browse tool. Pretty straightforward so far. Now we're actually going to string a lot of these tools together to solve a problem that you might encounter at work in the first workshop. Or he's into creating meaningful work flows by helping a finance team with a forecasting scenario which will involve using a few tills to split and consolidate files in the second workshop will step it up a notch by helping a supermarket with automating. It sells reports far, stringing together several different tools, such as data cleansing, formatting formulas, calculations Dada summary ization and charting. The third workshop, which includes back rows, will be the most advanced out of the three. We're going to build a crypto currency arbitrage robot that helps us find the exchange with the lowest price to buy and the highest exchange to sell at, so let's get into it. 36. 9.0 Workshop 1 Intro - File Splitting & Consolidation: in this workshop. We're going to take a hate our fall off employees details, split them into separate files. Biota limiter, inserts him diving into each file and then joined them back together without any context. I'm sure you're wondering, why on earth would this be useful? Well, let me add some context. Let's pretend you're an accountant in an organization working on a profit and loss statement forecast for the next financial year. In other words, revenue minus cost of goods minus expenses. The profit and loss statement is almost completed, but you're missing an important line item in your expenses. Your forecasted wages, salaries slash total employment cost. This is where Al tricks comes into the picture in order to complete this task. Hey, Jonah, have provided you with a file that contains the current salary details of each employee. Your job will be to split this data into multiple files by the business unit. Kahlan, you could do this manually, but if there are 100 business units that's 100 files, you have to split manually. You'll then have to send the respective falls toe. Let's say a senior manager in that respective business unit to fill in the forecast that spend once it's filled in and sent back to you, you'll have to consolidate all the files at the end from each business unit back into one file so you can calculate the total employment cost. So with that in mind, this workshop will be split into two videos. The first is splitting the file, and the second is stitching it back together. Once we've received our forecast from each business unit, Good luck and let's get started. 37. 9.1 File Splitting: so following on from the previous video HR have kindly provided us the file off salary details in the fall. We have employee I d, their first and last name and their current position. We also have the business unit code, business unit description and current salary. Our manager has asked us to split this file into individual workbooks by the business unit . Curd column Each fall will be sent to one of the senior managers in that business unit to fill in. The forecast would spend, sir. At the end of this, we should end up with eight different workbooks. We also need to include four new columns at the end of the file to forecast weather. Each employee will get a pay rise, commission bonus and fringe benefits. So for Step one, let's start by importing our HR file into our work. For we'll go to the in out Tub and Dragon, the Input data tool and connect to our HR file, 9.1 hr File Worksheet for step two. We need to add for new columns to the end off our file. We'll do that by adding a formula tool into our workflow. So let's go to the preparation tub and dragon formula. Let's go to the select column, dropped down to the left and add pay rise as one of the columns that we needed to add. We'll give this a data type off double with the size of eight and assign it value off. No, as we want the senior manager in that business unit to fill this in, we'll do this three more times for commission Bonus and Fringe Benefits Commission bonus and cream benefits. Trouble double and double for Step three will add one more column called file, and this will be the location off where we store our split sheets will give this a format of VW String. Leave the size alone and type the folder location in double quotes, along with the foul name and shape name. So I'll put mine in. See Dr Educate Workshop one workshop, one dot XLs sex sheet sheet. One. Now here's the important part because we want to split the sheets by business. You know we're going to add the business unit code. In other words, be your one or two etcetera into the far name, so we'll break up the text here with a double court and we'll add the BU courage in hell. BU code plus Excellent six. And for sheet name. Well, sir, put would be occurred. Let's add a browse to with Control Shift B and run out work for And we've successfully added our four columns here and our new fall column. Okay, so the final step is to export all of this into eight different spreadsheets. Let's start by dragging the output data tool into our workflow and connecting that to the formula tool in the configuration. Pain will go to file and save this as a spreadsheet. We'll give this an arbitrary name. It's not going to matter because it will be over, written by the far name that we created in the formula Tool. Under output options will select over I fall. If it already finds an existing one and undertake file table name from field, we're going to change the entire fall path for feel, and we're going to replace it with the file column we created in the formula tool. Once we've done that, we can run out workflow, and if we go to this folder here, we will verify if our records were written successfully, and there we have it. How HR file has been split into eight different falls and it's ready to send to have senior managers to fill in. 38. 9.2 Workshop 1 - File Consolidation: in this workshop, We're going to assume the senior managers have filled in all our spread shades and email them back to you. So I'll leave it up to you as to whether you want to fill in the sheets yourself. Or you can download the populated sheets attached to this video. In any case, our sheets have been filled in and returned, and they're now in one folder. At the moment when we opened each individual sheet, we can see the total salaries, wages and fringe benefits for business unit. But now we want it for the entire company i e. All business units. We could easily open up each file and manually copy and paste the details into one sheet. But that's going to be really tedious. So let's combine them back into one file so we can calculate the forecasted spend Let's start a new workflow and dragon input data tool, and instead of looking for a specific file, we're going to look in a specific directory. So this directory is where I put all my bu sheets and I'm going to put in the u wildcard dot XLR sex. What this will do is grab each file in the workshop. One folder that starts with the letters BU will press for fresh and make sure that our table query line item here is empty and will now go to the joint tub on Dragon. The Union Tool. If you recall from video three point to the union tour weapons multiple datasets stacked under each other, either by field name or record position. In this instance, we want to use order. Conflict by field name finally will go back to the out tub and dragging the output data Tal and select a folder to write out files to I Want to Keep It in the same photo Workshop one . And I might just give it a far name off combined business units Doctor Excise sex and call it sheet one Under output options. I'll select overwrite file if it finds an existing one, and if we run out workflow and check out photo location, we should see our consolidated fall. He'll, with all the information that we populated or the senior managers populated 39. 10.0 Workshop 2 Intro - Supermarket Report: in this workshop, we're going to create a supermarket, sells report with graphs and charts. However, before we created, we need to clean the data set and calculate totals. Since it only comes with unit values, let's have a quick look at the data set in here. We have the grocery name being sold, which I'll or section. You can find it in the quantity sold cells, price purchase price and other cost of goods sold, which may consist of depreciation, shelving and freight to the rights we have four columns that are know that we need to calculate the 1st 1 is total revenue, which will be units sold multiplied by cells. Price. Total cost of goods sold, which will be the purchase price plus other cost of goods multiplied by units sold Profit margin, which will be our total revenue minus cost of goods sold, divided by cost of goods sold again and finally we're going to determine if the item was selling is actually profitable by checking. If our profit margin is more than 0% it might not be immediately apparent, but there are also duplicate rose and items. We can see it here for mushrooms and ketchup, and there are other items we need to remove two under section. We also have a lot of now values which will need to find and replace from the supermarket items table. And then we're going to round up the units sold as there appears to be a rounding error. You can't really sell a fraction off Asparagus or Brookly. And finally, we're going to do some calculations and formulas for the totals. Let's take a quick look at the report will be creating. Our report is going to consist of three charts, which compare the revenue to cost of goods sold by each section. So the blue bars show our revenue and the Orange Show our total cost of goods sold so we can see that particularly for cheese. We seem to have a pretty healthy margin here. The 2nd 1 will be the profit margins per section in a pie graph, and the third will show how many items per section are actually profitable. So under fresh fruits, we are selling 15 fresh fruits, but only 13 off them seem to be profitable. To understand how to get here, let's have a look at our workflow Okay, I know this looks pretty intimidating and it looks like a pretty big step up from our first workshop. But I promise you, at the end of it, you're going to be like That's actually not that bad. To make this digestible and manageable, we're going to break this workshop into three parts. The first is cleaning the data set, as I mentioned before, by removing duplicates and Knowles and rounding up the units sold will also be calculating totals. The second is building the charts out for our report, and the third is creating a pivot table with a grand total. Again, I know this looks like a lot to absorb, but remember, we're putting everything we've learned in the previous chapters into practice so you can get a good understanding of how to string or these tools together to perform process automation. So let's get started 40. 10.1 Workshop 2 - Clean Data: as discussed in the previous video, We're going to kick off this sells report by cleaning the daughter set from Ju pickets Knowles incorrect rounding and then perform some basic calculations. Let's start by importing our supermarket daughter spreadsheet so we'll go to the in out tab , dragging the import dot it'll and connect to 10 point or a supermarket daughter. Since we've already walked through the daughter set in, the previous video will dive straight into removing these duplicates. Let's drag inthe e unique tool from the preparation tub and connected to our input data. TAL. You'll notice that there's a you and two D output anchor, which means unique records will go to you, and duplicate records will go to D in the configuration pain. Let's select all items. The reason why we're selecting or feel it is because it's perfectly possible to sell an item off similar description. But over two sections or isles, for example, we have cottage cheese in dairy and another cottage cheese in the chief section. If we select all fields, it means birth. Cottage chases stay since their in different sections, but if we select the item only it will remove the second version off cottage cheese. However, since we want to keep both, we're going to select all unique fields. Let's add a browse tool to birth, unique and duplicates for easy viewing. So it's press control Shift B, and then we'll run our workflow and check the outputs. We should have 90 unique records and nine duplicate records. Now it's time to update the section. Kahlan. What we're going to do is similar to an XlV. Look up or look up the item in our supermarket data into the supermarket items table spread . Shoot. So let's Dragon another input data tool and connect this to al supermarket items. Table spreadsheets. As you can see, this contains a clean list off. All the items in each section will now go to the joint tub and drag in a find replace tool and will connect the unique output anchor from unique into the find in port Anchor from our find. Replace tool. Well, then drag the input data anchor from supermarket items table into the replace anchor in the configuration pain on the left. We want to match the entire field and find the item within our supermarket data and compare it to the item name in the supermarket items Table Daughter. Once it's found a match, we then want to upend values to the end off the data set, and we want to a pendant to the section column. Let's at a browse to with control Shift B. Let's run our workflow with control are and now we have a section with no more knows. But it's been appended to the end, and it's called Section two because we already have a column called Section. We want the section next to the item, so let's fix this by going to the preparation tab and dragging in. The select tool in the configuration will move Section two all the way up, so it's under section Willen Tick the original section and renamed section to Toe section. While we're here, let's update the profitable Kahlan to type into 16 because if an item is profitable, I wanted to return a one hints. This needs to be an integer. Let's at a browse to with control shift B and run out workflow with control are and you should now have section completely populated. The next thing we need to update is three units sold column. We're currently selling fractions of items when we should be selling whole numbers. So staying in the preparation tab, let's drag in a formula tool and connected to the output off our find. Replace tool. Let's put a formula in four units sold and will select the function off around. If it doesn't appear in your list here, you can type in round and click on that. The first Parameter X is asking us which column we want around. So we'll click on The Associated Variables button here and select units sold. The lost parameter is asking us to which multiply we want to round up to. We want a multiplier of one which will make it a whole number. Let's on a browse tool with control shift B and run out workflow with control off. And now we have our units sold nicely rounded up. So sticking with the formula tool, we're going to calculate our total revenue, total cost of goods sold, profit margin and that profitable flag. Let's add a new formula for total revenue, which is going to be the sales price multiplied by units sold who add a formula for total cost of goods sold, and that's going to be our purchase price. Plus other cost of goods sold moats applied by the units sold and then for profit margin. This will be calculated as profit divided by total cost of goods sold and profit is calculated, with total revenue minus total cost of goods sold, divided by total cost of goods sold. And then lastly for profitable, which we want to be. A one or a zero will use the if function, and the condition will be. If profit margin is more than zero, then output a one. Otherwise, output no, let's run out workflow with control. Aw, and now we have our total revenue. Total cost of goods sold, profit margin and out. Profitable flag calculated. Let's take a break there because that was a lot of information to absorb in this section of our workshop. We've cleaned up our daughter by removing duplicates, filling in Nell's, rounding up our numbers calculating totals and even figured out if an item is profitable now that how dot it is clean and ready to use in the next section off, this workshop will go over creating our supermarket report with charts 41. 10.2 Workshop 2 - Reporting: in this section of the workshop, we're going to build out our reporting and charging components. So picking up where we left off from in 10.1, let's actually start by quickly tidying up our workflow, so we'll probably just movil the browse tools to the top Here. Here we go that looks a bit more legible, and then we'll go to the Transform tab and drag in the summarized tool. Make sure to connect this to the output off our formula tool, he summarized. Tool allows us to group some count and do some basic processing on our data. Let's start with adding section into our summary and select group by as the action. We also want to report on total revenue, total cost of goods sold, profit margin and profitable flag. So let's add them in two for the totals and profit margin will select son and then for items or select count. And finally, for profitable will choose count non null because the values are only going to be one or no had a browse tool to the end of this and run the workflow, and you can see that we have our pivot table here which summarises theory, revenue, cost of goods sold and profitability details. So now it's time to create some reports. Let's go to the reporting Tub and Dragon. Three Interactive charts. We'll start with the first chart by creating a comparison off total revenue and total cost of goods sold. So let's add a new layer and give it a title off Total revenue. Five. Section We want the bar chart. The X axis will be our section on the Y Axis will be total revenue well, then create a new layer and call this one total cost of goods sold by section and will select BART shot again. X Axis is section, and then this time will choose some off total cost of goods sold. As you can see, we have our two columns side by side in the chart. Let's close this window and open up. The second chart for this chart will title it as profit margin by section and make it a pie graph and add section as the labels and the profit margin as the details in our pie graph. For the third chart, we want to show the number of items per section and how many of them were profitable. So let's click into it and add a new layer, and we'll call this one number off items well selected as a bar chart and the X axis will serve as our section. And why will be the count off items? We'll add a second layer and Courtis number off profitable items will choose Scatter this time, have section again on our X axis. And why will be the count off profitable items? Now that we've created three separate grafts, we now need to join them together so we can put them into one report. So we'll go to the Joint tab and dragon a joint multiple tool and connect this to our interactive charts in the configuration Pain on the left, who joined by record position. And then we'll, sir, drag in a report head on and report footer to give a report a more professional looking field. Once we've done that for, then go to lay out and configure our report to be vertical orientation and we'll put out header at the top chart one chart to chart three and then footer well, then finish off this section of the workshop by adding a render tool and out putting it to a HTML file. Oh, put mine in my course files call. It's 10 reporting and then we'll run out workflow and check the output off our report. And we now have our supermarket report with our charts completed. This pretty much finishes up our workshop, but I've added an extra video in here for creating a pivot table with the grand total. At the bottom, you'll notice that he summarized Tap doesn't include a grand total, so I'll demonstrate a work around in the next video. 42. 10.3 Workshop 2 - Pivot Table: All right. So this is the bonus material off the workshop, which I thought might come in handy if you ever need to show the grand total of a pivot table as thesis. Um, arised Tour doesn't have it. So this technique is more off a work around, but it still gets the result. Adding to our workflow from 10.2 will add another summarize tool parallel to our 1st 1 And we'll include all the same fields as the 1st 1 here. But this time we're not going to include grouping by section. But we'll keep everything else here. So let's re add it down here. So if a total revenue colleagues and profit margin will do a sum for item will do count and then profitable was count non null. Because we're not grouping by anything this time. We should get the grand total off everything. And if we add a browse tour here and run how workflow it shows in one line item the some off our total revenue, Coke's profit margin and then the count off items and count of profitable items. Now what we want to do is add the value grand total somewhere along this road and perform a union joint back to our summarized data set so that the grand total sits underneath hers so it will create a new column via the formula tool. So we'll go to preparation and drag in a formula tool. And let me just rearrange this. We're dragging the formula tool and create a new column called Grand Total and give it a Valley off grand total in double quotes. Well, then add a union tool and connected to our formula and other summarised table. If we add a browse tool and then run out workflow, we can see that grand total appears at the top here. With the heading and the grand Total Value appears at the bottom to tidy up the order, so that grand total appears in the first column. Instead, let's drag in a select tool from our preparation tub. Let's move this to the bottom and will move grand total to the 1st 1 here, and if we run our work floor again after adding a browse tool, we see grand total appearing at the bottom. We now have a pivot table with grand total innards. This concludes our second workshop and probably the first example off stringing all the tools together. I want to say congratulations and awesome work on making it this far. Feel free to take a break and get ready for the next workshop because we'll not only be stringing more tools together, but will also be utilizing the batch macro feature. 43. 11.0 Workshop 3 Intro - Crypto Arbitrage: welcome to the third and most advanced workshop where we will be creating a Cryptocurrency arbitrage robot. If you already understand Cryptocurrency and arbitrage, then off the bat, this is going to make a lot of sense. If not, let me give a quick introduction. Cryptocurrency, at a high level is much like a regular Fiat currency like AU de USD and yen, but it's only stored digitally and doesn't have a physical form. Cryptocurrency can be bought and sold on exchanges similar to how shares are traded on the share market. Some popular Cryptocurrency exchanges are Finance Beatrix and COO Coin. The price of most cryptocurrencies are usually shown as relative to the price of one Bitcoin or in USD. For example, you may see other Cryptocurrencies like ripple with 0.981 and light coin as 0.8 something something and Bitcoin cash with the price of 0.43 except trough. These prices are relative to Bitcoin. So, for example, if Bitcoin cash is worth approximately 4% of one Bitcoin and if we take one divided by 4% we can see that the price of one Bitcoin is roughly equivalent toe 23 24 Bitcoin cash arbitrage is taking advantage of a price difference, often asset between two exchanges. For example, you might see Bitcoin on finance for $1000 Bitcoin on bit tricks for $1500. Therefore, the arbitrage opportunity would be buying Bitcoin on finance and then selling it on bit tricks for a profit of $500. The workflow will be making today will identify arbitrage opportunities but won't do the trades for you. So because we're dealing with the potential sensitive topic of money before moving on, I'd like to mention that this video is purely for learning purposes only specifically to demonstrate how to utilize the download and Jason Pas tools and how to use the standard macro. We discuss these in Chapter six and seven, but haven't yet put it into practice. This video isn't financial advice and has not made with the intention for you to make money , and I highly discourage you from using the work for for that intent. If anything, you'll most likely not make any money at all. From this. At the time of making this video, I don't own Bitcoin, but I do own several other Cryptocurrencies. However, I don't own any of the example Cryptocurrencies will be talking about in the work for you. If you decide to use this workflow for trading, I'm not responsible for any of your actions, losses or gains. I'm going to say this one last time. Please don't use this workflow to trade Cryptocurrency. This is purely for learning only. So with that out of the way, for starters, you'll notice our source files contain a version one and version two. And that's because that the first part or version one off this workshop, we're going to build out workflow and have a working product. The second part will be converting this tour. Macro, let's have a look at what this workflow will look like. I know this looks like a lot to take in, so I've broken the workshop up into several components, so they're easier to digest The first component. Selecting your exchanges and currencies will start with two text input files. The first is the exchange Charles and the other one is for listing their coins. We want to research. We're going to combine these two imports together and replace parts of the exchange. You are Oh, without coin name will download the prices which will be returned to us in a table and then will identify the best price for each coin. Finally will determine if there are any worthy arbitrage opportunities. The end result will look something like this. Here we have the name of our coins, the cheapest exchange to buy them from the price and the exchange to sell them at with their associated sell price. In this example, like coin, trun and ripple don't have any where the arbitrage opportunities so there's no action to pursue. The second part of this workshop will be taking the workflow and converting it to a macro which will look something like this. This blue dot is a macro import tour that references theatrical macro, which is more or less al Version one workflow his al macro before moving on. If you're already familiar with what a P I Jason bid and ask prices are and how arbitrage works, you can probably skip the next video, but if this is new to you, I highly recommend watching the next video to provide context and background about what's happening 44. 11.1 Workshop 3 - Background and Jargon: So before we select our exchanges and currencies, I want to explain some of the jargon I'll be using as it can get pretty confusing. In order to download coin information, we're going to be using an A P I call, which stands for application programming Interface, which means a particular website or company is providing us access to specific features or functions within their application, instead of giving us access to the whole application itself in this example and exchanges providing us their price information without giving us access to their exchange as a whole to bring this toe life. Let's take the by Nan's a PR girl and look up the price of Bitcoin against the US dollar. The by Nance Exchange has provided us the bid and ask price with all these curly braces and quotes, which is actually a format called Jason or JavaScript object notation, which is another way of structuring data. Think of Jason as an alternative to XML. What we want out of this, Jason is the bid and ask price with bid, as the name suggests and what you would normally do at an auction. You're submitting a bid to buy Bitcoin at $3783 with Ask if you already hold Bitcoin or something else you want to sell. You would normally submit an asking price for it and looking at the ask price here, people have submitted orders to sell Bitcoin at $3783 so the price difference between bid and mosque is $1. If you can't remember which one is buy or sell, I always remember it as bid and by birth, start with B. Therefore, ask is self. The bid price will always be lower than the sell price. Therefore, if I wanted to buy Bitcoin, I can buy it immediately at the asking price which will be more expensive. Or I can submit a bid, play the waiting game and hope someone sells it to me for the price that I want. Depending on the bid price you've put, you might only have to wait one minute or one day or even longer to get your order fulfilled. Sometimes your bid will never get fulfilled because you may have put an unrealistic bid off $1 instead of something closer to the current prices to put it into perspective. Let's go through the scenario off buying Bitcoin at the ask and bid price and then selling Bitcoin at the ask and bid price. So using the example at the bottom if we were to buy Bitcoin weakened by it at the bid price of 3000 and the ask price off 3500 now, buying Bitcoin at the asking price is more expensive than the bid because that's the price the seller has set it at. However, I'll be able to purchase it immediately. Buying Bitcoin at the bid price is cheaper than the asking price, but there may be a delay to purchasing it as it requires someone who already earns Bitcoin to sell it to you at that lower price. Now let's go through the scenario off selling Bitcoin. If I were to sell Bitcoin at the asking price, I get a better R a y. But I may have a delay in my cell as it requires someone to buy it at that more expensive price. Selling Bitcoin at the bid price has worse r a y, but I'll be able to sell it immediately. So because arbitrage requires quick reactions and for orders to be fulfilled immediately. We need to identify the exchange with the cheapest ask so it can be bought immediately and then identify another exchange with the most expensive bid so it can be sold immediately. Putting all that into context. Now let's go make out work for you. 45. 11.2 Workshop 3 - Select Exchange & Currency: in this section off the workflow. All we're doing is getting our euros ready for the download tool to download. Current prices were going to use to input files one which lists the exchanges we want to look up. And the second list the coins were interested in for arbitrage. We're then going to do a Cartesian joined using the pen fields tool By combining all columns and records together, you notice for the exchange input. I've put a placeholder coin name called Temp One. This is where we're going to replace the part of our U R L with the coin name from the second input file. So for this example, I'll use X r p and the girl will say a p i dot finance dot com blah blah, blah x r p btc beatrix dot com equals BTC hyphen X, UPI and AP I don't cook coin baba bar X R P hyphen BTC. So let's start by dragging in to text input tools into our new workflow. I've obtained the A P i u URLs by going to the documentation from each exchange for your convenience. I've placed them in the text file in our course files under Chapter 11 will copy those and paste them into our text input, and we'll call this girl into the right will put their exchange names in there. So this one will be finance tracks and your point. For our second text input, you can insert the coins that you want to look up. Just as an example, I'll be using Ripple, light, coin and else their symbol names. Ah x r p o T c and G. O s. Now let's Dragon and a pen fields tool from the joint tub. Let's rearrange the column order so that name is at the top. You are always in the middle and exchanges at the bottom in terms of size. I think I've seen coined names upto five characters long, so let's just give it a bit of buffer and may be used. 10. Girl I know our current exchanges have pretty short you are ALS, but in case we want to include an exchange in the future with a really long you are oh, weaken. Probably bump this up to 1000 and exchange names are relatively short, but who used a size of 100? We'll add a browse tour to the end of this, And if we run our workflow, we'll see our upend fields. Tool has combined birth text inputs, but our your girls ah still showing temp one. Let's update this now by dragging in a formula tool from the preparation tab. The output column we want to update is you are also will select that, and the function we want to use is replace. Let's update the parameters. Firstly, with the string that we want to look up, which is Earl, we'll double click on that and locates the URL variable, followed by the delimit er or Tex We want to find, which is TMP one, and then the text we want to replace it with will be the coin name, So we'll double click on that and select name if we run out work floor again. After adding a browse, we can see that's how delimit er temp one has been replaced without coin names and euros are now ready to download that price information 46. 11.3 Workshop 3 - Download Prices: has demonstrated in video 11.1, where we used the by Nancy, A. P I to download price information in Jason format. We're going to do the same thing here, but for all exchanges, we'll take it one step further and pause that Jason into two columns. One column where we have the attributes name and another column with the attributes value. This is otherwise known as a key pair. Let's start by going to the connectors tub and dragging in the downward tool. Now configuration for this one is pretty straightforward. We just need to tell it which field has three euro we want to download, and in this example it's Earl. If we run out work for after we add a browse tool we now see to new columns have been added to our table download data, which contains our price information in Jason and the download headers, which tells us if we got a successful response, 200 means we did, because we only want the buy and sell prices from download data i e. The bid and ask price. We need a way to extract those two pieces of data and the easiest way to do that is to add a Jason Pars tool from the developer tab. What this will do is take each of the attributes names like symbol, bid price, big quantity, etcetera. And put that into one column and then take their respective values and put that into another column, creating a key pair value. This might be hard to visualize, so let's just drag in the Jason Pars tool and will specify which field has our Jason doctor in it, which is download data at a browse, tool and run. How workflow. And as you can see, the Jason name and Jason Values String have been created containing our attribute, names and attributes values. Now that we've paused out Jason into a table format, we can now extract the bite and sell prices with a filter tool, which will do in the next video. But before moving on to that, let's just quickly tidy up our daughter. Although I can't see any anomalies in here, sometimes random white spaces can be added in Jason, so let's just drag in a dark cleansing tool from the preparation tab and remove or replace knows with blanks and zeros and tick leading and trailing Whitespace is then lost. Lee will get rid of the U. R L and download Hedda's column. Since we don't need those anymore, so we'll go to the preparation tab and select Select Tool and until the U. R L and download headers who had a prize tool again. And if we run our workflow, we should end up with a table like this, which is going to help us find arbitrage opportunities. 47. 11.5 Workshop 3 - Find Arbitrage Opportunities: In the previous video, we identified the best buy and sell prices, and now we're going to combine all that information together to determine if there are any good arbitrage opportunities. Let's start by combining out data by dragging in a joint multiple tool from the joint tab will join the two unique anchors into the joint multiple, and we're joined by record position, since our sell and buy prices are filtered and sorted in the same way, so our coins will always be in the same position. Therefore, joined by record position can be used in this instance. Let's update the field names in the configuration pain. So for input one, which contains our immediate by price information, will rename Input One exchange, too, by from well, by from this exchange and then for Jason Value String. This will be our immediate buyout or immediate by Price import to exchange or call this still to or sold to this exchange and then for Jason Value String here. This will be immediate, so act, since we don't need the's fields anymore. Jason Name in input to name in Input to and Jason name and input one. We can untech those And then, lastly, we'll update our data types for Jason Value strings to double as they're going to be numbers and not text. And then we'll out of browse tool to the end of our work for. And if we run, I work for, we can see that our by and so data have now bean combined in the same road. Now we're going to finish this off by writing a formula to determine the price difference between ed buy and sell as a percentage and also a text field to advise on the best course of action. So what's dragging a formula tool from the preparation tub? And we will create a new output column cold return, and this is going to be ah, immediate. So at minus immediate buyout, we're hoping to see a positive number from this calculation now that we have the price difference where you want to see it as a percentage. So we'll add another output column and call this one turn percentage will at a round function and fill in the parameters here. So X will be our return divided by our immediate buyout multiplied by 100 and we want to round it off to a multiplier of one which will give us a whole number. This is giving us an error here because we've got the string data types that will change this to double change this to double, and we'll add a browse tool, toe our workflow and run it. And there we have our return details. So there was a problem with the recording here, but what I want you to do is create a new output column with the formula tool and name its action well, then insert and if function into it by clicking on the FX icon and locating the if function . So our C condition is going to be returned. Percentage is more than or equal to nine. And if that's true, then we want a text output identified otherwise, who returned? No job. Let's run our workflow and see if any arbitrage opportunities have been identified. So it looks like there were no arbitrage opportunities as all exchanges have their coins similarly priced. There you have it. You now have a working product that identifies price discrepancies between Cryptocurrency exchanges. I know the results may look a little underwhelming, but you can research other coins to find arbitrage opportunities. Congratulations on making it this far. I know this workshop contained a lot of new concepts in jargon, which can be pretty confronting, but I hope you learned a lot from it and can apply it to your work. So as I highlighted at the start of this video, it's most likely that you won't make any money from this. And at the time of recording this video, the market isn't doing very well, and this is purely for learning purposes. Only in the next video, we're going to convert our workflow into a macro and dynamically control the arbitrage percentage instead of a static 9%. 48. 11.4 Workshop 3 - Find Best Prices: now that we've downloaded the Jason and positive, we need to extract the relevant buy and sell prices such as here, here, here, here and so forth. This is where I'm going to split the workflow into 21 part toe identified the best immediate by Price and the other toe Identify the best immediate sell price. Let's start with identifying the by price. If we look at the Jason Name field, we want to extract the field names where it contains, ask or sell, in other words, buying it immediately without having to bid for it. And it looks like we'll have to account for one more condition where I'll ask or sell doesn't contain the word quantity or q t y as finance attributes, names, birth use, ask in their price and quantity attributes. Keeping those conditions in mind, we have to filter only by ask cell and not quantity. Let's drag in a filter. It'll from the preparation tub, and we're going to write a custom filter using the contains function. We want to filter only by Jason Name, so we'll type in contains the conditions we want to set. If the Jason name contains, ask yeah, Jason name contains Cell and Jason name does not contain Q T. Why, If you have other exchanges you want to add that are outside off this workshop, you may need to update these filter rules if they use different names. Let's run our workflow and check that we've filtered by. Ask and sell prices without quantity. Flat out crowds. Tools here run out workflow. Awesome. We now have the asking price for each coin on each exchange. The next step is to get the cheapest price of each coin and removed everything else, so we only end up with three records to do. This will use a sort and unique tool. So let's start by dragging the sort tool in from the preparation tub and we'll sort by name . So we get the coin names from A to Zed, and then we'll sort by Jason Value string in ascending order. So we get the prices from lowest to highest. We'll add a browse tool to our sort, and if we run out work for it, we can see that we've sorted each coined by the cheapest asking price and because the first record of each coin will be the cheapest since we've sorted it that way, we can now drag the unique tool and select the name column as the unique field we want to filter by. That's out of browse to to our unique and duplicates. And if we run out work for me. We now have three records, which each show the cheapest asking price for each coin. Now that we've identified the cheapest asking price, let's copy these three tools to I turn to fire the most expensive bid price. I'll just quickly rearrange these hours, and we'll drag from the output of our select tour to the incoming off. Our filter tool will update the filter to look for bid and boy, and we'll update our Jason Valley string here to be in descending order, which will give us the expensive prices first. And then we leave the unique tool alone, and we'll add a browse tool to the end of it with control Shift B, and we'll run out what flu with control. And we now have three records with the most expensive bid price. We're almost there. In the next video, we're going to combine these prices back together to determine if there's any worthy arbitrage opportunities 49. 11.6 Workshop 3 - Convert To Macro: in this video, we're going to convert how arbitrage workflow into a macro with the ability to dynamically set out arbitrage percentage opportunity. At the moment, we haven't statically set to 9% but at the end of this will be able to dynamically choose the number before we run the work for Before we dive into it, I'll quickly explain what's going to happen. We're going to cut a majority off, out work for and girl From Version one, which looks like this toe aversion to which looks like this. What you notice here is a blue dot that we haven't seen before. This is our macro, which we're going to build in the configuration pain. We have a list of questions which are go through in more detail soon, but you'll see at the bottom here we've enabled a capability to select the arbitrage percentage to whatever we want, so let's kick off converting our workflow toe a macro. We'll start by highlighting all the tours between the first formula tool and the last formula tool by selecting everything with control A. And then we'll just do select holding control the input tools Thea penned on the Lost browse tool. We will cut that with control X and paste this into a new workflow. Well, then go to the interface tab and dragon, a macro input tool at the front and a macro output at the end of our workflow. How macro import tool is producing an era. So let's quickly configure it in the configuration pain. We need to specify the text, input and anchor abbreviation. Everything else can be left alone. The text in what we need to provide are the column headers from our upend fields here, which are going to be named Earl and exchange. So let's type them in here name you are l and Exchange. The anchor will call. This is just input and the anchor abbreviation will be I. Everything else is left alone. So we're still getting an error without formula Tool here missing incoming connection. So we'll delete this one there and connected here and can remove that one. And we go but save our work for now with control s. And let's call this maybe arbitrage Carter. And be sure to save it as a don't y x m c. Well, then go back to our chopped up work, flirt and add our arbitrage macro to it To do this wall, right Click on the canvas Select insert, Go to Makro and choose out Arbitrage Macro. Since we gave our input, anchor the name off, I weaken, Say appear on the left of it to give it an output name. We can go to our macro back here, click on output Name this output on the anchor abbreviation of Oh, save it! And we get a message saying that our macro has been updated and our output anchor appears here Well, at the browse tool to the end of this would just drag that one back here and run out workflow toe check that everything still working and awesome looks like everything's to working. Now we want to dynamically set the arbitrage percentage without having to quick into our macro and modifying the formula tool. We wanted to appear as a question in the macro, which means it will appear in the configuration pain on the left to do this will jump back to our arbitrage macro and add a numeric up down tool on top of the formula tool at the end here, but don't connect anything just yet. Let me just remove this, browse to here and drag this one in in the configuration pain on the left. We need to configure the question that's going to be asked from our macro. Let's use something like Testify arbitrage percentage. We'll set the minimum arbitrage to 1%. Leave the maximum as 100 leave increment as one. Let's put a default of 9% and leave decimal places as zero. Now that we've configured the numeric up down tool, we can drag the Q or question output into the input off the formula. Tool automatically and action tool appears in the numeric and formula tool, but it's asking for the action to be configured in the configuration pain on the left. We want our action type to update a value, which is going to be that static 9%. So we'll leave this action alone in this box. Here. We need to specify the value or attribute we want to update, which again is the 9%. If we click through the formula fields and look inside action, we see our expression for that 9% there, but we can't select it, so to work around this. What we need to do is select the formula tool and add a new output column called Operate. And we'll set this to a daughter type of double and in the expression here, will insert nine. We'll then update our if function in action, to check if our return percentage is more than our newly created operate. If we go back to the action tool, a new formula field has been created, called Our Braid. The value we want to update in this expression is nine. So we'll click on that. Let's save this marker and go back to our arbitrage workflow, and we get the message saying that Al Banker has been updated. We can also see that the arbitrage percentage now appears as a question in the macro input , and we're still getting an era in here. Unknown variable rate. Let's go back in here and see what's wrong. A new invariable Try moving this up one to see if that makes a difference. And yet that removes the era will go back to our work for, And if we run out workflow again, we can say that everything still working and we can even customize the arbitrage percentage say 1% fantastic. Everything appears to be working. We've now converted our workflow into a macro and included a way to dynamically change the arbitrage rate without having to manually open our macro and update the formula to manually . 50. 12.0 Thank-You!: if you've made it this far and completed the course, I just want to say thank you so much for purchasing this course, and I hope you've learned enough to start independently building your own work flows. Although we didn't cover all the tabs in the ribbon, you should know enough now to understand how to drag tours into your work for and string them together to solve a problem. If you have any questions or comments, please feel free to leave them in the respective video, and I'll get back to you as quickly as possible. Alternatively, you conjoined my Facebook group to connect with other like minded individuals. Follow me on Twitter to get the latest updates or subscribe to my YouTube channel. Lastly, you can visit me at educates dot com, where I also offer Free Excel consultation. If you need personalised spreadsheet assistance, thank you so much again, and I hope to see you soon