Auto clearing status cells

Example spreadsheets and comments on example spreadsheets.
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

Hi grindhog this may well do exactly what I have been attempting to do with no VBA knowledge and not enough time! :) I will check it over later ... I just wanted to say thanks. :)
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi Firlandsfarm,
I hope its what you needed. I doubt if its an improvement on any of the other versions presented in this post and othe rposts but it was done for m own specific requirement and I would be delighted if it was of some use to you or others. It was only when i could read some vba script that I realised in fact their coding was beyond my limited capability and I could not understand it.
I reduced the time span in the sub to 2 seconds and would advise the same for others to avoid double bets.The delay in the market closing by betfair is a small problem but can be manually overcome by closing the excel "Bet Angel" in guardian and opening same in the new market. I was wondering if you were aware of any way that the market sheet can switch automatically as it does in automation with the automated market switch time of the markets area.
Regards
Grindhog
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

WHAT TYPE OF STATUS CLEARING
Sometimes when we go back to worksheets in BETANGEL it can be a muddle especially when we
are dealing with the clearing of status in BETANGEL sheets. My Plan was to have a Macro that automatically
clears the STATUS COLUMN to allow subsequent bets.
The idea was to clear the status at about 28 secs before race start (and have a feature to allow that time to be adjustable to any time one requires) , priming the sheet for whatever Back or Lay bet is employed later.
To ensure no unwanted double bets I made the bets to trigger at a time later than the STATUS CLEARING time used.
Here is what happens when the bets activate at the same time as the clearing of the STATUS indicator-You get double bets - hence the need for time separation.

double_Bets_again.png

DOUBLE BETS AGAIN BELOW- THREE TIMES OVER a 1 HOUR PERIOD
double_Bets_again.png
Another aim was to ensure that no Column headers became deleted (which happened in my previous feeble effort) and that Bet Rule and Command rule option info was retained at Cols L to AE. I am pleased to say that the
macro appears to work well over 2 day testing and it completed both the UK/Irish and US markets without a hitch. The macro was designed around the macros created previously on this forum but bits added also to avoid error messages.
I realise now that to make Macros that work you to have to understand EXACTLY how the worksheet works
in BetAngel Excel otherwise you will perceive macro problems that essentially of your own making. I am laying out the summary of my research in this regard which might help anybody who might struggle with STATUS CLEARING

SETTING UP THE SHEET WITH THE MACRO
The Macro comes in 2 parts the latter triggered by the first. They both are in place in the attached Sheet.
They operate automatically to clear the STATUS indicator 28 secs PRE-START-TIME to free the sheet for a bet
at whatever time condition is employed in the bet formula. I used the Race START-TIME for the bet to trigger
-The Private Sub - Which calls up the Reset_Bet sub at whatever time you designate and can be changed by changing the TIMESERIAL VALUES in the Private Sub for countdown visible at F4 .
If (not "In-Play") is another condition imposed as you may not want to automatically bet in-play. This is a precautionary measure as the timings setup means that should not happen anyway.
Both conditions can be simply changed also by going to developer/Visual Basic and editing.

The Private Sub Macro is accessible to edit in the Sheet 1(Bet Angel) Folder in Developer/Visual Basic/
by rightclicking and "view code" and editing/saving directly if you want to change the timings or change not in_play
which shows as "<>In-play" to in Play which can be revised/Adjusted TO SHOW as "=In-Play".
WARNING: Usually the Module 1 code is automatically visible on developer/Visual Basic entry so that it is ALWAYS necessary to highlight the Sheet 1(Bet Angel) Folder- rightclick/view code in order to access that folders code.

