The Single Index Model

The Single Index Model


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 ( 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:

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 ( 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


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.


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 ( and download the monthly yields for bankaccepted-


Go here-> 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,

Last Updated on February 11, 2019 by Essay Pro