Record a price at a set times ie: 2 hrs before off

[nhmaster]

Hello

Already asked but cannot work it out, how can I record a price at a 3 set times ie: 2 hrs , 30 mins , 5 mins before the off, if I can get it working will be prepared to show what idea I have, for your help

Cheers Shaun

[phrenetic]

Shaun

Do you need to record prices for all horses for each race?

It should be possible, but will require at least 3 quick pick lists - one for each time frame - as you will need to ensure suitable separation.

You probably need 3 tabs in a spreadsheet to take the data from BA, and a 4th tab to record the prices on.

Personally I would set-up 4 quick pick lists - one to cycle through to collect the names of all runners, one to record prices at 2 hours before the race, one for 30 mins before the race and the last for 5 mins before the race.

I have something that can probably be adapted - PM me if interested.

Alistair

[Captain Sensible]

phrenetic, it could be done easily enough with one sheet> I used the code below to log two separate time frames on a sheet and easy enough to extend it to three - I think it was probably you that gave me the code

Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Target.Rows.Count
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(1, 26) > Cells(4, 38) Then
If Cells(1, 26) < Cells(3, 38) Then
For i = 5 To 35
Cells(i, 30) = Cells(i, 15)
Next i
Else
Cells(1, 31) = "Done 10"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Cells(1, 26) > Cells(6, 38) Then
If Cells(1, 26) < Cells(5, 38) Then
For i = 5 To 35
Cells(i, 31) = Cells(i, 15)
Next i
Else
Cells(1, 32) = "Waiting to log"
End If
Application.EnableEvents = True

Else

Application.EnableEvents = True
End If


End Sub


I had the time cell D2 copied to Z1 (If Cells(1, 26)) just because i'd hidden the cell D2 so I could see more of what I needed rather than all price cells. I then just enter the start and end logging times in AL4 to AL7 (Cells(6, 38) etc) so I could change when needed.

If you don't understand the code just PM an email add and I'll send the sheet as it's easier than trying to explain it

[Captain Sensible]

Seems to have changed all my 38 ) to 38)

[phrenetic]

Captain

Yes - I think that code did originate from me.

The problem, as I see it, is the lengths of time involved here.

It's probably OK to take data at 10 mins before the race and again at 5 mins before the race, but at 2 hours I think you run into difficulties.

Imagine a quick pick list, filled with today's races. You can set the quick pick list to start monitoring a market 2 hours before the off, but if you do it will auto-advance to the next race every 10-15 minutes, and thus you will never see the odds at 30 mins and at 5 mins.

I thought initially that you could just separate meetings onto separate tabs, but I don't think that would work in this case - again due to the timings involved.

Hence, I thought you would have to move to a quick pick list for each time frame - and collate the results on a single worksheet so that you could use it as the basis of any lookups.

Of course, I could be wrong here - and a simpler way of doing things, I'm sure, would be appreciated.

Alistair

[Captain Sensible]

Must admit I hadn't thought he'd want all races :

Guess you would need four sheets in a workbook to do the days racing one for each time slot and one to log results. Could always generate a cell reference to log each race to by stripping out the race time and multiplying by 864 to allow for the 3 time slots (i.e. 5mins * 288 =1 so 3*288 allows for three columns each race)