In this tutorial, we will create an automation that extracts a table with today's exchange rates for the Euro against other currencies from the European Central Bank website. We keep historical exchange rate data in an Excel workbook and we want to add this data to it daily. Because the format of the data extracted from the website is different from the one in our Excel workbook, we must also edit the extracted data in Excel to apply the right format to it.

We will create a project and start by adding an Excel File Card to indicate the Excel file to use. We will then click Data Extraction in the StudioX ribbon to start a wizard that will help us extract the data from the website. When we complete the wizard, two already configured activities will be added automatically to our project: an Application Card activity that indicates the web page and an Extract Data activity that extracts the data. We will then add a series of Excel activities to transpose the data, format the data as dates or numbers, and append the data to the worksheet with historical exchange rates.

Step 1: Set up the project and get the necessary files.

1. Create a new project using the default settings.
2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page and copy the file currency data.xlsx to your project folder.

Step 2: Add the Excel file to the project.

1. Click on the Designer panel.
2. In the Common Scenarios section of the search box at the top of the screen, select Use Excel File, and then browse to and select the file currency data.xlsx. An Excel File Card activity is added to the Designer panel.
3. In the activity, in the field And reference it later as, enter currency.
   You have indicated that you will work on the file currency data.xlsx that is known in your automation as currency.

Step 3: Extract the data from the web page.

1. Open Internet Explorer and navigate to the following web page: https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html.
2. In StudioX, click Data Extraction in the ribbon menu to open the Extract Wizard.

3. In the Select Element wizard step, click Next to indicate the table from which to extract the data.
4. Start moving your mouse and notice how StudioX highlights user interface elements it detects. Move the mouse somewhere in the table and click, for example you can click the Currency header.

5. StudioX detects that you have clicked inside a table and asks you if you want to extract data from the entire table. Click Yes.
6. A preview of the extracted table data is displayed. Click Finish.

7. StudioX asks if the data spans multiple pages. Since that is not the case, click No.
   The wizard is completed. Your project is displayed in StudioX, where an Application Card and an Extract Data activity have been added.
8. In the Extract Data activity, click on the right side of the Extract to field, and then select currency > Sheet1 [Sheet] to indicate that you want to copy the extracted data to Sheet1 of the currency Excel file.

Step 4: Transpose, format, and copy the data to the Historical Data sheet.

1. Click below the Application Card activity, then find the Copy Paste Range activity in the search box at the top of the screen and click it. The activity is added to the project.
2. In the Copy Paste Range activity:
   • Click on the right side of the Source range field, and then select currency > Indicate in Excel.
   • In the Excel file, select Sheet1, then select the range A2:C33 (the range with data extracted from the web page not including the header row), and then click Confirm in the UiPath tab in the Excel ribbon menu.
     You have indicated that you want to copy the range A2:C33 from Sheet1 of the currency file.
   • Click on the right side of the Destination range field, and then select currency > Indicate in Excel.
   • In the Excel file, select Sheet2, then select cell B1, and then click Confirm in the UiPath tab in the Excel ribbon menu.
     You have indicated that you want to paste the data to Sheet2 of the currency file starting with cell B1.
   • From the What to copy drop-down menu, select All.
   • Select Transpose to indicate that you want to transpose the data at the destination (columns to rows and rows to columns).
3. Click below the Copy Paste Range activity, then find the Format Range activity in the search box at the top of the screen and click it. The activity is added to the project.
4. In the Format Range activity:
   • Click on the right side of the Source range field, and then select currency > Indicate in Excel.
   • In the Excel file, select Sheet2, then select the entire third row containing the exchange rates, and then click Confirm in the UiPath tab in the Excel ribbon menu.
   • Click Set Format. In the Format data as type window, from the Category drop-down menu select Number, in the Decimals box enter 2, and select Use 1000 separator.
     You have indicated that you want to format the data in Sheet2, row 3 as number with two decimals and use the 1000 separator.
5. Add a second Format Range activity below the previous one.
6. In the second Format Range activity:
   • Click on the right side of the Source range field, and then select currency > Indicate in Excel.
   • In the Excel file, select Sheet2, then select cell A3, and then click Confirm in the UiPath tab in the Excel ribbon menu.
   • Click Set Format. In the Format data as type window, from the Category drop-down menu select Date and from the Date format drop-down menu select 3/14/2012.
     You have indicated that you want to format the data in Sheet2, cell A3 as date with the format d/mm/yyyy. This is the cell where we will add today's date.
7. Click below the second Format Range activity, then find the Write Cell activity in the search box at the top of the screen and click it. The activity is added to the project.
8. In the Write Cell activity:
   • Click on the right side of the What to write field, and then select Workspace Excel > Date [Sheet] > Today [Cell].
   • Click on the right side of the Where to write field, and then select currency > Indicate in Excel.
   • In the Excel file, select Sheet2, then select cell A3, and then click Confirm in the UiPath tab in the Excel ribbon menu.
     You have indicated that you want to copy today's date from the Excel Workspace to cell A3 in Sheet2 of the currency file.
9. Click below the Write Cell activity, then find the Append Range activity in the search box at the top of the screen and click it. The activity is added to the project.
10. In the Append Range activity:

• Click on the right side of the Append after what range field, and then select currency > Historical Data [Sheet].
• Click on the right side of the What to append field, and then select currency > Indicate in Excel.
• In the Excel file, select Sheet2, then select the entire third row, and then click Confirm in the UiPath tab in the Excel ribbon menu.
  You have indicated that you want copy the data in row 3 of Sheet2 and add it to the Historical Data sheet of the currency file after the existing data in the sheet.
• From the What to copy drop-down menu, select All.
• Leave Transpose deselected, since we don't need to transpose the data.

11. Click Save in the ribbon menu to save the automation, then click Run to execute the automation.

The web page is opened, the exchange rates are copied to Excel where they are formatted and added to the Historical Data sheet.

Download example