How do I get Data from external web sites into Excel

[methodman]

Can anybody help me.

I have limited excel abilities but have got to grips with the basics and simple macros but am stuck with the next step.

I would like to extract forecast prices from the Racing Post and Sporting Life web sites and load them into my excel spreadsheet. I can extract them manually (cut & paste and then use excel to reformat them) but have no idea how to automate the collection of data from the web.

Has anybody got any experience of this and ideally any code they are prepared to share. I have been looking around the web and anything i've found on screen scrapping just goes clear over my head.

John

[Captain Sensible]

Are you trying to link them against Betfair odds in real time ? I had a spreadsheet doing that automatically but had to also use php to parse the data and select the right sporting life page using the data from cell A1 so unless you've access to some webspace with php or php on your pc it won't be much use

If you're just doing each race manually have a look at web queries on excel but even that becomes a pain with the formatting of the odds. I guess one of the VB bods on here might have the answer though

[thunderfoot]

As your request is for 'forecast prices' and not current live prices then doing as CS has suggested (using EXCELS Data/Import External Data/New Web Query) should do the trick. Note: on later versions of EXCEL it is easier, as the little yellow boxed arrows gives the option of only selecting the tables within the webpage that you want .... on earlier versions of EXCEL you had to download the whole damn lot!!!!
When the window pane pops-up there is also the option of dealing with the English way of showing the odds i.e. 2/1, 3/1, 7/2 etc. etc. by disabling date recognition, under Options.

[throwmeadisc]

I have found that trying to web query RP is a problem because you need to log in first and I haven't been able to get around that yet..... although I haven't tried very hard to find a solution. Has anyone??

I have a sheet that processes the betting forecasts from a RP data extract that I wrote myself. It's a bit rough around the edges but works quite nicely.

Extracting the data from the RP site isn't that easy as the format is not consistent all the time and the betting forecasts themselves are not always very uniformed. But I managed to get through that with some fairly complicated formula.

I haven't used it for a while because I never found a good system / method in using that data and got sidetracked with other things, but if you have then I'll be interested in what you are doing.

Al

[methodman]

Hi Guys
Thanks for the replies.

What I want to do is compare actual prices with forecast prices just before the off so that I can automate a system. BA would then trigger the bets if certain criteria met.
At this stage I don't know if it will be profitable, so don't want to pay for a programmer. I was hoping I could get all the forecast prices into my BA spreadsheet, so I could output a file showing the prices at off time, original forecast prices, results of some calculations and the selections that would have been made.
I could then easily see if it would be profitable or not.

I'm running Excel 2000 and havn't had got the web query to work yet so guess i'll just have to have another look at it.


Thanks
John

[alrodopial]

I have found that trying to web query RP is a problem because you need to log in first and I haven't been able to get around that yet..... although I haven't tried very hard to find a solution. Has anyone??

I have a sheet that processes the betting forecasts from a RP data extract that I wrote myself. It's a bit rough around the edges but works quite nicely.

Extracting the data from the RP site isn't that easy as the format is not consistent all the time and the betting forecasts themselves are not always very uniformed. But I managed to get through that with some fairly complicated formula.

I haven't used it for a while because I never found a good system / method in using that data and got sidetracked with other things, but if you have then I'll be interested in what you are doing.

Al

Hi, for the logging problem you have to log in manually first (make a new querry, log in and then without downloading data close the querry window). Excel remembers that you have loged.
The easiest way for the forcast prices is to download the page (for tomorrow first race in Muss) like this:
Races' URL & "&card_choice=pedigree&ratings=rpr"
url: http://www.racingpost.co.uk/horses/card ... atings=rpr

Note that RP names sometimes are differrent than BF names(BF doesnt use apostrophs in names-and few other differrences) so you have to find a way to match the names.
Hope i was helpfull.
Alexandros

[Ian]

You can't be serious about using the Sporting Life forecast. They come up with the odds a few minutes after declaration time so can't be putting any thought into them at all. I believe the RP forecast is only the opinion of the Spotlight writer.

[throwmeadisc]

Thanks Alexandros.

I have already looked into doing it that was and it wasn't something that was very automated and that wasn't the end result I was after. I might look into an embedded webbrowser or something and a keep alive thingy, maybe that would work. But i don't use the forecasts much anyway so it's not on my priority list.

Thanks for your suggestion.

[mellenec]

I have found that trying to web query RP is a problem because you need to log in first and I haven't been able to get around that yet..... although I haven't tried very hard to find a solution. Has anyone??

I have a sheet that processes the betting forecasts from a RP data extract that I wrote myself. It's a bit rough around the edges but works quite nicely.

Extracting the data from the RP site isn't that easy as the format is not consistent all the time and the betting forecasts themselves are not always very uniformed. But I managed to get through that with some fairly complicated formula.

I haven't used it for a while because I never found a good system / method in using that data and got sidetracked with other things, but if you have then I'll be interested in what you are doing.

Al

Hi, for the logging problem you have to log in manually first (make a new querry, log in and then without downloading data close the querry window). Excel remembers that you have loged.
The easiest way for the forcast prices is to download the page (for tomorrow first race in Muss) like this:
Races' URL & "&card_choice=pedigree&ratings=rpr"
url: http://www.racingpost.co.uk/horses/card ... atings=rpr

Note that RP names sometimes are differrent than BF names(BF doesnt use apostrophs in names-and few other differrences) so you have to find a way to match the names.
Hope i was helpfull.
Alexandros

below the code I use to login to theRP site before web querying some of the tables I am interested in :
Set qt = Worksheets("Feuil1").QueryTables.Add(Connection:="URL;http://www.racingpost.co.uk", Destination:=Range("A1"))
Sheets("Feuil1").QueryTables(1).FillAdjacentFormulas = True
Application.DisplayAlerts = False
' login
Acode = "URL;https://reg.racingpost.co.uk/cde/login_iframe.sd?in_un=aprxracing&in_pw=uqwr93ew&process=IN&PARGS="

Worksheets("Feuil1").QueryTables(1).Connection = Acode
On Error Resume Next
Worksheets("Feuil1").QueryTables(1).Refresh BackgroundQuery:=False