MS3501 Automated Data Analysis in Financial Modelling

Part I

Course Duration : One Semester
Credit Units : 3
Level: B3
Medium of Instruction: English
Prerequisite(s) : Introductory statistics course; Introductory financial
Precursors(s) : Intermediate level Excel skills 
Equivalent Course(s) : Nil
Exclusive Course(s) :
MS3111

Part II

Course Aims:

  • Provide students with programming concepts and techniques for creating automated data analysis using Excel VBA programming language in the finance industry.
  • Enable students to apply relevant Excel VBA programming techniques in data manipulation and processing, statistical analysis and forecasting in financial modelling.
Course Intended Learning Outcomes (CILOs)
Upon successful completion of this course, students should be able to:

No. CILOs Weighting
1 Develop simple VBA macros to manipulate and process data, automate and control a wide variety of Excel tasks related to statistical analysis and forecasting in the finance industry. (Ability) 50%
2 Apply Excel VBA programming techniques to design a financial modelling system using Excel to create feasible solutions to real-life problems in the finance industry. (Accomplishment) 50%

Course Intended Learning Outcomes (CILOs)
(state what the student is expected to be able to do at the end of the course according to a given standard of performance)

Upon successful completion of this course, students should be able to:

No.

CILOs

Weighting

1.

Use Excel and Develop simple VBA macros to manipulate and process data, automate and control a wide variety of Excel tasks related to statistical analysis and forecasting in the finance industry. (Ability)

50%

2.

Apply Excel and VBA programming techniques to design a financial modelling system using Excel to create feasible solutions to real-life problems in the finance industry. (Accomplishment)

50%

Teaching and Learning Activities (TLAs)
(Indicative of likely activities and tasks designed to facilitate students’ achievement of the CILOs. Final details will be provided to students in their first week of attendance in this course)

TLA 1: Lectures

  • Lectures: Advanced Excel skills and Concepts of various Excel VBA programming syntax are explained and demonstrated.
  • Practical Examples: Financial examples of using Excel and creating VBA macros to enhance data manipulation and processing, automate statistical analysis and forecasting are discussed.  Students will be asked to explore the possible VBA programming solutions to the examples discussed.  The lecturer provides instant feedback based on students’ responses.

TLA 2: Computer Laboratory 

  • Hands-On Practice: Students perform in-class practice to enhance their skills in Excel and VBA programming. The teacher monitors the hands-on practice, identifies learning difficulties and facilitates the learning.
  • Project: In order to provide students with the specialist knowledge and training to create a financial modelling system using Excel and VBA, students work in groups to develop one such innovation for a financial problem they have encountered in a real-life setting they are familiar with or for a financial scenario specified by the lecturer. Each group needs to identify a problem in financial applications and submit a project proposal including the proposed solution, so that the financial problem can be identified and the proposed solution to the problem verified by the lecturer. This is a semester-long activity, and the students need to make use of everything they have learned in this course in order to create the VBA macro program for the proposed problem. Finally, each group presents their developed application to the class and writes a final report that describes their developed financial modelling system.

Constructive Alignment of CILOs and TLAs


CILO No

TLA1: Lecture

TLA2: Computer Laboratory

Hours/week
(if applicable)

CILO 1

Y

Y

 

CILO 2

Y

Y

 

Assessment Tasks/Activities
(Indicative of likely activities and tasks designed to assesshow well the students achieve the CILOs. Final details will be provided to students in their first week of attendance in this course)

Type of assessment tasks/activities

Weighting

Remarks

AT1
Project

50%

This project is designed to assess students’ ability in creating a financial modelling system using Excel and VBA. An oral presentation of the developed application is given to the class and a final report describing their system is submitted.

AT2
Tests or Assignments

50%

Tests or assignments are designed to test students’ grasp of applying programming concepts and techniques for creating automated data analysis using Excel and VBA programming language in the finance industry.

Constructive Alignment of CILOs and ATs

 

AT1
Project

AT2
Tests or Assignments

CILO 1

Y

Y

CILO 2

Y

Y


Grading of Student Achievement :Refer to Grading of Courses in the Academic Regulations (Attachment) and to the Explanatory Notes.

Letter Grade Grade Point Overall Grade Definitions
A+
A
A-
4.3
4.0
3.7
Overall score is 76 or above out of 100. Demonstrated excellent ability to develop macros and automated data analysis with applications in financial modelling using all EXCEL VBA learned in lectures.
B+
B
B-
3.3
3.0
2.7
Overall score is between 61 and 75 out of 100. Demonstrated good ability to develop macros and automated data analysis with applications in financial modelling using some EXCEL VBA learned in lectures.
C+
C
C-
2.3
2.0
1.7
Overall score is between 46 and 60 out of 100. Demonstrated reasonable ability to develop macros and automated data analysis with applications in financial modelling using some EXCEL VBA learned in lectures.
D 1.0 Overall score is between 41 and 45 out of 100. Demonstrated limited ability to develop macros and automated data analysis with applications in financial modelling using some EXCEL VBA learned in lectures.
F 0.0 Overall score is 40 or below out of 100. Failed to demonstrate ability to develop macros and automated data analysis with applications in financial modelling using EXCEL VBA learned in lectures.

Part III

Keyword Syllabus:

Basics of VBA

Macros; Simple VBA programs; Control logic and loops; Excel objects; Working with Range; Working with Workbook, Worksheets, and Chartsheets; Import external data into Excel.

User Forms

Designing User Forms; Setting properties of controls; Writing event code for User Forms.

Automated Data Analysis Applications

Possible applications, but not limited to, include data manipulation, reports with summary statistics and graphs (such as histogram, bar chart, time series plot, scatter plot) or tables (such as frequency table, Excel Pivot table), statistical inferences, regression models, analysis of variance, time series forecasting, Monte Carlo simulation, economic and financial modelling.

Related Links
Department of Management Sciences