Oddschecker

[Ian]

I'm getting some errors now and I think it's something to do with non-runners as the structure of the source code isn't the same as for a runner. There probably weren't any in the races I tested this morning.

[Ian]

Code: Select all

        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), "');"">")
            If UBound(w) = UBound(head) Then
            For m = 1 To UBound(head)
                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) + 0, "0.00")
                    Else
                        arr(n, m + 1) = Format(Evaluate(arr(n, m + 1)) + 0, "0.00")
                    End If
                End If
            Next
            End If
        Next

Can you try this lindemann. I've also changed the code so that (at least for me) I get the proper decimal odds.

[lindemann]

Ian,

I pasted this code in above the line:

"ThisWorkbook.Sheets(sh).[ba4].Resize(55, 25) = """

Unfortunately, the query didn't seem to refresh either the odds or the runners list. Have I made a mistake?

[Ian]

That should have been ok lindeman. But in any case, I found another problem with the way the spread odds are delimited - it is not always the same as the fixed odds and causes the mismatch error. I think it's easy to fix but I have no way of testing it until tomorrow.

[lindemann]

OK Ian. You're doing a great job and we can pick it up again tomorrow.

[danjuma]

Just to add Ian, many thanks from me as well for trying to resolve this. I could not test any of the code myself as by the time I got back, the races had finished. Won't be able to do any testing until probably Thursday.

On a different note, I noticed something weird on this thread (or may be I am mistaken and it's in another thread, but I am sure it is this thread). Somebody, either you or mak asked Osknows for advice regarding an issue they were having trying to amend Osknows code to work with bestbetting site. I then added a reply to request a copy of the spreadsheet from the person after they have managed to get it to work with bestbetting site. All of sudden, all postings relating to this (person's original post, Osknows reply and my post) have disappeared from this thread!

[Ian]

Dan, I think that it was in the thread where you asked about using a lookup table to obtain the URL's not this one. I have done some work with bestbetting but I'm finding their site very "sticky", and quite often completely offline for a few minutes. This causes the vba macro not to respond and I end up having to use the task manager to kill Excel. In actual fact, it seems to be an easier site to use compared to oddschecker.

[danjuma]

Dan, I think that it was in the thread where you asked about using a lookup table to obtain the URL's not this one. I have done some work with bestbetting but I'm finding their site very "sticky", and quite often completely offline for a few minutes. This causes the vba macro not to respond and I end up having to use the task manager to kill Excel. In actual fact, it seems to be an easier site to use compared to oddschecker.

Oops! My apologies

[osknows]

Oh dear, it looks like oddschecker are onto us

I haven't been around for over a week so will have a look at this when I get home tonight or tomorrow morning. I've had a very quick look at their site and it looks like they are just sending the required data based on settings rather than exposing all data as previously.

Os

[osknows]

Didn't get much done today as this was much more fun! Can someone give this a test and let me know if there are any problems?

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

OC used to have all odds available for every setting in a single call to each link when navigating to their site, it's now been tidied up to only send the data requested in the settings. The odds are now calculated using Javascript based on the user settings with the default being traditional odds (eg 11/4) and commision applied. I've had to use a slightly different approach whereby Internet Explorer is opened hidden within VBA and the Javascript called to set the base settings to 'decimal' and 'no commision'. The correct odds are then extracted.

It's actually quite a slow site to load (approx 6 secs) so it's not worth reducing the link to the site below 60 seconds otherwise it will have a big hit on excel/BA performance

Os

[danjuma]

Hi Osknows,

And thanks for your efforts and time on this. Just tested the spreadsheet, took me a while to to realise I needed Internet Explorer opened for the sheet to work. Unfortunately, performance wise, the sheet seems to freeze a lot. When you select the first market, it updates the odds from OC, every minute or so (which I suppose is the default setting). However, or some reason, it seems to affect the updating of odds from BA as well, a lot of the time, for quite a few seconds, the odds from BA stop updating. One major problem so far is that when you select a new market on BA, while linked to the spreadsheet, BA freezes at the "Market Loading" stage, and you have to close BA completely and start over again.

Cheers
Dan

[osknows]

Hi Dan,

I've tested again and all is working fine this end. You shouldn't need to open IE? What is your default browser?

Is anyone else having probs?

[danjuma]

Hi Os,

Default browser is firefox.

When you have the sheet linked to BA, did you try to select another race in BA, and if so, did the market load ok in BA (ie, BA did not freeze at the "Market loading" stage). I am using BA version 1.1.0.66x18, which worked fine with the former spreadsheet.

Cheers
Dan

[lindemann]

Os/Dan,

Thanks for your renewed efforts Os. My first try with this sheet resulted in a freeze similar to Dan's description. My second attempt was a complete success using the Courses page, Ian's formula and all. I moved manually from one market to the next and everything seemed to be fine.

I can't explain what the difference was and I'll continue to use it tomorrow but so far so good.

[danjuma]

Lucky you lindemann,

It's still not working properly my end. I have downloaded the spreadsheet again and have just been trying it, but doing exactly the same thing as earlier mentioned. It won't update unless Internet Explorer is opened (which is no big deal). It takes several attempts of linking, unlinking and relinking sheet with BA before it starts to work at all . Then often, it updates once (the first time), and just seems to freeze (both BA odds and OC odds not updating on sheet). When I select another market on BA, BA freezes at the "Market Loading" stage. Another thing the sheet is doing as well is after it freezes and I unlink it from BA, and close BA if necessary and then reopen BA as well as the sheet, and then check the VBA editor because the sheet is not responding at all (even when lnked to BA), I find there is now a duplicate sheet in the VB project window (if you know what I mean). I then now have to use BA to kill all excel processes to clear this and reopen sheet.

Unfortunately, the sheet is not really working for me at all and I don't know why. I am using excel 2007

Cheers
Dan