Extracting Data from Webpage

Objective

The objective of this How-To guide is to program OpenBots to open a web browser window (Google Chrome), navigate to a website (Yahoo Finance®), extract website data (Price of Tesla Stock), and enter it into an Excel Workbook. These commands used in this guide can be applied in many different scenarios.

Walkthrough

The following guide demonstrates the commands to complete the objective above.

Step 1: To create an instance of a Web Browser, there is only one command needed, called Selenium Create Browser Command. This instance will be associated with the new browser window throughout the entire process.

Select Selenium Create Browser Command
Type the name of the Web Browser Instance (ex. ChromeBrowser)
Select the Engine Type (in this case Google Chrome)
Type in the website URL (finance.yahoo.com for Yahoo Finance)
Set Window State to Maximize (default)
Click OK

Step 2: To type into the search bar of the Yahoo Finance website, use the Selenium Set Text Command.

Select Selenium Set Text Command
Set the Browser Instance name to the same as step 1 (ex. ChromeBrowser)
Enter the search bar’s XPath selector (the user can utilize the Element Manager by clicking the ‘Insert Element’ link)
Write ‘TSLA’ (This is the stock symbol for Tesla) under the ‘Text to set’ field.
Click OK

Step 3: To click the search button on the Yahoo Finance website, use the Selenium Click Element command to find the search button using its XPath and then using the Element Action to click it.

Select Selenium Click Element Command
Set the Browser Instance name to the same as step 1 (ex. ChromeBrowser)
Enter the Search Button’s XPath selector
Set Click Type to “Invoke Click”
Click OK

Step 4: To wait for the Tesla Stock Price to appear on the webpage, the Selenium Wait for Element to Exist command can be used. This allows the automation to provide a certain element on the web page with time to load before moving on to the next command.

Select Selenium Wait for Element to Exist Command
Set the Browser Instance name to the same as step 1 (ex. ChromeBrowser)
Enter the Item title’s XPath selector
Enter the wait time for the bot in seconds (ex. 5 seconds)
Click OK

Step 5: Once the wait is over, the Selenium Get Text command can be used to grab the price of the stock

Select Selenium Get Text Command
Set the Browser Instance name to the same as step 1 (ex. ChromeBrowser)
Enter the Item title’s XPath selector (the same selector as step 5)
Next to the label ‘Variable Name’, create a variable to pass the stock price value (in this case, {vPrice}).
Click OK

Step 6: To create a new Excel workbook, there is only one command needed, called Create Excel Application Command

Select Create Excel Application Command
Set the Excel Instance name to “ExcelInstance”
Under ‘New/Open Workbook’, select ‘New Workbook’
Set Visible status to “No” (default, optional)
Click OK

Step 7: To write the label ‘Closing Price’ in the workbook, there is only one command needed, the Write Cell Command

Select Write Cell Command
Set the Excel Instance name to “ExcelInstance”
Type ‘Closing Price’ in the section ‘Cell Value’
Type in the cell location where label will be found (i.e. A1)
Click OK

Step 8: To write the label Stock Price found online into the workbook, there is only one command needed, the Write Cell Command

Select Write Cell Command
Set the Excel Instance name to “ExcelInstance”
Type the variable vPrice in the section ‘Cell Value’. This will write the stock price captured on Yahoo Finance into the workbook
Type in the cell location where label will be found (in this case B1)
Click OK

Step 9: To save the new Excel Workbook, the Save Workbook As command is used to close the Excel instance

Select Save Workbook As Command
Set the Excel Instance name to “ExcelInstance”
Under ‘Workbook Location’, select where to store this file (in this exercise, the file will be saved in the same folder as the automation project)
Write the name of the new file (in this exercise, DateTime Day, Month, and Year variables were used to automatically name the file based on the current day. Ex: 9_23_20)
Click OK

Step 10: To close the edited Excel Workbook, the Close Excel Application command is used to close the Excel instance

Select Close Excel Application Command
Set the Excel Instance name to “ExcelInstance”
Set Save Workbook to “No” (saving the document in this command is redundant since it was saved in the previous command)
Click OK

Step 11: The Selenium Close Browser command is very similar to the Close Excel Application command and closes the Browser Instance

Select Selenium Close Browser Command
Set the Browser Instance name to “ChromeBrowser”
Click OK

The commands in this guide can be utilized alongside many of the other commands that will be discussed in later guides. Below is a snapshot of the commands in this guide:

At the end of the process, the new file should be saved in the same folder as the automation project file. Should the workbook be opened, the label ‘Closing Price’ should appear in cell A1 and the stock price should appear in cell B1. This can be seen in the following images:

Commands utilized for the above concept (Access Website Information) are as follows:

Outlook Command(s): Selenium Create Browser, Selenium Navigate to URL, Selenium Set Text, Selenium Get Text, Selenium Click Element, Create Excel Application, Write Cell, Save Workbook As, Close Excel Application, Selenium Close Browser

Lets stay in touch!