FIN 5550 Investments

Project 1: Beta and Return.

This project is worth 10% of your final grade.

This project must be completed individually.

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 closing 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 closing 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. 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 August 2016. 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, detailing how, exactly, you calculated the results in your Table (i.e., replicate the detailed findings for your stocks). The two Excel worksheets should be in one file with two different labeled worksheet tabs. Alternatively, you may use 20 Excel sheets named with the ticker symbol if you wish to combine the date, ticker symbol, closing price, adjusted closing price, and calculations and/or regressions separately for each of your 20 companies.

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