Explore BrainMass
Share

Explore BrainMass

    Excel - ASIC website, Single Index Model regression

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    Now I am working on a financial report problem. But before finish the report, I want to someone to help me to do an Excel for perfecting my work.
    The excel is NOT required by my professor, and She said we can ask someone else to do the excel part because the excel part is not included in this class. I will do the report by myself anyway.
    Here is my requirement, I think each step is simple and can be done quickly.

    A.
    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://asic.gov.au/regulatory-resources/find-a-document/regulatory-guides/rg-196-short-selling/ (if this
    link doesn't work with a click then copy and paste it into your browser).

    B
    Go to the ASIC website section under http://asic.gov.au/regulatory-resources/markets/short-selling/. 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 July 6, 2015. Save it as a new spreadsheet
    called AsicSortedShortSales. Sort this data so that the stocks that have the highest number of reported
    short positions are at the top (see 'Reported Short Positions' column) and save your work.

    C
    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 for the assignment. One is from your AsicSortedShortSales
    spreadsheet the other will be BHP.AX or NAB.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 (by reported position).
    (ii) If the second last digit of your student number is EVEN then you will be working with NAB
    AX. Otherwise, if the second last digit of your student number is ODD then 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.
    You will collect monthly price data for both your stocks from au.finance.yahoo.com. For this
    assignment we will be using price data spanning July 2013 to July 2015 (a total of 24 months
    of return data). Save this data in an Excel file called 'SIM'. If your stock has data beginning after
    July 2013 then please use the data that is available up to July 2015. In this case you should
    investigate why this might be.
    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 NAB (NAB.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: https://au.finance.yahoo.com/q/hp?s=NAB.AX&a=00&b=29&c=1988&d=02&e=27&f=2013&g=m
    D.

    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?

    E.
    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.

    F.
    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.

    G.
    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?

    H.
    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.

    © BrainMass Inc. brainmass.com October 10, 2019, 8:10 am ad1c9bdddf
    https://brainmass.com/business/finance/excel-asic-website-single-index-model-regression-611650

    Solution Summary

    The expert examines single index model regression and ASIC website using Excel. The response addresses the query posted in excel files attached

    $2.19