How do I record all the odds

[eightball]

In the "F" column at the moment excel opens the next market? Example F5 = 3.2 How do I record this into another cell without it changing when the F5 cell updates?

[rourkem]

Hi EB, have you found anything that works for this by any chance already?

[Captain Sensible]

Do you know how to use VBA, probably the easiest soltion just to stick in a routine as you can then just expand it as you go along.

I'm assuming you're trying to freeze the first price of the market you opened and not the prices from the prev market? The following should just stick a copy of the F5:F60 prices into AA5:AA60 when the market changes, i.e. the value in A1 changes

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("AA5:AA60").Value = Range("F5:F60").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


[eightball]

Do you know how to use VBA, probably the easiest soltion just to stick in a routine as you can then just expand it as you go along.

I'm assuming you're trying to freeze the first price of the market you opened and not the prices from the prev market? The following should just stick a copy of the F5:F60 prices into AA5:AA60 when the market changes, i.e. the value in A1 changes

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("AA5:AA60").Value = Range("F5:F60").Value = ""
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


Thanks for the your help, but it is having a problem with the following line. Range("AA5:AA60").Value = Range("F5:F60").Value = ""

[Captain Sensible]

Sorry about that just cut and pasted some code, it should have read as

Range("AA5:AA60").Value = Range("F5:F60").Value

So when the market changes it just put's the value of the F columns into AA column, probably wise to add Range("AA5:AA60").Value ="" before so it deletes any residual data from markets where there's more or less runners. I have something similar that just logs from a few minutes before the off so it's easy to tweak to your needs

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("AA5:AA60").Value =  ""
Range("AA5:AA60").Value = Range("F5:F60").Value
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


[Captain Sensible]

Actually no need for that line, Range("AA5:AA60").Value =""

I was just wondering why I'd pasted it in but must have just been cos I'd cut and pasted stuff

[eightball]

Sorry about that just cut and pasted some code, it should have read as

Range("AA5:AA60").Value = Range("F5:F60").Value

So when the market changes it just put's the value of the F columns into AA column, probably wise to add Range("AA5:AA60").Value ="" before so it deletes any residual data from markets where there's more or less runners. I have something similar that just logs from a few minutes before the off so it's easy to tweak to your needs

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
Range("AA5:AA60").Value =  ""
Range("AA5:AA60").Value = Range("F5:F60").Value
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


That works OK but, when a new event is loaded it still showing the odds from the previous one.

[Captain Sensible]

Have you checked macros are enabled etc maybe try with worksheet change instead

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant

Application.EnableEvents = False
Application.Calculation = xlCalculationManual



If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value

Range("AA5:AA60").Value = Range("F5:F60").Value
End If
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
   
   


End Sub

[MiniBlueDragon]

Hey Cap, you reckon this would be able to combine with the Excel "next market" cell (Q2?) to record odds, swap to next market and repeat (with range defined as a variable and a quick if check like IF Cell("AA5").Value = "" THEN range = "AB5") as an example?

[Captain Sensible]

Hey Cap, you reckon this would be able to combine with the Excel "next market" cell (Q2?) to record odds, swap to next market and repeat (with range defined as a variable and a quick if check like IF Cell("AA5").Value = "" THEN range = "AB5") as an example?

Everythings possible , I think there's a sheet somewhere on the forum that logs the markets in a loop.

You just need to think through exactly what you're hoping to acheive then look at the best way to accomplish that. Not sure what you're trying to do looping thru the markets, are you trying to gather data at set points or all market moves etc Are you looping once or continually to catch certain times? If you have a sheet continually looping to catch certain points it's actually better to log the data under the runner names rather than as a market cos you can just use thing like VLOOKUP to put the data back into the sheet once that market comes round again if you see what I mean.

[MiniBlueDragon]

Nothing complex actually; the plan is to click a button to move through all races in the Quick Pick list and record the current back odds for that race, then move to the next race and record those odds either underneath or to the side of the odds from that last race. Basically a snapshot of all markets at whatever time of day I run the script.

I don't think the implementation will be hard as I've done far more complicated VBA (I currently have a spreadsheet I coded which starts a timer for the day and then at the predefined time it stops the timer, goes off to a website, gathers all race times, names and web addresses for the day and then each time the market changes it looks up the correct race from that list, goes to the website again and pulls in all relevant racecard info for that race)

Do you know off hand what cell is used for "select next race" and how it's used perchance?

[Captain Sensible]

That should be simple enough, I have something that just loops thru the markets and deletes certan types of markets, then dumps the the market names in a list.

The special triggers for moving thru markets go into Q2 (if A1 is your starting cell)
-1 goes to the next race
-2 the previous race
-5 goes to the first market in the list
-8 deletes athe market from the quick pick list

Guess the easiest way would be for you to have your routine within any Worksheet change or calculate routines already on the sheet and just have a macro button switch a cell to Yes then an if statement to trigger the copy macro routine. That'd allow you to have the macro run and reset , then just let the routine run and exit before any other coding, that way you can always reset the cell to No once it's reached the last market (when J3="L") and stick -5 in Q2 to send it back to the start. Dumping the data should be easy enough to a separate worksheet as you can just use something like this to find the last cell and dump the data under each other

Dim LastCell As Long
Worksheets("Sheet2").Range("A655").End(xlUp).Offset(1, 0).Row
Worksheets("Sheet2").Range("A" & LastCell) = Worksheets("Sheet1").Range("A1").Value

[MiniBlueDragon]

Cheers Cap!

Am I being vacant here? I can't seem to get Q2 working in Betting Assistant for BetDaq:

Sheets("Gruss").Range("Q2").Value = "-5"

[Captain Sensible]

Not sure if the Betdaq version is as advanced as the Betfair one tbh, someone else will need to answer whether the special triggers work on the Betdaq one

[MiniBlueDragon]

It's going to be just my luck isn't it? lol