jamiepullen
01-28-2008, 02:56 PM
Hi, I would REALLY appreciate some help. I embarked on a project that I cannot do, but on the other hand I cannot put it down now and need someone to put me out of my misery!! When I started trying to teach myself excel 6 months ago, I was looking for data to generally play with and work out excel functions. My friend at work gave me a file of horse race results downloaded from betfair that was huge and I learned a lot of excel functions, even though I have no real interest in horse racing. I am now the first person they come to at work for help with excel, all self taught. I stumbled across this spreadsheet on the developer forum on betfair and was blown away by what excel can actually do!!! I had no idea things like this were remotely possible.
The sheet would be able to automate what my friend does on betfair and so stupidly I said I would have a crack at setting it up for him as a project. Now I do not know where to start. Problem is that on the excel side, I can muddle my way through, but on the V b A side, I may as well be reading Russian!! I got several books from the library, but I still have no clue!!
The frustrating thing is it practically does what i need it to, except a few small details!
What I want to do is be able to do is log on, select ?next horse race? from the browser pop up, populate the worksheet with it and then leave it to follow the next horse race all day. This it already does, BUT the data to the right does not update when it jumps to the next race and stays as it did when the sheet was first populated. Ie, columns E onwards do not update when it changes race.
Also, I wanted the sheet created when the track market button is pressed to have the name ?current race?. At the moment, it calls the sheet whatever the race name is at the time of creating it.
I may be wrong, but I am guessing that this is quite simple for a person in the know, as most of the sheet is already done and (I guess) it just needs a bit of code changed.
Please help me as I am too stubborn to admit defeat and really do not want to go back with my tail between my legs and tell him I cannot do it!!
If it is a huge task, at least give some advice as to where I could read up and what I would need to change.
the code that i think populates the sheet is:
Const BetfairLiteURL = "BETFAIR LITE LINK REMOVED, FORUM WILL NOT LET ME USE LINKS!!"
Const DefaultServlet = "Default.do?s="
Const LocaleInfo = "000009z"
Const metaInfoColumn = 6
Const cntRunnerNameRow = 6
Const cntRunnerIdRow = 7
Const cntAsianHandicapIdRow = 8
Const cntOddsRow = 9
Const cntAmountRow = 10
Const cntActionRow = 11
Const cntPlaceBetYN = 12
Const cntBetId = 13
Const cntBetFormula = 14
Public TrackerTimerID As Long
Private Sub btnSnapshotMarket_Click()
MarketMonitor False
End Sub
'''''''
' Triggers on navigation completion
'Private Sub BetfairBrowser_NavigateComplete2(ByVal pDisp As Object, URL As Variant)
'
' If Right(URL, 1) = "=" Then
' UserCookie = Mid(URL, Len(URL) - 43, 43) ' Gets everything up to "="
' End If
'End Sub
Private Sub BetfairBrowser_DocumentComplete(ByVal pDisp As Object, URL As Variant)
If InStr(BetfairBrowser.LocationURL, "?") > 0 Then
pageURL = Left$(BetfairBrowser.LocationURL, InStr(BetfairBrowser.LocationURL, "?") - 1)
sessionID = Right$(BetfairBrowser.LocationURL, Len(BetfairBrowser.LocationURL) - InStr(BetfairBrowser.LocationURL, "?"))
If Len(sessionID) >= 52 Then UserCookie = Mid(sessionID, 10, 43) Else UserCookie = ""
' MsgBox UserCookie
End If
End Sub
Private Sub BetfairBrowser_StatusTextChange(ByVal Text As String)
End Sub
Private Sub btnTrackMarket_Click()
MarketMonitor
End Sub
Private Sub CleanupWorksheetsButton_Click()
cleanupWorksheets
End Sub
Private Sub UserForm_Initialize()
If UserCookie = "" Then
If pageURL = "" Then pageURL = "BETFAIR LITE LINK REMOVED, FORUM WILL NOT LET ME USE LINKS!!"BetfairBrowser.Navigate pageURL + "?" + sessionID
Else
Debug.Print "Auto relogging in user..."
BetfairBrowser.Navigate pageURL + "?" + sessionID
' BetfairBrowser.Navigate BetfairLiteURL & DefaultServlet & LocaleInfo & UserCookie & "="
End If
End Sub
Private Function CleanMarketName(aMarketName)
CleanMarketName = Replace(aMarketName, ":", "h")
End Function
Private Sub MarketMonitor()
' Grab the Market name
Dim MarketName As String
MarketName = getMarketName()
On Error Resume Next
Set worksheetCheck = Worksheets(CleanMarketName(MarketName))
If Err.Number = 0 Then
MsgBox "You are already tracking this market"
Exit Sub
End If
If UserCookie = "" Then
MsgBox "You must be logged in to track this market"
Exit Sub
End If
Dim newWorksheet As Worksheet
Sheets("Market - Master").Select
Sheets("Market - Master").Copy After:=Sheets(4)
Set newWorksheet = Sheets("Market - Master (2)")
newWorksheet.Name = CleanMarketName(MarketName)
Dim qryTable As QueryTable
Set qryTable = ActiveSheet.QueryTables.Add( _
Connection:="URL;" & BetfairBrowser.LocationURL, _
Destination:=Range("A1") _
)
With qryTable
.Name = MarketName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
' .RefreshPeriod = txtRefreshInterval.Text
.RefreshPeriod = 15
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Set qryTableListener = New ClsModQt
qryTableListener.InitQueryEvent QT:=qryTable
QueryTableListeners.Add qryTableListener
populateMetaInformation newWorksheet
addTimer txtRefreshInterval.Text
End Sub
Private Function getMarketName()
Dim strDocument As String
strDocument = BetfairBrowser.Document.body.innerHTML
Dim marketNameRegExp As New RegExp
marketNameRegExp.Pattern = "<strong>(.*)</strong>(.nbsp.. In-play .)*<br>"
marketNameRegExp.Global = True
marketNameRegExp.MultiLine = True
marketNameRegExp.IgnoreCase = True
Dim reMatches As MatchCollection
Set reMatches = marketNameRegExp.Execute(strDocument)
getMarketName = reMatches(0).SubMatches(0)
End Function
Private Sub populateMetaInformation(metaInformationWorksheet As Worksheet)
Dim strDocument As String
strDocument = BetfairBrowser.Document.body.innerHTML
' Get the market/userid token
Dim MarketID As String
Dim URL As String
URL = BetfairBrowser.LocationURL
oddsPanelStart = InStr(strDocument, "Oddspanel.do?s=")
oddsPanelEnd = InStr(oddsPanelStart + Len("Oddspanel.do?s="), strDocument, "=") - Len("Oddspanel.do?s=")
marketSessionID = Mid(strDocument, oddsPanelStart + Len("Oddspanel.do?s="), oddsPanelEnd - oddsPanelStart)
metaInformationWorksheet.Cells(1, metaInfoColumn) = "MarketSessionId"
metaInformationWorksheet.Cells(1, metaInfoColumn + 1) = marketSessionID
metaInformationWorksheet.Cells(2, metaInfoColumn) = "MarketID"
metaInformationWorksheet.Cells(2, metaInfoColumn + 1) = getMarketID(marketSessionID)
Dim RunnerInfoRegExp As New RegExp
RunnerInfoRegExp.Pattern = "<tr>\s*<TD>([\w \.]+)<"
RunnerInfoRegExp.Global = True
RunnerInfoRegExp.MultiLine = True
RunnerInfoRegExp.IgnoreCase = True
Dim runnerInfoMatches As MatchCollection
Set runnerInfoMatches = RunnerInfoRegExp.Execute(strDocument)
RowCount = 2
ColumnCount = 0
metaInformationWorksheet.Cells(cntRunnerNameRow, metaInfoColumn - 1) = "Selection Name"
Dim runnerMatch As Match
For Each runnerMatch In runnerInfoMatches
metaInformationWorksheet.Cells(cntRunnerNameRow, metaInfoColumn + ColumnCount) = runnerMatch.SubMatches(0)
ColumnCount = ColumnCount + 1
Next
' Fill in Odds etc
RowCount = 2
ColumnCount = 0
metaInformationWorksheet.Cells(cntRunnerIdRow, metaInfoColumn - 1) = "Selection ID"
metaInformationWorksheet.Cells(cntAsianHandicapIdRow, metaInfoColumn - 1) = "Asian Handicap ID"
metaInformationWorksheet.Cells(cntOddsRow, metaInfoColumn - 1) = "Odds"
metaInformationWorksheet.Cells(cntAmountRow, metaInfoColumn - 1) = "Amount"
metaInformationWorksheet.Cells(cntActionRow, metaInfoColumn - 1) = "Action"
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn - 1) = "Place Bet (Y/N)"
metaInformationWorksheet.Cells(cntBetId, metaInfoColumn - 1) = "Bet ID"
Dim runnerIdMatches As MatchCollection
RunnerInfoRegExp.Pattern = "si=([0-9]+)"
Set runnerIdMatches = RunnerInfoRegExp.Execute(strDocument)
lastMatch = "" ' For deduping IDs
For Each idMatch In runnerIdMatches
If lastMatch <> idMatch.SubMatches(0) Then
metaInformationWorksheet.Cells(cntRunnerIdRow, metaInfoColumn + ColumnCount) = idMatch.SubMatches(0)
metaInformationWorksheet.Cells(cntAsianHandicapIdRow, metaInfoColumn + ColumnCount) = 0
metaInformationWorksheet.Cells(cntOddsRow, metaInfoColumn + ColumnCount).Formula = "=TRIM(LEFT(B" & (2 + ColumnCount) & ", FIND("" "", B" & (2 + ColumnCount) & ")))"
metaInformationWorksheet.Cells(cntAmountRow, metaInfoColumn + ColumnCount) = 2
metaInformationWorksheet.Cells(cntActionRow, metaInfoColumn + ColumnCount) = "back"
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.Delete
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes,No"
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.IgnoreBlank = True
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.InCellDropdown = True
metaInformationWorksheet.Cells(cntBetId, metaInfoColumn + ColumnCount) = idMatch.SubMatches(0) & ".1"
metaInformationWorksheet.Cells(cntBetFormula, metaInfoColumn + ColumnCount).Formula = "=IF(R12C=""Yes"", fnSubmitBet(R13C, R9C, R10C, R8C, R2C7, R7C, R11C, R1C7), ""No Bet"")"
ColumnCount = ColumnCount + 1
End If
lastMatch = idMatch.SubMatches(0)
Next
End Sub
Function getMarketID(marketSessionID)
getMarketID = Mid(marketSessionID, 7, InStr(7, marketSessionID, "x") - 7)
End Function
Thanks,
Jamie
Jamie-pullen@tiscali.co.uk (Jamie-pullen@tiscali.co.uk)
The sheet would be able to automate what my friend does on betfair and so stupidly I said I would have a crack at setting it up for him as a project. Now I do not know where to start. Problem is that on the excel side, I can muddle my way through, but on the V b A side, I may as well be reading Russian!! I got several books from the library, but I still have no clue!!
The frustrating thing is it practically does what i need it to, except a few small details!
What I want to do is be able to do is log on, select ?next horse race? from the browser pop up, populate the worksheet with it and then leave it to follow the next horse race all day. This it already does, BUT the data to the right does not update when it jumps to the next race and stays as it did when the sheet was first populated. Ie, columns E onwards do not update when it changes race.
Also, I wanted the sheet created when the track market button is pressed to have the name ?current race?. At the moment, it calls the sheet whatever the race name is at the time of creating it.
I may be wrong, but I am guessing that this is quite simple for a person in the know, as most of the sheet is already done and (I guess) it just needs a bit of code changed.
Please help me as I am too stubborn to admit defeat and really do not want to go back with my tail between my legs and tell him I cannot do it!!
If it is a huge task, at least give some advice as to where I could read up and what I would need to change.
the code that i think populates the sheet is:
Const BetfairLiteURL = "BETFAIR LITE LINK REMOVED, FORUM WILL NOT LET ME USE LINKS!!"
Const DefaultServlet = "Default.do?s="
Const LocaleInfo = "000009z"
Const metaInfoColumn = 6
Const cntRunnerNameRow = 6
Const cntRunnerIdRow = 7
Const cntAsianHandicapIdRow = 8
Const cntOddsRow = 9
Const cntAmountRow = 10
Const cntActionRow = 11
Const cntPlaceBetYN = 12
Const cntBetId = 13
Const cntBetFormula = 14
Public TrackerTimerID As Long
Private Sub btnSnapshotMarket_Click()
MarketMonitor False
End Sub
'''''''
' Triggers on navigation completion
'Private Sub BetfairBrowser_NavigateComplete2(ByVal pDisp As Object, URL As Variant)
'
' If Right(URL, 1) = "=" Then
' UserCookie = Mid(URL, Len(URL) - 43, 43) ' Gets everything up to "="
' End If
'End Sub
Private Sub BetfairBrowser_DocumentComplete(ByVal pDisp As Object, URL As Variant)
If InStr(BetfairBrowser.LocationURL, "?") > 0 Then
pageURL = Left$(BetfairBrowser.LocationURL, InStr(BetfairBrowser.LocationURL, "?") - 1)
sessionID = Right$(BetfairBrowser.LocationURL, Len(BetfairBrowser.LocationURL) - InStr(BetfairBrowser.LocationURL, "?"))
If Len(sessionID) >= 52 Then UserCookie = Mid(sessionID, 10, 43) Else UserCookie = ""
' MsgBox UserCookie
End If
End Sub
Private Sub BetfairBrowser_StatusTextChange(ByVal Text As String)
End Sub
Private Sub btnTrackMarket_Click()
MarketMonitor
End Sub
Private Sub CleanupWorksheetsButton_Click()
cleanupWorksheets
End Sub
Private Sub UserForm_Initialize()
If UserCookie = "" Then
If pageURL = "" Then pageURL = "BETFAIR LITE LINK REMOVED, FORUM WILL NOT LET ME USE LINKS!!"BetfairBrowser.Navigate pageURL + "?" + sessionID
Else
Debug.Print "Auto relogging in user..."
BetfairBrowser.Navigate pageURL + "?" + sessionID
' BetfairBrowser.Navigate BetfairLiteURL & DefaultServlet & LocaleInfo & UserCookie & "="
End If
End Sub
Private Function CleanMarketName(aMarketName)
CleanMarketName = Replace(aMarketName, ":", "h")
End Function
Private Sub MarketMonitor()
' Grab the Market name
Dim MarketName As String
MarketName = getMarketName()
On Error Resume Next
Set worksheetCheck = Worksheets(CleanMarketName(MarketName))
If Err.Number = 0 Then
MsgBox "You are already tracking this market"
Exit Sub
End If
If UserCookie = "" Then
MsgBox "You must be logged in to track this market"
Exit Sub
End If
Dim newWorksheet As Worksheet
Sheets("Market - Master").Select
Sheets("Market - Master").Copy After:=Sheets(4)
Set newWorksheet = Sheets("Market - Master (2)")
newWorksheet.Name = CleanMarketName(MarketName)
Dim qryTable As QueryTable
Set qryTable = ActiveSheet.QueryTables.Add( _
Connection:="URL;" & BetfairBrowser.LocationURL, _
Destination:=Range("A1") _
)
With qryTable
.Name = MarketName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
' .RefreshPeriod = txtRefreshInterval.Text
.RefreshPeriod = 15
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Set qryTableListener = New ClsModQt
qryTableListener.InitQueryEvent QT:=qryTable
QueryTableListeners.Add qryTableListener
populateMetaInformation newWorksheet
addTimer txtRefreshInterval.Text
End Sub
Private Function getMarketName()
Dim strDocument As String
strDocument = BetfairBrowser.Document.body.innerHTML
Dim marketNameRegExp As New RegExp
marketNameRegExp.Pattern = "<strong>(.*)</strong>(.nbsp.. In-play .)*<br>"
marketNameRegExp.Global = True
marketNameRegExp.MultiLine = True
marketNameRegExp.IgnoreCase = True
Dim reMatches As MatchCollection
Set reMatches = marketNameRegExp.Execute(strDocument)
getMarketName = reMatches(0).SubMatches(0)
End Function
Private Sub populateMetaInformation(metaInformationWorksheet As Worksheet)
Dim strDocument As String
strDocument = BetfairBrowser.Document.body.innerHTML
' Get the market/userid token
Dim MarketID As String
Dim URL As String
URL = BetfairBrowser.LocationURL
oddsPanelStart = InStr(strDocument, "Oddspanel.do?s=")
oddsPanelEnd = InStr(oddsPanelStart + Len("Oddspanel.do?s="), strDocument, "=") - Len("Oddspanel.do?s=")
marketSessionID = Mid(strDocument, oddsPanelStart + Len("Oddspanel.do?s="), oddsPanelEnd - oddsPanelStart)
metaInformationWorksheet.Cells(1, metaInfoColumn) = "MarketSessionId"
metaInformationWorksheet.Cells(1, metaInfoColumn + 1) = marketSessionID
metaInformationWorksheet.Cells(2, metaInfoColumn) = "MarketID"
metaInformationWorksheet.Cells(2, metaInfoColumn + 1) = getMarketID(marketSessionID)
Dim RunnerInfoRegExp As New RegExp
RunnerInfoRegExp.Pattern = "<tr>\s*<TD>([\w \.]+)<"
RunnerInfoRegExp.Global = True
RunnerInfoRegExp.MultiLine = True
RunnerInfoRegExp.IgnoreCase = True
Dim runnerInfoMatches As MatchCollection
Set runnerInfoMatches = RunnerInfoRegExp.Execute(strDocument)
RowCount = 2
ColumnCount = 0
metaInformationWorksheet.Cells(cntRunnerNameRow, metaInfoColumn - 1) = "Selection Name"
Dim runnerMatch As Match
For Each runnerMatch In runnerInfoMatches
metaInformationWorksheet.Cells(cntRunnerNameRow, metaInfoColumn + ColumnCount) = runnerMatch.SubMatches(0)
ColumnCount = ColumnCount + 1
Next
' Fill in Odds etc
RowCount = 2
ColumnCount = 0
metaInformationWorksheet.Cells(cntRunnerIdRow, metaInfoColumn - 1) = "Selection ID"
metaInformationWorksheet.Cells(cntAsianHandicapIdRow, metaInfoColumn - 1) = "Asian Handicap ID"
metaInformationWorksheet.Cells(cntOddsRow, metaInfoColumn - 1) = "Odds"
metaInformationWorksheet.Cells(cntAmountRow, metaInfoColumn - 1) = "Amount"
metaInformationWorksheet.Cells(cntActionRow, metaInfoColumn - 1) = "Action"
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn - 1) = "Place Bet (Y/N)"
metaInformationWorksheet.Cells(cntBetId, metaInfoColumn - 1) = "Bet ID"
Dim runnerIdMatches As MatchCollection
RunnerInfoRegExp.Pattern = "si=([0-9]+)"
Set runnerIdMatches = RunnerInfoRegExp.Execute(strDocument)
lastMatch = "" ' For deduping IDs
For Each idMatch In runnerIdMatches
If lastMatch <> idMatch.SubMatches(0) Then
metaInformationWorksheet.Cells(cntRunnerIdRow, metaInfoColumn + ColumnCount) = idMatch.SubMatches(0)
metaInformationWorksheet.Cells(cntAsianHandicapIdRow, metaInfoColumn + ColumnCount) = 0
metaInformationWorksheet.Cells(cntOddsRow, metaInfoColumn + ColumnCount).Formula = "=TRIM(LEFT(B" & (2 + ColumnCount) & ", FIND("" "", B" & (2 + ColumnCount) & ")))"
metaInformationWorksheet.Cells(cntAmountRow, metaInfoColumn + ColumnCount) = 2
metaInformationWorksheet.Cells(cntActionRow, metaInfoColumn + ColumnCount) = "back"
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.Delete
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes,No"
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.IgnoreBlank = True
metaInformationWorksheet.Cells(cntPlaceBetYN, metaInfoColumn + ColumnCount).Validation.InCellDropdown = True
metaInformationWorksheet.Cells(cntBetId, metaInfoColumn + ColumnCount) = idMatch.SubMatches(0) & ".1"
metaInformationWorksheet.Cells(cntBetFormula, metaInfoColumn + ColumnCount).Formula = "=IF(R12C=""Yes"", fnSubmitBet(R13C, R9C, R10C, R8C, R2C7, R7C, R11C, R1C7), ""No Bet"")"
ColumnCount = ColumnCount + 1
End If
lastMatch = idMatch.SubMatches(0)
Next
End Sub
Function getMarketID(marketSessionID)
getMarketID = Mid(marketSessionID, 7, InStr(7, marketSessionID, "x") - 7)
End Function
Thanks,
Jamie
Jamie-pullen@tiscali.co.uk (Jamie-pullen@tiscali.co.uk)