Sign up  |  Log in

Optimization using Excel

I’m exporting five years of historical price data, monthly, to calculate monthly returns from Yahoo Finance. I from there calculate the average return, annualized, and standard deviation, annualized. I also formulate a covariance matrix using the return data for 39 different stocks. My issue is the price data I extract doesn’t include dividends so my average returns are under stated. Is it okay to use this price data without dividends to calculate standard deviation, correlation and return? I’m assuming no. 

Don’t waste your valuable time with unreliable and bogus strategies for passing the CFA® exam. Schweser prepares you for success with adaptive tools, expert instruction, and proven study techniques personalized to your unique learning style.

Think I found my answer and I need to adjust for dividends

Use adjusted closing prices; that adjusts for dividends, splits, and reverse splits.

Simplify the complicated side; don't complify the simplicated side.

Financial Exam Help 123: The place to get help for the CFA® exams
http://financialexamhelp123.com/

Oh my goodness so that’s what adjusted closing price is. Thanks!! Glad to see you’re still on here s2000. You helped me a lot back in my study days. 

You’re quite welcome.

Simplify the complicated side; don't complify the simplicated side.

Financial Exam Help 123: The place to get help for the CFA® exams
http://financialexamhelp123.com/

So I checked XLU to see how the adjusted closing price varies from the normal closing price on the day of a dividend. How come on 9/20 a dividend was paid out yet the adjusted closing price is unchanged?

The adjustment is made historically; look at the closing price on 9/19 vs. the adjusted closing price: 64.11 vs. 63.62.  When the dividend was paid they changed the adjusted closing price for all days prior to the dividend.

Simplify the complicated side; don't complify the simplicated side.

Financial Exam Help 123: The place to get help for the CFA® exams
http://financialexamhelp123.com/

Understood!

Cool!

Simplify the complicated side; don't complify the simplicated side.

Financial Exam Help 123: The place to get help for the CFA® exams
http://financialexamhelp123.com/