# 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.

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

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

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.

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.

Understood!

Cool!