Reference no: EM132263080

Lab Assignment -

Data Importing and Data Importing Wizard: Show and experiment with the data import wizard and data importing functions and manipulation.

Complete and Submit the Dropbox Exercises: Please refer to the attached Instructions and Example document. There should be two completed Excel files.

Text Importing Assignment Using the Data Import Wizard - Part 1

You are given a set of credit card transaction data for one bank condensed from the vendors' point-of-sale (POS) systems. The data has been converted from hexadecimal to decimal format for you, but is meaningless us unless it is separated into parts that make it readable to us.

Objective: Import the transaction information from a text file (.txt) into it proper readable parts using the Excel Data Import wizard. Derive mathematical statistics from the imported information.

Vendor Account Numbers, transaction Authorization Codes, card Expiration Dates, and Card Verification Value Codes (CVC's) are listed in a separate database searchable by Transaction Code for security reasons. (That information is not necessary to help us track our purchases).

Use Get External Data to import data from the text file "Polled Credit Card Data...". The guidelines below will help you separate the text into the proper columns.

The data is given in a fixed width 54 digit format as follows:

• The 1^st digit is the Transaction Status. The codes denote 0 for not approved or approval pending, 1 for approved, 8 for transaction flagged for review by request of customer, 9 for transaction flagged for review by request of bank or card company. You must import this as a Text field in order for the zeros to display properly.
• The next 11 digits are the Transaction Number. The first 3 digits of the code denote the Region, or where the purchase was made (in relation to the bank or customer location). The next 8 digits denote the Transaction Code (assigned by the POS system). You must import this as a Text field in order for the zeros to display properly.
• The next 16 digits show the Customer account or Card Number. You must import this as a Text field in order for the zeros to display properly.
• The next 6 digits show the date of the transaction. You can import this as a Date field.
• The next 6 digits show the time of the transaction broken into hours, minutes and seconds. You will need to break it into 3 Text fields (hours, minutes, and seconds) to avoid using a formula such as =DATE( LEFT(H2,2) ,MID(H2,3,2), RIGHT(H2,2) ) after the import to clean the data (The =DATE(H2,I2,J2) formula is much simple if the entire date is used in future calculations.
• The next 7 digits denote the amount of the transaction in units (U.S. Dollars for the U.S.) and fractions (units divided by 100). You will need to break this up into 2 General fields (one for dollars and one for cents). Using Text fields would mean that the amounts would need to be converted to numeric values using a =VALUE formula after the import, which means much more manual work.
• The next 4 digits display the Port (server or terminal in the POS system) where the transaction took place. You must import this as a Text field in order for the zeros to display properly.
• The last 3 digits denote Checksum information. These are check digits that are derived from a formula using the transaction information to make sure that the transmission of the 54 digit code is complete and authentic, and not altered or tampered with during or before transmission. In our example, no checksum information should exceed the value 256. You must import this as a Text field in order for the zeros to display properly.

Using the data provided to you, separate and label each field of portion of the data code as shown in the example.

Add headings to each column to designate the information in that column.

Label the worksheet tab with a descriptive name. Sheet 1 means absolutely nothing to your boss or your user. Sort the data by date and time.

Without adding columns or altering the data, find both the total and the average of the charge amounts. The total can be found by adding the total dollar amount to the total fraction amount (cents) with the fraction divided by 100.

To find the average, you will have to take the total divided by the number of entries.

You must use the COUNT function (or the COUNTA function minus 1) to count the number of entries, since the number changes constantly and with each import (DO NOT MANUALLY COUNT any CALCULATIONS in your formula).

Text Importing Assignment Finding Useful Data - Part 2

Find and open the comma delimited file listing Global Power Generating Stations. Format the sheet to look professional if need be.

Filter the data to find local power plants in our area. You will need to find the latitude and longitude of the closest power plant to us. Then label and store the name of the power plant and its location in a separate area. Round the latitude and longitude to integer values and filter the latitude to values that include one minus through one plus the integer latitude number. Do the same for the longitude, but keep in mind that the value filters must be entered in numerical order.

Paste the data to another page using the Goto Special option to select Visible Data only. Then, select the new data, and create a Pivot Table.

List each power plant (name) by generating capacity (Use Capacity as your numeric value). Filter the columns to find and list the Top 4 Primary Fuels used for each power plant. Make sure the pivot table (design) rows are banded for easier reading.

Attachment:- Assignment Files.rar