VB help needed for "Race Simulator"

[obias]

Hi all ... I have recently discovered that you can download historical data from Betfair which records when prices were first and last matched, (as well as the number of bets made and the volume traded for a particular price). They are HUGE .csv files that contain over 1 million records of data ... literally every market (win, place, without fav, forecast etc) and every race at every course offered in a month recorded on a second by second basis.

It occured to me that knowing what prices the horses were at a particular moment in time, one ought to be able to "re-run" the race so to speak ie to simulate the race as it happened (as prices changed.)
Using Excel, I've managed to do a database query to bring in a days racing records onto one sheet ... then a pivot table to summarize the price changes second by second onto another sheet....
I've written formulas to put the names of the horses (from the pivot table) into where BA puts them and the time, e.g. 14:10:00, into the cell where BA puts the race time and lookup formulas to get each horses price at that time i.e. the price the horse was matched at 14:10:00 into where BA puts the Best Back Odds.

At the moment I'm manually inputting a new time e.g. 14:10:01, 14:10:02,14:10:03, etc to cause the lookup formulas to lookup the prices matched at those times. Very tiring for a 4 minute race ... over 240 entries! and if you include the price changes before the "Off" this easily gets to over 600 records for even a 5f race!!
I reckon a vb routine could do this for me but I don't know how ...I'm OK putting together spreadsheets using formulas but not programming.

Do any of you guys know how to write such a routine? ... Also, if the lookup formula returns 0, because no matched bet was made at that particular second, I need to have the program "remember" the last matched price and offer that as the current price instead of zero. Also, as a new price is matched, it would be great to copy the current Best Back odds price into the position for the 2nd best back odds price and put the new best back odds into the Best Back odds position. In this way, as the routine added 1 second to the time, all the price would update thereby simulating the race.

Once we had a means of simulating a past race, using actual price changes as they happened, we can use it to test our systems of betting, triggered betting in particular. We could use it to review races to hopefully spot useful trends.

Any thoughts on the idea? Any help available? ... I can post a typical pivot table if that would help.

[Dedes]

Hi,

Tell me something, what kind of trend do you expect to find from past races?

[GeorgeUK]

The time thing can be done quite easily via code, but i would recommend a start and stop button.
you could put the start time in a certain cell that the formulas link to
press the button to start the clock
the cell would then update by a second every second
(something like application.ontime now + timevalue("00:00:01"), "updateclock")
then press another button to stop the clock so that you can do other things with the computer or amend something in your formulas/code.

As for the rest, that would take quite a bit of doing. I assume you are using vlookup or sumproduct formulas - not sure how to get past the 0 for matched price, unless maybe put in another column to your table which will do this and have the formulas look at this instead. Probably doable with code, but would take quite a bit of coding probably and would need to know the layout of your data etc.

If you want to be able to test your triggered betting without risking anything, the easiest thing would be to setup BA but not login to betfair - then you can see what bets would be trying to be placed without risking your bank.

As for spotting trends in a race - never had much luck with that but i have downloaded the data you are talking about a few weeks ago. Now i need to set them up in access as they are way too big for excel, and of course - learn how to use access as i have never used it before.

Good luck with this.

[obias]

Hi,

Tell me something, what kind of trend do you expect to find from past races?

Hi Dedes,
I was hoping to see if there's any fav/rest of field price structure that indicates a strong fav eg when (during in-play) the fav is 2/1, 2nd fav is 5/2 and 3rd fav is 12/1 is this a strong indicator that the race is between the fav & 2nd fav?
By examining past races, I hope to get a statistic for this e.g. the above scenario is correct 68%, or 52% or 87% of the time.
If it was somethjing like 87% true, then you could confidently set your Excel sheet to detect those conditions and fire off a dutch bet on the 1st 2. Without the statistic you're left hoping you're right and in my experience, once you've put a bet on and find yourself hoping you're right, you've already lost!

[Dedes]

Hi!

I've worked on something a bit similar some time ago. What I did was much more simple though. I just tried to colour the first, second and third horse on Excel and when they were at low suficient odds to make me beleive that from these I would have my winner a bet would be trigger. I soon saw that on horse racing "in play" markets they're completly unfair. A horse can be at 1.5 and then jump to 10 and back again to 1.5. Made me lose money.

Then I found a company selling a live track software (RapidMobile) and I thought that this way I could easely follow the races live and trigger a bet upon what I was seeing. This software runs faster then SIS. I couldn't be more wrong. The problem is that when you think a favourite is not going to win just because he's last... he can win!!! And when people start really beleiving that he's not going to win the odds driff so fast that we don't have money in tha bank to make the Lay bet. Or you could try and do the other thing around: try to find the winner. But what makes horse racing so fun is that you have very competitive races. Almost all of them. And after you decided on a horse there's no time for error. You win or lose.

I know many people won't agree with me, and thanks for that, but I think the way to be sucessuful on horse racing is before the race, trading. So, what we should do is to study this markets and try to see if there's anything there to make things the right way. WOM and POM are nice and logical but they lack a lot on profit.

Sorry if I made myself sound very pessimistic