Benchmarking software

aba20 · 08-18-2012, 01:34 AM

Last year I set aside money for an actively traded account. I want to benchmark my results vs the S&P 500 and other indexes. Does anyone know any software or an excel spreadsheet that will do this?

I was doing it by hand but it gets very complicated when you don't hold stocks for the entire year and they pay out dividends which you reinvest in the stock.

dc_publius · 08-18-2012, 11:22 AM

Why can't you net starting balance vs current balance and then look up S&P performance since you started trading?

In other words, benchmarking your 1 day trade of 10% of your account against how S&P did that day is a mistake. Sitting money that is not traded can kill your results and it's much harder to have all your money in play all the time as opposed to keeping it all in S&P where 100% of your account is exposed to 100% of the upside/downside.

aba20 · 08-18-2012, 12:25 PM

I could though I would prefer more accurate results. I would like to see each stocks results as well as the portfolios results. I want to track my results vs the money I set aside, but that is what makes this complicated as the % cash I have changes.

Spurious · 08-18-2012, 03:45 PM

Just use Excel and download the prices.
Should be fairly easy, the data web query stuff automated in a macro should do the trick.

Spurious · 08-18-2012, 04:36 PM

Quote:

Function ReadSourcecode(jURL As String) As String
Dim oXMLHTTP As MSXML2.XMLHTTP: Set oXMLHTTP = New MSXML2.XMLHTTP
Dim strError As String: strError = ""
Dim strResponse As String: strResponse = ""
strURL = LCase(jURL)
On Error GoTo err_catch
With oXMLHTTP
.Open "GET", strURL, False
.send ""
If .Status <> 200 Then
strError = .statusText
GoTo err_resume
Else
strResponse = .responseText
End If
End With
err_resume:
On Error Resume Next
Set oXMLHTTP = Nothing
If Len(strError) > 0 Then
MsgBox strError
Else
ReadSourcecode = strResponse
End If
Exit Function
err_catch:
strError = Err.Description
Resume err_resume
End Function

Sub ReadYahooTicker()
Dim YahooTicker As String
Dim i As Long
i = 1
Do While ThisWorkbook.Sheets("Data").Range("A1").Offset(i, 0) <> ""
YahooTicker = ThisWorkbook.Sheets("Data").Range("B1").Offset(i, 0)
If YahooTicker <> "" Then
ThisWorkbook.Sheets("Data").Range("C1").Offset(i, 0) = GetResult(YahooTicker)
End If
i = i + 1
Loop
End Sub

Function GetResult(YahooTicker) As Variant
Dim jQText As String
Dim ValueToday
Dim jMatch As Variant
jQText = ReadSourcecode("http://finance.yahoo.com/q?s=^" & YahooTicker)
jQText = Replace(jQText, " ", "")
jQText = Replace(jQText, Chr(10), "")
jQText = Replace(jQText, Chr(13), "")

With CreateObject("VBScript.RegExp")
.Global = True
.ignorecase = True
.Pattern = "<span class=" & Chr(34) & "time_rtq_ticker" & Chr(34) & "><span id=.*?>(.*?)</span></span>"
If .test(jQText) Then
Set jMatch = .Execute(jQText)
ValueToday = jMatch(0).submatches(0)
End If
End With
GetResult = ValueToday
End Function

This code implemented in Excel (Alt + F11, insert module, copy + paste code) will download you the results for indices (I only tested S&P500 Total Return and the Excess Return Futures, it didnt work for ETFs and single stocks, was too lazy to implement).
You have to have the name of the underlying in Range A2:Ax, the yahoo ticker (e.g. PXTY in Range B2:Bx) and it will print the value in Range C2:Cx.

Then go to macros and run the macro (name: ReadYahooTicker).

As I said, only works for indices, was too lazy to do it for more.

Shoot if you got any questions.

Spurious · 08-18-2012, 05:26 PM

Quote:

Originally Posted by Spurious

This code implemented in Excel (Alt + F11, insert module, copy + paste code) will download you the results for indices (I only tested S&P500 Total Return and the Excess Return Futures, it didnt work for ETFs and single stocks, was too lazy to implement).
You have to have the name of the underlying in Range A2:Ax, the yahoo ticker (e.g. PXTY in Range B2:Bx) and it will print the value in Range C2:Cx.

Then go to macros and run the macro (name: ReadYahooTicker).

As I said, only works for indices, was too lazy to do it for more.

Shoot if you got any questions.

Oh yeah, in order for this to work, you also need to activate a reference: Microsoft XML 6.0 (Extra --> References, you'll find it in that list).