ISOM3230 - Assignment - Solved

Learning Outcomes
1.      Apply programming concepts to solve business problems 

2.      Describe the logic and flows of given programs 

3.      Predict the output of a program 

4.      Write programs with common programming practices 

5.      Identify and fix logical and runtime errors in programs 

 

 

Background 
 

As a layman in HK stock market, Ken lost most of his share investments dramatically during the financial tsunami. Ken has started thinking of a better way to invest on stocks. After doing some researches on the web, Ken has finally realized that he needs a share analyzer to provide some financial indicators before making a stock purchasing decision. In this assignment, you are asked to develop a VBA program to analyze stock pricing information. It can be treated as an investment analyzer. The VBA investment analyzer can access stock pricing information from the Internet (e.g., https://www.quandl.com) and perform basic analysis on them in order to facilitate the investment decision making process. 

 

This program provides simple moving average (SMA) for 10‐, 20‐, 50‐,100‐ and 250‐ days, and also provides graphs plotting among trading days. The program can compute a Golden Cross indicator for all shares being considered so the user may have some ideas for his/her purchasing decisions. 

 

The following statement can download stock pricing information of CKH Holdings from https://www.quandl.com.

  

Application.Workbooks.Open("https://www.quandl.com/api/v3/datasets/HKEX/00001.csv?api_key=1Yn jPZgaqTtVq3cwQERs")

 

 

 
After executing the statement, an Excel file will be created and it will contain the following information. 

 

 

            CKH Holdings. (00001) from quandl.com

 

In the program, you are required to implement Simple Moving Average (SMA) for 10‐, 20‐, 50‐, 100‐, and 250‐days evaluations. Pricing information including nominal price, previous close are involved in the calculations. Further information about SMA can be obtained in the following link: 

http://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:moving_averages 

 
 

There is no specific design of graphical user interface (GUI) for this assignment and students are free to come up with their own designs. The VBA program needs to include the following basic functionalities. 1. Provide a set of controls to manipulate all functionalities in the “MyAnalyzer” worksheet of an Excel file.

For example, the controls for capturing the most updated data, calculating SMA, Golden Cross Indicator,

                                and plotting graph.  

2.       Read stock information of all candidate stocks listed in the “MyAnalyzer” worksheet  

3.       Download all listed share records from quandl.com. The historical data of each stock should be stored in a separate worksheet named as its stock number (E.g. “00001” is the name of the worksheet which contains

CKH Holdings’ historical data). Use only one excel file to store both the “MyAnalyzer” worksheet and other

                                stock pricing record worksheets.  

4.       Update the latest stock quote of the candidate stocks in the “MyAnalyzer” worksheet  

5.       Calculate the Simple Moving Average (SMA) in 10‐, 20‐, 50‐, 100‐ and 250‐days  

6.       Implement the Golden Cross, an uptrend Indicator, to show the situation that a short‐term moving average (e.g. 20‐days) of a stock’s price exceeds its long‐term moving average (e.g. 250‐days). Your program must check the existence of this indicator (i.e. yes or no) and present with the corresponding

                                crossing date for all stocks in the “MyAnalyzer” worksheet.  

7.       Plot graph (e.g. the basic chart for each candidate stock) by using a macro. You can make use of the macro recorder function to find out how to write program for creating charts.  

8.       Your program must provide comments (documentation) to explain the code and maintain a good programming style, i.e. choosing a meaningful variable name.  

 

                   In addition to the basic functions, the VBA program will possess the following additional works: 

1. Implement some additional functions in the VBA in order to help the user to have some ideas for making his/her own investment (purchasing or selling) decisions.  

For example, your additional function can be based on some financial models or technical analysis indicator such as Exponential Moving Average (EMA) and Bollinger Bands. You can also implement the best‐buy index for all shares based on your own design. Typically, the index is likely to be related to a formula mixing some financial indicators such as SMA, SMM, RSI, ROC and MFI. Please note that you must 

                        indicate how to interpret your own best‐buy index, i.e. the higher the number, the better to buy.  2. Provide a user‐friendly interface in the program. Your “MyAnalyzer” 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, profit and loss, etc.