Excel - how to automatically clear the 'status' column on new market?

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
jamesedwards
Posts: 2641
Joined: Wed Nov 21, 2018 6:16 pm

I'm new to Excel with Bet Angel and have been using it successfully to trigger trades.

But I'm having some frustration always needing to clear the 'status' column manually every time I connect a new market.

Looking for ideas on the easiest way to clear this column automatically on every new market please? Ideally I don't want to go into the spreadsheet at all, everything else just works on its own in the background.
User avatar
conduirez
Posts: 369
Joined: Tue May 23, 2023 8:25 pm

jamesedwards wrote:
Fri May 10, 2024 6:24 pm
I'm new to Excel with Bet Angel and have been using it successfully to trigger trades.

But I'm having some frustration always needing to clear the 'status' column manually every time I connect a new market.

Looking for ideas on the easiest way to clear this column automatically on every new market please? Ideally I don't want to go into the spreadsheet at all, everything else just works on its own in the background.
When I first started to learn Excel Macros there is a great tool, if you click the ' View' option on the menu bar in Excel and then look to the top right of the window and click the down arrow under the Macro icon, you will see a new menu, just click record Macro.

Type "a" in cell A1 "b" in cell A2 go back to the Macro icon and click the down arrow and select 'Stop Recording'.

Now click the Macro icon itself (not the down arrow below it) the macro control will open you can view the code if you want too, but click options and add a control letter, say K if you don't use it for other short cuts,.

Go back to your Excel spreadsheet delete both the 'a' and the 'b' in cells A1 and A2, now click the control key + K so (ctrl + k} at the same time and you will find it has run the same Macro you recorded and you should find
"a" in cell A1 "b" in cell A2 you have just run a macro, do the same for more complex things like you are suggesting.

A couple of words of warning on the Bet Angel spreadsheet delete the values in cells in column 'O' only after you have made sure either the stake or odds cell has no value, to stop your spreadsheet from firing another bet, secondly ensure you delete cells right down, to account for say 34 runners, just incase you trade outsiders.
User avatar
jamesedwards
Posts: 2641
Joined: Wed Nov 21, 2018 6:16 pm

conduirez wrote:
Fri May 10, 2024 10:31 pm
jamesedwards wrote:
Fri May 10, 2024 6:24 pm
I'm new to Excel with Bet Angel and have been using it successfully to trigger trades.

But I'm having some frustration always needing to clear the 'status' column manually every time I connect a new market.

Looking for ideas on the easiest way to clear this column automatically on every new market please? Ideally I don't want to go into the spreadsheet at all, everything else just works on its own in the background.
When I first started to learn Excel Macros there is a great tool, if you click the ' View' option on the menu bar in Excel and then look to the top right of the window and click the down arrow under the Macro icon, you will see a new menu, just click record Macro.

Type "a" in cell A1 "b" in cell A2 go back to the Macro icon and click the down arrow and select 'Stop Recording'.

Now click the Macro icon itself (not the down arrow below it) the macro control will open you can view the code if you want too, but click options and add a control letter, say K if you don't use it for other short cuts,.

Go back to your Excel spreadsheet delete both the 'a' and the 'b' in cells A1 and A2, now click the control key + K so (ctrl + k} at the same time and you will find it has run the same Macro you recorded and you should find
"a" in cell A1 "b" in cell A2 you have just run a macro, do the same for more complex things like you are suggesting.

A couple of words of warning delete the values in cells in column 'O' only after you have made sure either the stake or odds cell has no value, to stop your spreadsheet from firing another bet, secondly ensure you delete cells right down to account for say 34 runners, just incase you trade outsiders.
Thanks for the idea, but I'm looking for something that involves no interaction at all. I just want my spreadsheet to sit in the background doing its thing with no intervention required.

I've been looking to see if there is some functionality in excel that clears a cell based on the contents of another cell changing, but apparently not.
bob1957
Posts: 11
Joined: Fri Feb 11, 2022 7:16 am

You could enable "Win Status" in the excel settings in BetAngel and then put some VBA code to clear the status cells when "winner" is declared in the "Worksheet Calculate" event.
Or use multiple excel sheets one for each market.
User avatar
conduirez
Posts: 369
Joined: Tue May 23, 2023 8:25 pm

bob1957 wrote:
Sun May 12, 2024 1:34 am
You could enable "Win Status" in the excel settings in BetAngel and then put some VBA code to clear the status cells when "winner" is declared in the "Worksheet Calculate" event.
Or use multiple excel sheets one for each market.
Those are good suggestions.

I might prefer to create a second worksheet, I would call 'Calculated' and in cell "A2" I would put in a formula.
=if('Bet Angel'!A1<>Calculated!A1,1,0)
This would be 1 if Cells A1 in the two sheets do not match, or 0 if they do.

I would then write in the Calculated sheet code, something like this.

Code: Select all

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        If Sheets("Calculated").Cells(2,1) = 1 Then
            
            ' Call your clean up sub-routine do the cleaning of the Bet Angel worksheet
            
            
            Sheets("Calculated").Cells(1, 1) = Sheets("Bet Angel").Cells(1, 1)
        End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub
After this the value in cell A2 of the calculated sheet will be 0.
User avatar
getagrip68
Posts: 53
Joined: Thu Oct 14, 2021 5:56 pm

To the best of my knowledge you can't do what you're after, I've created a button and a macro to clear a sheet but that still involves manually 'pressing' the button to clear the sheet.

