Tutorial: Extracting Table Data From a Web Page and Editing It in Excel

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 spreadsheet 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 spreadsheet, 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 a Use Excel File activity 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: a Use Application/Browser activity that indicates the web page and an Extract Table 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 blank 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.
3. This example uses Google Chrome, which requires the UiPath extension for Chrome to be installed. To install it, go to Home (StudioX Backstage View) > Tools, and then, under UiPath Extensions, click Chrome.
Step 2: Add the Excel file to the project.
1. Click Add activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. A Use Excel File activity is added to the Designer panel.
2. In the activity:
  - Click Browse next to the Excel file field, and then browse to and select the file currency data.xlsx
  - In the field Reference as, enter currency.
    
    You have indicated that you will work with the file currency data.xlsx that is known in your automation as currency.
Step 3: Extract the data from the web page.
1. Open Google Chrome 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 Table Extraction in the ribbon 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 a Use Application/Browser and an Extract Table Data activity have been added.
8. In the Extract Table Data activity, click Plus 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 Add activity below the Use Application/Browser activity, then find the Copy 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 Range activity:
  - Click Plus 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.
    
    You have indicated that you want to copy the range A2:C33 from Sheet1 of the currency file.
  - Click Plus 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.
    
    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 Add activity below the Copy Range activity, then find the Format Cells 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 Cells activity:
  - Click Plus 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.
  - 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 Cells activity below the previous one.
6. In the second Format Cells activity:
  - Click Plus 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.
  - 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 Add activity below the second Format Cells 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 Plus on the right side of the What to write field, and then select Project Notebook (Notes) > Date [Sheet] > Today [Cell].
  - Click Plus 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.
    
    You have indicated that you want to copy today's date from the Project Notebook to cell A3 in Sheet2 of the currency file.
9. Click Add activity 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 Plus on the right side of the Append after range field, and then select currency > Historical Data [Sheet].
  - Click Plus 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.
    
    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 StudioX ribbon 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