Posted: May 29th, 2019

PORTFOLIO/VBA ASSIGNMENT – PROJECT III

PORTFOLIO/VBA ASSIGNMENT – PROJECT III
Due Date: Friday 7 June 2019, 23:59 Weighting: 30% Type: Individual Submission: Electronic Submission via AUT Online
Instructions
Complete your answers in this excel workbook: Answer_File_(V1_2019).xlsm, once you have finished your answers, upload your solutions to Blackboard to be marked. You have to complete the two following tasks using VBA:
Trading Strategy (20 marks)
In the worksheets “Prices” and “Price-to-Book Values”, you can find monthly share prices and market values for NZ stocks, respectively. Use this information to implement a trading strategy that, at the end of each June, buys the stocks belonging to the 10% of the stocks with the lowest price-to-book values and sells the stocks belonging to then 10% of the stocks with the highest price-to-book values. Then hold the portfolio for one year until next June. In the worksheet “Trading Strategy”, generate a graph that shows the returns of this trading strategy over time. On the same worksheet, show the total return of this trading strategy. Assume that the selected stocks are weighted in the portfolios based on their market values.
Data Scraper (10 marks)
Create a drop-down list in the worksheet “Data Scraper” using the list of tickers provided in the worksheet “Ticker”. The user should be able to select a ticker in the list and then to click on a button. By clicking on the button, the user should launch a VBA code that scraps the income statement of the company corresponding to the ticker from the internet for the year 2016 and display it in the worksheet “Data Scraper” in a readable way to the user. As a source of inspiration, you can use the code of the Data Scraper available in the file “Extra VBA 2 – Scrap Data”. If you are only able to do it for a restricted number of tickers in the list, highlight them in red and use only these tickers in the drop-down list.
On top of the usual marketing criteria, the clarity and efficiency of the VBA code is going to be considered in the marking. You can create other worksheets to store information (for the stock returns for instance). Marks will be deducted if you are not using VBA to complete the tasks.
The answers will only be marked if built within the file AnswerFile.xlsm available from AUT Online. Students are expected to be able to explain or demonstrate any features or techniques in their assignments if requested. Students should also be aware that stolen USB’s, laptops and corrupted files are not considered grounds for an extension under the BBus Assessment Guidelines, make sure you back up your work securely.