How to save odds in Excel

[iantrader]

Hi

I'd like to take a snapshot of the odds just before the off. I thought I'd be able to do it using the Seconds to off countdown in $X$1 but, of course, when the time goes outside the range the cell reverts to "".

Here's what I've been trying:

=IF(AND('Place Market'!$X$1<2,'Place Market'!$X$1>0),C2,"")

Is there a way to make the price 'stick'?

[Captain Sensible]

You're best off using VBA to take a snapshot as formulas will just continually calculate on each refresh and won't freeze the price. Plenty of vba examples on the forum to take snapshots of one or more prices

[iantrader]

You're best off using VBA to take a snapshot as formulas will just continually calculate on each refresh and won't freeze the price. Plenty of vba examples on the forum to take snapshots of one or more prices

Thanks, Captain.

Alas, very poor VBA skills although I'm currently working through a course. Would be grateful if you could point me to some examples.

There's no way of doing this purely in Excel?

[Captain Sensible]

Don't think there's any simple way of freezing it with just excel because the sheet continually calculates so any formulas would just track the current odds or display your else statement. VBA seems complicated to start but once you get the basics it gives you a lot more options especially for botting. Most of use use something like this as our basis to start

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
    If Target.Columns.Count <> 16 Then Exit Sub                    'If columns changed <> 16 then exit sub
    Application.EnableEvents = False  'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual 

'your trigger coding etc would go here





    If [A1].Value = MyMarket Then
    GoTo Xit
    Else
    MyMarket = [A1].Value
' code to clear new market variables or copy old data on market change can go here
    End If



    Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    End Sub



The MyMarket stuff is there so you can monitor when the market changes to a new market becomes more useful when you add routines

So simply adding the lines below is basically the same as an If formula, IF the market is not inplay and F2 is equal to nothing ie not suspended or closed then the Range AA5:AA50 would be equal to F5:F50 the back odds column, as soon as the market went in play or suspended then the prices would effectively be frozen.

If Range("E2").Value <> "In Play" AND Range("F2").Value = "" Then
Range("AA5:AA50").Value = Range("F5:F50").Value
End If

Code: Select all

 Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
    If Target.Columns.Count <> 16 Then Exit Sub                    'If columns changed <> 16 then exit sub
    Application.EnableEvents = False  'Turn off events so changes to cell don't retrigger event
    Application.Calculation = xlCalculationManual


If Range("E2").Value <> "In Play" And Range("F2").Value = "" Then
             Range("AA5:AA50").Value = Range("F5:F50").Value
              End If




    If [A1].Value = MyMarket Then
    GoTo Xit
    Else
    MyMarket = [A1].Value
' code to clear new market variables or set up things can go in here
    End If



Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

Obviously that's very simplified and you might want to copy data to other sheets to store the days racing etc rather than just dump it in column AA

Always a good idea to add a separate sub routine to a module to reset event from maybe a macro button as you're bound to come across times when you break your code mid routine and it gets stuck, a quick reset of the events will get you running again

Sub Reset()
Application.EnableEvents = True
End Sub

[vanbuuts]

I have a similar problem (sorry to hijack the thread).

When the event turns in play I want to place an X in cell F33 (to signify it is inplay). The problem is my macro keeps saying "out of stack space" and getting caught in a loop.

Any ideas on this

[Captain Sensible]

Maybe your macro is getting caught up in a loop, does it turn off automatic calcualtions and events?

Not sure why you need a macro as the In play status is already being sent to E2 why not just reference E2 in your sheet rather than use a macro, even just sticking a simple IF formula would do what you need IF(E2="In Play","X","")

[vanbuuts]

Hi CS, no my events are not turned off, nor my calculations.

Been banging my head against a wall for 2 days as seem to be going round in circles. Just when I think I have a working model, I go to test it on the BA interface and it just stars looping like crazy.

If I reference E2 in my sheet it will try and run a macro continually when E2 is diplayed

[vanbuuts]

When E2 is displayed as "In play" I should say

[Captain Sensible]

Not sure how you're firing the macro but maybe try enclosing it within the following to avoid looping, hard to say what's causing it to loop without seeing the code but generally wise to turn off the events and calculations until any coding has run


Application.EnableEvents = False 'Turn off events so changes to cell don't retrigger event
Application.Calculation = xlCalculationManual

Call your_E2_macro

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

[vanbuuts]

Thanks for the reply, I fear it could be yet another rebuild from scratch. These things are so frustrating, especially when you are so close.

[iantrader]

Thanks for that, Captain. I'll have a look through but, as yet, I don't understand much.

I've only got as far as Modules and ranges...

[vanbuuts]

Hi Captain sensible, just a question on the enable events. I notice that when I use your code below it works the first time I run it. But then it doesn;t work If i try again.

Is this due to the enable vents in effect "turning off" this macro once it has been run, and if so - is there a way to turn it back on?

[Captain Sensible]

The code at the bottom Application.EnableEvents = True, Application.Calculation = xlCalculationAutomatic should be turning the events etc back on after your code has run. I always have a separate macro in my sheets to reset things if it gets stuck or stops

Sub Reset()
Application.EnableEvents = True
'etc
End Sub


You should be trying to track down,debug etc why it's failing though and it's always good practice to have error trapping within your code too

[vanbuuts]

Hi CS, the problem for me is error 28 "Out of stack space" and refers to codes I have under the Private Sub Worksheet_Calculate() some of which are below to give an example. Can you think of a way around this happening?

Private Sub Worksheet_Calculate()


'Fix pre match odds


If Range("G33").Value = 1 Then
Me.Activate
Odds2

End If

'Fix pre match Formulas


If Range("G33").Value = 2 Then
Me.Activate
Formulas2
End If

[Captain Sensible]

I can only assume whatever is in your Odds2 or formulas2 is sending data back to the sheet and therefore causing the Worksheet_Calculate() to trigger again and so on in a loop. That's why you generally turn off events to stop things looping, why not try something like this that should turn off events until the code has finished

Code: Select all

        Private Sub Worksheet_Change(ByVal Target As Range)
        Static MyMarket As Variant
        If Target.Columns.Count <> 16 Then Exit Sub                    'If columns changed <> 16 then exit sub
        Application.EnableEvents = False  'Turn off events so changes to cell don't retrigger event
        Application.Calculation = xlCalculationManual

'Fix pre match odds


If Range("G33").Value = 1 Then
Me.Activate
Odds2

End If

'Fix pre match Formulas


If Range("G33").Value = 2 Then
Me.Activate
Formulas2
End If


        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        End Sub