ISOM3230 Business Applications Programming

Background Story

As a beginner in the share market, Tim lost most of his share investments dramatically during the financial tsunami. Tim has started thinking of a better way of managing his stocks. After doing some researches on the Internet, Tim has finally realized that he needs an investment portfolio to keep track of his purchased shares and their market values in order to adjust or change his investment strategy.

The investment portfolio program records number of purchased shares and their associated properties: stock number, stock name, purchase date, unit, purchase price, current market value, and current total. In addition, the portfolio program will show profit or lost based on his investment in total and the total current market values of purchased shares. The following is a sample output of the investment portfolio program (VB version).

In addition, Tim would like to have some financial analysis in this portfolio. The program should extract financial data from the Internet, and then performs some technical analysis on the downloaded data and make comparisons on various shares with graphs and numbers.

Project Description

Firstly, the program provides simple moving median (SMM) for 10-, 20-, 50-, 100- and 250- days. To implement SMM calculations, pricing information such as opening, day-high, day-low, and adjusted closing are needed.

Relevant data can be downloaded from many financial web sites, including hk.finance.yahoo.com. Further information about SMM, it can be obtained from the following link: https://en.wikipedia.org/wiki/Moving_average#Moving_median

Secondly, the program should implement Bollinger bands in which they are volatility bands placed above and below a moving average. End users should be able to enter a custom number of days that is served as the day for the Bollinger bands calculation. Further information can be found from the following link: http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:bollinger_bands.

Below is an expected output from the 20 day Bollinger bands:

Thirdly, the program should provide a function for graphs plotting among trading days. It is suggested that using Macro recorder offered in Excel VBA to give you some startup/skeleton. For example, the following is the extract from the Macro Recorder that records the steps to plot a graph:

Sub Macro1() 
'
' Macro1 Macro
'
'
Range("A:A,G:G,L:L").Select 
Range("L1").Activate 
ActiveSheet.Shapes.AddChart.Select 
ActiveChart.ChartType = xlLine 
ActiveChart.SetSourceData Source:=Range( _
"'0005.HK'!$A:$A,'0005.HK'!$G:$G,'0005.HK'!$L:$L")
End Sub

Throughout the assignment, you can use the Macro Recorder to give you some ideas on how to implement a particular functions. For example, the following extract is to show how to download a stock data (i.e., 3988) from the Internet and integrate the data into the active workbook.

Sub Macro2()
'
' Macro2 Macro
'

'
Workbooks.Open Filename:="http://ichart.yahoo.com/table.csv?s=3988.HK" 
Sheets("table").Select
Sheets("table").Move After:=Workbooks("InvestmentPortfolio.xlsm").Sheets(4) 
Sheets("table").Select
Sheets("table").name = "3988.HK" End Sub

After Macro2() has been executed, the workbook should have an additional worksheet containing the following:

HSBC (0005.HK) from hk.finance.yahoo.com

Requirements

There is no specific design of graphical user interface (GUI) for this assignment and students are free to design their own GUI for the portfolio. The VBA program needs to provide the following basic functions.

1. Provide a set of controls (e.g., buttons) to manipulate all functionalities in the “MyPortfolio” worksheet of an Excel file. For example, the controls for capturing the most updated data, calculating SMM, calculating Bollinger Bands, and plotting
2. Download the historical data of all candidate stocks listed in the “MyPortfolio” worksheet from hk.finance.yahoo.com. The downloaded pricing information for each stock is stored in a separate worksheet named as its stock number (E.g. “0005.HK” is the name of the worksheet for HSBC’s historical data). Use only one Excel file to store both the “MyPortfolio” worksheet and candidate stock
3. The “MyPortfolio” worksheet contains stock number, company name, purchase date, unit, price, latest stock quote, and total amount for each candidate stock as well as the total investment and total market value in the
4. Calculate the Simple Moving Median (SMM) in 10-, 20-, 50-, 100- and 250-days.
5. Calculate the Bollinger Bands with the specified number of
6. Present a stock pricing information in a chart. You can make use of the Macro recorder function to find out how to write a program for creating
7. Your program must provide comments to explain the code and maintain a good programming style, i.e. choosing a meaningful variable

In addition to the basic functions, the VBA program needs to possess an additional functional work:

1. Provide a user-friendly interface in the program. Your “MyPortfolio” worksheet and controls should be well- organized and convenient to use. E.g., all buttons are associated with macros, the user does not need to scroll down to the bottom of the worksheet to check his/her total investment, etc...

Marking Criteria

Note: We will use Excel 2013 Windows for grading. Please make sure your submission can work on Excel 2013 in a Windows system.

Very Important!!!

You are NOT ALLOWED to use any Excel built-in formulas in data cell to perform math calculations. Instead, you have to use your own code to do the calculations. Examples are given below: