Practice Problem 1

Objective

The objective of this practice problem is for the user to demonstrate an understanding of the If Statement commands discussed in the previous section by creating an automation that utilizes said commands. The commands covered in the previous section were the If, End If, and Else commands. In the future, these commands may be implemented into other practice problems.

Scenario

An employee of the ABC company needs to extract stock information from Yahoo Finance and send that information to another employee via email. The Excel file that will store the stock information may not always exist, therefore the automation will need to check to see if the file exists before extracting information, filling in the file, and then emailing the file to a colleague. If the file does not exist, then the information is still extracted, but only the information itself is emailed to the colleague.

Prerequisites

The only prerequisite for this practice problem is an Excel file that is formatted in a manner as seen below. The value in A2 needs to be a ticker/stock code that is recognized by Yahoo Finance.

Practice Problem Requirements

The user must create an automation that performs the following tasks:

Checks if the prerequisite file exists
If the file exists
- Extract the value in the Stock Market Index column.
- Navigate to https://finance.yahoo.com/
- Search for the value that was extracted
- Extract the Stock Value and the Percent Change
- Fill in the Excel file
- If the Stock Value is greater than or equal to 11,150
  - Write Yes in Column D
- If Stock Value is not greater than or equal to 11,150
  - Write No in Column D
- Close the file and email the file to any email address the user desires.
If the file does not exist
- Navigate to and search https://finance.yahoo.com/ for the IXIC Stock Code
- Extract the Stock Value and Percent Change
- Send an email with a message that contains the extracted Stock Value and Percent Change to any email address that the user wants.

Note: These are the values that need to be extracted from Yahoo Finance and be sure to run a test where the prerequisite file does not exist.

Problem Difficulty: Medium

Solution

The code for the solution has been provided below.

As a quick summary:

Step 1: Use an If Statement to check to see if the prerequisite file exists in the designated folder.

Step 2: If the file exists, open it and extract the Stock Market Index.

Step 3: Navigate to Yahoo Finance, input the Stock Market Index into the search bar, and click the search bar using Web Browser commands.

Step 4: Extract the Stock Value and Percent Change values and then input them into the file. If the Stock Value is greater than or equal to 11,150, write yes in Column D. If not, write no.

Step 5: Close the file and email it to another user.

Step 6: If the file does not exist, navigate to Yahoo Finance, and enter IXIC into the search bar, and click the search bar using Web Browser commands.

Step 7: Extract the Stock Value and Percent Change values and then send an email to a colleague that contains the values.

Lets stay in touch!