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 - Sales and Trading Forum
| +23 | Q&A: US Power Trader (Prop/Spec) | 14 | 8h |
| +12 | Worth reneging on S&T offer? | 8 | 8h |
| +10 | Trading Forum Idea | 6 | 8h |
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 Next
Feel 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...
Dignissimos sequi quae sed accusamus. Adipisci consequuntur ut minima vel. Voluptatibus incidunt autem sequi a ipsum.
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...