Update Spreadsheet with latest scores

Example spreadsheets and comments on example spreadsheets.
Post Reply
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Hi All,

Does anyone know how to automatically (with VBA) update a spreadsheet with the latest football scores.

Paul
mikelenard
Posts: 33
Joined: Fri Apr 24, 2009 7:12 pm

Through VBA I'm not sure but you could record a Macro and do it that way.

I use Excel 2010. Go to the Data tab and create an external connection "From Web". When the Internet box opens up go to "http://www.xscores.net/soccer/soccer.js ... =sportData" where you'll see the live results. Select the small yellow icon with a tiny arrow in it to the left of the header of the main live scores table, a tick will appear. Then in the bottom right hand corner select "Import". The window will close and Excel will present you with a table updated when you click on "Refresh All". To refresh quicker go through VBA, or every minute go to "Properties" and select the box "Refresh every" and put in 1 minute.

Hope that helps.

Mike
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Hi Mike,

Thanks for that - I tried something similar with another website.

Anyway, I have since written some VBA which retrieves the latest scores. However, only once. When I click the button after that I doesn't work.

The attached spreadsheet was used yesterday in a Bet Angel workbook. Maybe, Bet Angel can explain why the button only works once per session....

Paul
You do not have the required permissions to view the files attached to this post.
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Here is my latest scores file.

Please can someone check live...now.

Update scores - test by clearing scores when goal is scored, update again.

Doesn't work on my PC. I have to save, quit excel, load again before it works - what is my problem.

Paul
You do not have the required permissions to view the files attached to this post.
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

OK, if anyone is interested - I have solved the refresh problem.

I have to remove the webpage url from cache - which allows a refresh. So, after a big change in price of say the draw odds I can now force a refresh the score from that football match to keep the scores in the spreadsheet up to date. I'll leave the manual refresh button as well.

If there is a requirement for my solution, then please post here and I will upload another spreadsheet with user guide...
purpletrader
Posts: 12
Joined: Wed Apr 15, 2009 5:16 pm

I would love to try the new sheet, been trying something similar with vba but my brain exploded :o
Innertube
Posts: 215
Joined: Mon Mar 14, 2011 9:18 am

I'd be really interested as well.
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Scores_18-20Oct2011.xls
Here is another example for this week's midweek matches (includes Champions League, Europa League and English League Matches), with user guide. Manual update button - I will post more examples later, and also update this one with match IDs when available.

Always open to suggestions that other Bet Angel users may require...
You do not have the required permissions to view the files attached to this post.
purpletrader
Posts: 12
Joined: Wed Apr 15, 2009 5:16 pm

Thanks pdupre

If the first weekend sheet with prices could be updated It would be ideal, also is possible to adapt it to tennis?

Many thanks
jcp7743
Posts: 13
Joined: Tue Jan 26, 2010 10:32 pm

Good work - this could help me with automated strategies that require score lines. Thanks
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

purpletrader wrote: If the first weekend sheet with prices could be updated It would be ideal, also is possible to adapt it to tennis?
Tennis - what are you looking for???
BetAngel_WeekendFootball-1022.xls
Ok, working on this - attached is spreadsheet with manual update - auto update code to be tested this week.

This is example only - formulas set up for 5 Premier League matches only. With 12 Bet Angel worksheets and 1 Correct Scores worksheet.
You do not have the required permissions to view the files attached to this post.
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Scores_18-20Oct2011.xls
Another spreadsheet for Midweek matches.

Regards, Paul
You do not have the required permissions to view the files attached to this post.
purpletrader
Posts: 12
Joined: Wed Apr 15, 2009 5:16 pm

For tennis it would be similar to the weekend sheet with prices. It would need to work off a BA sheet that has today's tennis matches (I use Guardian to load all the matches).
Eventually I am looking to either have an excel alert or better still a trigger when a match reaches a certain score and/or price.

Thanks for the sheets uploaded, it shows what is possible with excel and BA but sadly I struggle to get to grips with it.

Many Thanks
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Anyone getting Excel not responding with tonight's Scores spreadsheet?
User avatar
pdupre1961
Posts: 410
Joined: Fri Feb 18, 2011 8:01 pm
Location: Morden, London

Seems I changed the code and missed out a vital line.

Please insert the following code:

Code: Select all

    Set objWeb = Nothing
after this code - in last sub:

Code: Select all

    strHTML = objWeb.responseText
I thought this was causing "Out of Memory". But, I'm still getting this error.

Paul
Post Reply

Return to “Bet Angel - Example spreadsheets”