I'm not sure it's possible to repeatedly bet into the same market, with the same selection, via excel. Obviously you can bet into multiple market by using multiple sheets, I currently to 15 races at a time this way and I guess the only limit is how many sheets you can cram in on Excel.
User avatar
conduirez
Posts: 369
Joined: Tue May 23, 2023 8:25 pm

getagrip68 wrote:
Sun May 12, 2024 8:51 am
To the best of my knowledge you can't do what you're after, I've created a button and a macro to clear a sheet but that still involves manually 'pressing' the button to clear the sheet.
If you do what I suggested with the code above and call your macro from it, you will not need a button the macro will run every time you change the market in Bet Angel, Just alter your cleaning macro to account for the number of runners.
Lewis_c
Posts: 1
Joined: Fri May 17, 2024 3:56 pm

Below is the cycle that excel goes through when the worksheet is updated.

A1:B1-- Race ID and description
G1:H1– suspended & inplay
F2:F4 – Tx count, event start & countdown
A9:K24 – Runner details inc runner id, runner name, profit/loss, back/lay prices, LTP & volume
T9:AE24 --Unmatched / Matched bet info Note: The last row, in these instances 24 , will change depending on the number of runners
C2:C6 – Total Matched, Last Updated, No. Runners, Unmatched bets & Balance

There will be other changes not shown but they mostly relate to putting information in and Betfair responding ie, placing a bet and Betair pumping the relevant response back into the worksheet.

If you target A1:B1 and run the macro when this value refreshes, it’s possible that some information may be missed as the rest of the sheet has not been updated yet.

To circumvent this get the macro to run only when the C2:C6 values have been updated.
To get the macro run run only once and not continuously, the value in B1 is stored in K1. When C2:C6 is refreshed, the macro compares the two values. If they are the same, then the macro has already run, obviously if they are different then the macro will run then put the value of B1 into K1

Note: The code goes in the Bet Angel sheet as a Worksheet change event

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$C$2:$C$6") And Range("K1") <> Range("B1") Then

Range("K1") = Range("B1")

'clear the command cells before the status cells so that another bet wont trigger
Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47") = ""

'clear the status cells
Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47") = ""

End If

End Sub
bigfatwallet
Posts: 2
Joined: Fri Jun 14, 2024 11:40 pm

I have this in my AutoBot spreadsheet:

Code within the BetAngel sheet:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("F4")) Is Nothing Then
        If Me.Range("F4") < 0.00035 And Me.Range("F4") > 0.0003 And Me.Range("G1") <> "In-play" Then
            Call Module1.ClearSheet
        End If
        If Me.Range("F4") < 0.0001157 And Me.Range("G1") <> "In-play" Then
            If Sheets("Strategy").Range("I4") = "" Then
            Call Module1.PlaceBet
            End If
        End If
    End If

End Sub
In Module1 I have a sub called Clearsheet as follows:

Code: Select all

Sub ClearSheet()
Dim wsb As Worksheet: Set wsb = Sheets("Bet Angel")
Dim wss As Worksheet: Set wss = Sheets("Strategy")
wsb.Activate
For r = 9 To 68
wsb.Range(Cells(r, 12), Cells(r, 19)) = ""
Next
wss.Activate
wss.Range("I2:I8") = ""
wss.Range("I2") = Application.WorksheetFunction.Text(Now(), "hh:mm")
End Sub
This will clear the Bet Angel sheet of any previous bets, odds, stake. It triggers when the countdown on the Bet Angel tab is less than 30 seconds and greater than 26 seconds and the event is not In-play.

You could simply replace my Clarheet mdoel with your own version.

Hope it helps.
User avatar
jamesedwards
Posts: 2641
Joined: Wed Nov 21, 2018 6:16 pm

Lewis_c wrote:
Fri May 17, 2024 5:47 pm
Below is the cycle that excel goes through when the worksheet is updated.

A1:B1-- Race ID and description
G1:H1– suspended & inplay
F2:F4 – Tx count, event start & countdown
A9:K24 – Runner details inc runner id, runner name, profit/loss, back/lay prices, LTP & volume
T9:AE24 --Unmatched / Matched bet info Note: The last row, in these instances 24 , will change depending on the number of runners
C2:C6 – Total Matched, Last Updated, No. Runners, Unmatched bets & Balance

There will be other changes not shown but they mostly relate to putting information in and Betfair responding ie, placing a bet and Betair pumping the relevant response back into the worksheet.

If you target A1:B1 and run the macro when this value refreshes, it’s possible that some information may be missed as the rest of the sheet has not been updated yet.

To circumvent this get the macro to run only when the C2:C6 values have been updated.
To get the macro run run only once and not continuously, the value in B1 is stored in K1. When C2:C6 is refreshed, the macro compares the two values. If they are the same, then the macro has already run, obviously if they are different then the macro will run then put the value of B1 into K1

Note: The code goes in the Bet Angel sheet as a Worksheet change event

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = ("$C$2:$C$6") And Range("K1") <> Range("B1") Then

Range("K1") = Range("B1")

'clear the command cells before the status cells so that another bet wont trigger
Range("L9,L11,L13,L15,L17,L19,L21,L23,L25,L27,L29,L31,L33,L35,L37,L39,L41,L43,L45,L47") = ""

'clear the status cells
Range("O9,O11,O13,O15,O17,O19,O21,O23,O25,O27,O29,O31,O33,O35,O37,O39,O41,O43,O45,O47") = ""

End If

End Sub
A belated big thank you to everyone on this thread and especially Lewis for this genius solution. It clears the L and O columns only when a new market is loaded into Excel which is exactly what I needed.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”