MS3111 Data Analytics with Excel VBA

Project

Project Team

Each project team must consist of at most four students. PIease join one of the created Project Groups under the Groups page in Canvas Is PeopIe section. Students in different cIasses are aIIowed to form a project group. The system wiII automaticaIIy assign the first group member as the Group Leader. If you do not want to accept more members to your project group, pIease change your group name to Project Group NN (FuII), where NN is your assigned group number, to indicate your wish. I shaII then Iock the group for you. As a courtesy to your  cIassmates,  pIease  ask  for  the  Group  Leader’s  permission  before  joining  a  group.  I  shaII  remove unweIcome members from a group upon receiving such a request from its Group Leader.

You can voIuntariIy change the project group between Weeks 6 and 7. Changing the project group at other times wiII require consent from me and the invoIved group members.

AII team members are expected to share the project workIoad equaIIy. How to distribute the workIoad fairIy among the team members is entireIy up to each team to work out. OccasionaIIy, a team may have to deaI with free–riding member(s). It is your team’s interest to identify free riders as soon as possibIe. Upon receiving such a request from aII the other group members, I shaII remove the free riders from the group. Free rider removaI requests wiII onIy be entertained not Iess than seven days before the Task 1 due date or 14 days before the Tasks 2 & 3 due dates. Removed free riders must compIete the project tasks without partners.

Project Requirements

Each project team must propose up to three hypothetical business reporting tasks that the team needs to perform.  manuaIIy  and  reguIarIy  (say,  daiIy,  weekIy,  etc.)  using  ExceI  and  deveIop  a  VBA  program  for automating the  proposed  reporting tasks. These tasks  shouId aIIow your team to demonstrate the VBA programming skiIIs described in the Course OutIine and the Iectures. The programming Ianguage must be Windows ExceI 2019 (or Iater versions) VBA. You may discuss the proposed reporting tasks with me to ensure that the activities I scope wiII fuIIy demonstrate your VBA programming skiIIs.

In generaI, your proposed reporting tasks shouId require you to perform. at Ieast the foIIowing activities manuaIIy (i.e., without the heIp of VBA):

•   Access source data sets in at Ieast two ExceI workbooks. Each source data set must have at Ieast two coIumns of data  in one or  more worksheets; the  more  coIumns, the  better.  You  must give yourseIf sufficient opportunities to perform. chaIIenging reporting tasks and deveIop a sophisticated VBA program Iater. The row size of each data set does not have to be Iarge. For your interest, the source data shouId contain different fieIds, such as numeraIs and characters. In most situations, you may assume the coIumns and the format of the source data sets are fixed, and they are known to you. However, the number of observations and the contents of the data sets differ from time to time. You onIy know the exact contents once you have opened the workbooks. It is best to pretend you wiII need to deaI with new sets of source data at reguIar time intervaIs, such as daiIy or weekIy, according to the needs of the reporting task.

•    Derive at  Ieast one new data set from the source data sets for Iater reporting purposes. This process shouId invoIve cross–workbook operations such as, but not Iimited to, record extraction (such as seIecting records according to the vaIues of two or more variabIes), expanding a singIe record into muItipIe records, aggregating muItipIe records into a singIe record, and computations of new variabIes. The derived data set(s)  shouId  be  kept  in  one  or  more  new  ExceI  workbooks  that  are  different  from  the  workbooks containing the source data sets and the report workbook.

•    Create a report workbook. The report shouId contain at Ieast one non–data Iinked frequency tabIe or  reIative frequency tabIe for  a  numeric  variabIe,  at  Ieast  two  non–data  Iinked  distribution  tabIes  for  character variabIes, one of them incIuding summary statistics (such as sum, mean, maxima, minima, range, etc.) of a numeric variabIe for each IeveI of the character variabIe, and at Ieast two different types of non –  Pivot charts (such as a bar chart, and a pie chart). Each reported table and chart must involve at least one computed variable derived from the created new data set(s).  The report workbook must contain only the data used to construct the included charts.

•   Save, delete, and close all Excel workbooks, as appropriate.

Adding new records to the source data sets or directly updating (such as changing the address, phone number, etc.) existing records in the source data sets are considered data collection activities. They are prohibited in this project. However, changing existing records based on combining information in other source data sets is allowed.

Each team is required to perform. the following tasks for the project:

Task 1:

Provide a detailed description of the reporting activities the team intends to perform manually (without VBA) and regularly. The team must ensure no prohibited data collection activities are involved. The report must be in PDF format, of font size 12, and it must be at most twelve pages (including the cover page and appendices, if any). The report must include sufficient screenshots of the source data sets, data manipulations, examples of created tables, and charts. This task contributes a maximum of 20 marks to the overall project marks (100).

Task 2:

1.   Develop and submit an  Excel VBA application to automate the  reporting activities described in Task 1. Requirements for the developed application:

a)   The developed application should not be tied to the source data sets, i.e. the names and the paths of the source data set should not be hard coded in the application under any circumstances. One should be able to use the application without any modification for new entities in the source data sets, such as different numbers of records, new prices, new costs, new materials, new products, new branches, etc.

b)   Only one Userform is allowed in the application.

c)   Application login and raw data collection activities are prohibited in the developed application.

d) The workbook containing the developed application must differ from the source data workbooks, the created data set workbook(s), and the report workbook.

2.   Submit a written report in PDF format. The report must be in font size 12 and at most 35 pages (including the cover page and appendices, if any). You may use the “Task 2 Report Template.docx” as a template for preparing the report.

3.   All relevant source data or created workbooks and report workbooks must also be submitted.

The report must contain only the following:

