I created an Excel file using QLink instead of the web query. Imported only daily close data for the past 200 days. Got a correlation of 0.82 between LTR and MO. Loews is primarily an insurance company, but they also own Lorillard Tobacco. Got a 0.95 between RJR and MO. Also, I got these numbers for correlation with the XBD index: LEH 0.27, MWD 0.89, MER 0.89, GS 0.66, BSC 0.56, AGE 0.74. Agree that LEH looks strangely low. -v
Perhaps there is a difference between the data from Yahoo Finance and Qlink? All the other numbers seem to be right. LEH is way too low. I think I got slightly higher number for GS, MWD, MER. That said, it is not intended to be perfect, just a quick tool for confirmation. It gets the job done and I personally won't spend any more time on it unless we can somehow get historical intraday data. In that case using hourly data instead of daily will get us a lot more precision. Or may be we can find a better formula to calculate correlation than the default function in Excel. Until then I don't think it is worth the time to further develop this tool, it is what it is. Strangely, neither MO nor RJR follows the TOB index . . .