FIN2020 Excel and VBA – Individual Assignment
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FIN2020 Excel and VBA – Individual Assignment
Assignment
First, you should download a workbook entitled "Technical trading rules.xlsx" from the Individual assignment page on Canvas. The workbook contains different spreadsheets to help you finish this assignment. The cell references, formulas, and functions must be saved and remain active in the spreadsheets. (Don't paste values!) Another workbook entitled 'Individual project information.xlsx' provides essential information related to the questions.
Part I: Data collection and processing (80%)
In the first part of this assessment, you are expected to collect and process data in MS Excel. You need to create new sheets to solve the following questions:
1) Each student is assigned 5 constituent stocks from the Standard & Poor's 500 index. Please check the details from the 'Stock names' worksheet of 'Individual project information.xlsx'. Obtain the daily closing price (last_price) series for these stocks from Bloomberg or other appropriate data sources. The data sample should range from 01/09/2021 (1st September 2021) to 31/08/2022 (31st August 2022). Save the obtained price series into the workbook and name the new worksheet 'Stock prices'. 5%
2) Use the knowledge from our lectures to construct 5 technical trading rules for each stock. You need to save all the process data, formulas, and functions in the workbook properly. The 5 trading rules include the following:
a. 1 Moving Average rule: If the daily closing price of the stock moves up above the moving average over the past q days, MA(q), go long the stock until its daily closing price moves down below MA(q), at which time go short the stock. If the stock's daily closing price moves down below MA(q), go short until the daily closing price moves up above MA(q), at which time go long the stock.
b. 2 Oscillator rules: If the relative strength indicator (RSI) over the past h days, RSI(h), is above 50 + v for at least 2 days and then moves below 50 + v, go short the stock and hold the position until the next signal. If RSI(h) is below 50-v for at least 2 days and then moves above 50-v, go long the stock and hold the position until the next signal. No signals are generated in other scenarios.
c. 2 Filter rules: If the stock's daily closing price moves up at least x percent above its most recent low, go long the stock and hold the position until the next signal. If the daily closing price drops below its most recent high by at least x percent, go short the stock and hold the position until the next signal. In this context, we define the most recent high (low) as the highest (lowest) daily closing price over the past k trading days.
As a result, you will have 5*5=25 trading rules in total. The trading rule inputs, e.g., q, h, v, x, and k, are assigned according to your student number in the worksheet 'TTR inputs' of 'Individual project information.xlsx'. 20%
3) Build the abovementioned technical trading rules using VBA codes. 15%
4) Based on the returns of trading rules, compute the summary statistics of the trading rule performance, including mean and standard deviation for each trading rule return series in rows 4-5 of the 'Performance summary' worksheet. You may need to change the content in rows 2 and 3 to correctly present the stocks' names and trading rules' names. 5%
5) Using your knowledge of VBA, create a VBA function called Sharpe Ratio, which takes the range of returns and the risk-free rate series as inputs and outputs its Sharpe ratio. Once you have created this function, use it to calculate the Sharpe ratio for each trading rule series in row 6 of the 'Performance summary' worksheet. The annual risk-free rates are available in the 'Risk free rates' worksheet. 10%
6) Using your knowledge of VBA and Excel, extract real-time prices of the five stocks assigned to you from online resources, e.g., Yahoo Finance, Investing.com or any other available data sources. Then, update the stock price series to cover prices from 01/09/2021 to the current date, and the new price series should be updated dynamically according to the date. Please adjust your workbook to display the performance of TTRs in the 'Performance summary' worksheet based on the updated data. 15%
7) Format every worksheet to ensure the data and results are clear to the audience, e.g., add and format the worksheet titles, and adjust the row height and column width. 10%
Save your workbook titled 'Your student number+TTR' (for instance, your student number is 123456, the file title should be 123456TTR) and ensure it is in the appropriate format to support the VBA code.
Part II: Project report (20%)
Next, please write a project report in MS Word to evaluate the technical trading rule. Insert the performance summary table from the 'Performance summary' worksheet in the report using an appropriate professional format. For each stock, select 1 of the 5 rules as the best-performing rule based on the mean of trading rule returns, standard deviation, and Sharpe ratio, respectively. The report should have no more than 800 words. 20%
2022-12-05