Clearing previous market content on auto-bind

[banjocarter]

Hi

I have rolled out a new workbook and have been testing it for the first time today using the BA for BETDAQ version today. I was using Guardian to bind to multiple sheets. Initially I created a workbook with enough sheets to cover all the markets I needed (16). I allowed guardian to auto-bind them. I had the refresh set to 1 second. I have a rule which triggers once at 20 seconds before the off. I noticed that in one of the early races of the day a bet was submitted twice into the market. This worried me slightly but thankfully I was only using very small stakes to test this out.

Would having too many markets in Guardian cause this?

I then came across the restrict markets feature which is pretty neat and I figured I'd use so I can reduce the number of sheets I have in workbook down to say 3 and then have these get re-used as the time progresses. I have lots of VLOOKUPs referring off to some of my own custom sheets and want to keep this to a minimum.

I noted though when using restricted market mode that the data from the previous market wasn't cleared out when the next market gets auto-bound. I had selected auto-clear so when the market suspends the sheet is unlinked from guardian. The problem is I am left with old data when that sheet is re-bound to a new market. Basically it looked like any data which was set in columns O onwards gets retained.

Do you need to manually clear out the old data? Surely I'm not the first to come across this but I can't see an other topics mentioning this?

Ideally I want to set this up at the start of the day and then leave it unattended but the multi-bet issue is bothering me slightly.

If anyone has any hints on working around both of these issues I'd be grateful to hear them.

Thanks

Ben

[CallumPerry]

So you have 16 sheets, for argument sake say 4 events happen every 4 hours so your restrict refresh means only 4 markets are being refreshed (on a 1 second basis) for the entire hour. This means every 4 seconds the first market is being refreshed. Is this correct first of all?

Now in one of the first events you say a bet had gone into the market twice? I assume you are using some sort of trigger which says IF the countdown is less than 20 seconds THEN run my calculations? How many bets were being put into the market in that one event, a single bet or multiple? If it is a single bet, do you have ONE line i.e. Cell L9 which is firing the bet in or are L9, L11, L13 etc all looking at multiple runners?

Just trying to narrow down what went wrong. I've never had a problem like that before so intrigued to know why it happened to yourself. If you can provide some more info i.e answers to the questions above/your log somebody may be able to pinpoint what went wrong.

[banjocarter]

Thanks for the response Callum.

Yes your first assertion is true. The refresh rate of each market in seconds is going to be equal to the number of markets being actively processed.

So I am only trying to put bets on a single selection once if the time until the off is less than 20 seconds.. I have a formula in the odds column which puts a desired odds value in there or leaves it blank if the selection is of no interest. In the bet rules cells i then have the same formula.

=IFERROR(IF(AND(AVERAGE(G9,H9)<M9,$D$101<20),"BACK",""),"")

This takes the average of the current back and lay odds and BACKs if they are less than the desired odds in the odds column.

In D101 i have converted the countdown time to an absolute number of seconds.

Interestingly I found that this evaluates to true and places the word BACK in all the selections which are of no interest and contain blank in the odds column. I'll need to add an extra condition for this. I also found that the countdown timer reverts to a time in play once it turns in play so i'll need to factor this in too. (this was the first time I ever used this). Despite this, no bets actually get placed by Bet Angel for these selections as no odds are stated.

