Circling through horse racing markets

[danjuma]

Could somebody help with this please or point me in the right direction?

I want my spreadsheet to go through each race in the quick pick list, and if a race meets the conditions I have set, place a bet and move on to the next race, or if condition not met, still move on to the next race after a specified time (say 10 secs or so). After gone through the last race on the list, start again from the first race in the list, and place bets in races where bets were not placed before and condition now met. Continue to do this (obviously ignoring races that have expired) until either no more race left without placed bets, or last race on list has expired.

I am sure somebody probably use something like this (automatically circling through the markets). I am thinking it will involve using the -1 and -5 triggers in cell Q2, and I can get it to check if the current market has matched bets using the MyBets sheet and move on to the next market if it does. My problem is how to get it to move on to the next market after a specified period if no bets placed in current market, and how to start again from the first race after the last race?

Many thanks

[2020vision]

Hi Danjuma

not sure if this would help but could you not simply add all markets to
the first tab and then link to Excel - monitor the whole lot in one go?

Should pick up all possible betting conditions for you pretty quick?
Something I have done through the night sometimes with fair results.

Just hoping to help, all the best - Michael

[danjuma]

Hi Danjuma

not sure if this would help but could you not simply add all markets to
the first tab and then link to Excel - monitor the whole lot in one go?

Should pick up all possible betting conditions for you pretty quick?
Something I have done through the night sometimes with fair results.

Just hoping to help, all the best - Michael

Hi 2020vision,

Thanks for the suggestion. It's an idea, but probably not going to be ideal, as my conditions are kind of complex. If I understand what you are saying - add all the races in one tab, and then get them displayed on one sheet with each race starting from a specified cell or something on the one sheet, will entail me having to write the complex condition/checks for each race (which could be as many as 50 some days).

I think I can work something out myself, just thought may be somebody on the forum has done something similar before and could give me some pointers.

Just wondered, if anybody or Gary could answer this please, if I put -1 in Q2 and it's the last race in the list, what happens next? Will the sheet start again from the first race in the list?

Thanks

[Captain Sensible]

Just wondered, if anybody or Gary could answer this please, if I put -1 in Q2 and it's the last race in the list, what happens next? Will the sheet start again from the first race in the list?

Thanks

I do something similar , excel now shows the number of markets left in the quick pick list in cell J3. The last market shows as L so I just set it to use -5 when J3 is L so it'll loop and use -1 when J3 isn't L.

As for staying on the market for a specified period you could get it to timestamp a cell when it enters the market and have it move on after a set time. Or even just use a simple counter that'll reset on entering a new market and have your move on routine move forward once the number of refreshes has hit your time limit.

Mine has a simple counter in the worksheet_change code

counter = counter + 1

that gets reset to 0 once conditions are met to move to the next market

[danjuma]

Just wondered, if anybody or Gary could answer this please, if I put -1 in Q2 and it's the last race in the list, what happens next? Will the sheet start again from the first race in the list?

Thanks

I do something similar , excel now shows the number of markets left in the quick pick list in cell J3. The last market shows as L so I just set it to use -5 when J3 is L so it'll loop and use -1 when J3 isn't L.

As for staying on the market for a specified period you could get it to timestamp a cell when it enters the market and have it move on after a set time. Or even just use a simple counter that'll reset on entering a new market and have your move on routine move forward once the number of refreshes has hit your time limit.

Mine has a simple counter in the worksheet_change code

counter = counter + 1

that gets reset to 0 once conditions are met to move to the next market

Many thanks CS. Was thinking along the same line, though was not aware of the 'number of markets left..." in cell J3 function, so many thanks for pointing this out. Cheers

[danjuma]

Ok, I have the following VBA code in a sheet named "Win". By the way, I don't really know much about VBA. All the codes I make up are just bits and pieces I pick up from this forum and sort of mix and match.

Code: Select all

