“Optimal complete portfolio and systematic risk of Australian firms: Impact of Global Financial Crisis (GFC)”
(1) BUS329 (Investment Analysis) Assignment : How to download data from Yahoo Finance This guidelines show you how to download stock price data from Yahoo Finance. The first part is related to data you need to construct optimal complete po rtfolio and the second part is related to data you need to calculate systemat ic risks . First part: Constructing optimal com plete portfo lio (1) Th is part of the assignment is about constructing optimal comple te portfolio with two risky assets and one risk -free asset. (2) For risk -free rate use 1 month Bank Accepted Bill (BAB) rate available on Reserve Bank of Australia (RBA) website as follow: (http://www.rba.gov.au/statistics/tables/#interest -rates ) (Take the average of BAB rate during your sample period) . You will need to click “ Interest Rates and Yield – Money Market – Monthly – F1.1 ” t o get this data. After clicking an excel file will be downloaded , where you will find 1 -moth BAB rate. (3) For risky assets choose two stocks from Australia Stock Exchange (ASX) (4) Download stock price data from Yahoo Finance for free (website:
https://a u.finance.yahoo.com/q/hp?s=ASX.AX ) (5) How to download data from Yahoo Finance website: You need to enter stock symbol here to download the price of your selected stock. (6) Stock symbols are available in ASX website (http://www.asx.com.au/asx/research/listedCompanies.do ). Clicking this link will give you the following web page: (2) (7) You need to use these ASX codes (this is what we call symbol above) to download stock price. For example , suppose you want to download stock price of ABACUS PROPERTY GROUP. Code for this stock is ABP. So you type ABP in the box and click „Look Up‟ as follows: (8) This will return the following page: (9) Now click „Hist orical Prices‟. This will return the following page: (3) (10) Now select your start and end date here. Download monthly data by clicking „Monthly‟. I strongly recommend using monthly data to avoid the complexit ies of handling public holiday s, non -trading day etc. O nce you have selected start and end date and checked „Monthly‟, click „Get Prices‟ to download the data. When data download is complete, go to the bottom of the page and click „Download to SpreadSheet‟ to get data in CSV format. Finally save the data as per your convenience. For analysis purpose use the stock price along the „ Adj Close‟ column (11) Repeat the same process to download your second stock price data. (12) Use monthly data from J uly 2008 to December 2016. (13) Next you will need to calculate stoc k return from stock prices. Use the following formula to calculate the return series: (4) (1) For example, in the screenshot below „Adj Close‟ price on 1 June 2016 is AU$3. 07 and that on 1 July 2016 is AU$3.1 0. Therefore return on 1 July 2016 is calculated as follows: Using stock price data the return series can easily be generated in excel. (14) If the data are downloaded from ‘New’ to ‘Old’, then you need to convert them from ‘Old’ to ‘New’. For example, in the screenshot above 1 July, 2016 data come first and then 1 June 2016 – this is ‘New’ to ‘Old’ pattern. You will need to convert them to ‘O ld’ to ‘New’ form. This can easily be done by using ‘Sort ’ under ‘Data’ in Excel (15) For your analysis you will need to calculate variance, covariance, standard deviation etc. for the stock return series. You can calculate these by using formulas in excel. Please visit the following YouTube website to see how excel can be used to calculate those statistics: https://www.youtube.com/results?search_que ry=how+to+calculate+variance%2C+covarian ce+in+excel (16) Calculate optimum weights of two risky assets (say, asset S and Asset B) by using the following formula [this is similar to the formula given in Equati on 7.13 (page 217) of your text] 100 1 1 t t t t P P P r % 98.0 100 07.3$ 07.3$ 10.3$ 2016 1 AU AU AU rJu ly (5) Use average of st ock returns calculated using formula (1) above as a proxy for expected return, E(r), in the above equation. Similarly use the average of risk -free rate as a proxy for r f in the above equation. (17) You will also be required a value for risk aversion coefficient , A. It is not available anywhere. It is a subjective value. Use any one of the following three risk aversion coefficients: (a) Highly risk -averse investor: if you think you are highly risk averse, then use 5 as your risk -aversion coefficient (i.e., A = 5) (b) Mod erate risk -averse investor: if you think you are moderately risk averse, then use 4 as your risk -aversion coefficient (i.e., A = 4) (c) Low risk -averse investor: if you think you are low risk -averse investor, then use 3 as your risk -aversion coefficient (i.e., A = 3) (18) Split the whole sample into two sub -periods: (a) From July 2008 to December 2011 (this period is a proxy for Global Financial Crisis (GFC). (b) From January 2012 to December 2016 (this period is a proxy for post Global Financial Crisis (post -GFC). (6) Second part: Systematic risk of the risky assets (1) In this part of the assignment you will be required to calculate the systematic risk of the risky assets, that is, two stocks used in Part A of the assignment. In other words, you are required to calculate β (beta) of each stock. (2) Split the whole sample into two sub -periods: (3) From J uly 200 8 to December 2011 (this period is a proxy for Global Financial Crisis (GFC) and hence is expected to capture the effect of GFC on stocks‟ systematic risk) (4) From January 2012 to December 2016 (this period is a proxy for po st Global Fnancial Crisis (post -GFC) and hence is expected to capture the stocks‟ systematic risk during post -GFC period) (5) You can calculate beta by using the following formula: (2) here r M is return on market portfolio. You can use ASX All Ordinary index as a proxy for market portfolio. (6) To download ASX All Ordinary index data type „^AORD‟ in the box as below: (7) Now click „Look Up‟, which will return the following page. M M i i r Var r r Cov , (7) (8) Now click „Historical Prices‟ and download ASX All Ordinary index following steps described in above. (9) Calculate ASX All Ordinary index return using formula given in eq (1) above. (10) Calculate covariance between stock return and index return (you will need to calculate two co -variances for two stock returns. (11) Calculate variance of index return (12) Finally calculate beta using formula in equation (2) above. (13) Compare the betas (GFC beta vs post -GFC beta) Important Note: This is a gro up assignment. Each gro up will work on two firms ‟ stock price data ; however, each group has to work on different pair of firms ‟ stock prices. For example, if Group 1 is work s on Firm A and Firm B, no other group can work on Firm A and Firm B data; another group can wo rk on Firm A and Firm C data or Firm B and Firm C data. Remember exactl y same two forms ‟ data cannot be used by multiple groups. If two groups work on exactly same two firms ’ data, they will arrive at exac tly same result , which will be treated as serious type of plagiarism. So using exactly same two firms ‟ data by more than one group is strictly prohibited.