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:
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.
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.
- 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
- 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
- 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
- Calculate the Simple Moving Median (SMM) in 10-, 20-, 50-, 100- and 250-days.
- Calculate the Bollinger Bands with the specified number of
- 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
- 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:
- 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
|
Area |
Percentage |
|
General · Your VBA program must apply the followings: o A worksheet called “MyPortfolio” to record all stock purchases’ information (i.e., stock number, company name, purchase date, unit, price, latest stock quote, total amount, total investment and total market value), VBA buttons and Macros. o Worksheets record individual share’s historical pricing information. These information can be found in the following website: http://hk.finance.yahoo.com · You may assume that the latest stock quote is the most updated record in the corresponding stock pricing data. |
30% |
|
Simple Moving Median (SMM) · Your VBA program must implement the SMM in 10-, 20-, 50-, 100- and 250- days. |
10% |
|
Bollinger Bands · Your VBA program must provide a choice from users on the number of days for the implementation. |
20% |
|
Charts · Plotting graphs can be done by using Macro Recorder. |
10% |
|
Creativity (GUI) · Your VBA program must provide well-designed and well-organized user interface. (Remark: A user-friendly interface should provide an easy access for users to use the program. For example, the “MyPortfolio” worksheet and controls should be well-organized so that 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.) |
15% |
|
Comments · Your VBA program must contain comments to explain the code. · All the comments must be relevant. o E.g. 'Debug.print i is not a relevant comment |
15% |
|
Total : |
100 % |
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:
|
Mistake #1 (Building a custom formula in a data cell): Cells(1, 1).Value = "=B1 + B2" Instead, you should write a full calculation statement and assign the result back to the data cell. |
|
Public Sub Example1() Cells(1, 1).Value = CDbl([B1].Value) + CDbl([B2].Value) End Sub |
|
Mistake #2 (Calling an Excel built-in formula in a data cell): Cells(1, 1).Value = "=Average(A1:A10)" Instead, you should write your own function to perform the same task as the built-in formula. |
|
Public Sub Example2() Dim r As Range Dim Total As Double Total = 0 For Each r In [A1:A10] Total = Total + CDbl(r.Value) Next Cells(1, 1).Value = Total / ([A1:A10].Count) End Sub |
|
Mistake #3 (Using WorksheetFunction object in your code): MsgBox WorksheetFunction.Average(Range("A1:A10")) Instead, you should write your own function to perform the same task as in the WorksheetFunction. |
|
Public Sub test() MsgBox Example3([A1:A10]) End Sub Public Function Example3(ByRef MyRange As Range) As Double Dim r As Range Dim Total As Double Total = 0 For Each r In MyRange Total = Total + CDbl(r.Value) Next Example3 = Total / MyRange.Count End Function |
USA 
India