how to automatically record markets in a quick list

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

how to automatically record markets in a quick list

Postby cjones198 » Tue Sep 22, 2020 9:22 pm

Hi

How do I set my spreadsheet to automatically start recording when a market changes ?
I have a manual recording sheet, where i have a command button which starts recording when i click it. So my set up is split into

Markets = Gruss markets sheet
Selection = sheet with a dropdown box that i select the runner and click record.
Chart = plots the price movements as a chart
Charts = saved markets
Codes = saved price increments, matched amounts etc

I know that i have to enter -1 into cell Q2,
im struggling with how i get it to automatically start recording at the start of each market?

i want it to

If Sheets("Market").Cells(2, 5).Value = "Not In Play" Then
[Q2] = ""
ElseIf Sheets("Market").Cells(2, 5).Value = "In Play" Then
[Q2] = -1

End If

so once markets in play switch to next market, my sheet records automatically, once the next market goes in play, it first stops, saves and logs chart then enters -1 into Q2 and switches market, and then starts again.

I dont want the drop down box, just gonna pick the first horse in row 5 to record

cheers

chris
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Wed Sep 23, 2020 11:54 am

Usually it's just a case of working through things logically and trying to cover all bases, things like abandoned races etc could stop your code as the market wouldn't go in play and so on. So to make life easier you'd usually stick all your code routines into separate sub routines to make your code easier to amend and read. As you already have record routines to run them is nothing more complicated than calling them like so
Code: Select all
Call Macro1
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Wed Sep 23, 2020 4:15 pm

Thanks for the reply Captain

It's a renko chart I'm logging.
So I have sheet 1 = Market
Sheet 2 = selection
Sheet 3 renko chart
So I have on sheet 1
Worksheet event change which gives an x value for horse price and x value for amount matched depending on which horse I choose on selection page.
On selection page, again use worksheet event
Which is two subs, sub 1 makes the chart according to the parameters set and horse chosen and logs it on sheet 3 (renko) but only performs when command button (sub2 ) is clicked.
Sub 2 on the sheet page is private click command button. So it's an active x button that when clicked erases the former price and volume plots on selection page, calls a macro that erases renko chart, logs the the price and amount matched on sheet 1 as my start price and start volume. Then the routine in sub 1 performs its task until market goes in play.
The only normal modules I made was a pause recording, start recording and save chart.
I am very very new to VBA and am just learning as I go along lol
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Wed Sep 23, 2020 5:28 pm

I've no idea what a renko chart is but if you already have code you use to record with a button it's just as easy to start it with VBA. All that happens when you click the button is that it calls the sub2 code to run. To do that in VBA is as simple as putting

Code: Select all
Call sub2


Your active x buttons will just have sub routines attached to them and you can see those by pressing Alt and F11 together.

So to automate things you'd basically be sticking all those routines to run in the correct order

Code: Select all
Call setup_market_sub
Call market_record_sub

If market=In Play then
Call save_sub
Call erase_sub
Call move market_sub
end if



You need to compile a flow chart as to when those actions need to run 10 minutes out/as soon as you enter the market etc and also put your sub routines into modules so they're easier to organise and edit. You seem to have already made quite a few routines so I'm guessing you have a rough idea of how to write and amend code in the VBA editor.

Most of us use simple code like below and then build up from there.

Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> MyMarket  Then
MyMarket = Worksheets("Sheet1").Range("A1").Value

' In here we'd set up all the new variables to setup a new market and it would only run once

End If


'Here would be the main section of the code where we would call sub routines that possibly run on each refresh i.e Call Record routines


Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Wed Sep 23, 2020 5:37 pm

Tha is Captain, I've got to nip out again, so will have a look in depth at your post when I get back in. A renko chart is just a block chart basically so when price goes up a coloured green block forms and when down a coloured red block forms.
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Wed Sep 23, 2020 8:12 pm

ive tried the code just to simply log price changes from how ive done it before, and it doesnt work

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
Dim Price As Variant
Dim iCol As Integer
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> MyMarket Then
MyMarket = Worksheets("Sheet1").Range("A1").Value

' In here we'd set up all the new variables to setup a new market and it would only run once

Price = Sheets("Sheet1").Cells(5, 15).Value  'store price
iCol = 4  'store col number
Sheets("Sheet2").Rows(4).Clear 'clear recorded row


End If


'Here would be the main section of the code where we would call sub routines that possibly run on each refresh i.e Call Record routines
If Price <> Sheets("Sheet1").Cells(5, 15).Value Then
Sheets("Sheet2").Cells(4, iCol).Value = Sheets("sheet1").Cells(5, 15).Value
Price = Sheets("sheet1").Cells(5, 15).Value
iCol = iCol + 1
End If

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Wed Sep 23, 2020 8:28 pm

Your variables price and icol are only set when you set mymarket. Because they're not static variables the values are reset to nothing each time the code runs as setting mymarket only happens once. You need to set them as static or public variables if you want the values to be retained.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Wed Sep 23, 2020 8:38 pm

so would i just declare
public price as variant
public iCol as variant

i tried that and the sheet wont even load up
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Wed Sep 23, 2020 8:50 pm

Done!
it works i declared iCol and Price as static

i will try and just build an auto sheet from scratch now, good practice as well, im gnna try adding if inplay then change market now
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Wed Sep 23, 2020 8:54 pm

You'd declare Public variables outside the routine and within your modules rather than under the sheet but static variables will work declared within the routine.

If you declare as public variables it means they are available to all sub routines so generally more useful than static ones. But obviously you can always tweak code later for things like that.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Wed Sep 23, 2020 9:18 pm

Lovely! it works! thanks Captain!
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Sep 24, 2020 10:49 am

Sorry to bother captain.

But when I use code
If inplay Q2 = -1, it always seems to skip to the 3rd market?
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Thu Sep 24, 2020 12:33 pm

That occurs because sometimes the market hasn't switched in time for the next refresh so In Play will still be in the cell. To get around this we use public variables so we can ensure the -1 only gets placed once.

To do that we can set a variable to true when it's needed then reset it back to false when the market has changed. Would be better to use Public variables but here's the idea behind it using a static variable


Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
Static switchMarket as Boolean

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> MyMarket Then
MyMarket = Worksheets("Sheet1").Range("A1").Value
switchMarket = False

End If

If Worksheets("Sheet1").Range("E2").Value ="In Play"  and switchMarket = False Then
Worksheets("Sheet1").Range("Q2").Value = -1
switchMarket = True

End If



Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Sep 24, 2020 12:39 pm

Thanks Captain, im about to try it
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Sep 24, 2020 12:47 pm

Works great!!! thanks Captain
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Next

Return to Discussion

Who is online

Users browsing this forum: No registered users and 20 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.