Auto update NBA standings

[harlem9515]

Hi there,

I’m trying to setup an excel sheet where it will update the NBA standings automatically using the “Get External Data” technique from the ESPN website or somewhere else.
How do I type out the formula to use in retrieving the wins and losses?
At the same time, I don’t need the team names to move around as the standings change, I want them to stay in the order I have them in…. So this is the tricky part… Because the data I retrieve from the website WILL move around as the standings change… So, the excel formula have to put that into consideration..

Can someone help me figure this out? I’ve been at this for hours already and can’t figure it out….
Thanks!

[CK76]

I wouldn't recommend "Get External Data" method in this case.

Since most Sports stats site use Script/API to fill the data on the site. You won't able to get at it, without simulating/navigating IE or some other browser from URL.

Of few sites I looked, NBA.com is easiest to scrape.

Use following URL: https://data.nba.net/prod/v1/current...onference.json

This will get you json response with win/loss stats. However, teams are identified by internal ID instead of names...
So you will need to use another URL to find out: https://data.nba.net/prod/v2/2018/teams.json

Use WEBSERVICE function to get response from URL.

Now it's just matter of using text manipulation to extract the desired data.

I stored WEBSERVICE formula for stats in G5 and manually filled team IDs in Column C, Win/Loss has moved to D & E.

Win formula:

Please Login or Register  to view this content.

Loss formula:

Please Login or Register  to view this content.

Only difference is SUBSTITUTE(String,":",Instance) part, and only instance argument is changed.

See attached sample.

Edit: Since you have Excel 2010, you won't have this function available out of the box. I'd add following UDF and replace WEBSERVICE function with WebService2 and save workbook as macro enabled workbook.

Please Login or Register  to view this content.

[harlem9515]

Thanks man! But is there any way I can move this to another workbook? I'm having trouble making this work on another workbook... I seem to have added the UDF module now, and copied your weblink in G5 to the other workbook, but it still just shows #NAME? after I press enter....

[CK76]

Try this one. I've replaced WEBSERVICE with UDF.

Open VBE and you can see where it's stored.