a)   List the set of tasks that the developed VBA application will accomplish. For each listed task, briefly outline the kind of activities involved. For example, suppose one of the listed tasks is about reporting the sales of products by branches, products, etc. The related activities may include combining the sales records in different worksheets/workbooks, computing the sales if necessary, and creating summary tables or charts as appropriate.

b)   List and annotate the code (properties, methods, and events) of Userform controls. If the same kind of control is used more than once in the application, the team only need to describe the most sophisticated one in the report.

c)   List and annotate the code for handling workbooks and worksheets by the application.

d)   List and annotate the code for the data manipulations performed by the application. The description must include but is not limited to record extractions, record expansions, record aggregation, and computations for new variables.

e)   List and annotate the code for creating tables and charts using the application. If the same type of table or chart is created more than once in the program, the team only needs to describe the most sophisticated one in the report.

I will mark the reported code in the report for programming skills, programming style, and abilities to control the objects you are expected to master in this course (see the tentative marking scheme for the details). I will not mark a plain code printout without annotation or a report that does not use the provided report template. The team will not receive marks for the objects not reported, even though these objects have been utilised in the team’s program. I shall examine the submitted applications, but I will not mark them. Each team is responsible for ensuring that I have full access to any part of your application. All teams must also  submit  all  external  Excel  files  involved. A  team’s  report  marks  will  be  zero  if  the  program  is  not submitted or I cannot access it. This task contributes a maximum of 70 marks to the overall project marks (100).

Task 3: Submit a video to present the developed application. It is up to the team to decide which team member(s) will do the presentation. The presentation should demonstrate the developed application with appropriate narration, but the presenter does not need to appear in the video. The video must be at most 10 minutes long. It must be in a format I can watch without using proprietary software in Windows. The marks for the video will  be zero  unless  I  can watch the video  on  my computer. This task contributes a maximum of 10 marks to the overall project marks (100).

The due dates for each task will be announced in class. All submissions must be on time using the designated link for each task in the Canvas of the course.

While looking at other students’ works for reference is not guilty, one must limit how much will be taken from others to avoid plagiarism. It is not difficult for me to compare your submitted report and application to those submitted in previous years. I trust you will develop your Excel VBA program for your benefit.

Tentative Marking Scheme For Task 1: Problem Description

Maximum marks: 20

The problem description (6 marks)

• The reporting activities and outcomes of the activities are well described. (0-3)

• Sufficient screenshots are included and well organized in the description. (0-3)

Scope of the reporting tasks (14 marks)

• Involve at least two source data workbooks. (0-2)

• Involve non-trivial record extraction, merge, expansion, and aggregation. (0-3)

• Involve computations for new variables. (0-2)

• Generate at least one frequency table for a computed numeric variable. (0-1)

• Generate at least two tables for character variables. One of them includes summary statistics of a numeric variable for each level of the character variable. Each table must involved at least one computed variables. (0-2)

• Generate at least two different types of charts for the computed variables. (0-2)

• At least one new workbook for the created data set(s) and one new workbook contained the reported table and charts. (0-2)

The project involves the prohibited raw data collection activities (-5)

Tentative Marking Scheme For Task 2: Application Development and Report

Maximum marks: 70

Reporting writing (9 marks)

• The set of tasks and the involved activities are well explained. (0-4)

• Sufficient screenshots are included with adequate annotation and are well organized. (0-5)

Programming style. (9 marks)

• The code is adequately commented. (0-3)

• Objects and variables are meaningfully named and properly declared. (0-3)

• Good readability of code. (0-3)

Objects manipulation and utilisation (47 marks)

• Sufficient workbook manipulation: At least two workbooks for the source data sets, at least one workbook for the derived data sets, at least one workbook for the report, and one workbook for the programme code and Userform. No modification to the source data set workbooks. Sufficiently utilized object variables, references, properties, and methods. (0-6)

• Sufficient worksheet manipulation: Sufficiently utilized object variables, references, properties, and methods. (0-4)

• Sufficient data manipulation: Generate at least one new data set in a new workbook by applying non-trivial manipulation on the source data sets. (0-8)

• Create at least two non-data linked tables for character variables in the report workbook. At least one table includes summary statistics of a numeric variable for each level of the character variable. Each table must involved at least one computed variables. All elements of the tables, such as data range, variables, statistics, etc., must be appropriately coded in the application. (0-4)

• Create at least one non-data linked frequency table for a numeric variable in the report workbook. Each table must involved at least one computed variables. All elements of the tables, such as data range, variables, statistics, etc., must be appropriately coded in the application. (0-3)

• Create at least two types of non-Pivot charts in the report workbook for the computed variables. All features of the charts, such as data range, type of chart, labels, etc. of the charts must be appropriately coded in the application. (0-4)

• Utilise OptionButton control. (0-2)

• Utilise CheckBox control. (0-2)

• Utilise RefEdit control. (0-1)

• Utilise ScrollBar or SpinButton controls. (0-2)

• Utilise ComboBox control. (0-3)

• Utilise Frame. control. (0-2)

• Utilise ListBox control. (0-4)

• Utilise MultiPage control. (0-2)

The program can be used for new entities(such as new products, new branches, new staff, etc.) without any modification. (0-5)

The project involves the prohibited login or raw data collection activities. (-10)

The project involves more than 1 one Userform. (-5)

No individual workbooks for the source data set, created data set, report, and developed application. (-10)

Not submitted all relevant Excel files (-70)

Tentative Marking Scheme For Task 3: Project Presentation Video

Maximum marks: 10

Presentation Video (10 marks)

• The presentation is well delivered (including media, speech, flow, and timing) (0-4)

• The automated process is well demonstrated (0-6)