200 likes | 293 Views
PROJECT OVERVIEW PINGER EXECUTIVE PLOTS AKBAR MEHDI. Sequence. TASKS GIVEN OVERVIEW OF TOOLS USED Pivot Tables VBA CURRENT TASK PROGRESS. TASKS. The aim is to provide high level plots of data generated by PingER to executives. These plots are to be designed using tools in MS Excel such as
E N D
Sequence • TASKS GIVEN • OVERVIEW OF TOOLS USED • Pivot Tables • VBA • CURRENT TASK PROGRESS
TASKS • The aim is to provide high level plots of data generated by PingER to executives. • These plots are to be designed using tools in MS Excel such as • VBA(Visual Basic for Applications) • Pivot tables • Macros • Plots should provide a more management oriented view of the data generated by PingER.
Pivot Tables • Pivot tables quickly summarize and analyze large amounts of data in lists and tables. • Independent of the original data layout, a summary is created by dragging, dropping columns to different rows, columns, or summary positions. • As an example • Lets say we have a huge database of census data in Excel with various data fields. • Obviously it would be difficult to analyze such a large amount of data and even graphing would not be feasible. • We can create a pivot table summary using this raw census data. • With the drag of a mouse, you can easily rearrange the pivot table so that it summarizes the data based on gender or age groupings or geographic location.
VBA (Visual Basic for Applications) • VBA is "Visual Basic for Application". • It is a programming language that allows users to program macros to accomplish complex tasks within an application. • Such Applications include MS Word, MS Excel and MS Access. • VBA for Excel is powerful programming language with which you can develop within Excel, real programs that will accomplish in a few minutes very complex tasks.
VBA Features • VBA code uses five types of components: • Objects • They are the building blocks of Excel projects. • They are the application (Excel), the workbooks, the worksheets, the cells and the ranges, the charts, the drawings, the controls (command buttons, text boxes, list boxes, etc.). • Properties • All objects have properties that you can set and modify. • For example the font can be bold, the sheet can be visible or not, a cell can have a formula • Procedures • Allow the programmer to write code in the form of modules to be called as macros.
VBA Features (contd.) • Functions • These include • Excel functions such as SUM( ), COUNTIF( ) • VBA functions such as UCASE, LCASE, NOW() • Can be called from VBA procedures. • Statements • IF..THEN, DO...LOOP, FOR...NEXT, WITH...END WITH, EXIT FOR, EXIT DO, EXIT SUB
Tasks Completed • I have completed an excel based application which: • Automatically reads data from text file into Excel sheet • Generates Pivot Tables of the Data • Creates graphs of the pivot tables. • The data for the text file is obtained from: • http://www-iepm.slac.stanford.edu/cgi-wrap/pingtable.pl
Current Tasks • Generating Pivot Tables and graphs for data on the following page: • http://www-iepm.slac.stanford.edu/pinger/prmout • Improving the created pivot tables and graphs to make them more dynamic