Hi All,
Does anyone know how to automatically (with VBA) update a spreadsheet with the latest football scores.
Paul
Update Spreadsheet with latest scores
-
- 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
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
- 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
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.
- 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
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.
- 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...
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...
-
- 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
- pdupre1961
- Posts: 410
- Joined: Fri Feb 18, 2011 8:01 pm
- Location: Morden, London
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.
-
- 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
If the first weekend sheet with prices could be updated It would be ideal, also is possible to adapt it to tennis?
Many thanks
- pdupre1961
- Posts: 410
- Joined: Fri Feb 18, 2011 8:01 pm
- Location: Morden, London
Tennis - what are you looking for???purpletrader wrote: If the first weekend sheet with prices could be updated It would be ideal, also is possible to adapt it to tennis?
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.
- pdupre1961
- Posts: 410
- Joined: Fri Feb 18, 2011 8:01 pm
- Location: Morden, London
Regards, Paul
You do not have the required permissions to view the files attached to this post.
-
- 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
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
- pdupre1961
- Posts: 410
- Joined: Fri Feb 18, 2011 8:01 pm
- Location: Morden, London
Anyone getting Excel not responding with tonight's Scores spreadsheet?
- 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:
after this code - in last sub:
I thought this was causing "Out of Memory". But, I'm still getting this error.
Paul
Please insert the following code:
Code: Select all
Set objWeb = Nothing
Code: Select all
strHTML = objWeb.responseText
Paul