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

[jamesedwards]

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.

[conduirez]

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.

[jamesedwards]

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]

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.

[conduirez]

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.

[getagrip68]

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.

[conduirez]

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]

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