Does anyone know where I can download/export historical market data (for free) and get it into Microsoft Excel? I’m having trouble finding this and feel dumb. There has to be a site offering this for free. Yahoo Answers was useless for me. Hope you guys can help!
finance.yahoo.com Look up symbol>click historical prices> define time period>click download to spreadsheet
yep… i’m dumb… just figured that out… Thanks!!
There are also add-ins usually connected to yahoo finance that can do that. Also excel 2007 has some functionality built-it. Google is your friend.
So I’ve come up with a strategy that I want to test against the S&P. I’m a level III candidate, and yet I have no idea really where to begin haha! Does anyone here have any experience back testing quant strategies? As of right now I have a pretty elementary formula (momentum indicator) to come up with a “buy”, “hold”, or “sell” decision on a weekly basis. What are my next steps in actually testing out this b!tch?? Does this make sense? and I hope its not a dumb question… Thanks!
There are actually free excel add-ins that you can use to get data. http://groups.yahoo.com/group/smf_addin/summary Anyway, it’s good that you want to test your strategy. As a note, I would highlight there is no holy grail to trading and that you need to develop a complete system. A good place to start is “Trade your way to financial freedom” by Van Tharp. I assume you have familiarity with excel. Let’s say you get SPY adjusted close returns, weekly going back to when it began. Create your indicator. Let’s say you have data on the S&P500 going back to 1950 or so and you get the 12 month return and then use a zscore that begins to roll starting when the SPY starts (so it incorporates new data and does not look ahead). Then create a function that is 1 if the zscore is bigger than say 1, -1 if less than -1 and 0 otherwise. In this type of situation, I would emphasize the Tharp book - the exits and risk management are as important as the entry. Or you can translate the z score to a portfolio allocation. Say you have 60%/40% stock-bond as your main allocation, then you use the z score to determine the stock allocation where .6*(1+scale*zscore) is the new allocation (and 1-stock=bond). You can adjust the scaling factor to change your returns. This isn’t exactly rocket science. Obviously, then you would compare your strategy versus a base case (like my 60/40 example). I normally look at the first four moments and Sharpe, but if should also look at your R, drawdowns, and all those other things if you really want to evaluate your trading.
Sub Download() Set occXMLHTTP = CreateObject(“Microsoft.XMLHTTP”) ti_dir = “C:\historical” ED = Day(Now) EM = Month(Now) EM = EM - 1 EY = Year(Now) PFROW = 1 Do Until Worksheets(“Portfolio”).Cells(PFROW, 1) = “” PFROW = PFROW + 1 Loop PFROW = PFROW - 1 For x = 1 To PFROW fn = Worksheets(“Portfolio”).Cells(x, 1) fname = Worksheets(“Portfolio”).Cells(x, 1) & “.txt” occXLS = ti_dir & fname occUrl = “http://ichart.finance.yahoo.com/table.csv?s=” & Worksheets(“Portfolio”).Cells(x, 1) & “&d=” & EM & “&e=” & ED & “&f=” & EY & “&g=d&a=2&b=7&c=2002” occLocalFile = ti_dir & fname occLocalFileName = Worksheets(“Portfolio”).Cells(x, 1) & “.txt” occXMLHTTP.Open “GET”, occUrl, False occXMLHTTP.send occArray = occXMLHTTP.ResponseBody occfile = 1 Open occLocalFile For Binary As #occfile Put #occfile, , occArray Close #occfile RemoveLine Next MsgBox “Completed.” End Sub Sub RemoveLine() Set oFSO = CreateObject(“Scripting.FileSystemObject”) fname_path = ti_dir & fname DeleteLine = 1 sTemp = “Date,Open,High,Low,Close,Volume,Adj Close” & vbCrLf On Error Resume Next If oFSO.FileExists(fname_path) Then Set oFSTR = oFSO.OpenTextFile(fname_path) lCtr = 1 Do While Not oFSTR.AtEndOfStream sLine = oFSTR.ReadLine If lCtr <> DeleteLine Then sTemp = sTemp & sLine & vbCrLf Else bLineFound = True End If lCtr = lCtr + 1 Loop oFSTR.Close Set oFSTR = oFSO.CreateTextFile(fname_path, True) oFSTR.Write sTemp End If oFSTR.Close Set oFSTR = Nothing oFSO.MoveFile fname_path, ti_dir & fn & “.csv” Remove_Column oFSO.DeleteFile ti_dir & fn & “.csv” Set oFSO = Nothing End Sub Sub Remove_Column() fn1 = fn & “.csv” fn2 = fn & “.xls” RV = ti_dir & fn & “.csv” Workbooks.Open RV Set rv1 = Workbooks(fn1).Sheets(fn) currRow = 1 Do currRow = currRow + 1 Loop While rv1.Cells(currRow, 1).Value <> “” currRow = currRow - 1 rv1.Columns(“G:G”).Select Selection.Delete Shift:=xlToLeft rv1.Range(“A1:F” & currRow & “”).Select Selection.Sort Key1:=rv1.Range(“A1:F” & currRow & “”), Order1:=xlAscending, Header:= _ xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.DisplayAlerts = False Workbooks(fn1).SaveAs Filename:=ti_dir & fn & “.xls”, FileFormat:=xlNormal Workbooks(fn2).Close True End Sub Copy and paste into VBA. Create a tab titled “Portfolio” and the symbols you want to look up go into column A. Also, you have to create a folder titled “historical” in your c-drive
CF_AHHHHHHHHH Wrote: ------------------------------------------------------- > Does anyone here have any experience back testing > quant strategies? > > What are my next steps in actually testing out > this b!tch?? Excel is not the best tool to use for testing those that can be done. I’ve used TradeStation and Wealth lab. I’m sure you can find other specialized software for that. When you decide on software and start testing beware of curve-fitting. Use insample-outofsample framework, limit the number of parameters to a few (1-3), perform sensitivity analysis around your selected parameters.
I’ve found Matlab to be relatively effective at this sort of work.
Hello Mister Walrus Wrote: ------------------------------------------------------- > I’ve found Matlab to be relatively effective at > this sort of work. Matlab is a great tool indeed.
What is Matlab? and is it free? Would I need any specific training on it? or could I just tinker with it and figure it out??
CF_AHHHHHHHHH Wrote: ------------------------------------------------------- > What is Matlab? and is it free? > > Would I need any specific training on it? or could > I just tinker with it and figure it out?? It’s a very common program used by people who do quantitative work, especially in academia. It’s very good with vector/matrix sorts of operations. It’s also pretty easy to learn, but you will need some basic programming knowledge.
CF_AHHHHHHHHH Wrote: ------------------------------------------------------- > What is Matlab? and is it free? > > Would I need any specific training on it? or could > I just tinker with it and figure it out?? It’s not free - actually it’s pretty expensive as far as software goes (though if you’re a student, you can get an educational license for about $100). It’s also not something i’d say you could just “tinker with and figure out”. If you don’t have any background in programming, it’ll be harder. Also, you need some familiarity with matrices since it’s all based on that. That said, it’s a great tool once you learn how to use it - very few things are better for finance math.