FIN5550 Lynn University Stocks Beta and Return Project I need calculations about 20 stocks. Project 1 is connected to project 2 . It will be easier FIN 555
FIN5550 Lynn University Stocks Beta and Return Project I need calculations about 20 stocks. Project 1 is connected to project 2 . It will be easier 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 2019. 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? (Please note that the Yahoo Finance Help page no longer defines terms like beta. Yahoos Help
Page used to say: The Beta used is Beta of Equity. Beta is the monthly price change of a particular
company relative to the monthly price change of the S&P500. The time period for Beta is 3 years (36
months of returns) when available.)
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 closing 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, 2019:
Ticker No. Ticker Symbol Company
3M Co
1 MMM
2 ABT
Abbott Laboratories
3 ABBV
AbbVie Inc.
4 ABMD
Abiomed
5 ACN
Accenture plc
6 ATVI
Activision Blizzard
7 ADBE
Adobe Systems Inc
8 AAP
Advance Auto Parts Inc.
9 AMD
Advanced Micro Devices
10 AES
AES Corp
11 AMG
Affiliated Managers Group, Inc.
12 A
Agilent Technologies Inc
13 APD
Air Products & Chemicals Inc
14 AKAM
Akamai Technologies Inc
15 ALK
Alaska Air Group Inc.
16 ALB
Albemarle Corp.
17 AA
Alcoa Inc
18 ARE
Alexandria Real Estate Equities
19 ALXN
Alexion Pharmaceuticals Inc
20 ALGN
Align Technology Inc
21 ALLE
Allegion
22 AGN
Allergan PLC
23 ADS
Alliance Data Systems
24 LNT
Alliant Energy Corp.
25 ALL
Allstate Corp
26 MO
Altria Group Inc
27 GOOGL
Alphabet Class A
28 GOOG
Alphabet Class B
29 AMZN
Amazon.com Inc
30 AEE
Ameren Corp
31 AAL
American Airlines Group
32 AEP
American Electric Power
33 AXP
American Express Co
34 AIG
American Intl Group Inc
35 AMT
American Tower Corp A
36 AWK
American Water Works Co. Inc.
37 AMP
Ameriprise Financial Inc
38 ABC
AmerisourceBergen Corp
39 AME
Ametek
40 AMGN
Amgen Inc
41 APH
Amphenol Corp A
42 ADI
Analog Devices Inc
43 ANSS
ANSYS Inc
44 ANTM
Anthem Inc (formerly WellPoint Inc)
45 AON
Aon plc
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
AOS
APA
AIV
AAPL
AMAT
ADM
ANET
AJG
AIZ
ATO
T
ADSK
ADP
AZO
AVGO
AVB
AVY
BHGE
BLL
BAC
BK
BAX
BBT
BDX
BMS
BRK-B
BBY
BIIB
BLK
HRB
BA
BKNG
BWA
BXP
BSX
BMY
BR
BF-B
CHRW
COG
CDNS
CPB
COF
CAH
KMX
CCL
CAT
A.O. Smith
Apache Corp
Apartment Investment & Mgmt
Apple Inc.
Applied Materials Inc
Archer-Daniels-Midland Co
Arista Networks
Arthur J. Gallagher & Co.
Assurant Inc
Atmos Energy Corp
AT&T Inc
Autodesk Inc
Automatic Data Processing
AutoZone Inc
Avago Technologies
AvalonBay Communities Inc
Avery Dennison Corp
Baker Hughes, a GE Company
Ball Corp
Bank of America Corp
The Bank of New York Mellon Corp
Baxter International Inc
BB&T Corp
Becton Dickinson & Co
Bemis Co Inc.
Berkshire Hathaway B
Best Buy Co Inc
Biogen Idec Inc
BlackRock Inc
Block H & R Inc
Boeing Co
Booking Holdings Inc
Borgwarner Inc
Boston Properties Inc
Boston Scientific Corp
Bristol-Myers Squibb
Broadridge Financial Solutions
Brown-Forman Corp B
C.H. Robinson Worldwide Inc
Cabot Oil & Gas A
Cadence Design Systems
Campbell Soup Co
Capital One Financial
Cardinal Health Inc
Carmax Inc
Carnival Corp
Caterpillar Inc
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
CBOE
CBG
CBS
CE
CELG
CNC
CNP
CTL
CERN
CF
SCHW
CHTR
CVX
CMG
CB
CHD
CI
XEC
CINF
CTAS
CSCO
C
CFG
CTXS
CLX
CME
CMS
TPR
KO
CTSH
CL
CMCSA
CMA
CAG
CXO
COP
ED
STZ
CPRT
COO
GLW
CTVA
COST
COTY
CCI
CSX
CMI
CBOE Holdings
CBRE Group Inc.
CBS Corp B
Celanese Corp.
Celgene Corp
Centene Corporation
Centerpoint Energy Inc
CenturyLink Inc
Cerner Corp
CF Industries Holdings
Charles Schwab Corp
Charter Communications
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
Tapestry 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
Copart
The Cooper Companies
Corning Inc
Corteva
Costco Co.
Coty Inc
Crown Castle Intl Corp
CSX Corp
Cummins Inc
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
CVS
DHI
DHR
DRI
DVA
DE
DLPH
DAL
XRAY
DVN
FANG
DLR
DFS
DISCA
DISCK
DISH
DG
DLTR
D
DOV
DOW
DTE
DUK
DRE
DD
DXC
ETFC
EMN
ETN
EBAY
ECL
EIX
EW
EA
EMR
ETR
EOG
EFX
EQIX
EQR
ESS
EL
RE
EVRG
ES
EXC
EXPE
CVS Health
D.R. Horton Inc
Danaher Corp
Darden Restaurants Inc
DaVita Inc
Deere & Co
Delphi Automotive PLC
Delta Airlines
Dentsply Sirona
Devon Energy Corp
Diamondback Energy Inc.
Digital Realty Trust
Discover Financial Services
Discovery Communications – A
Discovery Communications – C
Dish Network
Dollar General Corp
Dollar Tree Inc
Dominion Resources Inc
Dover Corp
Dow Inc.
DTE Energy Co
Duke Energy Corp
Duke Realty Corp
DuPont de Neumours Inc.
DXC Technology
E*TRADE Financial Corp
Eastman Chemical Co
Eaton Corp plc
eBay Inc.
Ecolab Inc
Edison Intl
Edwards Lifesciences Corp
Electronic Arts
Emerson Electric Co
Entergy Corp
EOG Resources
Equifax Inc
Equinix
Equity Residential
Essex Property Trust Inc.
Estee Lauder Cos.
Everest Re Group
Evergy
Eversource Energy
Exelon Corp
Expedia Inc.
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
EXPD
EXR
XOM
FFIV
FB
FAST
FRT
FDX
FIS
FITB
FE
FRC
FISV
FLT
FLIR
FLS
FMC
FTI
F
FTNT
FTV
FBHS
BEN
FCX
GPS
GRMN
IT
GD
GE
GIS
GM
GPC
GILD
GPN
GS
GWW
HAL
HBI
HOG
HRS
HIG
HAS
HCA
HCP
HP
HSIC
HSY
Expeditors Intl
Extra Space Storage
Exxon Mobil Corp
F5 Networks Inc
Facebook
Fastenal Co
Federal Realty Investment Trust
FedEx Corporation
Fidelity National Information Srvcs.
Fifth Third Bancorp (OH)
FirstEnergy Corp
First Republic Bank
Fiserv Inc
Fleetcor Technologies
FLIR Systems Inc
Flowserve Corp
FMC Corp
FMC Technologies Inc
Ford Motor Co
Fortinet
Fortive Corp.
Fortune Brands Home & Security
Franklin Resources Inc
Freeport McMoRan Copper & Gold
Gap Inc
Garmin Ltd
Gartner Inc
General Dynamics
General Electric Co
General Mills Inc
General Motors
Genuine Parts Co
Gilead Sciences Inc
Global Payments
Goldman Sachs Group Inc
Grainger W.W. Inc
Halliburton Co
Hanesbrands Inc.
Harley-Davidson Inc
Harris Corp
Hartford Finl Services Group
Hasbro Inc
HCA Holdings
HCP Inc
Helmerich & Payne Inc
Henry Schein
Hershey Foods Corp
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
HES
HPE
HLT
HFC
HOLX
HD
HON
HRL
HST
HPQ
HUM
HBAN
HII
IEX
IDXX
INFO
ITW
ILMN
IR
INTC
ICE
IBM
IP
IPG
IFF
INTU
ISRG
IVZ
IPGP
IRM
JBHT
JKHY
JEC
JCI
JNJ
JPM
JNPR
KSU
K
KEY
KEYS
KMB
KIM
KMI
KLAC
KSS
KHC
Hess Corp
Hewlett-Packard Enterprise
Hilton Worldwide Holdings Inc
HollyFrontier
Hologic Inc.
Home Depot Inc
Honeywell Intl Inc
Hormel Foods Corp
Host Hotels & Resorts Inc
HP Inc.
Humana Inc
Huntington Bancshares (OH)
Huntington Ingalls Industries
IDEX Corporation
IDEXX Laboratories
IHS Markit Ltd
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
IPG Photonics
Iron Mountain Inc
J.B. Hunt Transport Services Inc.
Jack Henry & Associates Inc.
Jacobs Engineering Group Inc
Johnson Controls, Inc.
Johnson & Johnson
JP Morgan Chase & Co
Juniper Networks Inc
Kansas City Southern
Kellogg Co
KeyCorp
Keysight Technologies
Kimberly-Clark
Kimco Realty Corp
Kinder Morgan Inc
KLA-Tencor Corporation
Kohl’s Corp
Kraft Heinz Co.
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
KR
LB
LH
LRCX
LW
LEG
LDOS
LEN
LUK
LLY
LNC
LIN
LKQ
LMT
L
LOW
LYB
MTB
MAC
M
MRO
MPC
MKTX
MAR
MMC
MLM
MAS
MA
MXIM
MKC
MCD
MCK
MDT
MRK
MET
MTD
MGM
KORS
MCHP
MU
MSFT
MAA
MHK
TAP
MDLZ
MNST
MCO
Kroger Co
L Brands Inc
Lab Corp of America Hldgs
Lam Research Corp
Lamb Weston Holdings Inc.
Leggett & Platt
Leidos Holdings
Lennar Corp
Leucadia National Corp (NY)
Lilly (Eli) & Co
Lincoln National Corp
Linde PLC
LKQ Corporation
Lockheed Martin
Loews Corp
Lowe’s Cos Inc
LyondellBasell Industries N.V.
M&T Bank Corp
Macerich
Macy’s Inc
Marathon Oil Corp
Marathon Petroleum Corp.
MarketAxess
Marriott Intl A
Marsh & McLennan Companies
Martin Marietta Materials
Masco Corp
Mastercard Inc A
Maxim Integrated Products Inc.
McCormick & Co
McDonald’s Corp
McKesson Corp
Medtronic PLC
Merck & Co Inc
Metlife Inc
Mettler Toledo
MGM Resorts International
Michael Kors Holdings
Microchip Technology Inc
Micron Technology Inc
Microsoft Corp
Mid-America Apartments
Mohawk Industries
Molson Coors Brewing Co B
Mondelez International Inc
Monster Beverage Corp
Moody’s Corp
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
MS
MOS
MSI
MSCI
MYL
NDAQ
NOV
NKTR
NTAP
NFLX
NWL
NEM
NWSA
NWS
NEE
NLSN
NKE
NI
NBL
JWN
NSC
NTRS
NOC
NCLH
NRG
NUE
NVDA
ORLY
OXY
OMC
OKE
ORCL
PCAR
PKG
PH
PAYX
PYPL
PNR
PBCT
PEP
PKI
PRGO
PFE
PM
PSX
PNW
PXD
Morgan Stanley
Mosaic Co
Motorola Solutions Inc
MSCI
Mylan N.V.
Nasdaq OMX Group
National Oilwell Varco Inc
Nektar Therapeutics
NetApp Inc
NetFlix Inc
Newell Rubbermaid 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
Norwegian Cruise Line
NRG Energy
Nucor Corp
Nvidia Corp
O’Reilly Automotive
Occidental Petroleum
Omnicom Group
ONEOK Inc
Oracle Corp
PACCAR Inc
Packaging Corporation of America
Parker-Hannifin Corp
Paychex Inc
Paypal
Pentair Ltd.
People’s United Financial Inc
PepsiCo Inc
PerkinElmer Inc
Perrigo Co
Pfizer Inc
Philip Morris International
Phillips 66
Pinnacle West Capital (AZ)
Pioneer Natural Resources
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
PNC
RL
PPG
PPL
PFG
PG
PGR
PLD
PRU
PEG
PSA
PHM
PVH
QRVO
QCOM
PWR
DGX
IQV
RJF
RTN
O
REG
REGN
RF
RSG
RMD
RHI
ROK
ROL
ROP
ROST
RCL
SPGI
CRM
SBAC
SLB
STX
SEE
SRE
SHW
SPG
SWKS
SLG
SJM
SNA
SO
LUV
PNC Finl Services Group
Polo Ralph Lauren Corp
PPG Industries Inc
PPL Corp
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
IQVIA Holdings Inc
Raymond James Financial
Raytheon Co
Realty Income Corporation
Regency Centers Corporation
Regeneron
Regions Financial Corp
Republic Services Inc
ResMed
Robert Half Intl Inc
Rockwell Automation Inc
Rollins Inc.
Roper Industries Inc
Ross Stores Inc
Royal Carribean Cruises Ltd.
S&P Global Inc.
Salesforce.com
SBA Communications
Schlumberger Ltd
Seagate Technology
Sealed Air Corp
Sempra Energy
Sherwin-Williams Co
Simon Property Group
Skyworks Solutions Inc.
SL Green Realty
Smucker J.M. Co
Snap On Inc
Southern Co
Southwest Airlines Co
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
SE
SWK
SBUX
STT
SYK
STI
SIVB
SYMC
SYF
SNPS
SYY
TMUS
TROW
TTWO
TGT
TEL
TFX
TSO
TXN
TXT
TMO
TIF
TJX
TMK
TSS
TSCO
TDG
TRV
TRIP
FOXA
FOX
TWTR
TSN
USB
UDR
ULTA
UA
UNP
UAL
UNH
UPS
URI
UTX
UHS
UNM
VFC
VLO
Spectra Energy Corp
Stanley Black & Decker
Starbucks Corp
State Street Corp
Stryker Corp
SunTrust Banks Inc (GA)
SVB Financial
Symantec Corp
Synchrony Financial
Synopsys
Sysco Corp
T-Mobile US
T. Rowe Price Group Inc
Take-Two Interactive
Target Corp
TE Connectivity Ltd.
Teleflex Inc.
Tesoro Petroleum Co.
Texas Instruments Inc
Textron Inc
Thermo Fisher Scientific
Tiffany & Co
TJX Cos Inc
Torchmark Corp
Total System Services Inc
Tractor Supply Company
TransDigm Group
Travelers Cos Inc
TripAdvisor Inc.
Twenty-First Century Fox Class A
Twenty-First Century Fox Class B
Twitter
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
United Rentals
United Technologies Corp
Universal Health Services
Unum Group
V.F. Corp
Valero Energy Corp
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
VAR
VTR
VRSN
VRSK
VZ
VRTX
VIAB
V
VNO
VMC
WAB
WMT
WBA
DIS
WM
WAT
WCG
WFC
WELL
WDC
WU
WY
WHR
WMB
WLTW
WEC
WYNN
XEL
XRX
XLNX
XYL
YUM
ZBH
ZION
ZTS
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
Wabtec Corporation
Wal-Mart Stores
Walgreens Boots Alliance
Walt Disney Co
Waste Management Inc
Waters Corp
WellCare
Wells Fargo & Co
Welltower, Inc.
Western Digital Corp
Western Union Co
Weyerhaeuser Co
Whirlpool Corp
The Williams Companies Inc
Willis Towers Watson
Wisconsin Energy Corp
Wynn Resorts Ltd
Xcel Energy Inc
Xerox Corp
Xilinx Inc
Xylem Inc
Yum! Brands Inc
Zimmer Biomet Holdings
Zions Bancorp (UT)
Zoetis
Purchase answer to see full
attachment
