Recording odds increase/decrease in x seconds

[MiniBlueDragon]

I'm toying with the idea of recording the % increase/decrease in odds over x number of seconds both before the off and in play for a specific runner. I currently use the Worksheet_Change method to track my Max Bank via this code:

'Check current Bank and if it's higher than existing Max Bank, update Max Bank
If Worksheets("Gruss").Range("I2").Value > Worksheets("Gruss").Range("U1").Value Then
Worksheets("Gruss").Range("U1").Value = Worksheets("Gruss").Range("I2").Value
End If

I'm thinking it may be possible to do something similar but for the odds. Any suggestions on how I can tweak the above code (or even if it'll work) please?

[MiniBlueDragon]

Just found this thread so will start using that as a basis and grab odds instead I reckon.

[MiniBlueDragon]

This thread:

http://gruss-software.co.uk/forum/viewtopic.php?t=6839

[MiniBlueDragon]

OK I'm stuck. The basic code in that example spreadsheet seems to make sense but for some reason it's not working with BA for Betdaq.

Code: Select all

Option Explicit

Dim totalMatched As Variant
Dim totalMatchedDiff(54, 0) As Double
Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Integer, marketChanged As Boolean
    If Target.Columns.Count = 16 Then
        If [A1] <> currentMarket Then
            currentMarket = [A1]
            marketChanged = True
        Else
            marketChanged = False
        End If
        For r = 5 To 54
            If marketChanged Then
                totalMatchedDiff(r - 5, 0) = 0
            Else
                totalMatchedDiff(r - 5, 0) = Cells(r, 16).Value - totalMatched(r - 4, 1)
            End If
        Next
        totalMatched = Range(Cells(5, 16), Cells(54, 16)).Value
        Application.EnableEvents = False
        Range(Cells(5, 26), Cells(54, 26)) = totalMatchedDiff
        Application.EnableEvents = True
    End If
End Sub

[MiniBlueDragon]

No ideas on this vba gurus? I'm completely stumped. :/

[alrodopial]

BA for Betdaq is a bit different I think, others that use Betdaq will help you tomorrow.

[osknows]

What happens when the code runs in Betdaq? It works okay here?

The variable Dim totalMatchedDiff(54, 0) As Double might throw an error if the type is not a double but apart from that it should work.

[kiint]

Is it the line:
totalMatchedDiff(r - 5, 0) = Cells(r, 16).Value - totalMatched(r - 4, 1)

First time through it will think the market has changed, hit this line and it won't have had an array assigned to it yet.

Other than that I don't know. What error are you getting? Bit difficult to identify the problem without knowing the symptoms.

[MiniBlueDragon]

There's no specific 'error' so that's why I haven't reported one; it just doesn't ever update the "Total matched increase" figures.

I'll run it this morning and report back if there's anything else.

[MiniBlueDragon]

Yep as above. I have the spreadsheet logging prices for the first race of the day and £188.78 just got matched on the third selection (from 0 matched) and the "Total matched increase" column just doesn't update.

[osknows]

Is it doing what you expect?

On the first refresh, as it's a new market then nothing is calculated and output is zero. However, it records the matched amounts ready for the next refresh.

On the second refresh, assuming no changes in matched amount, then it's comparing the same matched amount to the first refresh, therefore zero change.

Whenever there is a change it lasts for a single refresh then resets to zero.

[MiniBlueDragon]

If I comment out the "Option Explicit" at the beginning after having it running for a while I suddenly get a flood of backed-up information in the "Total amount matched" column but again the "Total matched increase" never changes from 0 at all.

I normally troubleshoot using debug.Print to output variables and see what's happening but even that isn't working with the spreadsheet. :/

Even breakpoints aren't being hit now.

[osknows]

Not sure what problems you're having. You may need to check the events are firing by using Application.EnableEvents = True


What is it your trying to measure exactly? The current code is calculating the difference in amount matched between each refresh. So if there is no change then it will display 0. If you need to measure the change over a longer period than a single refresh then this code won't do that.

[MiniBlueDragon]

For now all I want to do is get this speadsheet running and from there I'll be looking into customising it for odds movements.

I know what the spreadsheet is supposed to do and that it's the difference in matched between each refresh but it's never changing for any runner on any refresh at all.

The Application.EnableEvents = False isn't until the bottom of the code and only while it updates the sheet itself so I assume everything else in there before it's switched off, the sheet is updated and it's switched on again should be firing as normal.

I'm wondering if perhaps there's something in there which is Betfair only and that's why it's not working for Betdaq?

[osknows]

There's nothing wrong with code, itt works fine in both Betdaq and Betfair here.