[




- The Reset_Bet Sub- Deletes the Status Data from Col O9 down
The Reset_Bet Sub is accessible to edit either in Macros or again in the Module 1 Folder in Developer/Visual Basic/.
by highlighting Module1 and rightclicking/view code and editing directly but I cannot see any likely changes.
The name of the sheet in the macro must always coincide with the sheetname "BetAngel_1.xlsm" or if you change
the sheets name you must do the same also in the Macro "Reset_Bet" in the editing manner described above.



KNOWING HOW SHEETS OPERATE IN BETANGEL IS ESSENTIAL TO KNOWING IF/WHY YOU HAVE A PROBLEM
A good understanding about how/when/why sheets load in betangel is required if you encounter something
unusual happening so that you avoid the temptation to change things when no change is needed or you think
the Macros are wrong when in fact its something else. Yes I had this problem myself!!!
A knowledge of the expected sequence of events is helpful so that you know where you are at any point.
and I have set out that below as a guide. For proper use of excel it is critical that the sheets bind from race to race
as the races finish.
AUTO BIND/AUTO CLEAR BINDING of BetAngel sheets-Will only bind if
(1) AutoBind/ClearBind are TICKED in Guardian in the excel section.
(2) Your worksheet is connected (preferably from the same folder for good housekeeping as the betangel.exe file for the currently running BA as you may wish to use different Macros with different timings for different strategies)
(3) The refresh rate is set to be currently active (recommend 1/2 hour before/After race start should guarantee binding as some races arise 30 mins apart (set at 1800 sec before and 1800 sec after) or the refresh rate restriction unticked which makes it always active.
(4) The previous race has finished,
(5) "in-Play" drops from the Guardian screen and the worksheet simultaneously (usually within Mins of race end)
then the Bet angel sheet, provided (1) to (4) are in place, will "bind to/make active" the next market and the new racecard then populates the worksheet. The previous report of bets made (n Col L) is also overwritten together with the other details of the bets matched,amounts etc.(in Cols P to AE)
STATUS "PLACED" BET BLOCKER REMAINS
Critically the only thing that still remains from the previous race is the STATUS which usually states "Placed". No Bets can be made for the new race until the old Status indicator is either deleted by highlight/delete in excel proper or automatically from a macro or manually by Macro using a designated shortcut key.( Ctrl v is used in the sheet)
BTW when assigning a new macro key in Developer/Macro/options ensure the sheet is saved to retain the Shortcut Key.







The BINDING HAS MOVED TO A NEW RACE AND THE GLOBAL STATUS "PLACED REMAINS"







The "STATUS" CARRY of course is a security measure for users to ensure bets are not fired unintentionally.
However be careful as it may occur that the previous race has only say 8 runners to say 10 in the current
race. The carried forward STATUS blockers will only be 8 in number potentially allowing the remaining 2 runners
to have their bets placed immediately even up to 30 mins before the race depending on your timings for the macros
and the BACK or LAY bets.. The market may not be properly formed at that time (in US Races particularly) and therefore early bets may be a disaster. That is another reason why bets have to be delayed in the Bet Formula.












THE STATUS "Placed" is cleared at the designated time 28 secs from the OFF ready for the bet to trigger at OFF.
HOW TO DELAY YOUR ACTUAL BET FOR A TIME AFTER THE "STATUS INDICATOR" IS REMOVED
When the "PLACED" from the previous race is deleted you will know you are in the Bets allowed phase
because the bet report info is no longer in Col L as shown below.
Col L should until normal circumstances be blank without any report of bets actually made.
However occasionally "Back" or " Lay" is visible when it is no longer possible to process a bet as the designated time has passed beyond the bet formula timing. This rare event is explained later where a manual intervention arises.



THE BETS ARE PLACED AT THE "OFF" TIME












In my opinion the timing separation part of the Macro and later the Bet timing itself are very important features as they allows 1 only bet to be placed at a time after the "Placed" blocker has been removed where multiple bets might otherwise occur if both the STATUS indicator removal and the bet placement were to happen at the same time.

HOW TO AVOID BETS BEING PLACED AT THE STATUS REMOVAL TIME.
Within the bet formula itself you could use $C$3>$F$3 to represent the current time is Greater (later) than the Start time ensuring bets are only placed at start time which in this setup is about 28 seconds after the "STATUS" indicator.
Also using AB10<>1 in the bet formula ensures no further bet when one bet is already placed although it
should be unnecessary as a new STATUS " "Placed" indicator should do the trick. Lets call it a security backup.
USE FORMULAS IN YOUR BACK /LAY BET and the PRICE and STAKE to avoid being part of any DATA clearout
Altogether I used =IF(AND(AND(H9>0,AB10<>1,$C$3>$F$3)),"LAY","") to execute the LAY BET.
For the price/odds I used =IF(i9=0,"",i9) and for stake I used =IF(H9=0,"",$AE$1/H9) in AE1 +50+50 was entered.
Note that my sample betting by Laying the Field by Book % is not a viable strategy except maybe when some floor
and ceiling limits are applied which is not done here and should be used only in practice mode.

BINDING IS ESSENTIALLY BETFAIR GENERATED
Binding is dependent time-wise on when exactly an event ceases and is closed by betfair and is not exact time predictible for that reason. You can only therefore generate the Status remover relative to the current race time.
The changing of the market is generated by Betfair and is dictated largely by when an event finishes and presumably related to market settlement time which is an unknown quantity.
MARKET SWITCH TIME
Is exclusively for Automation and the timing of the switch to a one click screen window.
It does not in any way relate to the new market generation in excel which as explained is sheet re-binding dependent.
FURTHER NOTE ABOUT REFRESH RATE TIMING
If you untick Refresh Rate then it cannot cause a problem as regards status clearing.
However different refresh rates are required for different strategies. Typically a pre-race trader will have the refresh rates set in a short span so that they will have that market continually refreshing at a fast rate so that the most
up to date "prices" and "available to bet" amounts and "volume" can be seen. For traders the refresh rate
operating near to a race will cause the market only to bind near the race time.
If on the other hand Pre-Race bettors would likely have no restriction on the refresh rate by having it unticked
or have the span for restriction at 1800 secs before/1800 secs after the race.
POSSIBLE MANUAL INTERVENTION TO SORT A NATURALLY OCCURING "LATE RACE" PROBLEM
Sometimes it occurs that a race is so late that its finish/settlement time is near or may overrun the start of the next race. If its time is run after 28 secs before the new race start (or whatever timing your using) the situation might be retrieved manually if you can in guardian/excel sheet col, remove the "Bet Angel" by going to the blank area at the top and then in the upcoming race you wish to bet on,apply "BetAngel" to manually force a binding .
WHEN RETRIEVAL TIME IS MORE THAN 28 SECOND BEFORE OFF-TIME
Then use your Macro ctrl v shortcut to clear the status before the start time and the bet timed for OFFTIME should occur as normal. In fact you can manually force a bind for any future race you like passing over any interceding races.
If a race is late it can often affect a passover of a number of races in succession as usually a rebinding will occur only at the end of the current active race.
RETRIEVE AFTER 28 SEC BUT BEFORE OFF-TIME
If the current active race is not yet off and you are anxious to get on then the following procedure is recommended provided you have the time to deploy.
(1) Move the "Bet angel" from the old race to the required race manually as expained(It will take a few seconds max)
(2) Copy the adjusted bet with no timing at AE2 to L9 and then copy from L9 to L11,L13,L15 etc down to the last bet space in that column adjacent to a runner.
(3) The bets will place as you copy. Without saving the sheet ,In guardian/Excel close the workbook and Browse
to find and open the file "BetAngel_1.xlsm". Click open again in Guardian/excel and tick connect. The
original formulas should then be restored.
I appreciate that for many my post will be mind numbingly boring, for others it will be hilarious
and for a few of my own ilk it might be useful. To all but the last I apologise.
Question : What were you doing during the lockdown? Answer : "Status Clearing"!!









































-
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi,
looks like my post got sent without most of the images and the sheet. I am going to attempt to load them here!!
THE PRIVATE SUB IMAGE
Grindhog
PRIVATE_SUB.png
Reset_bet_2020-06-13 231037.png
THE RESET_BET IMAGE
Reset_bet_2020-06-13 231037.png
STATUS_BLOCKER_IN_PLACE.png
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Hi Main macr0_Status_clearing sheet to attach
BetAngel_Multiple.xls
This FILE HAS TO SAVED AS AN XSLM TO ENABLE THE MACRO AS WRITTEN TO WORK. OTHERWISE CHANGE THE SHEET NAME IN
RESET_BET FROM BetAngel_1.xlsm to BetAngel_1_xls
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Added wrong file in error
BetAngel_1 .xls
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Unable to get these on the original file - Exceeded file size presumably. Let me emphasise to change the filename by resave as from .xls to .xlsm to have the MACRO work. Haloooooooo is anybody out there??? :D
STATUS_BLOCKER_IN_PLACE.png

status_cleared.png



BETS_PLACED.png
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

AUTOCLEARS_STATUS_ON_MKT_CHANGE-Final one I hope!!
The sheet is fully automated to clear the STATUS REPORTS at Col O.
without having to apply any timing as was in the previous macro. This readies the sheet for any subsequent bet. It is not a good idea to have the bet primed to go immediately as stated earlier as it mostly leads to double bets
Have no clue why it occurs but I presume the bets can sneak in while the System is busy with the Status clearing .
To keep with the automation bit the bets can be timed in the bet command formula to arrive at any time (hopefully if the race is not delayed later) relative to scheduled Start-Time. Ctrl b shortcut key allows more clearing for additional bets if required also.
Have fun with it. :D
BetAngel_1_AUTOCLEARS_STATUS_AT_MKT_CHANGE.xls
AUTO_CLEAR_PRIVATESUB_andRESET_BET.png
AUTOCLEAR_STATUS_CODE.png
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

AUTOCLEAR STATUS ON MKT CHANGE V2
Thought of a rare situation where the Macros might run twice which I have corrected in this the final version.
The Macro runs when the "Suspended" and not "In-play displays and there is an event change also.
It could occur that after the STATUS is cleared and the BETS PLACED another unexpected "Suspended" might arise while the horses
were going down/or at the post when say a horse runs loose. My change prevents another Status clearing and further bets by checking that the count of LAY and BACK bets combined are Zero. Further manual clearing by Ctrl b is not affected by the changes.
BetAngel_1_AUTOCLEARS_STATUS_AT_MKT_CHANGE (V2).xls
AUTOCLEAR_STAUS_PRIVATE_SUB_CODE.png
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Im afraid there is a problem with V2 and I am working on a solution. V1 works perfectly well . If anybody can provide a solution for Ver2
in the meantime (while its not critical as it fixes only a fairly rare situation) please post it.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

Potential problem of extra clear_status and extra bet when system suspends before race solved.
Put in extra condition essentially that the racecard data must be unpopulated. It de-populates just before the In-play drops off while suspended so far without fail allowing for the status clearing but not for any (loose Horse) suspension just before race.
AUTOCLEAR_STATUS_Private_SUB.png
BetAngel_1_AUTOCLEARS_STATUS_AT_MKT_CHANGE (V2.1).xls
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

There was Sheet 2 code that I was experimenting with left in that maybe for safety is taken out. I have done this and attached the revised file.Also the is best saved then as an xslm file which is designed specifically for macro enabled sheets. I could not upload an xlsm file directly
as it is not a supported upload file type.the xls filetype is supported
It worked perfectly all day yesterday with these revisions and so all should be good.
BetAngel_1_AUTOCLEARS_STATUS_AT_MKT_CHANGE (V2.1).xls
You do not have the required permissions to view the files attached to this post.
Dr Ginhog
Posts: 260
Joined: Wed Nov 03, 2010 12:10 am
Location: Ballygarvan,Cork Ireland T12D2VR
Contact:

This the last change.While i corrected a macro problem in the previous upload I never corrected the sheet itself which had Headers and command option info removed also. Now we have the fully corrected working version. If anybody has any comments or criticisms about the
sheet I dont mind hearing from you. While its all been done before and done very well I would like to think this is a different fully automated (with manual control also) variation on a theme.
BetAngel_1_AUTOCLEARS_STATUS_AT_MKT_CHANGE_(V2.1).xls
You do not have the required permissions to view the files attached to this post.
bvdahl
Posts: 4
Joined: Tue Oct 13, 2020 7:55 am

Hello all,

I just created a routine that runs every minute and clears the status fields. It can also be triggered by a button I have in the sheet. I found that if I tried to tie this to cell changes, there were too many things happening at once, and the danger of double bets was very high. Running this task on a schedule (in the example, every minute, but it can be changed), leads to much fewer errors.

I am using it for Golf, which I guess is much less time-critical than most sports as the start approaches, but maybe you will find it useful anyway. I am pasting the specific code, and not the whole spreadsheet, but hopefully, it is clear.

One last note, you have to remember to run the 'StopTimer' procedure before closing the sheet, otherwise, it will mess with the state of excel. I have put it in the close event of the spreadsheet, in addition to having a button for it + a cell that turns green once the timer is running.

Cheers,
Baard

Code: Select all

Public RunWhen As Double
Public Const cRunWhat = "ScheduleClearUpdates"

Sub StartTimer()

Sheets("Settings").Range("L12").Interior.ColorIndex = 4



RunWhen = Now + TimeSerial(0, 1, 0)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

Sub StopTimer()

Sheets("Settings").Range("L12").Interior.ColorIndex = 3

   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub

Sub ScheduleClearUpdates()
    Tour = "EUR"
    Call ClearStatuses
    Tour = "PGA"
    Call ClearStatuses
    Tour = ""
    Sheets("Settings").Range("L13") = Now
    Call StartTimer
End Sub


Sub ClearStatuses()

Dim BFNames() As String
Dim PlayerRow As Integer

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False


If TypeName(Application.Caller) = "String" Then

    Tour = Left(Application.Caller, 3)

End If

Call DefineSheets

ReDim BFSheets(1 To 4)

BFSheets(1) = tw.Name
BFSheets(2) = t5.Name
BFSheets(3) = t10.Name
BFSheets(4) = t20.Name

For i = 1 To UBound(BFSheets)

    LastRow = Sheets(BFSheets(i)).Cells(1000, 2).End(xlUp).Row
    
    Sheets(BFSheets(i)).Cells(6, 15) = ""
    
    For ii = 9 To LastRow Step 2
    
        If Sheets(BFSheets(i)).Cells(ii, 15) <> "" And Sheets(BFSheets(i)).Cells(ii, 15) <> "PLACING" Then
        
            Sheets(BFSheets(i)).Cells(ii, 15) = ""
        
        End If
    
        If Sheets(BFSheets(i)).Cells(ii + 1, 12) <> "" And Sheets(BFSheets(i)).Cells(ii, 15) <> "PLACING" Then
        
            Sheets(BFSheets(i)).Cells(ii + 1, 12) = ""
        
        End If
    
    Next ii
    

Next i



Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Sub DefineSheets()

'Sets the variable for the current workbook

Set wb = ActiveWorkbook


'FilePath = Range("PlayerFile")
'Set gp = Workbooks("GolfPlayers.xlsx")


'Call FindNewestFile
'Setting the variables for each worksheet


Set s = wb.Sheets("Settings")
Set p = wb.Sheets("Players")

Set tw = wb.Sheets(Tour & " Winner")
Set t5 = wb.Sheets(Tour & " Top5")
Set t10 = wb.Sheets(Tour & " Top10")
Set t20 = wb.Sheets(Tour & " Top20")
Set tm = wb.Sheets(Tour & " MatchUps")
Set twb = wb.Sheets(Tour & " Winner Bets")
Set t5b = wb.Sheets(Tour & " Top5 Bets")
Set t10b = wb.Sheets(Tour & " Top10 Bets")
Set t20b = wb.Sheets(Tour & " Top20 Bets")
Set f = wb.Sheets(Tour & " Field")

End Sub
Post Reply

Return to “Bet Angel - Example spreadsheets”