Grabbing Historical NBA Data

[lordfoo]

I had nothing better to do so I figured I'd write up some code. If you want to input NBA data from 1993-1994 to 2005-2006 into Excel, just do this:


1. Open a new blank Excel workbook. Name the first sheet "1993-1994".


2. Put the following headers in:

A1: "Date"
B1: "Away"
C1: "Home"
D1: "Home Line"
E1: "Total"
F1: "Away Score"
G1: "Home Score"

(Change the column widths and alignments if you prefer)


3. Insert a web query for the 1993-1994 data.

a) Select the N2 cell.

b) Select Data | Import External Data | New Web Query.

c) In the address bar, put

http://www.goldsheet.com/historic/93nbalog.html

d) Select "Go". When the page loads, select the arrow next to the first set of game listings.

This should be the third arrow on the page, and just above the "ATLANTA HAWKS" line. Now all of the games should be highlighted, and the arrow should be a check mark.

e) Select "Import". Select "Properties..." and clear the "Enable background refresh" checkbox.

Select OK.

f) Select OK. After a few seconds, the game data should appear in the sheet.


4. Create the macro.

a) Open the Visual Basic editor (ALT + F11).

b) In the left-hand pane, select "Module1" under "Modules". If "Module1" isn't there, right-click on "Microsoft Excel Object" and select Insert | Module. Now double-click on "Module1".

c) In the blank page that appears, insert the macro code [to be posted in a followup].


5. Insert a control button for the macro.

a) Go back to the Excel window. Select View | Toolbars | Forms.

b) The forms toolbar should appear. Select the button (right column, second from top). Draw a button in a convenient spot. I used cells H1-J1.

c) After you're finished drawing the button, select the "getGames" macro in the macro window that appears.

d) You can rename the caption of the button if you want to.


6. Copy the worksheets.

a) Right-click on the worksheet tab on the bottom of the Excel window. Select "Move or Copy".

Select the "create a copy" checkbox. Select OK.

b) Repeat step (6a) 11 more times.

c) Rename each sheet using the convention "YEAR1-YEAR2", e.g. "1996-1997" or "1999-2000". THIS STEP IS IMPORTANT BECAUSE THE MACRO USES THE SHEET LABEL TO DETERMINE THE APPROPRIATE DATES FOR THE GAMES. (To rename a sheet, right-click on the sheet's tab and select "Rename")


7. Edit the queries. (Right now, we have a bunch of worksheets, all with the 1993-1994 data)

Go to each sheet in turn and do the following:

a) Right-click somewhere in the imported data. Select "Edit Query".

b) Edit the address of the query as follows:

1994-1995: http://www.goldsheet.com/historic/94nbalog.html

1995-1996: http://www.goldsheet.com/historic/nbalog95.html

1996-1997: http://www.goldsheet.com/historic/nbalog96.html

1997-1998: http://www.goldsheet.com/historic/nbalog97.html

1998-1999: http://www.goldsheet.com/historic/nbalog98.html

1999-2000: http://www.goldsheet.com/historic/nbalog99.html

2000-2001: http://www.goldsheet.com/historic/nbalog00.html

2001-2002: http://www.goldsheet.com/historic/nbalog01.html

2002-2003: http://www.goldsheet.com/historic/nbalog02.html

2003-2004: http://www.goldsheet.com/historic/nbalog03.html

2004-2005: http://www.goldsheet.com/historic/nbalog04.html

2005-2006: http://www.goldsheet.com/historic/nbalog05.html

c) Select "Go" to update the web page.

d1) For the seasons before 2001-2002, do the following: when the page loads, select the arrow next to the first set of game listings. This should be the third arrow on the page, and just above the "ATLANTA HAWKS" line. Now all of the games should be highlighted, and the arrow should be a check mark.

d2) For seasons 2001-2002 and later, select the second arrow, which should include the "The Gold Sheet" logo. Make sure all of the games are selected, and that the arrow turns into a green check mark.

d3) For the 1997-1998 and 2000-2001 seasons, you may need to use the first arrow instead of the third. Just make sure all the games are highlighted.

e) Select Import. Make sure the data is updated.


EXECUTION NOTES:

- Just click the button on each sheet. The status bar should update progress

- If "####" symbols appear in a column, just widen the column to accomodate the data.

- A Spread of 999 or -999 means that there was no line given.

- A Total of 0 means that there was no total given.

- Random errors in input data:
1996-1997: on line 365 of the input data (May 28), the data for the next game (June 1) is on that line (there's a missing line return in the input data). Change the last cell of the line from "176O 6/1 Utah" to "176O". Delete the next line (it will have an "L" in the first column instead of a date).