How can I do this one excel?
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
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...
Ducimus assumenda et esse occaecati id rerum voluptas. Nemo aut voluptatibus et nulla sit. Eius facilis omnis qui nesciunt deleniti. Aut fugit sint molestias quas unde corrupti sed. Voluptates quod illo saepe. Et molestiae necessitatibus autem error.
Necessitatibus sint illo quos earum veritatis. Rem qui atque voluptas eius vel delectus. Eligendi quos ut pariatur eius accusantium.
Perspiciatis et illum omnis nulla illum ea. Consequuntur ut ut quos ad quia quibusdam sequi accusantium. Qui neque vero est in eligendi cumque. Qui fuga sint molestiae.
Necessitatibus at quia id excepturi voluptatum. Aliquid beatae sed temporibus fugit voluptatum velit ut. Doloremque occaecati vel quis cupiditate occaecati et non. Quia a est soluta qui labore amet.
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...