Price history

[Volker0866]

Hi fans,
now I made first attempt to create a useful Excel Sheet to handle the bets.
Whats missing are the odds against the time. I need this for analysing purpose.

Anybody here who have a script/macro to store odds = function (time) into seperate Excel sheets/tabs.

(I have only very basic programming knowledge.)

Volker

[Volker0866]

By the way, I get message "log individual prices are no more active because of license problems with betfair."
But in principle, LogIndividualPrices are that what I want - but for all selections at once. This would be fantastic!

[GeorgeUK]

This thread i think touched on what you want.

In the code on the 2nd page, it retains about the last 20 odds.
It can probably be amended to more, but there is a maximum of 255 as this is the total number of columns.
Otherwise the data will need to be stored going down the page (65,536 rows max)

I can't do it at work (and am not yet subscribed to BA) so not sure i can be of much more use.
Will look again when i have time.

George

[Volker0866]

Hi George, sounds good - thats's what I want. In your reply you mentioned code for the last 20 odds retaining
or a link to a code? can't see.

Volker

[GeorgeUK]

Sorry about that. I think this is the code i was talking about

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow
Dim CopyingTo As Range
Static x As Range
Set lastrow = Sheets("Sheet2").Range("A65536").End(xlUp)
Set CopyingTo = Sheets("Sheet2").Range("A5:A" & lastrow.Row)

Range(Range("F5"), Range("F65536").End(xlUp)).Copy _
Destination:=CopyingTo.End(xlToRight).Offset(0, 1)

    If CopyingTo.End(xlToRight).Offset(0, 1).Column > 120 Then
        CopyingTo.Offset(0, 1).Delete Shift:=xlToLeft
    End If

If ActiveSheet.ChartObjects.Count > 0 Then

    ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(z), PlotBy:= _
    xlRows
End If
End Sub


It will retain the last 120 values, then remove the 1st to make space for the last.

It may be possible to recode so that once F5:F?? is copied to sheet2, that data is put instead of in rows to columns
Possibly having the data copied, then use pastespecial - transpose
this should then allow you to use all 65536 rows instead of 255 columns to store the data.

Hopefully someone else can jump in here. I'll try to have a play about with it tonight, but will be quite busy today.

Note: Where to put this code - rightclick on the worksheet tab that has the data being updated. View code. Paste the above in the window that appears. Now when the data updates, it should appear on sheet2

[Volker0866]

Hi George,
this is perfect. Hope you did not spend too much time for it.
I will try it this evening to integrate it into my excel sheet.
Bye for now,
Volker

[Volker0866]

Hi George,
I tried it, but I get runtime errors '1004'.
The debugger stops at bold marked location. Don't know why, as I cannot programm. Last time I programmed was with C64-Basic Interpreter and a bit of pascal prog.language.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow
Dim CopyingTo As Range
Static x As Range
Set lastrow = Sheets("Sheet2").Range("A65536").End(xlUp)
Set CopyingTo = Sheets("Sheet2").Range("A5:A" & lastrow.Row)

Range(Range("F5"), Range("F65536").End(xlUp)).Copy _
Destination:=CopyingTo.End(xlToRight).Offset(0, 1)

If CopyingTo.End(xlToRight).Offset(0, 1).Column > 120 Then
CopyingTo.Offset(0, 1).Delete Shift:=xlToLeft
End If

If ActiveSheet.ChartObjects.Count > 0 Then

ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(z), PlotBy:= _
xlRows
End If
End Sub

[GeorgeUK]

I've been a numpty

The code seems to work (i think)
but i had left a small bug in it that i never got round to fixing.

From the original thread:
copy A5 to B30 to sheet2 A5 - we need a figure in column B - this can be fixed later with a workbook open event or something similar

So as long as there is data in sheet2 column B for all the runners - it should be ok.

sorry about that.

[GeorgeUK]

Before the line you highlighted in bold, try putting (untested code)

dim a as range
for each a in CopyingTo
b=b+1
sheets("Sheet2").Range("A" & a.row).value = b
sheets("Sheet2").Range("B" & a.row).value = range("a" & a.row).value
next a
b=empty

Range(Range("F5"), Range("F65536...

This should (i hope) copy the horse name to column B of sheet2 on each refresh and also list the horses in column A 1,2,3...

So the code should hopefully work without you having to copy and paste data before the macro will work.
How's that for a theory?

Let me know how you get on. As i said, i'm not yet signed up so is a little difficult for me to do any testing.
I think it's right though.

[Volker0866]

ok, not so bad but still a runtime error 1004

Now have the following code in sheet1. Whats happening is: first selection is written to B5
On Colum C there are odds beginning at C1, C2, C3, ....
on Colum D I find odds shifted back to D5, D6, D7, .... then everythink seams ok.
After comming to the right end, odds are written to the left back.
I see no Graph, but I can make it with diagramm function - but I'm sure, your ...PlotBy:= _
xlRows ...
makes this much better - when it will work.
I get the next runtime error after odds comming back to Colum A. Its hanging at bold code.

Volker


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow
Dim CopyingTo As Range
Static x As Range
Set lastrow = Sheets("Sheet2").Range("A65536").End(xlUp)
Set CopyingTo = Sheets("Sheet2").Range("A5:A" & lastrow.Row)

Dim a As Range
For Each a In CopyingTo
b = b + 1
Sheets("Sheet2").Range("A" & a.Row).Value = b
Sheets("Sheet2").Range("B" & a.Row).Value = Range("a" & a.Row).Value
Next a
b = Empty


Range(Range("F5"), Range("F65536").End(xlUp)).Copy _
Destination:=CopyingTo.End(xlToRight).Offset(0, 1)

If CopyingTo.End(xlToRight).Offset(0, 1).Column > 120 Then
CopyingTo.Offset(0, 1).Delete Shift:=xlToLeft
End If

If ActiveSheet.ChartObjects.Count > 0 Then

ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(z), PlotBy:= _
xlRows
End If
End Sub

[GeorgeUK]

This was the original thread
http://gruss-software.co.uk/forum/viewt ... 4&start=15

If you put this code above your existing code, you should be able to doubleclick one of the horse names and see the chart. Only problem is excel can only display and update one chart at a time so you need to delete the chart to see another one.

The error is because i wasn't paying attention. Didn't realise i had left code relating to charting at the bottom, but it seems this is what you want?
We need to recode my quickfix - The code should look for the horse name in column A of sheet2.
The z that you highlighted is from the missing code that i mention above. If i recall right, it lets the chart know what area to look at when charting.

Sorry about that.

Code: Select all

dim a as range
for each a in CopyingTo
 
sheets("Sheet2").Range("A" & a.row).value = range("a" & a.row).value
sheets("Sheet2").Range("B" & a.row).value = 0.01 '
next a


[Volker0866]

Hi George,
now I must say sorry because I can nomore test the code. Subscripton ended and because of I lost too much money with live-horse-racings with betfair I decided for me to stop live betting / trading with betfair. I want to reenter BA&Betfair in cold season. (Betfair is for sure best bookie but I'm hanging on the computer, loosing money and time goes by.

So that the work is not for the wind, I will publish my work. As I have no own URL adress do you know how to upload a excel sheet?

Volker

[GeorgeUK]

you could try here?
http://www.mytempdir.com/