Oddschecker

[osknows]

Here is an updated and simplified version of the oddschecker extract.

Essentially, copy and paste the URL directly from the site into cell BA1 and ensure BA2 is set to 'On'. The prices will update every minute but may be in a different order to what's in BA. Some people may also notice that the prices look different from the site, this is because the oddschecker site applies a commission to the price, turn this off on their site by unchecking 'Include commision settings' and you will see matching prices

For this to work you must add the following 2 libraries into the VBA editor, through TOOLS/REFERENCES
'1. Microsoft XML, v6.0
'2. Microsoft VBScript Regular Expressions 5.5

- use earlier version if v6.0 or 5.5 are not available.

Also, I've only tested with horse racing markets.

http://www.mediafire.com/?j9hsq49nubo9j7r

[Ian]

I'm getting "subscript out of range" on this line

arr(0, m + 1) = Split(head(m), "'")(0)

[osknows]

Hi there Ian, can you PM me the exact link you were using

thanks

[osknows]

link to updated version, http://www.mediafire.com/?20a8um9up2we59k

[danjuma]

link to updated version, http://www.mediafire.com/?20a8um9up2we59k

THANK YOU VERY MUCH SIR!!!

[sebaku]

There is a horse name from Oddschecker in column BA.
Could be possible to add also a column with saddle cloth number from OC?
It would solve the problem, if selections are displayed in different order.
Horse names are not always exact the same in Oddschecker and Betfair.

many thanks

[danjuma]

Oksnow,

Not sure what they've done to their site again, but for some reason, the odds are no longer being displayed. The list of the horse names get displayed, but the columns for the bookies odds are just blank!

[Ian]

They do appear to have changed their site because the string Os looks for to determine where the odds start is no longer there. Also, I think they may no longer have any code where a bookmaker doesn't have a price for a selection - I can't see where Os takes that into account in his existing code.

[danjuma]

They do appear to have changed their site because the string Os looks for to determine where the odds start is no longer there. Also, I think they may no longer have any code where a bookmaker doesn't have a price for a selection - I can't see where Os takes that into account in his existing code.

Ian,

I appreciate Os has already spent a lot of time and done a lot coming up with this spreadsheet., and he's probably busy right now to amend it so it starts working again. You seem to understand it and what might have been changed on the OC website that's caused the spreadsheet to stop working. Would you be able to amend the spreadsheet so it works again? Thanks

[Ian]

Change the code in Module 1 as follows

Code: Select all

        If UBound(v) > 1 Then
       
'        outputtext (htm)
        head = Split(v(0), "javascript:goBk('")
        ReDim arr(0 To UBound(v), 1 To UBound(head) + 1)
        For m = 1 To UBound(head)
        arr(0, m + 1) = Split(head(m), "'")(0)
        Next
       
        For k = 1 To UBound(v)
            DoEvents
            n = k ' CLng(Left(v(k), 1))
            arr(n, 1) = Split(Split(v(k), " name=")(1), """")(1)
            w = Split(v(k), "');"">")
            For m = 1 To UBound(w)
                arr(n, m + 1) = Split(w(m), "</td><td")(0)
                arr(n, m + 1) = Trim(Application.WorksheetFunction.Clean(arr(n, m + 1)))
                If arr(n, m + 1) Like "[0-9]*" Then 'Format as 0.00
                    If IsNumeric(arr(n, m + 1)) Then
                        arr(n, m + 1) = Format(arr(n, m + 1) + 1, "0.00")
                    Else
                        arr(n, m + 1) = Format(Evaluate(arr(n, m + 1)) + 1, "0.00")
                    End If
                End If
            Next
        Next

This works as long as there is no blank column for a bookmaker on oddschecker so it's probably ok for the current day's horse racing. Also I don't get the correct odds but I think that may be because I have the oddschecker odds set to decimal rather then traditional.

[lindemann]

Ian,

Many thanks for doing this, it works fine and the odds figures are just one point higher than the correct decimal odds. I assume this is just a change since the code was first written and it's easy enough to correct within my own spreadsheet formula.

[lindemann]

Spoke too soon I'm afraid. There are persistent errors like "type mismatch" and (most often) "subscript out of range". I assume these are caused by changes at the Oddschecker site.

[danjuma]

Yes Ian, thanks for the amendment. I have not tried it yet, but as lindemann has and has found errors with it, I shall wait for your next amendment to resolve the errors.

Thanks
Dan

[Ian]

I've been running this for the last couple of hours and I've not had any errors. Does it not work at all for you, lindemann ?

[lindemann]

Ian,

I started it before racing and all seemed fine when I first posted. About 1.55 ish the order of betting changed for the 2.00 at Windsor and this threw up an error and I've had persistent errors all afternoon despite closing and reopening the spreadsheet.

Given my earlier issues with Excel 2003/2007 however, it might be best if someone else tries it to see if it's OK.

The most frequent error I get by the way is subscript out of range at line
"arr(n, m + 1) = Split(w(m), "</td><td")(0)". I'm running Excel 2007 this afternoon.