Skip to main content
Version: 10.2.9

Web scraping

Example overview​

Problem statement

There is a lot of information on the Internet on various websites that can be useful for business needs like information about stock prices, currency rates, or specific news. Business requires this information to make decisions or use it in daily work. In most cases, gathering data occurs manually or via files stored in FTP or some shared source. Manual work takes a lot of effort in the case of big data volume because repeated actions are needed to be done many times. Getting files from FTP requires specific access, and asking for it takes time and additional actions from the IT department side. These disadvantages can be resolved using a script.

Existing manual process

Usually, employees should go to an appropriate source and copy-paste required data as much as required. Depending on how much data is on a web page, it can take 10-15 seconds to hours.

Automation solution

Our example allows collecting press releases from the U.S. Securities and Exchange commissions site for the latest seven days without any manual intervention. You just start it, and a bot will do all the necessary actions by itself. As a result, you will receive a file with the links to press releases to your email. One more option allows applying reusable components of the script in your own scripts without any modifications.

Reusable components

In this example, there are three reusable components:

  • Calculate the period.
  • Send an email with an attachment via Outlook.
  • Close Outlook.

Example overview video​

Preconditions​

  1. Install:
    • Work.AI Developer
    • Any of these browsers: Google Chrome, Firefox, or Internet Explorer
    • MS Excel
    • MS Outlook
  2. Download the example with an Excel template.

Getting started​

To get started, perform the following actions:

  1. Add the example to your Recorder. For more details, refer to Explore Recorder features.
  2. Copy the Excel template press_releases.xlsx to any appropriate folder and edit the following variables:
    • file_path: the path to the Excel template file.
    • user_email: the email address where the result list is sent to.
  3. Make sure that your Outlook account is configured to send emails.

Script overview​

Environment​

  • Windows OS that meets System Requirements
  • Any of these browsers: Google Chrome, Firefox, or Internet Explorer
  • MS Excel
  • MS Outlook with an email account configured and logged in for the bot to be able to send emails

Data and logic​

Input data:

  • Period of dates: seven days before current date—current date
  • Excel template that should be copied to any convenient folder. The bot should have access to this folder.

Output data:

  • Excel file with the list of press releases

Common logic​

The bot opens a website in the default browser and scrapes data in the background mode. It means that you see only an open web page in the browser during this operation. After scraping, the bot filters press releases from the current date to seven days before today. Then it prepares the data to be pasted into an Excel template and saves the file. After updating the file, the bot opens Outlook, creates a new email, attaches the Excel list, and sends to the email that is set in the script's variable.

Script variables​

Variable nameTypeDescriptionDefault value
current_dateDateTimeCurrent date to calculate the date period
file_pathStringPath to the Excel template where the data is saved
final_tableTableFiltered data from the site for the latest seven days
press_release_dateDateTimePress release date from the web page
secgov_urlStringWebsite URLhttps://www.sec.gov/news/pressreleases
start_dateDateTimeStart date required to calculate the period
subjectStringSubject of an Outlook email with the attachment to be sent to the recipient
temporary_tableTableData scraped (Date, Title, Release No, Link) from the last web page of https://www.sec.gov/news/pressreleases
user_emailStringEmail to send the Excel file to

Script workflow​

Calculate period for press-releases​

Description: The group of actions is required to calculate the period of dates from the current date to seven days before today. The whole group is reusable in other projects.

To reuse the script, change the text on the 6th row of the script Period.ofWeeks(1) to the period you would like to calculate. For example:

  • Period.ofWeeks(2): calculate the date two weeks before today.
  • Period.ofDays(3): calculate the date three days before today.

  1. The Constant Value action saves the current date to the current_date variable (reusable).

  2. A Groovy script in action 3 (Script) calculates the period of date in the current_date variable to seven days before and saves to the start_date variable.

Open website, scrape and filter data​

