Recording and saving odds

[iantrader]

Hi All

New to this and Excel knowledge limited, VBA non-existant.

Is it possible to record and save prices in a market a few seconds before the Off?

I saw the Record Playback v1.0 spreadsheet but it doesn't seem to work or record or save any data.

Essentially, I'd like to record the prices of the horses in a market at a certain time and either save them in the spreadsheet or save them to disc, for later analysis. And have this repeated for each market in the QuickPick List.

Would greatly appreciate any help or pointers.

[SenseiCRO]

Hello,

I made horse recorder for both, prerace and inrace.
Contact me if you are interested.

Cheers

[iantrader]

Someone gave me a bit of VBA code to capture prices when a timer reached a certain level but I can't apply it to this

Would anyone help adapt it to capture prices from a couple of worksheets?

I don't think it would be too difficult. Working through a VBA course but it's nowhere near covering anything like this yet.

Ian

[Captain Sensible]

I posted a sheet somewhere showing how to save the last odds to a separate sheet to cover all races through the day. I doubt it'd be hard to amend that to include a trail of all prices a few seconds from the off.

Easiest way I found to lock into the actual off time rather than scheduled off was by constantly having one set of data replace the old set so if we wanted to save 10 refreshes pre off in AA onwards we'd first copy cells AA5 to AI5 and dump them in AB5 to AI then drop last odds into AA5 that way you'd build up a trail of the last 10 refreshes. Just stop logging when the indicator is inplay and dump the lot into a separate sheet along with runners names and race details.

If i get time I'll try and knock one up later

[Captain Sensible]

Just had a look at the example I posted and tweaked it so it'll record a trail of 10 prices until the market goes in running, once the market has finished it'll copy the data to sheet 2. The code's just been amended from something someone else was looking to do so it's only there to give you ideas of how you can do things, you'll just need to tweak it so it copies the data you want and switches when you want it too, bu t hopefully show you it's maybe not as hard as you first thought.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Static MyMarket As Variant
   
If Target.Columns.Count <> 16 Then Exit Sub
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

If [A1].Value = MyMarket Then



   
   
   
If Range("E2").Value <> "In Play" And Range("F2").Value = "" Then
    Range("AB5:AJ50").Value = Range("AA5:AI50").Value
    Range("AA5:AA50").Value = Range("F5:F50").Value
End If


If Application.WorksheetFunction.IsText(Range("D2")) And Range("AB1").Value <> "Copied" And Range("F2").Value <> "" Then
On Error GoTo Switch_Market
Range("AC1").Value = Range("AC1").Value + 1

    If Range("AC1").Value > 2 Then

    Range("AB1").Value = "Copied"
    Dim LastDataCell As Long
    Dim LastMarketCell As Long
    LastDataCell = Sheets("Sheet2").Range("A3000").End(xlUp).Offset(1, 0).Row
    LastMarketCell = Sheets("Sheet1").Range("A60").End(xlUp).Offset(1, 0).Row - 1
    Worksheets("Sheet1").Range("A1:A" & LastMarketCell).Copy Destination:=Worksheets("Sheet2").Range("A" & LastDataCell) 'runner name
    Worksheets("Sheet1").Range("X1:X" & LastMarketCell).Copy Destination:=Worksheets("Sheet2").Range("B" & LastDataCell) 'profit/loss
    Worksheets("Sheet1").Range("AA1:AJ" & LastMarketCell).Copy Destination:=Worksheets("Sheet2").Range("C" & LastDataCell) 'last odds

    GoTo Switch_Market
    End If
   
   
End If
   


 
   
    Else
   
    MyMarket = [A1].Value
    Worksheets("Sheet1").Range("AA5:AJ50").Value = ""
   
    Range("AB1").Value = ""
   
    Range("AC1").Value = 0
End If





Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
    Range("Q2").Value = -1
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub





[iantrader]

Hi Captain

Well, thank you for taking the time to do that.

I'm sure anyone with a working knowledge of VBA would be able to adapt it but I'm not getting very far with this course.

I don't even know what a Private Sub Worksheet is. Do I just copy this into a Module? How do I activate it?

I think I might have to find a friendly programmer to do it all for me...

Ian

[Captain Sensible]

Just open up a new workbook - press Alt and F11 , that will bring up the VBA screens for you , Double click on the Sheet1 in the left hand pane to bring up that sheet and paste the code I wrote into it, then save the sheet as a Macro Enabled Workbook. Open it up make sure you enable macros if it prompts you. Then link it to excel at A1 and it should just do pretty much what you were looking for. I can stick it in a sheet for you and put it online but if you're having trouble doing the above it might be best get a programmer to code one to exactly what you need.

[Captain Sensible]

Here's a copy of the sheet I did , http://www.mediafire.com/download/4cu7w ... ook12.xlsm

ALT + F11 will bring up the vba editor if you need to change things,

[Captain Sensible]

Ooops put up the wrong link, just remember to enable macros for it to work - Data saved on sheet 2

http://www.mediafire.com/download/oaj9a ... Book1.xlsm

[iantrader]

Done that, thanks!

Does it work automatically?

What do you mean by link it to Excel in A1?

Ian

[Captain Sensible]

On Betting Assistant there's an option to link the sheet to excel on the top menu. Click excel , choose Log current prices then browse to find where you saved the sheet and double click it. There's a box that says Top Left Hand Cell and that should say the A1 then click OK to link the spreadsheet with betting assistant. Load a few greyhound or racing markets in there and it should plod along after each race has finished onto the next one in the quick pick list

[iantrader]

Ah, ok, gotit. That's what I usually do, didn't understand the A1 bit

I'll have a bit play and see how I get on.

Much appreciate your help and patience