Constrained Pick List through to Excel Connection - How to?

[alanh99]

I posted this question on Saturday, but it seems to have gone missing.

As a new subscriber, I would like to ask whether there is a relative simple way of generating a Quick Pick list meeting a set of constraints, for instance

all races on a particular date for
Horse Racing Australia,
Gallops only,
State Capitals only (A grade venues)

and then connecting those to one or more Excel Workbooks/Worksheets,
of the Bet Placer type, with mods.

Basically I wish to automate the set-up process for an application which reacts to price movements as they occur, on a set of races which meet particular criteria.

I have reasonable experience programming VBA.

cheers, Alan H

[GaryRussell]

If you install the Beta version you have the option to add gallops only. That is far as it goes. You then have two possibilities.

1. Let it cycle through all the gallops races, but build into your criteria that races with specific text in cell A1 (race description) are ignored. If you cannot identify state capitals from this then it can't be done I'm afraid.

2. Use the special triggers in cell Q2 to cycle through the list and delete the races that are not required. -1 will move to the next race and -8 will delete the market from the list. -5 will move back to the top of the list. See http://gruss-software.co.uk/forum/viewtopic.php?t=5163 for an example using -1.

See http://www.gruss-software.co.uk/forum/viewforum.php?f=9 for latest Beta version.

[alanh99]

Thanks for the rapid reply, Gary.

I am not clear how I get 'it' to cycle through all of the gallops races. Do you mean the Excel spreadsheet? Is there a quick way of getting all of the, say, gallops races on a particular day onto a spreadsheet in a format like the 'bet-placer' one, without pointing and clicking? Once I know how to do that I can certainly do automated selection, and cutting and pasting based on whatever constraint criteria I want, provided the necessary 'hooks' are in the data. I have written myself a number of subs that do things like finding how many populated rows and columns there are overall, and finding row number of the next occurrence of a nominated string in any column, and so on, which I use in building Linear Programs and simulation models.

I will have a good look at your topic 5163 link, and download the beta version, but a quick pointer on how to get a spreadsheet populated in the first instance would be invaluable. I may be being dense, but I can't find any reference to that yet.

cheers,

Alan H

[GaryRussell]

Thanks for the rapid reply, Gary.

I am not clear how I get 'it' to cycle through all of the gallops races. Do you mean the Excel spreadsheet? Is there a quick way of getting all of the, say, gallops races on a particular day onto a spreadsheet in a format like the 'bet-placer' one, without pointing and clicking? Once I know how to do that I can certainly do automated selection, and cutting and pasting based on whatever constraint criteria I want, provided the necessary 'hooks' are in the data. I have written myself a number of subs that do things like finding how many populated rows and columns there are overall, and finding row number of the next occurrence of a nominated string in any column, and so on, which I use in building Linear Programs and simulation models.

I will have a good look at your topic 5163 link, and download the beta version, but a quick pointer on how to get a spreadsheet populated in the first instance would be invaluable. I may be being dense, but I can't find any reference to that yet.

cheers,

Alan H

Do you mean all the markets on the same spreadsheet at the same time so that they are all being monitored all day leading up to the race or do you only need to monitor them from a few minutes up to the start?

[alanh99]

Just a couple of minutes from advertised start.

[alanh99]

Sorry, pressed submit too quickly. I will be setting a couple of cell values on the sheet at the time we start monitoring, which will be used to determine bet placement. I have already modified the bet-placer to do more-or-less what I want.

Specifically what I need to do is to open a sheet (or section on a sheet) for each race in a pre-determined Pick List at, say a couple of minutes before each race is due to start, transfer in some parameters to specific cells, carry out calcs based on the parameters and the prices as they update, then place a bet (or bets) based on decision rules, say ten seconds (or less) before the advertised off. After the race, the race sheet, or part-sheet can be deleted (-8 in Q2?), after it has transferred pertinent info to an archive workbook. I presume I can feed in the race results and outcomes from elsewhere?

For reasons of management and tidiness, and not stressing Excel, a limited number of workbooks/worksheets open at a time would be preferable.

How quickly can each race result be obtained from Betfair automatically?

cheers,

Alan H

[GaryRussell]

