Preamble
This individual assignment is worth 20% of your final grade. The aim of this project is to
expose you to: (i) acquiring Australian data; (ii) contemplating subtleties in financial data
such as stock splits, dividend payments and yield differences on short-dated debt
instruments in Australia; and, (iii) applying simple regression techniques and interpreting
the output in terms of a financial model (SIM) as a template for the more complex factor
models used in industry.
Task list
You should complete all eight tasks below before writing your report. You should document
your work for each task and maintain your Excel spreadsheet with a view to writing the final
report. The tasks themselves do not constitute the report and marks are not awarded
directly for completed tasks—marks are awarded on the basis of the report.
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.
Task 2
Now find the daily short sales data for 2013 on the ASIC website and download the daily CSV
file. Take the data for the latest day and place it in a new spreadsheet and name it
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). Identify the FOUR most shorted
stocks in Australia at this time (note the date at which you determined this). Save the file as
an .XLSX file for reference.
Task 3
Go to this site:
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.
Go to the yahoo finance website (http://au.finance.yahoo.com) and download the monthly
price data for the FOUR most shorted stocks as identified in Task 2. Additionally, collect the
data for these FOUR stocks: CBA.AX, COH.AX, BHP.AX, and RHC.AX. Your monthly price datashould span the period Sep-2007 to Mar-2013. Place each stock in a different tab
and label
the tabs by their stock code. You will need to sort the data by the date column (oldest to
newest). Warning: be careful to choose the right data. All your share codes should end in
‘.AX’.
Here is an example of the data for Westpac (WBC). You will need to change the dates and
the stock. The data file can be downloaded from the link at the bottom of this 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 each stock 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 each stock?
Task 5
Visit yahoo finance and download the monthly price data on the All Ordinaries Index
(^AORD) and the S&P/ASX-200 Index (^AXJO). Place this data in separate tabs and calculate
the monthly returns. Compare the returns on these two indices and explain any differences.
Task 6
Now go to the RBA’s website (rba.gov.au) and download the monthly yields for bankaccepted-
bills
Go here-> 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 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 each stock return series using the 30-day BAB
as the risk-free rate. 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).1 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,