Description: The group of actions is required to scrape information from the website https://www.sec.gov/news/pressreleases and then filter it, so only press releases for the latest seven days are saved to the final_table variable.

  1. Exception Handling is added, so the script can be opened in a default browser installed on your machine (Google Chrome, Mozilla Firefox, or Internet Explorer).

  2. The Open Website action opens the website from the secgov_url variable.

  3. Actions 8-11 save Date, Title, Release Number, and press release URL from the website to the List variables using XPath:

    • date_column
    • title_column
    • link_column
    • release_no_column

  4. Actions 12-15 add the previously scraped and saved data from List variables to the Table variable temporary_table (Expression Value). All the press releases from the last page of the website are stored in this variable.

  5. All the actions nested in the For Each loop are performed for each row in the Table variable temporary_table.

  6. Exception Handling is added as the dates on the website are written in a custom format. To compare all these dates with the start_date variable (seven days before today), convert them into DateTime variables. But as the dates are written in a different format, there can be an exception during conversion. That's why Exception Handling is added.

  7. Action 65 (IF-Else) compares the press release dates with the start date (seven days before today). If the press release date is more or equal to the start date, then it pushes the filtered data to the final_table variable.

Save data in Excel file​

Description: The group of actions is required to paste the data from the final table results to an Excel spreadsheet and set appropriate values to necessary cells.

  1. The bot opens the Excel spreadsheet (Open Spreadsheet) using the file_path variable.

  2. The bot pastes the value of the final_table variable to the Excel spreadsheet (Set Range). The range will start from A2 cell, as cells A1-D1 contain column titles (Date, Title, Link, Release No).

  3. The final actions take values from string variables (Date, Title, Link, Release No) and paste them into specific cells in Excel (Set Cell Value).

Create specific subject for Outlook email​

Description: The group of actions is required to create a specific subject for the Outlook email to be sent.

  1. Actions 75-76 convert the DateTime start_date and current_date variables to string variables, so you can use them in the email subject (DateFormat).

  2. Actions 77-78 join four variables so you get the email subject (Join Strings). Action 34 joins text_for_subject and start_date_string and saves to the subject_start variable, for it to look like "Press releases for Feb 01, 2019".

  3. Action 35 joins the subject_start and current_date String variables, for the final result to look like "Press releases for Feb 01, 2019 - Feb 08, 2019".

Send email with attachment via Outlook (reusable)​

Description: The group of actions is required to open Outlook and to send the email with the press releases attached. The whole group is reusable in other projects.

To reuse this group, change the values of variables:

  • user_email: Change the user's email to send to, for example, test@test.com.
  • subject: Choose the subject to set into the variable value.
  • file_path: Set the right path for the file you want to attach.

  1. Actions 80-84 open the Run window, type "Outlook" (Enter Keystrokes), and then open Outlook. With the help of the Window action, the bot switches to the specified window and ignores any random popups.

  2. Actions 85-110 send an email with the specific subject and the attached file to the email from the user_email variable. Use the Enter Keystrokes, Mouse Click, and Window actions.

  3. The exception handlings are added as some window titles, and elements differ in various Outlook versions. The script below works for Outlook from 2007 to 365:

    1. Action 85 adds Exception Handling due to the window title difference in Outlook versions.
    2. Action 92 adds Exception Handling due to the difference in the Subject selector in Outlook versions.
    3. Action 96 adds another Exception Handling as the Attach file selector differs in Outlook versions.
    4. Action 101 adds Exception Handling as the flow for attaching a file differs depending on the Outlook version.
    5. Action 106 adds Exception Handling as the Send button selector differs depending on the Outlook version.

Close Outlook (reusable)​

Description: The group of actions is required to close Outlook when the email is sent. The whole group is reusable in other projects. 

  1. The bot enters Alt+F4 (Enter Keystrokes) to close Outlook.

  2. The Wait action gives time for the email to be sent.

  3. Exception Handling is added due to the window title difference in various Outlook versions.