Once you have the markets you want in the quick pick list you can have them automatically opened at a specific number of minutes before the scheduled start. Use Market|Auto select markets|At specific time before the off.

If you are using the Beta version. You can use the trigger "-3.33" in cell Q2 to load the quick pick list with AUS Gallops.

You can probably avoid having to use -8 to delete items from the quick pick list. Just build into your criteria not to place bets on those markets.

I presume I can feed in the race results and outcomes from elsewhere?

If you know how to do this with VBA then it's possible.

Betting Assistant does not obtain the race result from Betfair. If you use the results sheet feature it will log the results for bets you have placed, but cannot know the winner if you have not bet on it. The results sheet is updated when a market is opened. It's dependent on when Betfair settle the market. If for example it is opening the markets one minute before the scheduled off and the races are 20 minutes apart then the results are usually available. If they are 5 minutes apart they might not yet be available.

[alanh99]

Hi Gary, thanks for the reply. I don't know whether I am being obtuse, but there are some things I just don't 'get' at the moment. I have no previous experience with Excel interacting with the internet. My VBA applications, although quite complicated, are all static applications used as front ends to Operations Research models in large corporates. Any pointers to get me up to speed would be most appreciated.

My first problem is that the 'Help - User Guide' does not work.

If I click on it I get a window with the following message. "Alternate HTML content should be placed here. This content requires the Adobe Flash Player. Get Flash" I am running Windows 7 Home Premium 64 bit. I, of course, already have Adobe Flash installed. If I didn't many of the web pages I visit would not work. Just in case, I installed the 64 bit Flash beta, by clicking on the link and following the instructions. No effect, same message.

Next, I have downloaded and am running the 'beta' version. When I open the Visual Basic editor via 'Developer' in an active BA spreadsheet, should I be able to see any code? When I open your 5163 topic 'move to next market' example, I can, of course see the code in Sheet 1. I cut and pasted it into Sheet 1 my modified version of the 'Bet Placer' workbook that I am opening from BA. This sheet is updating each second as it should, and my additional code, in the sheet itself, works fine. However if I put -1, or -8 or -3.33 into Q2, nothing happens, except that the entered figure disappears on the next update, ie next second. From the text you have placed in Q1 I presume Q2 should also change the update frequency if a positive integer is entered. This doesn't happen either.

Also, I see in the formula in the active cells of the 'Trigger' column that a procedure called "BACK" is called. Where and what is that, please? I wish to emulate it to save a snapshot of the sheet at the instant that the bet is placed. I have no desire whatsoever to pinch any of your IP, since I am more than happy to use your application at its very reasonable price, but I feel I need to know how this all works, to work out what I can and cannot do within the software. As soon as I can get a prototype working I will take out a yearly subscription.

I can get the race results from elsewhere, so please forget that I even mentioned it.

When is the best time slot to catch you, please? I realise it is 2 am there so that doesn't work. 6 pm onward, our time, maybe?

cheers,

Alan H

[GaryRussell]

The user guide will work if you use the 32 bit version of Internet Explorer. It should be on your PC. You can view it here http://www.gruss-software.co.uk/Betting ... p/help.htm

When I open the Visual Basic editor via 'Developer' in an active BA spreadsheet, should I be able to see any code?

It depends whether the example uses VBA. VBA isn't always necessary. You could just use a blank workbook and use formulas for triggering bets.

However if I put -1, or -8 or -3.33 into Q2, nothing happens, except that the entered figure disappears on the next update, ie next second. From the text you have placed in Q1 I presume Q2 should also change the update frequency if a positive integer is entered. This doesn't happen either.

There aren't any issues preventing this from working that I am aware of. How are you entering the values? What happens if you just link to a blank workbook and type -1 into cell Q2? You should see it change to the next market in the quick pick list.

Also, I see in the formula in the active cells of the 'Trigger' column that a procedure called "BACK" is called. Where and what is that, please?

"BACK" is not a procedure in the spreadsheet. Each time Betting Assistant updates the spreadsheet it reads this cell and if it sees "BACK" then it will place a back bet using the odds and stakes in the next columns.

I recommend trying things manually to start with to get a feel for how it works. Try entering some odds and stakes in columns R and S then type BACK or LAY in column Q. See what happens.

