Reference no: EM133011121

BAFI3252 Modelling in Finance - RMIT University

Background information
Technical analysis is a form of investment valuation that analyses past prices to predict future price action. The moving average (MA) method is one of the most widely used methods of technical analysis. A moving average is an average of observations from several successive time-periods. To estimate a moving average sequence, we calculate successive averages of a predetermined number of successive observations. For example, 5-day MA at day t is the average of daily prices from day t-4 to day t. Table 1 shows how the 5-day MA is calculated. The calculation is repeated for each subsequent day. This method is known as simple moving average (SMA).

The objective of the MA method is to smooth out variations in price data. Crossovers are one of the main MA trading strategies. The method compares the recent market price with the MA of the stock price, known as crossovers. If the current price breaks through the MA from below accompanied by significant trading volume, this is considered as a positive change and a signal of an upward trend. In this case, it suggests a long investment position. Conversely, if the current price breaks through the MA from above, a short position is taken. One common variation is to replace the current market price with a short MA and compares the short MA with the long MA. The above suggests the following buy and sell signals when:
- Buy signal at time t: short-term MA breaks through long-term MA from below at time t and price at time t is above both MAs;
- Sell signal at time t: long-term MA breaks through short-term MA from above at time t and price at time t is below both MAs;

The effectiveness of this method largely depends on how the moving averages are calculated i.e. how many past observations are used to estimate MA and the methodology used to estimate the MA. There are three types of MAs, simple (arithmetic), weighted (linear), and exponential. The most popular simple moving averages to use in a crossover strategy are the 50 and 200 SMAs. Figure 1 shows examples of buy and sell signals based on short-term and long-term SMAs.

Task
In this assignment you are asked to evaluate the strategy of buying on a cross up and selling on a cross down. You are given daily price information where you will calculate returns, moving averages and identify buy and sell signals. The analysis will be based on the given historical price information. You can use Excel formulas and create additional variables in the spreadsheet to assist you in completing the tasks. There are four worksheets in the file assignment2.xlsm. The main worksheet is where you interact with the users (for inputs and outputs). The data worksheet has daily stock price information required for the analysis. The two ‘free' worksheets are provided if you want to use them to save the results. Do not add anymore worksheets in the file.

Part A - Required features
1. Write a VBA subroutine to calculate daily stock returns (use simple returns)
2. Write a VBA subroutine to calculate 50 and 200-day simple moving averages
3. Write a VBA subroutine(s) to identify the buy and sell signals based on the conditions for buy and sell signals discussed in the background information. At each buy and sell signals, calculate cumulative returns (sum of returns) of subsequent 5, 15 and 25 days. For example, the 5-day window is from the first day after a signal (not including) to the fifth day.
4. Write a VBA subroutine to produce summary statistics as described in Table 2 and comment on the findings (write detail comments in the textbox).
Note: If you are unable to complete VBA subroutines to fully automate the tasks, use a combination of Excel formulas, spreadsheet modelling and VBA (partial) to handle the tasks.

Part B - Additional features:

1. Design a dynamic model where the users can select (or input) two moving average periods - one for short term and one for long term. The model will then generate the results (repeat questions 3 and 4 part A) based on the chosen MAs.

2. Keep the data in one worksheet with all the necessary calculations (and use the free worksheets provided only to temporarily save the results). All input parameters, control buttons (optional), instructions and main outputs must be visible in the main worksheet.

3. Think about other features you want to add and implement the ideas into your model. You are encouraged to use your initiatives and seek other sources for ideas on what else to include.

Part C - Comments and structure

1. The input/output are clearly presented, and the model is well structured.

2. Provide instructions and highlight features (other than those required) of your model. Write your comments in the textbox (insert->textbox).

3. Add comments to your VBA code, especially on any special techniques employed.

4. Properly formatting and indenting your code. To indent code, simply press the TAB key. Press the tab key again to add a second code indention. Generally, you want to add one indentation for each code block such as IF statements and Loops.

Attachment:- Modelling in Finance.rar