In the one case where a duplicate bet was placed it was on the same runner and the timestamp in BETDAQ was identical (well at least to the resolution as displayed in BETDAQ i.e. it occurred within the same second.

Also interested if you've ever seen this effect of BA not clearing out old log info from previously used sheets? I don't want to have to keep closing and re-opening the workbook - I want to be hands off with this.

Thanks

Ben

[CallumPerry]

Ok so the problem seems to be that SOMETHING is happening too quickly and it is firing two bets in instead of one. The first thing that springs to mind is that the trigger command may be too quick i.e. it flashes in the number you want to stake and Back/Lay multiple times before the system sends anything to BetFair but I'm sure this should only happen if you're refreshing quicker than 200m/s; I'm also not sure how excel deals with flashing figures in the Bet Rules area, more technical users may be able to shed some light.

What I would try is preventing any sort of flashing in cells L9 and M9 by using some sort of macro. For example, if you want to Back on selection 1 I would have your formula in another cell i.e. A1 = 1. Then a macro that looks for A1 = 1 to activate L9 = "BACK". This means BACK would appear once instead of potentially flashing in a volatile market. It would only appear next to the selection you desire instead of all selections as you said in your last message. Test to see if this resolves the issue. It probably is down to more than this but best to tick things off one step at a time with the basics.

With regards to your sheets not clearing, is it just your log information that's not clearing or some of your formulas? Your log info doesn't matter because new bets will overwrite previous info when the next lot of bets are made. Unless you are wanting to keep the log info for analysis after? I would use a recording sheet personally if this is the case.

Hope this has helped even a little bit.

[Dabbla]

Interestingly I found that this evaluates to true and places the word BACK in all the selections which are of no interest and contain blank in the odds column. I'll need to add an extra condition for this. I also found that the countdown timer reverts to a time in play once it turns in play so i'll need to factor this in too. (this was the first time I ever used this). Despite this, no bets actually get placed by Bet Angel for these selections as no odds are stated.

This sounds familiar, try and = the time to another cell, I vaguely remeber this being a fix for something.

I noted though when using restricted market mode that the data from the previous market wasn't cleared out when the next market gets auto-bound. I had selected auto-clear so when the market suspends the sheet is unlinked from guardian. The problem is I am left with old data when that sheet is re-bound to a new market. Basically it looked like any data which was set in columns O onwards gets retained.

Do you need to manually clear out the old data? Surely I'm not the first to come across this but I can't see an other topics mentioning this?

There shouldn’t be any problems with using multiple sheets, but if you don’t want lots of sheets, you can get away with just using one (BET ANGEL) file AND (BET ANGEL) sheet on all markets. It’s a bit of a pain, you will have to add them to the markets manually. It should get around old data problem.

Its been a while so both these bits of advice maybe useless.

[banjocarter]

Thanks guys I'll let it run again today and see if i can observe any peculiarities.

With regards to your sheets not clearing, is it just your log information that's not clearing or some of your formulas?

The problem is that say BetAngel uses the first sheet and ends up placing a back bet. The status cell for that selection in the sheet will say "PLACED". Once this event is over Guardian un-binds the sheet. Some time later in the day the same sheet gets auto-bound (because of the restrict refresh option being enabled). All the new runner information and race info is updated by guardian in the sheet but the status cell of is still set to "PLACED" from the last time this sheet was used. This will prevent any bets being submitted on the selection in that position if it meets the criteria.

[banjocarter]

Evening,

Have been trialling a workbook with fewer sheets. No instances of duplicate bets today.

I've attached a picture of the problem I've been describing about the failure to clear data out from previous markets. Its just been luck that two positions haven't conflicted today but I'm sure that bets wouldn't be placed if one of the previously used rows were to be used in a later market.

Any clues on getting around this would be helpful. Would writing a VBA macro to detect change in the sheet and then run a function when the market goes in play say?

Thanks

Ben

Not Clearing.png

[nigelk]

i use this routine to clear any Bet Angel sheets from a previous session everytime i open the workbook, just change the cleared range to suit.

Code: Select all

Private Sub Workbook_Open()

Application.EnableEvents = False
Application.Calculation = xlManual

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If InStr(ws.Name, "Bet Angel") > 0 Then

ws.Range("b1") = ""

ws.Range("b9:o68") = ""
End If

Next ws

Application.EnableEvents = True
Application.Calculation = xlAutomatic

End Sub

[banjocarter]

Thanks Nigel, problem is I want it to run whenever guardian uses that sheet again, not when the workbook opens. I want to open the workbook once in the morning and then leave it unattended after that.

I need to use restricted refresh as this greatly improves performance but it means that the same sheets will be reused throughout the day.

Any thoughts about what I could use to trigger the clearing function?

[CallumPerry]

Record a macro so:

IF whatever cell it is = "PLACED" Then Cell.ClearContents

See if you can figure it out for yourself, if you're still struggling tomorrow message in this thread again and I'll show you one I use. Just got home so having a bit of a rest now watching the England game with no crowd... I won't be awake much longer

[timevoyager]

I've had this problem in the past too, but for some strange and spooky reason the Status cells began clearing all by themselves eventually... which I believe they are supposed to do automatically with each new event loaded anyway.

If you haven't come across this thread, it may give you some useful ideas...

viewtopic.php?f=31&t=30&start=10

[banjocarter]

Hi TimeVoyager

Thanks for your reply. Were you using this with the Betfair version as opposed to the Betdaq version? I'm wondering if at some stage the BF product has had an update applied which wasn't applied to the BD version? Who knows?

Thanks for linking to the other topic, there are a few things in there to try out. The main issue is finding the correct trigger, I'm totally happy with what the resulting code should look like.

Thanks

Ben

[timevoyager]

Hi Ben,

I've only dabbled with the Betfair version.

[banjocarter]

For information i've now added the following code which will clear the appropriate cells. This is applied to each "Bet Angel" sheet in the workbook individually.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$G$1:$H$1" Then

        If (Range("G1").Value = "In-play") Then
            ' If-- other conditions as appropriate -- Then
        
                Application.EnableEvents = False
                Application.Calculation = xlCalculationManual
                Application.ScreenUpdating = False
    
                ' Clear out cells
                For i = 9 To 67 Step 2
                    Range("O" & i & ":AE" & i).ClearContents
                Next i
        
                For i = 10 To 68 Step 2
                    Range("L" & i & ":AE" & i).ClearContents
                Next i
                
                Application.ScreenUpdating = True
                Application.Calculation = xlCalculationAutomatic
                Application.EnableEvents = True
                
            ' End If -- other conditions as appropriate
            
        End If
       
    End If
    
End Sub

[foxwood]

For information i've now added the following code which will clear the appropriate cells. This is applied to each "Bet Angel" sheet in the workbook individually.
...

The Target may not explicitly be just those cells - other cells may be included in the range which could make your test fail and not tidy up.

Try this instead which should clear any previous values each time the market name changes (untested btw!) ...

Code: Select all

Dim B1_LastName

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Set B1_LastName = Intersect(Target, Worksheets("Bet Angel").Range("B1"))
    
    If Not B1_LastName Is Nothing Then
    
    	If B1_LastName <> Worksheets("Bet Angel").Range("B1").Value then
    	
    		' new market name found - tidy up from previous market

		' CLEARING CODE HERE
            
        End If
       
    End If
    
End Sub