Excel Help ...

[doubleback]

I'm looking for a way that reduces the amount of time it takes to Copy and Paste football results into Excel. Currently, I'm using Paste Special then Text Import Wizard.

I'm sure this procedure could be sped up by going into the Developer Tab and recording a Macro, but don't seem able to get this method to work ... any ideas?

[dude67]

Difficult to say why it doesn't work for you. Where/how does it fail?

Macro recording wizards like the Import Wizard should work just fine, it will produce VBA code setting the various option as you did.

Alternatively, consider downloading your data from a site like http://www.football-data.co.uk/ where the data is available in CSV format.

[Shaun]

What site are you copying the data from please?

[PFY]

You could try using the 'From Web' option which will put tables on a sheet for you and give you the chance to set it to automatically refresh. No real need for any macros then unless you want automate the part which generates the initial web query.

[Wednesday 13]

What site are you copying the data from please?

footballresultsonline.co.uk , you can read it from video above.

You could try using the 'From Web' option

Unfortunatelly it does not work with this site, I tried it yesterday evening when I first saw this thread; you can select that specific table from the site (there is that yellow arrow for that table), but once you import it, there is nothing, empty, "This Web Query returned no data... etc." message.

I am not very keen on those issues, but I suppose reason is that web page uses technique that preserves general URL of pages - URL of start page for "Team's Results" section is https://www.footballresultsonline.co...msResults.aspx ; once you perform your search, like for Man Utd in above example, URL still remains the same, there is nothing in URL related to that specific search, thus Excel is not able to extract the data.

[doubleback]

dude, I must say that I did look at www.football-data.co.uk before and overlooked it. The reason being, was that I wanted to compile data between certain dates that may cover two seasons. Also having to keep updating the database, I thought would be a bit of a bore ... However, I took a second look at it and that may indeed be the solution ... There is no reason why I can't combine two or more season's data together, into one spready ... Likewise, once all the data is combined, extracting the information that I want is quite easy. Overall, I think this way of doing things would be quicker than the way I was doing it ... i.e, having to search for specific teams data between certain dates, copy, paste, paste special, text import wizard, blah, blah, blah ... Now, I can use A-Z sort, highlight the For+ Against values between specific dates that I want and Auto-Sum shows the value at the bottom of the sheet ... Of course, I still have to update the sheet, but that only has to be done once each day ... Cheers ...

As for the original query and recording the Macro, the VBA code that is produced is;

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+x
'
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
End Sub

As you can see, it completely misses the text import wizard ...

[dude67]

Regarding the Import Text Wizard:

You seem to be running Excel 2007. If it's any consolation, I can tell you that Excel 2010 has the same bug in the macro recorder.

You will probably have to code it yourself, using the TextToColumns function.

http://msdn.microsoft.com/en-us/library/bb178160(v=office.12).aspx

[doubleback]

Cheers, dude, that is some consolation as I would have been scratching my head for a good while! I will take a look at that link, one can never stop improving his Excel skills ... it's on my to-do list.

At the moment, I have gone down the football-data route and have combined three seasons data on one sheet. I'm using ...

=SUMIFS(D2:D1131,B2:B1131,"="&G3,A2:A1131,"<"&J15,A2:A1131,">"&J14)

A = Date Column

B = Home Team Column

D = Goals (For) Column

G = Specific Home Team

J = Current Date & Date at the start of the current season.

I'm still working on the sheet, but when it's done I will be able to simply enter the Home and Away sides and the date, then the sheet will produce all the figures that I need, that will in-turn be linked to other cells for number crunching. It will save me a great deal of time indeed ...

[Peter72]

Hi I know this post is a fairly old one - but would like to add that www.football-bet-data.com also provides downloadable football data in Excel. Found it very useful for back testing and checking patetrns