Task 1

Go to the ASIC website (http://www.asic.gov.au/) and read the ‘About ASIC’ section so you know a

little about what they do. Now go to this part of the ASIC website and read Regulatory Guide 196.

(http://www.asic.gov.au/asic/asic.nsf/byheadline/Short-selling-reporting?openDocument – if this

link doesn’t work with a click then copy and paste it into your browser)

Task 2

On the web page in Task 1, click on ‘View table of short position reports’. Within the table titled

‘Daily aggregate short position per stock’ find and download the daily short sales data (CSV file) for

19-02-2014. Save it as a new spreadsheet called AsicSortedShortSales. Sort this data so that the

most shorted stocks are at the top (see ‘% of Total Product in Issue Reported as Short Positions’

column) and save your work.

Task 3

Visit http://help.yahoo.com/kb/index?page=content&y=PROD_FIN&locale=en_US&id=SLN2311 and

read about how stock prices (and therefore returns) are adjusted for splits and dividends. The

approach here is used by most data providers.

You will be working with two stocks (or possibly an ETF) for the assignment. One is from your

AsicSortedShortSales spreadsheet the other will be BHP.AX or CBA.AX. The data you will work with is

based on your student number.

(i) Take the last digit of your student number and identify the corresponding shorted

stock in your sorted spreadsheet (AsicSortedShortSales). E.g., if your student

number ends in eight (e.g. 9917568) you must identify the eighth (8th) most shorted

stock.

(ii) If the second last digit of your student number is EVEN then you will be working

with CBA.AX. Otherwise, if the second last digit of your student number is ODD then

2

you will need to collect data for BHP.AX. Take care with this as significant marks will

be lost if you fail to acquire the correct data.1

You will collect monthly price data for both your stocks from au.finance.yahoo.com. For this

assignment we will be using data spanning February 2012 to January 2014 (a total of 24 months).

Save this data in an Excel file called ‘SIM’.

Place the data for your two stocks in different tabs in your SIM spreadsheet and label the tabs by

their stock code. You will need to sort the data by the date column (oldest to newest).

Tip: Here is an example of collecting the data for Westpac (WBC.AX). You will need to change the dates and the

stock. The data file can be downloaded from the link at the bottom of the following web page.

http://au.finance.yahoo.com/q/hp?s=WBC.AX&a=00&b=29&c=1988&d=02&e=27&f=2013&g=m .

Task 4

Now determine the monthly return series for both stocks using the adjusted close prices. Keep this

data within the tab. You can delete the Open, High, Low, Close and Volume data as we won’t be

using this in the assignment. What is the mean and standard deviation of the returns for your two

stocks?

Task 5

Visit yahoo finance again and download the monthly price data for the S&P/ASX-200 Index (^AXJO).

Place this data in a separate tab on your spreadsheet and calculate the monthly returns.

Task 6

Now go to the RBA’s website (rba.gov.au) and download the monthly yields for bank-accepted-bills.

Visit http://www.rba.gov.au/statistics/tables/index.html#interest_rates and choose ‘Interest Rates

and Yields – Money Market – Monthly F1 [XLS]’

Ideally we would use this one-month Treasury Note data (FIRMMTN1) but there are gaps in this

data. So we will use the 30 day bank-accepted bill as a proxy (FIRMMBAB30). Note that these rates

are quotes as per annum rates.

Task 7

What are the differences between a bank-accepted-bill (BAB) and an Aust. Gov. Treasury note?

Which would you expect to have the higher yield and why? What happens to these yields in a

banking crisis? Can you find evidence on the RBA website to support your view?

Task 8

Conduct a Single Index Model regression for your two stocks return using the 30-day BAB as the riskfree

rate. Use the S&P/ASX-200 Index as the market index. You should take care to make sure the

data is correctly aligned. The Bank Bill yields are expressed as a percentage per annum. These will

need to be converted into a monthly rate (divide by 1200).2 Include the regression results in each tab

along with a graph of the SCL. You can get Excel to output the graph in the regression stage.

Form a table in a new worksheet called ‘Results’ that has the columns labelled (alpha, beta, standard

error,

1. Introduction (1 mark)

Write an explanation of what your report does and briefly summarise the findings. This MUST NOT

exceed 50 words and cannot contain formulae.

2. Stocks Analysed (2 marks)

Create a table containing the TWO stocks. Your table should identify the stock code, company name,

relevant industry (sector) groups, the average return and the standard deviation of the returns.

Which of your two stocks do you think is the most inefficiently priced? Why?

3. Data and the risk-free Asset (3 marks)

Note the sources for your data and how adjustments for dividends and stock splits have been made.

You should also discuss the suitability of the instrument you have used for the risk-free asset.

4. The Single Index Model (2 marks)

Write down the SIM and fully define/explain all terms.

5. Regression Results (4 marks)

Present the table from task 8. The table must be self-contained, clear, and documented. This means

there should be no need to look outside the table in order to interpret the results. Compare and

discuss the alphas and betas you have found. Interpret the quality of your regressions. Why might

the betas differ to the betas found from other sources (such as Bloomberg)?

6. Trade Idea (6 marks)

Consider the stock that you are working with that appeared on the most shorted list. Assuming no

margin requirements, explain how you might construct a statistical arbitrage between this stock and

an S&P/ASX-200 index ETF. Your answer must show the weights for all investments and discuss the

risks on this trade. All working must be presented.

The final two marks will be

**Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.**