Lecture 4 Advanced Topics OVERVIEW Importing Data Analysis

Lecture 4 Advanced Topics

OVERVIEW • • Importing Data Analysis Add-In Solver Macros

IMPORTING DATA • You may receive data that is not in Excel that needs to be opened in Excel • For example a text file, that is smaller and more amenable to email • Load this file into Excel, maintain the main columns

Importing Data From Text File

Importing Data From Text File

Importing Data From Text File

Importing Data From Text File

Importing Data From Text File

Importing Data From Text File

Importing Data From Text File • • Save and Close the file Open the file again from the folder Notice the changes we made in the text file The file is still a text file even though we have modifying it in Excel

Importing Data From Text File • From the Excel window select Save As Excel Workbook

DATA ANALYSIS ADD-IN • Some of Excel’s tools are not loaded automatically but must be activated by the user • For example the Statistical tools called “Data Analysis”

Add-Ins

Add - Ins

Data Analysis

Data Analysis • Use Data Analysis to calculate Descriptive Statistics of the “Speed” column

Data Analysis

Data Analysis

Data Analysis

SOLVER • Solver enables users to set up operations research problems e. g. integer linear programming, non linear programming etc • Operations research problems typically involve systems of equations that involve inequalities • Therefore, for non-inequalities problems we can use it as alternative, to say matrices, to solve systems of equations. • Solver must be activated from Add-Ins prior to use.

Optimization Using Solver • Consider the following investment decision problem for a financial institution • Three new financial products yield returns of 6. 5%, 7%, 9% respectively • Total investment amount in these products altogether cannot exceed $500 K • Due to risk, product 3 investment cannot exceed twice product 2 amount and product 2 investment cannot exceed three times product 1 • What is the optimal investment plan to maximize return on investment?

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver

Optimization Using Solver • Note that the more objectives and constraints, the more complex the problem

Optimization Using Solver

MACROS • Macros are used to record and save the keystrokes used to perform a task • By so doing the user can then call the macro to run the same tasks on another dataset • This is especially advantageous for repetitive type tasks, e. g. a weekly or daily traffic update report • Before we can use macros we must activate the Developer tab through Excel Options

Activate Developer Tab • Format the traffic data, but save all work in a macro

Activate Developer Tab

Activate Developer Tab

Record Macro

Record Macro • Keystrokes: – – – Highlight top row Bold All Borders Fill color (green) Select: Date, Time, Vehicle Format Auto. Fit Column Width Scroll back to left Highlight first 30 rows of speed column Fill with orange color Developer Stop Recording

Save As Macros Enabled File

Save As Macros Enabled File

View Your Macros

Calling Your Macro • Now open a new traffic data file • Copy and paste the data to the macros enabled file, making sure all columns and rows are lined up as in the macros enabled file • Run the macro from View Macro

Calling Your Macro

Call Your Macro

Call a Macro • The macro does the formatting on the new data for us in one shot • Note that we could have run it directly without copying over if we had selected the file name in the drop down • Students! Repeat the data import task, but this time save it as a macro • As we shall see macros can get VERY complex

References • http: //office. microsoft. com/enus/support/results. aspx? av=zxl • www. youtube. com