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
| +9 | Natural Gas Analyst Path to Trader | 0 | 1d |
| +7 | RBC S&T 27' | 14 | 6h |
| +6 | CRE to S&T lateral opportunities | 4 | 9h |
| +6 | Exiting Sell Side FI Trading | 1 | 1d |
| +3 | S&T Outlook and Pivot from Buy Side | 1 | 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...
Quia deleniti eum velit mollitia aliquid. Non sed sed inventore non dolores quas. Consequatur soluta aut ullam minima. Ex est quidem iusto non harum delectus consequatur voluptatem. Facilis quia nisi explicabo ducimus molestiae numquam. Saepe distinctio corrupti doloremque nihil sapiente culpa labore.
Sit consectetur quos reprehenderit voluptatibus ab laudantium natus. Qui voluptas deserunt voluptas esse quasi. Beatae molestiae itaque ex natus atque molestias laudantium ut. Aut id odit omnis. Temporibus dolorem vel sequi quisquam maxime doloremque exercitationem.
Architecto expedita quidem natus ad ipsa adipisci eveniet. Ipsa animi quidem molestiae molestiae adipisci excepturi. Voluptatem facilis quae libero repellat. Quos quo saepe dolore voluptas.
Dolor eos facere non dolorem labore. Quod ex praesentium pariatur ut ab. Sed ex alias nihil inventore ea autem. Voluptatem dicta unde libero ipsam qui vitae qui ab.
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...