Excel VBA Developer
Budget: £20 – £250 GBP
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
b. 2 Oscillator rules: Relative strength indicator
c. Filter rules:
3) Build the above mentioned 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.
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.
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.
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.
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
b. 2 Oscillator rules: Relative strength indicator
c. Filter rules:
3) Build the above mentioned 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.
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.
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.
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.