Question details

The objectives of this exercise
$ 50.00

Beta and Return. 

The objectives of this exercise: 
1. To access and download stock and index price data from yahoo.com. 
2. To discern the difference between a real-time closing price and an adjusted price. 
3. To discover how yahoo.com calculates beta for an individual stock. 
4. To replicate the yahoo.com calculation for twenty firms in the S&P 500 index, tabulate the results and report your findings. 


Learning Outcomes: 

Students should be able to: 
1. Identify individual stocks in the S&P 500 index and discuss the composition of the S&P 500 index. 
2. Collect financial data including closing stock prices and adjusted stock prices. 
3. Estimate beta from historical data. 
4. Tabulate regression results and discuss empirical findings. 

Guidelines: 

1. You will be assigned twenty (20) stocks from the current S&P 500 index (SEE THE STOCKS BELOW). You must use these 20 stocks (or, more precisely, the ticker symbols) to download data from yahoo.com. Go to yahoo.com, then Finance, then type in your ticker symbol. Next choose Historical Prices from the tabs on the left. Tag “Monthly” then choose the appropriate dates (to be determined below). Click Get Prices, scroll to the bottom of the page and download to spreadsheet (it is actually saved as a ‘.csv’ file, but it will open okay in Excel). The last month of return data should be June 2013. Be sure to use end-of-month data! Yahoo might print a beginning of month date in the monthly output, but it should be end of month. To check this, look at the daily data and verify that the last day of the month is used in the monthly spreadsheet (it should be if you wait until after the sample period to collect your data). 
2. You must determine how exactly yahoo.com calculates historical beta, then replicate their calculations. This requires the use of regression software (or functions in Excel, if you know how to do that). Note, you must calculate returns first. Is the data sorted correctly – in time order? If not, you must sort your stock prices and market prices so that the oldest prices are first, then calculate returns. Answer the following questions: 
a. How, exactly, does yahoo.com calculate their beta? How many months of return data do they use? How many months of price data do you need? How do you know that you and Yahoo used the same data period? 
b. Should you use closing price or adjusted price when calculating returns? Why? 
c. What is the market proxy? What ticker did you use for your market proxy? 
3. After calculating returns for your twenty stocks and the market, you must calculate beta for each of your twenty stocks. Create a Table showing the following: Ticker, Company Name, yahoo beta, calculated beta, intercept and r-square from your regressions. 
4. Do your regression results match yahoo.com results? Why or why not? 

Output:

You must submit a brief write-up of your results. Your paper should be detailed enough so that someone else could pick up your paper and replicate your results. So, you will need a brief introduction describing your data sources, how you manipulated the data, how you calculated returns, how you calculated beta, a Table, and a brief summary and discussion of your findings. In total, you must submit: 

1. Your write-up as a Word file including the Table described above in Guideline #3 and your answers to the various questions. 
2. An Excel spreadsheet (one sheet), properly formatted, containing the ticker symbol, closing price, adjusted price and dates (dates should be the same for each ticker) for each of your twenty stocks and your market proxy. 

3. Another Excel spreadsheet, properly formatted, with a detailed example of how, exactly, you calculated the results in your Table (i.e., replicate the detailed findings for one of your stocks). The two Excel worksheets should be in one file with two different labeled worksheet tabs.
 

