So essentially what i want to do...i get a correlation analysis of 20 different stocks / industries to each other..with the daily prices for the past 10 years.....whats the best software / or method of doing this?....can this be done on excel? or is reuters or another software better for this? ...also...whats a good database to get the stock prices in a nice downloadable excel file?....and if i am using excel...is there anyway to build a macro that automates the process of having to manually do a correlation for each two rows of data prices for the past 10 years?.....any feedback is greatly appreciated..thanks
Trending Content
Career Resources
In excel, put the data into columns. If you haven't already, install Data Analysis for Excel. Go to Tools -> Data Analysis. Select Correlation and go from there.
If you have a bloomberg terminal this is actually pretty easy to do and would not be that time consuming at all.
Alternatively you can pull data from yahoo finance.
I really want to learn VBA/Macros as I often find myself needing to do a lot of goofy stuff in excel but end up taking a bunch of uncessary steps that i wouldnt otherwise need to w/o excel. I suck with programming in general so its been something i have wanted to learn just never got around to it.
"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.
VBA is your friend. The code for 1-2 requires a bit more, well, code, but here's some pseudo-code for step 3:
1 - pull data sets from yahoo finance (search for ichart.finance.yahoo.com and csv for more info on this) using web queries 2 - dump it on a worksheet, one column/stock 3 - Run your correlation analysis, whatever it is (p-test, r^2, correl, etc.) through a nested for loop
Code-sort of for #3, assuming that each column in row 1 has a header:
Dim i, j as Integer Const statFormula as String = "=CORREL(range_x, range_y)" 'change me For i = 1 to ActiveSheet.Range("A1").End(xlToRight).Column - 1 For j = i + 1 to ActiveSheet.Range("A1").End(xlToRight).Column ActiveSheet.Cells(1,i).End(xlDown).Offset(2(j - i),0).Value = ActiveSheet.Cells(1,i).Value & "-" & ActiveSheet.Cells(1,j).Value ActiveSheet.Cells(1,i).End(xlDown).Offset(2(j - i) + 1, 0).Formula = Replace(Replace(statFormula, "range_x", Range(ActiveSheet.Cells(2,i), ActiveSheet.Cells(2,i).End(xlDown)).AddressLocal(False,False)), "range_y", Range(ActiveSheet.Cells(2,j), ActiveSheet.Cells(2,j).End(xlDown)).AddressLocal(False,False)) Next NextFeel free to PM me with questions on the code / VBA in general
oh man the things i could do if I knew how to code like that. Wanna help me backtest some trading strategies and develop the black box? lol.
"Oh the ladies ever tell you that you look like a fucking optical illusion" - Frank Slaughtery 25th Hour.
lol, that's pretty much what I've been doing for the last ~4 months at work...
Maxime ut porro reiciendis sit. Quisquam velit adipisci ipsam rem. Nemo ullam sapiente dolorem qui quis sapiente ducimus. Nihil aliquid qui nemo nihil quaerat.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...