Holy Grail Total Corners

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield Well try the prem data sheet from football data web site into cell a1 in excel. Format as below. Macro button to start it?

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield Do you look at all leagues co might be easier to pull the zip file from http://www.football-data.co.uk/mmz4281/0910/data.zip unzip and dump to Excel?

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield I use prem to e2. Scotprem. France. Spain. Germany. Italy. I just need this season only. I also have each league in a different work book so a generic code may be easier?

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

I use prem to e2. Scotprem. France. Spain. Germany. Italy. I just need this season only. I also have each league in a different work book so a generic code may be easier?

Not if you're programming!! Create a folder called C:\Footy. Open a new Excel file Press Alt+F11 Insert>Module Paste the code in Press F5 Run the routine DownloadExtractImport You can then loop through all the sheets and do what you need to with filters, finds etc. with whatever additional vba you need to.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long


Public Sub DownloadExtractMerge()
    Application.DisplayAlerts = False
    Dim strFolderName As String
    Dim strFileName As String
    
    strFolderName = "C:\Footy\"
    strFileName = "Data.zip"
    
    Call CreateFolder(strFolderName)
    Call CreateFolder(strFolderName & "Data")
    
    Call DownloadData(strFolderName, strFileName)
    Call SendKeys("%a")
    Call ExtractData(strFolderName, strFileName)
    Call ImportAllFiles(strFolderName & "Data\")
    
    Application.DisplayAlerts = True
End Sub

Private Sub CreateFolder(strFolderName As String)
    Dim fso As Variant
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(strFolderName) Then
        fso.CreateFolder (strFolderName)
    End If
End Sub


Private Sub DownloadData(strFolderName As String, strFileName As String)
    Dim strURL As String
    Dim strMsg As String

    strURL = "http://www.football-data.co.uk/mmz4281/0910/data.zip" 'Change this depending on current season
     
    If URLDownloadToFile(0, strURL, strFolderName & strFileName, 0, 0) = 0 Then
        strMsg = "Downloaded to: " & strFolderName & strFileName
    End If
    Debug.Print strMsg
End Sub

Private Sub ExtractData(strFolderName As String, strFileName As String)
    Dim fso As Object
    Dim oApp As Object

    Set oApp = CreateObject("Shell.Application")
    oApp.Namespace(strFolderName & "Data\").CopyHere oApp.Namespace(strFolderName & strFileName).items

    On Error Resume Next
    Set fso = CreateObject("scripting.filesystemobject")
    fso.deletefolder Environ("Temp") & "\Temporary Directory*", True
End Sub

Private Sub ImportAllFiles(strFolderName As String)
    On Error GoTo ErrTrap
    Dim strFileName As String
    
    strFileName = Dir(strFolderName & "*.csv")
        
    While strFileName  ""
        Debug.Print strFileName
            
        Select Case Replace(strFileName, ".csv", "")
            Case "E0", "E1", "E2", "SC0", "F1", "D1", "I1"
                Sheets.Add after:=Worksheets(Worksheets.Count)
                ActiveSheet.Name = Replace(strFileName, ".csv", "")
                Call GetCsv(strFolderName, strFileName)
            Case "B1", "D2", "F2", "G1", "I2", "N1", "P1", "SC1", "SC2", "SC3", "T1"
                Debug.Print strFileName
            Case Else
                Debug.Print strFileName
        End Select
        strFileName = Dir
    Wend
    
    Exit Sub
ErrTrap:
    If Err.Number = 1004 Then
        Sheets(Replace(strFileName, ".csv", "")).Delete
        Resume
    Else
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
End Sub
Private Sub GetCsv(strFolderName As String, strFileName As String)
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFolderName & strFileName, _
        Destination:=Range("$A$1"))
        .Name = strFileName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield Wow. Cant test it at the moment as not on pc. What exactly does it do? Does it open in one excel sheet?

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

Wow. Cant test it at the moment as not on pc. What exactly does it do? Does it open in one excel sheet?

Imports them all into their own sheet with the sheet name the same as the csv all in about 30 seconds. :dude

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield ale, i get run time error 91 object variable or with block variable not set debugging goes to here: oApp.Namespace(strFolderName & "Data\").CopyHere oApp.Namespace(strFolderName & strFileName).items BUT in the footy folder i have csv files of all of the information....so it looks to have got half way??

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield Weird, works for me mate. ExtractData just dumps the files from the zip into the footy\data folder. Comment out the download, sendkeys and extractdata by adding a ' in front of the text e.g. 'Call DownloadData(strFolderName, strFileName) and then try running the routine again to see if it falls over.

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield sorry ale, no idea what you mean?

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

SPREAD SYSTEM			spread		stake per
date	home	away	corners	bet	corner
29/8/09	Jubilo Iwata	Gamba Osaka	9.00	u	3

FIXED ODDS SYSTEM
A	Jubilo Iwata vs Gamba Osaka, under 10 corners @ 1.83
B	Nagoya Grampus vs Albirex Niigata, over 10 corners @ 2.2
	doubles and singles
	bet £1 on each single of A and B = £2
	bet £1 on each double: AB =£1
	total staked = £2+£1 = £3

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

sorry ale' date=' no idea what you mean?[/quote'] Comment out the code like this using ' 'Call DownloadData(strFolderName, strFileName) 'Call SendKeys("%a") 'Call ExtractData(strFolderName, strFileName) Call ImportAllFiles(strFolderName & "Data\") and rerun the script. This will prevent it from trying to download all the files again and extract them.

[Smokin Joe]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

FIXED ODDS SYSTEM
A	Jubilo Iwata vs Gamba Osaka, under 10 corners @ 1.83
B	Nagoya Grampus vs Albirex Niigata, over 10 corners @ 2.2
	doubles and singles
	bet £1 on each single of A and B = £2
	bet £1 on each double: AB =£1
	total staked = £2+£1 = £3

I agree with both of those bets.

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield cheers joe, that's good to know. i haved added another pick from Germany2 creating a patent. it involves adding one single, 2 doubles and the treble IF YOU HAVE placed the japanese games games already. just click 'patent' if you have not placed the bet yet.

FIXED ODDS SYSTEM
A	Jubilo Iwata vs Gamba Osaka, under 10 corners @ 1.83
B	Nagoya Grampus vs Albirex Niigata, over 10 corners @ 2.2
C	SpVgg Greuther Fürth vs Arminia Bielefeld, under 11 corners @ 1.83
	select 'patent' option in multiples section
	bet £1 on each single of A, B and C = £3
	bet £1 on each double: AB, AC, BC = £3
	bet £1 on the treble: ABC =£1
	total staked = £3+£3+£1 = £7

be quick as kick off in J is 11am.

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

Comment out the code like this using ' 'Call DownloadData(strFolderName, strFileName) 'Call SendKeys("%a") 'Call ExtractData(strFolderName, strFileName) Call ImportAllFiles(strFolderName & "Data\") and rerun the script. This will prevent it from trying to download all the files again and extract them.

tried this. nothing happens at all when i run it?

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield just for the record the patent will count over and instead of the original bet as it was possible to place and was done before kick off. :ok

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

tried this. nothing happens at all when i run it?

OK, manually extract all the csv files from C:\Footy into the C:\Footy\Data folder and try the macro again.

[Kanga]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield Stupid question here for you A1 ....but how do you create a folder C:\Footy ... in vista when i try it say it doesn't like the symbols \ ... i'm missing something really simple here.

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield K - it just means create a folder in the C drive called footy. i deleted all the csv files before i started and they don't appear when i run it.

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield ok - i ran the ORIGINAL macro to get all the csvs back. they are already in the data folder.

[Kanga]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

K - it just means create a folder in the C drive called footy.

M ... Doh ! what a numpty I am .... at times I think I'm ok on the brains front and then i do something really stupid and let myself down :loon

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield Right - create a C:\Footy\Data folder also and re-try!

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

M ... i've tried that and it give me the error ... vista doesn't like folders named with a symbol \ .... ?

The 2 folders are called Footy and Data C:\Footy\Data

[Kanga]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield A1 .... I get a run time error 91 ... object variable with block variable not set. and it debugs to the same places as M's above.

[Kanga]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield I get the above error but I get the data as well .... it creates it's own zipped data folder with all the excel sheets inside :clap

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield The only time I can replicate the error is when I remove the Data folder from the Footy folder. If you comment out the lines as above http://www.punterslounge.com/forum/1460826-post311.html and re run the script does it pull all those csv files into a single Excel workbook? Edit: OK - I've tweaked the script so you don't even need to create the folders plus narrowed the import: "prem to e2. Scotprem. France. Spain. Germany. Italy. " http://www.punterslounge.com/forum/1459865-post304.html

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield WOW! that final solution works a treat! many many thanks a1ehouse - you have skills that i am jealous of! :cheers:beer:nana

[muppet77]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

SPREAD SYSTEM
these bets	DOMESTIC	CL+UEFA	ALL
1	117	15	132	bets
100.0%	50.4%	46.7%	50.0%	wins
0.0%	4.3%	0.0%	3.8%	level
0.0%	45.3%	53.3%	46.2%	losses
6.00	579.90	108.75	688.65	staked
100%	26%	14%	24%	yield
6.00	150.40	15.55	165.95	profit
SPREAD SYSTEM			spread		stake per
date	home	away	corners	bet	corner	corners	profit
29/8/09	Jubilo Iwata	GambaOsaka	9.00	u	3	7	6.00

[muppet77]

Re: Total Corners: spreads = 132 bets +24% yield, fixed odds = 90 bets +22% yield

FIXED ODDS SYSTEM
A	RANDERS FC vs ESBJERG, under 10, @2.2
B	HB KOGE vs SONDERJYSKE, under 10, @2.1
C	Viking Stavanger vs Sandefjord, over 10, @2
D	Kalmar FF vs Hammarby, under 11, @1.83
	select 'lucky 15' option in multiples section
	bet £1 on each single of A, B, C and D = £4
	bet £1 on each double: AB, AC, AD, BC, BD, CD = £6
	bet £1 on each treble: ABC, ABD, BCD, ACD = £4
	bet £1 on the 4 fold: ABCD =£1
	total staked = £4+£6+£4+£1 = £15

FIXED ODDS SYSTEM
A	Halmstads BK vs BK Hacken, under 11, @1.83
B	IFK Goteborg vs IF Elfsborg, under 11, @1.83
C	Feyenoord vs Twente, under 10, @2
D	Los Angeles vs Chivas USA, under 9, @2
	select 'lucky 15' option in multiples section
	bet £1 on each single of A, B, C and D = £4
	bet £1 on each double: AB, AC, AD, BC, BD, CD = £6
	bet £1 on each treble: ABC, ABD, BCD, ACD = £4
	bet £1 on the 4 fold: ABCD =£1
	total staked = £4+£6+£4+£1 = £15

[a1ehouse]

Re: Total Corners: spreads = 131 bets +23% yield, fixed odds = 90 bets +22% yield

WOW! that final solution works a treat! many many thanks a1ehouse - you have skills that i am jealous of! :cheers:beer:nana

Pleasure mate, give and take - glad it works now! Don't forget, if you're doing routine things manually in Excel, you can also automate it. If you need owt else, lemme know. :ok

[muppet77]

Re: Total Corners: spreads = 132 bets +24% yield, fixed odds = 90 bets +22% yield Cheers ale. The only massively time consuming thing that i do for this is get data from web sites for the lesser leagues. I once built a scrape to get the historical data to build the system but it differed for each league and i had to work out and input all the different urls for each match. I need to update the stats weekly but this if easy for the major leagues on the football data website but the minor leagues (where i reckon the profit it) are the pain.