List of Stocks in the S&P 500 Index on August 31, 2015:
Ticker No. Ticker Symbol Company
1 FOXA 21st Century Fox
2 MMM 3M Co
3 ACE ACE Limited
4 AES AES Corp
5 AFL AFLAC Inc
6 GAS AGL Resources
7 T AT&T Inc
8 ABBV AbbVie Inc.
9 ABT Abbott Laboratories
10 ACN Accenture plc
11 ACT Actavis Inc
12 ATVI Activision Blizzard
13 ADBE Adobe Systems Inc
14 AAP Advance Auto Parts Inc.
15 AET Aetna Inc
16 AMG Affiliated Managers Group, Inc.
17 A Agilent Technologies Inc
18 APD Air Products & Chemicals Inc
19 ARG Airgas Inc
20 AKAM Akamai Technologies Inc
21 AA Alcoa Inc
22 ALXN Alexion Pharmaceuticals Inc
23 ALLE Allegion
24 ADS Alliance Data Systems
25 ALL Allstate Corp
26 ALTR Altera Corp
27 MO Altria Group Inc
28 AMZN Amazon.com Inc
29 AEE Ameren Corp
30 AAL American Airlines Group
31 AEP American Electric Power
32 AXP American Express Co
33 AIG American Intl Group Inc
34 AMT American Tower Corp A
35 AMP Ameriprise Financial Inc
36 ABC AmerisourceBergen Corp
37 AME Ametek
38 AMGN Amgen Inc
39 APH Amphenol Corp A
40 APC Anadarko Petroleum Corp
41 ADI Analog Devices Inc
42 ANTM Anthem Inc (formerly WellPoint Inc)
43 AON Aon plc
44 APA Apache Corp
45 AIV Apartment Investment & Mgmt
46 AAPL Apple Inc.
47 AMAT Applied Materials Inc
48 ADM Archer-Daniels-Midland Co
49 AIZ Assurant Inc
50 AN AutoNation Inc
51 AZO AutoZone Inc
52 ADSK Autodesk Inc
53 ADP Automatic Data Processing
54 AVGO Avago Technologies
55 AVB AvalonBay Communities Inc
56 AVY Avery Dennison Corp
57 BBT BB&T Corp
58 BHI Baker Hughes Inc
59 BLL Ball Corp
60 BAC Bank of America Corp
61 BCR Bard C.R. Inc
62 BXLT Baxalta Inc.
63 BAX Baxter Intl Inc
64 BDX Becton Dickinson & Co
65 BBBY Bed Bath & Beyond Inc
66 BRK.B Berkshire Hathaway B
67 BBY Best Buy Co Inc
68 BIIB Biogen Idec Inc
69 BLK BlackRock Inc
70 HRB Block H & R Inc
71 BA Boeing Co
72 BWA Borgwarner Inc
73 BXP Boston Properties Inc
74 BSX Boston Scientific Corp
75 BMY Bristol-Myers Squibb
76 BRCM Broadcom Corp A
77 BF.B Brown-Forman Corp B
78 CA CA Inc
79 CBG CBRE Group Inc.
80 CBS CBS Corp B
81 CF CF Industries Holdings
82 CHRW CH Robinson Worldwide Inc
83 CMS CMS Energy Corp
84 CNX CONSOL Energy Inc
85 CSX CSX Corp
86 CVS CVS Caremark Corp.
87 CVC Cablevision Systems Co A
88 COG Cabot Oil & Gas A
89 CAM Cameron International Corp
90 CPB Campbell Soup Co
91 COF Capital One Financial
92 CAH Cardinal Health Inc
93 KMX Carmax Inc
94 CCL Carnival Corp
95 CAT Caterpillar Inc
96 CELG Celgene Corp
97 CNP Centerpoint Energy Inc
98 CTL CenturyLink Inc
99 CERN Cerner Corp
100 CHK Chesapeake Energy Corp
101 CVX Chevron Corp
102 CME Chicago Mercantile Exchange
103 CMG Chipotle Mexican Grill Inc.
104 CB Chubb Corp
105 CI Cigna Corporation
106 XEC Cimarex Energy
107 CINF Cincinnati Financial Corp
108 CTAS Cintas Corp
109 CSCO Cisco Systems Inc
110 C Citigroup Inc
111 CTXS Citrix Systems Inc
112 CLX Clorox Co
113 COH Coach Inc
114 KO Coca-Cola Co
115 CCE Coca-Cola Enterprises
116 CTSH Cognizant Tech Solutions Corp
117 CL Colgate-Palmolive Co
118 CPGX Columbia Pipeline Group Inc.
119 CMCSA Comcast Corp
120 CMA Comerica Inc (MI)
121 CSC Computer Sciences
122 CAG ConAgra Foods Inc
123 COP ConocoPhillips
124 ED Consolidated Edison Inc
125 STZ Constellation Brands Inc A
126 GLW Corning Inc
127 COST Costco Wholesale Corp
128 CCI Crown Castle Intl Corp
129 CMI Cummins Inc
130 DTE DTE Energy Co
131 DVA DaVita HealthCare Partners Inc
132 DHR Danaher Corp
133 DRI Darden Restaurants Inc
134 DE Deere & Co
135 DLPH Delphi Automotive PLC
136 DAL Delta Airlines
137 XRAY Dentsply Intl
138 DVN Devon Energy Corp
139 DO Diamond Offshore Drilling
140 DFS Discover Financial Services
141 DISCA Discovery Communications Inc
142 DISCK Discovery Communications Inc
143 DG Dollar General Corp
144 DLTR Dollar Tree Inc
145 D Dominion Resources Inc
146 DOV Dover Corp
147 DOW Dow Chemical
148 DPS Dr Pepper Snapple Group
149 DUK Duke Energy Corp
150 DNB Dun & Bradstreet Corp
151 ETFC E*TRADE Financial Corp
152 DD E. I. du Pont de Nemours and Company
153 EMC EMC Corp
154 EOG EOG Resources
155 EQT EQT Corporation
156 EMN Eastman Chemical Co
157 ETN Eaton Corp plc
158 EBAY eBay Inc.
159 ECL Ecolab Inc
160 EIX Edison Intl
161 EW Edwards Lifesciences Corp
162 EA Electronic Arts
163 EMR Emerson Electric Co
164 ENDP Endo International
165 ESV Ensco PLC - CL A
166 ETR Entergy Corp
167 EFX Equifax Inc
168 EQIX Equinix
169 EQR Equity Residential
170 ESS Essex Property Trust Inc.
171 EL Estee Lauder Cos.
172 EXC Exelon Corp
173 EXPE Expedia
174 EXPD Expeditors Intl of WA Inc
175 ESRX Express Scripts Holding Co.
176 XOM Exxon Mobil Corp
177 FFIV F5 Networks Inc
178 FLIR FLIR Systems Inc
179 FMC FMC Corp
180 FTI FMC Technologies Inc
181 FB Facebook
182 FAST Fastenal Co
183 FDX FedEx Corp
184 FIS Fidelity National Information
185 FITB Fifth Third Bancorp (OH)
186 FHN First Horizon National Corp
187 FSLR First Solar Inc
188 FE FirstEnergy Corp
189 FISV Fiserv Inc
190 FLS Flowserve Corp
191 FLR Fluor Corp
192 F Ford Motor Co
193 FOSL Fossil Inc
194 BEN Franklin Resources Inc
195 FCX Freeport McMoRan Copper & Gold
196 FTR Frontier Communications Corp
197 GME GameStop Corp A
198 GCI Gannett Co Inc
199 GPS Gap Inc
200 GRMN Garmin Ltd
201 GD General Dynamics
202 GE General Electric Co
203 GGP General Growth Properties Inc.
204 GIS General Mills Inc
205 GM General Motors
206 GPC Genuine Parts Co
207 GNW Genworth Financial Inc
208 GILD Gilead Sciences Inc
209 GS Goldman Sachs Group Inc
210 GT Goodyear Tire & Rubber Co
211 GOOG Google Inc
212 GOOGL Google Inc
213 GWW Grainger W.W. Inc
214 HCA HCA Holdings
215 HCP HCP Inc
216 HAL Halliburton Co
217 HBI Hanesbrands
218 HOG Harley-Davidson Inc
219 HAR Harman Intl Industries Inc
220 HRS Harris Corp
221 HIG Hartford Finl Services Group
222 HAS Hasbro Inc
223 HCN Health Care REIT Inc
224 HP Helmerich & Payne Inc
225 HSIC Henry Schein
226 HSY Hershey Foods Corp
227 HES Hess Corp
228 HPQ Hewlett-Packard Co
229 HD Home Depot Inc
230 HON Honeywell Intl Inc
231 HRL Hormel Foods Corp
232 DHI Horton D.R. Inc
233 HST Host Hotels & Resorts Inc
234 HCBK Hudson City Bancorp
235 HUM Humana Inc
236 HBAN Huntington Bancshares (OH)
237 ITW Illinois Tool Works Inc
238 IR Ingersoll-Rand Plc
239 INTC Intel Corp
240 ICE IntercontinentalExchange
241 IPG Interpublic Group Cos
242 IBM Intl Business Machines Corp
243 IFF Intl Flavors & Fragrances
244 IP Intl Paper Co
245 INTU Intuit Inc
246 ISRG Intuitive Surgical Inc
247 IVZ Invesco Ltd
248 IRM Iron Mountain Inc
249 JBHT JB Hunt Transport Services Inc.
250 JPM JP Morgan Chase & Co
251 JEC Jacobs Engineering Group Inc
252 JNJ Johnson & Johnson
253 JCI Johnson Controls Inc
254 JOY Joy Global Inc
255 JNPR Juniper Networks Inc
256 KLAC KLA-Tencor Corporation
257 K Kellogg Co
258 GMCR Keurig Green Mountain
259 KEY KeyCorp
260 KMB Kimberly-Clark
261 KIM Kimco Realty Corp
262 KMI Kinder Morgan Inc
263 KSS Kohl's Corp
264 KRFT Kraft Foods Group Inc.
265 KR Kroger Co
266 LLL L-3 Communications Holdings
267 LH Lab Corp of America Hldgs
268 LRCX Lam Research Corp
269 LM Legg Mason Inc
270 LEG Leggett & Platt
271 LEN Lennar Corp
272 LUK Leucadia National Corp (NY)
273 LVLT Level 3 Communications
274 LLY Lilly Eli & Co
275 LB L Brands Inc
276 LNC Lincoln National Corp
277 LLTC Linear Technology Corp
278 LMT Lockheed Martin
279 L Loews Corp
280 LOW Lowe's Cos Inc
281 LYB LyondellBasell Industries N.V.
282 MTB M&T Bank Corp
283 MAC Macerich
284 M Macy's Inc
285 MNK Mallinckrodt Plc.
286 MRO Marathon Oil Corp
287 MPC Marathon Petroleum Corp.
288 MAR Marriott Intl A
289 MMC Marsh & McLennan Companies
290 MLM Martin Marietta Materials
291 MAS Masco Corp
292 MA Mastercard Inc A
293 MAT Mattel Inc
294 MKC McCormick & Co
295 MCD McDonald's Corp
296 MHFI McGraw Hill Financial, Inc.
297 MCK McKesson Corp
298 MJN Mead Johnson Nutrition Co
299 MDT Medtronic Inc
300 MRK Merck & Co Inc
301 MET Metlife Inc
302 KORS Michael Kors
303 MCHP Microchip Technology Inc
304 MU Micron Technology Inc
305 MSFT Microsoft Corp
306 MHK Mohawk Industries
307 TAP Molson Coors Brewing Co B
308 MDLZ Mondelez International Inc
309 MON Monsanto Co.
310 MNST Monster Beverage Corp
311 MCO Moody's Corp
312 MS Morgan Stanley
313 MOS Mosaic Co
314 MSI Motorola Solutions Inc
315 MUR Murphy Oil Corp
316 MYL Mylan Inc.
317 NKE NIKE Inc B
318 NRG NRG Energy
319 NDAQ Nasdaq OMX Group/The
320 NOV National Oilwell Varco Inc
321 NAVI Navient
322 NTAP NetApp Inc
323 NFLX NetFlix Inc
324 NWL Newell Rubbermaid Inc
325 NFX Newfield Exploration Co
326 NEM Newmont Mining Corp
327 NWSA News Corporation
328 NEE NextEra Energy Inc
329 NLSN Nielsen Holdings
330 NI Nisource Inc
331 NBL Noble Energy Inc
332 JWN Nordstrom Inc
333 NSC Norfolk Southern Corp
334 NU Northeast Utilities
335 NTRS Northern Trust Corp (IL)
336 NOC Northrop Grumman Corp
337 NUE Nucor Corp
338 NVDA Nvidia Corp
339 ORLY O'Reilly Automotive
340 OKE ONEOK Inc
341 OXY Occidental Petroleum
342 OMC Omnicom Group
343 ORCL Oracle Corp
344 OI Owens-Illinois Inc
345 PCAR PACCAR Inc
346 PCG PG&E Corporation
347 PNC PNC Finl Services Group
348 PPG PPG Industries Inc
349 PPL PPL Corp
350 PH Parker-Hannifin Corp
351 PDCO Patterson Cos Inc
352 PAYX Paychex Inc
353 PYPL Paypal
354 PNR Pentair Ltd.
355 PBCT People's United Financial Inc
356 POM Pepco Holdings Inc
357 PEP PepsiCo Inc
358 PKI PerkinElmer Inc
359 PRGO Perrigo Co
360 PFE Pfizer Inc
361 PM Philip Morris International
362 PSX Phillips 66
363 PNW Pinnacle West Capital (AZ)
364 PXD Pioneer Natural Resources
365 PBI Pitney Bowes Inc
366 PCL Plum Creek Timber Co
367 PX Praxair Inc
368 PCP Precision Castparts Corp
369 PCLN Priceline.com Inc
370 PFG Principal Financial Group
371 PLD ProLogis Inc
372 PG Procter & Gamble
373 PGR Progressive Corp
374 PRU Prudential Financial Inc
375 PEG Public Service Enterprise Grp
376 PSA Public Storage
377 PHM Pulte Group Inc
378 PVH PVH Corp
379 QRVO Qorvo
380 QCOM QUALCOMM Inc
381 PWR Quanta Services Inc
382 DGX Quest Diagnostics
383 KSU Railroad Kansas City Southern
384 RL Ralph Lauren Corp
385 RRC Range Resources Corp
386 RTN Raytheon Co
387 O Realty Income Corporation
388 RHT Red Hat Inc
389 REGN Regeneron
390 RF Regions Financial Corp
391 RSG Republic Services Inc
392 RAI Reynolds American Inc
393 RHI Robert Half Intl Inc
394 ROK Rockwell Automation Inc
395 COL Rockwell Collins
396 ROP Roper Industries Inc
397 ROST Ross Stores Inc
398 RCL Royal Carribean Cruises Ltd.
399 R Ryder System Inc
400 SCG SCANA Corp
401 SLG SL Green Realty
402 CRM Salesforce.com
403 SNDK SanDisk Corp
404 SLB Schlumberger Ltd
405 SCHW Schwab Charles Corp
406 SNI Scripps Networks Interactive
407 STX Seagate Technology
408 SEE Sealed Air Corp
409 SRE Sempra Energy
410 SHW Sherwin-Williams Co
411 SIAL Sigma-Aldrich Corp
412 SIG Signet Jewelers
413 SPG Simon Property Group
414 SWKS Skyworks Solutions Inc.
415 SJM Smucker J.M. Co
416 SNA Snap On Inc
417 SO Southern Co
418 LUV Southwest Airlines Co
419 SWN Southwestern Energy Co
420 SE Spectra Energy Corp
421 STJ St Jude Medical Inc
422 SWK Stanley Black & Decker
423 SPLS Staples Inc
424 SBUX Starbucks Corp
425 HOT Starwood Hotel & Resort World
426 STT State Street Corp
427 SRCL Stericycle Inc
428 SYK Stryker Corp
429 STI SunTrust Banks Inc (GA)
430 SYMC Symantec Corp
431 SYY Sysco Corp
432 TROW T Rowe Price Group Inc
433 TEL TE Connectivity Ltd.
434 TE TECO Energy Inc
435 TJX TJX Cos Inc
436 TGT Target Corp
437 THC Tenet Healthcare
438 TDC Teradata Corp
439 TSO Tesoro Corp
440 TXN Texas Instruments Inc
441 TXT Textron Inc
442 ADT The ADT Corp.
443 BK The Bank of New York Mellon Corp
444 WMB The Williams Companies Inc
445 TMO Thermo Fisher Scientific
446 TIF Tiffany & Co
447 TWC Time Warner Cable Inc
448 TWX Time Warner Inc
449 TMK Torchmark Corp
450 TSS Total System Services Inc
451 TSCO Tractor Supply Company
452 RIG Transocean
453 TRV Travelers Cos Inc
454 TRIP TripAdvisor Inc.
455 TYC Tyco Intl
456 TSN Tyson Foods Inc A
457 USB US Bancorp
458 UA Under Armour
459 UNP Union Pacific Corp
460 UAL United Continental Holdings
461 UPS United Parcel Service Inc B
462 URI United Rentals
463 UTX United Technologies Corp
464 UNH Unitedhealth Group Inc
465 UHS Universal Health Services
466 UNM Unum Group
467 URBN Urban Outfitters
468 VFC VF Corp
469 VLO Valero Energy Corp
470 VAR Varian Medical Systems Inc
471 VTR Ventas Inc
472 VRSN VeriSign Inc
473 VZ Verizon Communications Inc
474 VRTX Vertex Pharmaceuticals
475 VIAB Viacom Inc B
476 V Visa Inc
477 VNO Vornado Realty Trust
478 VMC Vulcan Materials Co
479 WMT Wal-Mart Stores
480 WAG Walgreen Co
481 DIS Walt Disney Co
482 WM Waste Management Inc
483 WAT Waters Corp
484 WFC Wells Fargo & Co
485 WDC Western Digital Corp
486 WU Western Union Co
487 WRK Westrock Co
488 WY Weyerhaeuser Co
489 WHR Whirlpool Corp
490 WFM Whole Foods Market Inc
491 WEC Wisconsin Energy Corp
492 WYN Wyndham Worldwide Corp
493 WYNN Wynn Resorts Ltd
494 XL XL Group Plc
495 XEL Xcel Energy Inc
496 XRX Xerox Corp
497 XLNX Xilinx Inc
498 XYL Xylem Inc
499 YHOO Yahoo Inc
500 YUM Yum! Brands Inc
501 ZMH Zimmer Holdings Inc
502 ZION Zions Bancorp (UT)
Available solutions