Power Rankings Auto Update From Web In Excel Spreadsheet

[dljjackson]

I made a NBA power rating spreadsheet which uses each team's ranking in specific statistical categories from teamrankings.com. I'm wondering is it possible for me to somehow program my spreadsheet to automatically update the rankings from the web instead of me having to manually enter them whenever I want to update my power ratings. Below is a copy of my spreadsheet, not sure how to attach it

Team	3 Pointers Made/Gm	Field Goal %	Opp. 3 Pointers Made/Gm	Turnover Margin	Offensive Rebounding	Free Throw Attempts/Game	Opp. Free Throw Attempts/Gm	Points Per Game Difference	Assists Per Game	Opp. Field Goal %	D.I.Z. Score
Los Angeles Clippers	2	3	5	6	21	2	20	6	5	6	105
Oklahoma City Thunder	28	4	11	23	17	5	25	2	12	2	104
Indiana Pacers	21	10	1	16	26	13	3	1	17	1	103
Golden State Warriors	4	2	5	29	13	19	25	7	11	4	103
Miami Heat	10	7	26	3	30	11	17	4	3	10	102
Houston Rockets	16	3	11	30	18	1	12	9	21	6	102
San Antonio Spurs	3	8	5	13	28	30	2	3	2	6	102
Portland Trailblazers	11	11	1	25	4	18	5	5	4	17	100
Phoenix Suns	7	9	1	19	13	11	25	10	29	10	100
Toronto Raptors	12	10	11	7	6	8	12	13	28	17	100
Dallas Mavericks	15	1	11	4	24	27	25	12	8	17	99
Washington Wizards	1	5	11	10	15	22	7	15	7	17	99
Chicago Bulls	13	12	5	27	8	14	3	18	14	4	99
Atlanta Hawks	6	6	11	15	29	20	5	11	1	17	98
New Orleans Pelicans	17	16	11	5	3	15	25	14	9	17	98
Memphis Grizzlies	5	4	11	11	8	25	7	21	15	17	98
Denver Nuggets	24	21	5	20	5	5	12	16	16	6	98
Minnesota Timberwolves	26	26	11	1	2	3	1	7	6	26	98
Charlotte Hornets	29	29	11	8	19	7	7	17	26	2	96
Detroit Pistons	22	13	11	9	1	10	12	20	24	26	96
Cleveland Cavaliers	14	9	26	14	10	21	7	27	27	10	96
Boston Celtics	20	12	1	21	23	27	17	19	30	10	95
Orlando Magic	8	14	11	21	26	22	7	22	19	10	94
Sacramento Kings	23	13	26	11	12	9	17	23	19	26	93
Milwaukee Bucks	18	17	11	16	15	26	20	28	18	10	93
New York Knicks	9	18	11	1	20	29	25	25	21	17	93
Los Angeles Lakers	19	22	11	27	21	17	12	24	13	10	92
Brooklyn Nets	27	18	26	18	25	4	20	26	21	17	91
Utah Jazz	25	11	5	25	10	24	20	29	24	26	90
Philadelphia 76ers	30	29	30	24	7	15	20	30	9	26	87

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[ukmikeb]

Hi

Welcome to MrExcel Forum.

It should be possible to automate the collection of the statistics but the number of webpages to access to arrive at your final sheet would seem to be quite high.

For instance the Turnover Margin figure doesn't seem to be available in the menus (links to specific stats) is it a calculated field?

If you could itemise the source of the column headings it should be possible to create the automated routine.

 

[dljjackson]

Sorry, I don't use teamrankings for my NBA power rankings but I do for my NFL power rankings which does use quite a few different sources and may be much tougher than this task. Luckily, all the stats needed for my NBA power rankings can be found under team statistics at 2014-2015 NBA Stats and League Leaders - National Basketball Association - ESPN.

 

[dljjackson]

My NFL power rankings:

Team	Yards Per Pass	Avg. Time of Poss.	Def. Yards Per Pass	Avg. Starting Field Position	Offensive Red Zone Efficiency	Def. Red Zone Efficiency	Penalty Yards Per Game	Points Per Game Difference	Turnover Margin Per Game	Big Play %	D.I.Z. Score
Green Bay Packers	3	25	10	12	9	6	7	6	3	9	28
Kansas City Chiefs	24	6	6	15	4	2	2	7	19	8	27
Denver Broncos	2	11	1	16	2	25	29	1	8	8	26
Detroit Lions	14	3	3	3	20	5	21	12	11	7	25
Seattle Seahawks	18	16	9	7	19	28	22	10	9	11	24
Cleveland Browns	5	24	7	14	8	3	14	13	4	5	23
Philadelphia Eagles	12	31	12	5	26	19	23	3	25	12	23
Indianapolis Colts	6	1	21	1	16	31	15	4	17	7	23
San Francisco 49ers	20	2	4	9	31	29	20	19	7	10	23
New England Patriots	13	21	11	4	13	18	32	2	1	7	23
Miami Dolphins	28	13	2	6	30	8	3	8	10	6	23
Arizona Cardinals	16	9	28	10	23	14	13	9	2	7	22
Baltimore Ravens	15	15	18	8	21	4	9	6	13	5	22
Houston Texans	9	18	20	20	17	12	10	17	5	7	21
San Diego Chargers	7	7	15	13	11	32	16	16	14	7	21
Dallas Cowboys	8	5	24	24	7	27	5	11	21	7	21
New Orleans Saints	10	8	27	18	10	21	6	14	28	8	21
Cincinnati Bengals	17	30	8	11	18	13	8	20	18	7	20
Buffalo Bills	27	23	5	2	32	16	27	18	6	8	20
New York Giants	22	22	30	17	5	17	4	24	15	7	19
Pittsburgh Steelers	4	4	19	25	25	10	25	15	22	6	18
Washington Redskins	1	17	13	30	15	15	28	23	29	8	18
Chicago Bears	21	14	32	32	6	11	24	28	24	9	17
Atlanta Falcons	11	26	31	21	3	23	19	21	12	5	16
Minnesota Vikings	30	19	14	27	24	26	17	22	16	8	16
Carolina Panthers	25	12	23	22	27	30	12	25	20	8	15
St. Louis Rams	23	20	28	19	12	7	31	29	26	7	15
Tennessee Titans	19	29	16	23	21	20	30	26	23	8	14
Oakland Raiders	31	32	26	31	1	9	11	31	32	7	14
New York Jets	32	10	22	26	29	24	26	27	30	8	12
Jacksonville Jaguars	29	27	25	29	28	1	1	32	31	5	12
Tampa Bay Buccaneers	26	28	29	28	14	22	18	30	27	7	12

<colgroup><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

All of the stats besides "big play %" and "average starting field position" can be found on Team Rankings -- NFL Football Stats on TeamRankings.com .

Big play % can be found at Team Big Play Statistics: 2013 NFL Season

Average starting field position can be found at FOOTBALL OUTSIDERS: Innovative Statistics, Intelligent Analysis | 2014 DRIVE STATS -- it is labelled NET Los/Dr

 

[ukmikeb]

Hi

I've had a closer look at the TeamRankings website and it appears that it uses javascript, such that the data within the web pages can't be accessed through the HTML commands.

I mentioned a way of copying the whole of the webpage text to another user in this thread :-
http://www.mrexcel.com/forum/excel-...wser-displays-not-source-html-into-sheet.html
possibly you could adapt that to your need.

hth