Dim refreshCount As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
With ThisWorkbook.Sheets(Target.Worksheet.Name)
 If Range("X1").Value = 1 Then
  refreshCount = refreshCount + 1
 Else
  refreshCount = 0
 End If
 If refreshCount > 30 Then
  Range("X2").Value = 1
  Else
  Range("X2").Value = 0
 End If
 End With
 Application.EnableEvents = True
 End Sub


Which is meant to start counting up when a market is loaded that has no bets placed. Cell X1 = 1 when no bets have been placed in the market (determined by another formula).

Then I have the following formula in Q2:

=IF(AND(OR(Control!J23=0,Control!J24=0),J3<>"L"),-1,IF(AND(X2=1,J3<>"L"),-1,-5))

Which amongst other things look to see if X2 = 1 (after 30 refreshes) and loads next market.

What's happening though is that once I link a market to the sheet, the sheet loads the next market, and then the formula in Q2 becomes deleted and Q2 now becomes blank, so no formula in Q2 to evaluate and load next market. I have written formulae into cell Q2 in the past which works fine and stays on without being deleted after subsequent refreshes. What am I doing wrong here?

Many thanks

[Captain Sensible]

I think Q2 cell always gets cleared on refreshes , I just fill that Q2 cell using vba within the Worksheet_Change routine. You can always stick the routine in a module to stop things getting complicated and use Call to pull it up

i'e.

Call MarketChange

[danjuma]

I think Q2 cell always gets cleared on refreshes , I just fill that Q2 cell using vba within the Worksheet_Change routine. You can always stick the routine in a module to stop things getting complicated and use Call to pull it up

i'e.

Call MarketChange

Hi CS,

Many thanks for your reply and assistance as usual. Quite strange because like I mentioned before, I have used formulae in some of my past spreadsheets - example is this in one of my sheets =IF(AND(E2="In Play",F2=""),Trade!$P$21,Trade!$P$24) - and the formula has always remained after subsequent refreshes/links/new markets etc.

Anyway, with regards to your suggestion, would you mind explaining a bit to me how I would go about implementing this? Like I mentioned, I am not that proficient in VBA. Many thanks

[Captain Sensible]

Not sure why it's cleared as I've had formulas stay in place before but then get cleared for some reason. Thats why I stuck to using just VBA for Q2

You could even just move the formula to a separate cell say AA1 and use a simple line like


If refreshCount > 30 Then
Range("X2").Value = 1
Range("Q2").Value = Range("AA1").Value
Else
Range("X2").Value = 0
End If

[Captain Sensible]

That probably won't work as Application.EnableEvents is in your sheet and X2 wouldn't be updated for the formula to return the correct value

But you could put

Range("Q2").Value = Range("AA1").Value
at the top of the code so it'd ensure Q2 shows what's in the formula

I just use If and ElseIf statements for when my refresh has been hit

If Range("J3").Value = "L" And Range("AB11").Value > Range("AB17").Value Then
Range("Q2").Value = -5
End If

[danjuma]

Ok CS,

I will play around with your suggestion and see what happens. Many thanks.

[mak]

hi Danjuma
unfortunately vba is not my strong point (i am trying hard though)
Don't hesitate to post your code when you manage to get it work

[Captain Sensible]

mak something as simple as



Dim counter As Currency

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

counter = counter + 1

If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 10 Then
counter = 0
[Q2].Value = -1
End If


End Sub

will continually loop through the markets every 10 refreshes, you just need to add further conditions within the code if you don't want it to move on until bets have been placed etc

[mak]

Captain thanks
can you explain please what the phrase
counter = counter + 1

means & how is working?

Just test it & it is working fine

I was thinking much more complicated and it is very simple

[Captain Sensible]

counter = counter + 1

Is just a way of counting the refreshes so the code will only move on after a certain number of refreshes. Everytime the code executes on a market refresh it will add 1 to the value counter. So say counter holds a value of 0 on the next refresh the code adds 1 to 0 so the value of counter now becomes 1 next refresh it adds another 1 so counter now holds the value 2 and so on.

We then reset the value of counter to 0 when switching the market so it can restart counting up from 0 again


If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 10 Then
counter = 0
[Q2].Value = -1
End If