[alanh99]

Excellent Gary, I figured out how silly my "BACK" remark was quite soon after I posted that last reply. I googled and found some stuff about the 'worksheet_change' event and have set up my spreadsheet to do various things based on the value in cell S1, using your in-feed to C2 as the change trigger.

I have a spare notebook running 32 bit, so I am just getting the Help up on it. No probs.

I gather I need to set up the Quick Pick list by hand, though, although that is no great chore, just scrolling and hitting the q key.
Q1. Is it the case that I can't feed a pick list back into BA by dumping a big list out and refining it, then uploading? No real worry, although it could be nice in the future.

At present I don't understand how the -3.33 in Q2 thing works.
Q2. Where do the AUS gallops get put? Does it populate the Quick Pick list in the main BA application screen?

The ability to auto-select markets in the pick list at a set time before the off is appreciated. When I get the cell Q2 thing working I gather I should be able to stop the Excel updating a soon as the race goes In Play, to save the BetFair servers some effort (not to mention fees), and rely on BA to start it up again a minute before the next event.
Q3. Could you please confirm this?

Again, thanks for your patience and forbearance.

cheers,

Alan H

[GaryRussell]

Q1. Is it the case that I can't feed a pick list back into BA by dumping a big list out and refining it, then uploading? No real worry, although it could be nice in the future.

That's right.

Q2. Where do the AUS gallops get put? Does it populate the Quick Pick list in the main BA application screen?

Yes the quick pick list in the main BA screen is populated.

The ability to auto-select markets in the pick list at a set time before the off is appreciated. When I get the cell Q2 thing working I gather I should be able to stop the Excel updating a soon as the race goes In Play, to save the BetFair servers some effort (not to mention fees), and rely on BA to start it up again a minute before the next event.
Q3. Could you please confirm this?

It doesn't work exactly like this. It stops updating when the market is closed which is typically 5 minutes aftet the race. You could use Q2 to adjust the refresh rate to a much slower rate when aren't interested in monitoring.

To answer your question from earlier. We monitor the forum most actively between 9am to 5pm UK time. We also monitor at other times so sometimes you will get a quick answer outside these hours.

[alanh99]

All good, Gary. Now I just have to get the q2 thing working. I will do as you suggest, with a brand new workbook, and hopefully everything will be fine.

Thanks again

Alan H

[alanh99]

Hi Gary, The Q2 thing has me puzzled. I have done as you suggested and used a newly created Workbook to link to. Put a few races in the Quick Pick List, then selected one, clicked on Excel in the top menu, then the first option 'Log Current Prices'. Switch to the spreadsheet and, as expected, time is counting dowm, prices moving at 1 second intervals. Put 3 in Q2, by selecting cell, typing 3 and return, which should, I gather, change to 3 second refresh. The figure 3 appears, and stays there. Seconds still count down at one second intervals. Put in -1, should jump to next in Quick Pick list; nothing happens. If I put a figure in any position before column Q, they disappear at next refresh, but anything entered in any cell in column Q and beyond just stays there. Haven't tried Bet Placer in anger yet, but I suspect it may not work either, and I will certainly need that functionality soon.

'About' on top left of BA screen says I have Ver 1.1.0.66x75

As I have mentioned previously I am running Win 7 Home Premium on this desktop box. I use Firefox as my main browser. As you said, Internet Explorer 32 and 64 bit are both there.

Any suggestions gladly received.
cheers,

Alan H

[osknows]

Hi Alan,

Have you checked 'Enable Triggered Betting'? Some of the Q2 functions in Excel will only operate with this checked.

The refresh rate in Q2 sets how often BA writes new data to Excel. The data gets written in 2 blocks and will overwrite any cells within these blocks which is why you see data 'disappear'. Also, Q2 needs to be cleared as if the -1 stayed it would continually forward through all markets.

Have a read of this thread http://www.gruss-software.co.uk/forum/v ... php?t=5132

There's some code in there to show the address of the cells that are being overwritten with each BA refresh; it may give you a better feel for what's happening behind the scenes?

[alanh99]

Where do I check 'Enable Triggered Betting', please?

thanks,

Alan H