On the right-hand side, under “Quote Lookup
[ad_1]
EXAMPLE OF HOW TO CALCULATE BETA (and others) USING EXCEL
1. Go to https://au.finance.yahoo.com
2. On the right-hand side, under “Quote Lookup”, type in the name of your company
allocated to your group.
3. Make sure that you choose a company that is listed in the Australian Security Exchange.
This is usually shown as XYZ.AX if the company is XYZ.
4. Select Historical Data
5. Type in the time period. e.g. Start Date: 8/9/2018, End Date: 8/9/2019. The date
convention (Australia or US) will depend on the computer you are using.
6. Under frequency, choose “weekly”
7. Select “Apply”
8. Select “Download Data”
9. Open with “Microsoft Excel”
10. Save as – File Name: XYZ, and type : Excel Workbook
11. Repeat Step 2 to Step 9 for ^AORD. This index represents the price of the market
portfolio.
12. Save as – File Name: AORD, and type: Excel Workbook
13. For both files (10 and 12), keep only the Date and the Adj Close columns. Delete other
columns.
14. Copy the Adj Close data in AORD to the XYZ file. Refer to the attached Excel example.
15. Label Y-Axis Security Return, and X-Axis Market Return.
16. Calculate Security Return and Market Return on a weekly basis.
TO INSTALL DATA ANALYSIS IN YOUR COMPUTER
1. Go to:
• File/Options/Add-Ins/Analysis Toolpak/Go/OK
• Tick “Analysis Toolpak”
2. Click Data, and you should see “Data Analysis” on the top right-hand side of the screen.
TO CARRY OUT REGRESSION ANALYSIS
1. Select “Data Analysis”, then “Regression”.
2. Input Y range-type in the cell references for all the data under Y-Axis. e.g. $D$5:$D$52
3. Input X range-type in the cell references for all the data under X-Axis. e.g. $E$5:$E$52
(For multiple regression, select the data under all the four ratios)
4. Choose Output Range. e.g. $G$4
5. Press OK, and the Regression Summary Output will show up from column G to O.
6. For TFIN 501 students, carry out multiple regression and correlation as well.
7. Repeat the work from 2005 to 2019.
8. Record Beta, R-square and p-value. See green highlight in the Excel example.
[Button id=”1″]
[ad_2]
Source link
"96% of our customers have reported a 90% and above score. You might want to place an order with us."
